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=arch...@jab.org
