Re: INSERT performace.

Поиск
Список
Период
Сортировка
От Sean Chittenden
Тема Re: INSERT performace.
Дата
Msg-id 20020129225513.A39839@ninja1.internal
обсуждение исходный текст
Ответ на Re: INSERT performace.  (Ferdinand Smit <ferdinand@telegraafnet.nl>)
Ответы Re: INSERT performace.  (Ferdinand Smit <ferdinand@telegraafnet.nl>)
Список pgsql-admin
> > > > 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

В списке pgsql-admin по дате отправления:

Предыдущее
От: Andy Ruhl
Дата:
Сообщение: Re: Sample database for me to practice with?
Следующее
От: "Martins Zarins"
Дата:
Сообщение: tags in HTML formated output