So slow inserts... Why?

As i dont know anything about triggers:)
Have you done a vacuum on the databases to clear out old transactions?

/Johan

Michal Samek <webmaster@tony.cz> on 99-05-26 10:47:43

To: pgsql-general@postgreSQL.org
cc: (bcc: Johan Granlund/Solid AB)
Subject: [GENERAL] So slow inserts... Why?

Hi. At first sorry of my English :) I'm using Postgresql in the music
e-shop; the main table kat (katalogue of CDs and MCs) is filled from
.dbf files by a php script. And it's just very, very slow - about 1
inserted record per sec.

Table kat has one primary index and three another indexes, and it's
triggered both on insert and update by plpgsql function to track updates
/ inserts to mark rows which needs updating to production database on
internet. It's no php slowing the process, only postgresql backend loads
processor very much (about 50% on Celeron 266 with 64 MB ram). So I can
understand that it's a time-consuming to insert row, run the trigger
(which is only several lines of the code) and update indicies, but I
thing it's reallt SLOWER THAN IT SHOULD BE.

I've tried MySQL doing the same job, of course without trigger, but it
was TERRIBLY QUICK, for the same job about one minute to process with
Mysql - with postgresql it lasted about 45 min. I do now that Mysql is
considered rather quick, but so much difference? I can't believe that
postgresql is so slow, rather I'm thinking about how to optimize my job.

So, if you are smart enough and want to help me, please, give me some
information about how to affect the speed of inserting. I dno't know,
maybe is better to drop non-primary key indexes, to do inserts and then
re-create indexes? Or to forget using triggers or optimize the backend
some way? Of course I can provide you with details about my querries and
data structures, but I think I'm not doing any special job.

And, postgresql is 6.4.2.

Many thanks for any ideas.
--
Michal Samek, Tony distribuce s.r.o.
webmaster@tony.cz (++420659/321350)
ICQ: 38607210

johan.granlund@solid.se wrote:

As i dont know anything about triggers:)
Have you done a vacuum on the databases to clear out old transactions?

I recreated the table, so I think there is not necessary to vacuum it
(it takes unbelieveable amount of time); maybe when I'm filling the
table by sending a lot of INSERT queries I should sometimes between
inserts do a vacuum ? It looks strange for me.

And many thanks for your help; I was thinking about dropping indexes and
trigger, but It's impossible. Imagine this is not just a series of
insert queries, the process I'm using is (simplicied): For each dbase
record (the catalogue from the music label) to look-up if exist and if
so, update; if no, insert. So I can't drop indexes which are needed by
look-up function; nor can't drop trigger because it tracks inserts /
updates of my table.
I'm testing to group inserts / updates to blocks enclosed with
transaction BEGIN; END;, but it now looks that it will help only little.

PS I plan to rewrite the whole thing to Mysql (even it's no triggers, it
will be harder but possible of course) and to report the result here if
will be interesting)

--
Michal Samek, Tony distribuce s.r.o.
webmaster@tony.cz (++420659/321350)
ICQ: 38607210

PS I plan to rewrite the whole thing to Mysql (even it's no triggers, it
will be harder but possible of course) and to report the result here if
will be interesting)

Why don't you at the same time try the approach you're going to use for
MySQL ona PostgreSQL system? It's kinda unfair to use triggers with PostgreSQL,
no triggers with MySQL, and then say that MySQL is faster (althought it
very likely is).

Maarten

--

Maarten Boekhold, boekhold@tibco.com
TIBCO Finance Technology Inc.
The Atrium
Strawinskylaan 3051
1077 ZX Amsterdam, The Netherlands
tel: +31 20 3012158, fax: +31 20 3012358
http://www.tibco.com

I removed your first mail, but if i remembered it right you was using PHP.
I have seen on my server (dual P5/100) that the "startup" cost of the
backend is rather high.
If you is using PHP and not using persistent connection (or whatever it was
called) you may have a lot of
start/stop of backends.

Whatewer the reason is, 1 transaction/sec is awfully slow. Something must
be wrong.
What other things is running on this machine. Is it highly loaded?

/Johan

Michal Samek <webmaster@tony.cz> on 99-05-26 16:15:10

To: Johan Granlund/Solid AB
cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] So slow inserts... Why?

johan.granlund@solid.se wrote:

As i dont know anything about triggers:)
Have you done a vacuum on the databases to clear out old transactions?

I recreated the table, so I think there is not necessary to vacuum it
(it takes unbelieveable amount of time); maybe when I'm filling the
table by sending a lot of INSERT queries I should sometimes between
inserts do a vacuum ? It looks strange for me.

