From: | Ben Carbery <ben(dot)carbery(at)gmail(dot)com> |
---|---|
To: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Partitioning Advice |
Date: | 2012-06-07 09:02:21 |
Message-ID: | CACp6DjAAo71TV_L7CixHht-1GofV0SND_MkrPxWuDOTFDV0+Gw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>
>
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Dinsdale | 2012-06-07 13:57:12 | Missing row after update |
Previous Message | Willy-Bas Loos | 2012-06-07 08:55:48 | Re: acessibility for tables |