List:General Discussion« Previous MessageNext Message »
From:Alex Schaft Date:June 21 2011 7:49am
Subject:Index creation
View as plain text  
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
Thread
Index creationAlex Schaft21 Jun
  • Re: Index creationClaudio Nanni21 Jun