Re: Sudden drop in DBb performance

On 09/05/2011 09:39 AM, Tomas Vondra wrote:
> On 5 Září 2011, 16:08, Gerhard Wohlgenannt wrote:
>> Below please find the results of vmstat 2 over some periode of time ..
>> with normal database / system load.
>
> What does a "normal load" mean? Does that mean a time when the queries are
> slow?
>
> Are you sure the machine really has 48GB of RAM? Because from the vmstat
> output it seems like there's just 32GB.
>
> procs -----------memory---------- ---swap-- -----io---- -system--
> ----cpu----
> r b swpd free buff cache si so bi bo in cs us sy
> id wa
> 0 0 1342168 336936 107636 31353512 0 0 14 183 1911 3426
> 2 1 93 4
>
>
> 1342168 + 336936 + 107636 + 31353512 = 33140252 ~ 31GB
>
> BTW there's 1.3GB of swap, although it's not used heavily (according to
> the vmstat output).
>
> Otherwise I don't see anything wrong in the output. What is the size of
> the database (use pg_database_size to get it)? Did it grow significantly
> recently?
>
> Tomas
>

Yeah, its interesting that it swapped in memory, but never out. Looking at this vmstat, it does not look like a hardware problem.(Assuming "normal load" means slow queries)

> Did it grow significantly recently?

That's a good thought, maybe the stats are old and you have bad plans? It could also be major updates to the data too (as opposed to growth).

Gerhard, have you done an 'explain analyze' on any of your slow queries? Have you done an analyze lately?

-Andy