数据库服务器mysql性能调优

rgr3255

mysql性能调优分为4个方面
一.硬件（CPU   内存   硬盘）监控CPU  内存 硬盘的值。
[iyunv@fanxh ~]# top
top - 03:58:11 up 10:05,  1 user,  load average: 0.00, 0.00, 0.00
Tasks: 121 total,   1 running, 120 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.0%us,  0.7%sy,  0.0%ni, 99.0%id,  0.0%wa,  0.0%hi,  0.3%si,  0.
Mem:   1004412k total,   922828k used,    81584k free,    70236k buffers
Swap:  2031608k total,        0k used,  2031608k free,   194964k cached

   PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
  4341 root      20   0  565m  12m 1500 S  2.0  1.3   5:26.23 ndb_mgmd
     1 root      20   0 19356 1540 1228 S  0.0  0.2   0:01.38 init
     2 root      20   0     0    0    0 S  0.0  0.0   0:00.00 kthreadd
     3 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 migration/0
     4 root      20   0     0    0    0 S  0.0  0.0   0:01.32 ksoftirqd/0
     5 root      RT   0     0    0    0 S  0.0  0.0   0:00.00 migration/0
     6 root      RT   0     0    0    0 S  0.0  0.0   0:00.09 watchdog/0
     7 root      20   0     0    0    0 S  0.0  0.0   0:32.77 events/0
二.网络(带宽)
三.服务本身问题 （mysqld）
    mysql体系结构
    连接池   sql接口     分析器  优化器   查询缓存   存储引擎    文件系统  管理工具
    一。设置mysql服务运行参数（软调优）
     mysql>   命令行下设置     选项=值;

     vim  /etc/my.cnf
    [mysqld]
    选项=值
    : wq
         1、数据库服务器并发连接数设置
mysql> show processlist;   查看当前连接线程的列表
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
|  1 | root | localhost | NULL | Query   |    0 | init  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)


mysql> show variables like "max_connections"; 查看数据库默认 的最大并发连接数 +-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.17 sec)



mysql> set GLOBAL  max_connections=300;设置默认的最大并发连接数
mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 300   |
+-----------------+-------+
1 row in set (0.00 sec)


mysql> show global status like "max_used_connections";查看曾经有过的最大连接
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 1     |
+----------------------+-------+
1 row in set (0.07 sec)

mysql> flush  status;  把max_used_connections的值清零从新计算

max_used_connections / max_connections  = 0.85    *   100%  ≈   85%
剩余的15%是为了应付突发访问。


如果想设置默认的最大连接使服务启动就生效，那么把它写入配置文件就可以。

vim   /etc/my.cnf
[mysqld]
max_connections=300
:wq

        2.连接数据库服务器“超时时间”设置
mysql  -hserver_ip  -uroot  -p123456

connect_timeout =10  （秒）  在获取链接时，等待握手的超时时间，只在登录时有效
mysql> show variables   like   "connect_timeout";    默认超时时间为10秒，一般不改
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| connect_timeout | 10    |
+-----------------+-------+
1 row in set (0.00 sec)


mysql> SET GLOBAL   connect_timeout=7;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables   like   "connect_timeout";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| connect_timeout | 7     |
+-----------------+-------+
1 row in set (0.00 sec)


wait_timeout = 28800  （秒） 服务器在关闭一个连接上等待行动的秒数  默认28800秒
mysql> show variables like "wait_timeout";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set wait_timeout=3600;
Query OK, 0 rows affected (0.02 sec)

mysql> show variables like "wait_timeout";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 3600  |
+---------------+-------+
1 row in set (0.01 sec)


3.   thread_cache_size可以重复使用的保存在缓存中线程的数量
mysql> show    variables    like    'thread_cache_size';可以重复使用的保存在缓存中线程的数量
什么是线程？线程是进程中的最小单元。
怎么判断thread_cache_size的值好不好？用show global status like 'Thread%';查看

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 9     |
+-------------------+-------+
1 row in set (0.00 sec)
mysql> show global status like 'Thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 1     |
| Threads_created   | 1     |            越小越好
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.03 sec)

4.  table_open_cache   所有线程打开表的数量，设置多少比较好用open%table%来查看。
mysql> show variables like "table_open_cache";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 2000  |
+------------------+-------+
1 row in set (0.00 sec)


mysql> show global status like "open%table%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Open_table_definitions   | 67    |
| Open_tables              | 60    |
| Opened_table_definitions | 67    |
| Opened_tables            | 67    |
+--------------------------+-------+
4 rows in set (0.00 sec)
用一下公式来计算来设置tables_open_cache的值。

