I have an innodb table with 200 million rows and growing.
It is a highly active table with tons of inserts and updates at all times.
I notice a select query I test on that table is 0.01 seconds or less
when all the inserts/updates are OFF.
But when I throttle up the writes to the table, the select sql then
takes like 1-3 full seconds or more.
This is a serious bottleneck in our application.
Any basic innodb wisdom for this scenario?
+-----------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-------+
| id | bigint(20) unsigned | NO | PRI | 0 | |
| clock | int(11) | NO | PRI | 0 | |
| type | int(11) | NO | PRI | 3 | |
| num | int(11) | NO | | 0 | |
| value_min | double(20,4) | YES | | NULL | |
| value_avg | double(20,4) | YES | | NULL | |
| value_max | double(20,4) | YES | | NULL | |
+-----------+---------------------+------+-----+---------+-------+
# cat /etc/my.cnf|grep -i innodb
default-storage-engine=InnoDB
innodb_data_file_path = ibdata1:256M:autoextend
innodb_buffer_pool_size = 768M
innodb_additional_mem_pool_size = 32M
innodb_log_file_size = 192M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_flush_method=O_DIRECT
innodb_table_locks=0;
innodb_use_legacy_cardinality_algorithm=0;
16G memory
16G swap
8 CPU
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
