Re: Analyse without locking?

On Sat, Nov 28, 2009 at 6:57 PM, Richard Neill <rn214(at)cam(dot)ac(dot)uk> wrote:
> Greg Smith wrote:
>>
>> Richard Neill wrote:
>>>
>>> Or am I barking up the wrong tree entirely?
>>
>> If you haven't already tuned checkpoint behavior, it's more likely that's
>> causing a dropout than autovacuum.  See the checkpoint_segments section of
>> http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server for an intro.
>>
>
> Greg Smith wrote:
>> Richard Neill wrote:
>>> Or am I barking up the wrong tree entirely?
>> If you haven't already tuned checkpoint behavior, it's more likely
>> that's causing a dropout than autovacuum.  See the checkpoint_segments
>> section of http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
>> for an intro.
>>
>
> Thanks - I did that already - it's currently
>   checkpoint_segments = 64
>
> Now, I understand that increasing checkpoint_segments is generally a good
> thing (subject to some limit), but doesn't that just mean that instead of
> say a 1 second outage every minute, it's a 10 second outage every 10
> minutes?
>
> Also, correct me if I'm wrong, but mere selects shouldn't cause any addition
> to the WAL. I'd expect that a simple row insert might require perhaps 1kB of
> disk writes(*), in which case we're looking at only a few kB/sec at most of
> writes in normal use.?
>
> Is it possible (or even sensible) to do a manual vacuum analyze with
> nice/ionice?

this is the job of autovacuum_vacuum_cost_delay and vacuum_cost_delay.

About checkpoint, you may eventually set :
synchronous_commit = off

Please note that you may loose some queries if the server badly crash.
(but that shouldn't cause database corruption like a fsync = off)

If you are running on linux, you could try to monitor (rrd is your
friend) /proc/meminfo and specifically the "Dirty" field.

Read your syslog log to see if the checkpoint is a problem.
Here is a sample of mine (cleaned) :
checkpoint complete: wrote 3117 buffers (1.2%); 0 transaction log
file(s) added, 0 removed, 3 recycled;
write=280.213 s, sync=0.579 s, total=280.797 s

The more Dirty page (/proc/meminfo), the longer is your sync time.
A high sync time can easily "lock" your server.

To reduce the dirty page, tune /proc/sys/vm/dirty_background_ratio
I have set it to "1" on my 32GB servers.

You should also be carefull about all the other
/proc/sys/vm/dirty_*
And specifically /proc/sys/vm/dirty_ratio :
Maximum percentage of total memory that can be filled with dirty pages
before processes are forced to write dirty buffers themselves during
their time slice instead of being allowed to do more writes.
Note that all processes are blocked for writes when this happens, not
just the one that filled the write buffers.

About "ionice" : it only work with the CFQ I/O Scheduler.
And CFQ is a very bad idea when using postgresql.

--
Laurent "ker2x" Laborde
Sysadmin & DBA at http://www.over-blog.com/