From: PFC <lists@peufeu.com>
To: andrew klassen <aptklassen@yahoo.com>
To: James Mansion <james@mansionfamily.plus.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: insert/update tps slow with indices on table > 1M rows
Date: Wed, 04 Jun 2008 23:50:09 +0200
Message-ID: <op.ub8uxvcrcigqcu@apollo13.peufeu.com> (raw)
In-Reply-To: <354284.6724.qm@web37304.mail.mud.yahoo.com>
References: <354284.6724.qm@web37304.mail.mud.yahoo.com>
> I am using the c-library interface and for these particular transactions
> I preload PREPARE statements. Then as I get requests, I issue a BEGIN,
> followed by at most 300 EXECUTES and then a COMMIT. That is the
> general scenario. What value beyond 300 should I try?
> Thanks.
Do you have PREPARE statements whose performance might change as the
table grows ?
I mean, some selects, etc... in that case if you start with an empty
table, after inserting say 100K rows you might want to just disconnect,
reconnect and analyze to trigger replanning of those statements.
> Also, how might COPY (which involves file I/O) improve the
> above scenario?
It won't but if you see that COPY is very much faster than your INSERT
based process it will give you a useful piece of information.
I understand your problem is :
- Create table with indexes
- Insert batches of rows
- After a while it gets slow
Try :
- Create table with indexes
- COPY huge batch of rows
- Compare time with above
Since COPY also updates the indexes just like your inserts do it will
tell you if it's the indexes which slow you down or something else.
Also for insert heavy loads it's a good idea to put the xlog on a
separate disk (to double your disk bandwidth) unless you have a monster
disk setup.
During your INSERTs, do you also make some SELECTs ? Do you have triggers
on the table ? Foreign keys ? Anything ?
How much RAM you have ? And can you measure the size of the table+indexes
when it gets slow ?
>
>
> ----- Original Message ----
> From: James Mansion <james@mansionfamily.plus.com>
> To: andrew klassen <aptklassen@yahoo.com>
> Cc: pgsql-performance@postgresql.org
> Sent: Wednesday, June 4, 2008 3:20:26 PM
> Subject: Re: [PERFORM] insert/update tps slow with indices on table > 1M
> rows
>
> andrew klassen wrote:
>> I'll try adding more threads to update the table as you suggest.
> You could try materially increasing the update batch size too. As an
> exercise you could
> see what the performance of COPY is by backing out the data and
> reloading it from
> a suitable file.
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com, aptklassen@yahoo.com, james@mansionfamily.plus.com
Subject: Re: insert/update tps slow with indices on table > 1M rows
In-Reply-To: <op.ub8uxvcrcigqcu@apollo13.peufeu.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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