查看 MySQL Server 参数
MySQL 服务启动后，我们可以用 SHOW VARIABLES 和 SHOW STATUS 命令查看 MySQL 的服务器静态参数值和动态运行状态信息。其中前者是在数据库启动后不会动态更改的值，比如缓冲区大小、字符集、数据文件名称等；后者是数据库运行期间的动态变化的信息，比如，锁等待、当前连接数等。
查看服务器参数值：
mysql> show variables;
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /home/mysql/mysql_home/ |
| bdb_cache_parts | 0 |
| bdb_cache_size | 0 |
| bdb_home |
……
查看服务器运行状态值：
mysql> show status;
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 0 |
| Aborted_connects | 2 |
| Binlog_cache_disk_use | 5 |
| Binlog_cache_use | 22 |
| Bytes_received | 163 |
| Bytes_sent | 10533 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_event | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 1 |
| Com_change_master | 0 |
……
也可以在操作系统下直接查看数据库参数或数据库状态信息，具体命令如下：
[mysql@db3 bin]$ mysqladmin -uroot variables
+---------------------------------+-------------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
| automatic_sp_privileges | ON |
| back_log | 50 |
| basedir | /home/mysql/mysql_home/ |
| bdb_cache_parts | 0 |
| bdb_cache_size | 0 |
| bdb_home | |
| bdb_log_buffer_size | 0 |
| bdb_logdir |
...
[mysql@db3 bin]$ mysqladmin -uroot status
Uptime: 327065 Threads: 1 Questions: 157054 Slow queries: 0 Opens: 0 Flush tables: 1
Open tables: 15 Queries per second avg: 0.480
MySQL 服务器的参数很多，如果需要了解某个参数的详细定义，可以使用以下命令：
[mysql@bj72 mysql]$ mysqld --verbose --help|more
从输出结果中可以看出，输出结果分为两部分：第一部分是对服务器参数的介绍，第二部分 是当前服务器的实际参数值。如果需要查询某个参数的定义和当前值，可以用操作系统命令进行过滤。比如，想知道当前服务器字符集的设置，可以用如下命令查看：
[root@localhost zzx]# mysqld --verbose --help|grep character-set-server
影响 MySQL Server 性能的重要参数
key_buffer_size 的设置
首先看看 mysqld（MySQL 服务器启动命令，加“--verbose –help”显示全部启动选项）中是如何定义 key_buffer_size 参数的：
[root@localhost zzx]# mysqld --verbose --help|grep key_buffer_size=
--key_buffer_size=# The size of the buffer used for index blocks for MyISAM
从上面可知，这个参数是用来设置索引块（Index Blocks）缓存的大小，它被所有线程共享，此参数只适用于 MyISAM 存储引擎。MySQL* 5.1 以前只允许使用一个系统默认 的 key_buffer，MySQL 5.1 以后提供了多个 key_buffer，可以将指定的表索引缓存入指定的key_buffer，这样可以更小地降低线程之间的竞争。
可以这样建立一个索引缓存：
mysql> set global hot_cache2.key_buffer_size=128*1024;
Query OK, 0 rows affected (0.01 sec)
其中，global 表示对每一个新的连接，此参数都将生效。hot_cache2 是新的 key_buffer 名称。 如果需要更改参数值，可以随时进行重建，例如：
mysql> set global hot_cache2.key_buffer_size=200*1024;
Query OK, 0 rows affected (0.00 sec)
然后可以把相关表的索引放到指定的索引缓存中，如下：
mysql> cache index sales,sales2 in hot_cache2;
+---------------+--------------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+--------------------+----------+----------+
| sakila.sales | assign_to_keycache | status | OK |
| sakila.sales2 | assign_to_keycache | status | OK |
+---------------+--------------------+----------+----------+
2 rows in set (0.04 sec)
要想将索引预装到默认 key_buffer 中，可以使用 LOAD INDEX INTO CACHE 语句。例如，下面的语句可以预装表 sales 的所有索引：
mysql> load index into cache sales;
+--------------+--------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+--------------+----------+----------+
| sakila.sales | preload_keys | status | OK |
+--------------+--------------+----------+----------+
1 row in set (0.00 sec)
如果需要删除索引缓存，则要使用下面命令：
mysql> set global hot_cache2.key_buffer_size=0;
Query OK, 0 rows affected (0.00 sec)
cache index 命令在一个表和 key_buffer 之间建立一种联系，但每次服务器重启时 key_buffer 中的数据将清空。如果想要每次服务器重启时相应表的索引能自动放到 key_buffer 中，可以在配置文件中设置 init-file 选项来指定包含 cache index 语句的文件路径，然后在对应的文件中写入 cache index 语句。下面是一个例子：
[zzx@localhost ~]$ more /etc/my.cnf
...
key_buffer_size=4G
hot_cache.key_buffer_size=2G
cold_cache.key_buffer_size=2G
init_file=/path/to/data=directory/mysqld_init.sql
...
每次服务器启动时执行 mysqld_init.sql 中的语句，该语句每行应包含一个 SQL 语句。下面的例子分配几个表，分别对应 hot_cache 和 cold_cache。
CACHE INDEX a.t1, a.t2, b.t3 IN hot_cache;
CACHE INDEX a.t4, a.t5, b.t6 IN cold_cache;
table_cache 的设置
在 mysqld 中对 table_cache 参数的定义如下：
[zzx@localhost ~]$ mysqld --verbose --help|grep table_cache=
--table_cache=# The number of open tables for all threads.
这个参数表示数据库用户打开表的缓存数量。每个连接进来，都会至少打开一个表缓存。因此，table_cache 与 max_connections 有关，例如，对于 200 个并行运行的连接，应该让表 的缓存至少有 200×N，这里 N 是可以执行的查询的一个联接中表的最大数量。此外，还需要为临时表和文件保留一些额外的文件描述符。
可以通过检查 mysqld 的状态变量open_tables和opened_tables确定这个参数是否过小， 这两个参数的区别是前者表示当前打开的表缓存数，如果执行 FLUSH TABLES 操作，则此系统会关闭一些当前没有使用的表缓存而使得此状态值减小；后者表示曾经打开的表缓存数，会一直进行累加，如果执行 FLUSH TABLES 操作，值不会减少。
innodb_buffer_pool_size 的设置
mysqld 中对 innodb_buffer_pool_size 参数的定义如下（grep 后面的“-A 2”表示显示包含指定字符串的行和此行后面的 2 行）：
[zzx@localhost ~]$ mysqld --verbose --help|grep "\-\-innodb_buffer_pool_size" -A 2
--innodb_buffer_pool_size=#
The size of the memory buffer InnoDB uses to cache data and indexes of its tables.
这个参数定义了 InnoDB 存储引擎的表数据和索引数据的最大内存缓冲区大小。和 MyISAM 存储引擎不同，MyISAM 的 key_buffer_size 只缓存索引键，而 innodb_buffer_pool_size 却是同时为数据块和索引块做缓存，这个特性和 Oracle 是一样的。这个值设得越高，访问 表中数据需要的磁盘 I/O 就越少。在一个专用的数据库服务器上，可以设置这个参数达机器 物理内存大小的 80%。尽管如此，还是建议用户不要把它设置得太大，因为对物理内存的竞争可能在操作系统上导致内存调度。
innodb_flush_log_at_trx_commit 的设置
在 mysqld 中对 innodb_flush_log_at_trx_commit 参数的定义如下：
[zzx@localhost ~]$ mysqld --verbose --help|grep "\-\-innodb_flush_log_at_trx_commit" -A 3
--innodb_flush_log_at_trx_commit[=#]
Set to 0 (write and flush once per second), 1 (write and
flush at each commit) or 2 (write at commit, flush once
per second).
这个参数是用来控制缓冲区中的数据写入到日志文件以及日志文件数据刷新到磁盘的操作时机。对这个参数的设置可以对数据库在性能与数据安全之间进行折中。
- 当这个参数是 0 的时候，日志缓冲每秒一次地被写到日志文件，并且对日志文件做向磁盘刷新的操作，但是在一个事务提交不做任何操作。
- 当这个参数是 1 的时候，在每个事务提交时，日志缓冲被写到日志文件，并且对日志文件做向磁盘刷新的操作。
- 当这个参数是 2 的时候，在每个事务提交时，日志缓冲被写到日志文件，但不对日志文件做向磁盘刷新的操作，对日志文件每秒向磁盘做一次刷新操作。
innodb_flush_log_at_trx_commit 参数的默认值是1，也是最安全的设置，即每个事务提交的时候都会从 log buffer 写到日志文件，而且会实际刷新磁盘盘，但是这样性能有一定的损失。如果可以容忍在数据库崩溃的时候损失一部分数据，那么设置成0或者2都会有所改善。设置成 0，则在数据库崩溃的时候会丢失那些没有被写入日志文件的事务，最多丢失 1 秒钟的事务，这种方式是最不安全的，也是效率最高的。设置成 2 的时候，因为只是没有刷新到 磁盘，但是已经写入日志文件，所以只要操作系统没有崩溃，那么并没有丢失数据，比设置成 0 更安全一些。
在 MySQL 官方手册中，为了确保事务的持久性和复制设置的一致性，都是建议将这个参数设置为 1 的。
innodb_lock_wait_timeout 的设置
在 mysqld 中，对 innodb_lock_wait_timeout 参数的定义如下：
[zzx@localhost ~]$ mysqld --verbose --help|grep "\-\-innodb_lock_wait_timeout" -A 2
--innodb_lock_wait_timeout=#
Timeout in seconds an InnoDB transaction may wait for a
lock before being rolled back.
MySQL 可以自动地监测行锁导致的死锁并进行相应的处理，但是对于表锁导致的死锁不能自动的监测，所以该参数主要被用于在出现类似情况的时候等待指定的时间后回滚。系统默认值是 50 秒，用户可以根据应用的需要进行调整。
innodb_support_xa 的设置
在 mysqld 中，对 innodb_support_xa 参数的定义如下：
[zzx@localhost ~]$ mysqld --verbose --help|grep "\-\-innodb_support_xa"
--innodb_support_xa Enable InnoDB support for the XA two-phase commit
通过该参数设置是否支持分布式事务，默认值是 ON 或者 1，表示支持分布式事务。如果确认应用中不需要使用分布式事务，则可以关闭这个参数，减少磁盘刷新的次数并获得更好的 InnoDB 性能。
innodb_log_buffer_size 的设置
在 mysqld 中，对 innodb_log_buffer_size 参数的定义如下：
[zzx@localhost ~]$ mysqld --verbose --help|grep "\-\-innodb_log_buffer_size" -A2
--innodb_log_buffer_size=#
The size of the buffer which InnoDB uses to write log to
the log files on disk.
从参数名称可以显而易见看出，含义是日志缓存的大小。默认的设置在中等强度写入负载以及较短事务的情况下，一般都可以满足服务器的性能要求。如果存在更新操作峰值或者负载较大，就应该考虑加大它的值了。如果它的值设置太高了，可能会浪费内存，因为它每秒都会刷新一次，因此无需设置超过 1 秒所需的内存空间。通常设置为 8～16MB 就足够了。越小的系统它的值越小。系统默认值是 1MB。
innodb_log_file_size 的设置
在 mysqld 中，对 innodb_log_file_size 参数的定义如下：
[zzx@localhost ~]$ mysqld --verbose --help|grep "\-\-innodb_log_file_size" -A1
--innodb_log_file_size=#
Size of each log file in a log group.
该参数含义是一个日志组（log group）中每个日志文件的大小。此参数在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高，但是带来的副作用是，当系统灾难时恢复时间会加大。系统默认值是 5MB。
内容来自《深入浅出MySQL》，作为笔记记录。
网友评论