Re: High memory used by MySQL
Hi Peter, sorry for not being active in last two weeks but I was checking my database like you suggest. Thanks of you now I know much more how to control my database and thank you for that !
Memory is more stable after I use new configuration using formula from you ( we found also few not optimal query and we already fix them ), but still I see that mysql can cross his maximum memory limit.
But when his is above the limit and i use "mysqladmin refresh" command, then he back with memory used to the limit from formula and after some time
he still grow up ( maybe slower, but still ). Do you have idea why ?
formula:
innodb_buffer_pool_size + innodb_ft_total_cache_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + query_cache_size + tmp_table_size + key_buffer_size + max_connections * ( thread_stack + max( max_allowed_packet, net_buffer_length ) + net_buffer_length + read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size )
| innodb_buffer_pool_size | 3221225472 |
| innodb_ft_total_cache_size | 640000000 |
innodb_additional_mem_pool_size
| innodb_log_buffer_size | 16777216 |
| query_cache_size | 0 |
| tmp_table_size | 134217728 |
| key_buffer_size | 524288000 |
| max_connections | 180 |
| thread_stack | 262144 |
| max_allowed_packet | 31457280 |
| net_buffer_length | 16384 |
| read_buffer_size | 524288 |
| read_rnd_buffer_size | 2097152 |
| sort_buffer_size | 524288 |
| join_buffer_size | 524288
3221225472 + 640000000 + 0 + 16777216 + 0 + 134217728 + 524288000 + 180 * ( 262144 + 31457280 + 16384 + 524288 + 2097152 + 524288 + 524288 ) = 10909556736 Bytes = 10.1603 Gigabytes || but now he use 11.4 GB
TODAY:
%Cpu(s): 5.4 us, 0.4 sy, 0.0 ni, 93.2 id, 1.0 wa, 0.0 hi, 0.1 si, 0.0 st
KiB Mem : 16423680 total, 185600 free, 13113776 used, 3124304 buff/cache
KiB Swap: 4194300 total, 3575020 free, 619280 used. 2963224 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
25297 mysql 20 0 15.203g 0.011t 9760 S 11.8 72.8 724:41.58 mysqld
During the last weekend he reach even 13GB
27.02.2020:
%Cpu(s): 5.4 us, 0.4 sy, 0.0 ni, 93.1 id, 1.0 wa, 0.0 hi, 0.1 si, 0.0 st
KiB Mem : 16423680 total, 176276 free, 15296148 used, 951256 buff/cache
KiB Swap: 4194300 total, 3881908 free, 312392 used. 788812 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
19408 mysql 20 0 18.198g 0.013t 7216 S 111.8 84.1 4910:25 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
but after i used "mysqladmin refresh" he back to 9.8 GB and continue slow growing
Current configuration :
#slow_query_log = 1
#long_query_time = 1
#slow_query_log_file = /var/log/mysql/slow-query.log
#log_queries_not_using_indexes = 1
symbolic-links=0
skip-external-locking
key_buffer_size = 500M
max_allowed_packet = 30M
open_files_limit = 5000
table_open_cache = 2048
table_definition_cache = 2048
sort_buffer_size = 512K
read_buffer_size = 512K
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 64M
thread_cache_size = 50
query_cache_size = 0
tmp_table_size = 128M
join_buffer_size = 512K
query_cache_type = 0
innodb_file_per_table = 1
innodb_log_files_in_group = 2
innodb_log_file_size = 650M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_buffer_pool_size = 3G
innodb_buffer_pool_instances = 1
innodb_lock_wait_timeout = 600
max_heap_table_size = 128M
max_connections=180
max_user_connections=200
wait_timeout=600
interactive_timeout=500
long_query_time= 5
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
show global status from today
Variable_name Value
Aborted_clients 4
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 5076369793
Bytes_sent 210552307381
Com_admin_commands 3
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 709406
Com_binlog 0
Com_call_procedure 771131
Com_change_db 13707
Com_change_master 0
Com_change_repl_filter 0
Com_check 0
Com_checksum 0
Com_commit 708762
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 16910
Com_delete_multi 0
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 10690
Com_explain_other 0
Com_flush 6
Com_get_diagnostics 0
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_help 0
Com_insert 1121882
Com_insert_select 27
Com_install_plugin 0
Com_kill 0
Com_load 0
Com_lock_tables 0
Com_optimize 0
Com_preload_keys 0
Com_prepare_sql 10690
Com_purge 0
Com_purge_before_date 0
Com_release_savepoint 125256
Com_rename_table 0
Com_rename_user 0
Com_repair 0
Com_replace 436
Com_replace_select 0
Com_reset 0
Com_resignal 0
Com_revoke 0
Com_revoke_all 0
Com_rollback 571
Com_rollback_to_savepoint 12629
Com_savepoint 125256
Com_select 16570356
Com_set_option 1801483
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 73
Com_show_create_proc 292
Com_show_create_table 25432
Com_show_create_trigger 146
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 63887
Com_show_function_code 0
Com_show_function_status 73
Com_show_grants 1
Com_show_keys 9271
Com_show_master_status 1
Com_show_open_tables 2
Com_show_plugins 1
Com_show_privileges 0
Com_show_procedure_code 0
Com_show_procedure_status 73
Com_show_processlist 1170
Com_show_profile 0
Com_show_profiles 0
Com_show_relaylog_events 0
Com_show_slave_hosts 0
Com_show_slave_status 1
Com_show_status 1180
Com_show_storage_engines 0
Com_show_table_status 12629
Com_show_tables 783
Com_show_triggers 12629
Com_show_variables 903
Com_show_warnings 16
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 10690
Com_stmt_close 4002
Com_stmt_fetch 0
Com_stmt_prepare 10690
Com_stmt_reset 0
Com_stmt_send_long_data 0
Com_truncate 9
Com_uninstall_plugin 0
Com_unlock_tables 73
Com_update 2507657
Com_update_multi 363589
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 1048151
Created_tmp_disk_tables 455622
Created_tmp_files 771
Created_tmp_tables 1711174
Delayed_errors 0
Delayed_insert_threads 0
Delayed_writes 0
Flush_commands 4
Handler_commit 20535225
Handler_delete 211999
Handler_discover 0
Handler_external_lock 69483521
Handler_mrr_init 0
Handler_prepare 0
Handler_read_first 2035474
Handler_read_key 1735580300
Handler_read_last 8302
Handler_read_next 10374540498
Handler_read_prev 104437558
Handler_read_rnd 97091972
Handler_read_rnd_next 5598183382
Handler_rollback 739
Handler_savepoint 125244
Handler_savepoint_rollback 12629
Handler_update 253161943
Handler_write 4010971243
Innodb_buffer_pool_dump_status Dumping of buffer pool not started
Innodb_buffer_pool_load_status Buffer pool(s) load completed at 200228 8:43:57
Innodb_buffer_pool_resize_status
Innodb_buffer_pool_pages_data 180830
Innodb_buffer_pool_bytes_data 2962718720
Innodb_buffer_pool_pages_dirty 1322
Innodb_buffer_pool_bytes_dirty 21659648
Innodb_buffer_pool_pages_flushed 16594046
Innodb_buffer_pool_pages_free 1024
Innodb_buffer_pool_pages_misc 14730
Innodb_buffer_pool_pages_total 196584
Innodb_buffer_pool_read_ahead_rnd 0
Innodb_buffer_pool_read_ahead 1779074
Innodb_buffer_pool_read_ahead_evicted 4643
Innodb_buffer_pool_read_requests 34809699407
Innodb_buffer_pool_reads 5299980
Innodb_buffer_pool_wait_free 21
Innodb_buffer_pool_write_requests 6947118722
Innodb_data_fsyncs 2283074
Innodb_data_pending_fsyncs 1
Innodb_data_pending_reads 0
Innodb_data_pending_writes 0
Innodb_data_read 116373115392
Innodb_data_reads 7103367
Innodb_data_writes 17833039
Innodb_data_written 358845094400
Innodb_dblwr_pages_written 5113561
Innodb_dblwr_writes 219771
Innodb_log_waits 0
Innodb_log_write_requests 3855850
Innodb_log_writes 941010
Innodb_os_log_fsyncs 1009930
Innodb_os_log_pending_fsyncs 1
Innodb_os_log_pending_writes 0
Innodb_os_log_written 3153287680
Innodb_page_size 16384
Innodb_pages_created 277734
Innodb_pages_read 7102846
Innodb_pages_written 16594067
Innodb_row_lock_current_waits 0
Innodb_row_lock_time 1615945
Innodb_row_lock_time_avg 129
Innodb_row_lock_time_max 31176
Innodb_row_lock_waits 12462
Innodb_rows_deleted 465084
Innodb_rows_inserted 2657654323
Innodb_rows_read 14765086168
Innodb_rows_updated 2584464
Innodb_num_open_files 514
Innodb_truncated_status_writes 0
Innodb_available_undo_logs 128
Key_blocks_not_flushed 0
Key_blocks_unused 418422
Key_blocks_used 124
Key_read_requests 1758806
Key_reads 2561
Key_write_requests 2427
Key_writes 2427
Locked_connects 0
Max_execution_time_exceeded 0
Max_execution_time_set 0
Max_execution_time_set_failed 0
Max_used_connections 51
Max_used_connections_time 2020-02-28 21:15:46
Not_flushed_delayed_rows 0
Ongoing_anonymous_transaction_count 0
Open_files 57
Open_streams 0
Open_table_definitions 1000
Open_tables 2048
Opened_files 7061
Opened_table_definitions 2448
Opened_tables 172821
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 0
Qcache_free_memory 0
Qcache_hits 0
Qcache_inserts 0
Qcache_lowmem_prunes 0
Qcache_not_cached 0
Qcache_queries_in_cache 0
Qcache_total_blocks 0
Queries 26057976
Questions 25222696
Select_full_join 85301
Select_full_range_join 10699
Select_range 1695888
Select_range_check 0
Select_scan 1734137
Slave_open_temp_tables 0
Slow_launch_threads 0
Slow_queries 0
Sort_merge_passes 531
Sort_range 229947
Sort_rows 42146383
Sort_scan 1087374
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 18446744073709551615
Ssl_ctx_verify_mode 5
Ssl_default_timeout 0
Ssl_finished_accepts 0
Ssl_finished_connects 0
Ssl_server_not_after Feb 8 08:00:26 2030 GMT
Ssl_server_not_before Feb 11 08:00:26 2020 GMT
Ssl_session_cache_hits 0
Ssl_session_cache_misses 0
Ssl_session_cache_mode SERVER
Ssl_session_cache_overflows 0
Ssl_session_cache_size 128
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 1227484
Table_locks_waited 2293
Table_open_cache_hits 33839275
Table_open_cache_misses 172821
Table_open_cache_overflows 164683
Tc_log_max_pages_used 0
Tc_log_page_size 0
Tc_log_page_waits 0
Threads_cached 19
Threads_connected 2
Threads_created 90
Threads_running 2
Uptime 261647
Uptime_since_flush_status 26164