Обсуждение: Vacuum Problems

Hi there,

Rafael Domiciano wrote:

Hei

> I need some help or just some hints. I am having problems with vacuum
> full in one table only: "clifatura".
> That table has today around 7 million rows.
>

How big is the database?

> I scheduled on the server a cron job to run VACUUM FULL every day at 23
> P.M, but on the following day, at 8 A.M. vacuum is yet working on
> "clifatura"; so I have to kill vacuum.
>

Do you have any locks in this table/database that prevent the vacuum to
be started?

Why do you use VACUUM FULL everyday? This will lock your database until
it finish. I can count with one hand the times we had to run vacuum full
in our systems in the last 6 years.

Use a normal vacuum analyze if 'autovacuum' is not enough for you.

--
 Rafael Martinez, <r.m.guerrero@usit.uio.no>
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/

The database has around 40 Gb.

2008/11/26 Rafael Domiciano <rafael.domiciano@gmail.com>:
> The database has around 40 Gb.
> If I not use vacuum full everyday the database get very slow.
>
> There is no deadlock on the database.

You didn't mention  if you were using autovacuum or not.  You also
didn't mention whether or not you'd tried regular vacuums if for some
reason you can't use autovacuum.  Vacuum full is very intrusive and
can cause index bloat.  It's more of a repair operation than a routine
maintenance one.

What do the last 10 or so lines of vacuum verbose run as a super user say?

I'm not using autovacuum. Regular vacuum goes ok.

On Wed, Nov 26, 2008 at 10:21 AM, Rafael Domiciano
<rafael.domiciano@gmail.com> wrote:
> I'm not using autovacuum. Regular vacuum goes ok.
> To see the last 10 lines of verbose i will need to run vacuum tonight
> If a run a reindex before the vacuum full, increase the "speed" of doing
> vacuum? I found something about it googling.

You should look into enabling autovac.  You can set the sleep
parameter to 10 or 20 to keep it from hogging your I/O bandwidth.
Also, reindex AFTER the vacuum full, not before.

Rafael Domiciano wrote:

> The database has around 40 Gb.
>
> If I not use vacuum full everyday the database get very slow.
>
> There is no deadlock on the database.
> The vacuum does a clean in the table and in every index of the table
> "clifatura". And in the end of the vacuum, seems that vacuum is working
> hard on the table (Vacuum is using CPU and memory), but doesn't end.

Well - maybe an index on the table is broken? I did once run a script on
a database to insert a rather big amount of data. During reindexing the
db ran out of diskspace, but obviously it was freed after that so I
missed the really important error message. I just wondered why the
server had restarted in the meantime. A vacuum on the table in question
took forever. I'm not sure whether I just did a reindex or a dump and
restore to fix this. Just a suggestion. Good luck.

Peter

Rafael Domiciano wrote:
> I'm not using autovacuum. Regular vacuum goes ok.
> To see the last 10 lines of verbose i will need to run vacuum tonight
>
> If a run a reindex before the vacuum full, increase the "speed" of
> doing vacuum? I found something about it googling.

It might help a bit, but by the end of VACUUM FULL you would need to run
reindex again as VACUUM FULL tends to cause a lot of index bloat.   It
is normal for tables to have some slack space, so if you do a regular
vacuum every day (or let autovacuum) it's normal for the table to be a
bit bigger than after a VACUUM FULL, but they should ready steady state
and stop growing.

You may also want to look into using CLUSTER it will rewrite the whole
table and is typically much more efficient that VACUUM FULL, but it
requires 2x disk space while running.

Matt

On Wed, Nov 26, 2008 at 12:54 PM, Matthew T. O'Connor <matthew@zeut.net> wrote:
> Rafael Domiciano wrote:
>>
>> I'm not using autovacuum. Regular vacuum goes ok.
>> To see the last 10 lines of verbose i will need to run vacuum tonight
>>
>> If a run a reindex before the vacuum full, increase the "speed" of doing
>> vacuum? I found something about it googling.
>
> It might help a bit, but by the end of VACUUM FULL you would need to run
> reindex again as VACUUM FULL tends to cause a lot of index bloat.   It is
> normal for tables to have some slack space, so if you do a regular vacuum
> every day (or let autovacuum) it's normal for the table to be a bit bigger
> than after a VACUUM FULL, but they should ready steady state and stop
> growing.

