Postgres 8.2 memory weirdness - Mailing list pgsql-performance

From Tory M Blue
Subject Postgres 8.2 memory weirdness
Date
Msg-id 8a547c840801231023q55796128nb2e88b8c8cfe86e@mail.gmail.com
Whole thread Raw
Responses Re: Postgres 8.2 memory weirdness
List pgsql-performance
I'm not sure what is going on but looking for some advice, knowledge.

I'm running multiple postgres servers in a slon relationship. I have
hundreds of thousands of updates, inserts a day. But what I'm seeing
is my server appears to "deallocate" memory (for the lack of a better
term) and performance goes to heck, slow response, a sub second query
takes anywhere from 6-40 seconds to complete when this happens.

My guess is that postgres does not have enough memory to handle the
query and starts to swap (although I can't see any swapping happening
(vmstat)

Some examples: Also when the memory deallocates, I start stacking
connections (again, performance goes to heck).

FC6, postgres 8.2 on a dual quad core intel box, 8 gigs of physical RAM

Between 8:17:36 and 8:17:57, my system goes from almost all memory
consumed to almost all memory free.. Previous to this big swing, Cache
goes from 7 gigs down to what you see here, connections start stacking
and IOWAIT goes thru the roof.

1200932256 - Mon Jan 21 08:17:36 2008 - qdb02.gc.sv.admission.net - 0
sec. elapsed.
procs -----------memory---------- ---swap-- -----io---- --system--
-----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  2   9072 297460   4904 1543316    0    0    52   175    2    2 21
3 75  2  0

1200932277 - Mon Jan 21 08:17:57 2008 - qdb02.gc.sv.admission.net - 2
sec. elapsed.
procs -----------memory---------- ---swap-- -----io---- --system--
-----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
18  6   9072 6354560   6128 1574324    0    0    52   175    2    2 21
 3 75  2  0

At this point I have connections stacking (simple queries),  Until
what I believe is that the kernel starts to allocate memory for the dB
(or the db starts to be moved from disk to memory?) (queries, tables
etc).. However performance is still dismal and I do see high IOWAIT
when I don't have sufficient memory for the DB (again my feeling). If
I do a reindex at this time, the system alocates most of my RAM and my
queries are subsecond agiain and I work fine until the next time, the
DB appears to be flushed from RAM.

I'm having a hard time understanding what I'm seeing. I do grab
netstat/ps/free/vmstat/netstat every few seconds so I have insight as
to the systems health when these things happen.

I suspect I've outgrown our initial postgres config, or there are more
sysctl or other kernel tweaks that need to happen.

I really am just trying to understand the memory allocation etc. And
why my memory goes from fully utilized to 90% free and DB performance
goes to heck.

I'll provide more info as needed, and my apologies for this being a
bit scattered, but I'm really confused.  I'm either running out of a
resource or other (but no errors in any logs, postgres or otherwise)..

Thanks
Tory

pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: planner chooses unoptimal plan on joins with complex key
Next
From: "Thomas Lozza"
Date:
Subject: Vacuum and FSM page size