Re: INSERT performace.

On Sunday 27 January 2002 20:31, Sean Chittenden wrote:
> > > 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.

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.

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

Regards,
Ferdinand