But there are certain use cases that would be classified as
pathological in nature, that you can't handle with regular vacuum.
It's only when you've proven that that's your case, and you can't
program around it, that you should start using vacuum full though.
vacuum full is so expensive in terms of time the system is largely
unusable combined with the need to run reindex or replace the whole
thing with cluster, that if regular or autovacuum can handle the load,
then that's what you do.

I've only seen cases where things like large imports were using a
shared table where it would get bloated insanely if three or four
imports were running at the same time with the occasional update with
no where clause.  Since you can't truncate the table, because it's
shared with other imports, you have to vacuum it, but if you bloat it
by 10x or 100x normal size in 30 seconds, no amount of regular
vacuuming will help.

So, users need to understand why they're always asked if they're
running autovacuum or not.  It's like asking someone with a
nonfunctioning dryer if they've cleaned the lint trap.  It's just
something we assume someone should try first unless there's a good
reason not to.  Because it does work so well most of the time.  I run
autovacuum.  I also email myself the output from vacuum verbose every
week, to look through and see how the tables are looking.  A quick
look near the end tells you if you're mostly ok, and quick
investigation can find bloated tables pretty fast.

So, for the OP, have you tried autovacuum, and why aren't you using
it.  Most of the time people aren't running it it's for erroneous
reasons.

Also, look into updating to 8.3 or above.  With its HOT update
mechanism, and autovacuum enabled by default it handles these
situations quite easily.

 
Hi...
 
I have an IBM server with NUMA architecture, two nodes with 4 CPU quad core and 64 GB of RAM each, and PostgrSQL 8.3.5.

Is there any way to avoid the performance degradation when the load goes up and used the two nodes?

I understand this performance degradation is due to the higher latency than a cpu access a remote memory that the  local memory. But there are ways to improve this?
 
http://archives.postgresql.org/pgsql-general/2005-04/msg01187.php
 
 
Any suggestions?

    Greetings... 
            Thanks...

¿Tus fotos son un desorden? La solución a tus males se llama Galería fotográfica de Windows Live

On Wed, Nov 26, 2008 at 06:13:41PM -0700, Fabricio wrote:

> I have an IBM server with NUMA architecture, two nodes with 4 CPU quad
> core and 64 GB of RAM each, and PostgrSQL 8.3.5. Is there any way to
> avoid the performance degradation when the load goes up and used the
> two nodes? I understand this performance degradation is due to the
> higher latency than a cpu access a remote memory that the  local
> memory. But there are ways to improve this?
>
> http://archives.postgresql.org/pgsql-general/2005-04/msg01187.php
>
> Any suggestions?     Greetings...             Thanks...

I suppose this has nothing to do with PostgreSQL but needs some
operating system level tuning. Maybe there is a way to bind the postgres
processes to one node's CPUs... What operating system are you using?
I know that there are ways to bind processes to a CPU on Linux, I'm not
sure how to assign multiple processes to multiple CPUs. The Linux
scheduler should know about NUMA architectures though, there not that
new any more.

Tino.

--
"What we nourish flourishes." - "Was wir nähren erblüht."

www.lichtkreis-chemnitz.de
www.craniosacralzentrum.de

On 11/27/08, Fabricio <fabrixio1@hotmail.com> wrote:
>  I have an IBM server with NUMA architecture, two nodes with 4 CPU quad core and 64 GB of RAM each, and PostgrSQL
8.3.5.
>
> Is there any way to avoid the performance degradation when the load goes up and used the two nodes?
>
> I understand this performance degradation is due to the higher latency than a cpu access a remote memory that the
localmemory. But there are ways to improve this? 
>
>  http://archives.postgresql.org/pgsql-general/2005-04/msg01187.php


You can use sched_setaffinity to select group of cpu-s for a process
and it's childs:

  http://www.kernel.org/doc/man-pages/online/pages/man2/sched_setaffinity.2.html

There is a bit higher level concept of cpusets too:

  http://www.kernel.org/doc/man-pages/online/pages/man7/cpuset.7.html

In both cases, you can either patch Postgres or write a launcher
that configures CPUs before executing postgres.

--
marko

 
> I suppose this has nothing to do with PostgreSQL but needs some
> operating system level tuning. Maybe there is a way to bind the postgres
> processes to one node's CPUs... What operating system are you using?
 
I am using Red Hat Enterprise 5.2, kernel  2.6.18-92.el5 x86_64

> I know that there are ways to bind processes to a CPU on Linux, I'm not
> sure how to assign multiple processes to multiple CPUs. The Linux
> scheduler should know about NUMA architectures though, there not that
> new any more.
 
