我们目前拥有包含数千个表(约350k)的AWS RDS MySQL服务器实例.这是因为我们使用按客户模式方法，其中每个客户都有自己的数据库.
随着时间的推移，我们注意到性能下降，相应地，根据AWS在this article中的建议，我们应该调整table_open_cache和table_definition_cache参数.
如何在确保不会耗尽内存的同时增加此参数？
数据库实例有8 GB的RAM，其中2 GB大部分时间是空闲的.目前，TABLE_OPEN_CACHE和TABLE_DEFINITION_CACHE都设置为400.
SHOW GLOBAL STATUS;人的结果
Variable_name | Value
Aborted_clients | 10
Aborted_connects | 21
Acl_cache_items_count | 1
AuroraDb_commits | 2036080
AuroraDb_commit_latency | 10474020840
AuroraDb_ddl_stmt_duration | 967597256
AuroraDb_select_stmt_duration | 2137359090413
AuroraDb_insert_stmt_duration | 2885053612
AuroraDb_update_stmt_duration | 1007422326
AuroraDb_delete_stmt_duration | 288692100
Aurora_binlog_io_cache_allocated | 0
Aurora_binlog_io_cache_read_requests | 0
Aurora_binlog_io_cache_reads | 0
Aurora_caspian_buf_pool_resize_completed | 0
Aurora_caspian_buf_pool_resize_rejected | 0
Aurora_caspian_buf_pool_resize_requested | 0
Aurora_enhanced_binlog | DISABLED
Aurora_enhanced_binlog_earliest_continuous_file |
Aurora_external_connection_count | 12
Aurora_fast_insert_cache_hits | 0
Aurora_fast_insert_cache_misses | 235634
Aurora_last_allocated_lsn_for_replication | 7104504239
Aurora_thread_pool_thread_count | 4
Aurora_tmz_version | 2023c
Binlog_cache_disk_use | 0
Binlog_cache_use | 0
Binlog_stmt_cache_disk_use | 0
Binlog_stmt_cache_use | 0
Bytes_received | 16391884265
Bytes_sent | 269785114378
Com_admin_commands | 17910
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 | 10526
Com_alter_tablespace | 0
Com_alter_user | 1
Com_alter_user_default_role | 0
Com_analyze | 0
Com_begin | 2
Com_binlog | 0
Com_call_procedure | 0
Com_change_db | 1351785
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 | 88248
Com_create_db | 3
Com_create_event | 0
Com_create_function | 18
Com_create_index | 0
Com_create_procedure | 0
Com_create_role | 0
Com_create_server | 0
Com_create_table | 396
Com_create_resource_group | 0
Com_create_trigger | 0
Com_create_udf | 0
Com_create_user | 0
Com_create_view | 176
Com_create_spatial_reference_system | 0
Com_dealloc_sql | 0
Com_delete | 283314
Com_delete_multi | 0
Com_do | 0
Com_drop_db | 2
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 | 224
Com_drop_trigger | 0
Com_drop_user | 0
Com_drop_view | 88
Com_empty_query | 0
Com_execute_sql | 0
Com_explain_other | 0
Com_flush | 1087
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 | 1178206
Com_insert_select | 232155
Com_install_component | 0
Com_install_plugin | 0
Com_kill | 0
Com_load | 0
Com_lock_instance | 0
Com_lock_tables | 15504
Com_optimize | 0
Com_preload_keys | 0
Com_prepare_sql | 0
Com_purge | 0
Com_purge_before_date | 589
Com_release_savepoint | 0
Com_rename_table | 0
Com_rename_user | 0
Com_repair | 0
Com_replace | 335
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 | 40630993
Com_set_option | 9437548
Com_set_password | 0
Com_set_resource_group | 0
Com_set_role | 0
Com_signal | 0
Com_show_binlog_events | 0
Com_show_binlogs | 2
Com_show_charsets | 0
Com_show_collations | 0
Com_show_create_db | 5168
Com_show_create_event | 0
Com_show_create_func | 28560
Com_show_create_proc | 0
Com_show_create_table | 1405977
Com_show_create_trigger | 0
Com_show_databases | 11
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 | 703350
Com_show_function_code | 0
Com_show_function_status | 5168
Com_show_grants | 2
Com_show_keys | 272
Com_show_master_status | 3
Com_show_open_tables | 9
Com_show_plugins | 0
Com_show_privileges | 0
Com_show_procedure_code | 0
Com_show_procedure_status | 2924
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 | 3
Com_show_slave_status | 3
Com_show_status | 6030
Com_show_storage_engines | 0
Com_show_table_status | 702986
Com_show_tables | 35096
Com_show_triggers | 702986
Com_show_variables | 648
Com_show_warnings | 15321
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 | 13260
Com_update | 784832
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_awslambda | 0
Com_alter_system | 0
Com_unit_test | 0
Com_show_volume_status | 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 | 2038910
Created_tmp_disk_tables | 883
Created_tmp_files | 19665
Created_tmp_tables | 3883565
Delayed_errors | 0
Delayed_insert_threads | 0
Delayed_writes | 0
Error_log_buffered_bytes | 10976
Error_log_buffered_events | 83
Error_log_expired_events | 0
Error_log_latest_write | 1709825235374115
Flush_commands | 3
Global_connection_memory | 0
Handler_commit | 61027839
Handler_delete | 265951
Handler_discover | 0
Handler_external_lock | 276839613
Handler_mrr_init | 0
Handler_prepare | 0
Handler_read_first | 28458052
Handler_read_key | 339781404
Handler_read_last | 175570
Handler_read_next | 1529321466
Handler_read_prev | 270240705
Handler_read_rnd | 22316541
Handler_read_rnd_next | 9158337706
Handler_rollback | 167214
Handler_savepoint | 0
Handler_savepoint_rollback | 0
Handler_update | 12175120
Handler_write | 60152987
Innodb_buffer_pool_dump_status |
Innodb_buffer_pool_load_status |
Innodb_buffer_pool_resize_status |
Innodb_buffer_pool_pages_data | 194578
Innodb_buffer_pool_bytes_data | 3187965952
Innodb_buffer_pool_pages_dirty | 143
Innodb_buffer_pool_bytes_dirty | 2342912
Innodb_buffer_pool_pages_flushed | 0
Innodb_buffer_pool_pages_free | 0
Innodb_buffer_pool_pages_misc | 110
Innodb_buffer_pool_pages_total | 194688
Innodb_buffer_pool_read_ahead_rnd | 0
Innodb_buffer_pool_read_ahead | 0
Innodb_buffer_pool_read_ahead_evicted | 271281
Innodb_logical_read_ahead_page_count | 52828972
Innodb_aurora_average_batched_read_request_size | 7
Innodb_aurora_batched_read_requests | 1559793
Innodb_aurora_shm_batched_read_requests | 1559793
Innodb_aurora_shm_read_requests | 8253438
Innodb_buffer_pool_read_requests | 10672076159
Innodb_buffer_pool_reads | 20476583
Innodb_buffer_pool_wait_free | 0
Innodb_buffer_pool_write_requests | 20304459
Innodb_data_fsyncs | 0
Innodb_data_pending_fsyncs | 0
Innodb_data_pending_reads | 0
Innodb_data_pending_writes | 0
Innodb_data_read | 335497510912
Innodb_data_reads | 11942
Innodb_data_writes | 13496
Innodb_data_written | 0
Innodb_dblwr_pages_written | 0
Innodb_dblwr_writes | 0
Innodb_log_waits | 0
Innodb_log_write_requests | 0
Innodb_log_writes | 0
Innodb_os_log_fsyncs | 0
Innodb_os_log_pending_fsyncs | 0
Innodb_os_log_pending_writes | 0
Innodb_os_log_written | 0
Innodb_page_size | 16384
Innodb_pages_created | 94967
Innodb_pages_read | 20477548
Innodb_pages_written | 0
Innodb_redo_log_enabled | ON
Innodb_row_lock_current_waits | 0
Innodb_row_lock_time | 159
Innodb_row_lock_time_avg | 5
Innodb_row_lock_time_max | 35
Innodb_row_lock_waits | 30
Innodb_rows_deleted | 24929
Innodb_rows_inserted | 1262274
Innodb_rows_read | 10694424554
Innodb_rows_updated | 746111
Innodb_system_rows_deleted | 252513
Innodb_system_rows_inserted | 283465
Innodb_system_rows_read | 318461192
Innodb_system_rows_updated | 347064
Innodb_sampled_pages_read | 0
Innodb_sampled_pages_skipped | 0
Innodb_num_open_files | 10
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 | 0
Key_read_requests | 0
Key_reads | 0
Key_write_requests | 0
Key_writes | 0
Locked_connects | 0
Max_execution_time_exceeded | 0
Max_execution_time_set | 0
Max_execution_time_set_failed | 0
Max_used_connections | 130
Max_used_connections_time | 2024-03-05 17:00:54
Not_flushed_delayed_rows | 0
Ongoing_anonymous_transaction_count | 0
Open_files | 2
Open_streams | 0
Open_table_definitions | 20000
Open_tables | 6000
Opened_files | 2
Opened_table_definitions | 1170497
Opened_tables | 5899304
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 | 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
Queries | 75795920
Questions | 57100560
Secondary_engine_execution_count | 0
Select_full_join | 395827
Select_full_range_join | 2086
Select_range | 721284
Select_range_check | 29
Select_scan | 18416923
Slave_open_temp_tables | 0
Slow_launch_threads | 0
Slow_queries | 143
Sort_merge_passes | 1514
Sort_range | 0
Sort_rows | 25017607
Sort_scan | 6471447
Table_locks_immediate | 16141
Table_locks_waited | 0
Table_open_cache_hits | 131416357
Table_open_cache_misses | 5899816
Table_open_cache_overflows | 5882645
Tc_log_max_pages_used | 0
Tc_log_page_size | 0
Tc_log_page_waits | 0
Threads_cached | 1
Threads_connected | 17
Threads_created | 7
Threads_running | 2
Uptime | 176548
Uptime_since_flush_status | 176548
server_audit_active | OFF
server_audit_last_error |
server_audit_writes_failed | 0
server_aurora_das_messages_missed | 0
server_aurora_das_messages_queued | 0
server_aurora_das_num_fatal_errors | 0
server_aurora_das_running | OFF
SHOW VARIABLES人的结果:
Variable_name | Value
big_tables | OFF
bind_address | *
binlog_cache_size | 32768
binlog_checksum | CRC32
binlog_direct_non_transactional_updates | OFF
binlog_encryption | OFF
binlog_error_action | ABORT_SERVER
binlog_expire_logs_seconds | 0
binlog_format | ROW
binlog_group_commit_sync_delay | 0
binlog_group_commit_sync_no_delay_count | 0
binlog_gtid_simple_recovery | ON
binlog_max_flush_queue_time | 0
binlog_order_commits | ON
binlog_rotate_encryption_master_key_at_startup | OFF
binlog_row_event_max_size | 8192
binlog_row_image | FULL
binlog_row_metadata | MINIMAL
binlog_row_value_options |
binlog_rows_query_log_events | OFF
binlog_stmt_cache_size | 32768
binlog_transaction_compression | OFF
binlog_transaction_compression_level_zstd | 3
binlog_transaction_dependency_history_size | 25000
binlog_transaction_dependency_tracking | COMMIT_ORDER
block_encryption_mode | aes-128-ecb
bulk_insert_buffer_size | 8388608
check_proxy_users | OFF
completion_type | NO_CHAIN
concurrent_insert | AUTO
connect_timeout | 10
connection_memory_chunk_size | 8912
connection_memory_limit | 18446744073709551615
cte_max_recursion_depth | 1000
datadir | /rdsdbdata/db/
default_storage_engine | InnoDB
default_tmp_storage_engine | InnoDB
default_week_format | 0
delay_key_write | ON
delayed_insert_limit | 100
delayed_insert_timeout | 300
delayed_queue_size | 1000
disabled_storage_engines |
div_precision_increment | 4
end_markers_in_json | OFF
enforce_gtid_consistency | OFF
eq_range_index_dive_limit | 200
error_count | 0
event_scheduler | ON
expire_logs_days | 0
explicit_defaults_for_timestamp | ON
flush | OFF
flush_time | 0
foreign_key_checks | ON
ft_boolean_syntax | "+ -><()~*:""""&|"
ft_max_word_len | 84
ft_min_word_len | 4
ft_query_expansion_limit | 20
ft_stopword_file | (built-in)
generated_random_password_length | 20
global_connection_memory_limit | 18446744073709551615
global_connection_memory_tracking | OFF
group_concat_max_len | 1024
group_replication_consistency | EVENTUAL
gtid_executed |
gtid_executed_compression_period | 0
gtid_mode | OFF_PERMISSIVE
gtid_next | AUTOMATIC
gtid_owned |
gtid_purged |
have_compress | YES
have_dynamic_loading | YES
have_geometry | YES
have_openssl | YES
have_profiling | YES
have_query_cache | NO
have_rtree_keys | YES
have_ssl | YES
have_statement_timeout | YES
have_symlink | DISABLED
histogram_generation_max_mem_size | 20000000
host_cache_size | 328
hostname | ip-172-27-1-235
identity | 0
immediate_server_version | 999999
information_schema_stats_expiry | 86400
innodb_adaptive_flushing | ON
innodb_adaptive_flushing_lwm | 10
innodb_adaptive_hash_index | OFF
innodb_adaptive_hash_index_parts | 8
innodb_adaptive_max_sleep_delay | 150000
innodb_api_bk_commit_interval | 5
innodb_api_disable_rowlock | OFF
innodb_api_enable_binlog | OFF
innodb_api_enable_mdl | OFF
innodb_api_trx_level | 0
innodb_aurora_enable_auto_akp | OFF
innodb_aurora_max_partitions_for_range | 0
innodb_autoextend_increment | 64
innodb_autoinc_lock_mode | 2
innodb_btr_prefetch_perf_optimization | ON
innodb_buffer_pool_chunk_size | 1594884096
innodb_buffer_pool_dump_at_shutdown | OFF
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 | 2
innodb_buffer_pool_load_abort | OFF
innodb_buffer_pool_load_at_startup | OFF
innodb_buffer_pool_load_now | OFF
innodb_buffer_pool_size | 3189768192
innodb_change_buffer_max_size | 25
innodb_change_buffering | none
innodb_checksum_algorithm | none
innodb_cleanup_temp_tablespaces_in_background | ON
innodb_cmp_per_index_enabled | OFF
innodb_commit_concurrency | 0
innodb_compression_failure_threshold_pct | 5
innodb_compression_level | 6
innodb_compression_pad_pct_max | 50
innodb_concurrency_tickets | 5000
innodb_data_file_path | ibdata1:12M:autoextend
innodb_data_home_dir |
innodb_ddl_buffer_size | 1048576
innodb_ddl_threads | 4
innodb_deadlock_detect | ON
innodb_dedicated_server | OFF
innodb_default_row_format | dynamic
innodb_directories |
innodb_disable_shm_reads | OFF
innodb_disable_sort_file_cache | OFF
innodb_doublewrite | OFF
innodb_doublewrite_batch_size | 0
innodb_doublewrite_dir |
innodb_doublewrite_files | 0
innodb_doublewrite_pages | 0
innodb_extend_and_initialize | OFF
innodb_fast_shutdown | 1
innodb_file_per_table | OFF
innodb_fill_factor | 100
innodb_flush_log_at_timeout | 1
innodb_flush_log_at_trx_commit | 1
innodb_flush_method | O_DIRECT
innodb_flush_neighbors | 0
innodb_flush_sync | ON
innodb_flushing_avg_loops | 30
innodb_force_load_corrupted | OFF
innodb_force_recovery | 0
innodb_fsync_threshold | 0
innodb_ft_aux_table |
innodb_ft_cache_size | 8000000
innodb_ft_enable_diag_print | OFF
innodb_ft_enable_stopword | ON
innodb_ft_max_token_size | 84
innodb_ft_min_token_size | 3
innodb_ft_num_word_optimize | 2000
innodb_ft_result_cache_limit | 2000000000
innodb_ft_server_stopword_table |
innodb_ft_sort_pll_degree | 2
innodb_ft_total_cache_size | 640000000
innodb_ft_user_stopword_table |
innodb_idle_flush_pct | 100
innodb_io_capacity | 200
innodb_io_capacity_max | 2000
innodb_lock_wait_timeout | 50
innodb_log_buffer_size | 16777216
innodb_log_checksums | ON
innodb_log_compressed_pages | ON
innodb_log_file_size | 50331648
innodb_log_files_in_group | 2
innodb_log_group_home_dir | ./
innodb_log_spin_cpu_abs_lwm | 80
innodb_log_spin_cpu_pct_hwm | 50
innodb_log_vdl_improved_callback | ON
innodb_log_wait_for_flush_spin_hwm | 400
innodb_log_write_ahead_size | 8192
innodb_log_writer_threads | ON
innodb_lru_scan_depth | 1024
innodb_max_dirty_pages_pct | 90.000000
innodb_max_dirty_pages_pct_lwm | 10.000000
innodb_max_purge_lag | 0
innodb_max_purge_lag_delay | 0
innodb_max_undo_log_size | 1073741824
innodb_monitor_disable |
innodb_monitor_enable |
innodb_monitor_reset |
innodb_monitor_reset_all |
innodb_old_blocks_pct | 37
innodb_old_blocks_time | 1000
innodb_online_alter_log_max_size | 134217728
innodb_open_files | 300
innodb_optimize_fulltext_only | OFF
innodb_page_cleaners | 2
innodb_page_size | 16384
innodb_parallel_read_threads | 4
innodb_print_all_deadlocks | OFF
innodb_print_ddl_logs | OFF
innodb_purge_batch_size | 600
innodb_purge_rseg_truncate_frequency | 128
innodb_purge_threads | 1
innodb_random_read_ahead | OFF
innodb_read_ahead_threshold | 56
innodb_read_io_threads | 1
innodb_read_only | OFF
innodb_redo_log_archive_dirs |
innodb_redo_log_encrypt | OFF
innodb_replication_delay | 0
innodb_rollback_on_timeout | OFF
innodb_rollback_segments | 128
innodb_segment_reserve_factor | 12.500000
innodb_shared_buffer_pool_uses_huge_pages | ON
innodb_sort_buffer_size | 1048576
innodb_spin_wait_delay | 6
innodb_spin_wait_pause_multiplier | 50
innodb_stats_auto_recalc | ON
innodb_stats_include_delete_marked | OFF
innodb_stats_method | nulls_equal
innodb_stats_notify_change | OFF
innodb_stats_on_metadata | OFF
innodb_stats_persistent | ON
innodb_stats_persistent_sample_pages | 20
innodb_stats_transient_sample_pages | 8
innodb_status_output | OFF
innodb_status_output_locks | OFF
innodb_strict_mode | ON
innodb_sync_array_size | 1
innodb_sync_spin_loops | 30
innodb_table_locks | ON
innodb_temp_data_file_path | ibtmp1:12M:autoextend
innodb_temp_tablespaces_dir | ./
innodb_thread_concurrency | 0
innodb_thread_sleep_delay | 10000
innodb_tmpdir |
innodb_trx_write_next_max_trx_id | ON
innodb_undo_directory | ./
innodb_undo_log_encrypt | OFF
innodb_undo_log_truncate | OFF
innodb_undo_tablespaces | 2
innodb_use_fdatasync | OFF
innodb_use_native_aio | OFF
innodb_validate_tablespace_paths | OFF
innodb_version | 8.0.28
innodb_write_io_threads | 4
insert_id | 0
interactive_timeout | 28800
internal_tmp_mem_storage_engine | TempTable
join_buffer_size | 262144
keep_files_on_create | OFF
key_buffer_size | 16777216
key_cache_age_threshold | 300
key_cache_block_size | 1024
key_cache_division_limit | 100
keyring_operations | ON
large_files_support | ON
large_page_size | 0
large_pages | OFF
last_insert_id | 0
local_infile | ON
lock_wait_timeout | 31536000
locked_in_memory | OFF
log_bin | OFF
log_bin_basename |
log_bin_index |
log_bin_trust_function_creators | ON
log_bin_use_v1_row_events | OFF
log_error | /rdsdbdata/log/error/mysql-error.log
log_error_services | log_filter_internal; log_sink_internal
log_error_suppression_list |
log_error_verbosity | 3
log_output | FILE
log_queries_not_using_indexes | OFF
log_raw | OFF
log_replica_updates | ON
log_slave_updates | ON
log_slow_admin_statements | OFF
log_slow_extra | OFF
log_slow_replica_statements | OFF
log_slow_slave_statements | OFF
log_statements_unsafe_for_binlog | ON
log_throttle_queries_not_using_indexes | 0
log_timestamps | UTC
long_query_time | 10.000000
low_priority_updates | OFF
lower_case_file_system | OFF
lower_case_table_names | 0
master_info_repository | TABLE
master_verify_checksum | OFF
max_allowed_packet | 67108864
max_binlog_cache_size | 18446744073709547520
max_binlog_size | 134217728
max_binlog_stmt_cache_size | 18446744073709547520
max_connect_errors | 100
max_connections | 200
max_delayed_threads | 20
max_digest_length | 1024
max_error_count | 1024
max_execution_time | 0
max_heap_table_size | 16777216
max_insert_delayed_threads | 20
max_join_size | 18446744073709551615
max_length_for_sort_data | 4096
max_points_in_geometry | 65536
max_prepared_stmt_count | 16382
max_relay_log_size | 0
max_seeks_for_key | 18446744073709551615
max_sort_length | 1024
max_sp_recursion_depth | 0
max_user_connections | 0
max_write_lock_count | 18446744073709551615
min_examined_row_limit | 0
myisam_data_pointer_size | 6
myisam_max_sort_file_size | 9223372036853727232
myisam_mmap_size | 18446744073709551615
myisam_recover_options | OFF
myisam_repair_threads | 1
myisam_sort_buffer_size | 8388608
myisam_stats_method | nulls_unequal
myisam_use_mmap | OFF
net_buffer_length | 16384
net_read_timeout | 30
net_retry_count | 10
net_write_timeout | 60
ngram_token_size | 2
offline_mode | OFF
old | OFF
old_alter_table | OFF
open_files_limit | 1048576
optimizer_prune_level | 1
optimizer_search_depth | 62
optimizer_switch | "index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=off,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on"
optimizer_trace | "enabled=off,one_line=off"
optimizer_trace_features | "greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on"
optimizer_trace_limit | 1
optimizer_trace_max_mem_size | 1048576
optimizer_trace_offset | -1
original_commit_timestamp | 36028797018963968
original_server_version | 999999
parser_max_mem_size | 18446744073709551615
partial_revokes | OFF
performance_schema | OFF
performance_schema__auto__ | ON
performance_schema_accounts_size | 0
performance_schema_digests_size | 0
performance_schema_error_size | 5057
performance_schema_events_stages_history_long_size | 0
performance_schema_events_stages_history_size | 0
performance_schema_events_statements_history_long_size | 0
performance_schema_events_statements_history_size | 0
performance_schema_events_transactions_history_long_size | 0
performance_schema_events_transactions_history_size | 0
performance_schema_events_waits_history_long_size | 0
performance_schema_events_waits_history_size | 0
performance_schema_hosts_size | 0
performance_schema_max_cond_classes | 0
performance_schema_max_cond_instances | 0
performance_schema_max_digest_length | 0
performance_schema_max_digest_sample_age | 60
performance_schema_max_file_classes | 0
performance_schema_max_file_handles | 0
performance_schema_max_file_instances | 0
performance_schema_max_index_stat | 0
performance_schema_max_memory_classes | 0
performance_schema_max_metadata_locks | 0
performance_schema_max_mutex_classes | 0
performance_schema_max_mutex_instances | 0
performance_schema_max_prepared_statements_instances | 0
performance_schema_max_program_instances | 0
performance_schema_max_rwlock_classes | 0
performance_schema_max_rwlock_instances | 0
performance_schema_max_socket_classes | 0
performance_schema_max_socket_instances | 0
performance_schema_max_sql_text_length | 0
performance_schema_max_stage_classes | 0
performance_schema_max_statement_classes | 0
performance_schema_max_statement_stack | 0
performance_schema_max_table_handles | 0
performance_schema_max_table_instances | 0
performance_schema_max_table_lock_stat | 0
performance_schema_max_thread_classes | 0
performance_schema_max_thread_instances | 0
performance_schema_session_connect_attrs_size | 0
performance_schema_setup_actors_size | 0
performance_schema_setup_objects_size | 0
performance_schema_show_processlist | OFF
performance_schema_users_size | 0
persist_only_admin_x509_subject |
persisted_globals_load | ON
pid_file | /rdsdbdata/log/mysql-3306.pid
port | 3306
preload_buffer_size | 32768
print_identified_with_as_hex | OFF
profiling | OFF
profiling_history_size | 15
protocol_compression_algorithms | "zlib,zstd,uncompressed"
protocol_version | 10
proxy_user |
query_alloc_block_size | 8192
query_prealloc_size | 8192
rand_seed1 | 0
rand_seed2 | 0
range_alloc_block_size | 4096
range_optimizer_max_mem_size | 8388608
rbr_exec_mode | STRICT
read_buffer_size | 262144
read_only | OFF
read_rnd_buffer_size | 524288
regexp_stack_limit | 8000000
regexp_time_limit | 32
replication_optimize_for_static_plugin_config | OFF
replication_sender_observe_commit_only | OFF
require_row_format | OFF
require_secure_transport | OFF
resultset_metadata | FULL
rpl_read_size | 5242880
rpl_stop_replica_timeout | 31536000
rpl_stop_slave_timeout | 31536000
schema_definition_cache | 400
secondary_engine_cost_threshold | 100000.000000
select_into_buffer_size | 131072
select_into_disk_sync | OFF
select_into_disk_sync_delay | 0
session_track_gtids | OFF
session_track_schema | ON
session_track_state_change | OFF
session_track_transaction_info | OFF
sha256_password_auto_generate_rsa_keys | ON
sha256_password_private_key_path | private_key.pem
sha256_password_proxy_users | OFF
sha256_password_public_key_path | public_key.pem
show_create_table_skip_secondary_engine | OFF
show_create_table_verbosity | OFF
show_old_temporals | OFF
skip_external_locking | ON
skip_name_resolve | ON
skip_networking | OFF
skip_replica_start | ON
skip_show_database | OFF
skip_slave_start | ON
slave_allow_batching | OFF
slave_checkpoint_group | 512
slave_checkpoint_period | 300
slave_compressed_protocol | OFF
slave_exec_mode | STRICT
slave_max_allowed_packet | 1073741824
slave_net_timeout | 60
slave_parallel_type | LOGICAL_CLOCK
slave_parallel_workers | 4
slave_pending_jobs_size_max | 134217728
slave_preserve_commit_order | ON
slave_rows_search_algorithms | "INDEX_SCAN , HASH_SCAN"
slave_skip_errors | OFF
slave_sql_verify_checksum | ON
slave_transaction_retries | 10
slave_type_conversions |
slow_launch_time | 2
socket | /tmp/mysql.sock
sort_buffer_size | 2097152
source_verify_checksum | OFF
sql_auto_is_null | OFF
sql_big_selects | ON
sql_buffer_result | OFF
sql_log_bin | ON
sql_log_off | OFF
sql_notes | ON
sql_quote_show_create | ON
sql_replica_skip_counter | 0
sql_require_primary_key | OFF
sql_safe_updates | OFF
sql_select_limit | 18446744073709551615
sql_slave_skip_counter | 0
stored_program_cache | 256
stored_program_definition_cache | 256
super_read_only | OFF
sync_binlog | 1
sync_master_info | 10000
sync_relay_log | 10000
sync_relay_log_info | 10000
sync_source_info | 10000
table_definition_cache | 20000
table_encryption_privilege_check | OFF
table_open_cache | 6000
table_open_cache_instances | 4
tablespace_definition_cache | 400
temptable_max_mmap | 1073741824
temptable_max_ram | 16777216
temptable_use_mmap | ON
terminology_use_previous | NONE
thread_cache_size | 4
thread_handling | thread-pools
thread_stack | 262144
tmp_table_size | 16777216
transaction_alloc_block_size | 8192
transaction_allow_batching | OFF
transaction_isolation | REPEATABLE-READ
transaction_prealloc_size | 4096
transaction_read_only | OFF
transaction_write_set_extraction | XXHASH64
unique_checks | ON
updatable_views_with_limit | YES
use_secondary_engine | ON
version | 8.0.28
wait_timeout | 28800
warning_count | 0
windowing_use_high_precision | ON
SELECT count(*) FROM INFORMATION_SCHEMA.TABLES人的结果:
count(*)
350963
SELECT COUNT(*), sum(data_length), sum(index_length), sum(data_free) FROM information_schema.tables LIMIT 100000人的结果
COUNT(*),sum(data_length),sum(index_length),sum(data_free)
350963,147484688384,15894839296,527753445113856
SELECT 'SLEEPING time use', COUNT(*),SUM(time) FROM information_schema.processlist WHERE command="Sleep"人的结果
SLEEPING time use,COUNT(*),SUM(time)
SLEEPING time use,9,7639
AWS RSD实例:t3.large