1

I have a setup with MySQL 5.7 and the following configuration:

[mysqld]
innodb_buffer_pool_size=2GB
innodb_buffer_pool_instances=2

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

bind-address = 0.0.0.0

symbolic-links=0

sql-mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 3

log_error=/var/log/mysql/mysqld.log

[mysqld_safe]
pid-file=/var/run/mysqld/mysqld.pid

Because I have innodb_buffer_pool_size=2GB, I would expect MySQL to not grow much more than 2GB, maybe 3GB, but I saw that it reached 4.8GB. If I run free -m it shows:

              total        used        free      shared  buff/cache   available
Mem:          15576       10180         159          14        5236        5055
Swap:             0           0           0

The buff/cache shows almost the same amount as available, so I don't think the excess of memory used from MySQL is cached (from the system PoV). For now it's still ok, but if it grows too large it could cause OOM. It's running in a Linux AMI 2 machine (AWS).

Is there a way to force MySQL to not use more than 4GB?

PS: I also run other services in the same machine, so I wouldn't want MySQL to take more than 4GB, to avoid OOM.

UPDATE (2020-07-13)

Below is the output of SHOW GLOBAL STATUS:

Aborted_clients 0   
Aborted_connects    0   
Binlog_cache_disk_use   0   
Binlog_cache_use    0   
Binlog_stmt_cache_disk_use  0   
Binlog_stmt_cache_use   0   
Bytes_received  12240674099 
Bytes_sent  152333451613    
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_instance  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   18  
Com_binlog  0   
Com_call_procedure  0   
Com_change_db   3342    
Com_change_master   0   
Com_change_repl_filter  0   
Com_check   0   
Com_checksum    0   
Com_commit  2087098 
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  10994   
Com_delete_multi    303 
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_explain_other   0   
Com_flush   2   
Com_get_diagnostics 0   
Com_grant   0   
Com_ha_close    0   
Com_ha_open 0   
Com_ha_read 0   
Com_help    0   
Com_insert  3772593 
Com_insert_select   10760   
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   0   
Com_purge_before_date   0   
Com_release_savepoint   18  
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    4398    
Com_rollback_to_savepoint   1962    
Com_savepoint   18  
Com_select  15018049    
Com_set_option  4195720 
Com_signal  0   
Com_show_binlog_events  0   
Com_show_binlogs    1   
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   1963    
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 4324    
Com_show_function_code  0   
Com_show_function_status    0   
Com_show_grants 1   
Com_show_keys   209 
Com_show_master_status  0   
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 1   
Com_show_storage_engines    0   
Com_show_table_status   1962    
Com_show_tables 26  
Com_show_triggers   1962    
Com_show_variables  44  
Com_show_warnings   326 
Com_show_create_user    0   
Com_shutdown    0   
Com_slave_start 0   
Com_slave_stop  0   
Com_group_replication_start 0   
Com_group_replication_stop  0   
Com_stmt_execute    0   
Com_stmt_close  0   
Com_stmt_fetch  0   
Com_stmt_prepare    0   
Com_stmt_reset  0   
Com_stmt_send_long_data 0   
Com_truncate    0   
Com_uninstall_plugin    0   
Com_unlock_tables   18  
Com_update  4711974 
Com_update_multi    3821620 
Com_xa_commit   0   
Com_xa_end  0   
Com_xa_prepare  0   
Com_xa_recover  0   
Com_xa_rollback 0   
Com_xa_start    0   
Com_stmt_reprepare  0   
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 1736    
Created_tmp_disk_tables 7425    
Created_tmp_files   442 
Created_tmp_tables  845525  
Delayed_errors  0   
Delayed_insert_threads  0   
Delayed_writes  0   
Flush_commands  1   
Handler_commit  21954024    
Handler_delete  5366008 
Handler_discover    0   
Handler_external_lock   116727051   
Handler_mrr_init    0   
Handler_prepare 0   
Handler_read_first  231297  
Handler_read_key    529681239   
Handler_read_last   6668    
Handler_read_next   3625938089  
Handler_read_prev   11366292    
Handler_read_rnd    57337396    
Handler_read_rnd_next   2421984397  
Handler_rollback    3940    
Handler_savepoint   18  
Handler_savepoint_rollback  1962    
Handler_update  9960970 
Handler_write   27983599    
Innodb_buffer_pool_dump_status  Dumping of buffer pool not started  
Innodb_buffer_pool_load_status  Buffer pool(s) load completed at 200711  3:45:34    
Innodb_buffer_pool_resize_status        
Innodb_buffer_pool_pages_data   122894  
Innodb_buffer_pool_bytes_data   2013495296  
Innodb_buffer_pool_pages_dirty  12925   
Innodb_buffer_pool_bytes_dirty  211763200   
Innodb_buffer_pool_pages_flushed    8874905 
Innodb_buffer_pool_pages_free   2080    
Innodb_buffer_pool_pages_misc   6098    
Innodb_buffer_pool_pages_total  131072  
Innodb_buffer_pool_read_ahead_rnd   0   

