List:General Discussion« Previous MessageNext Message »
From:Claudio Nanni Date:June 21 2011 8:06am
Subject:Re: Index creation
View as plain text  
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 <alexs@stripped>

> 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.nanni@stripped<http://lists.mysql.com/mysql?unsub=1
>
>


-- 
Claudio

Thread
Index creationAlex Schaft21 Jun
  • Re: Index creationClaudio Nanni21 Jun