List:General Discussion« Previous MessageNext Message »
From:Aaron Holtz Date:April 9 2000 3:08am
Subject:Re: MySQL/PostgreSQL Speed issues
View as plain text  
Do you have indexes on these tables?  Having indexes setup on a table can
slow down monster inserts from my experience.  My suggestion would be to
drop the indexes, import the data and then rebuild the indexes.  Not sure
how feasible that is in your situation, but that is what I would do.  I've
just got done importing several db's with over 7 million rows in them and
the whole process of importing and building 2 indexes took under 2 hours
on a dual PIII 500 linux setup.  

--------------------------------------------------------------------------
Aaron Holtz
ComNet Inc.
UNIX Systems Administration/Network Operations
"It's not broken, it just lacks duct tape."
--------------------------------------------------------------------------

On Apr 8, Dave molded the electrons to say....

>Hi all,
>
>I am just looking for some tips/advice on how to deal with speed issues of
>an INSERT (I will deal with UPDATE later).
>
>I have lots of data (about 400,000 rows of 60 fields) and use PHP to parse
>the data, build an INSERT statement and dump it into MySQL. This method
>takes about 4 hours to parse all the data and insert it on MySQL. I have
>also tried to output the INSERT statement for each record into a file and
>pipe it into mysql via bash#mysql db < data.sql and it still runs around 4
>hours. The platform this is being done on is BSDI 4.01, Pentium 200MMX with
>64MB RAM and a quick EIDE drive.
>
>I have tried the same as above on Linux RH 6.1, Pentium 200MMX/64MB EIDE on
>PostgreSQL and after almost 2 hours elapsed I only had about 45,000 rows
>inserted.
>
>What I am wondering is...is this right? Is there a more efficient way to
>insert data into MySQL/PostgreSQL than the way I am doing it? This data will
>be updated monthly and possibly weekly and will contain over a million rows
>each update. I haven't even tried UPDATE yet to see how that is going to
>perform...but with these INSERT's taking this long I can guess that an
>UPDATE will not be speedy either.
>
>My INSERT is similar to the following:
>
>INSERT into table (field1, field2, field3....) values (data1, data2,
>data3...)
>
>nothing fancy, nothing wierd...
>
>
>Anyone got any tips for speeding up the INSERT's and/or how to address
>UPDATE's? When the table needs to be updated the data I receive will be
>every existing record (with or without changes) plus additions and
>deletions.
>
>Any help is greatly appreciated...
>
>-Dave
>
>
>
>-- 
>---------------------------------------------------------------------
>Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
>posting. To request this thread, e-mail mysql-thread33660@stripped
>
>To unsubscribe, send a message to:
>    <mysql-unsubscribe-aholtz=bright.net@stripped>
>

Thread
MySQL/PostgreSQL Speed issuesDave9 Apr
  • Re: MySQL/PostgreSQL Speed issuesAaron Holtz9 Apr
  • Re: MySQL/PostgreSQL Speed issuesTonu Samuel9 Apr
    • Re: MySQL/PostgreSQL Speed issuesTim Bunce10 Apr
  • Re: MySQL/PostgreSQL Speed issuesWesley Darlington9 Apr
  • Re: MySQL/PostgreSQL Speed issuesDave10 Apr