Skip site navigation (1) Skip section navigation (2)

Re: Really really slow select count(*)

From: Shaun Thomas <sthomas(at)peak6(dot)com>
To: felix <crucialfelix(at)gmail(dot)com>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Really really slow select count(*)
Date: 2011-02-08 14:23:02
Message-ID: 4D5151C6.7040109@peak6.com (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)peak6(dot)com

______________________________________________

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

In response to

Responses

pgsql-performance by date

Next:From: Maciek SakrejdaDate: 2011-02-08 16:23:25
Subject: Re: Really really slow select count(*)
Previous:From: Laszlo NagyDate: 2011-02-08 14:15:27
Subject: Bad query plan when the wrong data type is used

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group