subject: To identify which mysql process / setting using high memory usage.
mysql high memory usage things to check
Every connection = Apporx 100MB memory, >> check active connections(hint CPU usage also raise)
Query cache: disbaled from mysql 8 no worries.
Innodb Memory: it doesn’t go above limit specified
key_buffer_size: if you are not using MYISAM as storage engineer ignore it.
mysql open limit files: deafult 1000 but my case 30000 similar to linux inode limit
mysql open table cache: how many tables in memory
mysql temporary tables memory limit: max_heap_table_size
mysql is multi threaded so htop command shows same percentage for all all child procesess or threads.
Identify: problems with mysqltuner
mysql table cache >> look at this 1st:
| Parameter | MySQL Default SIZE MB |
|---|---|
| key_buffer_size (myisam) | |
| + query_cache_size | |
| + tmp_table_size | |
| + innodb_buffer_pool_size | |
| + innodb_additional_mem_pool_size | |
| + innodb_log_buffer_size | |
| + max_connections | |
| × | |
| sort_buffer_size | |
| + read_buffer_size | |
| + read_rnd_buffer_size | |
| + join_buffer_size | |
| + thread_stack | |
| + binlog_cache_size | |
-
Global Buffer:
key_buffer_size innodb_buffer_pool_size innodb_log_buffer_size innodb_additional_mem_pool_size net_buffer_size -
Thread Buffer:
sort_buffer_size myisam_sort_buffer_size read_buffer_size join_buffer_size read_rnd_buffer_size thread_stack
mysql> SHOW VARIABLES LIKE '%buffer%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_chunk_size | 55574528 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_in_core_file | ON |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 55574528 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_log_buffer_size | 16777216 |
| innodb_sort_buffer_size | 1048576 |
| join_buffer_size | 262144 |
| key_buffer_size | 8388608 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 131072 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 262144 |
| sql_buffer_result | OFF |
+-------------------------------------+----------------+
25 rows in set (0.02 sec)
I
mysql global buffers + thread buffers(connection level)
key_buffer myisam
not more than 25% ram when using myisam engine.
thread_cache_size
new connection being made needs a new thread created for it. When the connections disengage the thread is destroyed.
majority of connections can be made on cached threads.
default value:
mysql max_connections memory usage
128 concurrent connections
table_cache
You might start with a value 2x of max_connections or 2x the total number of tables and then tune from there.
tables opened from disk cached in memory
mysql file descriptor limit should greater than tables limit.
Monitoring MySQL Memory Usage
mysql> SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE ‘%memory%’;
retuns 400+ rows
SELECT * FROM performance_schema.setup_instruments
WHERE NAME LIKE ‘%memory/innodb%’;
reruned 200+ rows
*** MySQL’s maximum memory usage is dangerously high ***
innodb_buffer_pool_size (>= 624.2M) if possible.
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
check exsting innodb log file size and adjust accordigly.
SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
+————————-+———–+
| Variable_name | Value |
+————————-+———–+
| innodb_buffer_pool_size | 134217728 | 128MB
+————————-+———–+
1 row in set (0.01 sec)
Mysql memory usage in linux by htop command
3278M = 3.3GB Memory consuming now as per Htop.
watch mysql processlist
HOW FULL PROCESSLIST;
Mysql and php fpm connection timeout
mysql connection: 3600 seconds or 1 hour.
max_execution_time 300s or 5 mins
Only 1 process running.
To check Active mysql active connections
run mysql connection commands to know current connections
show global status like ‘%conn%’;
Variable_name | Value |
+——————————————————-+———————+
| Aborted_connects | 1 |
| 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 | 158913 |
| Locked_connects | 0 |
| Max_used_connections | 43 |
| Max_used_connections_time | 2021-08-06 15:31:22 |
| Mysqlx_connection_accept_errors | 0 |
| Mysqlx_connection_errors | 5 |
| Mysqlx_connections_accepted | 13 |
| Mysqlx_connections_closed | 13 |
| Mysqlx_connections_rejected | 0 |
| Performance_schema_session_connect_attrs_longest_seen | 116 |
| Performance_schema_session_connect_attrs_lost | 0 |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_finished_connects | 0 |
| Threads_connected | 1 |
+——————————————————-+———————+
22 rows in set (0.03 sec)
innodb_buffer_pool_size
512MB
mysql> SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
+————————-+———–+
| Variable_name | Value |
+————————-+———–+
| innodb_buffer_pool_size | 536870912 |
+————————-+———–+
SHOW VARIABLES LIKE ‘key_buffer_size’;
mysql> SHOW VARIABLES LIKE ‘key_buffer_size’;
+—————–+———+
| Variable_name | Value |
+—————–+———+
| key_buffer_size | 8388608 | 8MB
+—————–+———+
1 row in set (0.00 sec)
SHOW VARIABLES LIKE ‘max_allowed_packet’;
+——————–+———-+
| Variable_name | Value |
+——————–+———-+
| max_allowed_packet | 67108864 | 64mb
+——————–+———-+
tmp_table_size
SHOW VARIABLES LIKE ‘tmp_table_size’;
mysql> SHOW VARIABLES LIKE ‘tmp_table_size’;
+—————-+———–+
| Variable_name | Value |
+—————-+———–+
| tmp_table_size | 134217728 | 134MB
+—————-+———–+
1 row in set (0.00 sec)
Problem identified: open table cache
Find current value of open_tables and opened_tables
mysql> show global status like ‘open%’;
Find out Table cache hit rate
Table cache hit rate = table_open_cache*100/Opened_tables
Calculate the tune value of table_open_cache and set it
Table_open_cache = total_tables*Threads_connected
Table_open_cache = total_tables*Threads_connected*.50
Along with table_open_cache you should also tune open_files_limit system variable.
In general it is 2x of table_open_cache.
open_files_limit= Table_open_cache*2
Mysql table cache 1101 total tables in mysql. and allocated table cache and file descriptor limits to 30000
has to check out mysql open file limit file descripot increase
suggestion by mysql tuner.
open table cache: 2x of max_connections or 2x the total number of tables
mysql> SHOW VARIABLES LIKE ‘max_connections’;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| max_connections | 151 | //default (100mb memory for each connection general maintained mysql)
table_open_cache | 10000
151*2=302
+—————–+——-+
1 row in set (0.00 sec)
mysql> show global variables like ‘%open%’;
+—————————-+——–+
| Variable_name | Value |
+—————————-+——–+
| have_openssl | YES |
| innodb_open_files | 58000 |
| mysqlx_port_open_timeout | 0 |
| open_files_limit | 300000 |
| table_open_cache | 58000 |
| table_open_cache_instances | 16 |
+—————————-+——–+
6 rows in set (0.01 sec)
mysql> show global status like ‘opened_tables’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Opened_tables | 7095 |
+—————+——-+
1 row in set (0.01 sec)
mysql> show global status like ‘open_tables’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Open_tables | 6693 |
+—————+——-+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE ‘table_open_cache’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| table_open_cache | 10000 |
+——————+——-+
1 row in set (0.00 sec)
mysql open tables vs opened_tables table_open_cache
number of opened tables is increasing rapidly, you should look at increasing the table_open_cache value
mysql> show global status like ‘%open%’;
+—————————-+———-+
| Variable_name | Value |
+—————————-+———-+
| Com_ha_open | 0 |
| Com_show_open_tables | 0 |
| Innodb_num_open_files | 456 |
| Mysqlx_cursor_open | 0 |
| Mysqlx_expect_open | 0 |
| Open_files | 370 |
| Open_streams | 0 |
| Open_table_definitions | 639 |
| Open_tables | 6909 |
| Opened_files | 370 |
| Opened_table_definitions | 709 |
| Opened_tables | 7077 |
| Slave_open_temp_tables | 0 |
| Table_open_cache_hits | 13056111 |
| Table_open_cache_misses | 7077 |
| Table_open_cache_overflows | 0 |
+—————————-+———-+
16 rows in set (0.00 sec)
The table_open_cache and max_connections system variables affect the maximum number of files the server keeps open
table cache hit rate: 7% (274 open / 3K opened) since the server start,
opned_tables = from disk
open tables = from memory.
a restart required to effect he stats.
Open_tables | 6694 |
| Opened_files | 243
max_allowed_packet
Total (per session)
innodb_buffer_pool_size
key_buffer_size
Total Memory Needed (for X connection)
To know the current running threads
mysql> SHOW PROCESSLIST;
+——-+—————–+———–+——+———+——–+————— ———+——————+
| Id | User | Host | db | Command | Time | State | Info |
+——-+—————–+———–+——+———+——–+————— ———+——————+
| 5 | event_scheduler | localhost | NULL | Daemon | 145300 | Waiting on emp ty queue | NULL |
| 73165 | root | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST |
+——-+—————–+———–+——+———+——–+————— ———+——————+
2 rows in set (0.00 sec)
show global status like ‘%thread%’;
mysql> show global status like ‘%thread%’;
+——————————————+——-+
| Variable_name | Value |
+——————————————+——-+
| Delayed_insert_threads | 0 |
| Mysqlx_worker_threads | 2 |
| Mysqlx_worker_threads_active | 0 |
| Performance_schema_thread_classes_lost | 0 |
| Performance_schema_thread_instances_lost | 0 |
| Slow_launch_threads | 0 |
| Threads_cached | 49 | //mysql thread cache
| Threads_connected | 1 |
| Threads_created | 84 |
| Threads_running | 2 |
+——————————————+——-+
10 rows in set (0.03 sec)
mysql> SHOW STATUS \G;
displays the name and values of variables that gives you information about the server status
Identify: problems with mysqltuner
-
./mysqltuner.pl
*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
key_buffer_size=0
innodb_buffer_pool_size (>= 803.1M) if possible. (512mb)
innodb_log_file_size should be (=64M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
key_buffer_size=0 (not using myisam as storage engine.)
16M is probably sufficient for any situation.
Buffer Key MyISAM set to 0, no MyISAM table detected
mysql> SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
+————————-+———–+
| Variable_name | Value |
+————————-+———–+
| innodb_buffer_pool_size | 536870912 | (512mb)
mysql> SHOW VARIABLES LIKE ‘innodb_log_file_size’;
+———————-+———–+
| Variable_name | Value |
+———————-+———–+
| innodb_log_file_size | 134217728 | 128mb
+———————-+———–+
1 row in set (0.00 sec)
more at mysql bufferpool size
table_definition_cache(900) > 1100 or -1 (autosizing if supported)
table_definition_cache
| Default Value | -1 (signifies autosizing; do not assign this literal value) |
|---|---|
| Minimum Value | 400 |
| Maximum Value | 524288 |
SHOW VARIABLES LIKE ‘table_definition_cache’;
mysql> SHOW VARIABLES LIKE ‘table_definition_cache’;
+————————+——-+
| Variable_name | Value |
+————————+——-+
| table_definition_cache | 900 |
+————————+——-+
1 row in set (0.00 sec)
table_definition_cache setting acts as a soft limit for the number of table instances in the InnoDB data dictionary cache and the number file-per-table tablespaces that can be open at one time.
