From: Sergei Shelukhin <realgeek@gmail.com>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: concurrent inserts into two separate tables are very slow
Date: Tue, 08 Jan 2008 01:49:42 +0300
Message-ID: <4782AC86.20207@gmail.com> (raw)
In-Reply-To: <dcc563d10801071417qc0a5330hebbc390899ccb132@mail.gmail.com>
References: <b6c58ae4-18fd-4dfb-bc8c-8d39150e04f7@n20g2000hsh.googlegroups.com>
<dcc563d10801071417qc0a5330hebbc390899ccb132@mail.gmail.com>
Scott Marlowe wrote:
> On Jan 5, 2008 9:00 PM, Sergei Shelukhin <realgeek@gmail.com> wrote:
>
>> Hi. Running postgres 8.2 on debian.
>> I've noticed that concurrent inserts (archiving) of large batches data
>> into two completely unrelated tables are many times slower than the
>> same inserts done in sequence.
>> Is there any way to speed them up apart from buying faster HDs/
>> changing RAID configuration?
>>
>
> What method are you using to load these data? Got a short example
> that illustrates what you're doing?
>
>
The basic structure is as follows: there are several tables with
transaction data that is stored for one month only.
The data comes from several sources in different formats and is pushed
in using a custom script.
It gets the source data and puts it into a table it creates (import
table) with the same schema as the main table; then it deletes the month
old data from the main table; it also searches for duplicates in the
main table using some specific criteria and deletes them too (to make
use of indexes 2nd temp table is created with id int column and it's
populated with one insert ... select query with the transaction ids of
data duplicate in main and import tables, after that delete from pages
where id in (select id from 2nd-temp-table) is called). Then it inserts
the remainder of the imports table into the main table.
There are several data load processes that function in the same manner
with different target tables.
When they are running in sequence, they take about 20 minutes to
complete on average. If, however, they are running in parallel, they can
take up to 3 hours... I was wondering if it's solely the HD bottleneck
case, given that there's plenty of CPU and RAM available and postgres is
configured to use it.
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: realgeek@gmail.com, scott.marlowe@gmail.com
Subject: Re: concurrent inserts into two separate tables are very slow
In-Reply-To: <4782AC86.20207@gmail.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