Re: INSERT performace.

> > > > We have a fresh database and have begun to observe performance
> > > > degradation for INSERTs as a table went from empty to
> > > > 100,000-ish rows. Initial INSERTs were sub second while after
> > > > 30k rows, they were 1-3 seconds.
> > >
> > > we just hit this problem when moving old database to new one
> > > (new schema). we had to insert approx. 1.5 million rows, and
> > > from initial 0.02s/insert after several thousands of inserts it
> > > came to 20 seconds per insert. what we did was removing foreign
> > > keys from table which we made inserts to. it helped. we manage
> > > to put 300k records in ca. 2-3 hours.
> >
> > If possible, use the COPY command. We did 90K rows in about 40sec
> > using this puppy on a Solaris U5 (took over 130sec for MySQL on
> > the same box in case the performance geeks in the crowd are
> > interested).
>
> We were transfering a mysql-database to a new linux-server (PIII-800
> dual). I don't now how mutch rows, but the dump was 8 Gb (not
> zipped). It took us 4 hours to import, and 5 hours to create the
> indexes.

How were you inserting the data? Were you doing multiple inserts per
transactions? Copy? That sounds really slow to me.

> By testing we created a postgres database to on an other server
> (same type). The copy command did'nt work, because of 'strange
> characters', so we used normal inserts. It took us 12 hours to
> import, and 10 hours to create the indexes.

Have you tried to escape the data before you inserted it? That
should've solve things.

http://www.postgresql.org/idocs/index.php?sql-copy.html

> Although, i like postgres more, mysql is still faster with hugh
> (simple) data.

I've never found that to be the case in only a few instances
actually... and typically with small data sets that are less than 1M
rows. vacuum analyze and turn fsync off. :~) -sc

--
Sean Chittenden