Hi Alex, with MySQL 4.1.22 there is not much you can do.
MySQL alter tables recreating a new temporary one and swapping then afterwards. In my opinion the effort to speed up the process is not worth for just this alter table. Cheers Claudio 2011/6/21 Alex Schaft <al...@quicksoftware.co.za> > Hi, > > I'm busy creating an index on a 518505 record table on a single column > which is now taking about 2 hours on the copy to tmp table process > > The server is a 2gig ram Intel(R) Xeon(TM) CPU 3.00GHz running on a > hardware raid 5. The inno config was left as a standard install from > my-medium config. > > innodb_data_home_dir = /var/lib/mysql/ > innodb_data_file_path = ibdata1:10M:autoextend > innodb_log_group_home_dir = /var/lib/mysql/ > innodb_log_arch_dir = /var/lib/mysql/ > innodb_buffer_pool_size = 16M > innodb_additional_mem_pool_**size = 2M > innodb_log_file_size = 5M > innodb_log_buffer_size = 8M > innodb_flush_log_at_trx_commit = 1 > innodb_lock_wait_timeout = 50 > > The server is :mysql Ver 14.7 Distrib 4.1.22, for redhat-linux-gnu (i386) > using readline 4.3 > > Table stats are currently as follows: > > +---------------+--------+----**-----+------------+--------+--** > --------------+-------------+-**----------------+-------------** > -+-----------+----------------**+---------------------+-------** > ------+------------+----------**---------+----------+---------** > -------+----------------------**+ > | Name | Engine | Version | Row_format | Rows | Avg_row_length | > Data_length | Max_data_length | Index_length | Data_free | Auto_increment | > Create_time | Update_time | Check_time | Collation | > Checksum | Create_options | Comment | > +---------------+--------+----**-----+------------+--------+--** > --------------+-------------+-**----------------+-------------** > -+-----------+----------------**+---------------------+-------** > ------+------------+----------**---------+----------+---------** > -------+----------------------**+ > | wininv_invdet | InnoDB | 9 | Fixed | 518526 | 1824 | > 945815552 | NULL | 1144487936 | 0 | 518506 | > 2011-06-21 07:36:20 | NULL | NULL | latin1_swedish_ci | > NULL | | InnoDB free: 9216 kB | > +---------------+--------+----**-----+------------+--------+--** > --------------+-------------+-**----------------+-------------** > -+-----------+----------------**+---------------------+-------** > ------+------------+----------**---------+----------+---------** > -------+----------------------**+ > > Innodb status is: > > ==============================**======= > 110621 9:47:04 INNODB MONITOR OUTPUT > ==============================**======= > Per second averages calculated from the last 31 seconds > ---------- > SEMAPHORES > ---------- > OS WAIT ARRAY INFO: reservation count 1187081, signal count 1182987 > Mutex spin waits 4624590, rounds 17483575, OS waits 350615 > RW-shared spins 138728, OS waits 66949; RW-excl spins 833217, OS waits > 690480 > ------------ > TRANSACTIONS > ------------ > Trx id counter 0 4252 > Purge done for trx's n:o < 0 4198 undo n:o < 0 0 > History list length 0 > Total number of lock structs in row lock hash table 255 > LIST OF TRANSACTIONS FOR EACH SESSION: > ---TRANSACTION 0 0, not started, process no 16841, OS thread id 2954886048 > MySQL thread id 3186, query id 3047513 localhost root > show engine innodb status > ---TRANSACTION 0 4251, ACTIVE 62 sec, process no 16841, OS thread id > 2957421472 inserting, thread declared inside InnoDB 160 > mysql tables in use 2, locked 3 > 258 lock struct(s), heap size 27968, undo log entries 2285 > MySQL thread id 2, query id 3041739 pc-00030.quicksoftware.co.za 10.1.1.30 > root copy to tmp table > CREATE INDEX WININV_INVDET_SUPREF3 ON `wininv_invdet` (`indkey_004`) /* > Create synthetic Index */ > -------- > FILE I/O > -------- > I/O thread 0 state: waiting for i/o request (insert buffer thread) > I/O thread 1 state: waiting for i/o request (log thread) > I/O thread 2 state: waiting for i/o request (read thread) > I/O thread 3 state: waiting for i/o request (write thread) > Pending normal aio reads: 0, aio writes: 0, > ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 > Pending flushes (fsync) log: 1; buffer pool: 1 > 5028159 OS file reads, 4867919 OS file writes, 272069 OS fsyncs > 105.58 reads/s, 17525 avg bytes/read, 84.48 writes/s, 3.87 fsyncs/s > ------------------------------**------- > INSERT BUFFER AND ADAPTIVE HASH INDEX > ------------------------------**------- > Ibuf for space 0: size 519, free list len 271, seg size 791, is not empty > Ibuf for space 0: size 519, free list len 271, seg size 791, > 13085268 inserts, 13064005 merged recs, 1775632 merges > Hash table size 69257, used cells 63, node heap has 1 buffer(s) > 181.74 hash searches/s, 737.07 non-hash searches/s > --- > LOG > --- > Log sequence number 6 1970388696 > Log flushed up to 6 1970162325 > Last checkpoint at 6 1963765307 > 1 pending log writes, 0 pending chkp writes > 108282 log i/o's done, 1.68 log i/o's/second > ---------------------- > BUFFER POOL AND MEMORY > ---------------------- > Total memory allocated 35308974; in additional pool allocated 2095872 > Buffer pool size 1024 > Free buffers 0 > Database pages 1022 > Modified db pages 557 > Pending reads 0 > Pending writes: LRU 0, flush list 0, single page 0 > Pages read 6249386, created 1640028, written 7658612 > 112.93 reads/s, 9.87 creates/s, 109.77 writes/s > Buffer pool hit rate 973 / 1000 > -------------- > ROW OPERATIONS > -------------- > 1 queries inside InnoDB, 0 queries in queue > Main thread process no. 16841, id 2976910240, state: flushing log > Number of rows inserted 10916769, updated 2438114, deleted 0, read 15124668 > 37.22 inserts/s, 0.00 updates/s, 0.00 deletes/s, 37.22 reads/s > > I'm fairly new at mysql use and optimization, but I upped the buffer_pool > size already, but I don't think it affects the current query. > > What can I generally do to actually make this perform? > > Thanks, > Alex > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?** > unsub=claudio.na...@gmail.com<http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com> > > -- Claudio