Open_tables / table_open_cache=数字   *    100% <= 95%  
    60            /    2000   =num         *  100%    <=  95%   
60     /     x   =0.95  *  100%    <=  95%


5.key_buffer_size =  8M    设置索引缓冲区的大小   （字节） 怎么设置合适的缓存？查看key_read%的值。
mysql> show variables like "key_buffer_size";
+-----------------+---------+
| Variable_name   | Value   |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+
1 row in set (0.00 sec)

mysql> show global status like "key_read%";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_read_requests | 0     |
| Key_reads         | 0     |
+-------------------+-------+
2 rows in set (0.00 sec)

sort_buffer_size = 512K         

  每个需要进行排序的线程分配该大小的缓冲区。增加此值加速order by或group by操作
mysql> show variables like "sort_buffer_size%";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.00 sec)


read_buffer_size = 256K
从数据表顺序读取数据的读操作保留的缓存区的长度

mysql> show variables like "read_buffer_size%";
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| read_buffer_size | 131072 |
+------------------+--------+
1 row in set (0.00 sec)


read_rnd_buffer_size = 512K
按某种特定顺序(比如使用了ORDER BY子句的查询)输出的查询结果
mysql> show variables like "read_rnd_buffer_size%";
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| read_rnd_buffer_size | 262144 |
+----------------------+--------+
1 row in set (0.00 sec)
6.查询缓存的配置
mysql> show variables like "query_cache%";
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |      拿出缓存的最小单元
| query_cache_size             | 1048576 |         查询缓存的大小
| query_cache_type             | OFF     |            查询缓存的类型
| query_cache_wlock_invalidate | OFF     |   当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果。

+------------------------------+---------+
5 rows in set (0.00 sec)

query_cache_type    off     0   
                                 on      1
                                 DEMAND          2

0 或OFF 将阻止缓存或查询缓存结果。
1或ON 将允许缓存，  以SELECT SQL_NO_CACHE    开始的查询语句除外。
2或DEMAND ，仅对以SELECT SQL_CACHE 开始的那些查询语句启用缓存。
select SQL_CACHE  name,user  from webdb.user2;
开启缓存的命令写在配置文件中才能启用。

怎么设置缓存的参数？显示查询缓存的统计信息。
mysql> show   global    status    like   "qcache%";
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031368 |    缓存中的空内存
| Qcache_hits             | 0       |      每次查询在缓存中命中时就增大
| Qcache_inserts          | 0       |  每次 插入一个查询时就增大
| Qcache_lowmem_prunes    | 0       | 缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数
| Qcache_not_cached       | 2       |    不合适进行缓存的查询的数量
| Qcache_queries_in_cache | 0       |   当前缓存的查询（和响应）的数量
| Qcache_total_blocks     | 1       |    缓存中块的数量
+-------------------------+---------+
8 rows in set (0.00 sec)


四.查询语句有问题
启动binlog日志进行对查询语句的错误
MySQL  有4种类型的日志 ： 错误日志   binlog日志     查询日志   慢查询日志
错误日志是默认开启的， 其他的都没有开启，需要手动进行开启。

记录显示查询结果比较慢的sql查询语句；         10秒

开启mysql数据库服务的“慢查询日志”  记录超过指定时间显示查询结果的sql语句
1  在数据库服务器上启用慢查询日志  （记录超过指定时间才显示查询结果的sql语句）
                             主机名-slow.log                       10s
                             mysqld-slow.log
                             数据库目录

vim  /etc/my.cnf
[mysqld]
log-slow-queries      启用慢查询日志
long-query-time=5  设置超时时间
slow-query-log-file=fanxhslow
log-queries-not-using-indexes    记录没有使用索引做查询的sql语句
:wq


[iyunv@fanxh ~]#cat   mysqld-slow.log     查看慢查询日志的记录信息
[iyunv@fanxh ~]#[iyunv@localhost mysql]# mysqldumpslow  mysqld-slow.log //查看慢查询日志内容
Reading mysql slow query log from mysqld-slow.log
Count: 2 Time=10.00s (20s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@localhost
select sleep(N)

2.在数据库服务器上启用"查询日志" （记录所有sql语句）
默认日志文件名      mysqld.log   (more  less  tail  cat  head)
                              主机名.log
默认存放目录          数据库目录


vim  /etc/my.cnf     
[mysqld]
log         启用查询日志        
:wq

[iyunv@fanxh ~]#cat  mysqld.log       查看查询日志的记录信息
3.错误日志   默认是开启   ，  记录的数据库服务在启动 和 运行过程中产生的错误信息
默认日志文件名      
                              主机名.err
默认存放目录          数据库目录