Re: Are indexes blown?

Phoenix Kiula wrote:
> On 15/02/2008, Richard Huxton <dev(at)archonet(dot)com> wrote:
>>
>> Are you sure the two sets of vmstat/top figures are from when PG was
>> crashing/running queries slow? Everything seems idle to me in those figures.
>
> No. They are the vmstat figures from when I was replying to your
> email. What will vmstat tell me and how should I set it up to do
> "vmstat 10 logging"?

I'd write a small script and call it e.g. "trackusage.sh" and save it in
/tmp/

#!/bin/sh
while (/bin/true)
do
date >> /tmp/vmstat_figures.txt
vmstat 10 60 >> /tmp/vmstat_figures.txt
done

Then, set the execute flag on it and do something like:
nohup /tmp/trackusage.sh &

That should run even when you disconnect (don't forget to kill it once
this is fixed). It will log a timestamp every 10 minutes and vmstat
activity between.

[snip logging fragment]
> Now I don't know what is wrong or even where I should look. Postgresql
> is often taking quite a bit of memory and CPU resources.

Just checking - this is a real machine and not a virtual one, isn't it?

> I've reduced work_mem to 10MB and Max_connections to 100. (Anyway, the
> old values were working just fine until recently!)
>
> The biggest problem: when I su into postgres user and do a psql to get
> into the PG console in my SSH, it takes a whole lot of time to come
> up! It used to come up in a jiffy earlier!!! It now shows me this
> error:
>
> ~ >
> psql: could not connect to server: Connection timed out
> Is the server running on host "localhost" and accepting
> TCP/IP connections on port 5432?
>
> Then, five minutes later, I can connect again! In less than a second!
> What gives?

Hopefully vmstat will show us.

> Finally, very simple queries like this one:
>
> select url, disable_in_statistics, id, user_known from links where
> alias = '1yqw7' and status = 'Y' limit 1
>
> Which used to be server in "5 ms" (0.005 seconds) are now taking
> upwards of 200 seconds!

Same symptom. I'd have guessed the machine is running out of memory and
swapping, but the vmstat/top stuff all look fine.

> Your suggestion to "Explain Analyze" --
>
> =# explain analyze select url, disable_in_statistics, id, user_known
> from links where alias = '1yqw7' and status = 'Y' limit 1 ;
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=0.00..8.74 rows=1 width=113) (actual time=9.639..9.643
> rows=1 loops=1)
> -> Index Scan using links2_alias_key on links (cost=0.00..8.74
> rows=1 width=113) (actual time=9.630..9.630 rows=1 loops=1)
> Index Cond: ((alias)::text = '1yqw7'::text)
> Filter: (status = 'Y'::bpchar)
> Total runtime: 16.425 ms

Fine - it's nothing to do with the planner, indexes or anything else.
This is system-related, and vmstat should point us in the right direction.
--
Richard Huxton
Archonet Ltd