Re: Are indexes blown? - Mailing list pgsql-general
From | Richard Huxton |
---|---|
Subject | Re: Are indexes blown? |
Date | |
Msg-id | 47B5B5AC.8050608@archonet.com Whole thread Raw |
In response to | Re: Are indexes blown? ("Phoenix Kiula" <phoenix.kiula@gmail.com>) |
Responses |
Re: Are indexes blown?
|
List | pgsql-general |
Phoenix Kiula wrote: > On 15/02/2008, Richard Huxton <dev@archonet.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
pgsql-general by date: