Re: Performance tips

On 9 Jan 2002, Doug McNaught wrote:

> Andrew Perrin <andrew_perrin(at)unc(dot)edu> writes:
>
> > Well, here's the output from vmstat:
> >
> > aperrin(at)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(at)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(at)unc(dot)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