pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedVacuum and Memory Loss
4+ messages / 4 participants
[nested] [flat]
* Vacuum and Memory Loss
@ 2006-10-20 01:24 Mike <akiany@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Mike @ 2006-10-20 01:24 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Vacuum and Memory Loss
@ 2006-10-22 21:30 Dave Dutcher <dave@tridecap.com>
parent: Mike <akiany@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Dave Dutcher @ 2006-10-22 21:30 UTC (permalink / raw)
To: 'Mike' <akiany@gmail.com>; pgsql-performance
> 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Vacuum and Memory Loss
@ 2006-10-23 08:45 Richard Huxton <dev@archonet.com>
parent: Mike <akiany@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Richard Huxton @ 2006-10-23 08:45 UTC (permalink / raw)
To: Mike <akiany@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Vacuum and Memory Loss
@ 2006-10-23 21:36 Jim C. Nasby <jim@nasby.net>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jim C. Nasby @ 2006-10-23 21:36 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Mike <akiany@gmail.com>; pgsql-performance
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)
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-10-23 21:36 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-20 01:24 Vacuum and Memory Loss Mike <akiany@gmail.com>
2006-10-22 21:30 ` Dave Dutcher <dave@tridecap.com>
2006-10-23 08:45 ` Richard Huxton <dev@archonet.com>
2006-10-23 21:36 ` Jim C. Nasby <jim@nasby.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox