PG optimization question

Поиск
Список
Период
Сортировка
Искать

PG optimization question

От:
Nickolay <nitro@zhukcity.ru>
Дата:
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.

Re: PG optimization question

От:
Craig Ringer <craig@postnewspapers.com.au>
Дата:
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
FAQ