ySQL Memory allocation is complicated. There are global buffers, per-connection buffers (which depend on the workload), and some uncontrolled memory allocations (i.e., inside Stored Procedures),
The default configuration is designed to permit a MySQL server to start on a virtual machine that has approximately 512MB of RAM. You can improve MySQL performance by increasing the values of certain cache and buffer-related system variables.
Htop Showing
mysql using1200MB RAM with zero conections
Buffer pool set 256MB but using 266MB slightyly higer
mysql temporary tables memory limit
tmp_table_size ( maximum size for temporary tables in-memory)
max_heap_table_size (maximum size of an user-created table that can be created in memory)
default values 32mb
32M to 64M is the commonly suggested initial value to set tmp_table_size and max_heap_table_size. Important to note, that MySQL will take the LOWER of the two values assigned to these variables.
SHOW VARIABLES LIKE ‘tmp_table_size’;
mysql> SHOW VARIABLES LIKE ‘tmp_table_size’;
+—————-+———-+
| Variable_name | Value |
+—————-+———-+
| tmp_table_size | 33554432 |
+—————-+———-+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE ‘max_heap_table_size’;
+———————+———-+
| Variable_name | Value |
+———————+———-+
| max_heap_table_size | 33554432 |
+———————+———-+
1 row in set (0.01 sec)
MySQL Performance Schema Memory
The Performance Schema dynamically allocates memory incrementally, scaling its memory use to actual server load, instead of allocating required memory during server startup. Once memory is allocated, it is not freed until the server is restarted
Most temporary tables are memory-based hash tables.
The table_open_cache system variable defines the initial table cache size; see Section 8.4.3.1, “How MySQL Opens and Closes Tables”.
MySQL also requires memory for the table definition cache. The table_definition_cache system variable defines the number of table definitions that can be stored in the table definition cache. If you use a large number of tables, you can create a large table definition cache to speed up the opening of tables. The table definition cache takes less space and does not use file descriptors, unlike the table cache.
Conntection level
The connection buffer and result buffer each begin with a size equal to net_buffer_length bytes, but are dynamically enlarged up to max_allowed_packet bytes as needed.
Each request that performs a sequential scan of a table allocates a read buffer. The read_buffer_size system variable determines the buffer size.
The max_binlog_cache_size system variable specifies the upper limit of memory usage by an individual transaction.
The max_binlog_stmt_cache_size system variable specifies the upper limit of memory usage by the statement cache.
FLush_tables to free up memory
FLUSH TABLES does not return until all tables have been closed.
mysql> SHOW VARIABLES LIKE ‘innodb_flush_method’;
+———————+——-+
| Variable_name | Value |
+———————+——-+
| innodb_flush_method | fsync |
+———————+——-+
1 row in set (0.00 sec)
we recommend innodb_flush_method=O_DIRECT in most cases, which won’t use Operating System File Cache.
mysql> SELECT * FROM sys.memory_global_by_current_bytes
-> WHERE event_name LIKE ‘memory/innodb/buf_buf_pool’\G
*************************** 1. row ***************************
event_name: memory/innodb/buf_buf_pool
current_count: 2
current_alloc: 261.76 MiB
current_avg_alloc: 130.88 MiB
high_count: 2
high_alloc: 261.76 MiB
high_avg_alloc: 130.88 MiB
1 row in set (0.00 sec)
mysql> SELECT SUBSTRING_INDEX(event_name,’/’,2) AS
-> code_area, FORMAT_BYTES(SUM(current_alloc))
-> AS current_alloc
-> FROM sys.x$memory_global_by_current_bytes
-> GROUP BY SUBSTRING_INDEX(event_name,’/’,2)
-> ORDER BY SUM(current_alloc) DESC;
+—————————+—————+
| code_area | current_alloc |
+—————————+—————+
| memory/innodb | 360.20 MiB |
| memory/performance_schema | 171.30 MiB |
| memory/sql | 20.21 MiB |
| memory/mysys | 17.54 MiB |
| memory/temptable | 1.00 MiB |
| memory/mysqld_openssl | 828.36 KiB |
| memory/myisam | 18.00 KiB |
| memory/mysqlx | 3.25 KiB |
| memory/csv | 120 bytes |
| memory/blackhole | 120 bytes |
| memory/vio | 80 bytes |
+—————————+—————+
11 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE ‘innodb_buffer_pool_size’;
+————————-+———–+
| Variable_name | Value |
+————————-+———–+
| innodb_buffer_pool_size | 268435456 |
+————————-+———–+
1 row in set (0.00 sec)
mysql table cache
table_open_cache Default Value 2000
Also take into account that the MyISAM storage engine needs two file descriptors for each unique open table. To increase the number of file descriptors available to MySQL, set the open_files_limit system variable.
mysql> SHOW GLOBAL STATUS LIKE ‘Opened_tables’;
+—————+———+
| Variable_name | Value |
+—————+———+
| Opened_tables | 1744571 |
+—————+———+
1 row in set (0.00 sec)
SHOW GLOBAL variables LIKE ‘table_definition_cache’;
SHOW GLOBAL STATUS LIKE ‘Open_tables’;
mysql> SHOW GLOBAL variables LIKE ‘table_open_cache’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| table_open_cache | 100 |
+——————+——-
mysql> SHOW GLOBAL STATUS LIKE ‘Open_tables’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| Open_tables | 96 |
+—————+——-+
1 row in set (0.00 sec)
Table cache hit ratio(%) = (open_tables / opened_tables) x 100
A good hit ratio value should be 90% and above. Otherwise, increase the table_open_cache variable until the hit ratio reaches a good value.
open_tables is the number of tables you have open right now; opened_tables is the total number of table-opening operations since the server started.
mysql> SHOW GLOBAL variables LIKE ‘open_files_limit’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| open_files_limit | 1024 |
+——————+——-+
1 row in set (0.00 sec)
mysql> SHOW GLOBAL variables LIKE ‘table_definition_cache’;
+————————+——-+
| Variable_name | Value |
+————————+——-+
| table_definition_cache | 450 |
+————————+——-+
1 row in set (0.00 sec)
mysql> SHOW STATUS;
+——————————————————-+———————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————+
| Variable_name | Value |
+——————————————————-+———————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————+
| Aborted_clients | 0 |
| Aborted_connects | 9 |
| Acl_cache_items_count | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
| Bytes_received | 1259 |
| Bytes_sent | 39000 |
| Caching_sha2_password_rsa_public_key | —–BEGIN PUBLIC KEY—–
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAugMyPmPcTkYZiv+o/ZGs
B8b7/fSMpgvXiG6YoMy9RWZ1QHRkjeMBuGBRWOdlFGXzLXAw10d4TYCfbd4sIhWd
yUmSt+umOXWdVqV3aA0PtZVGnrZj1+Ule6g5iVEjZwFrqUgMmY3z65r8ACr4EF2V
ftGxJnJ1crD1KhKdUsgccmq5IFuzvt4djPpYhz52Jaj7v+K7LRFu4QJPl0cOO4fL
M+fAQnLMaCf/PUuBtmlhxvyIZOVflqgFreKunES6oDlqnXuY79Ops0/l2b1llCpf
EE2f8KIOmAQZ2rkJUMKntlq5y9T8bUdUPs9fypksqF8lYK6+zVI9Zj/Pyl6Oqm/Y
6wIDAQAB
—–END PUBLIC KEY—–
|
| Com_admin_commands | 0 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_instance | 0 |
| Com_alter_procedure | 0 |
| Com_alter_resource_group | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 0 |
| Com_alter_tablespace | 0 |
| Com_alter_user | 0 |
| Com_alter_user_default_role | 0 |
| Com_analyze | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 0 |
| Com_change_master | 0 |
| Com_change_repl_filter | 0 |
| Com_change_replication_source | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_clone | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_role | 0 |
| Com_create_server | 0 |
| Com_create_table | 0 |
| Com_create_resource_group | 0 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_create_spatial_reference_system | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 0 |
| 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_resource_group | 0 |
| Com_drop_role | 0 |
| Com_drop_server | 0 |
| Com_drop_spatial_reference_system | 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 | 0 |
| Com_get_diagnostics | 0 |
| Com_grant | 0 |
| Com_grant_roles | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_import | 0 |
| Com_insert | 0 |
| Com_insert_select | 0 |
| Com_install_component | 0 |
| Com_install_plugin | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_lock_instance | 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 | 0 |
| 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_restart | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_revoke_roles | 0 |
| Com_rollback | 0 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 0 |
| Com_select | 5 |
| Com_set_option | 0 |
| Com_set_password | 0 |
| Com_set_resource_group | 0 |
| Com_set_role | 0 |
| Com_signal | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| 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 | 0 |
| 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 | 0 |
| Com_show_function_code | 0 |
| Com_show_function_status | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| 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_replicas | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_replica_status | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 3 |
| Com_show_storage_engines | 0 |
| Com_show_table_status | 0 |
| Com_show_tables | 0 |
| Com_show_triggers | 0 |
| Com_show_variables | 6 |
| Com_show_warnings | 0 |
| Com_show_create_user | 0 |
| Com_shutdown | 0 |
| Com_replica_start | 0 |
| Com_slave_start | 0 |
| Com_replica_stop | 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_component | 0 |
| Com_uninstall_plugin | 0 |
| Com_unlock_instance | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| 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 |
| Compression | OFF |
| Compression_algorithm | |
| Compression_level | 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 | 62615 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 72670 |
| Created_tmp_tables | 7 |
| Current_tls_ca | ca.pem |
| Current_tls_capath | |
| Current_tls_cert | server-cert.pem |
| Current_tls_cipher | |
| Current_tls_ciphersuites | |
| Current_tls_crl | |
| Current_tls_crlpath | |
| Current_tls_key | server-key.pem |
| Current_tls_version | TLSv1.2,TLSv1.3 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Error_log_buffered_bytes | 11336 |
| Error_log_buffered_events | 72 |
| Error_log_expired_events | 0 |
| Error_log_latest_write | 1691828936050643 |
| Flush_commands | 3 |
| Global_connection_memory | 0 |
| Handler_commit | 11 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 238 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 263 |
| Handler_read_last | 0 |
| Handler_read_next | 62 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 6643 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 174 |
| Handler_write | 17 |
| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 230811 11:24:40 |
| Innodb_buffer_pool_resize_status | |
| Innodb_buffer_pool_resize_status_code | 0 |
| Innodb_buffer_pool_resize_status_progress | 0 |
| Innodb_buffer_pool_pages_data | 15101 |
| Innodb_buffer_pool_bytes_data | 247414784 |
| Innodb_buffer_pool_pages_dirty | 394 |
| Innodb_buffer_pool_bytes_dirty | 6455296 |
| Innodb_buffer_pool_pages_flushed | 538854 |
| Innodb_buffer_pool_pages_free | 1016 |
| Innodb_buffer_pool_pages_misc | 267 |
| Innodb_buffer_pool_pages_total | 16384 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 1360267 |
| Innodb_buffer_pool_read_ahead_evicted | 15803 |
| Innodb_buffer_pool_read_requests | 1791489541 |
| Innodb_buffer_pool_reads | 2840460 |
| Innodb_buffer_pool_wait_free | 242 |
| Innodb_buffer_pool_write_requests | 3378713 |
| Innodb_data_fsyncs | 719860 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 68962488832 |
| Innodb_data_reads | 4211390 |
| Innodb_data_writes | 1141148 |
| Innodb_data_written | 9944665600 |
| Innodb_dblwr_pages_written | 538713 |
| Innodb_dblwr_writes | 152938 |
| Innodb_redo_log_read_only | OFF |
| Innodb_redo_log_uuid | 1075899837 |
| Innodb_redo_log_checkpoint_lsn | 163902205311 |
| Innodb_redo_log_current_lsn | 163902417348 |
| Innodb_redo_log_flushed_to_disk_lsn | 163902417348 |
| Innodb_redo_log_logical_size | 212480 |
| Innodb_redo_log_physical_size | 838860800 |
| Innodb_redo_log_capacity_resized | 1073741824 |
| Innodb_redo_log_resize_status | OK |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 1651215 |
| Innodb_log_writes | 434282 |
| Innodb_os_log_fsyncs | 297687 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 955144704 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 15217 |
| Innodb_pages_read | 4208953 |
| Innodb_pages_written | 539074 |
| Innodb_redo_log_enabled | ON |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 4236 |
| Innodb_row_lock_time_avg | 132 |
| Innodb_row_lock_time_max | 211 |
| Innodb_row_lock_waits | 32 |
| Innodb_rows_deleted | 52071 |
| Innodb_rows_inserted | 38199 |
| Innodb_rows_read | 779125448 |
| Innodb_rows_updated | 65857 |
| Innodb_system_rows_deleted | 29 |
| Innodb_system_rows_inserted | 33 |
| Innodb_system_rows_read | 4414833 |
| Innodb_system_rows_updated | 365 |
| Innodb_sampled_pages_read | 0 |
| Innodb_sampled_pages_skipped | 0 |
| Innodb_num_open_files | 300 |
| Innodb_truncated_status_writes | 0 |
| Innodb_undo_tablespaces_total | 2 |
| Innodb_undo_tablespaces_implicit | 2 |
| Innodb_undo_tablespaces_explicit | 0 |
| Innodb_undo_tablespaces_active | 2 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 13396 |
| Key_blocks_used | 513 |
| Key_read_requests | 794882 |
| Key_reads | 109460 |
| Key_write_requests | 7049 |
| Key_writes | 3455 |
| Last_query_cost | 78.473000 |
| Last_query_partial_plans | 2 |
| Locked_connects | 0 |
| Max_execution_time_exceeded | 0 |
| Max_execution_time_set | 0 |
| Max_execution_time_set_failed | 0 |
| Max_used_connections | 57 |
| Max_used_connections_time | 2023-08-11 11:24:36 |
| Mysqlx_aborted_clients | 0 |
| Mysqlx_address | 127.0.0.1 |
| Mysqlx_bytes_received | 0 |
| Mysqlx_bytes_received_compressed_payload | 0 |
| Mysqlx_bytes_received_uncompressed_frame | 0 |
| Mysqlx_bytes_sent | 0 |
| Mysqlx_bytes_sent_compressed_payload | 0 |
| Mysqlx_bytes_sent_uncompressed_frame | 0 |
| Mysqlx_compression_algorithm | |
| Mysqlx_compression_level | |
| Mysqlx_connection_accept_errors | 0 |
| Mysqlx_connection_errors | 0 |
| Mysqlx_connections_accepted | 0 |
| Mysqlx_connections_closed | 0 |
| Mysqlx_connections_rejected | 0 |
| Mysqlx_crud_create_view | 0 |
| Mysqlx_crud_delete | 0 |
| Mysqlx_crud_drop_view | 0 |
| Mysqlx_crud_find | 0 |
| Mysqlx_crud_insert | 0 |
| Mysqlx_crud_modify_view | 0 |
| Mysqlx_crud_update | 0 |
| Mysqlx_cursor_close | 0 |
| Mysqlx_cursor_fetch | 0 |
| Mysqlx_cursor_open | 0 |
| Mysqlx_errors_sent | 0 |
| Mysqlx_errors_unknown_message_type | 0 |
| Mysqlx_expect_close | 0 |
| Mysqlx_expect_open | 0 |
| Mysqlx_init_error | 0 |
| Mysqlx_messages_sent | 0 |
| Mysqlx_notice_global_sent | 0 |
| Mysqlx_notice_other_sent | 0 |
| Mysqlx_notice_warning_sent | 0 |
| Mysqlx_notified_by_group_replication | 0 |
| Mysqlx_port | 33060 |
| Mysqlx_prep_deallocate | 0 |
| Mysqlx_prep_execute | 0 |
| Mysqlx_prep_prepare | 0 |
| Mysqlx_rows_sent | 0 |
| Mysqlx_sessions | 0 |
| Mysqlx_sessions_accepted | 0 |
| Mysqlx_sessions_closed | 0 |
| Mysqlx_sessions_fatal_error | 0 |
| Mysqlx_sessions_killed | 0 |
| Mysqlx_sessions_rejected | 0 |
| Mysqlx_socket | /var/run/mysqld/mysqlx.sock |
| Mysqlx_ssl_accepts | 0 |
| Mysqlx_ssl_active | |
| Mysqlx_ssl_cipher | |
| Mysqlx_ssl_cipher_list | |
| Mysqlx_ssl_ctx_verify_depth | 18446744073709551615 |
| Mysqlx_ssl_ctx_verify_mode | 5 |
| Mysqlx_ssl_finished_accepts | 0 |
| Mysqlx_ssl_server_not_after | Nov 10 19:53:47 2032 GMT |
| Mysqlx_ssl_server_not_before | Nov 13 19:53:47 2022 GMT |
| Mysqlx_ssl_verify_depth | |
| Mysqlx_ssl_verify_mode | |
| Mysqlx_ssl_version | |
| Mysqlx_stmt_create_collection | 0 |
| Mysqlx_stmt_create_collection_index | 0 |
| Mysqlx_stmt_disable_notices | 0 |
| Mysqlx_stmt_drop_collection | 0 |
| Mysqlx_stmt_drop_collection_index | 0 |
| Mysqlx_stmt_enable_notices | 0 |
| Mysqlx_stmt_ensure_collection | 0 |
| Mysqlx_stmt_execute_mysqlx | 0 |
| Mysqlx_stmt_execute_sql | 0 |
| Mysqlx_stmt_execute_xplugin | 0 |
| Mysqlx_stmt_get_collection_options | 0 |
| Mysqlx_stmt_kill_client | 0 |
| Mysqlx_stmt_list_clients | 0 |
| Mysqlx_stmt_list_notices | 0 |
| Mysqlx_stmt_list_objects | 0 |
| Mysqlx_stmt_modify_collection_options | 0 |
| Mysqlx_stmt_ping | 0 |
| Mysqlx_worker_threads | 2 |
| Mysqlx_worker_threads_active | 0 |
| Not_flushed_delayed_rows | 0 |
| Ongoing_anonymous_transaction_count | 0 |
| Open_files | 0 |
| Open_streams | 0 |
| Open_table_definitions | 450 |
| Open_tables | 96 |
| Opened_files | 0 |
| Opened_table_definitions | 2 |
| Opened_tables | 116 |
| 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_longest_seen | 116 |
| 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 |
| Queries | 4474885 |
| Questions | 14 |
| Replica_open_temp_tables | 0 |
| Resource_group_supported | ON |
| Rsa_public_key | —–BEGIN PUBLIC KEY—–
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAugMyPmPcTkYZiv+o/ZGs
B8b7/fSMpgvXiG6YoMy9RWZ1QHRkjeMBuGBRWOdlFGXzLXAw10d4TYCfbd4sIhWd
yUmSt+umOXWdVqV3aA0PtZVGnrZj1+Ule6g5iVEjZwFrqUgMmY3z65r8ACr4EF2V
ftGxJnJ1crD1KhKdUsgccmq5IFuzvt4djPpYhz52Jaj7v+K7LRFu4QJPl0cOO4fL
M+fAQnLMaCf/PUuBtmlhxvyIZOVflqgFreKunES6oDlqnXuY79Ops0/l2b1llCpf
EE2f8KIOmAQZ2rkJUMKntlq5y9T8bUdUPs9fypksqF8lYK6+zVI9Zj/Pyl6Oqm/Y
6wIDAQAB
—–END PUBLIC KEY—–
|
| Secondary_engine_execution_count | 0 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 16 |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 12 |
| Sort_scan | 2 |
| 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 | Nov 10 19:53:47 2032 GMT |
| Ssl_server_not_before | Nov 13 19:53:47 2022 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_timeout | 300 |
| 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 | 35463 |
| Table_locks_waited | 0 |
| Table_open_cache_hits | 3 |
| Table_open_cache_misses | 116 |
| Table_open_cache_overflows | 116 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Telemetry_traces_supported | ON |
| Threads_cached | 3 |
| Threads_connected | 1 |
| Threads_created | 523 |
| Threads_running | 2 |
| Tls_library_version | OpenSSL 3.0.2 15 Mar 2022 |
| Uptime | 91094 |
| Uptime_since_flush_status | 91094 |
+——————————————————-+———————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————+
497 rows in set (0.00 sec)
mysql>
mysql> SHOW STATUS LIKE ‘Key%’;
+————————+——–+
| Variable_name | Value |
+————————+——–+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 13381 |
| Key_blocks_used | 562 |
| Key_read_requests | 801745 |
| Key_reads | 110677 |
| Key_write_requests | 8582 |
| Key_writes | 4049 |
+————————+——–+
7 rows in set (0.00 sec)
SHOW STATUS LIKE ‘inno%’;
mysql> SHOW STATUS LIKE ‘inno%’;
+——————————————-+————————————————–+
| Variable_name | Value |
+——————————————-+————————————————–+
| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 230811 11:24:40 |
| Innodb_buffer_pool_resize_status | |
| Innodb_buffer_pool_resize_status_code | 0 |
| Innodb_buffer_pool_resize_status_progress | 0 |
| Innodb_buffer_pool_pages_data | 15112 |
| Innodb_buffer_pool_bytes_data | 247595008 |
| Innodb_buffer_pool_pages_dirty | 1 |
| Innodb_buffer_pool_bytes_dirty | 16384 |
| Innodb_buffer_pool_pages_flushed | 540947 |
| Innodb_buffer_pool_pages_free | 1024 |
| Innodb_buffer_pool_pages_misc | 248 |
| Innodb_buffer_pool_pages_total | 16384 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 1366954 |
| Innodb_buffer_pool_read_ahead_evicted | 15840 |
| Innodb_buffer_pool_read_requests | 1797075510 |
| Innodb_buffer_pool_reads | 2851464 |
| Innodb_buffer_pool_wait_free | 242 |
| Innodb_buffer_pool_write_requests | 3383413 |
| Innodb_data_fsyncs | 722291 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 69252715008 |
| Innodb_data_reads | 4229104 |
| Innodb_data_writes | 1144857 |
| Innodb_data_written | 9980509696 |
| Innodb_dblwr_pages_written | 540806 |
| Innodb_dblwr_writes | 153502 |
| Innodb_redo_log_read_only | OFF |
| Innodb_redo_log_uuid | 1075899837 |
| Innodb_redo_log_checkpoint_lsn | 163903183395 |
| Innodb_redo_log_current_lsn | 163903183546 |
| Innodb_redo_log_flushed_to_disk_lsn | 163903183546 |
| Innodb_redo_log_logical_size | 512 |
| Innodb_redo_log_physical_size | 838860800 |
| Innodb_redo_log_capacity_resized | 1073741824 |
| Innodb_redo_log_resize_status | OK |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 1654524 |
| Innodb_log_writes | 435310 |
| Innodb_os_log_fsyncs | 298497 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 956286464 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 15237 |
| Innodb_pages_read | 4226667 |
| Innodb_pages_written | 541167 |
| Innodb_redo_log_enabled | ON |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 4236 |
| Innodb_row_lock_time_avg | 132 |
| Innodb_row_lock_time_max | 211 |
| Innodb_row_lock_waits | 32 |
| Innodb_rows_deleted | 52129 |
| Innodb_rows_inserted | 38260 |
| Innodb_rows_read | 781596653 |
| Innodb_rows_updated | 66086 |
| Innodb_system_rows_deleted | 29 |
| Innodb_system_rows_inserted | 33 |
| Innodb_system_rows_read | 4426936 |
| Innodb_system_rows_updated | 365 |
| Innodb_sampled_pages_read | 0 |
| Innodb_sampled_pages_skipped | 0 |
| Innodb_num_open_files | 300 |
| Innodb_truncated_status_writes | 0 |
| Innodb_undo_tablespaces_total | 2 |
| Innodb_undo_tablespaces_implicit | 2 |
| Innodb_undo_tablespaces_explicit | 0 |
| Innodb_undo_tablespaces_active | 2 |
+——————————————-+————————————————–+
72 rows in set (0.00 sec)
mysql>
Global Buffers
https://dev.mysql.com/doc/refman/8.0/en/monitor-mysql-memory-use.html
https://dev.mysql.com/doc/refman/8.0/en/memory-use.html