慢慢成长 - 数据库
type: channel
link: https://azuoa.com/index.php/category/%E6%95%B0%E6%8D%AE%E5%BA%93/
items: MySQL参数调优和相关命令

MySQL参数调优和相关命令
type: item
date: 2022-06-09T05:40:00+00:00
description: 一. 相关命令说明1. show status: 查看当前状态# 示例 # 查看全部状态 show status; # 模糊查询 show status like 'Thread%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 22 | | Threads_connected | 17 | | Threads_created | 39 | | Threads_running | 1 | +-------------------+-------+ 4 rows in set (0.04 sec) # 常用查询项 2.show processlist: 查看当前进程列表# 示例 # 查看全部进程列表 show processlist; # 查看全部进程列表详细信息 show full processlist;3.show global variables: 查看当前参数# 示例 # 查看全部参数 show global variables; # 模糊查询 show global variables like 'key%'; +--------------------------+-----------+ | Variable_name | Value | +--------------------------+-----------+ | key_buffer_size | 536870912 | | key_cache_age_threshold | 300 | | key_cache_block_size | 1024 | | key_cache_division_limit | 100 | | keyring_operations | ON | +--------------------------+-----------+ 5 rows in set (0.04 sec)二. 推荐配置1. 1-2G内存, 使用 < 1Gkey_buffer_size=128M query_cache_size=64M tmp_table_size=64M innodb_buffer_pool_size=256M innodb_log_buffer_size=16MB thread_cache_size=64 table_open_cache=128 max_connections=100 sort_buffer_size=768KB read_buffer_size=768KB read_rnd_buffer_size=512KB join_buffer_size=1024KB binlog_cache_size=64KB2. 2-4G内存, 使用 2Gkey_buffer_size=256M query_cache_size=128M tmp_table_size=384M innodb_buffer_pool_size=384M innodb_log_buffer_size=16MB thread_cache_size=96 table_open_cache=192 max_connections=200 sort_buffer_size=768KB read_buffer_size=768KB read_rnd_buffer_size=512KB join_buffer_size=2048KB binlog_cache_size=64KB3. 4-8G内存, 使用 3Gkey_buffer_size=384M query_cache_size=192M tmp_table_size=512M innodb_buffer_pool_size=512M innodb_log_buffer_size=16MB thread_cache_size=128 table_open_cache=384 max_connections=300 sort_buffer_size=1024KB read_buffer_size=1024KB read_rnd_buffer_size=768KB join_buffer_size=2048KB binlog_cache_size=128KB4. 8-16G内存, 使用 6.5Gkey_buffer_size=512M query_cache_size=256M tmp_table_size=1024M innodb_buffer_pool_size=1024M innodb_log_buffer_size=16MB thread_cache…