Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id EBD1D64FCCE for ; Tue, 9 Dec 2008 06:58:29 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 60786-06 for ; Tue, 9 Dec 2008 06:58:23 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from gv-out-0910.google.com (gv-out-0910.google.com [216.239.58.188]) by mail.postgresql.org (Postfix) with ESMTP id EFD5D64FC50 for ; Tue, 9 Dec 2008 06:58:21 -0400 (AST) Received: by gv-out-0910.google.com with SMTP id i36so323229gve.7 for ; Tue, 09 Dec 2008 02:58:19 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=i03SfMUDDum0aVWr6l3xeAejP3AcMS+axk0LGLFMzj0=; b=i1O2qRYdnvQ/BD7LOHOqbCPLybvs/fKGFme9GNtzWARRD4sDxjHBD/42ZEAWi4ZR+r eFuEgZJiOcaTPsvnbCvZqmKFKfedbwg9p0bbUDfLjmyzerlin9nS73c08AWyOq9wKKAq jF/s2uAG1L4jUISfhWN6EKcOEKWe3DsQEJ9QM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=k2xWX2zBCqYt/IDZ7URF+uergNIlJ7PqVguOXQqyItCZj6rxwi9s3D2ICpBbpHxpBD CPPxHLpVk+Ejc/sTm6M2bD1pKY+3cQqGBy8MnEZH2J1a3OuqHVdQY3Gj26SOEPEaBbIa kTJlu5HSNjLh4Xhb4nFPR8mDjOF3bQU7X7IyM= Received: by 10.86.52.6 with SMTP id z6mr3297689fgz.63.1228820299307; Tue, 09 Dec 2008 02:58:19 -0800 (PST) Received: by 10.86.86.18 with HTTP; Tue, 9 Dec 2008 02:58:19 -0800 (PST) Message-ID: <3a0028490812090258k247ad8b2j8f9037d35b01390f@mail.gmail.com> Date: Tue, 9 Dec 2008 08:58:19 -0200 From: "Rafael Domiciano" To: "Scott Marlowe" Subject: Re: Vacuum Problems Cc: "Matthew T. O'Connor" , "Rafael Martinez" , "pgsql-admin@postgresql.org" In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_171626_2632489.1228820299291" References: <3a0028490811260543x74d02719w775a457999cfc74@mail.gmail.com> <3a0028490812080504x22618c6fxd7df8febaadb1632@mail.gmail.com> <3a0028490812080811r1c8a4756i1a1a514911741def@mail.gmail.com> <3a0028490812080829t1f485ec3g66626e8c4e2b610c@mail.gmail.com> <3a0028490812080917n170df296kfe33a2733a0d0377@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200812/69 X-Sequence-Number: 30902 ------=_Part_171626_2632489.1228820299291 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 #bgwriter_delay = 200ms # 10-10000ms between rounds bgwriter_lru_percent = 16.0 # 0-100% of LRU buffers scanned/round bgwriter_lru_maxpages = 170 # 0-1000 buffers max written/round bgwriter_all_percent = 7.0 # 0-100% of all buffers scanned/round bgwriter_all_maxpages = 400 # 0-1000 buffers max written/round 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. 2008/12/8 Scott Marlowe > On Mon, Dec 8, 2008 at 10:17 AM, Rafael Domiciano > 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. > ------=_Part_171626_2632489.1228820299291 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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

#bgwriter_delay = 200ms                 # 10-10000ms between rounds
bgwriter_lru_percent = 16.0             # 0-100% of LRU buffers scanned/round
bgwriter_lru_maxpages = 170             # 0-1000 buffers max written/round
bgwriter_all_percent = 7.0              # 0-100% of all buffers scanned/round
bgwriter_all_maxpages = 400             # 0-1000 buffers max written/round

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.

2008/12/8 Scott Marlowe <scott.marlowe@gmail.com>
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.

------=_Part_171626_2632489.1228820299291--