Thread: Vacuum and Memory Loss
Hello friends, I am responsible for maintaining a high volume website using postgresql 8.1.4. Given the amount of reads and writes, I vacuum full the server a few times a week around 1, 2 AM shutting down the site for a few minutes. The next day morning around 10 - 11 AM the server slows down to death. It used to be that the error 'Too many clients' would be recorded, until I increased the number of clients it can handle, and now it simply slows down to death having lots and lots of postmaster processes running: Tasks: 665 total, 10 running, 655 sleeping, 0 stopped, 0 zombie Cpu(s): 14.9% us, 16.7% sy, 0.0% ni, 0.0% id, 68.4% wa, 0.0% hi, 0.0% si Mem: 2074932k total, 2051572k used, 23360k free, 2736k buffers Swap: 2096440k total, 1844448k used, 251992k free, 102968k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 6420 postgres 15 0 26912 11m 10m R 3.6 0.6 0:00.11 postmaster 6565 postgres 16 0 26912 11m 10m S 3.6 0.6 0:00.12 postmaster 6707 postgres 15 0 26912 11m 10m S 3.3 0.6 0:00.10 postmaster 6715 postgres 15 0 26912 11m 10m S 3.3 0.6 0:00.11 postmaster 6765 postgres 15 0 26912 11m 10m S 3.3 0.6 0:00.11 postmaster 6147 postgres 15 0 26912 11m 10m R 3.0 0.6 0:00.15 postmaster 6311 postgres 15 0 26904 11m 10m R 3.0 0.6 0:00.10 postmaster 6551 postgres 15 0 26912 11m 10m R 3.0 0.6 0:00.09 postmaster 6803 postgres 16 0 26912 11m 10m R 3.0 0.6 0:00.09 postmaster 6255 postgres 15 0 26904 11m 10m R 2.6 0.6 0:00.14 postmaster 6357 postgres 15 0 26912 11m 10m R 2.6 0.6 0:00.11 postmaster 6455 postgres 15 0 26912 11m 10m S 2.6 0.6 0:00.10 postmaster 6457 postgres 15 0 26912 11m 10m S 2.6 0.6 0:00.11 postmaster 6276 postgres 15 0 26912 11m 10m S 2.3 0.6 0:00.10 postmaster 6475 postgres 15 0 26912 11m 10m R 2.3 0.6 0:00.11 postmaster 6868 postgres 15 0 26912 11m 10m S 2.3 0.6 0:00.07 postmaster 6891 postgres 15 0 26912 11m 10m S 1.3 0.6 0:00.19 postmaster Thanks for your help in advance, Mike
> Hello friends, > > I am responsible for maintaining a high volume website using > postgresql > 8.1.4. Given the amount of reads and writes, I vacuum full > the server a > few times a week around 1, 2 AM shutting down the site for a few > minutes. The next day morning around 10 - 11 AM the server slows down > to death. It used to be that the error 'Too many clients' would be > recorded, until I increased the number of clients it can handle, and > now it simply slows down to death having lots and lots of postmaster > processes running: If you are saying that running the vacuum full helps your performance, then you want to make sure you are running plain vacuum and analyze frequently enough. If you have a database which has lots of update and delete statements, and you do not run vacuum regularly enough, you can end up with lots dead blocks slowing down database scans. If you do lots of updates and deletes you should shedule vacuum and analyze more often, or you might want to look into running auto vacuum: http://www.postgresql.org/docs/8.1/interactive/maintenance.html#AUTOVACUUM If you aren't doing lots of updates and deletes, then maybe you just have a busy database. Lots of postmaster processes implies you have lots of clients connecting to your database. You can turn on stats_command_string and then check the pg_stat_activity table to see what these connections are doing. If they are running queries, you can try to optimize them. Try turning on logging of long running queries with log_min_duration_statement. Then use EXPLAIN ANALYZE to see why the query is slow and if anything can be done to speed it up.
Mike wrote: > Hello friends, > > I am responsible for maintaining a high volume website using postgresql > 8.1.4. Given the amount of reads and writes, I vacuum full the server a > few times a week around 1, 2 AM shutting down the site for a few > minutes. The next day morning around 10 - 11 AM the server slows down > to death. It used to be that the error 'Too many clients' would be > recorded, until I increased the number of clients it can handle, and > now it simply slows down to death having lots and lots of postmaster > processes running: > > Tasks: 665 total, 10 running, 655 sleeping, 0 stopped, 0 zombie > Cpu(s): 14.9% us, 16.7% sy, 0.0% ni, 0.0% id, 68.4% wa, 0.0% hi, > 0.0% si > Mem: 2074932k total, 2051572k used, 23360k free, 2736k > buffers > Swap: 2096440k total, 1844448k used, 251992k free, 102968k cached This seems to be saying you have 1.8GB of swap in use. I'd start by checking with vmstat whether you're actively swapping. If so, you're overallocating memory. -- Richard Huxton Archonet Ltd
On Mon, Oct 23, 2006 at 09:45:59AM +0100, Richard Huxton wrote: > Mike wrote: > >Hello friends, > > > >I am responsible for maintaining a high volume website using postgresql > >8.1.4. Given the amount of reads and writes, I vacuum full the server a > >few times a week around 1, 2 AM shutting down the site for a few > >minutes. The next day morning around 10 - 11 AM the server slows down > >to death. It used to be that the error 'Too many clients' would be > >recorded, until I increased the number of clients it can handle, and > >now it simply slows down to death having lots and lots of postmaster > >processes running: > > > >Tasks: 665 total, 10 running, 655 sleeping, 0 stopped, 0 zombie > >Cpu(s): 14.9% us, 16.7% sy, 0.0% ni, 0.0% id, 68.4% wa, 0.0% hi, > >0.0% si > >Mem: 2074932k total, 2051572k used, 23360k free, 2736k > >buffers > >Swap: 2096440k total, 1844448k used, 251992k free, 102968k cached > > This seems to be saying you have 1.8GB of swap in use. I'd start by > checking with vmstat whether you're actively swapping. If so, you're > overallocating memory. Which could easily be caused by a combination of trying to handle too many database connections at once and setting work_mem too high. I've often gone into client sites to find they've set the database up to accept hundreds or thousands of connections, even though the hardware they're running on would most likely fall over if they actually had that many simultaneously active connections. In many cases, increasing the number of connections the the database will hurt performance rather than help it, because you're now asking an already overloaded server to do even more work. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)