From: | Joseph Marlin <jmarlin(at)saucontech(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | WAL tuning advice |
Date: | 2012-07-30 20:12:32 |
Message-ID: | 549441084.80859100.1343679152373.JavaMail.root@email |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
I'm hoping someone can help me figure out how to better tune my WAL parameters. Right now I am seeing WAL activity that is faster than I think it should be - we average about 30 write queries a second at our peak, and yet we're getting a new WAL file every 15-20 seconds.
Our postgresql.conf parameters:
wal_level = hot_standby # minimal, archive, or hot_standby
checkpoint_segments = 10 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 5min # range 30s-1h
checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s # 0 disables
Here is "select * from pg_stat_bgwriter;"
checkpoints_timed: 2081;
checkpoints_req: 18889;
buffers_checkpoint: 4320036;
buffers_clean: 416180117;
maxwritten_clean: 1580518;
buffers_backend: 77676446;
bueffers_backend_fsync: 0;
buffers_alloc: 4804432940;
stats_reset: "2012-06-24 23:13:10.716096-04"
From my previous research, I *think* that that data implies:
1) Due to high ratio of checkpoints_req to checkpoints_timed, I should increase checkpoints_segments, maybe from 10 to 15?
2) There are only about 206 buffers (buffers_checkpoint/(checkpoints_timed+checkpoints_req)) for each checkpoint. I think that is far lower than it should be, but I'm not positive about that, and I don't know what would increase it?
3) maxwitten_clean should be much lower than that?
Thanks for your help!
From | Date | Subject | |
---|---|---|---|
Next Message | Ilija Vidoevski | 2012-07-30 20:17:05 | Re: very slow update query |
Previous Message | Tom Lane | 2012-07-30 19:21:01 | Re: 8.4.12 log truncation not working? |