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: