MYSQL系统优化与系统设置参数的一次调查

摘自http://blog.chinaunix.net/u/9817/showart_1094779.html
MYSQL系统优化与系统设置参数的一次调查

对mysql进行优化意味着适当地分配内存，并让mysqld了解将会承担何种类型的负载。减少磁盘访问的次数。


类似地，确保 MySQL 进程正确操作就意味着它花费在服务查询上的时间要多于花费在处理后台任务（如处理临时磁盘表或打开和关闭文件）上的时间。



记录慢速查询

在一个 SQL 服务器中，数据表都是保存在磁盘上的。索引为服务器提供了一种在表中查找特定数据行的方法，而不用搜索整个表。当必须要搜索整个表时，就称为表扫描。通常来说，您可能只希望获得表中数据的一个子集，因此全表扫描会浪费大量的磁盘 I/O，因此也就会浪费大量时间。当必须对数据进行连接时，这个问题就更加复杂了，因为必须要对连接两端的多行数据进行比较。


当然，表扫描并不总是会带来问题；有时读取整个表反而会比从中挑选出一部分数据更加有效（服务器进程中查询规划器用来作出这些决定）。如果索引的使用效率很低，或者根本就不能使用索引，则会减慢查询速度，而且随着服务器上的负载和表大小的增加，这个问题会变得更加显著。执行时间超过给定时间范围的查询就称为慢速查询。


您可以配置 mysqld 将这些慢速查询记录到适当命名的慢速查询日志中。管理员然后会查看这个日志来帮助他们确定应用程序中有哪些部分需要进一步调查。清单 1 给出了要启用慢速查询日志需要在 my.cnf 中所做的配置。


启用 MySQL 慢速查询日志

[mysqld]

; enable the slow query log, default 10 seconds

log-slow-queries

; log queries taking longer than 5 seconds

long_query_time = 5

; log queries that don't use indexes even if they take less than long_query_time

; MySQL 4.1 and newer only

log-queries-not-using-indexes


这三个设置一起使用，可以记录执行时间超过 5 秒和没有使用索引的查询。请注意有关 log-queries-not-using-indexes 的警告：您必须使用 MySQL 4.1 或更高版本。慢速查询日志都保存在 MySQL 数据目录中，名为 hostname-slow.log。如果希望使用一个不同的名字或路径，可以在 my.cnf 中使用 log-slow-queries = /new/path/to/file 实现此目的。


阅读慢速查询日志最好是通过 mysqldumpslow 命令进行。指定日志文件的路径，就可以看到一个慢速查询的排序后的列表，并且还显示了它们在日志文件中出现的次数。一个非常有用的特性是 mysqldumpslow 在比较结果之前，会删除任何用户指定的数据，因此对同一个查询的不同调用被计为一次；这可以帮助找出需要工作量最多的查询。
对查询进行缓存

一般程序严重依赖数据库，但有时却会反复执行相同的查询。每次执行查询时，数据库都必须要执行相同的工作。对查询进行分析，确定如何执行查询，从磁盘中加载信息，然后将结果返回给客户机。MySQL 有一个特性称为查询缓存，它将查询结果保存在内存中。在很多情况下，这会极大地提高性能。

虚拟系统查询缓存为：

my.cnf中添加

[4a-o07-b4:root/1054]#cat /etc/my.cnf|grep query_cache

query_cache_size=32M


mysql> show variables like '%cache%';

+------------------------------+------------+

| Variable_name | Value |

+------------------------------+------------+

| query_cache_size | 33554432 |

+------------------------------+------------+

14 rows in set (0.00 sec)


监视查询缓存


在启用查询缓存之后，重要的是要理解它是否得到了有效的使用。MySQL 有几个可以查看的变量，可以用来了解缓存中的情况。

mysql> SHOW STATUS LIKE 'qcache%';

+-------------------------+------------+

| Variable_name | Value |

+-------------------------+------------+

| Qcache_free_blocks | 5216 |

| Qcache_free_memory | 14640664 |

| Qcache_hits | 2581646882 |

| Qcache_inserts | 360210964 |

| Qcache_lowmem_prunes | 281680433 |

| Qcache_not_cached | 79740667 |

| Qcache_queries_in_cache | 16927 |

| Qcache_total_blocks | 47042 |

+-------------------------+------------+

8 rows in set (0.00 sec)




变量名
    

说明

Qcache_free_blocks
   

缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE 会对缓存中的碎片进行整理，从而得到一个空闲块。

Qcache_free_memory
   

缓存中的空闲内存。

Qcache_hits
   

每次查询在缓存中命中时就增大。

Qcache_inserts
   

每次插入一个查询时就增大。命中次数除以插入次数就是不中比率；用 1 减去这个值就是命中率。在上面这个例子中，大约有 87% 的查询都在缓存中命中

Qcache_lowmem_prunes
   

缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的 free_blocks 和 free_memory 可以告诉您属于哪种情况）。

Qcache_not_cached
   

不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句。

Qcache_queries_in_cache
   

当前缓存的查询（和响应）的数量。

Qcache_total_blocks
   

缓存中块的数量。


   



通常，间隔几秒显示这些变量就可以看出区别，这可以帮助确定缓存是否正在有效地使用。运行 FLUSH STATUS 可以重置一些计数器，如果服务器已经运行了一段时间，这会非常有帮助。


使用非常大的查询缓存，期望可以缓存所有东西，这种想法非常诱人。由于 mysqld 必须要对缓存进行维护，例如当内存变得很低时执行剪除，因此服务器可能会在试图管理缓存时而陷入困境。作为一条规则，如果 FLUSH QUERY CACHE 占用了很长时间，那就说明缓存太大了。


强制限制


您可以在 mysqld 中强制一些限制来确保系统负载不会导致资源耗尽的情况出现。清单 3 给出了 my.cnf 中与资源有关的一些重要设置。


清单 3. MySQL 资源设置

set-variable=max_connections=500

set-variable=wait_timeout=10

max_connect_errors = 100



连接最大个数是在第一行中进行管理的。与 Apache 中的 MaxClients 类似，其想法是确保只建立服务允许数目的连接。要确定服务器上目前建立过的最大连接数，请执行 SHOW STATUS LIKE 'max_used_connections'。


第 2 行告诉 mysqld 终止所有空闲时间超过 10 秒的连接。在 LAMP 应用程序中，连接数据库的时间通常就是 Web 服务器处理请求所花费的时间。有时候，如果负载过重，连接会挂起，并且会占用连接表空间。如果有多个交互用户或使用了到数据库的持久连接，那么将这个值设低一点并不可取！


最后一行是一个安全的方法。如果一个主机在连接到服务器时有问题，并重试很多次后放弃，那么这个主机就会被锁定，直到 FLUSH HOSTS 之后才能运行。默认情况下，10 次失败就足以导致锁定了。将这个值修改为 100 会给服务器足够的时间来从问题中恢复。如果重试 100 次都无法建立连接，那么使用再高的值也不会有太多帮助，可能它根本就无法连接。


缓冲区和缓存


MySQL 支持超过 100 个的可调节设置；但是幸运的是，掌握少数几个就可以满足大部分需要。查找这些设置的正确值可以通过 SHOW STATUS 命令查看状态变量，从中可以确定 mysqld 的运作情况是否符合我们的预期。给缓冲区和缓存分配的内存不能超过系统中的现有内存，因此调优通常都需要进行一些妥协。


打开的表缓存

1.每个表都可以表示为磁盘上的一个文件，必须先打开，后读取。

为了加快从文件中读取数据的过程，mysqld 对这些打开文件进行了缓存，其最大数目由 /etc/mysqld.conf 中的 table_cache 指定


mysql> show variables like 'table_cache%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| table_cache | 512 |

+---------------+-------+

1 row in set (0.00 sec)


mysql> SHOW STATUS LIKE 'open%tables';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Open_tables | 301 |

| Opened_tables | 893 |

+---------------+-------+

2 rows in set (0.00 sec)

看status说明目前有301给表是打开的，有893个表需要打开。

如果opened_tables随着重新运行show status命令快速增加，就说明缓存命中率不够。

如果open_tables比tabe_cache设置小很多，就说明该值太大了。




线程缓存

与表的缓存类似，对于线程来说也有一个缓存。 mysqld 在接收连接时会根据需要生成线程。在一个连接变化很快的繁忙服务器上，对线程进行缓存便于以后使用可以加快最初的连接。

mysql> show variables like 'thread_cache%';

+-------------------+-------+

| Variable_name | Value |

+-------------------+-------+

| thread_cache_size | 0 |

+-------------------+-------+

1 row in set (0.00 sec)


mysql> \q

Bye

[4a-o07-b4:root/1003]#cat /etc/my.cnf|grep thread_cache

[4a-o07-b4:root/1004]#



mysql> SHOW STATUS LIKE 'threads%';

+-------------------+-------+

| Variable_name | Value |

+-------------------+-------+

| Threads_cached | 0 |

| Threads_connected | 39 |

