Phoenix Kiula wrote:
On 15/02/2008, Richard Huxton <dev@xxxxxxxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
