Re: Really really slow select count(*)

On 02/07/2011 09:17 PM, felix wrote:

> Well.... it said "Failed to shutdown ..............."  and then
> returned control. and then proceeded to run for about an hour. I'm
> not sure how graceful that is.

Ah, but that was just the control script that sends the database the
command to shut down. The 'graceful' part, is that the database is being
nice to everyone trying to do things with the data inside.

The control script has a timeout. So it'll send the command, wait a few
seconds to see if the database responds, and then gives up. At that
point, you can use a fast shutdown to tell the database not to be so
nice, and it'll force disconnect all users and shut down as quickly as
possible while maintaining data integrity.

The easiest way to see this in action is to take a look at the postgres
log files. In most default installs, this is in /your/pg/dir/pg_log and
the files follow a postgresql-YYYY-MM-DD_HHMMSS.log format and generally
auto-rotate. If not, set redirect_stderr to on, and make sure
log_directory and log_filename are both set. Those are in your
postgresql.conf, by the way. :)

> I've only been using postgres since we migrated in May

Aha. Yeah... relatively new installs tend to have the worst growing
pains. Once you shake this stuff out, you'll be much better off.

> its only conjecture that the issue is file space bloat or free map
> problems.  those are overall issues that I will get to as soon as I can.
> but this is table specific.

With 300k rows, count(*) isn't a good test, really. That's just on the
edge of big-enough that it could be > 1-second to fetch from the disk
controller, even if the table is fully vacuumed. And in your case, that
table really will likely come from the disk controller, as your
shared_buffers are set way too low. The default settings are not going
to cut it for a database of your size, with the volume you say it's getting.

But you need to put in those kernel parameters I suggested. And I know
this sucks, but you also have to raise your shared_buffers and possibly
your work_mem and then restart the DB. But this time, pg_ctl to invoke a
fast stop, and then use the init script in /etc/init.d to restart it.

> I am not a DBA,

You are now. :) You're administering a database, either as part of your
job description, or because you have no choice because your company
doesn't have an official DBA. Either way, you'll need to know this
stuff. Which is why we're helping out.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas@peak6.com

______________________________________________

See  http://www.peak6.com/email_disclaimer.php
for terms and conditions related to this email