0

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.

  • Please also provide SHOW VARIABLES;. Many of the formulas for tuning involve both GLOBAL STATUS and VARIABLES, plus sometimes RAM size. – Rick James Sep 7 '15 at 18:43
  • @RickJames Ok! Sharing it in the body of the question exceeded the number of characters allowed, show I've done it through a link. – miganml Sep 8 '15 at 8:45
1

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.

  • I have no rights to modify certain parameters. About your proposal, I can change only innodb_buffer_pool_size and innodb_buffer_pool_instances. I activated the slow query_log and it begins loging queries when long_query_time=0.3. I have checked all tables in the db and they almost all include two indexes or more having at least one index always. I don't think I need optimizing them, do you? – miganml Sep 16 '15 at 9:37
  • If they are "too slow", it is worth look at them. Provide SHOW CREATE TABLE and EXPLAIN SELECT .... – Rick James Sep 16 '15 at 12:07
0

The important thing first is what you're trying to fix? Do you have high pageload times on your wordpress site? Do you have write operations piling up? Isn't RDS instance a shared database? Do you have the ability to change the config variables?

The question is quite broad and without actual usage pattern, table structures etc. it's hard to say something specific.

A couple of things pop out immediately which are easy fixes.

1) Assuming you're using InnoDB (the status variables indicate you do): your innodb_log_file_size is way too low even for a test database. I would recommend it to bump it up especially if you're application is write heavy as your question suggests. Although your status variables show differently. The com_select is a couple of order of magnitude higher than write operations.

Here's a link about how to determine optimal file size: https://www.percona.com/blog/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/

2) The other thing there is your queries are not well indexed. Handler_read_rnd_next is ~20x higher than Handler_read_next. So most of your results are returned via full scan. That could also cause very high IO but you say your data size is somewhere around 150Mb and the buffer pool is above 2GB so everything should fit into RAM.

Turn on slow query logging and you can use pt-query-digest from percona toolkit (https://www.percona.com/software/mysql-tools/percona-toolkit) to have a more human readable format and start checking your queries and indexes.

Some tools to have better visibility:

1) I would also recommend to run mysqltuner.pl and see what it produces: http://mysqltuner.com It's a very handy tool for seeing where you have opportunities to improve.

There are definitely further improvements you can do but starting with the big wins first and fine-tune afterwards.

2) You can turn on performance schema and see where your high IO originates. On 5.5 it's quite limited but still there are some nice things in there. The structure not easy to read at first so I would recommend to install Mark Leith's Sys schema: https://github.com/MarkLeith/mysql-sys/ which provides better views.

  • The big problem I have to face is the stability of the website. Two weeks ago MySQL server was installed on the same instance as Apache and something went wrong . Innodb_buffer_pool ran out of memory often and stopped working. Now, in the new instance, I see the db consumes so much memory (2.2~2.5 Gb). So first, I won't like it ran out of memory and second, I would like to use a smaller instance with just 2GiB of RAM. A better performance in website loads is also desired. I'm going to work the points you put. Thank you! – miganml Sep 8 '15 at 13:59
0

If your DB is 150MB, having 2GB for innodb is too much. Unless you think your database will grow to 2GB in less than 6 month, I would limit the amount of memory to 200 MB Max. It looks like you have query using Myisam tables. Run the following query to find out your biggest tables:

SELECT table_name, TABLE_COLLATION as 'collation',ENGINE as 'engine', 
 round( data_length / ( 1024 *1024 ) , 2 )  as 'data_length', round( index_length / ( 1024 *1024 ) , 2 )   as 'index_length',   round((data_length + index_length ) / ( 1024 *1024 ) , 2 )   as 'TotalSize',TABLE_ROWS
 FROM information_schema.TABLES WHERE   ENGINE in ('MyISAM','InnoDB') GROUP BY 6

If some of the tables are Myisam, I would recommend increase key_buffer_size.Can you share your my.cnf config? Thanks.

  • Before, I had some InnoDB and some MyIsam tables. Since 10 days ago, I migrated every table to InnoDB with ALTER TABLE table_name ENGINE=INNODB. Probably I have configs not apropiated for InnoDB. As an Amazon RDS user I have not direct access to my.cnf file. However, I can share the parameter-group variables I can see from RDS CLI. – miganml Sep 9 '15 at 10:07
  • @miganml - Other issues to be aware of in converting to InnoDB: mysql.rjweb.org/doc.php/myisam2innodb – Rick James Sep 16 '15 at 12:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.