Innodb_buffer_pool_read_ahead   7494430 
Innodb_buffer_pool_read_ahead_evicted   45318   
Innodb_buffer_pool_read_requests    10158601964 
Innodb_buffer_pool_reads    25338501    
Innodb_buffer_pool_wait_free    18330   
Innodb_buffer_pool_write_requests   206640760   
Innodb_data_fsyncs  4923321 
Innodb_data_pending_fsyncs  0   
Innodb_data_pending_reads   0   
Innodb_data_pending_writes  0   
Innodb_data_read    545999639040    
Innodb_data_reads   33325597    
Innodb_data_writes  13116680    
Innodb_data_written 301227037696    
Innodb_dblwr_pages_written  8881776 
Innodb_dblwr_writes 335348  
Innodb_log_waits    0   
Innodb_log_write_requests   16677148    
Innodb_log_writes   3779568 
Innodb_os_log_fsyncs    3831375 
Innodb_os_log_pending_fsyncs    0   
Innodb_os_log_pending_writes    0   
Innodb_os_log_written   10126928384 
Innodb_page_size    16384   
Innodb_pages_created    92064   
Innodb_pages_read   33325471    
Innodb_pages_written    8883985 
Innodb_row_lock_current_waits   0   
Innodb_row_lock_time    16982   
Innodb_row_lock_time_avg    42  
Innodb_row_lock_time_max    788 
Innodb_row_lock_waits   403 
Innodb_rows_deleted 5366000 
Innodb_rows_inserted    3804575 
Innodb_rows_read    6269149444  
Innodb_rows_updated 7334997 
Innodb_num_open_files   290 
Innodb_truncated_status_writes  0   
Innodb_available_undo_logs  128 
Key_blocks_not_flushed  0   
Key_blocks_unused   6694    
Key_blocks_used 4   
Key_read_requests   16  
Key_reads   7   
Key_write_requests  0   
Key_writes  0   
Locked_connects 0   
Max_execution_time_exceeded 0   
Max_execution_time_set  0   
Max_execution_time_set_failed   0   
Max_used_connections    28  
Max_used_connections_time   2020-07-12 19:20:22 
Not_flushed_delayed_rows    0   
Ongoing_anonymous_transaction_count 0   
Open_files  7   
Open_streams    0   
Open_table_definitions  324 
Open_tables 2000    
Opened_files    809 
Opened_table_definitions    324 
Opened_tables   20195   
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_index_stat_lost  0   
Performance_schema_locker_lost  0   
Performance_schema_memory_classes_lost  0   
Performance_schema_metadata_lock_lost   0   
Performance_schema_mutex_classes_lost   0   
Performance_schema_mutex_instances_lost 0   
Performance_schema_nested_statement_lost    0   
Performance_schema_prepared_statements_lost 0   
Performance_schema_program_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_table_lock_stat_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  1031832 
Qcache_hits 0   
Qcache_inserts  0   
Qcache_lowmem_prunes    0   
Qcache_not_cached   15015015    
Qcache_queries_in_cache 0   
Qcache_total_blocks 1   

Queries 33651424    
Questions   33651423        
Select_full_join    1254    
Select_full_range_join  480 
Select_range    2049165 
Select_range_check  0   
Select_scan 187990  
Slave_open_temp_tables  0   
Slow_launch_threads 0   
Slow_queries    405 
Sort_merge_passes   3291    
Sort_range  283769  
Sort_rows   24436609    
Sort_scan   462574  
Ssl_accept_renegotiates 0   
Ssl_accepts 325 
Ssl_callback_cache_hits 0   
Ssl_cipher      
Ssl_cipher_list     
Ssl_client_connects 0   
Ssl_connect_renegotiates    0   
Ssl_ctx_verify_depth    18446744073709551615    
Ssl_ctx_verify_mode 5   
Ssl_default_timeout 0   
Ssl_finished_accepts    325 
Ssl_finished_connects   0   
Ssl_server_not_after    Feb 14 18:37:43 2030 GMT    
Ssl_server_not_before   Feb 17 18:37:43 2020 GMT    
Ssl_session_cache_hits  0   
Ssl_session_cache_misses    0   
Ssl_session_cache_mode  SERVER  
Ssl_session_cache_overflows 128 
Ssl_session_cache_size  128 
Ssl_session_cache_timeouts  0   
Ssl_sessions_reused 0   
Ssl_used_session_cache_entries  90  
Ssl_verify_depth    0   
Ssl_verify_mode 0   
Ssl_version     
Table_locks_immediate   147 
Table_locks_waited  0   
Table_open_cache_hits   55091760    
Table_open_cache_misses 20195   
Table_open_cache_overflows  18188   
Tc_log_max_pages_used   0   
Tc_log_page_size    0   
Tc_log_page_waits   0   
Threads_cached  7   
Threads_connected   17  
Threads_created 99  
Threads_running 1   
Uptime  191286  
Uptime_since_flush_status   191286  
validate_password_dictionary_file_last_parsed   2020-07-11 03:45:29 
validate_password_dictionary_file_words_count   0   

