Re: Need help to improve innodb performance
Posted by:
Song Wang
Date: March 30, 2009 10:17PM
Thanks for your reply and advice.
Here comes more information about MySQL server and our data model.
KEYS and OUTBOUND are the most busy tables. Currently, the top throughput on KEYS table is about 1650 inserts/second and 1350 inserts/second on OUTBOUND. Each record in KEYS are quite small, but in OUTBOUND, it's about 1.7K/record.
The totally throughput of DB server is about 3M/second. And it seems that we have reach the ceiling under current config.
mysql> show variables like '%innodb%';
+---------------------------------+--------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------+
| have_innodb | YES |
| innodb_additional_mem_pool_size | 33554432 |
| innodb_autoextend_increment | 1000 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| 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_adaptive_hash_index | ON |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 32 |
| 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_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 268435456 |
| 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 | 0 |
| innodb_thread_sleep_delay | 10000 |
+---------------------------------+--------------------------+
36 rows in set (0.00 sec)
mysql> show innodb status\G
*************************** 1. row ***************************
Status:
=====================================
090331 6:00:28 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 7 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 3212718, signal count 1101030
Mutex spin waits 0, rounds 38302709, OS waits 430310
RW-shared spins 1847784, OS waits 1415873; RW-excl spins 1709398, OS waits 1243760
------------
TRANSACTIONS
------------
Trx id counter 0 4766
Purge done for trx's n:o < 0 1331 undo n:o < 0 0
History list length 13
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 14987, OS thread id 1146288448
MySQL thread id 271, query id 2115001 localhost root
show innodb status
--------
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: 0
15622 OS file reads, 16660 OS file writes, 10555 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 20, seg size 22,
14910 inserts, 14910 merged recs, 566 merges
Hash table size 17700857, used cells 763055, node heap has 1152 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 1 4235221809
Log flushed up to 1 4235221809
Last checkpoint at 1 4235221809
0 pending log writes, 0 pending chkp writes
13608 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 9825166400; in additional pool allocated 15749376
Buffer pool size 524288
Free buffers 284228
Database pages 238908
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 64358, created 174550, written 241777
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 14987, id 1140881728, state: waiting for server activity
Number of rows inserted 2108574, updated 0, deleted 0, read 154269
0.00 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.17 sec)
mysql> show create table keys\G
*************************** 1. row ***************************
Table: keys
Create Table: CREATE TABLE `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,
KEY `IN_LOG_ID` (`log_root_id`,`log_sub_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.06 sec)
mysql> show create table outbound\G
*************************** 1. row ***************************
Table: outbound
Create Table: CREATE TABLE `outbound` (
`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,
KEY `IN_LOG_ID` (`log_root_id`,`log_sub_id`,`starttime`),
KEY `FK_nso_oper_id` (`oper_id`),
CONSTRAINT `FK_nso_oper_id` FOREIGN KEY (`oper_id`) REFERENCES `toper` (`oper_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
/Song