Re: Partitioning Advice

>
>
> 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