Re: Terrible performance of a specific query with lots of connections

[Ben Dowling]

Here are all my InnoDB settings, incase there is something there that is of interest:

mysql> show variables like "%inno%";
+---------------------------------+------------------------+
| Variable_name                   | Value                  |
+---------------------------------+------------------------+
| have_innodb                     | YES                    | 
| innodb_additional_mem_pool_size | 1048576                | 
| innodb_autoextend_increment     | 8                      | 
| innodb_buffer_pool_awe_mem_mb   | 0                      | 
| innodb_buffer_pool_size         | 21474836480            | 
| innodb_checksums                | ON                     | 
| innodb_commit_concurrency       | 0                      | 
| innodb_concurrency_tickets      | 500                    | 
| innodb_data_file_path           | ibdata1:10M:autoextend | 
| innodb_data_home_dir            |                        | 
| innodb_doublewrite              | ON                     | 
| innodb_fast_shutdown            | 1                      | 
| innodb_file_io_threads          | 4                      | 
| innodb_file_per_table           | ON                     | 
| innodb_flush_log_at_trx_commit  | 1                      | 
| innodb_flush_method             |                        | 
| 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          | 1048576                | 
| innodb_log_file_size            | 5242880                | 
| 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               | 300                    | 
| innodb_rollback_on_timeout      | OFF                    | 
| innodb_support_xa               | ON                     | 
| innodb_sync_spin_loops          | 20                     | 
| innodb_table_locks              | ON                     | 
| innodb_thread_concurrency       | 8                      | 
| innodb_thread_sleep_delay       | 10000                  | 
+---------------------------------+------------------------+

Thanks again, Ben