| Threads_created | 31287 |

| Threads_running | 1 |

+-------------------+-------+

4 rows in set (0.00 sec)


虚拟系统没有设置线程的缓存。



索引缓冲区

关键字缓冲区保存了 MyISAM 表的索引块。理想情况下，对于这些块的请求应该来自于内存，而不是来自于磁盘。

mysql> show variables like 'key_buffer_size';

+-----------------+-----------+

| Variable_name | Value |

+-----------------+-----------+

| key_buffer_size | 134217728 |

+-----------------+-----------+

1 row in set (0.00 sec)


mysql> SHOW STATUS LIKE 'key_read%';

+-------------------+------------+

| Variable_name | Value |

+-------------------+------------+

| Key_read_requests | 1407915295 |

| Key_reads | 713099 |

+-------------------+------------+

2 rows in set (0.00 sec)


Key_reads 代表命中磁盘的请求个数， Key_read_requests 是总数。命中磁盘的读请求数除以读请求总数就是不中比率 —— 在本例中每 2,000 个请求，大约有 1 个没有命中内存。如果每 1,000 个请求中命中磁盘的数目超过 1 个，就应该考虑增大关键字缓冲区了。例如，key_buffer = 128M 会将缓冲区设置为 128MB。


确定临时表的使用

临时表可以在更高级的查询中使用，其中数据在进一步进行处理（例如 GROUP BY 字句）之前，都必须先保存到临时表中；理想情况下，在内存中创建临时表。但是如果临时表变得太大，就需要写入磁盘中。

每次使用临时表都会增大 Created_tmp_tables；基于磁盘的表也会增大 Created_tmp_disk_tables。对于这个比率，并没有什么严格的规则，因为这依赖于所涉及的查询。长时间观察 Created_tmp_disk_tables 会显示所创建的磁盘表的比率，您可以确定设置的效率。


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 | 16777216 |

+---------------------+----------+

1 row in set (0.00 sec)



mysql> SHOW STATUS LIKE 'created_tmp%';

+-------------------------+----------+

| Variable_name | Value |

+-------------------------+----------+

| Created_tmp_disk_tables | 92153 |

| Created_tmp_files | 48269 |

| Created_tmp_tables | 35837047 |

+-------------------------+----------+


每个会话的设置

注意这里针对的是每个会话的，它们在乘以可能存在的连接数的时候，这些选项表示大量的内存！
排序缓冲区(sort_buffer_size)

当 MySQL 必须要进行排序时，就会在从磁盘上读取数据时分配一个排序缓冲区来存放这些数据行。

如果要排序的数据太大，那么数据就必须保存到磁盘上的临时文件中，并再次进行排序。


mysql> show variables like 'sort_buffer_size';

+------------------+---------+

| Variable_name | Value |

+------------------+---------+

| sort_buffer_size | 2097144 |

+------------------+---------+

1 row in set (0.00 sec)



mysql> SHOW STATUS LIKE 'sort%';

+-------------------+-----------+

| Variable_name | Value |

+-------------------+-----------+

| Sort_merge_passes | 31 |

| Sort_range | 99583552 |

| Sort_rows | 123728703 |

| Sort_scan | 64994 |

+-------------------+-----------+

4 rows in set (0.00 sec)


如果 sort_merge_passes 很大，就表示需要注意 sort_buffer_size。例如， sort_buffer_size = 2M 将排序缓冲区设置为 2MB。



读缓冲区(read_buffer_size)

理想情况下，索引提供了足够多的信息，可以只读入所需要的行。

但是有时候查询，需要读取表中大量数据。

要理解这种行为，需要知道运行了多少个 SELECT 语句Com_select ，以及需要读取表中的下一行数据的次数Handler_read_rnd_next。



mysql> SHOW STATUS LIKE "handler_read_rnd_next";

+-----------------------+-----------+

| Variable_name | Value |

+-----------------------+-----------+

| Handler_read_rnd_next | 788605837 |

+-----------------------+-----------+

1 row in set (0.00 sec)


mysql> SHOW STATUS LIKE "com_select";

+---------------+-----------+

| Variable_name | Value |

+---------------+-----------+

| Com_select | 185910100 |

+---------------+-----------+

1 row in set (0.00 sec)


mysql>



Handler_read_rnd_next/Com_select =1/5 。如果该值超过4000，就应该查看read_buffer_size，虚拟系统为131072。

read_buffer_size

每个线程连续扫描时为扫描的每个表分配的缓冲区的大小(字节)。如果进行多次连续扫描，可能需要增加该值，默认值为131072。