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 <major@mhtx.net>
[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.
Best Answer
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.