something like this?
numactl --membind=1 su - postgres -c "/usr/local/pgsql/bin/pg_ctl start -D /usr/local/pgsql/data/"
 this bind memory to node 1 to use the shared memory on node 1 and cpus preferred node 1. With this the other processes and S.O by default will use the node 0?
 
thanks...

---

¿Tienes cámara digital, más de una cuenta de email, planeas fiestas o blogueas? Entonces, necesitas Windows Live

Hi Marko, thanks for your help...
 
> There is a bit higher level concept of cpusets too:
>
> http://www.kernel.org/doc/man-pages/online/pages/man7/cpuset.7.html
>

> In both cases, you can either patch Postgres or write a launcher
> that configures CPUs before executing postgres.

This paragraph is the liks of cpuset:

       On systems with kernels compiled with built in support for cpusets, all
       processes are attached to a cpuset, and cpusets are always present.  If a
       system supports cpusets, then it will have the entry nodev cpuset in the file
       /proc/filesystems.  By mounting the cpuset file system (see the EXAMPLE
       section below), the administrator can configure the cpusets on a system to
       control the processor and memory placement of processes on that system.  By
       default, if the cpuset configuration on a system is not modified or if the
       cpuset file system is not even mounted, then the cpuset mechanism, though
       present, has no affect on the system's behavior.

Do I need to mount the cpuset file system for use numactl?

Example:
numactl --membind=1 su - postgres -c "/usr/local/pgsql/bin/pg_ctl start -D /usr/local/pgsql/data/"

The two liks are about programing, but can I use only numactl with postgres?

thanks  marko...

---

¿Tus fotos son un desorden? La solución a tus males se llama Galería fotográfica de Windows Live

Zitat von Fabricio <fabrixio1@hotmail.com>:

>
>
> Hi...
>
> I have an IBM server with NUMA architecture, two nodes with 4 CPU
> quad core and 64 GB of RAM each, and PostgrSQL 8.3.5. Is there any
> way to avoid the performance degradation when the load goes up and
> used the two nodes? I understand this performance degradation is due
> to the higher latency than a cpu access a remote memory that the
> local memory. But there are ways to improve this?
>
> http://archives.postgresql.org/pgsql-general/2005-04/msg01187.php

As far as i can see PostgreSQL is not able to support NUMA? With a
little search i get to rellay interesting links according to the
problem and a potential solution supported by Oracle (using huge
pages). Unfortunately PostgreSQL seams not using "huge pages" until
now??

http://oss.linbit.com/hugetlb/
http://kevinclosson.wordpress.com/kevin-closson-index/oracle-on-opteron-k8l-numa-etc/

Any comments on this from PostgreSQL developers?

Regards

Andreas

Hello guys,

On Mon, Dec 8, 2008 at 6:04 AM, Rafael Domiciano
<rafael.domiciano@gmail.com> wrote:
> Hello guys,
> I tried to modify my vacuum routine, and started to only run vacuum verbose
> analyze diary followed by a reindex weekly.
> But I still having problems in my database. The uptime database is hard to
> stay below 10.
> I'm thinking that my hardware is not more good as it was sometime ago.
> The machine is a: 2 x Intel Xeon Dual-Core 2.3 GHz, 2 Gb RAM. The load on
> machine is about at 10000 transactions / m
> Maybe I need more RAM memory?

Likely you need more hard drives and / or a quality caching RAID
controller.  What's your I/O subsystem look like now?

On Mon, Dec 8, 2008 at 6:04 AM, Rafael Domiciano
<rafael.domiciano@gmail.com> wrote:
> Hello guys,
> I tried to modify my vacuum routine, and started to only run vacuum verbose
> analyze diary followed by a reindex weekly.

Have you tried running autovacuum with a naptime of 10 or 20?

How I do to see my I/O subsystem? vmstat? If so, follow:

No, I'm gonna to try

On Mon, Dec 8, 2008 at 9:11 AM, Rafael Domiciano
<rafael.domiciano@gmail.com> wrote:
> How I do to see my I/O subsystem? vmstat? If so, follow:

No, I mean, how many drives do you have, what kind of RAID controller,
if any, how they're configured, and so on.  :)  Sorry wasn't really
clear there was I?

The vmstat numbers are horrible by the way, assuming they're in 1k
blocks, you're reading and writing at < 1 Meg a second.  It almost
looks like a degraded RAID-5 array.

1 Drive Only. This server has no RAID.