And many thanks for your help; I was thinking about dropping indexes and
trigger, but It's impossible. Imagine this is not just a series of
insert queries, the process I'm using is (simplicied): For each dbase
record (the catalogue from the music label) to look-up if exist and if
so, update; if no, insert. So I can't drop indexes which are needed by
look-up function; nor can't drop trigger because it tracks inserts /
updates of my table.
I'm testing to group inserts / updates to blocks enclosed with
transaction BEGIN; END;, but it now looks that it will help only little.

PS I plan to rewrite the whole thing to Mysql (even it's no triggers, it
will be harder but possible of course) and to report the result here if
will be interesting)

--
Michal Samek, Tony distribuce s.r.o.
webmaster@tony.cz (++420659/321350)
ICQ: 38607210

Try using transactions. I found when doing single inserts they could take
hours, but when grouping all the inserts into a single transaction, it was
relatively quick. It's been a while, but I believe the syntax is:
BEGIN
insert ...
insert ...
insert ...
COMMIT;

At 11:15 AM 5/26/99, Michal Samek wrote:

1) Are you re-connecting for each insert?
a) through TCP/IP for each insert?
yes - If you can connect via unix sockets it will speed up your
connection time
yes - Why would you be doing that? Since in an earlier message you said
the your placing BEGIN and END around the insert to do them in one
transaction I'll assume you're smarter than that.
no - Good, that would be insane
2) Are you sending -F to the backend at startup (this is done through the
postmaster startup with the -o param.)
no - Then the backend is fsync'ing between each insert. Which means you
are disk bound buddy. If you are using a SCSI-raid then I'm wrong, if you
are using IDE then you get what you pay for.
yes - Good the only reason I've found not to include that option is if you
don't trust your OS to properly buffer files for you, or not to crash once a
day. (And if that's the case you should stop using Windows ASAP - just a
joke)
3) What plans do your queries give you? (the ones in the triggers).
you should remember that those triggers will be called for each row
inserted or updated.
4) also remember to use TRANSACTION as much as possible with your
inserts/updates for the same reasons outlined in 2 above.

-DEJ

Try using transactions. I found when doing single inserts they could take
hours, but when grouping all the inserts into a single transaction, it was
relatively quick. It's been a while, but I believe the syntax is:
BEGIN
insert ...
insert ...
insert ...
COMMIT;

Btw. I want to ask about how much "inserts" can I insert between BEGIN and
END; When I do it last time ( I want to add about 1000 "inserts") - I get
error - that buffer can contain to 20 000 characters.
I use psql, so I'm not sure if this is a limit of psql itself or limit of
backend.
And may be anybody knows if it is possible change this limit and where?
TIA
Rem

-------------------------------------------------------------------*------------
Remigiusz Sokolowski e-mail: rems@gdansk.sprint.pl * *
-----------------------------------------------------------------*****----------

First of all many thanks to all people trying to help me. Now I'm
testing my job with the postgresql in various conditions, with applying
interesting suggestions from you, and I'm getting crazy a bit :) I must
say that it's not so clear to leave the dBase world and start the SQL
Master :) What about writting some doc of optimizing postgresql more
explaining the behaviour of the backend? (Maybe I've missed some part of
docs but I don't think so.)

Now I know that my problem is more SELECT's cost when lookuping than
INSERT's cost. I want to say what I can see that it helped me the best:
About TWO-THREE
times QUICKER is my job after :

1. Dropping all indexes (included theese like table_record_key and
table_pkey (if you have the primary key) ) - I must say that I was a bit
surprised that it's possible to drop these "system-level" indexes. How
it would affect on primary uniq key when I drop it's index? Will it
still be uniq, or not?

2. VACUUMing the table - it's really short and I think this should be
documented, that it's much better than VACUUM performed on indexed
table. Before you explained me it I thought that VACUUM on indexed table
should take the time of VACUUM on non-indexed table plus the time of
indexes creating :)

3. Recreating all the indexes included these two which you created
together with table (I'm not sure if it's right):
create unique index table_pkey on table(primary_key_name) ;
create unique index table_record_key on table(oid) ;

And for me it's a bit surprising, that something like "index quallity"
is decreasing with INSERT's. Isn't it ?

--
Michal Samek, Tony distribuce s.r.o.
webmaster@tony.cz (++420659/321350)
ICQ: 38607210

Remigiusz Sokolowski wrote:

Try using transactions. I found when doing single inserts they could take
hours, but when grouping all the inserts into a single transaction, it was
relatively quick. It's been a while, but I believe the syntax is:
BEGIN
insert ...
insert ...
insert ...
COMMIT;

Btw. I want to ask about how much "inserts" can I insert between BEGIN and

2^32 - 2 -:)

END; When I do it last time ( I want to add about 1000 "inserts") - I get
error - that buffer can contain to 20 000 characters.
I use psql, so I'm not sure if this is a limit of psql itself or limit of
backend.
And may be anybody knows if it is possible change this limit and where?

You have to add ; after each insert...

Vadim