Re: Insert statements really slow

I have an application that I am porting from MySQL to PostgreSQL and I
am working on the import Perl script that process the data.

The data is web log data and each line has a variable amount of the
fields (mostly because of cookies) so I am using a lot of insert
statements.

In MySQL I go through a file in about 2 minutes and it is taking about
30 in PG. I have removed all but the primary key index and have done a
BEGIN and COMMIT after turning off AUTOCOMMIT.

What other things should I be looking at to spped this up?

Dave Waller

On Fri, Nov 09, 2007 at 11:53:08AM -0600, Waller, David wrote:
> The data is web log data and each line has a variable amount of the
> fields (mostly because of cookies) so I am using a lot of insert
> statements.
>
> In MySQL I go through a file in about 2 minutes and it is taking about
> 30 in PG. I have removed all but the primary key index and have done a
> BEGIN and COMMIT after turning off AUTOCOMMIT.
>
> What other things should I be looking at to spped this up?

It could be that your server is doing too much work because you don't have
enough WAL segments or something.

But I'd suggest looking at COPY for bulk imports. It's way faster.

A

--
Andrew Sullivan
Old sigs will return after re-constitution of blue smoke

On Nov 9, 2007 11:53 AM, Waller, David <Waller_D(at)imsdm(dot)com> wrote:
>
> In MySQL I go through a file in about 2 minutes and it is taking about 30 in
> PG. I have removed all but the primary key index and have done a BEGIN and
> COMMIT after turning off AUTOCOMMIT.

How many rows are you wrapping in begin/commit pairs? Hopefully a
good number, but just wondering.

On Nov 9, 2007 12:53 PM, Waller, David <Waller_D(at)imsdm(dot)com> wrote:
> I have an application that I am porting from MySQL to PostgreSQL and I am
> working on the import Perl script that process the data.
>
> The data is web log data and each line has a variable amount of the fields
> (mostly because of cookies) so I am using a lot of insert statements.
>
> In MySQL I go through a file in about 2 minutes and it is taking about 30 in
> PG. I have removed all but the primary key index and have done a BEGIN and
> COMMIT after turning off AUTOCOMMIT.

can you give us a better idea of the # records/sec we are talking
about here? mysql and pg are usually pretty close in insert
performance. maybe there is something fishy going on.

merlin

I have found some errors in my perl script that was slowing everything
down. I too am now seeing similar speed between postgresql and mysql.

Sorry for the confusion.

Dave

-----Original Message-----
From: Merlin Moncure [mailto:mmoncure(at)gmail(dot)com]
Sent: Tuesday, November 13, 2007 8:44 PM
To: Waller, David
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Insert statements really slow

On Nov 9, 2007 12:53 PM, Waller, David <Waller_D(at)imsdm(dot)com> wrote:
> I have an application that I am porting from MySQL to PostgreSQL and I

> am working on the import Perl script that process the data.
>
> The data is web log data and each line has a variable amount of the
> fields (mostly because of cookies) so I am using a lot of insert
statements.
>
> In MySQL I go through a file in about 2 minutes and it is taking about

> 30 in PG. I have removed all but the primary key index and have done
> a BEGIN and COMMIT after turning off AUTOCOMMIT.

can you give us a better idea of the # records/sec we are talking about
here? mysql and pg are usually pretty close in insert performance.
maybe there is something fishy going on.

merlin