On Tue, Nov 05, 2002 at 08:18:57PM -0500, Murad Nayal wrote: > > > Hello, > > I am struggling trying to optimize the performance of mysql over rather > large databases. for now there is one issue I don't understand and I am > wondering if anybody can help with any hints. > > I am trying to create an index for a large table (100,000,000 rows). the > index is for a column of CHAR(4). The indexing operation has already > taken more than 10 hours and hasn't finished yet. The most perplexing > thing is that the the CPUs are mostly idle (90% idle!!). there is no > shortage of memory. the machine has 2Gigs of memory and over 1Gig is > available. it is not a disk access bottle neck either as osview > (equivalent to iostat on the IRIX) does not seem to report anything > suspicious. when I connect to the mysqld daemon using dbx it seems busy > coping tables. process list shows a state of "copy to tmp table". > > here are the mysqld options (I thought the buffers were fairly > generous!) > > /local/bin/safe_mysqld --user=mysql -O join_buffer_size=16M -O > key_buffer_size=128M -O record_buffer=4M -O record_rnd_buffer=4M -O > query_buffer_size=512k -O tmp_table_size=128M -O > myisam_sort_buffer_size=16M -O sort_buffer=8M -O table_cache=256 -O > thread_cache_size=40 --datadir=/echoes/databases/mysql > --safe-show-database --safe-user-create > > any idea why is it that the mysqld daemon is not using the CPUs and/or > why is this taking so long??
You might benefit from a larger key buffer. Can you show us the output of "vmstat 1" for 10 or 20 seconds? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 3.23.51: up 91 days, processed 1,906,162,793 queries (240/sec. avg) --------------------------------------------------------------------- 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
