Re: Really really slow select count(*)

You really got screwed by the default settings. You don’t actually need to “hack” the kernel, but you do have to make these changes, because the amount of memory PG has on your system is laughable. That might actually be the majority of your problem.

In your /etc/sysctl.conf, you need these lines:

kernel.shmmax = 68719476736
kernel.shmall = 4294967296

Then you need to run

sysctl -p

These changes can only be made as root, by the way. That will give you more than enough shared memory to restart PG. But it also tells me you’re using the default memory settings. If you have more than 4GB on that system, you need to set shared_buffers to 1G or so. In addition, you need to bump your effective_cache_size to something representing the remaining inode cache in your system. Run ‘free’ to see that.

You also need to know something about unix systems. If you’re running an ubuntu system, your control files are in /etc/init.d, and you can invoke them with:

service pg_cluster restart

or the more ghetto:

/etc/init.d/pg_cluster restart

It may also be named postgres, postgresql, or some other variant.

The problem you’ll run into with this is that PG tries to play nice, so it’ll wait for all connections to disconnect before it shuts down to restart. That means, of course, you need to do a fast shutdown, which forces all connections to disconnect, but the service control script won’t do that. So you’re left with the pg_ctl command again.

pg_ctl –D /my/pg/dir –m fast

And yeah, your checkpoint segments probably are too low. Based on your session table, you should probably have that at 25 or higher.

But that’s part of the point. I highly recommend you scan around Google for pages on optimizing PostgreSQL installs. These are pretty much covered in all of them. Fixing the shmall and shmax kernel settings are also pretty well known in database circles, because they really are set to ridiculously low defaults for any machine that may eventually be a server of anything. I was surprised it blocked the memory request for the max_fsm_pages setting, but that just proves your system was unoptimized in several different ways that may have been slowing down your count(*) statements, among other things.

Please, for your own sanity and the safety of your systems, look this stuff up to the point you can do most of it without looking. You can clearly do well, because you picked your way through the manuals to know about the kernel settings, and that you could call pg_ctl, and so on.

______________________________________________

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