Re: Need help to improve innodb performance
Posted by: Song Wang
Date: April 02, 2009 01:28AM
Date: April 02, 2009 01:28AM
hi, Rich,
Thank you for your helpful reply.
Here's some supplementary inforation:
-------------
>mysql --version
mysql Ver 14.12 Distrib 5.1.31, for redhat-linux-gnu (x86_64) using readline 5.1
Disk size
One local disk. Size is 500G
IO
Now 3M/sec. Desired about 10M/sec
---------------
I modified the data model according to your suggestions. The table name slightly changed.
----------------
mysql> show create table tab_proclog_nso\G
*************************** 1. row ***************************
Table: tab_proclog_nso
Create Table: CREATE TABLE `tab_proclog_nso` (
`log_root_id` varchar(100) NOT NULL,
`log_sub_id` varchar(30) NOT NULL,
`hour` int(4) unsigned DEFAULT NULL,
`transaction_id` varchar(32) DEFAULT NULL,
`instance` varchar(1000) NOT NULL,
`oper_id` int(10) unsigned NOT NULL,
`status` varchar(30) NOT NULL,
`hostname` varchar(200) DEFAULT NULL,
`username` varchar(100) DEFAULT NULL,
`starttime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`responsetime` int(10) unsigned NOT NULL COMMENT 'microsecond for this outbound',
`responsecode` varchar(200) DEFAULT NULL,
`fulloperation` varchar(4000) DEFAULT NULL,
`fullresult` varchar(4000) DEFAULT NULL,
PRIMARY KEY (`log_root_id`,`log_sub_id`,`starttime`),
KEY `FK_nso_oper_id` (`oper_id`),
CONSTRAINT `FK_nso_oper_id` FOREIGN KEY (`oper_id`) REFERENCES `tab_proclog_oper` (`oper_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show create table tab_proclog_keys\G
*************************** 1. row ***************************
Table: tab_proclog_keys
Create Table: CREATE TABLE `tab_proclog_keys` (
`log_root_id` varchar(100) NOT NULL,
`log_sub_id` varchar(30) NOT NULL,
`starttime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`name` varchar(200) NOT NULL,
`value` varchar(200) NOT NULL,
PRIMARY KEY (`log_root_id`,`log_sub_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show create table tab_proclog_oper\G
*************************** 1. row ***************************
Table: tab_proclog_oper
Create Table: CREATE TABLE `tab_proclog_oper` (
`oper_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`operation` varchar(200) NOT NULL,
`target` varchar(500) NOT NULL,
`type` varchar(10) NOT NULL,
PRIMARY KEY (`oper_id`)
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
And I tuned the concurrent client session number, ranging from 8 to 64. Also tuned the data size in one tx ranging from 1M to 3M(batch insert).
I get little improvement. The peak throughput of innodb engine is about 3M as before.
So, i believe innodb has a bottleneck on total throughput and i wonder whether it's possible to increase the throughput.
I paste the innodb status here. There're lots of locks. I once tried to remove all the keys except those PK, in order to get rid of the locks on each insertion operation. But it doesn't work.
I was told that when setting innodb_autoinc_lock_mode=2, we get rid of table lock when inserting into auto_inc-columned table. I turned to version 5.1.31. And I don't know whether it take effects.
Sorry for the long and tiresome text here. But i think maybe it helps.
----------------
mysql> show innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
090402 4:38:17 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 5 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2216031, signal count 1030677
--Thread 1151531328 has waited at btr/btr0cur.c line 384 for 0.00 seconds the ssutphore:
S-lock on RW-latch at 0x2aaab5038938 created in file dict/dict0dict.c line 1356
a writer (thread id 1143810368) has reserved it in mode exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr/btr0cur.c line 384
Last time write locked in file btr/btr0cur.c line 377
--Thread 1149667648 has waited at btr/btr0cur.c line 384 for 0.00 seconds the ssutphore:
S-lock on RW-latch at 0x2aaab5038938 created in file dict/dict0dict.c line 1356
a writer (thread id 1143810368) has reserved it in mode exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr/btr0cur.c line 384
Last time write locked in file btr/btr0cur.c line 377
--Thread 1151265088 has waited at btr/btr0cur.c line 384 for 0.00 seconds the ssutphore:
S-lock on RW-latch at 0x2aaab5038938 created in file dict/dict0dict.c line 1356
a writer (thread id 1143810368) has reserved it in mode exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr/btr0cur.c line 384
Last time write locked in file btr/btr0cur.c line 377
--Thread 1146472768 has waited at btr/btr0cur.c line 384 for 0.00 seconds the ssutphore:
S-lock on RW-latch at 0x2aaab5038938 created in file dict/dict0dict.c line 1356
a writer (thread id 1143810368) has reserved it in mode exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr/btr0cur.c line 384
Last time write locked in file btr/btr0cur.c line 377
--Thread 1151797568 has waited at btr/btr0cur.c line 384 for 0.00 seconds the ssutphore:
S-lock on RW-latch at 0x2aaab5038938 created in file dict/dict0dict.c line 1356
a writer (thread id 1143810368) has reserved it in mode exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr/btr0cur.c line 384
Last time write locked in file btr/btr0cur.c line 377
--Thread 1157388608 has waited at btr/btr0cur.c line 377 for 0.00 seconds the ssutphore:
X-lock on RW-latch at 0x2aaab506d538 created in file dict/dict0dict.c line 1356
a writer (thread id 1144076608) has reserved it in mode wait exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr/btr0cur.c line 384
Last time write locked in file btr/btr0cur.c line 377
--Thread 1156323648 has waited at btr/btr0cur.c line 377 for 0.00 seconds the ssutphore:
X-lock on RW-latch at 0x2aaab506d538 created in file dict/dict0dict.c line 1356
a writer (thread id 1144076608) has reserved it in mode wait exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr/btr0cur.c line 384
Last time write locked in file btr/btr0cur.c line 377
--Thread 1150998848 has waited at btr/btr0cur.c line 384 for 0.00 seconds the ssutphore:
S-lock on RW-latch at 0x2aaab5038938 created in file dict/dict0dict.c line 1356
a writer (thread id 1143810368) has reserved it in mode exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr/btr0cur.c line 384
Last time write locked in file btr/btr0cur.c line 377
--Thread 1142479168 has waited at btr/btr0cur.c line 377 for 0.00 seconds the ssutphore:
X-lock on RW-latch at 0x2aaab5038938 created in file dict/dict0dict.c line 1356
a writer (thread id 1143810368) has reserved it in mode exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr/btr0cur.c line 384
Last time write locked in file btr/btr0cur.c line 377
Mutex spin waits 0, rounds 104319797, OS waits 280212
RW-shared spins 1270460, OS waits 809909; RW-excl spins 1371931, OS waits 937322
------------
TRANSACTIONS
------------
Trx id counter 0 10363
Purge done for trx's n:o < 0 0 undo n:o < 0 0
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 18387, OS thread id 1141414208
MySQL thread id 1, query id 2001034 localhost root
show innodb status
---TRANSACTION 0 10362, ACTIVE 2 sec, process no 18387, OS thread id 1141680448 waiting in InnoDB queue
mysql tables in use 1, locked 1
1 lock struct(s), heap size 368, 0 row lock(s), undo log entries 117
MySQL thread id 2, query id 2001002 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_keys(log_root_id,log_sub_id,starttime,name,value) VALUES('g00000000000000000000192218CAI','/0','2009-04-01 10:24:13','a','1')
Trx read view will not see trx with id >= 0 10363, sees < 0 10290
---TRANSACTION 0 10361, ACTIVE 4 sec, process no 18387, OS thread id 1141946688 waiting in InnoDB queue
mysql tables in use 1, locked 1
1 lock struct(s), heap size 368, 0 row lock(s), undo log entries 229
MySQL thread id 3, query id 2001015 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_keys(log_root_id,log_sub_id,starttime,name,value) VALUES('g00000000000000000000192030CAI','/0','2009-04-01 10:24:12','a','1')
Trx read view will not see trx with id >= 0 10362, sees < 0 10290
---TRANSACTION 0 10360, ACTIVE 4 sec, process no 18387, OS thread id 1150732608 waiting in InnoDB queue
mysql tables in use 1, locked 1
1 lock struct(s), heap size 368, 0 row lock(s), undo log entries 239
MySQL thread id 36, query id 2001028 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_keys(log_root_id,log_sub_id,starttime,name,value) VALUES('g00000000000000000000189940CAI','/0','2009-04-01 10:24:07','a','1')
Trx read view will not see trx with id >= 0 10361, sees < 0 10290
---TRANSACTION 0 10359, ACTIVE 6 sec, process no 18387, OS thread id 1150998848 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 368, 0 row lock(s), undo log entries 300
MySQL thread id 37, query id 2000496 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_cso(log_root_id,log_sub_id,hour,transaction_id,instance,oper_id,status,hostname,username,starttime,responsetime,responsecode,fulloperation,fullresult) VALUES('g00000000000000000000189401CAI','/0',10,'123','a=1',1,'Success','sut1349','user','2009-04-01 10:24:06',63,'0','<?xml version=\'1.0\' encoding=\'ISO-8859-1\' standalone=\'no\'?><ParamList><operation>Create</operation><mOType>AdditionalMSISDNxxx</mOType><mOId><value><msisdn>12345678</msisdn></value></mOId><mOAttributes><CreateAdditionalMSISDN xmlns="http://sche
Trx read view will not see trx with id >= 0 10360, sees < 0 10290
---TRANSACTION 0 10358, ACTIVE 6 sec, process no 18387, OS thread id 1149667648 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 368, 0 row lock(s), undo log entries 330
MySQL thread id 32, query id 2000436 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_cso(log_root_id,log_sub_id,hour,transaction_id,instance,oper_id,status,hostname,username,starttime,responsetime,responsecode,fulloperation,fullresult) VALUES('g00000000000000000000190031CAI','/0',10,'123','a=1',1,'Success','sut1349','user','2009-04-01 10:24:07',63,'0','<?xml version=\'1.0\' encoding=\'ISO-8859-1\' standalone=\'no\'?><ParamList><operation>Create</operation><mOType>AdditionalMSISDNxxx</mOType><mOId><value><msisdn>12345678</msisdn></value></mOId><mOAttributes><CreateAdditionalMSISDN xmlns="http://sche
Trx read view will not see trx with id >= 0 10359, sees < 0 10290
---TRANSACTION 0 10357, ACTIVE 6 sec, process no 18387, OS thread id 1142479168 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 368, 0 row lock(s), undo log entries 363
MySQL thread id 5, query id 2000432 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_cso(log_root_id,log_sub_id,hour,transaction_id,instance,oper_id,status,hostname,username,starttime,responsetime,responsecode,fulloperation,fullresult) VALUES('g00000000000000000000191264CAI','/0',10,'123','a=1',1,'Success','sut1349','user','2009-04-01 10:24:10',63,'0','<?xml version=\'1.0\' encoding=\'ISO-8859-1\' standalone=\'no\'?><ParamList><operation>Create</operation><mOType>AdditionalMSISDNxxx</mOType><mOId><value><msisdn>12345678</msisdn></value></mOId><mOAttributes><CreateAdditionalMSISDN xmlns="http://sche
Trx read view will not see trx with id >= 0 10358, sees < 0 10290
---TRANSACTION 0 10356, ACTIVE 7 sec, process no 18387, OS thread id 1143810368 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 368, 0 row lock(s), undo log entries 397
MySQL thread id 10, query id 2000426 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_cso(log_root_id,log_sub_id,hour,transaction_id,instance,oper_id,status,hostname,username,starttime,responsetime,responsecode,fulloperation,fullresult) VALUES('g00000000000000000000190397CAI','/0',10,'123','a=1',1,'Success','sut1349','user','2009-04-01 10:24:08',63,'0','<?xml version=\'1.0\' encoding=\'ISO-8859-1\' standalone=\'no\'?><ParamList><operation>Create</operation><mOType>AdditionalMSISDNxxx</mOType><mOId><value><msisdn>12345678</msisdn></value></mOId><mOAttributes><CreateAdditionalMSISDN xmlns="http://sche
Trx read view will not see trx with id >= 0 10357, sees < 0 10290
---TRANSACTION 0 10355, ACTIVE 7 sec, process no 18387, OS thread id 1151797568 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 368, 0 row lock(s), undo log entries 409
MySQL thread id 40, query id 2000484 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_cso(log_root_id,log_sub_id,hour,transaction_id,instance,oper_id,status,hostname,username,starttime,responsetime,responsecode,fulloperation,fullresult) VALUES('g00000000000000000000188010CAI','/0',10,'123','a=1',1,'Success','sut1349','user','2009-04-01 10:24:02',63,'0','<?xml version=\'1.0\' encoding=\'ISO-8859-1\' standalone=\'no\'?><ParamList><operation>Create</operation><mOType>AdditionalMSISDNxxx</mOType><mOId><value><msisdn>12345678</msisdn></value></mOId><mOAttributes><CreateAdditionalMSISDN xmlns="http://sche
Trx read view will not see trx with id >= 0 10356, sees < 0 10290
---TRANSACTION 0 10354, ACTIVE 7 sec, process no 18387, OS thread id 1146472768 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 368, 0 row lock(s), undo log entries 421
MySQL thread id 20, query id 2000482 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_cso(log_root_id,log_sub_id,hour,transaction_id,instance,oper_id,status,hostname,username,starttime,responsetime,responsecode,fulloperation,fullresult) VALUES('g00000000000000000000191622CAI','/0',10,'123','a=1',1,'Success','sut1349','user','2009-04-01 10:24:11',63,'0','<?xml version=\'1.0\' encoding=\'ISO-8859-1\' standalone=\'no\'?><ParamList><operation>Create</operation><mOType>AdditionalMSISDNxxx</mOType><mOId><value><msisdn>12345678</msisdn></value></mOId><mOAttributes><CreateAdditionalMSISDN xmlns="http://sche
Trx read view will not see trx with id >= 0 10355, sees < 0 10290
---TRANSACTION 0 10353, ACTIVE 8 sec, process no 18387, OS thread id 1151265088 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 368, 0 row lock(s), undo log entries 405
MySQL thread id 38, query id 2000481 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_cso(log_root_id,log_sub_id,hour,transaction_id,instance,oper_id,status,hostname,username,starttime,responsetime,responsecode,fulloperation,fullresult) VALUES('g00000000000000000000186206CAI','/0',10,'123','a=1',1,'Success','sut1349','user','2009-04-01 10:23:58',63,'0','<?xml version=\'1.0\' encoding=\'ISO-8859-1\' standalone=\'no\'?><ParamList><operation>Create</operation><mOType>AdditionalMSISDNxxx</mOType><mOId><value><msisdn>12345678</msisdn></value></mOId><mOAttributes><CreateAdditionalMSISDN xmlns="http://sche
Trx read view will not see trx with id >= 0 10354, sees < 0 10290
---TRANSACTION 0 10352, ACTIVE 12 sec, process no 18387, OS thread id 1148868928 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 368, 0 row lock(s), undo log entries 610
MySQL thread id 29, query id 2000992 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_keys(log_root_id,log_sub_id,starttime,name,value) VALUES('g00000000000000000000185804CAI','/1','2009-04-01 10:23:57','a','1')
Trx read view will not see trx with id >= 0 10353, sees < 0 10289
---TRANSACTION 0 10351, ACTIVE 12 sec, process no 18387, OS thread id 1151531328 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 368, 0 row lock(s), undo log entries 587
MySQL thread id 39, query id 2000464 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_cso(log_root_id,log_sub_id,hour,transaction_id,instance,oper_id,status,hostname,username,starttime,responsetime,responsecode,fulloperation,fullresult) VALUES('g00000000000000000000188788CAI','/0',10,'123','a=1',1,'Success','sut1349','user','2009-04-01 10:24:04',63,'0','<?xml version=\'1.0\' encoding=\'ISO-8859-1\' standalone=\'no\'?><ParamList><operation>Create</operation><mOType>AdditionalMSISDNxxx</mOType><mOId><value><msisdn>12345678</msisdn></value></mOId><mOAttributes><CreateAdditionalMSISDN xmlns="http://sche
Trx read view will not see trx with id >= 0 10352, sees < 0 10289
---TRANSACTION 0 10350, ACTIVE 22 sec, process no 18387, OS thread id 1149135168 waiting in InnoDB queue
mysql tables in use 1, locked 1
2 lock struct(s), heap size 368, 0 row lock(s), undo log entries 677
MySQL thread id 30, query id 2001024 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_keys(log_root_id,log_sub_id,starttime,name,value) VALUES('g00000000000000000000188218CAI','/6','2009-04-01 10:24:03','a','6')
Trx read view will not see trx with id >= 0 10351, sees < 0 10287
---TRANSACTION 0 10290, ACTIVE 122 sec, process no 18387, OS thread id 1145674048 waiting in InnoDB queue
mysql tables in use 1, locked 1
3 lock struct(s), heap size 368, 0 row lock(s), undo log entries 2887
MySQL thread id 17, query id 2001017 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_nso(log_root_id,log_sub_id,hour,transaction_id,instance,oper_id,status,hostname,username,starttime,responsetime,responsecode,fulloperation,fullresult) VALUES('g00000000000000000000169810CAI','/2',10,'321','a=2',7,'Success','sut1349','user','2009-04-01 10:23:16',63,'0','<?xml version=\'1.0\' encoding=\'ISO-8859-1\' standalone=\'no\'?><ParamList><operation>Create</operation><mOType>AdditionalMSISDNxxx</mOType><mOId><value><msisdn>12345678</msisdn></value></mOId><mOAttributes><CreateAdditionalMSISDN xmlns="http://sche
Trx read view will not see trx with id >= 0 10291, sees < 0 10217
///////more tx info deleted here///////
--------
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: 0; buffer pool: 2
10209 OS file reads, 6937 OS file writes, 2811 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 8.60 writes/s, 3.20 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 783, seg size 785,
149569 inserts, 149569 merged recs, 5216 merges
Hash table size 17700857, node heap has 1619 buffer(s)
1339.33 hash searches/s, 6026.79 non-hash searches/s
---
LOG
---
Log sequence number 6 1466141081
Log flushed up to 6 1462063279
Last checkpoint at 5 3388759352
0 pending log writes, 0 pending chkp writes
1024 log i/o's done, 1.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 9612166728; in additional pool allocated 13857792
Dictionary memory allocated 54880
Buffer pool size 524288
Free buffers 332673
Database pages 189996
Modified db pages 122656
Pending reads 0
Pending writes: LRU 0, flush list 1, single page 0
Pages read 10741, created 179255, written 65402
0.00 reads/s, 314.14 creates/s, 76.78 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
16 queries inside InnoDB, 45 queries in queue
65 read views open inside InnoDB
Main thread process no. 18387, id 1140881728, state: sleeping
Number of rows inserted 1996126, updated 0, deleted 0, read 432
3249.95 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set, 1 warning (0.00 sec)
mysql> show variables like '%inno%';
+---------------------------------+--------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------+
| have_innodb | YES |
| innodb_adaptive_hash_index | ON |
| innodb_additional_mem_pool_size | 33554432 |
| innodb_autoextend_increment | 1000 |
| innodb_autoinc_lock_mode | 2 |
| innodb_buffer_pool_size | 8589934592 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | idbdata1:200M:autoextend |
| innodb_data_home_dir | |
| innodb_doublewrite | OFF |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 16 |
| innodb_file_per_table | ON |
| innodb_flush_log_at_trx_commit | 2 |
| innodb_flush_method | O_DIRECT |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 134217728 |
| innodb_log_file_size | 1572864000 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 1000 |
| innodb_rollback_on_timeout | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | OFF |
| innodb_thread_concurrency | 16 |
| innodb_thread_sleep_delay | 10000 |
+---------------------------------+--------------------------+
34 rows in set (0.00 sec)
/Song
Edited 1 time(s). Last edit at 04/02/2009 02:05AM by Song Wang.
Thank you for your helpful reply.
Here's some supplementary inforation:
-------------
>mysql --version
mysql Ver 14.12 Distrib 5.1.31, for redhat-linux-gnu (x86_64) using readline 5.1
Disk size
One local disk. Size is 500G
IO
Now 3M/sec. Desired about 10M/sec
---------------
I modified the data model according to your suggestions. The table name slightly changed.
----------------
mysql> show create table tab_proclog_nso\G
*************************** 1. row ***************************
Table: tab_proclog_nso
Create Table: CREATE TABLE `tab_proclog_nso` (
`log_root_id` varchar(100) NOT NULL,
`log_sub_id` varchar(30) NOT NULL,
`hour` int(4) unsigned DEFAULT NULL,
`transaction_id` varchar(32) DEFAULT NULL,
`instance` varchar(1000) NOT NULL,
`oper_id` int(10) unsigned NOT NULL,
`status` varchar(30) NOT NULL,
`hostname` varchar(200) DEFAULT NULL,
`username` varchar(100) DEFAULT NULL,
`starttime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`responsetime` int(10) unsigned NOT NULL COMMENT 'microsecond for this outbound',
`responsecode` varchar(200) DEFAULT NULL,
`fulloperation` varchar(4000) DEFAULT NULL,
`fullresult` varchar(4000) DEFAULT NULL,
PRIMARY KEY (`log_root_id`,`log_sub_id`,`starttime`),
KEY `FK_nso_oper_id` (`oper_id`),
CONSTRAINT `FK_nso_oper_id` FOREIGN KEY (`oper_id`) REFERENCES `tab_proclog_oper` (`oper_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show create table tab_proclog_keys\G
*************************** 1. row ***************************
Table: tab_proclog_keys
Create Table: CREATE TABLE `tab_proclog_keys` (
`log_root_id` varchar(100) NOT NULL,
`log_sub_id` varchar(30) NOT NULL,
`starttime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`name` varchar(200) NOT NULL,
`value` varchar(200) NOT NULL,
PRIMARY KEY (`log_root_id`,`log_sub_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> show create table tab_proclog_oper\G
*************************** 1. row ***************************
Table: tab_proclog_oper
Create Table: CREATE TABLE `tab_proclog_oper` (
`oper_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`operation` varchar(200) NOT NULL,
`target` varchar(500) NOT NULL,
`type` varchar(10) NOT NULL,
PRIMARY KEY (`oper_id`)
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
And I tuned the concurrent client session number, ranging from 8 to 64. Also tuned the data size in one tx ranging from 1M to 3M(batch insert).
I get little improvement. The peak throughput of innodb engine is about 3M as before.
So, i believe innodb has a bottleneck on total throughput and i wonder whether it's possible to increase the throughput.
I paste the innodb status here. There're lots of locks. I once tried to remove all the keys except those PK, in order to get rid of the locks on each insertion operation. But it doesn't work.
I was told that when setting innodb_autoinc_lock_mode=2, we get rid of table lock when inserting into auto_inc-columned table. I turned to version 5.1.31. And I don't know whether it take effects.
Sorry for the long and tiresome text here. But i think maybe it helps.
----------------
mysql> show innodb status\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
090402 4:38:17 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 5 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2216031, signal count 1030677
--Thread 1151531328 has waited at btr/btr0cur.c line 384 for 0.00 seconds the ssutphore:
S-lock on RW-latch at 0x2aaab5038938 created in file dict/dict0dict.c line 1356
a writer (thread id 1143810368) has reserved it in mode exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr/btr0cur.c line 384
Last time write locked in file btr/btr0cur.c line 377
--Thread 1149667648 has waited at btr/btr0cur.c line 384 for 0.00 seconds the ssutphore:
S-lock on RW-latch at 0x2aaab5038938 created in file dict/dict0dict.c line 1356
a writer (thread id 1143810368) has reserved it in mode exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr/btr0cur.c line 384
Last time write locked in file btr/btr0cur.c line 377
--Thread 1151265088 has waited at btr/btr0cur.c line 384 for 0.00 seconds the ssutphore:
S-lock on RW-latch at 0x2aaab5038938 created in file dict/dict0dict.c line 1356
a writer (thread id 1143810368) has reserved it in mode exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr/btr0cur.c line 384
Last time write locked in file btr/btr0cur.c line 377
--Thread 1146472768 has waited at btr/btr0cur.c line 384 for 0.00 seconds the ssutphore:
S-lock on RW-latch at 0x2aaab5038938 created in file dict/dict0dict.c line 1356
a writer (thread id 1143810368) has reserved it in mode exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr/btr0cur.c line 384
Last time write locked in file btr/btr0cur.c line 377
--Thread 1151797568 has waited at btr/btr0cur.c line 384 for 0.00 seconds the ssutphore:
S-lock on RW-latch at 0x2aaab5038938 created in file dict/dict0dict.c line 1356
a writer (thread id 1143810368) has reserved it in mode exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr/btr0cur.c line 384
Last time write locked in file btr/btr0cur.c line 377
--Thread 1157388608 has waited at btr/btr0cur.c line 377 for 0.00 seconds the ssutphore:
X-lock on RW-latch at 0x2aaab506d538 created in file dict/dict0dict.c line 1356
a writer (thread id 1144076608) has reserved it in mode wait exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr/btr0cur.c line 384
Last time write locked in file btr/btr0cur.c line 377
--Thread 1156323648 has waited at btr/btr0cur.c line 377 for 0.00 seconds the ssutphore:
X-lock on RW-latch at 0x2aaab506d538 created in file dict/dict0dict.c line 1356
a writer (thread id 1144076608) has reserved it in mode wait exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr/btr0cur.c line 384
Last time write locked in file btr/btr0cur.c line 377
--Thread 1150998848 has waited at btr/btr0cur.c line 384 for 0.00 seconds the ssutphore:
S-lock on RW-latch at 0x2aaab5038938 created in file dict/dict0dict.c line 1356
a writer (thread id 1143810368) has reserved it in mode exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr/btr0cur.c line 384
Last time write locked in file btr/btr0cur.c line 377
--Thread 1142479168 has waited at btr/btr0cur.c line 377 for 0.00 seconds the ssutphore:
X-lock on RW-latch at 0x2aaab5038938 created in file dict/dict0dict.c line 1356
a writer (thread id 1143810368) has reserved it in mode exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr/btr0cur.c line 384
Last time write locked in file btr/btr0cur.c line 377
Mutex spin waits 0, rounds 104319797, OS waits 280212
RW-shared spins 1270460, OS waits 809909; RW-excl spins 1371931, OS waits 937322
------------
TRANSACTIONS
------------
Trx id counter 0 10363
Purge done for trx's n:o < 0 0 undo n:o < 0 0
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 18387, OS thread id 1141414208
MySQL thread id 1, query id 2001034 localhost root
show innodb status
---TRANSACTION 0 10362, ACTIVE 2 sec, process no 18387, OS thread id 1141680448 waiting in InnoDB queue
mysql tables in use 1, locked 1
1 lock struct(s), heap size 368, 0 row lock(s), undo log entries 117
MySQL thread id 2, query id 2001002 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_keys(log_root_id,log_sub_id,starttime,name,value) VALUES('g00000000000000000000192218CAI','/0','2009-04-01 10:24:13','a','1')
Trx read view will not see trx with id >= 0 10363, sees < 0 10290
---TRANSACTION 0 10361, ACTIVE 4 sec, process no 18387, OS thread id 1141946688 waiting in InnoDB queue
mysql tables in use 1, locked 1
1 lock struct(s), heap size 368, 0 row lock(s), undo log entries 229
MySQL thread id 3, query id 2001015 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_keys(log_root_id,log_sub_id,starttime,name,value) VALUES('g00000000000000000000192030CAI','/0','2009-04-01 10:24:12','a','1')
Trx read view will not see trx with id >= 0 10362, sees < 0 10290
---TRANSACTION 0 10360, ACTIVE 4 sec, process no 18387, OS thread id 1150732608 waiting in InnoDB queue
mysql tables in use 1, locked 1
1 lock struct(s), heap size 368, 0 row lock(s), undo log entries 239
MySQL thread id 36, query id 2001028 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_keys(log_root_id,log_sub_id,starttime,name,value) VALUES('g00000000000000000000189940CAI','/0','2009-04-01 10:24:07','a','1')
Trx read view will not see trx with id >= 0 10361, sees < 0 10290
---TRANSACTION 0 10359, ACTIVE 6 sec, process no 18387, OS thread id 1150998848 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 368, 0 row lock(s), undo log entries 300
MySQL thread id 37, query id 2000496 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_cso(log_root_id,log_sub_id,hour,transaction_id,instance,oper_id,status,hostname,username,starttime,responsetime,responsecode,fulloperation,fullresult) VALUES('g00000000000000000000189401CAI','/0',10,'123','a=1',1,'Success','sut1349','user','2009-04-01 10:24:06',63,'0','<?xml version=\'1.0\' encoding=\'ISO-8859-1\' standalone=\'no\'?><ParamList><operation>Create</operation><mOType>AdditionalMSISDNxxx</mOType><mOId><value><msisdn>12345678</msisdn></value></mOId><mOAttributes><CreateAdditionalMSISDN xmlns="http://sche
Trx read view will not see trx with id >= 0 10360, sees < 0 10290
---TRANSACTION 0 10358, ACTIVE 6 sec, process no 18387, OS thread id 1149667648 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 368, 0 row lock(s), undo log entries 330
MySQL thread id 32, query id 2000436 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_cso(log_root_id,log_sub_id,hour,transaction_id,instance,oper_id,status,hostname,username,starttime,responsetime,responsecode,fulloperation,fullresult) VALUES('g00000000000000000000190031CAI','/0',10,'123','a=1',1,'Success','sut1349','user','2009-04-01 10:24:07',63,'0','<?xml version=\'1.0\' encoding=\'ISO-8859-1\' standalone=\'no\'?><ParamList><operation>Create</operation><mOType>AdditionalMSISDNxxx</mOType><mOId><value><msisdn>12345678</msisdn></value></mOId><mOAttributes><CreateAdditionalMSISDN xmlns="http://sche
Trx read view will not see trx with id >= 0 10359, sees < 0 10290
---TRANSACTION 0 10357, ACTIVE 6 sec, process no 18387, OS thread id 1142479168 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 368, 0 row lock(s), undo log entries 363
MySQL thread id 5, query id 2000432 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_cso(log_root_id,log_sub_id,hour,transaction_id,instance,oper_id,status,hostname,username,starttime,responsetime,responsecode,fulloperation,fullresult) VALUES('g00000000000000000000191264CAI','/0',10,'123','a=1',1,'Success','sut1349','user','2009-04-01 10:24:10',63,'0','<?xml version=\'1.0\' encoding=\'ISO-8859-1\' standalone=\'no\'?><ParamList><operation>Create</operation><mOType>AdditionalMSISDNxxx</mOType><mOId><value><msisdn>12345678</msisdn></value></mOId><mOAttributes><CreateAdditionalMSISDN xmlns="http://sche
Trx read view will not see trx with id >= 0 10358, sees < 0 10290
---TRANSACTION 0 10356, ACTIVE 7 sec, process no 18387, OS thread id 1143810368 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 368, 0 row lock(s), undo log entries 397
MySQL thread id 10, query id 2000426 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_cso(log_root_id,log_sub_id,hour,transaction_id,instance,oper_id,status,hostname,username,starttime,responsetime,responsecode,fulloperation,fullresult) VALUES('g00000000000000000000190397CAI','/0',10,'123','a=1',1,'Success','sut1349','user','2009-04-01 10:24:08',63,'0','<?xml version=\'1.0\' encoding=\'ISO-8859-1\' standalone=\'no\'?><ParamList><operation>Create</operation><mOType>AdditionalMSISDNxxx</mOType><mOId><value><msisdn>12345678</msisdn></value></mOId><mOAttributes><CreateAdditionalMSISDN xmlns="http://sche
Trx read view will not see trx with id >= 0 10357, sees < 0 10290
---TRANSACTION 0 10355, ACTIVE 7 sec, process no 18387, OS thread id 1151797568 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 368, 0 row lock(s), undo log entries 409
MySQL thread id 40, query id 2000484 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_cso(log_root_id,log_sub_id,hour,transaction_id,instance,oper_id,status,hostname,username,starttime,responsetime,responsecode,fulloperation,fullresult) VALUES('g00000000000000000000188010CAI','/0',10,'123','a=1',1,'Success','sut1349','user','2009-04-01 10:24:02',63,'0','<?xml version=\'1.0\' encoding=\'ISO-8859-1\' standalone=\'no\'?><ParamList><operation>Create</operation><mOType>AdditionalMSISDNxxx</mOType><mOId><value><msisdn>12345678</msisdn></value></mOId><mOAttributes><CreateAdditionalMSISDN xmlns="http://sche
Trx read view will not see trx with id >= 0 10356, sees < 0 10290
---TRANSACTION 0 10354, ACTIVE 7 sec, process no 18387, OS thread id 1146472768 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 368, 0 row lock(s), undo log entries 421
MySQL thread id 20, query id 2000482 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_cso(log_root_id,log_sub_id,hour,transaction_id,instance,oper_id,status,hostname,username,starttime,responsetime,responsecode,fulloperation,fullresult) VALUES('g00000000000000000000191622CAI','/0',10,'123','a=1',1,'Success','sut1349','user','2009-04-01 10:24:11',63,'0','<?xml version=\'1.0\' encoding=\'ISO-8859-1\' standalone=\'no\'?><ParamList><operation>Create</operation><mOType>AdditionalMSISDNxxx</mOType><mOId><value><msisdn>12345678</msisdn></value></mOId><mOAttributes><CreateAdditionalMSISDN xmlns="http://sche
Trx read view will not see trx with id >= 0 10355, sees < 0 10290
---TRANSACTION 0 10353, ACTIVE 8 sec, process no 18387, OS thread id 1151265088 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 368, 0 row lock(s), undo log entries 405
MySQL thread id 38, query id 2000481 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_cso(log_root_id,log_sub_id,hour,transaction_id,instance,oper_id,status,hostname,username,starttime,responsetime,responsecode,fulloperation,fullresult) VALUES('g00000000000000000000186206CAI','/0',10,'123','a=1',1,'Success','sut1349','user','2009-04-01 10:23:58',63,'0','<?xml version=\'1.0\' encoding=\'ISO-8859-1\' standalone=\'no\'?><ParamList><operation>Create</operation><mOType>AdditionalMSISDNxxx</mOType><mOId><value><msisdn>12345678</msisdn></value></mOId><mOAttributes><CreateAdditionalMSISDN xmlns="http://sche
Trx read view will not see trx with id >= 0 10354, sees < 0 10290
---TRANSACTION 0 10352, ACTIVE 12 sec, process no 18387, OS thread id 1148868928 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 368, 0 row lock(s), undo log entries 610
MySQL thread id 29, query id 2000992 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_keys(log_root_id,log_sub_id,starttime,name,value) VALUES('g00000000000000000000185804CAI','/1','2009-04-01 10:23:57','a','1')
Trx read view will not see trx with id >= 0 10353, sees < 0 10289
---TRANSACTION 0 10351, ACTIVE 12 sec, process no 18387, OS thread id 1151531328 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
2 lock struct(s), heap size 368, 0 row lock(s), undo log entries 587
MySQL thread id 39, query id 2000464 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_cso(log_root_id,log_sub_id,hour,transaction_id,instance,oper_id,status,hostname,username,starttime,responsetime,responsecode,fulloperation,fullresult) VALUES('g00000000000000000000188788CAI','/0',10,'123','a=1',1,'Success','sut1349','user','2009-04-01 10:24:04',63,'0','<?xml version=\'1.0\' encoding=\'ISO-8859-1\' standalone=\'no\'?><ParamList><operation>Create</operation><mOType>AdditionalMSISDNxxx</mOType><mOId><value><msisdn>12345678</msisdn></value></mOId><mOAttributes><CreateAdditionalMSISDN xmlns="http://sche
Trx read view will not see trx with id >= 0 10352, sees < 0 10289
---TRANSACTION 0 10350, ACTIVE 22 sec, process no 18387, OS thread id 1149135168 waiting in InnoDB queue
mysql tables in use 1, locked 1
2 lock struct(s), heap size 368, 0 row lock(s), undo log entries 677
MySQL thread id 30, query id 2001024 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_keys(log_root_id,log_sub_id,starttime,name,value) VALUES('g00000000000000000000188218CAI','/6','2009-04-01 10:24:03','a','6')
Trx read view will not see trx with id >= 0 10351, sees < 0 10287
---TRANSACTION 0 10290, ACTIVE 122 sec, process no 18387, OS thread id 1145674048 waiting in InnoDB queue
mysql tables in use 1, locked 1
3 lock struct(s), heap size 368, 0 row lock(s), undo log entries 2887
MySQL thread id 17, query id 2001017 192.168.30.84 dve_proclog_user update
/* */ INSERT INTO tab_proclog_nso(log_root_id,log_sub_id,hour,transaction_id,instance,oper_id,status,hostname,username,starttime,responsetime,responsecode,fulloperation,fullresult) VALUES('g00000000000000000000169810CAI','/2',10,'321','a=2',7,'Success','sut1349','user','2009-04-01 10:23:16',63,'0','<?xml version=\'1.0\' encoding=\'ISO-8859-1\' standalone=\'no\'?><ParamList><operation>Create</operation><mOType>AdditionalMSISDNxxx</mOType><mOId><value><msisdn>12345678</msisdn></value></mOId><mOAttributes><CreateAdditionalMSISDN xmlns="http://sche
Trx read view will not see trx with id >= 0 10291, sees < 0 10217
///////more tx info deleted here///////
--------
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: 0; buffer pool: 2
10209 OS file reads, 6937 OS file writes, 2811 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 8.60 writes/s, 3.20 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 783, seg size 785,
149569 inserts, 149569 merged recs, 5216 merges
Hash table size 17700857, node heap has 1619 buffer(s)
1339.33 hash searches/s, 6026.79 non-hash searches/s
---
LOG
---
Log sequence number 6 1466141081
Log flushed up to 6 1462063279
Last checkpoint at 5 3388759352
0 pending log writes, 0 pending chkp writes
1024 log i/o's done, 1.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 9612166728; in additional pool allocated 13857792
Dictionary memory allocated 54880
Buffer pool size 524288
Free buffers 332673
Database pages 189996
Modified db pages 122656
Pending reads 0
Pending writes: LRU 0, flush list 1, single page 0
Pages read 10741, created 179255, written 65402
0.00 reads/s, 314.14 creates/s, 76.78 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
16 queries inside InnoDB, 45 queries in queue
65 read views open inside InnoDB
Main thread process no. 18387, id 1140881728, state: sleeping
Number of rows inserted 1996126, updated 0, deleted 0, read 432
3249.95 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set, 1 warning (0.00 sec)
mysql> show variables like '%inno%';
+---------------------------------+--------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------+
| have_innodb | YES |
| innodb_adaptive_hash_index | ON |
| innodb_additional_mem_pool_size | 33554432 |
| innodb_autoextend_increment | 1000 |
| innodb_autoinc_lock_mode | 2 |
| innodb_buffer_pool_size | 8589934592 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | idbdata1:200M:autoextend |
| innodb_data_home_dir | |
| innodb_doublewrite | OFF |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 16 |
| innodb_file_per_table | ON |
| innodb_flush_log_at_trx_commit | 2 |
| innodb_flush_method | O_DIRECT |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 134217728 |
| innodb_log_file_size | 1572864000 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 1000 |
| innodb_rollback_on_timeout | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | OFF |
| innodb_thread_concurrency | 16 |
| innodb_thread_sleep_delay | 10000 |
+---------------------------------+--------------------------+
34 rows in set (0.00 sec)
/Song
Edited 1 time(s). Last edit at 04/02/2009 02:05AM by Song Wang.
Subject
Views
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.