Re: Performance tips - Mailing list pgsql-general

On 9 Jan 2002, Doug McNaught wrote:

> Andrew Perrin <andrew_perrin@unc.edu> writes:
>
> > Well, here's the output from vmstat:
> >
> > aperrin@hm269-26876:~/afshome/papers/authoritarian$ vmstat
> >    procs                      memory    swap          io     system
> > cpu
> >  r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us
> > sy  id
> >  0  1  0   3052   2132  10460 413284   0   0    11    14    6     5   6
> > 3  17
>
> Sorry, I should have told you to do 'vmstat 5' which will keep
> printing lines of numbers (every 5 seconds) until you interrupt it.
> One line isn't too useful.  But hold off on that for now, see below...

After the query I asked about had run for about 3 hours, I cancelled it
and figured I'll try again later. Here's the output from vmstat 5 during
it:

aperrin@hm269-26876:~$ vmstat 5
   procs                      memory    swap          io     system
cpu
 r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us
sy  id
 0  1  0   3288   2860  10460 412896   0   0     2    14    7     7   6
3  17
 3  1  0   3288   2668  10460 413088   0   0  2823     7  512  1135  27
5  68
 4  0  0   3288   2780  10460 412988   0   0  6078     5  587  1294  58
6  35
 4  0  0   3288   2552  10460 413212   0   0  6034     0  577  1294  60
8  32
 3  1  0   3288   2712  10460 413044   0   0  5256     0  571  1247  48
9  43
 3  0  0   3288   2076  10460 413676   0   0  5366     0  571  1265  51
7  42
 4  0  0   3288   2164  10460 413596   0   0  2671     0  509  1113  22
6  72
 2  1  0   3288   2456  10460 413300   0   0  6187     0  584  1309  65
7  29
 3  0  0   3288   2544  10460 413216   0   0  6037     2  577  1290  61
7  32
 4  0  0   3288   2324  10460 413436   0   0  5170     1  555  1233  53
7  41
 4  0  0   3288   3184  10460 412576   0   0  5532     0  586  1278  52
7  41
 5  0  0   3288   3192  10460 412568   0   0  2603     0  514  1115  25
3  72
 4  0  0   3288   2740  10460 413024   0   0  6212     0  591  1318  57
8  35
 3  1  0   3288   2648  10460 413116   0   0  6128     0  597  1301  57
7  36
 2  1  0   3288   2076  10460 413676   0   0  5211     1  565  1232  55
6  40
 3  1  0   3288   2300  10460 413452   0   0  5458     0  587  1270  52
7  42
 3  0  0   3288   3036  10460 412724   0   0  2645     0  495  1107  25
4  71


>
> 1) VACUUM actually ran that long (possible)
> 2) You had something else holding a transaction open, which prevents
>    VACUUM from running.  Do you have any clients running that hold
>    connections open?

I don't think so. But just to make sure, I killed off postmaster and
restarted it. (I'm the only user of this database, so unless I
inadvertently left something running, there shouldn't be stray
connections.)

>
> You *really* need to VACUUM ANALYZE, especially if your tables have
> been active with updates and deletes.  Once that's done, do an EXPLAIN
> on your long-running queries, post the output along with your schema
> and maybe we can help you speed things up.

I started a vacuum analyze; it's been about 20 minutes now, and nothing's
happened.  I'm going to let it run overnight and see if there's something
there when I wake up. Here's vmstat while the vacuum analyze is running:

   procs                      memory    swap          io     system
cpu
 r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us
sy  id
 5  0  0   3164   2716  10460 421736   0   0  1680     3  444  1030   2
9  89
 4  0  0   3164   2876  10460 421580   0   0  1682     0  417  1003   2
6  92
 3  0  0   3164   2244  10460 422220   0   0  1677     0  405  1000   1
5  94
 4  0  0   3164   2564  10460 421888   0   0  1699     2  427  1012   2
6  92
 4  0  0   3164   2116  10460 422336   0   0  1731     0  434  1021   2
7  91
 4  1  0   3164   2868  10460 421584   0   0  1735     7  426  1017   0
7  93
 4  0  0   3164   3108  10460 421356   0   0  1685     0  418  1005   2
5  93
 1  0  0   3164   2848  10460 421608   0   0  1683     0  420  1027   1
5  94
 3  0  0   3164   2864  10460 421588   0   0  1689     0  407  1003   1
5  94
 3  0  0   3164   2760  10460 421692   0   0  1733     0  424  1024   2
6  92
 4  0  0   3164   2712  10460 421744   0   0  1743     1  422  1017   2
7  91
 4  0  0   3164   2172  10460 422280   0   0  1800     0  426  1032   3
7  90
 4  0  0   3164   2644  10460 421812   0   0  1769     0  422  1022   1
8  91
 3  2  0   3164   2276  10460 422176   0   0  1637     0  400   976   2
5  94
 4  0  0   3164   3036  10460 421420   0   0  1754     0  418  1032   1
6  92
 4  0  0   3164   2552  10460 421904   0   0  1785     2  433  1013   2
6  92
 3  1  0   3164   3088  10460 421364   0   0  1773     1  416   995   1
6  93
 4  0  0   3164   2080  10460 422116  23   0  1703     0  423  1005   2
4  93
 4  0  0   3164   2780  10460 421412   0   0  1733     0  426   998   2
5  93
 4  0  0   3164   2800  10460 421396   0   0  1767     2  427  1002   2
6  92
 4  0  0   3164   2476  10460 421716   0   0  2008     0  444  1044   2
5  93

Here's what top shows (for memory, swap, etc.):
 00:09:01 up 26 days, 15:09,  6 users,  load average: 1.07, 1.04, 1.01
74 processes: 70 sleeping, 4 running, 0 zombie, 0 stopped
CPU states:  81.7% user,   8.0% system,   0.0% nice,  10.3% idle
Mem:    516516K total,   513888K used,     2628K free,    10460K buffers
Swap:   498004K total,     3164K used,   494840K free,   421916K cached

  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
26890 postgres  16   0  4580 4580  3396 R    88.4  0.8   4:25 postmaster


Thanks-
Andy

>
> -Doug
> --
> Let us cross over the river, and rest under the shade of the trees.
>    --T. J. Jackson, 1863
>



----------------------------------------------------------------------
Andrew J Perrin - andrew_perrin@unc.edu - http://www.unc.edu/~aperrin
 Assistant Professor of Sociology, U of North Carolina, Chapel Hill
      269 Hamilton Hall, CB#3210, Chapel Hill, NC 27599-3210 USA