Optimizing MYSQL Configuration for InnoDb

Question

After long and useful discussion with Mr. Rick James about how to change to innoDb , and after one week of changing a big table from myisam to innoDb i feel there is som improvements can be don, and i am seeking for community advice.

Please note :

Mysql 5.5

Mysql on SSD Drive.

Please Advice .

+++++++++++++++++++++++++++

Now let me show you what i have :

let's start with the table in question:

CREATE TABLE `status` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `md5id` char(32) CHARACTER SET ascii NOT NULL DEFAULT '0',
 `ptype` char(1) CHARACTER SET ascii NOT NULL DEFAULT '0',
 `media_id` varchar(50) CHARACTER SET ascii NOT NULL DEFAULT '0',
 `media_url` varchar(100) CHARACTER SET ascii NOT NULL,
 `media_photo` varchar(255) CHARACTER SET ascii NOT NULL,
 `user_id` varchar(50) CHARACTER SET ascii NOT NULL DEFAULT '0',
 `user_name` varchar(100) COLLATE utf8_swedish_ci NOT NULL DEFAULT '0',
 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `reason` text COLLATE utf8_swedish_ci,
 PRIMARY KEY (`id`),
 KEY `user_id_md5id` (`user_id`,`md5id`),
 KEY `md5id_mediaid_ptype` (`md5id`,`media_id`,`ptype`),
 KEY `md5id_ptype_date` (`md5id`,`ptype`,`date`),
 KEY `md5_user_id_ptype` (`md5id`,`user_id`,`ptype`),
 KEY `md5_id` (`md5id`)
) ENGINE=InnoDB AUTO_INCREMENT=11717643 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci

this table has now (11,929,637 record) and the size of the table is 10.1 GiB

the Slow Query i have is :

EXPLAIN SELECT * FROM status  WHERE md5id='4d40640b8dcf37c5e2348e91677a8b8c'  ORDER BY `id` DESC LIMIT 0, 18

1   SIMPLE  status  ref md5id_mediaid_ptype,md5id_ptype_date,md5_user_id_ptype,md5_id   md5id_ptype_date    32  const   14436   Using where; Using filesort 

and

EXPLAIN SELECT
                count(1)
 AS `counter`
FROM `status` `s1`
LEFT JOIN (`status`) on(((`status`.`user_id` = `s1`.`user_id`)
                                AND (`status`.`ptype` = 'u')
                                AND (`status`.`md5id` = 'e4011cdd03ab543b2268a6acce51e8b4')))
WHERE ((`s1`.`md5id` = 'e4011cdd03ab543b2268a6acce51e8b4')
       AND (`s1`.`ptype` = 'f')
       AND  status.md5id IS NULL
       AND (`s1`.`date` < ((now() - interval 2 DAY))))



1   SIMPLE  s1  range   md5id_mediaid_ptype,md5id_ptype_date,md5_user_id_ptype,md5_id   md5id_ptype_date    37      1448    Using where 
1   SIMPLE  status  ref user_id_md5id,md5id_mediaid_ptype,md5id_ptype_date,md5_user_id_ptype,md5_id user_id_md5id   84  myinstar_ranker.s1.user_id,const    1   Using where; Not exists 

Now Let go to the server specification

Server Ram

root@server [/output]# free -h
             total       used       free     shared    buffers     cached
Mem:           62G        60G       1.8G       2.4G       770M        43G
-/+ buffers/cache:        16G        45G
Swap:         1.9G       1.0G       914M
root@server [/output]#

CPU

model name      : Intel(R) Xeon(R) CPU E5-2620 0 @ 2.00GHz × 2
cpu MHz         : 2000.022
cache size      : 15360 KB

My.cnf

[mysqld]
table_definition_cache = 800
table_open_cache = 2000
open_files_limit= 50000

innodb_buffer_pool_instances=16
innodb_buffer_pool_size=30G
innodb_log_file_size=512M
innodb_file_per_table=1
innodb_stats_on_metadata = OFF

max_allowed_packet=268435456

myisam_sort_buffer_size=256M
sort_buffer_size=4M

