Mysql – InnoDB Index Doesn’t Fit in Memory

indexinnodbMySQLmysql-5.5

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?

Best Answer

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.

Related Solutions

InnoDB INSERT Performance Capabilities in MySQL

You need to tune your InnoDB settings in the following areas:

Make InnoDB access all your cores
Increase innodb_buffer_pool_size to 12G
Increase innodb_buffer_pool_instances to 2 (First run numactl --hardware to determine the number of Physical CPUs. What every number of CPUs it reports, use that number. I learned this recently in Jeremy Cole's Blog)
Increase Log File Size (innodb_log_file_size) to 2047M
support separate tablespace files for individual InnoDB tables (enaled innodb_file_per_table)
support either high performance or high durability (ACID Compliance)
- High Performance : innodb_flush_log_at_trx_commit set to 0 or 2
- High Durability : innodb_flush_log_at_trx_commit set to 1 (Default)
- Increase Size up the innodb_log_buffer_size in conjunction with the number of transactions per second (perhaps 32M)
- Your current setting for innodb_flush_log_at_trx_commit is good
- Your current setting for innodb_flush_method is good
Increase innodb_read_io_threads to 64
Increase innodb_write_io_threads to 64
Increase innodb_io_capactity to 10000

Here are my past posts on tuning the InnoDB storage engine

Is the CPU performance relevant for a database server? (Apr 26, 2012)
Why does InnoDB store all databases in one file? (Mar 25, 2012)
Multi cores and MySQL Performance (Sep 20, 2011)
Possible to make MySQL use more than one core? (Sep 12, 2011)
About single threaded versus multithreaded databases performance (May 26, 2011)
How to safely change MySQL innodb variable 'innodb_log_file_size'? (Feb 16, 2011)
How do you tune MySQL for a heavy InnoDB workload? (Feb 12, 2011)
Howto: Clean a mysql InnoDB storage engine? (October 29, 2010)

Mysql – Cannot write to thesql database while select query in process

Your first query is asking for 20,000 rows. what is being victimized here ???

Your Innodb Buffer Pool and Log Files.

Because of SELECT * FROM history WHERE log LIKE '%a%' ORDER BY log ASC LIMIT 0, 20000;, all the data pages for the history table is hogging InnoDB resources.

Since you are using Amazon RDS, you cannot resize the InnoDB Log Files. They are always 128M under all RDS MySQL models. Your buffer pool may or may not be full (You have 5882511360 set for it, which is 5610M or 5.4785G). Naturally, bigger model means more IOPs.

You can run this to see how full the buffer pool is

SELECT FORMAT(A.num * 100.0 / B.num,2) BufferPoolFullPct FROM
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value num FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;

Another aspect I see is the log field (TEXT). You are asking for an ORDER BY on this big column.

You need to change the first query to throttle the SELECT. In other words, fetch smaller chunks of history.

If you cannot change the data or the query, there is nothing you can configure except to shift to m1.xlarge (Comes with 11922309120 as the Buffer Pool Size = 11370M = 11.1035G) although log file size would still be stuck at 128M.

Infrastructure aside, it is possible for SELECTs to block INSERTs, UPDATEs, and DELETEs in InnoDB.

I wrote about this before