InnoDB Index Doesn't Fit in Memory

Question

I am using InnoDB tables in my database and total index_size is 250 GB and my data size is 500 GB. Server has 128 GB RAM, and innodb_buffer_pool_size is set to 64 GB. Data size is increasing every day and select queries performance is degrading because of swaps. What can be done to improve select query performance?

show variables like 'innodb%' output :

innodb_adaptive_flushing        ON  
innodb_adaptive_hash_index      ON  
innodb_additional_mem_pool_size 4194304  
innodb_autoextend_increment     8  
innodb_autoinc_lock_mode        0  
innodb_buffer_pool_instances    1  
innodb_buffer_pool_size 68719476736  
innodb_change_buffering all  
innodb_checksums        ON  
innodb_commit_concurrency       16  
innodb_concurrency_tickets      500  
innodb_data_file_path   ibdata1:10M:autoextend  
innodb_data_home_dir      
innodb_doublewrite      ON  
innodb_fast_shutdown    1  
innodb_file_format      Antelope  
innodb_file_format_check        ON  
innodb_file_format_max  Antelope  
innodb_file_per_table   ON  
innodb_flush_log_at_trx_commit  1  
innodb_flush_method     O_DSYNC  
innodb_force_load_corrupted     OFF  
innodb_force_recovery   0  
innodb_io_capacity      200  
innodb_large_prefix     OFF  
innodb_lock_wait_timeout        60  
innodb_locks_unsafe_for_binlog  OFF  
innodb_log_buffer_size  67108864  
innodb_log_file_size    805306368  
innodb_log_files_in_group       4  
innodb_log_group_home_dir       ./  
innodb_max_dirty_pages_pct      75  
innodb_max_purge_lag    0  
innodb_mirrored_log_groups      1  
innodb_old_blocks_pct   37  
innodb_old_blocks_time  0  
innodb_open_files       300  
innodb_print_all_deadlocks      OFF  
innodb_purge_batch_size 20  
innodb_purge_threads    0 

innodb_random_read_ahead        OFF  
innodb_read_ahead_threshold     56  
innodb_read_io_threads  4  
innodb_replication_delay        0  
innodb_rollback_on_timeout      OFF  
innodb_rollback_segments        128  
innodb_spin_wait_delay  6  
innodb_stats_method     nulls_equal  
innodb_stats_on_metadata        ON  
innodb_stats_sample_pages       8  
innodb_strict_mode      OFF  
innodb_support_xa       ON       
innodb_sync_spin_loops  30  
innodb_table_locks      OFF      
innodb_thread_concurrency       128  
innodb_thread_sleep_delay       10000  
innodb_use_native_aio   OFF      
innodb_use_sys_malloc   ON        
innodb_version  5.5.36 

innodb_write_io_threads 4 

Edit:

This server is a backup server and is mostly read only. Read frequency is also very low. But the queries are such that it wants to access a months data from a table.

mysql> show table status like 'TABLE_NAME'\G

Name: TABLE_NAME
Rows: 456194631
Avg_row_length: 60
Data_length: 27472723968
Index_length: 14432813056
Data_free: 14381219840

This table has data of 6 months and query is like :

select col1 from TABLE_NAME where col2 = 4 and Time_1 BETWEEN "2014-12-01" and "2014-12-30".

BTREE index is created on both col2 and Time_1.

explain query Output :

possible_keys = Time_1,Col2
key used = Col2
key_len = 3
ref = const
rows = 98126
Extra = Using where

But this query takes more than 20 minutes to execute. What can be done to improve the performance?

Answer 1

SUGGESTION #1

Since you are having issues with swap and looking at your current InnoDB settings, I would suggest one of the following settings:

innodb_buffer_pool_instances=2 # If your Server is DualCore
innodb_buffer_pool_instances=4 # If your Server is QuadCore
innodb_buffer_pool_instances=6 # If your Server is HexaCore
innodb_buffer_pool_size=90G

I addressed InnoDB and Swapping in one of my older posts (How do you tune MySQL for a heavy InnoDB workload?). I referred to Jeremy Cole's blog The MySQL “swap insanity” problem and the effects of the NUMA architecture. You might need to increase innodb_buffer_pool_size to 60%-75% of RAM. In your case, I would go with 90G (70.3125%).

SUGGESTION #2

For more CPU engagement I recommend these

innodb_read_io_threads=8
innodb_write_io_threads=16
innodb_thread_concurrency=0

I discuss why in my old posts

• May 26, 2011 : About single threaded versus multithreaded databases performance
• Sep 12, 2011 : Possible to make MySQL use more than one core?

OTHER SUGGESTIONS

• You might more RAM on the DB Server (Setting innodb_buffer_pool_size to 60-75% of RAM)
• You might need to tune your queries if you read lots of unnecessary data as indicated by the Innodb_buffer_pool_read_ahead_evicted status variable.