pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPG optimization question
14+ messages / 8 participants
[nested] [flat]
* PG optimization question
@ 2010-01-09 10:24 Nickolay <nitro@zhukcity.ru>
0 siblings, 2 replies; 14+ messages in thread
From: Nickolay @ 2010-01-09 10:24 UTC (permalink / raw)
To: pgsql-performance
Hi 2 all,
Here is my typical configuration: 1(2) GB of RAM, HP ML 350(150) series
server, SATA raid, Linux.
I have 1 big table (called "archive") which contains short text messages
with a plenty of additional service info.
Currently this table contains more than 4M rows for a period of 4,5
months, i.e. each row has average size of 1K.
I'm going to make our application work with partitions of this table
instead of one large table. The primary reason is that eventually we'd
need to remove old rows and it would be pretty hard with one table
because of blocking (and rows are being added constantly).
1. What would be your recommendations on how to partition this table (by
months, years or quarters)?
2. What is recommended PG settings for such configuration? Would it be
ok to set shared_buffers to let's say 512M (if RAM is 1Gig may be
shared_buffers is to be 400M?)? What other settings would you recommend?
Thanks in advance,
Nick.
^ permalink raw reply [nested|flat] 14+ messages in thread
* PG optimization question
@ 2010-01-09 10:32 Nickolay <nitro@zhukcity.ru>
0 siblings, 1 reply; 14+ messages in thread
From: Nickolay @ 2010-01-09 10:32 UTC (permalink / raw)
To: pgsql-performance
Hi 2 all,
Here is my typical configuration: 1(2) GB of RAM, HP ML 350(150) series
server, SATA raid, Linux.
I have 1 big table (called "archive") which contains short text messages
with a plenty of additional service info.
Currently this table contains more than 4M rows for a period of 4,5
months, i.e. each row has average size of 1K.
I'm going to make our application work with partitions of this table
instead of one large table. The primary reason is that eventually we'd
need to remove old rows and it would be pretty hard with one table
because of blocking (and rows are being added constantly).
1. What would be your recommendations on how to partition this table (by
months, years or quarters)?
2. What is recommended PG settings for such configuration? Would it be
ok to set shared_buffers to let's say 512M (if RAM is 1Gig may be
shared_buffers is to be 400M?)? What other settings would you recommend?
Thanks in advance,
Nick.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: PG optimization question
@ 2010-01-09 12:18 Grzegorz Jaśkiewicz <gryzman@gmail.com>
parent: Nickolay <nitro@zhukcity.ru>
1 sibling, 1 reply; 14+ messages in thread
From: Grzegorz Jaśkiewicz @ 2010-01-09 12:18 UTC (permalink / raw)
To: Nickolay <nitro@zhukcity.ru>; +Cc: pgsql-performance
maybe that 'one big table' needs something called 'normalisation'
first. See how much that will shed off. You might be surprised.
The partitioning needs to be done by some constant intervals, of time
- in your case. Whatever suits you, I would suggest to use the rate
that will give you both ease of archiving/removal of old data (so not
too wide), and also, one that would make sure that most of the data
you'll be searching for in your queries will be in one , two
partitions per query.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: PG optimization question
@ 2010-01-09 12:32 Thomas Kellerer <spam_eater@gmx.net>
parent: Nickolay <nitro@zhukcity.ru>
1 sibling, 0 replies; 14+ messages in thread
From: Thomas Kellerer @ 2010-01-09 12:32 UTC (permalink / raw)
To: pgsql-performance
Nickolay wrote on 09.01.2010 11:24:
> it would be pretty hard with one table because of blocking
What do you man with "because of blocking"?
Thomas
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: PG optimization question
@ 2010-01-09 12:42 Nickolay <nitro@zhukcity.ru>
parent: Grzegorz Jaśkiewicz <gryzman@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: Nickolay @ 2010-01-09 12:42 UTC (permalink / raw)
To: Grzegorz Jaśkiewicz <gryzman@gmail.com>; +Cc: pgsql-performance
I do not see any way to normalize this table anymore. it's size is 4Gig
for ~4M rows, i.e. 1Kb per row, i think it's ok.
Also there are 2 indexes: by date_time and by a couple of service fields
(total index size is 250Mb now).
I think i'll be going to partition by months (approx. 1M rows or 1Gig
per month), so it would be like 60 partitions for 5 years. Is that OK
for postgres?
Oh, btw, 95% of queries are searching rows for current date (last 24 hours).
Also we use SELECT...FOR UPDATE row-level locking for updating the rows
in archive (i.e. we INSERT new row when starting outgoing message
transmittion and then doing SELECT...FOR UPDATE and UPDATE for source
(incoming) message when outgoing message was sent), so I guess we would
have to explicitly write the name of partition table (i.e.
"archive_2009_12" instead of "archive") for SELECT...FOR UPDATE and
UPDATE requests, as they may need to access row in previous partition
instead of the current one.
Grzegorz Jaśkiewicz wrote:
> maybe that 'one big table' needs something called 'normalisation'
> first. See how much that will shed off. You might be surprised.
> The partitioning needs to be done by some constant intervals, of time
> - in your case. Whatever suits you, I would suggest to use the rate
> that will give you both ease of archiving/removal of old data (so not
> too wide), and also, one that would make sure that most of the data
> you'll be searching for in your queries will be in one , two
> partitions per query.
>
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: PG optimization question
@ 2010-01-09 12:46 Kenneth Marshall <ktm@rice.edu>
parent: Nickolay <nitro@zhukcity.ru>
0 siblings, 1 reply; 14+ messages in thread
From: Kenneth Marshall @ 2010-01-09 12:46 UTC (permalink / raw)
To: Nickolay <nitro@zhukcity.ru>; +Cc: Grzegorz Ja??kiewicz <gryzman@gmail.com>; pgsql-performance
On Sat, Jan 09, 2010 at 03:42:08PM +0300, Nickolay wrote:
> I do not see any way to normalize this table anymore. it's size is 4Gig for
> ~4M rows, i.e. 1Kb per row, i think it's ok.
> Also there are 2 indexes: by date_time and by a couple of service fields
> (total index size is 250Mb now).
> I think i'll be going to partition by months (approx. 1M rows or 1Gig per
> month), so it would be like 60 partitions for 5 years. Is that OK for
> postgres?
Not a problem. We have a log server that has 64 daily partitions.
> Oh, btw, 95% of queries are searching rows for current date (last 24
> hours).
You may want to use a daily staging table and then flush to the
monthly archive tables at the end of the day.
Ken
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: PG optimization question
@ 2010-01-09 12:59 Nickolay <nitro@zhukcity.ru>
parent: Kenneth Marshall <ktm@rice.edu>
0 siblings, 1 reply; 14+ messages in thread
From: Nickolay @ 2010-01-09 12:59 UTC (permalink / raw)
To: Kenneth Marshall <ktm@rice.edu>; +Cc: pgsql-performance
That may help with the queries speed (not a problem now), but we'll then
have to add UNION statement for daily staging table for other 5% of
requests, right? And there would be a moment when daily message is in
archive table AND in daily table (while transferring from daily table to
archive).
Our main problem is in blocking when doing DELETE (app sometimes freezes
for a long time), and also we have to do VACUUM on live table, which is
not acceptable in our app.
Thanks for your reply, I was kinda worried about number of partitions
and how this would affect PG query execution speed.
Kenneth Marshall wrote:
>> Oh, btw, 95% of queries are searching rows for current date (last 24
>> hours).
>>
>
> You may want to use a daily staging table and then flush to the
> monthly archive tables at the end of the day.
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: PG optimization question
@ 2010-01-09 15:37 Pierre Frédéric Caillaud <lists@peufeu.com>
parent: Nickolay <nitro@zhukcity.ru>
0 siblings, 1 reply; 14+ messages in thread
From: Pierre Frédéric Caillaud @ 2010-01-09 15:37 UTC (permalink / raw)
To: Nickolay <nitro@zhukcity.ru>; Kenneth Marshall <ktm@rice.edu>; +Cc: pgsql-performance
> That may help with the queries speed (not a problem now), but we'll then
> have to add UNION statement for daily staging table for other 5% of
> requests, right? And there would be a moment when daily message is in
> archive table AND in daily table (while transferring from daily table to
> archive).
> Our main problem is in blocking when doing DELETE (app sometimes freezes
> for a long time), and also we have to do VACUUM on live table, which is
> not acceptable in our app.
>
> Thanks for your reply, I was kinda worried about number of partitions
> and how this would affect PG query execution speed.
>
> Kenneth Marshall wrote:
>>> Oh, btw, 95% of queries are searching rows for current date (last 24
>>> hours).
>>>
>>
>> You may want to use a daily staging table and then flush to the monthly
>> archive tables at the end of the day.
If the rows in the archive tables are never updated, this strategy means
you never need to vacuum the big archive tables (and indexes), which is
good. Also you can insert the rows into the archive table in the order of
your choice, the timestamp for example, which makes it nicely clustered,
without needing to ever run CLUSTER.
And with partitioning you can have lots of indexes on the staging table
(and current months partition) (to speed up your most common queries which
are likely to be more OLTP), while using less indexes on the older
partitions (saves disk space) if queries on old partitions are likely to
be reporting queries which are going to grind through a large part of the
table anyway.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: PG optimization question
@ 2010-01-09 18:50 Nickolay <nitro@zhukcity.ru>
parent: Pierre Frédéric Caillaud <lists@peufeu.com>
0 siblings, 2 replies; 14+ messages in thread
From: Nickolay @ 2010-01-09 18:50 UTC (permalink / raw)
To: Pierre Frédéric Caillaud <lists@peufeu.com>; +Cc: pgsql-performance
Okay, I see your point with staging table. That's a good idea!
The only problem I see here is the transfer-to-archive-table process. As
you've correctly noticed, the system is kind of a real-time and there
can be dozens of processes writing to the staging table, i cannot see
how to make the transfer/flush process right and clear...
Pierre Frédéric Caillaud wrote:
>>>> Oh, btw, 95% of queries are searching rows for current date (last
>>>> 24 hours).
>>>>
>>>
>>> You may want to use a daily staging table and then flush to the
>>> monthly archive tables at the end of the day.
>
> If the rows in the archive tables are never updated, this strategy
> means you never need to vacuum the big archive tables (and indexes),
> which is good. Also you can insert the rows into the archive table in
> the order of your choice, the timestamp for example, which makes it
> nicely clustered, without needing to ever run CLUSTER.
>
> And with partitioning you can have lots of indexes on the staging
> table (and current months partition) (to speed up your most common
> queries which are likely to be more OLTP), while using less indexes on
> the older partitions (saves disk space) if queries on old partitions
> are likely to be reporting queries which are going to grind through a
> large part of the table anyway.
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: PG optimization question
@ 2010-01-09 18:58 Ludwik Dylag <ldylag@gmail.com>
parent: Nickolay <nitro@zhukcity.ru>
1 sibling, 0 replies; 14+ messages in thread
From: Ludwik Dylag @ 2010-01-09 18:58 UTC (permalink / raw)
To: Nickolay <nitro@zhukcity.ru>; +Cc: Pierre Frédéric Caillaud <lists@peufeu.com>; pgsql-performance
2010/1/9 Nickolay <nitro@zhukcity.ru>
> Okay, I see your point with staging table. That's a good idea!
> The only problem I see here is the transfer-to-archive-table process. As
> you've correctly noticed, the system is kind of a real-time and there can be
> dozens of processes writing to the staging table, i cannot see how to make
> the transfer/flush process right and clear...
>
> The simplest way to do this is to create view and add/remove first/last day
by recreating the view on daily interval.
--
Ludwik Dyląg
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: PG optimization question
@ 2010-01-10 01:24 Craig Ringer <craig@postnewspapers.com.au>
parent: Nickolay <nitro@zhukcity.ru>
0 siblings, 0 replies; 14+ messages in thread
From: Craig Ringer @ 2010-01-10 01:24 UTC (permalink / raw)
To: Nickolay <nitro@zhukcity.ru>; +Cc: pgsql-performance
On 9/01/2010 6:32 PM, Nickolay wrote:
> Hi 2 all,
>
> Here is my typical configuration: 1(2) GB of RAM, HP ML 350(150) series
> server, SATA raid, Linux.
>
> I have 1 big table (called "archive") which contains short text messages
> with a plenty of additional service info.
> Currently this table contains more than 4M rows for a period of 4,5
> months, i.e. each row has average size of 1K.
>
> I'm going to make our application work with partitions of this table
> instead of one large table. The primary reason is that eventually we'd
> need to remove old rows and it would be pretty hard with one table
> because of blocking (and rows are being added constantly).
DELETEs shouldn't block concurrent INSERTs.
That said, dropping a partition is a lot more convenient than DELETEing
from a big table.
--
Craig Ringer
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: PG optimization question
@ 2010-01-10 10:52 Pierre Frédéric Caillaud <lists@peufeu.com>
parent: Nickolay <nitro@zhukcity.ru>
1 sibling, 1 reply; 14+ messages in thread
From: Pierre Frédéric Caillaud @ 2010-01-10 10:52 UTC (permalink / raw)
To: Hannu Krosing <hannu@2ndquadrant.com>; Nickolay <nitro@zhukcity.ru>; +Cc: pgsql-performance
> If you transfer (delete from staging, insert into archive) in one
> transaction , then it will be always visible in exactly one of them,
> and exatly once in a view over both staging and archive(s).
Does the latest version implement this :
INSERT INTO archive (...) DELETE FROM staging WHERE ... RETURNING ...
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: PG optimization question
@ 2010-01-10 18:45 Robert Haas <robertmhaas@gmail.com>
parent: Pierre Frédéric Caillaud <lists@peufeu.com>
0 siblings, 1 reply; 14+ messages in thread
From: Robert Haas @ 2010-01-10 18:45 UTC (permalink / raw)
To: Pierre Frédéric Caillaud <lists@peufeu.com>; +Cc: Hannu Krosing <hannu@2ndquadrant.com>; Nickolay <nitro@zhukcity.ru>; pgsql-performance
2010/1/10 Pierre Frédéric Caillaud <lists@peufeu.com>:
>
>> If you transfer (delete from staging, insert into archive) in one
>> transaction , then it will be always visible in exactly one of them,
>> and exatly once in a view over both staging and archive(s).
>
> Does the latest version implement this :
>
> INSERT INTO archive (...) DELETE FROM staging WHERE ... RETURNING ...
No. There are no plans to support that, though there are proposals to support:
WITH x AS (DELETE FROM staging WHERE ... RETURNING ...) INSERT INTO
archive (...) SELECT ... FROM x
I'm not sure how much that will help though since, in the designs so
far discused, the tuples won't be pipelined.
...Robert
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: PG optimization question
@ 2010-01-11 11:25 Pierre Frédéric Caillaud <lists@peufeu.com>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: Pierre Frédéric Caillaud @ 2010-01-11 11:25 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Hannu Krosing <hannu@2ndquadrant.com>; Nickolay <nitro@zhukcity.ru>; pgsql-performance
On Sun, 10 Jan 2010 19:45:32 +0100, Robert Haas <robertmhaas@gmail.com>
wrote:
> 2010/1/10 Pierre Frédéric Caillaud <lists@peufeu.com>:
>>
>>> If you transfer (delete from staging, insert into archive) in one
>>> transaction , then it will be always visible in exactly one of them,
>>> and exatly once in a view over both staging and archive(s).
>>
>> Does the latest version implement this :
>>
>> INSERT INTO archive (...) DELETE FROM staging WHERE ... RETURNING ...
>
> No. There are no plans to support that, though there are proposals to
> support:
>
> WITH x AS (DELETE FROM staging WHERE ... RETURNING ...) INSERT INTO
> archive (...) SELECT ... FROM x
>
> I'm not sure how much that will help though since, in the designs so
> far discused, the tuples won't be pipelined.
>
> ...Robert
>
Yeah, but it's a lot more user-friendly than SELECT FOR UPDATE, INSERT
SELECT, DELETE...
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2010-01-11 11:25 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-01-09 10:24 PG optimization question Nickolay <nitro@zhukcity.ru>
2010-01-09 12:18 ` Grzegorz Jaśkiewicz <gryzman@gmail.com>
2010-01-09 12:42 ` Nickolay <nitro@zhukcity.ru>
2010-01-09 12:46 ` Kenneth Marshall <ktm@rice.edu>
2010-01-09 12:59 ` Nickolay <nitro@zhukcity.ru>
2010-01-09 15:37 ` Pierre Frédéric Caillaud <lists@peufeu.com>
2010-01-09 18:50 ` Nickolay <nitro@zhukcity.ru>
2010-01-09 18:58 ` Ludwik Dylag <ldylag@gmail.com>
2010-01-10 10:52 ` Pierre Frédéric Caillaud <lists@peufeu.com>
2010-01-10 18:45 ` Robert Haas <robertmhaas@gmail.com>
2010-01-11 11:25 ` Pierre Frédéric Caillaud <lists@peufeu.com>
2010-01-09 12:32 ` Thomas Kellerer <spam_eater@gmx.net>
2010-01-09 10:32 PG optimization question Nickolay <nitro@zhukcity.ru>
2010-01-10 01:24 ` Craig Ringer <craig@postnewspapers.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox