Innodb and bulk writes

--001636c92b8a5727d90482150a35
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi list,

Im having problems with bulk writes (restores from mysqldumps, alters,
delete in (select ...)) with innodb. The servers are at amazon EC2 instance=
s
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 =3D xxx" deletes 10000 r=
ows
/ 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 =3D 10G
innodb_additional_mem_pool_size =3D 20M
innodb_thread_concurrency =3D 8
innodb_support_xa =3D 0
innodb_thread_sleep_delay =3D 2000
innodb_flush_log_at_trx_commit =3D 0
innodb_log_file_size =3D 700M
innodb_log_buffer_size =3D 8M
innodb_lock_wait_timeout =3D 50
innodb_max_purge_lag =3D 10
innodb_max_dirty_pages_pct =3D 90
innodb_use_purge_thread =3D 4
innodb_extra_undoslots =3D 1
innodb_adaptive_checkpoint =3D estimate
innodb_io_capacity =3D 500
innodb_read_io_threads =3D 4
innodb_write_io_threads =3D 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:
==================== =====3D=
============
100310 13:12:07 INNODB MONITOR OUTPUT
==================== =====3D=
============
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),(136 032,'xxx',24),(=
136032,'xxx',72),(136032,'xxx',8),(136032,'xxx',14),(136032, 'xxx',13),(1360=
32,'xxx',153),(136032,'xxx',42),(136032,'xxx',8),(136032,'xx x',2),(136040,'=
xxx',1),(136044,'xxx',261),(136044,'xxx',36),(136044,'xxx',3 98),(136044,'xx=
x',76),(136044,'-xxx',7),(136044,'-xxx',87),(136044,'-xxx',6 0),(136044,'-xx=
x',114),(136044,'x
TABLE LOCK table `db`.`t1` trx id 43DE lock mode IX
----------------------------
END OF INNODB MONITOR OUTPUT
==================== =====3D=
===3D

--001636c92b8a5727d90482150a35--

Re: Innodb and bulk writes

--001636e0a6fe1d46a60482151c2c
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

when the writes are happening, please run "show full processlist" and let u=
s
know the out put.

regards
anandkl

On Thu, Mar 18, 2010 at 9:09 PM, Max Bube wrote:

> 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 restor=
e
> 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 agai=
n.
>
> The DBs are relative small about 70M rows and 10Gb size. I can repeat thi=
s
> 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 =3D 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 =3D 10G
> innodb_additional_mem_pool_size =3D 20M
> innodb_thread_concurrency =3D 8
> innodb_support_xa =3D 0
> innodb_thread_sleep_delay =3D 2000
> innodb_flush_log_at_trx_commit =3D 0
> innodb_log_file_size =3D 700M
> innodb_log_buffer_size =3D 8M
> innodb_lock_wait_timeout =3D 50
> innodb_max_purge_lag =3D 10
> innodb_max_dirty_pages_pct =3D 90
> innodb_use_purge_thread =3D 4
> innodb_extra_undoslots =3D 1
> innodb_adaptive_checkpoint =3D estimate
> innodb_io_capacity =3D 500
> innodb_read_io_threads =3D 4
> innodb_write_io_threads =3D 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:
> ==================== =====
=============3D
> 100310 13:12:07 INNODB MONITOR OUTPUT
> ==================== =====
=============3D
> 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),(136 032,'xxx',24)=
,(136032,'xxx',72),(136032,'xxx',8),(136032,'xxx',14),(13603 2,'xxx',13),(13=
6032,'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
> ==================== =====
====
>

--001636e0a6fe1d46a60482151c2c--

Re: Innodb and bulk writes

--0016e6470c82b94c05048215469f
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

This is an output from console when its performnace goes dows

Query OK, 65469 rows affected (0.82 sec)
Records: 65469 Duplicates: 0 Warnings: 0

Query OK, 65469 rows affected (0.78 sec)
Records: 65469 Duplicates: 0 Warnings: 0

Query OK, 65469 rows affected (10 min 57.30 sec)
Records: 65469 Duplicates: 0 Warnings: 0

Query OK, 65469 rows affected (10 min 56.58 sec)
Records: 65469 Duplicates: 0 Warnings: 0

and the show full processlist is, I cut the insert its about 65k rows like
you can see above


*************************** 1. row ***************************
Id: 1
User: event_scheduler
Host: localhost
db: NULL
Command: Daemon
Time: 4730
State: Waiting on empty queue
Info: NULL
*************************** 2. row ***************************
Id: 3
User: root
Host: localhost
db: test
Command: Query
Time: 603
State: update
Info: INSERT INTO `challenge` VALUES
(95794260,2,0),(95794261,3,1),(95794262,2,1),(95794263,5,0)

*************************** 3. row ***************************
Id: 7
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show full processlist

regards
Max

2010/3/18 Ananda Kumar

> when the writes are happening, please run "show full processlist" and let
> us know the out put.
>
> regards
> anandkl
>
> On Thu, Mar 18, 2010 at 9:09 PM, Max Bube wrote:
>
>> 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 resto=
re
>> from mysqldump, its starts processing more than 50000 rows/s but suddenl=
y
>> 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 th=
is
>> 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 =3D xxx" deletes 1000=
0
>> 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 =3D 10G
>> innodb_additional_mem_pool_size =3D 20M
>> innodb_thread_concurrency =3D 8
>> innodb_support_xa =3D 0
>> innodb_thread_sleep_delay =3D 2000
>> innodb_flush_log_at_trx_commit =3D 0
>> innodb_log_file_size =3D 700M
>> innodb_log_buffer_size =3D 8M
>> innodb_lock_wait_timeout =3D 50
>> innodb_max_purge_lag =3D 10
>> innodb_max_dirty_pages_pct =3D 90
>> innodb_use_purge_thread =3D 4
>> innodb_extra_undoslots =3D 1
>> innodb_adaptive_checkpoint =3D estimate
>> innodb_io_capacity =3D 500
>> innodb_read_io_threads =3D 4
>> innodb_write_io_threads =3D 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:
>> ==================== =====
=============3D
>> 100310 13:12:07 INNODB MONITOR OUTPUT
>> ==================== =====
=============3D
>> 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 / 100=
0
>> 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),(136 032,'xxx',24=
),(136032,'xxx',72),(136032,'xxx',8),(136032,'xxx',14),(1360 32,'xxx',13),(1=
36032,'xxx',153),(136032,'xxx',42),(136032,'xxx',8),(136032, 'xxx',2),(13604=
0,'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
>> ==================== =====
====
>>
>
>

--0016e6470c82b94c05048215469f--

Re: Innodb and bulk writes

Max Bube gmail.com> writes:


> 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

How busy are your disks when you start seeing slowdown in the delete
process? Are there blobs or big varchars in the deletes that you are
doing? Innodb might be filling up its log files and when you see a
slow down, it might be flushing the log to the disk.

One workaround for this is to not delete million rows, but to delete
in batches of 1000 rows. My guess would be that if each row is of
size B, and you delete in a batch size of [innodb_log_file_size (in
bytes) - 100 MB (in bytes)]/B , you should not see a slowdown.



--
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: Innodb and bulk writes

--00504502b0e3a66aac04821ff60f
Content-Type: text/plain; charset=ISO-8859-1

How big is your /tmp folder, can u move this to other fast moving disk.

On Fri, Mar 19, 2010 at 4:27 AM, Raj Shekhar wrote:

> Max Bube gmail.com> writes:
>
>
> > 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
>
> How busy are your disks when you start seeing slowdown in the delete
> process? Are there blobs or big varchars in the deletes that you are
> doing? Innodb might be filling up its log files and when you see a
> slow down, it might be flushing the log to the disk.
>
> One workaround for this is to not delete million rows, but to delete
> in batches of 1000 rows. My guess would be that if each row is of
> size B, and you delete in a batch size of [innodb_log_file_size (in
> bytes) - 100 MB (in bytes)]/B , you should not see a slowdown.
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
>
>

--00504502b0e3a66aac04821ff60f--

Re: Innodb and bulk writes

--0050450170f48de7e30482274f63
Content-Type: text/plain; charset=ISO-8859-1

Hi Raj, Ananda, the schema is very simple, we don't have any blob or text
column.

I thought the same about the log files, so I tried with diferent sizes but
nothing change.
This is the output of iostat -x 1 when the performance is slow running a
restore


avg-cpu: %user %nice %system %iowait %steal %idle
0.00 0.00 0.00 0.00 0.00 100.00

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz
avgqu-sz await svctm %util
sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sdd 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sde 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
md0 0.00 0.00 0.00 3.92 0.00 59.80 15.25
0.00 0.00 0.00 0.00
sdh 0.00 0.00 0.00 0.98 0.00 31.37 32.00
0.00 0.00 0.00 0.00
sdi 0.00 0.00 0.00 0.98 0.00 23.53 24.00
0.00 0.00 0.00 0.00
sdj 0.00 0.00 0.00 1.96 0.00 4.90 2.50
0.00 0.00 0.00 0.00
sdk 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00

avg-cpu: %user %nice %system %iowait %steal %idle
0.00 0.00 0.00 0.00 0.00 100.00

Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz
avgqu-sz await svctm %util
sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sdd 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sde 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
md0 0.00 0.00 0.00 1.98 0.00 17.82 9.00
0.00 0.00 0.00 0.00
sdh 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00
sdi 0.00 0.00 0.00 0.99 0.00 15.84 16.00
0.00 0.00 0.00 0.00
sdj 0.00 0.00 0.00 0.99 0.00 1.98 2.00
0.00 0.00 0.00 0.00
sdk 0.00 0.00 0.00 0.00 0.00 0.00 0.00
0.00 0.00 0.00 0.00


2010/3/18 Raj Shekhar

> Max Bube gmail.com> writes:
>
>
> > 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
>
> How busy are your disks when you start seeing slowdown in the delete
> process? Are there blobs or big varchars in the deletes that you are
> doing? Innodb might be filling up its log files and when you see a
> slow down, it might be flushing the log to the disk.
>

> One workaround for this is to not delete million rows, but to delete
> in batches of 1000 rows. My guess would be that if each row is of
> size B, and you delete in a batch size of [innodb_log_file_size (in
> bytes) - 100 MB (in bytes)]/B , you should not see a slowdown.
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=maxbube@gmail.com
>
>

--0050450170f48de7e30482274f63--