Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 45F4164FF57 for ; Tue, 16 Dec 2008 13:15:02 -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 63788-03 for ; Tue, 16 Dec 2008 13:14:57 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.158]) by mail.postgresql.org (Postfix) with ESMTP id A6A6864FEE7 for ; Tue, 16 Dec 2008 13:14:56 -0400 (AST) Received: by fg-out-1718.google.com with SMTP id 16so1437307fgg.23 for ; Tue, 16 Dec 2008 09:14:55 -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=R/GtM3WPpbhKxTZYHlYO1UZRUuzTF9Bymn6Fk8OwYYU=; b=YdwGZgNrD4jRSHtkYEDRvORYmN9spBZBhyMoITtI0D9q6njYBwLhaYM5znlgFP813t tEraOzP5Bxb0GPz9aEf3vAB3lz7Fog71OJmbB0w84eMhIemBqOGhVcuhv6VRCwsoIWLG Hrg9mc5ep5h/3iUkwUrepxBFf3/Bsx0q/1k94= 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=A25BbIgGu5gvGN2uYZW93GLQC49IotTjqvf1af/o2IOFs5PcrMYkyr/5RZjwgZVZd2 WGk7wtrMQ0LszNg3rrnzYLYi/qedumSI+h8dhsMlyuDk28mu/O2QvA0Tzd2eSHxIa8Vd HPD8RRhUXiiAQ0Lh2ook3vOWdjXNCF2KxnjB0= Received: by 10.86.74.4 with SMTP id w4mr4739416fga.2.1229447695098; Tue, 16 Dec 2008 09:14:55 -0800 (PST) Received: by 10.86.86.18 with HTTP; Tue, 16 Dec 2008 09:14:55 -0800 (PST) Message-ID: <3a0028490812160914v1d6aaa48l8169b0c34cd9c038@mail.gmail.com> Date: Tue, 16 Dec 2008 15:14:55 -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: <3a0028490812090258k247ad8b2j8f9037d35b01390f@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_9287_19636961.1229447695091" References: <3a0028490811260543x74d02719w775a457999cfc74@mail.gmail.com> <3a0028490812080504x22618c6fxd7df8febaadb1632@mail.gmail.com> <3a0028490812080811r1c8a4756i1a1a514911741def@mail.gmail.com> <3a0028490812080829t1f485ec3g66626e8c4e2b610c@mail.gmail.com> <3a0028490812080917n170df296kfe33a2733a0d0377@mail.gmail.com> <3a0028490812090258k247ad8b2j8f9037d35b01390f@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/117 X-Sequence-Number: 30950 ------=_Part_9287_19636961.1229447695091 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline I think that I resolved I my problems with vacuum.I started the autovacuum and daily runs vacuum verbose analyze, and weekly Reindex. I'm monitoring the database now. But, my database continues slow. Googling a while I started to read something about pdflush and dirty_ratio. Executing the command (while [ 1 ]; do cat /proc/meminfo | grep Dirty; sleep 1; done) I get: Dirty: 8912 kB Dirty: 5092 kB Dirty: 5912 kB Dirty: 6024 kB Dirty: 6472 kB With the time the Dirty Values increase and then my server does not do any query, or response, even "startup" stay on queue. So, Dirty values goes down and the server continues to proccess the queries and give response: Dirty: 50024 kB Dirty: 50684 kB Dirty: 49888 kB Dirty: 44884 kB Dirty: 16888 kB Dirty: 8884 kB I set the dirty_ratio = 30 and the dirty_background_ratio = 1. I don't know what more to do to solve this slow down problem. Somebody have any ideia? Thnks, Rafael Domiciano 2008/12/9 Rafael Domiciano > 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_9287_19636961.1229447695091 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline I think that I resolved I my problems with vacuum.
I started the autovacuum and daily runs vacuum verbose analyze, and weekly Reindex.
I'm monitoring the database now.

But, my database continues slow.
Googling a while I started to read something about pdflush and dirty_ratio.
Executing the command (while [ 1 ]; do cat /proc/meminfo | grep Dirty; sleep 1; done) I get:
Dirty:            8912 kB
Dirty:            5092 kB
Dirty:            5912 kB
Dirty:            6024 kB
Dirty:            6472 kB

With the time the Dirty Values increase and then my server does not do any query, or response, even "startup" stay on queue. So, Dirty values goes down and the server continues to proccess the queries and give response:
Dirty:           50024 kB
Dirty:           50684 kB
Dirty:           49888 kB
Dirty:           44884 kB
Dirty:           16888 kB
Dirty:           8884 kB

I set the dirty_ratio = 30 and the dirty_background_ratio = 1.

I don't know what more to do to solve this slow down problem.

Somebody have any ideia?

Thnks,

Rafael Domiciano

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

#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

> 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_9287_19636961.1229447695091--