I am working in the optimization of a MySQL WordPress database running in a RDS instance at Amazon AWS. The instance is a t2.medium machine with 4GiB of RAM and 2vCPU. The size of the DB in a sql file is arount 150Mb.
The point is the RDS uses at least 2.2 GiB of RAM regularly and I'd like to reduce the memory consumption.
But what I really care about is that write IOPS go between 5 to 50 normally, reaching peaks of 150-200 IOPS.
What should I do to improve the database work?
As I have been told in a recent question, I paste for you the output of
SHOW GLOBAL STATUS;
+-----------------------------------------------+-------------+
| Variable_name | Value |
+-----------------------------------------------+-------------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 27 |
| Binlog_cache_use | 50407 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
| Bytes_received | 994972242 |
| Bytes_sent | 46587265927 |
| Com_admin_commands | 0 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 0 |
| Com_alter_tablespace | 0 |
| Com_alter_user | 0 |
| Com_analyze | 0 |
| Com_begin | 17 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 18752 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 575 |
| Com_create_db | 0 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_server | 0 |
| Com_create_table | 0 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 11815 |
| Com_delete_multi | 12 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_procedure | 0 |
| Com_drop_server | 0 |
| Com_drop_table | 0 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 0 |
| Com_execute_sql | 0 |
| Com_flush | 565 |
| Com_get_diagnostics | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 14389 |
| Com_insert_select | 0 |
| Com_install_plugin | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 561 |
| Com_purge_before_date | 0 |
| Com_release_savepoint | 0 |
| Com_rename_table | 0 |
| Com_rename_user | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| 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 | 21040989 |
| Com_set_option | 38899 |
| Com_signal | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 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 | 464 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 0 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 0 |
| Com_show_events | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 19436 |
| Com_show_function_code | 0 |
| Com_show_function_status | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 79 |
| Com_show_master_status | 2 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 0 |
| Com_show_privileges | 0 |
| Com_show_procedure_code | 0 |
| Com_show_procedure_status | 0 |
| Com_show_processlist | 0 |
| Com_show_profile | 0 |
| Com_show_profiles | 0 |
| Com_show_relaylog_events | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 536 |
| Com_show_storage_engines | 0 |
| Com_show_table_status | 490 |
| Com_show_tables | 2 |
| Com_show_triggers | 464 |
| Com_show_variables | 1 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reprepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 2 |
| Com_update | 25542 |
| 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 |
| Connection_errors_accept | 0 |
| Connection_errors_internal | 0 |
| Connection_errors_max_connections | 0 |
| Connection_errors_peer_address | 0 |
| Connection_errors_select | 0 |
| Connection_errors_tcpwrap | 0 |
| Connections | 18824 |
| Created_tmp_disk_tables | 142660 |
| Created_tmp_files | 13190 |
| Created_tmp_tables | 372268 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 21050496 |
| Handler_delete | 36746 |
| Handler_discover | 0 |
| Handler_external_lock | 42993534 |
| Handler_mrr_init | 0 |
| Handler_prepare | 98100 |
| Handler_read_first | 18265756 |
| Handler_read_key | 110268061 |
| Handler_read_last | 30 |
| Handler_read_next | 131798473 |
| Handler_read_prev | 992980 |
| Handler_read_rnd | 84859948 |
| Handler_read_rnd_next | 2629821769 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 25829 |
| Handler_write | 8871457 |
| Innodb_buffer_pool_dump_status | not started |
| Innodb_buffer_pool_load_status | not started |
| Innodb_buffer_pool_pages_data | 16931 |
| Innodb_buffer_pool_bytes_data | 277397504 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 141104 |
| Innodb_buffer_pool_pages_free | 150318 |
| Innodb_buffer_pool_pages_misc | 815 |
| Innodb_buffer_pool_pages_total | 168064 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 3723 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 2988503434 |
| Innodb_buffer_pool_reads | 12394 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 705060 |
| Innodb_data_fsyncs | 219523 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 264769536 |
| Innodb_data_reads | 16408 |
| Innodb_data_writes | 291496 |
| Innodb_data_written | 4726494720 |
| Innodb_dblwr_pages_written | 141104 |
| Innodb_dblwr_writes | 55790 |
| Innodb_have_atomic_builtins | ON |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 130348 |
| Innodb_log_writes | 80013 |
| Innodb_os_log_fsyncs | 94607 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 95331840 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 776 |
| Innodb_pages_read | 16155 |
| Innodb_pages_written | 141104 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 2304 |
| Innodb_row_lock_time_avg | 13 |
| Innodb_row_lock_time_max | 1082 |
| Innodb_row_lock_waits | 166 |
| Innodb_rows_deleted | 36746 |
| Innodb_rows_inserted | 13822 |
| Innodb_rows_read | 2822924238 |
| Innodb_rows_updated | 25241 |
| Innodb_num_open_files | 245 |
| Innodb_truncated_status_writes | 0 |
| Innodb_available_undo_logs | 128 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 13396 |
| Key_blocks_used | 15 |
| Key_read_requests | 340974 |
| Key_reads | 12 |
| Key_write_requests | 334159 |
| Key_writes | 0 |
| Last_query_cost | 0.000000 |
| Last_query_partial_plans | 0 |
| Max_used_connections | 19 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 42 |
| Open_streams | 0 |
| Open_table_definitions | 318 |
| Open_tables | 1951 |
| Opened_files | 589397 |
| Opened_table_definitions | 362 |
| Opened_tables | 3054 |
| Performance_schema_accounts_lost | 0 |
| Performance_schema_cond_classes_lost | 0 |
| Performance_schema_cond_instances_lost | 0 |
| Performance_schema_digest_lost | 0 |
| Performance_schema_file_classes_lost | 0 |
| Performance_schema_file_handles_lost | 0 |
| Performance_schema_file_instances_lost | 0 |
| Performance_schema_hosts_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_session_connect_attrs_lost | 0 |
| Performance_schema_socket_classes_lost | 0 |
| Performance_schema_socket_instances_lost | 0 |
| Performance_schema_stage_classes_lost | 0 |
| Performance_schema_statement_classes_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 |
| Performance_schema_users_lost | 0 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031352 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 21040965 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
| Queries | 21192414 |
| Questions | 21191878 |
| Select_full_join | 238 |
| Select_full_range_join | 0 |
| Select_range | 444673 |
| Select_range_check | 0 |
| Select_scan | 18296318 |
| Slave_heartbeat_period | 0.000 |
| Slave_last_heartbeat | |
| Slave_open_temp_tables | 0 |
| Slave_received_heartbeats | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 6583 |
| Sort_range | 115573 |
| Sort_rows | 84854835 |
| Sort_scan | 357811 |
| 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_server_not_after | |
| Ssl_server_not_before | |
| 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 | 21495307 |
| Table_locks_waited | 0 |
| Table_open_cache_hits | 21513194 |
| Table_open_cache_misses | 3054 |
| Table_open_cache_overflows | 1075 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 9 |
| Threads_connected | 2 |
| Threads_created | 28 |
| Threads_running | 1 |
| Uptime | 168203 |
| Uptime_since_flush_status | 168203 |
+-----------------------------------------------+-------------+
341 rows in set (0.00 sec)
Thank you in advance.
Edit:
Here I share the output of SHOW GLOBAL VARIABLES
, as Rick James asked.
Best Answer
Observations
Version: 5.5.43-0ubuntu0.14.04.1
3.5 GB of RAM
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.
Summary (details below)
Change to
innodb_buffer_pool_size = 1200M
because it is bigger than needed, and don't want to let swapping occur in your VM.innodb_buffer_pool_instances = 1
Turn off the Query cache.
Find the slow queries and fix them.
Details
( innodb_buffer_pool_size / _ram ) = 2,147,483,648 / 3584M = 57.1% -- % of RAM used for InnoDB buffer_pool
( Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size ) = 150,318 * 16384 / 2147483648 = 114.7% -- buffer pool free
-- (No, I don't know how it could more than 100% free.)
-- The buffer_pool may be too big for a 4G VM. If you are swapping, decrease the setting.
( innodb_file_per_table ) = OFF -- Put each file in its own tablespace
-- (Mildly recommended, especially for large tables)
( innodb_stats_on_metadata ) = ON -- Re-analyze table when touching stats.
-- ON is likely to slow down certain SHOWs and information_schema accesses.
( Qcache_not_cached ) = 21,040,965 / 168203 = 125 /sec -- SQL_CACHE attempted, but ignored
( Qcache_not_cached / (Qcache_hits + Com_select + Qcache_not_cached) ) = 21,040,965 / (0 + 21040989 + 21040965) = 50.0% -- Percent of SELECTs that were not cached in the QC.
( Qcache_hits / Qcache_inserts ) = 0 / 0 = 0 -- Hit to insert ratio -- high is good
( Qcache_hits / (Qcache_hits + Com_select) ) = 0 / (0 + 21040989) = 0 -- Hit ratio -- SELECTs that used QC
-- QC is not very useful.
-- Turn off the query cache (query_cache_type = OFF & query_cache_size = 0)
( Created_tmp_disk_tables / (Created_tmp_disk_tables + Created_tmp_tables) ) = 142,660 / (142660 + 372268) = 27.7% -- Percent of temp tables that spilled to disk
-- Improve indexes, increase tmp_table_size and max_heap_table_size; avoid blobs, etc.
( Select_scan ) = 18,296,318 / 168203 = 108 /sec -- full table scans
( Select_scan / Com_select ) = 18,296,318 / 21040989 = 87.0% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries (unless they are tiny tables)
( slow_query_log ) = OFF -- Whether to log slow queries. (5.1.12)
-- Turn on
( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2
-- After a while, run pt-query-digest against the slowlog to see what needs the most fixing.
( Com_purge ) = 12/HR
-- Why are you PURGEing every 5 minutes?
( innodb_buffer_pool_instances ) = 10
-- You should use 1 for the size you have.
( Open_tables / table_open_cache ) = 488%
-- Does the STATUS not match the VARIABLES? This is an 'impossible' value.
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 154 minutes
-- Contrary to what is in another answer, I claim that the log_file_size is big enough. The logs are rotated quite infrequently.