thread_cache_size = 30
key_buffer_size=1G
default_storage_engine=InnoDB

join_buffer_size = 256k
tmp_table_size = 512M
max_heap_table_size = 512M
max_connections = 600

slow-query-log = 1
slow-query-log-file = /output/mysql-slow.log
long_query_time = 2
datadir=/disk3/mysql
query_cache_type = OFF
query_cache_size = 0

and here is mysql> SHOW GLOBAL STATUS;

mysql> SHOW GLOBAL STATUS;
+------------------------------------------+--------------+
| Variable_name                            | Value        |
+------------------------------------------+--------------+
| Aborted_clients                          | 5322         |
| Aborted_connects                         | 2566         |
| Bytes_received                           | 7604570459   |
| Bytes_sent                               | 565788289502 |
| Com_admin_commands                       | 8879         |
| Com_assign_to_keycache                   | 0            |
| Com_alter_table                          | 2622         |
| Com_alter_tablespace                     | 0            |
| Com_analyze                              | 0            |
| Com_begin                                | 347          |
| Com_binlog                               | 0            |
| Com_call_procedure                       | 0            |
| Com_change_db                            | 101660       |
| Com_change_master                        | 0            |
| Com_check                                | 43           |
| Com_checksum                             | 0            |
| Com_commit                               | 0            |
| Com_create_db                            | 1            |
| Com_create_table                         | 1754         |
| Com_delete                               | 553932       |
| Com_empty_query                          | 9270         |
| Com_flush                                | 11           |
| Com_grant                                | 10           |
| Com_ha_close                             | 0            |
| Com_ha_open                              | 0            |
| Com_ha_read                              | 0            |
| Com_help                                 | 0            |
| Com_insert                               | 1252280      |
| Com_insert_select                        | 2            |
| Com_install_plugin                       | 0            |
| Com_kill                                 | 0            |
| Com_load                                 | 0            |
| Com_lock_tables                          | 570          |
| Com_optimize                             | 0            |
| Com_preload_keys                         | 0            |
| Com_replace                              | 6873         |
| Com_replace_select                       | 0            |
| Com_reset                                | 0            |
| Com_resignal                             | 0            |
| Com_revoke                               | 0            |
| Com_revoke_all                           | 0            |
| Com_rollback                             | 0            |
| Com_rollback_to_savepoint                | 0            |
| Com_savepoint                            | 0            |
| Com_select                               | 36080433     |
| Com_set_option                           | 1593014      |
| Com_signal                               | 0            |
| Com_show_authors                         | 0            |
| Com_show_binlog_events                   | 0            |
| Com_show_binlogs                         | 3            |
| Com_show_charsets                        | 0            |
| Com_show_client_statistics               | 0            |
| Com_show_collations                      | 0            |
| Com_show_contributors                    | 0            |
| Com_show_create_db                       | 345          |
| Com_show_create_event                    | 0            |
| Com_show_create_func                     | 0            |
| Com_show_create_proc                     | 0            |
| Com_show_create_table                    | 14384        |
| Com_show_create_trigger                  | 0            |
| Com_show_databases                       | 167          |
| Com_show_engine_logs                     | 0            |
| Com_show_engine_mutex                    | 0            |
| Com_show_engine_status                   | 0            |
| Com_show_events                          | 345          |
| Com_show_errors                          | 0            |
| Com_show_fields                          | 80488        |
| Com_show_function_status                 | 467          |
| Com_show_grants                          | 2232         |
| Com_show_index_statistics                | 0            |
| Com_show_keys                            | 139          |
| Com_show_master_status                   | 4            |
| Com_show_open_tables                     | 0            |
| Com_show_plugins                         | 0            |
| Com_show_privileges                      | 0            |
| Com_show_procedure_status                | 467          |
| Com_show_processlist                     | 755          |
| Com_show_profile                         | 0            |
| Com_show_profiles                        | 0            |
| Com_show_relaylog_events                 | 0            |
| Com_show_slave_hosts                     | 1            |
| Com_show_slave_status                    | 5            |
| Com_show_status                          | 4            |
| Com_show_storage_engines                 | 1            |
| Com_show_table_statistics                | 0            |
| Com_show_table_status                    | 29377        |
| Com_show_tables                          | 162321       |
| Com_show_thread_statistics               | 0            |
| Com_show_triggers                        | 14328        |
| Com_show_user_statistics                 | 0            |
| Com_show_variables                       | 114          |
| Com_show_warnings                        | 14           |
| Com_slave_start                          | 0            |
| Com_slave_stop                           | 0            |
| Com_stmt_close                           | 3920         |
| Com_stmt_execute                         | 1080860      |
| Com_stmt_fetch                           | 0            |
| Com_stmt_prepare                         | 3969         |
| Com_stmt_reprepare                       | 31           |
| Com_stmt_reset                           | 0            |
| Com_stmt_send_long_data                  | 0            |
| Com_truncate                             | 70           |
| Com_uninstall_plugin                     | 0            |
| Com_unlock_tables                        | 936          |
| Com_update                               | 4294537      |
| Com_update_multi                         | 0            |
| 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                              | 1460159      |
| Created_tmp_disk_tables                  | 231781       |
| Created_tmp_files                        | 57           |
| Created_tmp_tables                       | 485739       |
| Delayed_errors                           | 0            |
| Delayed_insert_threads                   | 0            |
| Delayed_writes                           | 0            |
| Flush_commands                           | 1            |
| Handler_commit                           | 39574502     |
| Handler_delete                           | 613851       |
| Handler_discover                         | 0            |
| Handler_prepare                          | 0            |
| Handler_read_first                       | 778114       |
| Handler_read_key                         | 658437844    |
| Handler_read_last                        | 52930        |
| Handler_read_next                        | 7223040955   |
| Handler_read_prev                        | 49538168     |
| Handler_read_rnd                         | 20375684     |
| Handler_read_rnd_next                    | 447639325    |
| Handler_rollback                         | 351          |
| Handler_savepoint                        | 0            |
| Handler_savepoint_rollback               | 0            |
| Handler_update                           | 4470545      |
| Handler_write                            | 5101819      |
| Innodb_buffer_pool_pages_data            | 585066       |
| Innodb_buffer_pool_bytes_data            | 9585721344   |
| Innodb_buffer_pool_pages_dirty           | 9090         |
| Innodb_buffer_pool_bytes_dirty           | 148930560    |
| Innodb_buffer_pool_pages_flushed         | 2088070      |
| Innodb_buffer_pool_pages_free            | 1358946      |
| Innodb_buffer_pool_pages_misc            | 22060        |
| Innodb_buffer_pool_pages_total           | 1966072      |
| Innodb_buffer_pool_read_ahead_rnd        | 0            |
| Innodb_buffer_pool_read_ahead            | 38190        |
| Innodb_buffer_pool_read_ahead_evicted    | 0            |
| Innodb_buffer_pool_read_requests         | 11822384418  |
| Innodb_buffer_pool_reads                 | 496564       |
| Innodb_buffer_pool_wait_free             | 0            |
| Innodb_buffer_pool_write_requests        | 42278196     |
| Innodb_data_fsyncs                       | 7608493      |
| Innodb_data_pending_fsyncs               | 0            |
| Innodb_data_pending_reads                | 0            |
| Innodb_data_pending_writes               | 0            |
| Innodb_data_read                         | 8976240640   |
| Innodb_data_reads                        | 551508       |
| Innodb_data_writes                       | 8892650      |
| Innodb_data_written                      | 79100924416  |
| Innodb_dblwr_pages_written               | 2088070      |
| Innodb_dblwr_writes                      | 784069       |
| Innodb_have_atomic_builtins              | ON           |
| Innodb_log_waits                         | 0            |
| Innodb_log_write_requests                | 17412042     |
| Innodb_log_writes                        | 6006220      |
| Innodb_os_log_fsyncs                     | 6019710      |
| Innodb_os_log_pending_fsyncs             | 0            |
| Innodb_os_log_pending_writes             | 0            |
| Innodb_os_log_written                    | 10672267776  |
| Innodb_page_size                         | 16384        |
| Innodb_pages_created                     | 37030        |
| Innodb_pages_read                        | 548036       |
| Innodb_pages_written                     | 2088070      |
| Innodb_row_lock_current_waits            | 0            |
| Innodb_row_lock_time                     | 26727        |
| Innodb_row_lock_time_avg                 | 13           |
| Innodb_row_lock_time_max                 | 1230         |
| Innodb_row_lock_waits                    | 1971         |
| Innodb_rows_deleted                      | 598933       |
| Innodb_rows_inserted                     | 1236489      |
| Innodb_rows_read                         | 7778473186   |
| Innodb_rows_updated                      | 4260391      |
| Innodb_truncated_status_writes           | 0            |
| Key_blocks_not_flushed                   | 0            |
| Key_blocks_unused                        | 856377       |
| Key_blocks_used                          | 12274        |
| Key_read_requests                        | 87030658     |
| Key_reads                                | 58397        |
| Key_write_requests                       | 490822       |
| Key_writes                               | 64119        |
| Last_query_cost                          | 0.000000     |
| Max_used_connections                     | 189          |
| Not_flushed_delayed_rows                 | 0            |
| Open_files                               | 3041         |
| Open_streams                             | 0            |
| Open_table_definitions                   | 1971         |
| Open_tables                              | 2000         |
| Opened_files                             | 1494405      |
| Opened_table_definitions                 | 224527       |
| Opened_tables                            | 240175       |
| 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                      | 2            |
| Qcache_free_blocks                       | 0            |
| Qcache_free_memory                       | 0            |
| Qcache_hits                              | 0            |
| Qcache_inserts                           | 0            |
| Qcache_lowmem_prunes                     | 0            |
| Qcache_not_cached                        | 0            |
| Qcache_queries_in_cache                  | 0            |
| Qcache_total_blocks                      | 0            |
| Queries                                  | 45674940     |
| Questions                                | 45658201     |
| Rpl_status                               | AUTH_MASTER  |
| Select_full_join                         | 24318        |
| Select_full_range_join                   | 271          |
| Select_range                             | 591127       |
| Select_range_check                       | 0            |
| Select_scan                              | 604167       |
| 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                             | 72           |
| Sort_merge_passes                        | 0            |
| Sort_range                               | 734887       |
| Sort_rows                                | 28391767     |
| Sort_scan                                | 239143       |
| 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                   | NONE         |
| 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                    | 43580698     |
| Table_locks_waited                       | 258          |
| Tc_log_max_pages_used                    | 0            |
| Tc_log_page_size                         | 0            |
| Tc_log_page_waits                        | 0            |
| Threads_cached                           | 6            |
| Threads_connected                        | 111          |
| Threads_created                          | 18879        |
| Threads_running                          | 1            |
| Uptime                                   | 134928       |
| Uptime_since_flush_status                | 134928       |
+------------------------------------------+--------------+
317 rows in set (0.00 sec)

