I know it's naff but I've found it quicker to use "myisamchk" with row-sort than it is to get the MySQL daemon to regenerate keys (and if you know you're not changing the data file you can tell myisamchk not to copy the data), unless I've missed something in the MySQL config ...
The only way I know to do this for PRIMARY KEYs is a process described by Kyle J. Munn (but watch out for free space :-): http://lists.mysql.com/mysql/158737 This boils down to moving the MYD file, truncating the table to create an empty MYD, modify your key(s), move the MYD back, and repair using myisamchk to re-create the keys. Otherwise you can use ALTER TABLE <table> DISABLE KEYS/ENABLE KEYS to disable keys while you make your inserts, then enable keys to update non-Primary keys in one go. I've never tried PACK_KEYS so can't suggest anything on that, other than it sounds like it adds more data to your index which is probably a bad thing with such short values. You may find that the 'dic' KEY isn't necessary, as it's the first part of your PRIMARY KEY. Depending on your data you may be better off storing integer IDs and the char values in a look-up table. I've found better performance for multi-column keys by putting the columns in order of least variance first, e.g. for a list of dates: 1979-04-23 1979-07-15 1980-02-04 1980-06-04 You want a key on (YEAR-MONTH-DAY) If you can you could put the index/data on different disks - not sure how you would do that in Windows (INDEX DIRECTORY option for CREATE TABLE?). You should definitely put the binary log file on another disk, but again not something I've used. I've found MySQL to be a royal pain working with multi-GB tables (my biggest is 12GB+13GB index). I've learnt that MySQL is a bit like a confused puppy - it doesn't know why it wet the floor, but it expects you to know that pained expression means you need to move it somewhere else ... All the best, Tim. ----- Original Message ----- From: "matt ryan" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, June 30, 2004 7:50 PM Subject: Mysql growing pains, 4 days to create index on one table! > Rebuilding index takes 3 1/2 days!!! Growing pains with mysql.. > > I've got 2 dedicated servers, each with a slave, all run 32gig 15k rpm > raid 5 on u320 perc raid cards, dell 2600/4600's with single channel > backplanes (new ones will have dual channel) > > All have 2 gig of ram, but I've never seen mysql use more than 600mb > of ram. > > The servers handle huge loads, each day there are 30 1-2 gig files > loaded into large tables, total mysql data size is 96 gig, the large > tables are 2-6 gig. > > Inserts are done on dupe key ignore, this takes hours on the large > files, it barely keeps up with input files. > > At the bottom of this post I've got the mysql.ini config lines, any > suggestions are welcome, I'm already beyond the mysql "huge" sample > they used to include in the program. > > Sample table that I load is as follows. > > each day I get 40 % new records on the text file, the input file is > normally 20mb, once a week I get one that's 1-2 gig, these take all > day to load. > > I need more multiple column indexes, as some querys return millions of > rows that must be scanned, but the index size already exceeds the > table size, and the combinations I need would result in an myi that's > 5x larger than the data itself. > > Here's an example of the speed problem, the index was corrupt so I > dropped all and recreated, rather than a myisam repair. I think 3 days > is a little excessive for a table that's only 3.428 gig, index is > 2.729 gig. I cant remove the primary key, as it keeps duplicates out > of the system, the input files are from old database's, we use mysql > to store the data for the web frontend, mostly done in ASP, most > queries take less than a second, unforuntatly we have big queries that > take way more than the IIS timeout setting all the time, but no way > around it, I cant add more indexes without making it even slower :( > > > > I cant tell if it's mysql that's the problem, or the hardware, Here's a > screenshot of the disk IO, if I copy a file while mysql is doing the > build index, the io shoots way up, which tells me, mysql is NOT maxing > out the drives, and it's also not maxing out the memory. > > Unless it's doing lots and lots of seeks on the drive, which is harder > to test using perfmon, are there any mysql test setups that would help > identify where the bottleneck is? > > > screenshot of disk io usage > > http://www.geekopolis.com/pics/diskio.jpg > > I'm all out of ideas, other than switching to another db, and the table > & indexes split across drives, maybe a 2 channel setup, 4 drives per > channel, each 4 is a separate raid 5 setup, one holds data one holds > indexes, cant do this with mysql though > > mysql> alter table hood_stat add primary key > (dic,niin,fr_ric,don,suf,dte_txn,sta) , add index `don` (`don`), add > index `niin` (`niin`), add index `stor` (`stor`), add index `dic` > (`dic`), add index `ctasc` (`ctasc`); > > Query OK, 45449534 rows affected (3 days 19 hours 6 min 34.94 seconds > Records: 45449534 Duplicates: 0 Warnings: 0 > > > CREATE TABLE `hood_stat` ( > `dic` char(3) NOT NULL default '', > `fr_ric` char(3) NOT NULL default '', > `niin` char(11) NOT NULL default '', > `ui` char(2) NOT NULL default '', > `qty` char(5) NOT NULL default '', > `don` char(14) NOT NULL default '', > `suf` char(1) NOT NULL default '', > `dte_txn` char(5) NOT NULL default '', > `ship_to` char(3) NOT NULL default '', > `sta` char(2) NOT NULL default '', > `lst_sos` char(3) NOT NULL default '', > `esd` char(4) NOT NULL default '', > `stor` char(3) NOT NULL default '', > `d_t` char(4) NOT NULL default '', > `ctasc` char(10) NOT NULL default '', > PRIMARY KEY (`dic`,`niin`,`fr_ric`,`don`,`suf`,`dte_txn`,`sta` ), > KEY `don` (`don`), > KEY `niin` (`niin`), > KEY `stor` (`stor`), > KEY `dic` (`dic`), > KEY `ctasc` (`ctasc`) > ) TYPE=MyISAM MAX_ROWS=1000000000 PACK_KEYS=1 > > skip-locking > set-variable=delay_key_write=ALL > set-variable= key_buffer_size=1500M > set-variable=join_buffer=512M > set-variable= max_allowed_packet=256M > set-variable= table_cache=512 > set-variable= sort_buffer=256M > set-variable=tmp_table_size=400M > set-variable= record_buffer=512M > set-variable= thread_cache=8 > set-variable=myisam_sort_buffer_size=256M > myisam-recover=BACKUP,FORCE > set-variable=read_buffer_size=512M > set-variable=interactive_timeout=7200 > set-variable=wait_timeout=7200 > log-bin > server-id=1 > replicate-do-db=finlog > set-variable=open-files-limit=500 > set-variable=table-cache=400 > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
