Using an Apache/PHP/MySQL/Linux (Redhat 8.0) solution, PHPList, to create an e-mailing list for our 5.6 million book club members. Unfortunately, the import speed for importing records (at record number 150,000) is about 2,000 records per hour. We're running on the following:
P4 (1.5 Ghz), 1.2 Gbytes RAM (650 Mbytes RAM Disk using ramfs), IDE drive (7,200 rpm)
So far we've moved the MySQL data files (var/lib/mysql), PHP /tmp and upload directories and PHPlist web site files to RAM Disk (still just testing - not yet dealing with data safety issues). With all of this tuning we're still at only 2,000 records per hour for uploading.
We need to be at 100,000 records per hour (uploading and sending seem to run at about the same rate - we need to be able to send to all book club members in the same week). Any suggestions?
Creigh
(We're planning to run the system on a server with dual Opterons, 8 Gbytes RAM and RAID-5 SCSI drives, but I don't think the additional system horsepower will solve our problem.)
What format is the data in that you are importing? Are you using MyISAM data files and not InnoDb?
I've imported CSV data at around 1.5-2.5 million rows per minute using Load Data InFile (2.4ghz on Win2k). The problem with this command is the data being imported must be valid otherwise the row will be rejected and you will have a tough time finding out which row it was. MySQL Cookbook by Paul Dubois has some ideas on how to use Load Data InFile more reliably.
Even with Insert statements I'm getting around 1000 rows per seconds.
You can try inserting more than one row with a single Insert statement. This should speed things up some.
Or use transactions around 50-200 rows. (You will need to see how many rows/transaction gives you the best results)
Disable the keys (if possble while inserting) See DISABLE KEYS
Lock the table if possible while inserting the rows.
This should get you started. Paul Dubois has a section in his other book MySQL 2nd Edition on page 279.
Mike
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
