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 |
