List:General Discussion« Previous MessageNext Message »
From:Bryan Cantwell Date:February 19 2011 3:59pm
Subject:Super active table optimization
View as plain text  
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
Thread
Super active table optimizationBryan Cantwell19 Feb
  • Re: Super active table optimizationWalter Heck19 Feb
  • Re: Super active table optimizationReindl Harald19 Feb