matt
1) inserts using this format is much faster:
INSERT INTO table (col1, col2) VALUES (val1,val2), (val3,val4)
is much faster then single row insert. My experience is
2.5 hrs vs.. 36 hrs.
2) The PACK_KEYS=1 may be hurting you. I've never used it.
3) There may be a cache somewhere that's to small. You'll
have to do some digging in this area.
4) dup key ignore - what does that mean exactly?
5) what is your OS & rev, mysql rev.
Please post any suggestions that you find valuable so we
can all learn..
david
> -----Original Message-----
> From: matt ryan [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, June 30, 2004 11:51 AM
> To: [EMAIL PROTECTED]
> 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]
