mysql memory
| Parameter | MySQL Default SIZE MB |
|---|---|
| key_buffer_size | |
| + 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 | |
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: cache not buffers table open cache
Mysql table cache 1101 total tables in mysql. and allocated table cache and filedescriptor limits to 30000
has to check out mysql open file limit file descripot increase
suggestion by mysql tuner.
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 open tables vs opened_tables
able 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)
