Skip site navigation (1) Skip section navigation (2)

Re: Vacuum Problems

From: "Rafael Domiciano" <rafael(dot)domiciano(at)gmail(dot)com>
To: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
Cc: "Matthew T(dot) O'Connor" <matthew(at)zeut(dot)net>, "Rafael Martinez" <r(dot)m(dot)guerrero(at)usit(dot)uio(dot)no>, "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Vacuum Problems
Date: 2008-12-16 17:14:55
Message-ID: 3a0028490812160914v1d6aaa48l8169b0c34cd9c038@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
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(dot)domiciano(at)gmail(dot)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(dot)marlowe(at)gmail(dot)com>
>
>> On Mon, Dec 8, 2008 at 10:17 AM, Rafael Domiciano
>>
>> <rafael(dot)domiciano(at)gmail(dot)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(at)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(at)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.
>>
>
>

In response to

Responses

pgsql-admin by date

Next:From: Alvaro HerreraDate: 2008-12-16 19:33:14
Subject: Re: [ADMIN] shared_buffers and shmmax
Previous:From: Kevin GrittnerDate: 2008-12-16 14:50:02
Subject: Re: How can we see details of function in psql prompt

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group