pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedScalability question
5+ messages / 4 participants
[nested] [flat]
* Scalability question
@ 2008-06-11 09:56 Zoltan Boszormenyi <zb@cybertec.at>
2008-06-11 10:15 ` Re: Scalability question tv@fuzzy.cz
2008-06-11 13:34 ` Re: Scalability question Alvaro Herrera <alvherre@commandprompt.com>
2008-06-11 14:13 ` Re: Scalability question Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 3 replies; 5+ messages in thread
From: Zoltan Boszormenyi @ 2008-06-11 09:56 UTC (permalink / raw)
To: pgsql-performance; +Cc: Hans-Juergen Schoenig <hs@cybertec.at>
Hi,
I got a question about scalability in high volume insert situation
where the table has a primary key and several non-unique indexes
on other columns of the table. How does PostgreSQL behave
in terms of scalability? The high volume of inserts comes from
multiple transactions.
Best regards,
Zoltán Böszörményi
--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Scalability question
2008-06-11 09:56 Scalability question Zoltan Boszormenyi <zb@cybertec.at>
@ 2008-06-11 10:15 ` tv@fuzzy.cz
2008-06-11 10:35 ` Re: Scalability question Zoltan Boszormenyi <zb@cybertec.at>
2 siblings, 1 reply; 5+ messages in thread
From: tv@fuzzy.cz @ 2008-06-11 10:15 UTC (permalink / raw)
To: Zoltan Boszormenyi <zb@cybertec.at>; +Cc: pgsql-performance; Hans-Juergen Schoenig <hs@cybertec.at>
> Hi,
>
> I got a question about scalability in high volume insert situation
> where the table has a primary key and several non-unique indexes
> on other columns of the table. How does PostgreSQL behave
> in terms of scalability? The high volume of inserts comes from
> multiple transactions.
>
> Best regards,
> Zoltán Böszörményi
Well, that's a difficult question as it depends on hardware and software,
but with a proper tunning the results may be very good. Just do the basic
PostgreSQL tuning and then tune it for the INSERT performance if needed.
It's difficult to give any other recommendations without a more detailed
knowledge of the problem, but consider these hints:
1) move the pg_xlog to a separate drive (so it's linear)
2) move the table with large amount of inserts to a separate tablespace
3) minimize the amount of indexes etc.
The basic rule is that each index adds some overhead to the insert, but it
depends on datatype, etc. Just prepare some data to import, and run the
insert with and without the indexes and compare the time.
Tomas
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Scalability question
2008-06-11 09:56 Scalability question Zoltan Boszormenyi <zb@cybertec.at>
2008-06-11 10:15 ` Re: Scalability question tv@fuzzy.cz
@ 2008-06-11 10:35 ` Zoltan Boszormenyi <zb@cybertec.at>
0 siblings, 0 replies; 5+ messages in thread
From: Zoltan Boszormenyi @ 2008-06-11 10:35 UTC (permalink / raw)
To: tv@fuzzy.cz; +Cc: pgsql-performance; Hans-Juergen Schoenig <hs@cybertec.at>
tv@fuzzy.cz írta:
>> Hi,
>>
>> I got a question about scalability in high volume insert situation
>> where the table has a primary key and several non-unique indexes
>> on other columns of the table. How does PostgreSQL behave
>> in terms of scalability? The high volume of inserts comes from
>> multiple transactions.
>>
>> Best regards,
>> Zoltán Böszörményi
>>
>
> Well, that's a difficult question as it depends on hardware and software,
> but with a proper tunning the results may be very good. Just do the basic
> PostgreSQL tuning and then tune it for the INSERT performance if needed.
> It's difficult to give any other recommendations without a more detailed
> knowledge of the problem, but consider these hints:
>
> 1) move the pg_xlog to a separate drive (so it's linear)
> 2) move the table with large amount of inserts to a separate tablespace
> 3) minimize the amount of indexes etc.
>
> The basic rule is that each index adds some overhead to the insert, but it
> depends on datatype, etc. Just prepare some data to import, and run the
> insert with and without the indexes and compare the time.
>
> Tomas
>
Thanks. The question is more about theoretical working.
E.g. if INSERTs add "similar" records with identical index records
(they are non-unique indexes) does it cause contention? Because
these similar records add index tuples that supposed to be near
to each other in the btree.
--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Scalability question
2008-06-11 09:56 Scalability question Zoltan Boszormenyi <zb@cybertec.at>
@ 2008-06-11 13:34 ` Alvaro Herrera <alvherre@commandprompt.com>
2 siblings, 0 replies; 5+ messages in thread
From: Alvaro Herrera @ 2008-06-11 13:34 UTC (permalink / raw)
To: Zoltan Boszormenyi <zb@cybertec.at>; +Cc: pgsql-performance; Hans-Juergen Schoenig <hs@cybertec.at>
Zoltan Boszormenyi wrote:
> Hi,
>
> I got a question about scalability in high volume insert situation
> where the table has a primary key and several non-unique indexes
> on other columns of the table. How does PostgreSQL behave
> in terms of scalability? The high volume of inserts comes from
> multiple transactions.
btree and gist indexes can have multiple concurrent insertions in
flight. A potential for blocking is in UNIQUE indexes: if two
transactions try to insert the same value in the unique index, the
second one will block until the first transaction finishes.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Scalability question
2008-06-11 09:56 Scalability question Zoltan Boszormenyi <zb@cybertec.at>
@ 2008-06-11 14:13 ` Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Scott Marlowe @ 2008-06-11 14:13 UTC (permalink / raw)
To: Zoltan Boszormenyi <zb@cybertec.at>; +Cc: pgsql-performance; Hans-Juergen Schoenig <hs@cybertec.at>
On Wed, Jun 11, 2008 at 3:56 AM, Zoltan Boszormenyi <zb@cybertec.at> wrote:
> Hi,
>
> I got a question about scalability in high volume insert situation
> where the table has a primary key and several non-unique indexes
> on other columns of the table. How does PostgreSQL behave
> in terms of scalability? The high volume of inserts comes from
> multiple transactions.
PostgreSQL supports initial fill rates of < 100% for indexes, so set
it to 50% filled and new entries that live near current entries will
have room to be added without having the split the btree.
PostgreSQL also allows you to easily put your indexes on other
paritions / drive arrays etc...
PostgreSQL does NOT store visibility info in the indexes, so they stay
small and updates to them are pretty fast.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2008-06-11 14:13 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-06-11 09:56 Scalability question Zoltan Boszormenyi <zb@cybertec.at>
2008-06-11 10:15 ` tv@fuzzy.cz
2008-06-11 10:35 ` Zoltan Boszormenyi <zb@cybertec.at>
2008-06-11 13:34 ` Alvaro Herrera <alvherre@commandprompt.com>
2008-06-11 14:13 ` Scott Marlowe <scott.marlowe@gmail.com>
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