Would my MySQL database benefit from increasing the InnoDB buffer pool size?

Question

Of course, the answer is pretty much always "yes", but I'm interested in my specific situation.

This is similar to Making sense of INNODB buffer pool stats but, IMO, a little more specific.

First of all, I'm running MySQL 5.5 but will be moving up to 5.6 somewhat shortly. As far as I know, there is nothing significantly different between these two versions, so the advice should be roughly the same either way.

I have 64GiB server memory, and the server exists almost entirely for MySQL. I currently have:

innodb_buffer_pool_size=38G
innodb_buffer_pool_instances=19

I manually-warm the server each time it comes up because I know there are a few tables which are consulted quite often and benefit from being present in the buffer pool. Once warmed, the buffer pool reports only maybe 10-20% utilization. After a few days, the pool usage reaches 100%.

As I understand it, any time the buffer pool is not at 100%, memory is essentially being wasted. If the database doesn't fit completely into RAM (and mine doesn't), then anything less than 100% would be bad. But is 100% itself bad? Should I increase the buffer pool size even more? What about when I get to the practical limit with my hardware... maybe around 60GiB/64GiB? If I'm at 100%, does that mean that I necessarily have to expand my RAM?

Here are some stats from the server which has been running happily for quite some time (current up-time is `122 days 13 hours 37 min 49 sec):

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 41758490624; in additional pool allocated 0
Dictionary memory allocated 2031279
Buffer pool size   2490349
Free buffers       240
Database pages     2192757
Old database pages 809045
Modified db pages  1553
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 11604834, not young 0
4.16 youngs/s, 0.00 non-youngs/s
Pages read 4213650, created 898538, written 141481897
0.50 reads/s, 0.32 creates/s, 26.45 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2192757, unzip_LRU len: 0
I/O sum[26220]:cur[0], unzip sum[0]:cur[0]

Reading the above-referenced DBA/SE question, the currently-accepted answer basically says that if the hit rate is high (like 1000/1000 in my case), then all is well.

On the other hand, MySQLTuner has this to say:

[!!] InnoDB buffer pool / data size: 38.0G/108.8G
[OK] InnoDB Read buffer efficiency: 100.00% (667137634143 hits/ 667141256884 total)
[!!] InnoDB Write Log efficiency: 56.15% (53957444 hits/ 96087926 total)
[OK] InnoDB log waits: 0.00% (0 waits / 150045370 writes)

So reads seem fine, and the buffer pool doesn't help at all with writes. (The write-log efficiency is very likely due to the fact that I have an hilariously low 5MiB.)

So does everything look good, here? Or would I benefit significantly from expanding the buffer pool even more?

Update 2018-09-01

I have some more information for anyone reading this.

First, my CPU is quite happy under "load", hovering around 10-15% utilization across all 12 CPU cores. Individual CPUs seem to be under 50% utilization under load, and some CPUs are doing practically nothing. I appear to have plenty of CPU to spare. MySQL seems entirely IO-bound on this system, which is currently running SATA SSDs. We definitely have room to grow if necessary.

Complete output of SHOW GLOBAL STATUS:

+------------------------------------------+---------------+
| Variable_name                            | Value         |
+------------------------------------------+---------------+
| Aborted_clients                          | 1693          |
| Aborted_connects                         | 19            |
| Binlog_cache_disk_use                    | 9350          |
| Binlog_cache_use                         | 90526920      |
| Binlog_stmt_cache_disk_use               | 0             |
| Binlog_stmt_cache_use                    | 102760        |
| Bytes_received                           | 741440710519  |
| Bytes_sent                               | 2330763165310 |
| Com_admin_commands                       | 652023569     |
| Com_assign_to_keycache                   | 0             |
| Com_alter_db                             | 0             |
| Com_alter_db_upgrade                     | 1             |
| Com_alter_event                          | 0             |
| Com_alter_function                       | 0             |
| Com_alter_procedure                      | 0             |
| Com_alter_server                         | 0             |
| Com_alter_table                          | 113           |
| Com_alter_tablespace                     | 0             |
| Com_analyze                              | 0             |
| Com_begin                                | 28            |
| Com_binlog                               | 0             |
| Com_call_procedure                       | 1             |
| Com_change_db                            | 10            |
| Com_change_master                        | 0             |
| Com_check                                | 268           |
| Com_checksum                             | 0             |
| Com_commit                               | 10676104      |
| Com_create_db                            | 1             |
| Com_create_event                         | 0             |
| Com_create_function                      | 0             |
| Com_create_index                         | 0             |
| Com_create_procedure                     | 2             |
| Com_create_server                        | 0             |
| Com_create_table                         | 44            |
| Com_create_trigger                       | 0             |
| Com_create_udf                           | 0             |
| Com_create_user                          | 0             |
| Com_create_view                          | 0             |
| Com_dealloc_sql                          | 24            |
| Com_delete                               | 1074437       |
| Com_delete_multi                         | 0             |
| Com_do                                   | 0             |
| Com_drop_db                              | 1             |
| Com_drop_event                           | 0             |
| Com_drop_function                        | 0             |
| Com_drop_index                           | 0             |
| Com_drop_procedure                       | 4             |
| Com_drop_server                          | 0             |
| Com_drop_table                           | 1             |
| Com_drop_trigger                         | 0             |
| Com_drop_user                            | 0             |
| Com_drop_view                            | 0             |
| Com_empty_query                          | 0             |
| Com_execute_sql                          | 24            |
| Com_flush                                | 130           |
| Com_grant                                | 0             |
| Com_ha_close                             | 0             |
| Com_ha_open                              | 0             |
| Com_ha_read                              | 0             |
| Com_help                                 | 3             |
| Com_insert                               | 112556804     |
| Com_insert_select                        | 764124        |
| Com_install_plugin                       | 0             |
| Com_kill                                 | 9             |
| Com_load                                 | 0             |
| Com_lock_tables                          | 0             |
| Com_optimize                             | 5             |
| Com_preload_keys                         | 0             |
| Com_prepare_sql                          | 24            |
| Com_purge                                | 0             |
| Com_purge_before_date                    | 0             |
| Com_release_savepoint                    | 0             |
| Com_rename_table                         | 0             |
| Com_rename_user                          | 0             |
| Com_repair                               | 0             |
| Com_replace                              | 9448          |
| Com_replace_select                       | 0             |
| Com_reset                                | 0             |
| Com_resignal                             | 0             |
| Com_revoke                               | 0             |
| Com_revoke_all                           | 0             |
| Com_rollback                             | 10794957      |
| Com_rollback_to_savepoint                | 0             |
| Com_savepoint                            | 0             |
| Com_select                               | 797113543     |
| Com_set_option                           | 41389732      |
| Com_signal                               | 0             |
| Com_show_authors                         | 0             |
| Com_show_binlog_events                   | 0             |
| Com_show_binlogs                         | 0             |
| Com_show_charsets                        | 1             |
| Com_show_collations                      | 282541        |
| Com_show_contributors                    | 0             |
| Com_show_create_db                       | 0             |
| Com_show_create_event                    | 0             |
| Com_show_create_func                     | 0             |
| Com_show_create_proc                     | 0             |
| Com_show_create_table                    | 5             |
| Com_show_create_trigger                  | 0             |
| Com_show_databases                       | 101           |
| Com_show_engine_logs                     | 0             |
| Com_show_engine_mutex                    | 0             |
| Com_show_engine_status                   | 3             |
| Com_show_events                          | 0             |
| Com_show_errors                          | 0             |
| Com_show_fields                          | 20568         |
| Com_show_function_status                 | 0             |
| Com_show_grants                          | 0             |
| Com_show_keys                            | 38146952      |
| Com_show_master_status                   | 0             |
| Com_show_open_tables                     | 0             |
| Com_show_plugins                         | 3             |
| Com_show_privileges                      | 0             |
| Com_show_procedure_status                | 0             |
| Com_show_processlist                     | 198           |
| Com_show_profile                         | 0             |
| Com_show_profiles                        | 0             |
| Com_show_relaylog_events                 | 0             |
| Com_show_slave_hosts                     | 2             |
| Com_show_slave_status                    | 4             |
| Com_show_status                          | 481905        |
| Com_show_storage_engines                 | 3             |
| Com_show_table_status                    | 10            |
| Com_show_tables                          | 109           |
| Com_show_triggers                        | 0             |
| Com_show_variables                       | 431041        |
| Com_show_warnings                        | 8             |
| Com_slave_start                          | 0             |
| Com_slave_stop                           | 0             |
| Com_stmt_close                           | 24            |
| Com_stmt_execute                         | 24            |
| Com_stmt_fetch                           | 0             |
| Com_stmt_prepare                         | 24            |
| Com_stmt_reprepare                       | 0             |
| Com_stmt_reset                           | 0             |
| Com_stmt_send_long_data                  | 0             |
| Com_truncate                             | 0             |
| Com_uninstall_plugin                     | 0             |
| Com_unlock_tables                        | 0             |
| Com_update                               | 8715420       |
| Com_update_multi                         | 160406        |
| Com_xa_commit                            | 0             |
| Com_xa_end                               | 0             |
| Com_xa_prepare                           | 0             |
| Com_xa_recover                           | 0             |
| Com_xa_rollback                          | 0             |
| Com_xa_start                             | 0             |
| Compression                              | OFF           |
| Connections                              | 5240587       |
| Created_tmp_disk_tables                  | 7746864       |
| Created_tmp_files                        | 40834         |
| Created_tmp_tables                       | 146517652     |
| Delayed_errors                           | 0             |
| Delayed_insert_threads                   | 0             |
| Delayed_writes                           | 0             |
| Flush_commands                           | 2             |
| Handler_commit                           | 2225650354    |
| Handler_delete                           | 2513929       |
| Handler_discover                         | 0             |
| Handler_prepare                          | 258039138     |
| Handler_read_first                       | 5335209       |
| Handler_read_key                         | 106045918332  |
| Handler_read_last                        | 9             |
| Handler_read_next                        | 328378371619  |
| Handler_read_prev                        | 120           |
| Handler_read_rnd                         | 3024880351    |
| Handler_read_rnd_next                    | 41307678157   |
| Handler_rollback                         | 1915470       |
| Handler_savepoint                        | 0             |
| Handler_savepoint_rollback               | 0             |
| Handler_update                           | 76207796      |
| Handler_write                            | 2981602720    |
| Innodb_buffer_pool_pages_data            | 2194942       |
| Innodb_buffer_pool_bytes_data            | 35961929728   |
| Innodb_buffer_pool_pages_dirty           | 131           |
| Innodb_buffer_pool_bytes_dirty           | 2146304       |
| Innodb_buffer_pool_pages_flushed         | 149210414     |
| Innodb_buffer_pool_pages_free            | 17            |
| Innodb_buffer_pool_pages_misc            | 295390        |
| Innodb_buffer_pool_pages_total           | 2490349       |
| Innodb_buffer_pool_read_ahead_rnd        | 0             |
| Innodb_buffer_pool_read_ahead            | 135971        |
| Innodb_buffer_pool_read_ahead_evicted    | 955           |
| Innodb_buffer_pool_read_requests         | 704130093976  |
| Innodb_buffer_pool_reads                 | 3768682       |
| Innodb_buffer_pool_wait_free             | 0             |
| Innodb_buffer_pool_write_requests        | 1010990531    |
| Innodb_data_fsyncs                       | 269329521     |
| Innodb_data_pending_fsyncs               | 0             |
| Innodb_data_pending_reads                | 0             |
| Innodb_data_pending_writes               | 0             |
| Innodb_data_read                         | 71935987712   |
| Innodb_data_reads                        | 4390704       |
| Innodb_data_writes                       | 360055349     |
| Innodb_data_written                      | 5021614685696 |
| Innodb_dblwr_pages_written               | 149210414     |
| Innodb_dblwr_writes                      | 51073994      |
| Innodb_have_atomic_builtins              | ON            |
| Innodb_log_waits                         | 0             |
| Innodb_log_write_requests                | 101591974     |
| Innodb_log_writes                        | 158807817     |
| Innodb_os_log_fsyncs                     | 159790383     |
| Innodb_os_log_pending_fsyncs             | 0             |
| Innodb_os_log_pending_writes             | 0             |
| Innodb_os_log_written                    | 131810433024  |
| Innodb_page_size                         | 16384         |
| Innodb_pages_created                     | 950701        |
| Innodb_pages_read                        | 4390495       |
| Innodb_pages_written                     | 149210414     |
| Innodb_row_lock_current_waits            | 0             |
| Innodb_row_lock_time                     | 6219927       |
| Innodb_row_lock_time_avg                 | 650           |
| Innodb_row_lock_time_max                 | 51855         |
| Innodb_row_lock_waits                    | 9558          |
| Innodb_rows_deleted                      | 2512888       |
| Innodb_rows_inserted                     | 113153043     |
| Innodb_rows_read                         | 425620156257  |
| Innodb_rows_updated                      | 8210549       |
| Innodb_truncated_status_writes           | 0             |
| Key_blocks_not_flushed                   | 0             |
| Key_blocks_unused                        | 12864         |
| Key_blocks_used                          | 13396         |
| Key_read_requests                        | 1238110859    |
| Key_reads                                | 23827152      |
| Key_write_requests                       | 311403712     |
| Key_writes                               | 23962043      |
| Last_query_cost                          | 0.000000      |
| Max_used_connections                     | 45            |
| Not_flushed_delayed_rows                 | 0             |
| Open_files                               | 106           |
| Open_streams                             | 0             |
| Open_table_definitions                   | 286           |
| Open_tables                              | 600           |
| Opened_files                             | 31033981      |
| Opened_table_definitions                 | 1166          |
| Opened_tables                            | 2138          |
| Performance_schema_cond_classes_lost     | 0             |
| Performance_schema_cond_instances_lost   | 0             |
| Performance_schema_file_classes_lost     | 0             |
| Performance_schema_file_handles_lost     | 0             |
| Performance_schema_file_instances_lost   | 0             |
| Performance_schema_locker_lost           | 0             |
| Performance_schema_mutex_classes_lost    | 0             |
| Performance_schema_mutex_instances_lost  | 0             |
| Performance_schema_rwlock_classes_lost   | 0             |
| Performance_schema_rwlock_instances_lost | 0             |
| Performance_schema_table_handles_lost    | 0             |
| Performance_schema_table_instances_lost  | 0             |
| Performance_schema_thread_classes_lost   | 0             |
| Performance_schema_thread_instances_lost | 0             |
| Prepared_stmt_count                      | 0             |
| Qcache_free_blocks                       | 1856          |
| Qcache_free_memory                       | 6474208       |
| Qcache_hits                              | 1953817508    |
| Qcache_inserts                           | 363888531     |
| Qcache_lowmem_prunes                     | 162106761     |
| Qcache_not_cached                        | 432856579     |
| Qcache_queries_in_cache                  | 7147          |
| Qcache_total_blocks                      | 16243         |
| Queries                                  | 3636998091    |
| Questions                                | 2980040458    |
| Rpl_status                               | AUTH_MASTER   |
| Select_full_join                         | 32510         |
| Select_full_range_join                   | 1074987       |
| Select_range                             | 19633284      |
| Select_range_check                       | 0             |
| Select_scan                              | 50014427      |
| Slave_heartbeat_period                   | 0.000         |
| Slave_open_temp_tables                   | 0             |
| Slave_received_heartbeats                | 0             |
| Slave_retried_transactions               | 0             |
| Slave_running                            | OFF           |
| Slow_launch_threads                      | 0             |
| Slow_queries                             | 2316          |
| Sort_merge_passes                        | 23601         |
| Sort_range                               | 33648691      |
| Sort_rows                                | 4809913781    |
| Sort_scan                                | 83532099      |
| Ssl_accept_renegotiates                  | 0             |
| Ssl_accepts                              | 0             |
| Ssl_callback_cache_hits                  | 0             |
| Ssl_cipher                               |               |
| Ssl_cipher_list                          |               |
| Ssl_client_connects                      | 0             |
| Ssl_connect_renegotiates                 | 0             |
| Ssl_ctx_verify_depth                     | 0             |
| Ssl_ctx_verify_mode                      | 0             |
| Ssl_default_timeout                      | 0             |
| Ssl_finished_accepts                     | 0             |
| Ssl_finished_connects                    | 0             |
| Ssl_session_cache_hits                   | 0             |
| Ssl_session_cache_misses                 | 0             |
| Ssl_session_cache_mode                   | Unknown       |
| Ssl_session_cache_overflows              | 0             |
| Ssl_session_cache_size                   | 0             |
| Ssl_session_cache_timeouts               | 0             |
| Ssl_sessions_reused                      | 0             |
| Ssl_used_session_cache_entries           | 0             |
| Ssl_verify_depth                         | 0             |
| Ssl_verify_mode                          | 0             |
| Ssl_version                              |               |
| Table_locks_immediate                    | 2068275041    |
| Table_locks_waited                       | 17            |
| Tc_log_max_pages_used                    | 0             |
| Tc_log_page_size                         | 0             |
| Tc_log_page_waits                        | 137           |
| Threads_cached                           | 6             |
| Threads_connected                        | 22            |
| Threads_created                          | 1073          |
| Threads_running                          | 3             |
| Uptime                                   | 11107125      |
| Uptime_since_flush_status                | 11107125      |
+------------------------------------------+---------------+

Output of MySQLTuner (minus missing plugins, etc.):

 >>  MySQLTuner 1.7.10 - Major Hayden <[email protected]>
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA 
[--] Data in MyISAM tables: 352.4M (Tables: 26)
[--] Data in InnoDB tables: 109.5G (Tables: 219)
[OK] Total fragmented tables: 0

-------- Performance Metrics
[--] Up for: 128d 13h 22m 6s (2B q [268.298 qps], 5M conn, TX: 2170G, RX: 690G)
[--] Reads / Writes: 86% / 14%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory     : 63.0G
[--] Max MySQL memory    : 38.5G
[--] Other process memory: 422.3M
[--] Total buffers: 38.1G global + 2.7M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 38.2G (60.57% of installed RAM)
[OK] Maximum possible memory usage: 38.5G (61.01% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (2K/2B)
[OK] Highest usage of available connections: 29% (45/151)
[OK] Aborted connections: 0.00%  (20/5240615)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 71.0% (1B cached / 2B selects)
[!!] Query cache prunes per day: 1261000
[OK] Sorts requiring temporary tables: 0% (23K temp sorts / 117M sorts)
[!!] Joins performed without indexes: 32510
[OK] Temporary tables created on disk: 5% (7M on disk / 146M total)
[OK] Thread cache hit rate: 99% (1K created / 5M connections)
[OK] Table cache hit rate: 28% (600 open / 2K opened)
[OK] Open file limit used: 7% (106/1K)
[OK] Table locks acquired immediately: 99% (2B immediate / 2B locks)
[OK] Binlog cache memory access: 99.99% (90518299 Memory / 90527649 Total)

-------- MyISAM Metrics
[!!] Key buffer used: 21.5% (3M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/55.3M
[OK] Read Key buffer hit rate: 98.1% (1B cached / 23M reads)
[!!] Write Key buffer hit rate: 7.7% (311M cached / 23M writes)

-------- InnoDB Metrics
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 38.0G/109.5G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (0.0256990131578947 %): 5.0M * 2/38.0G should be equal 25%
[!!] InnoDB buffer pool instances: 19
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (704129278359 hits/ 704133047059 total)
[!!] InnoDB Write Log efficiency: 56.32% (57216580 hits/ 101592706 total)
[OK] InnoDB log waits: 0.00% (0 waits / 158809286 writes)

-------- Recommendations
General recommendations:
    Adjust your join queries to always utilize indexes
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_size (> 16M)
    join_buffer_size (> 128.0K, or always use indexes with JOINs)
    innodb_buffer_pool_size (>= 109.5G) if possible.
    innodb_log_file_size should be (=4G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
    innodb_buffer_pool_instances(=38)

ulimits:

Limit                     Soft Limit           Hard Limit
Max cpu time              unlimited            unlimited
Max file size             unlimited            unlimited
Max data size             unlimited            unlimited
Max stack size            8388608              unlimited
Max core file size        0                    unlimited
Max resident set          unlimited            unlimited
Max processes             515758               515758
Max open files            1361                 1361
Max locked memory         65536                65536
Max address space         unlimited            unlimited
Max file locks            unlimited            unlimited
Max pending signals       515758               515758
Max msgqueue size         819200               819200
Max nice priority         0                    0
Max realtime priority     0                    0
Max realtime timeout      unlimited            unlimited

I won't have enough space to post my my.cnf or some of the other stats, and I'd prefer to have as much information directly in the post as possible so it doesn't get auto-purged by e.g. pastebin. What in particular can I post that will be helpful? My InnoDB configuration isn't much more complicated than what I've already posted.

Answer 1

No, increase in innodb_buffer_pool_size would be helpful.

When innodb_data_reads / uptime result is < 1.00

there is no need to increase I_b_p_s. Your result is .39 which means you read less than one time per second for any table in your entire system.

If you want to make your server FASTER anyway, get in touch with me by Skype, please.

Answer 2

Buffer_pool size

If too large, it is wasting RAM, but not performance.

If too small and the working set is small enough, then "caching" make it so that the size does not matter much.

If too small and the working set is too big, then I/O will hurt performance. This especially happens when UUIDs are involved.

Bottom line: Focusing on the buffer_pool_size is only one part of the performance formula.

What is important?

Looking at the "slow" queries is more important. Minimizing the number of disk hits is more important. This leads to a smaller buffer_pool.

That is, back up to the queries to figure out what to do.

Getting GLOBAL STATUS

Don't use SELECT to get the STATUS values; SELECT has modifies the results. Do use pastbin (or other tool) for posting the data.

More on getting the variables/status, plus finding the 'worst' queries: http://mysql.rjweb.org/doc.php/mysql_analysis . I look at about 200 formulas, and usually find about 20 that are worth worrying about.

Status without the Variables is not very useful.

Query Cache

Most production systems should have the QC turned completely off (size=0 and type=0). Having it turned on costs something like 11% performance on simple queries.

Production systems often involve changes to tables -- this involves purging entries in the QC. Note that you have about 15 prunes/second. This may be more costly than the benefit.

60/64?

It might or might not be safe to set the buffer_pool too close to the RAM size. Swapping is much worse than having a slightly smaller buffer_pool.

CPU

The CPU seems high for a mere 268 qps. This may imply poor indexes, or other query problems. The slowlog should help isolating the problem.

Uptime = 128 days

Has the activity been the 'same' for that long? Or are things getting more active? I bring this up because any conclusions from VARIABLES and GLOBAL STATUS may be out of date.

Answer 3

Your ulimit report indicates Max open files are 1361,

ulimit -n 16384 would enable more open files

for this value to persist over shutdown/restart, please review this link

https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/

Rate Per Second=RPS

Suggestions to consider for your my.cnf [mysqld] section

table_open_cache=2000  # from 600 to reduce opened_tables count
query_cache_min_res_unit=512  # from 4096 to conserve RAM within QC and reduce prunes per day
key_cache_age_threshold-7200  # from 300 seconds to reduce key_reads RPS
key_cache_division_limit=50  # from 100 for HOT/WARM caches
key_cache_block_size=16384  # from 1024 to minimize CPU cycles used for KC mgmt.

The BEST to you, complete SHOW GLOBAL VARIABLES; would be helpful for analysis.