Re: Statistics collection question - Mailing list pgsql-general

Well first question: how can I check if autovacuum is working?


On 04/09/2007, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Phoenix Kiula" <phoenix.kiula@gmail.com> writes:
> > Basically, what I am missing is some info on actually tweaking the
> > postgresql.conf to suit my system.
>
> No, that's *not* what you're missing.  I'm not sure what the problem
> is in your system, but I'm pretty sure that everything you have
> frantically been tweaking is unrelated if not outright
> counterproductive.  You need to stop tweaking and start some methodical
> evidence-gathering to figure out what the problem actually is.
>
> Here are some things I would suggest trying:
>
> 1. Do a VACUUM VERBOSE when the system is fast, and save the output.
> When the system is slow, do another VACUUM VERBOSE, and compare file
> sizes to see if anything seems markedly bloated.  (It might be less
> labor-intensive to copy pg_class.relname, reltuples, relpages columns
> into another table for safekeeping after the first VACUUM, and use SQL
> queries to look for markedly different sizes after the second VACUUM.)


Did this. Saved the files as text files. Did not find much difference
for the tables and indexes stuff. Number of pages required overall
remains the same, by and large. Do I also need to compare the
"pg_toast" type stuff?



> 2. Set up a task to dump the results of
>         select * from pg_locks, pg_stat_activity where pid = procpid
> into a log file every few seconds.  Compare what you see when things
> are fast with when they are slow.  In particular you should fairly
> easily be able to tell if the slow queries are waiting long for locks.



Yes, did. Saved them into four different tables (scores1, scores2,
....where scores1 represents a time when queries were superfast,
scores4 when it was pathetically slow). Then joined them all, two at a
time, to track differences. The only four rows that are different
across these four tables are related to my two major tables:

#  select scores4.relname, scores4.reltuples, scores4.relpages,
scores1.relpages from scores4
left join scores1 on scores4.relname = scores1.relname where
scores4.relpages <> scores1.relpages
;


       relname        |  reltuples  | relpages | relpages
----------------------+-------------+----------+----------
 idx_trads_userid     | 2.82735e+06 |    11652 |    11644
 idx_trads_modifydate | 2.82735e+06 |     7760 |     7744
 tradcount            |      201349 |     1391 |     1388
 trads_alias_key      | 2.82735e+06 |    16172 |    16135
(6 rows)

Time: 2.073 ms


What do I make from this? From what I observe, some of the indexes
have a few more values and a few more pages thereof. This is exactly
how it should be, right? This is from a small database.




> 3. Log the output of "vmstat 1" over time, compare fast and slow
> periods.
>


Following is the vmstat from slow time:


 ~ > vmstat 1
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 0 12  14136  15608   5208 3554516    0    0   200   140    8     7  2  1 86 12
 0 14  14136  17208   5200 3552964    0    0     0    52 1137   372  0  0 23 77
 0 15  14136  17336   5204 3551140    0    0     0    60 1085   237  0  0 10 89
 0 16  14136  16832   5204 3551140    0    0    64     0 1108   323  0  0 25 75
 0 15  14136  15872   5204 3551140    0    0     0     0 1066   242  0  0 25 75
 0 16  14136  17360   5196 3546468    0    0   492   304 1144   570  1  1 29 69
 0 17  14152  17744   5192 3542816    0   48     0   188 1127   169  1  0 25 74
 0 10  14172  23312   5216 3540432    0    0   528   292 1244   453  0  1 25 74
 2  3  14064  15888   5276 3550148    0    0  6644   964 1192   427  1  1 65 33
 0  2  13840  16656   5232 3548596    0    0 24708    60 1413   882  1  2 75 23


Not sure how to read this. We're on 4GB RAM.

Thanks.