Index creation

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=gcdmg-mysql-2@m.gmane.org

Re: Index creation

--0016e64ec436cd30ee04a6345452
Content-Type: text/plain; charset=ISO-8859-1

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

> 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@gmail.com
>
>


--
Claudio

--0016e64ec436cd30ee04a6345452--