and here is mysqltuner.pl , report ,

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1d 13h 18m 42s (45M q [338.307 qps], 1M conn, TX: 524G, RX: 7G)
[--] Reads / Writes: 85% / 15%
[--] Binary logging is disabled
[--] Physical Memory     : 62.7G
[--] Max MySQL memory    : 34.4G
[--] Other process memory: 7.0G
[--] Total buffers: 31.5G global + 4.9M per thread (600 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 32.4G (51.67% of installed RAM)
[OK] Maximum possible memory usage: 34.4G (54.79% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (68/45M)
[OK] Highest usage of available connections: 31% (189/600)
[OK] Aborted connections: 0.18%  (2564/1454236)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 970K sorts)
[!!] Joins performed without indexes: 24282
[!!] Temporary tables created on disk: 47% (230K on disk / 483K total)
[OK] Thread cache hit rate: 98% (18K created / 1M connections)
[!!] Table cache hit rate: 1% (2K open / 169K opened)
[OK] Open file limit used: 5% (2K/50K)
[OK] Table locks acquired immediately: 99% (43M immediate / 43M locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.5% (198M used / 1B cache)
[OK] Key buffer size / total MyISAM indexes: 1.0G/751.4M
[OK] Read Key buffer hit rate: 99.9% (86M cached / 57K reads)
[!!] Write Key buffer hit rate: 13.1% (489K cached / 64K writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 30.0G/10.7G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (3.33333333333333 %): 512.0M * 2/30.0G should be equal 25%
[!!] InnoDB buffer pool instances: 16
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (11766329291 hits/ 11766825830 total)
[!!] InnoDB Write Log efficiency: 65.5% (11348015 hits/ 17326167 total)
[OK] InnoDB log waits: 0.00% (0 waits / 5978152 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /disk3/mysql/server.maximagroup.net.err file
    Control error line(s) into /disk3/mysql/server.maximagroup.net.err file
    Run OPTIMIZE TABLE to defragment tables for better performance
      OPTIMIZE TABLE `modsec`.`hits`; -- can free 2609.17892074585 MB
    Total freed space after theses OPTIMIZE TABLE : 2609.17892074585 Mb
    Restrict Host for user@% to user@SpecificDNSorIp
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: 
    Beware that open_files_limit (50000) variable
    should be greater than table_open_cache (2000)
    Performance shouldn't be activated for MySQL and MariaDB 5.5 and lower version
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
    join_buffer_size (> 256.0K, or always use indexes with joins)
    table_open_cache (> 2000)
    performance_schema = OFF disable PFS
    innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=15G) if possible.
    innodb_buffer_pool_instances(=30)

Answer 1

For the 2nd query, the EXPLAIN estimates that 1448 rows are needed in each probe into status. The first is a "range" that I guess would hit 4-5 blocks. Then the LEFT JOIN (or the possibly equivalent EXISTS, below) would make about 1448 probes. However, these would be more scattered. So, it would be better to have the index, below, to avoid having to hit as many disk blocks.

But... The above analysis is doing "count the disk hits", which is arguably irrelevant because "InnoDB buffer pool / data size: 30.0G/10.7G". That is, eventually everything would be cached.

So... I suggest that those queries were "slow" when the cache was cold, and they have gotten faster over time?? What is long_query_time? Lower it if necessary. Check for "slow" queries after a week.

Does this provide the 'correct' answer? What does it's EXPLAIN look like? (I don't know if it is an improvement over your LEFT JOIN.)

SELECT  count(*) AS `counter`
    FROM  `status` `s1`
    WHERE   NOT EXISTS(
                SELECT  *
                    FROM  status
                    WHERE  user_id = s1.user_id
                      AND  ptype = 'u'
                      AND  md5id = 'e4011cdd03ab543b2268a6acce51e8b4'
                     )
      AND  `s1`.`md5id` = 'e4011cdd03ab543b2268a6acce51e8b4'
      AND  `s1`.`ptype` = 'f'
      AND  `s1`.`date` < (now() - interval 2 DAY) ;

And add

INDEX(`md5id`,`ptype`,`user_id`)

Optionally remove md5_user_id_ptype (unless it is needed elsewhere).

Is some 3rd party software being used?

Off hand, the my.cnf looks ok.

Consider using IODKU instead of REPLACE.

Opened_files is rather large; I don't know what might be fixable for that.

Max_used_connections is 189 -- Can you throttle in the client?

InnoDB buffer pool instances: 16 -- should be fine

What is the value of innodb_log_buffer_size?

Answer 2

I think you will do better with a different index structure. One thing I am pretty sure you should look at is since you have md5id as an index you can drop it from the other indexes which are using it as the lead column. There a lot of reasons for this query to not be performing such as lots of users doing the same self-join on the table. You can't ever ignore storage in this case and its performance. AS you said the cache is going to clear due to the frequency of its use.

I would consider bumping the RAM on the server to 128 to provide a higher innodb_buffer_pool_size. I wouldn't increase the number of pools though. I have had a database with much larger tables and behind a website running only 8 pools and only slightly more in the buffer size at 45GB. The overhead might be less than you think so I would go ahead and enable performance_schema and use it to see how much time is being spent on data from disk. Also, remember if it doesn't affect end user experience then either as the direct contributor or as a blocker then don't expend too much effort on it.