MySQL Forums
Forum List  »  Performance

Re: High memory used by MySQL
Posted by: Damian Kolany
Date: March 02, 2020 04:12AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
1025
February 12, 2020 02:45AM
676
February 12, 2020 11:41AM
538
February 13, 2020 09:24AM
550
February 13, 2020 11:49AM
511
February 14, 2020 09:05AM
457
February 14, 2020 11:06AM
Re: High memory used by MySQL
477
March 02, 2020 04:12AM
434
March 02, 2020 04:17PM
361
March 02, 2020 08:35PM
407
March 02, 2020 08:36PM
481
March 03, 2020 06:12AM
387
March 03, 2020 11:51AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.