MySQL查看性能参数的多种方法（附带实例）
MySQL 服务器启动后，可以使用 SHOW VARIABLES 语句来查询服务器一些静态参数，比如缓冲区大小、字符集、数据文件名称等信息。示例如下：
使用 SHOW STATUS 查询服务器运行中的状态信息，比如当前连接数、锁等待状态信息。示例如下：
同时，可以在操作系统下直接查询数据库的状态，命令如下：
MySQL 服务器参数比较多，如果需要了解某个参数的含义，可以通过如下命令查询：
查询的结果中包含了当前服务器参数的介绍。如果需要查询其中的某些参数的含义，可以通过下面的命令过滤查询：
mysql> SHOW VARIABLES; +---------------------------------+------------------+ | Variable_name | Value | +---------------------------------+------------------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | | back_log | 50 | | basedir | /usr/ | | big_tables | OFF | | binlog_cache_size | 32768 | | binlog_format | STATEMENT | | bulk_insert_buffer_size | 8388608 | | character_set_client | utf8 | +---------------------------------+------------------+使用 SHOW RARIABLES 可以查看 MySQL 启动之前已经配置好的一些系统静态参数。
使用 SHOW STATUS 查询服务器运行中的状态信息，比如当前连接数、锁等待状态信息。示例如下：
mysql> SHOW STATUS; +--------------------------+--------+ | Variable_name | Value | +--------------------------+--------+ | Aborted_clients | 0 | | Aborted_connects | 0 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Bytes_received | 11567 | | Bytes_sent | 124957 | | Com_admin_commands | 0 | | Com_assign_to_keycache | 0 | | Com_alter_db | 0 | | Com_alter_db_upgrade | 0 | | Com_alter_event | 0 | | Com_alter_function | 0 | | Com_alter_procedure | 0 | | Com_alter_server | 0 | | Com_alter_table | 0 | | Com_alter_tablespace | 0 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_binlog | 0 | | Com_call_procedure | 0 | | Com_change_db | 0 | | Com_change_master | 0 | +--------------------------+--------+ 507 rows in set (0.80 sec)
同时，可以在操作系统下直接查询数据库的状态，命令如下：
[root@localhost ~]# mysqladmin -u root -p variables +---------------------------------+------------------+ | Variable_name | Value | +---------------------------------+------------------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | | back_log | 50 | | basedir | /usr/ | | big_tables | OFF | | binlog_cache_size | 32768 | | binlog_format | STATEMENT | | bulk_insert_buffer_size | 8388608 | | character_set_client | utf8 | +---------------------------------+------------------+ 267 rows in set (0.08 sec)
MySQL 服务器参数比较多，如果需要了解某个参数的含义，可以通过如下命令查询：
[root@localhost ~]#mysqld –verbose –help|more
mysqld Ver 9.0.1-log for pc-linux-gnu on i686 (MySQL Community Server (GPL))
Copyright (C) 2024 MySQL AB, by Monty and others
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Starts the MySQL database server
Usage: mysqld [OPTIONS]
Default options are read from the following files in the given order:
/etc/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
The following groups are read: mysql_cluster mysqld server mysqld-5.0
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit
--no-defaults Don't read default options from any options file
--defaults-file=# Only read default options from the given file #
--defaults-extra-file=# Read this file after the global files are read
-?, --help Display this help and exit.
--abort-slave-event-count=#
Option used by mysql-test for debugging and testing of
replication.
--allow-suspicious-udfs
Allows use of UDFs consisting of only one symbol xxx()
without corresponding xxx_init() or xxx_deinit(). That
also means that one can load any function from any
library, for example exit() from libc.so
-a, --ansi Use ANSI SQL syntax instead of MySQL syntax. This mode
will also set transaction isolation level 'serializable'.
--auto-increment-increment[=#]
Auto-increment columns are incremented by this
--auto-increment-offset[=#]
Offset added to Auto-increment columns. Used when
auto-increment-increment != 1
--automatic-sp-privileges
Creating and dropping stored procedures alters ACLs.
Disable with --skip-automatic-sp-privileges.
-b, --basedir=name Path to installation directory. All paths are usually
resolved relative to this.
--bdb Enable Berkeley DB (if this version of MySQL supports
it). Disable with --skip-bdb (will save memory).
...
sync-frm TRUE
table_cache 64
table_lock_wait_timeout 50
thread_cache_size 0
thread_concurrency 10
thread_stack 196608
time_format (No default value)
tmp_table_size 33554432
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
updatable_views_with_limit 1
wait_timeout 28800
To see what values a running MySQL server is using, type
'mysqladmin variables' instead of 'mysqld --verbose --help'.
查询的结果中包含了当前服务器参数的介绍。如果需要查询其中的某些参数的含义，可以通过下面的命令过滤查询：
[root@localhost ~]# mysqld --verbose --help|grep thread
--log-slave-updates Tells the slave to log the updates from the slave thread
The number of seconds the slave thread will sleep before
set, the slave thread will not be started. Note that the
master and where the I/O replication thread is in the
The password the slave thread will authenticate with when
--master-user=name The username the slave thread will use for authentication
for other threads.
A dedicated thread is created to, at the given
the SQL replication thread is in the relay logs.
Tells the slave thread to restrict replication to the
Tells the slave thread to restrict replication to the
Tells the slave thread to not replicate to the specified
Tells the slave thread to not replicate to the specified
Tells the slave thread to restrict replication to the
Tells the slave thread to not replicate to the tables
--skip-thread-priority
Don't give threads different priorities.
Tells the slave thread to continue replication when a
have. This comes into play when the main MySQL thread
that this is a limit per thread!
How long a INSERT DELAYED thread should wait for INSERT
--flush_time=# A dedicated thread is created to flush all tables at the
Number of times a thread is allowed to enter InnoDB
--innodb_file_io_threads=#
Number of file I/O threads in InnoDB.
--innodb_thread_concurrency=#
environments. Sets the maximum number of threads allowed
inside InnoDB. Value 0 will disable the thread
--innodb_thread_sleep_delay=#
Time of innodb thread sleeping before joining InnoDB
--max_delayed_threads=#
Don't start more than this number of threads to handle
--myisam_repair_threads=#
Number of threads to use when repairing MyISAM tables.
Each thread that does a sequential scan allocates a
exception for replication (slave) threads and users with
Number of times the slave SQL thread will retry a
If creating the thread takes longer than this value (in
seconds), the Slow_launch_threads counter will be
Each thread that needs to do a sort allocates a buffer of
--table_cache=# The number of open tables for all threads.
--thread_cache_size=#
How many threads we should keep in a cache for reuse.
--thread_concurrency=#
Permits the application to give the threads system a hint
for the desired number of threads that should be run at
--thread_stack=# The stack size for each thread.
innodb_file_io_threads 4
innodb_thread_concurrency 8
innodb_thread_sleep_delay 10000
max_delayed_threads 20
myisam_repair_threads 1
thread_cache_size 0
thread_concurrency 10
thread_stack 196608
mysqld --verbose --help|grep thread 命令会把包含 thread 的信息查询出来。
ICP备案：
公安联网备案：