Okay, here's a tuning/opt problem for you experts:
I have several CD's worth of records that are being converted
and then inserted into our phone listing database. the total
db size will be about 22 million listings.
Well, it has reached 7 million and the insertions are getting
slower and slower, it's down to inserting only about 8-10 records per
second now,
on a dual-processor 800Mhz P3 with large hi-speed disks.
Yes, it is inserting indexes too, and I'm sure that's contributing to
the slowness.
Now here's the problem:
1. at this rate (25000 per hour) it will take another month to finish
loading the database. That's bad.
2. I have already tried the following speedup tips, and the total
speedup was insignificant:
a) use persistent connection to the database (this should have helped,
but apparently not much)
b) insert many records at a time using INSERT multiple value statements
(doing 100 at a time now,
it takes 10-25 seconds for each multiple insert!)
c) make a bigger key buffer (I checked, it's already 400 Mb)
d) reduce number of queries (I was doing 2 selects before each insert,
I stopped and used tables in memory)
After a, b, and d, I expected significant speedup, but didn't get it.
The database is running hard and
slow, so I assume the task is db-bound.
The next obvious speedup tip would be to use LOAD INFILE instead of
insert, but that may be hard for the
following reasons:
1. Putting all the records in a file would eat at least as much disk as
the db itself, I don't have enough for both
2. Putting part in a file and loading in chunks is possible, but tricky:
Remember the indexes need to be done someday too.
3. Last time I tried adding indexes after the data, it took days at a
stretch, with no intermediate progress meter!
Just to help with any ideas, here's the table layout and indices:
CREATE TABLE White (
rec_no int(10) unsigned NOT NULL auto_increment,
phone_no varchar(16) NOT NULL default '',
prefix smallint(5) unsigned zerofill NOT NULL default '00000',
title varchar(20) NOT NULL default '',
first_name varchar(50) NOT NULL default '',
last_name varchar(140) NOT NULL default '',
street_no varchar(20) NOT NULL default '',
street_type enum('','street', 'avenue','road', 'lane', 'rue', ...etc')
NOT NULL default '',
city varchar(70) NOT NULL default '',
postal_code varchar(5) NOT NULL default '',
listed tinyint(1) NOT NULL default '0',
hide tinyint(1) NOT NULL default '0',
PRIMARY KEY (rec_no),
KEY phone_no (phone_no),
KEY lca (last_name(12),city(12),street_name(12)),
KEY lcs (last_name(12),city(12),street_short(12)),
KEY lpa (last_name(12),province,street_name(12)),
KEY lps (last_name(12),province,street_short(12)),
KEY lcpa (last_name(12),city(12),province,street_name(12)),
KEY lcps (last_name(12),city(12),province,street_short(12)),
KEY lfca (last_name(12),first_name(12),city(12),street_name(12)),
KEY lfcs (last_name(12),first_name(12),city(12),street_short(12)),
KEY lfpa (last_name(12),first_name(12),province,street_name(12)),
KEY lfps (last_name(12),first_name(12),province,street_short(12)),
KEY lfcpa
(last_name(12),first_name(12),city(12),province,street_name(12)),
KEY lfcps
(last_name(12),first_name(12),city(12),province,street_short(12)),
KEY city (city),
KEY province (province),
KEY postal_code (postal_code)
) TYPE=MyISAM;
Thanks in advance for any ideas!
--
Steve Rapaport
World Citizen
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