The following link has the results of SHOW GLOBAL VARIABLES: https://pastebin.com/UgW8s9zh

InnoDB Status: https://pastebin.com/5YAcesr4

Slow log summary: https://pastebin.com/ZctAU96P

Update (2020-07-15)

Output of ulimit -a:

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 62164
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 4096
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

My main request here is a way to define an upper limit to MySQL, independently of the number of requests or CPU and other processes. Just define a hard limit so that MySQL doesn't ask for more than that. I tried to accomplish it with innodb_buffer_pool_size (which I thought would be similar to java Xmx, defining a max memory, although it could be exceeded, but not so much).

I assumed it since, from the MySQL docs:

On a dedicated database server, you might set the buffer pool size to 80% of the machine's physical memory size. Be aware of the following potential issues when configuring buffer pool size, and be prepared to scale back the size of the buffer pool if necessary.

[...]

  • InnoDB reserves additional memory for buffers and control structures, so that the total allocated space is approximately 10% greater than the specified buffer pool size.

[...]

But in my case it's about 150% the size allocated for the buffer pool size (2G to 5G, a difference of about 3G). Maybe it's because only a small amount of the server memory is used for MySQL and so it tries to use more, but if that's the case, how can I limit it?

Like I said before, my main concern is limiting MySQL to not use more memory than an upper limit, not only in the general case, but even (and specially) in atypical ones, to avoid OOM (that's why I prefer a solution that doesn't rely on CPU used, average number of processes, slow logs, top processes and the like, because a solution based on those would probably be a short term solution, considering that they may change even if I don't make any changes to the server).

It doesn't need to be a MySQL specific solution, it could be a linux command to define an upper limit for the MySQL memory, so that MySQL sees the host as having that stipulated amount of RAM and asks and uses only that, instead of the total amount in the host. This is just an example of what could be a solution, the main point here is define a fixed upper limit of memory to avoid OOM, using a reliable way to accomplish it, as long as this is possible (hopefully it is).

17
  • There are dozens of other buffers, caches and corresponding variables which affect MySQL memory consumption. Each session and each running query needs memory. Please add output of show global status and variables to the question description. Jul 10 '20 at 19:52
  • I updated the question with the output of SHOW GLOBAL STATUS and SHOW GLOBAL VARIABLES, but because it went above the limit of 30.000 characters, I removed sql_mode (it is already defined in the conf file), optimizer_switch, optimizer_trace_features and the validate_password_ variables (these password variables shouldn't be related to memory consumption). Jul 13 '20 at 12:36
  • do you have any long running transactions? it looks like your server writes a lot of data. Could you please add output of "show engine innodb status" too? Jul 13 '20 at 15:34
  • Because of the lack of space I posted the output in PasteBin, and I also added a summary of our slow logs (2020-07-08), the slowest are during backup (root user) and others from the timers user that run some background scheduled tasks (most of them that are in the slow logs are about 5s, but some takes more time). Jul 13 '20 at 17:42
  • I don't know if the slow logs should be considered to define a limit for the memory (because that can change with time, so I wouldn't like to have a surprise later on). It could be used to define the appropriate value for the limit, though. If there is a way to define a fixed amount of memory for mysql, it would be the best scenario (I can do that if the service is in a docker container, for example, but in the case of this question it's running directly in the host). Jul 13 '20 at 17:44
1

Rate Per Second = RPS

Suggestions to consider for your Parameter group to reduce RAM requirements

max_connections=64  # from 151 default since max_used_connections was 28 in 2 days
read_rnd_buffer_size=64K  # from 256K to reduce handler_read_rnd_next RPS of 12,662
query_cache_size=0  # from 1M to conserve RAM since query_cache_type=OFF
query_cache_limit=0  # from 1M to conserve RAM since query_cache_type=OFF
query_cache_min_res_unit=512  # from 4096 to use minimum size

You may require AWS assistance with max_connections lowering via a support ticket.

To limit size of MySQL some other talent may provide suggestions.

These changes will lower your MySQL RAM footprint visible in htop or top reporting.

For additional suggestions, view profile, Network profile for contact info.

1
  • I will give it a look, although not at the moment because I need to test in a stage environment the changes first and see if everything is ok. Thanks for your suggestion. Jul 16 '20 at 17:29

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.