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).
SHOW GLOBAL STATUSandSHOW GLOBAL VARIABLES, but because it went above the limit of 30.000 characters, I removedsql_mode(it is already defined in the conf file),optimizer_switch,optimizer_trace_featuresand thevalidate_password_variables (these password variables shouldn't be related to memory consumption). – Lucas Basquerotto Jul 13 '20 at 12:36timersuser that run some background scheduled tasks (most of them that are in the slow logs are about 5s, but some takes more time). – Lucas Basquerotto Jul 13 '20 at 17:42httpdprocesses (it helped us find some spikes of memory due to httpd, that although each process wasn't using much memory, there were a lot, and we solved reducing the maximum number of connections). In the case ofmysqld, it's only 1 process. It has been working fine for years for us and it seems very precise, we run every minute in a cronjob. – Lucas Basquerotto Jul 14 '20 at 12:00SHOW FULL PROCESSLIST: https://pastebin.com/iNwfrqtw The SSH commands I may see another day. But like I said, I would prefer to limit the amount of memory used, to fine tune mysql I can do later (something that works only for a specified amount of cpu, ram or processes, may stop working in the future, my concern at the moment is only OOM, more than performance). – Lucas Basquerotto Jul 14 '20 at 20:32ulimit -a. – Lucas Basquerotto Jul 15 '20 at 17:48too open files. In the case of MySQL I don't see a relation between one thing and another. We also monitor the number of opened file descriptors per process and MySQL has about 300 to 400 opened files (much less than the minimum). It stayed between 319 and 331 in our daily logs of the last 7 days. – Lucas Basquerotto Jul 16 '20 at 15:56