Re: Statistics collection question

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

On 04/09/2007, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Phoenix Kiula" <phoenix(dot)kiula(at)gmail(dot)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.