Thread: Vacuum problem

On Mon, May 13, 2013 at 8:05 AM, S H <msq001@live.com> wrote:
> Hi,
>
> I have my database in which i am executing vacuuming running manually in one
> hour.

vacuum or vacuum full?

> In  my production database sometime when vacuuming is running it is taking
> long time in opening connection.

What is the db server doing when this happens? What does top, vmstat,
iostat etc have to say about it?

> My current version is version 8.1. Is there any known issue about open
> connection problem with vacuuming.

8.1 is out of support and has been for some time.

> I found something but i am not sure if it is applicable to V8.1 too?

Tom wrote:
>     broken if we start the scan at a random place within the catalog, so
> that
>     allowing syncscan is actually a big deoptimization if pg_attribute is
> large
SNIP
>     Back-patch to 8.3, where syncscan was introduced.

It was added in 8.3 so no, it is likely not your issue.

Running 8.1 means you're asking about a system no one else on this
list is likely to still be using much. The hackers aren't gonna be
interested in fixing it either, since it's out of support.

On Mon, May 13, 2013 at 8:37 AM, S H <msq001@live.com> wrote:

>> What is the db server doing when this happens? What does top, vmstat,
>> iostat etc have to say about it?
>
> It is high end server with 96 GB of RAM , 16 core server, but there are many
> other application running, This db is used for monitoring the performance of
> this server and inserting/updating data every one in 10-20 tables.
>
> I am able to reproduce this issue, in case i run vacuuming of my database
> and in parallel try to open 30 connections, sometime db client takes time to
> open connection .... Is there any workaround or there is some known issue
> already existing. If there is any known issue it will be easy to persuade my
> customers to upgrade..

I wonder if you've got a bloated pg catalog then. Certainly sounds
like it's a possibility.
So other than vacuuming when you recreate this, is the server working
hard? What is vacuum vacuuming when this happens (pg_stat_activity
should show that).

>> Running 8.1 means you're asking about a system no one else on this
>> list is likely to still be using much. The hackers aren't gonna be
>> interested in fixing it either, since it's out of support.
>
> I am migrating to new version, for new customers , but for old existing
> customer, it would require significant time. Is there any workaround for the
> same. Like improving some DB parameters.

Probably not. If the tables that make up the pg catalogs are bloated,
you might have to go take some outage time to run a vacuum full /
reindex on them. Some of them, I believe, require single user mode to
do this, but not sure, and definitely not sure if it's na 8.1 thing or
not.

On 5/13/2013 7:10 PM, S H wrote:
> My disk is utilized by many other components, thus do we have minimum
> recommendation my postgres to have sufficient speed. Current
> perfomance of my disk is around 1-5MB/sec. Is it sufficient?

how are you measuring this?   thats painfully slow by today's standards,
even my desktop SATA drives can sustain well over 100MB/second on
sequential read or write

what counts in a database server like postgres is NOT the sequential
transfer speed, instead its the random IO operations/second.   I'm
benchmarking a 8 disk RAID10 right now and seeing around 2000-4000
write/sec and as high as 1000-2400 read/sec during this TPC-B style
transaction benchmark.    iostat -xm during this operation looks like...

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
            3.34    0.00    1.93   39.43    0.00   55.31

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s wMB/s avgrq-sz
avgqu-sz   await  svctm  %util
sdb               0.00     0.10  153.50 4901.30     1.89 40.69
17.25   211.50   42.57   0.20 100.00

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           10.95    0.00    3.98   36.35    0.00   48.72

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s wMB/s avgrq-sz
avgqu-sz   await  svctm  %util
sdb               0.00     0.10  583.40 2955.40     7.10 28.75
20.75   215.77   63.01   0.28  99.97

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           44.36    0.00   14.28   24.61    0.00   16.75

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s wMB/s avgrq-sz
avgqu-sz   await  svctm  %util
sdb               0.00     1.10 2377.30 1062.90    29.23 29.97
35.24    29.53    8.59   0.29  99.95

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           48.39    0.00   16.45   21.18    0.00   13.98

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s wMB/s avgrq-sz
avgqu-sz   await  svctm  %util
sdb               0.00     0.00 2564.30 1182.40    31.15 32.42
34.75    28.81    7.69   0.27  99.97

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           44.59    0.00   15.02   25.38    0.00   15.02

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s wMB/s avgrq-sz
avgqu-sz   await  svctm  %util
sdb               0.00     0.10 2346.10 1097.50    28.66 29.28
34.46    29.58    8.60   0.29  99.98

(these are 10 second averages)

in general, the solution to more storage performance for a database
server is to use more disks in a raid10 configuration,  I have some
raids that are a many as 20 disks, dedicated to database use (everything
else on the server uses other storage).  These are all 15000rpm SAS2
server drives, on a raid controller with 1GB flash-backed write-back cache.




--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On Tue, May 14, 2013 at 7:27 AM, S H <msq001@live.com> wrote:
>> I wonder if you've got a bloated pg catalog then. Certainly sounds
>> like it's a possibility.
>> So other than vacuuming when you recreate this, is the server working
>> hard? What is vacuum vacuuming when this happens (pg_stat_activity
>> should show that)
>
> Does vacuum full is required to avoid bloating, i am running vacuum analyze
> regularly but not vacuum full.

Normally vacuum full is NOT required on a regular basis. However, if
you did something like creation 100M tables and then dropped them, or
did it one at a time real fast, you could outrun your autovacuum
daemons and get bloat in the pg catalog tables.

Just offering a possibility for why a connection might be taking a
long time. There's plenty of other possibilities I'm sure.

Not saying you HAVE bloating there, saying you MIGHT.