Speeding up series of complex INSERTS
KEVIN ZEMBOWER <[email protected]>
| Newsgroups | gmane.comp.db.mysql.perl |
|---|---|
| Message-ID | <[email protected]> |
This is my first experience working with a large database. I'm trying to insert over 320,000 record of a bibliographic database of medical articles. If anyone would like to test my system, the program is available at http://www.jhuccp.org/temp/KZ/pop2sql.pl, the SQL statements to set up the database are at http://www.jhuccp.org/temp/KZ/popline.sql and a file consisting of a header record and 1000 data records is at http://www.jhuccp..org/temp/KZ/popline.dmp.gz and must be decompressed before use. The program is run by specifying the name of the data file to be processed on the command line, such as "./pop2sql.pl popline.dmp". The only unusual module used by the program is Text::xSV. When testing this program with 1000 data records, I was processing about 570 records per minute. This would have processed all 320,000 records in about 9 hours, which was acceptable to me. However, I started it running yesterday at about 15:40 and it's still running now, almost 20 hours later. Right now, this is the condition of the database: Database popline running on localhost Table Records Type Size abstract 220,038 MyISAM 517.9 MB abstractword 0 MyISAM 1.0 KB abstractword_article 0 MyISAM 1.0 KB article 243,817 MyISAM 66.5 MB author 164,213 MyISAM 7.2 MB author_article 418,227 MyISAM 3.6 MB clientgroup 0 MyISAM 1.0 KB community 0 MyISAM 1.0 KB corpname 2,750 MyISAM 376.7 KB corpname_article 20,477 MyISAM 181.0 KB country 0 MyISAM 1.0 KB findingstype 0 MyISAM 1.0 KB findingstype_article 0 MyISAM 1.0 KB journal 45,032 MyISAM 5.2 MB journal_article 149,919 MyISAM 1.3 MB keyword 2,109 MyISAM 73.8 KB keyword_article 2,393,673 MyISAM 20.5 MB languguage 3 MyISAM 4.1 KB popreporttopic 0 MyISAM 1.0 KB popreporttopic_article 0 MyISAM 1.0 KB 20 table(s) 3,660,258 -- 622.8 MB As you can see, only 243,817 articles (records) have been inserted. It's currently inserting records at the rate of 104 records per minute. At this rate, it'll take another 13 hours to complete this run. I read the article in the reference manual at 7.2.14 on the Speed of INSERT Statements. I only use INSERT and SELECT statements. I don't know which of these techniques I can apply to my program. I don't think I can insert multiple rows at a time; I need to process the input file one row at a time. Would INSERT DELAYED help here? Would it be worthwhile to rewrite my program completely to generate a series of input files that I could use LOAD DATA INFILE with? I'm guessing that the most probable speed-up might occur if I disable the indexes, load the data, then recreate the indexes with myisamchk (I can't use the DISABLE/ENABLE KEYS of MySQL 4.0 as I'm running 3.23.49). Would it be worthwhile to rewrite my program to use more than one thread at a time? Any suggestions on what I can try? Any advice on diagnostics I can run to determine the bottleneck? Thanks for all your help and suggestions on my problem. -Kevin Zembower ----- E. Kevin Zembower Internet Systems Group manager Johns Hopkins University Bloomberg School of Public Health Center for Communications Programs 111 Market Place, Suite 310 Baltimore, MD 21202 410-659-6139 -- MySQL Perl Mailing List For list archives: http://lists.mysql.com/perl To unsubscribe: http://lists.mysql.com/[email protected]