Hi list,
Im having problems with bulk writes (restores from mysqldumps, alters,
delete in (select ...)) with innodb. The servers are at amazon EC2 instances
w/ 15G ram and raid0 4disks EBS.
The problem starts when I run bulk writes like an alter table or a restore
from mysqldump, its starts processing more than 50000 rows/s but suddenly
the ratio goes down to 100 rows /sec. and then its stucked at this ratio
even if I restart MySQL. The only way to get good perfomance again is
deleting all innodb files (ibdata, iblog files) and restoring the DB again.
The DBs are relative small about 70M rows and 10Gb size. I can repeat this
behavior all the time just running 2 restores of the same database.
Another example when its stucked:
I want to delete 1M rows
"delete from table where id IN (select id from ....)" deletes 100 rows /
sec
but if I run 1 Million "delete from table where id = xxx" deletes 10000 rows
/ sec
The problem is just only with writes on innodb, I can perfectly run
mysqldumps and bulk inserts on MyISAM.
This is happening with all MySQL 5.1.x versions I tested.
Any one have a clue about this issue??
Thanks in advance
Max
########################
# INNODB Settings #
########################
innodb_file_per_table
innodb_buffer_pool_size = 10G
innodb_additional_mem_pool_size = 20M
innodb_thread_concurrency = 8
innodb_support_xa = 0
innodb_thread_sleep_delay = 2000
innodb_flush_log_at_trx_commit = 0
innodb_log_file_size = 700M
innodb_log_buffer_size = 8M
innodb_lock_wait_timeout = 50
innodb_max_purge_lag = 10
innodb_max_dirty_pages_pct = 90
innodb_use_purge_thread = 4
innodb_extra_undoslots = 1
innodb_adaptive_checkpoint = estimate
innodb_io_capacity = 500
innodb_read_io_threads = 4
innodb_write_io_threads = 4
and this is a innodb status when was running at low preformance
mysql> show engine innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
100310 13:12:07 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 42 seconds
----------
BACKGROUND THREAD
----------
srv_master_thread loops: 4409 1_second, 4408 sleeps, 439 10_second, 399
background, 399 flush
srv_master_thread log flush and writes: 4925
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 37498, signal count 37467
Mutex spin waits 115051, rounds 948698, OS waits 24706
RW-shared spins 1458, OS waits 778; RW-excl spins 0, OS waits 11943
Spin rounds per wait: 8.25 mutex, 17.61 RW-shared, 358898.00 RW-excl
--------
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 (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 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: 0; buffer pool: 0
70 OS file reads, 367420 OS file writes, 113414 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 13.12 writes/s, 2.02 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 25499809, node heap has 5416 buffer(s)
98.74 hash searches/s, 1.43 non-hash searches/s
---
LOG
---
Log sequence number 34171430524
Log flushed up to 34171425746
Last checkpoint at 33506703349
Max checkpoint age 1187902219
Checkpoint age target 1150780275
Modified age 664727175
Checkpoint age 664727175
0 pending log writes, 0 pending chkp writes
37839 log i/o's done, 1.05 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 13205766144; in additional pool allocated 0
Internal hash tables (constant factor + variable factor)
Adaptive hash index 292738424 (203998472 + 88739952)
Page hash 12750664
Dictionary cache 51124680 (51001072 + 123608)
File system 90728 (82672 + 8056)
Lock system 31876248 (31875512 + 736)
Recovery system 0 (0 + 0)
Threads 407416 (406936 + 480)
Dictionary memory allocated 123608
Buffer pool size 786431
Buffer pool size, bytes 12884885504
Free buffers 1
Database pages 781014
Old database pages 288283
Modified db pages 36334
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 190597, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 70, created 1620259, written 4087613
0.00 reads/s, 0.29 creates/s, 45.36 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s
LRU len: 781014, unzip_LRU len: 0
I/O sum[2109]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 15552, id 1182845248, state: sleeping
Number of rows inserted 270830867, updated 0, deleted 0, read 0
99.59 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
------------
TRANSACTIONS
------------
Trx id counter 43DF
Purge done for trx's n:o < 43D2 undo n:o < 0
History list length 11
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started, process no 15552, OS thread id 1192302912
MySQL thread id 9, query id 16776 localhost root
show engine innodb status
---TRANSACTION 43DE, ACTIVE 156 sec, process no 15552, OS thread id
1192036672 inserting, thread declared inside InnoDB 17
mysql tables in use 1, locked 1
1 lock struct(s), heap size 368, 0 row lock(s), undo log entries 15513
MySQL thread id 8, query id 16774 localhost root update
INSERT INTO `t1` VALUES
(136032,'xxx',213),(136032,'xxx',86),(136032,'xxx',193),(136032,'xxx',24),(136032,'xxx',72),(136032,'xxx',8),(136032,'xxx',14),(136032,'xxx',13),(136032,'xxx',153),(136032,'xxx',42),(136032,'xxx',8),(136032,'xxx',2),(136040,'xxx',1),(136044,'xxx',261),(136044,'xxx',36),(136044,'xxx',398),(136044,'xxx',76),(136044,'-xxx',7),(136044,'-xxx',87),(136044,'-xxx',60),(136044,'-xxx',114),(136044,'x
TABLE LOCK table `db`.`t1` trx id 43DE lock mode IX
----------------------------
END OF INNODB MONITOR OUTPUT
============================