On Mon, Dec 8, 2008 at 9:29 AM, Rafael Domiciano
<rafael.domiciano@gmail.com> wrote:
> 1 Drive Only. This server has no RAID.
> Do you think that the I/O is very high and I'm needing a RAID?!

Not necessarily.  Like I said, my laptop currently is about 25 to 30
times faster writing to disk than your server.  So, I think something
is wrong.  Try doing this, run vmstat 1, and while that's running, in
another window, do something like:

time dd if=/dev/zero of=/mnt/myslowdrive/testfile bs=1000000 count=500
time dd of=/dev/null if=/mnt/myslowdrive/testfile

and see what vmstat says while that's running, and how long it takes.
I'm guessing something is very wrong and you'll get really low
numbers.

On Mon, Dec 8, 2008 at 10:17 AM, Rafael Domiciano
<rafael.domiciano@gmail.com> wrote:
> Here's the output. While the process was running my database get sometime
> without doing anything.
> You said that I probably get low numbers, but what numbers?

We're looking for MB/s and the bi/bo fields in bmstat (blocks in / blocks out)

> [root@postgres banco]# time dd if=/dev/zero of=/banco/testfile bs=1000000
> count=500
> 500+0 records in
> 500+0 records out
> 500000000 bytes (500 MB) copied, 85,4485 seconds, 5,9 MB/s
> real    1m25.451s
> user    0m0.003s
> sys     0m1.617s
> [root@postgres banco]# time dd of=/dev/null if=/banco/testfile
> 976562+1 records in
> 976562+1 records out
> 500000000 bytes (500 MB) copied, 47,5025 seconds, 10,5 MB/s
> real    0m47.543s
> user    0m0.457s
> sys     0m1.470s
> And the vmstat output:
>
> procs -----------memory---------- ---swap-- -----io---- --system--
> -----cpu------
>  r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id
> wa st
>  0  8 111760  56432  19812 1305692    0    0   744   832 2488  1518  5  3 46
> 46  0

Here, the bi/bo numbers are 768 in and 832 out.  That's k/second we're
looking at.

Lots of the same numbers cut out.

> procs -----------memory---------- ---swap-- -----io---- --system--
> -----cpu------
>  r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id
> wa st
>  0  6 111760  55452  18528 1296412    0    0    88 25040 1843  1436  2  3 34
> 61  0
>  1  7 111760  52352  18540 1296472    0    0    36 15220 1467   944  3  2 27
> 68  0
>  0  6 111760  54152  18552 1296568    0    0    88 42296 1609   822  1  2 38
> 59  0
>  1  8 111760  52412  18580 1296752    0    0   172 27052 1567   950  2  3 27
> 69  0
>  0  6 111724  53904  18632 1297008  320    0   580  1504 1902  2320  5  6 23
> 66  0
>  1  6 111724  54280  18660 1297200    4    0   200  1060 2014  1783  5  3 44
> 48  0
>  1  7 111676  51388  16340 1302348    8    0   156  1212 1684   848  2  2 53
> 42  0
>  1  6 111668  55040  14864 1301048    0    4   152 46328 1595  5108  1  5 40
> 54  0

Now we're showing that we can write to the disk at 25 to 42 Megs a
second, not too bad.  But it looks really bursty, like it can sustain
this throughput for only a few seconds.   Try writing a larger file
and run vmstat 10 or 60 as well and see what the average over a longer
time with a larger file is.  I get a feeling your machine has a hard
time sustaining throughput for some reason.

Maybe Could be Check-Points? I have changed the parameters sometime ago. Follow:

I think that I resolved I my problems with vacuum.

Rafael Domiciano escribió:

> 2008/12/9 Rafael Domiciano <rafael.domiciano@gmail.com>
>
> > Maybe Could be Check-Points? I have changed the parameters sometime ago.
> > Follow:
> > effective_cache_size = 200MB
> >
> > checkpoint_segments = 40                # in logfile segments, min 1, 16MB
> > each
> > checkpoint_timeout = 3min               # range 30s-1h

checkpoint_timeout 3 minutes?  I think you want to increase that a lot.

> > Now I'm having the following problem on vacuum:
> > vacuumdb: vacuuming of database "Postgres" failed: ERRO:  multiple active
> > vacuums for index "PartInd_replicacao_historico_sl_log_2-node-1".
> >
> > But there's no anymore vacuum running.

Something's very wrong here.  This shouldn't ever happen.  I haven't
followed this thread but you really should investigate this.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.