Since you are on RHEL 6 I would use ext4 throughout.
Great, I'll look into it. However my research suggested a journalled filesystem is unnecessary for xlogs and I assume ext4 is journalled?
You say you have I/O problems when "stats jobs" run. Can you describe those jobs
and what they are doing?
They summarise all the activity in the logs since the last run, essentially counting logs. Since there are around 30 columns in each log and I want to summarise in a number of ways - think multiple but separate groups bys with a regexp to filter out some of the logs which are not interesting - inevitably there are several passes over the table. Of course regexps are also very slow in postgres. I found various indices did not help at all, query planner thinks sequential scan is the way to go.
If you have a lot of sequential scans on a 10GB table, that will suck majorly
no matter how you tune it.
Ok.. but they I don't care how long they take, only that they don't affect new writes to the table. So will splitting xlogs off into a different partition/lun etc help?
Two more things that you can try out:
- Change the I/O scheduler to "deadline" by booting with "elevator=deadline".
I'll check it out.
- Split the 400GB LUN into several smaller LUNs and use tablespaces.
This could be worth doing on the active logging table if splitting off xlogs won't help. I archive (copy) these records off to the a new table every month and then use inheritance to access the entire logset..
log_master
..inherits
log_active
log_201205
log_201204
..etc
This is how I got the active table down to 10GB :)
I don't say that that is guaranteed to help, but I have made good experiences
with it.
Yours,
Laurenz Albe
thanks,
Ben
