一、MySQL性能优化项
MySQL参数优化对于不同的网站，及其在线量，访问量，帖子数量，网络情况以及机器硬件配置都有关系，优化不可能一次性完成，需要不断的观察以及调试，才有可能得到最佳效果。
下面列出了对性能优化影响较大的主要变量，主要分为连接请求的变量和缓冲区变量。
1.连接请求的变量
(1)max_connections
MySQL的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，如果连接数越多，MySQL会为每个连接提供连接缓冲区，就会开销更多的内存。所以要适当调整该值，不能盲目提高该值。
数值过小会经常出现ERROR 1040: Too many connections错误。
通过查看当前状态的连接数量(试图连接到MySQL(不管是否连接成功)的连接数)，以决定该值的大小。
mysql> show variables like 'max_connections'; #最大连接数
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 151 |
+-----------------+-------+
1 row in set (0.00 sec)
mysql> show status like 'max_used_connections'; #响应的连接数
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 1 |
+----------------------+-------+
1 row in set (0.00 sec)
计算max_used_connections / max_connections * 100%的值。（理想值≈ 85%）
如果max_used_connections跟max_connections相同那么就是max_connections设置过低或者超过服务器负载上限了，低于10%则设置过大。
想要修改max_connections，可以通过修改my.cnf文件并重启mysqld服务。
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
pid-file=/usr/local/mysql/data/mysqld.pid
log-error=/usr/local/mysql/data/mysqld.err
socket=/tmp/mysql.sock
max_connections=1024
[root@localhost ~]# service mysqld restart
查看当前状态的连接数量
mysql> show variables like 'max_connections'; #最大连接数
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 1024 |
+-----------------+-------+
1 row in set (0.00 sec)
(2)back_log
MySQL能暂存的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，它就会起作用。如果MySQL的连接数据达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。
back_log值指出在MySQL暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆栈中。如果期望在一个短时间内有很多连接，可以增加它来满足需求。
查看主机进程列表
mysql> show full processlist;
+----+------+-----------+--------------------+---------+------+----------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+--------------------+---------+------+----------+-----------------------+
| 2 | root | localhost | information_schema | Query | 0 | starting | show full processlist |
+----+------+-----------+--------------------+---------+------+----------+-----------------------+
1 row in set (0.00 sec)
观察主机进程列表，如果发现大量xxxxx | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL的待连接进程，就要加大back_log的值或max_connections的值。
查看back_log的默认值
mysql> show variables like 'back_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| back_log | 80 |
+---------------+-------+
1 row in set (0.00 sec)
如果想要修改此值，需要修改/etc/my.cnf文件，在[mysqld]下面添加。
back_log = 数值
(3)wait_timeout和interactive_timeout
wait_timeout：指的是MySQL在关闭一个非交互的连接之前所要等待的秒数;
interactive_timeout：指的是mysql在关闭一个交互的连接之前所要等待的秒数;
在终端进入mysql管理，使用的就是交互连接。如果没有操作的时间超过interactive_timeout设置的时间就会自动断开。默认数值是28800，可调优为7200。
对性能的影响
wait_timeout：
（1）如果设置太小，那么连接关闭的很快，从而使一些持久的连接不起作用;
（2）如果设置太大，容易造成连接打开时间过长;
在show processlist时，会看到太多的sleep状态的连接，从而造成too many connections错误。
（3）一般希望wait_timeout尽可能地低。
interactive_timeout的设置将要对你的web application没有多大的影响。
查看wait_timeout和interactive_timeout
mysql> show variables like '%wait_timeout%';
+--------------------------+----------+
| Variable_name | Value |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50 |
| lock_wait_timeout | 31536000 |
| wait_timeout | 28800 |
+--------------------------+----------+
3 rows in set (0.00 sec)
mysql> show variables like '%interactive_timeout%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
+---------------------+-------+
1 row in set (0.00 sec)
设置wait_timeout和interactive_timeout的值，可以修改/etc/my.cnf文件，在[mysqld]下面添加。
wait_timeout=100
interactive_timeout=100
二、缓冲区变量
1.全局缓冲
(1)key_buffer_size
指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。
mysql> show variables like 'key_buffer_size';
+-----------------+---------+
| Variable_name | Value |
+-----------------+---------+
| key_buffer_size | 8388608 |
+-----------------+---------+
1 row in set (0.03 sec)
通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。
mysql> show status like 'key_read%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Key_read_requests | 6 |
| Key_reads | 3 |
+-------------------+-------+
2 rows in set (0.00 sec)
key_reads与key_read_requests的比例应尽可能的低，至少是1:100，1:1000更好。
例如：一共有6个索引读取请求，有3个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率？
key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100% =50%
注意：key_buffer_size只对MyISAM表起作用。即使不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。
mysql> show status like 'created_tmp_disk_tables';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
+-------------------------+-------+
1 row in set (0.00 sec)
如何调整key_buffer_size?
默认配置数值是8388608(8M)，主机有4GB内存，可以调优值为268435456(256MB)。
修改/etc/my.cnf文件，在[mysqld]下面添加如下内容
key_buffer_size=268435456 或 key_buffer_size=256M
重启MySQL Server进入后，查看设置已经生效。
(2)query_cache_size(查询缓存简称QC)
MySQL将查询结果存放在缓冲区中，今后对于同样的SELECT语句(区分大小写)，将直接从缓冲区中读取结果。一个SQL查询如果以select开头，那么MySQL服务器将尝试对其使用查询缓存。
mysql> show variables like 'query_cache_size';
+------------------+---------+
| Variable_name | Value |
+------------------+---------+
| query_cache_size | 1048576 |
+------------------+---------+
1 row in set (0.00 sec)
注意：两个SQL语句，只要相差哪怕是一个字符(例如大小写不一样；多一个空格等)，那么这两个SQL将使用不同的CACHE。
通过检查状态值’Qcache%’，可以知道query_cache_size设置是否合理。
mysql> show status like 'qcache%';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031832 |
| 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.01 sec)
参数解释
Qcache_free_blocks：缓存中相邻内存块的个数。如果该值显示较大，则说明Query Cache中的内存碎片较多，FLUSH QUERY CACHE会对缓存中的碎片进行整理，从而得到一个空闲块。
注意：当一个表被更新之后，和它相关的cache blocks将被free。但是这个block依然可能存在队列中，除非是在队列的尾部。可以用FLUSH QUERY CACHE语句来清空free blocks。
Qcache_free_memory：Query Cache中目前剩余的内存大小。这个参数可以较为准确的观察出当前系统中的Query Cache内存大小是否足够，是需要增加还是过多了。
Qcache_hits：表示有多少次命中缓存。主要通过该值来验证查询缓存的效果。数字越大，缓存效果越理想。
Qcache_inserts：表示多少次未命中然后插入，新的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这种情况的次数越多，表示查询缓存应用到的越少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。
Qcache_lowmem_prunes：多少条Query因内存不足而被清除出Query Cache。
通过“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合，能够更清楚的了解系统中Query Cache的内存大小是否足够，是否非常频繁的出现因为内存不足而有Query被换出。
这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。
(上面的free_blocks和free_memory可以告诉清楚的表示属于哪种情况)。
Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是SELECT语句或者用了now()之类的函数。
Qcache_queries_in_cache：当前Query Cache中cache的Query数量。
Qcache_total_blocks：当前Query Cache中的block数量。
查询一下服务器关于query_cache的配置
mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1048576 |
| query_cache_type | OFF |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
6 rows in set (0.00 sec)
可以看出query_cache_type为off表示不缓存任何查询。
参数解释
query_cache_limit：超过此大小的查询将不缓存。
query_cache_min_res_unit：缓存块的最小大小，配置是一柄”双刃剑”，默认是4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，就容易造成内存碎片和浪费。
query_cache_size：查询缓存大小(QC存储的最小单位是1024byte，如果设定了一个不是1024的倍数的值，这个值会被四舍五入到最接近当前值的等于1024的倍数的值)。
query_cache_type：缓存类型，决定缓存什么样的查询，注意这个值不能随便设置，必须设置为数字。
可选项目以及说明如下：
query_cache_type=0（OFF）
query_cache_type=1（ON）
query_cache_type=2(DEMAND)
如果设置为0，相当于禁用。
如果设置为1，缓存所有的结果，除非select语句使用SQL_NO_CACHE禁用查询缓存。
如果设置为2，只缓存在select语句中通过SQL_CACHE指定需要缓存的查询。
query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成再读表获取结果。
修改/etc/my.cnf，配置完后的部分文件如下
query_cache_size=256M
query_cache_type=1
重新启动MYSQL服务，然后通过如下查询来验证是否真正开启。
查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%
如果查询缓存碎片率超过20%，可以用FLUSH QUERY CACHE整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。
查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%
查询缓存利用率在25%以下的话说明query_cache_size设置的过大，可适当减小；查询缓存利用率在80％以上而且Qcache_lowmem_prunes > 50说明query_cache_size可能有点小，要不就是碎片太多。
查询缓存命中率=Qcache_hits/(Qcache_hits + Qcache_inserts) * 100%
Query Cache的限制
a)所有子查询中的外部查询SQL不能被Cache；
b)在Procedure，Function以及Trigger中的Query不能被Cache；
c)每次执行可能得到不一样结果的函数的Query不能被Cache。
鉴于这些限制，在使用Query Cache的过程中，建议通过精确设置的方式来使用，仅让合适的表的数据可以进入Query Cache，仅让某些Query的查询结果被Cache。
(3)max_connect_errors
是MySQL中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况。当超过指定次数，MYSQL服务器将禁止host的连接请求，直到mysql服务器重启或通过flush hosts命令清空此host的相关信息。max_connect_errors的值与性能没有太大关系。
修改/etc/my.cnf文件，在[mysqld]下面添加如下内容
max_connect_errors=20
重启MySQL Server进入后，查看设置已经生效。
(4)sort_buffer_size
每个需要进行排序的线程分配该大小的一个缓冲区。增加此值会加速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)
是一个connection级参数，在每个connection(session)第一次需要使用这个buffer时，一次性分配设置的内存。
并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。
例如：500个连接将会消耗500*sort_buffer_size(2M)=1G内存
设置sort_buffer_size可以修改/etc/my.cnf文件，在[mysqld]下面添加如下内容。
sort_buffer_size = 2M
重启MySQL Server进入后，查看设置已经生效。
(5)max_allowed_packet = 32M
MySQL根据配置文件会限制Server接受的数据包大小。
mysql> show variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+
1 row in set (0.00 sec)
有时大的插入和更新会受max_allowed_packet参数限制，导致写入或者更新失败。最大值是1GB，必须设置1024的倍数。
(6)join_buffer_size = 2M
用于表间关联缓存的大小。
mysql> show variables like 'join_buffer_size';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.00 sec)
和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
(7)thread_cache_size = 300
服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量。
mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| thread_cache_size | 9 |
+-------------------+-------+
1 row in set (0.00 sec)
当断开连接时，客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限)试图连接到MySQL(不管是否连接成功)的连接数。如果线程重新被请求，那么请求将从缓存中读取，如果缓存中是空的或者是新的请求，那么这个线程将被重新创建，如果有很多新的线程，增加这个值可以改善系统性能。
通过比较Connections和Threads_created状态的变量，可以看到这个变量的作用。
设置规则如下：1GB内存配置为8，2GB配置为16，3GB配置为32，4GB或更高内存，可配置更大。
mysql> show status like 'threads%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 0 |
| Threads_connected | 1 |
| Threads_created | 1 |
| Threads_running | 1 |
+-------------------+-------+
4 rows in set (0.00 sec)
参数解释
Threads_cached：代表当前此时此刻线程缓存中有多少空闲线程。
Threads_connected：代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
Threads_created：代表从最近一次服务启动，已创建线程的数量，如果Threads_created值过大，表明MySQL服务器一直在创建线程，这比较耗资源，可以适当增加配置文件中thread_cache_size值。
Threads_running：代表当前激活的(非睡眠状态)线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态。
配置InnoDB的几个变量
(1)innodb_buffer_pool_size
对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用。
InnoDB使用该参数指定大小的内存来缓冲数据和索引。对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%。
根据MySQL手册，对于2G内存的机器，推荐值是1G(50%)。如果数据量不大，并且不会暴增，那么无需设置的太大。
mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)
设置innodb_buffer_pool_size可以修改/etc/my.cnf文件，在[mysqld]下面添加如下内容。
innodb_buffer_pool_size = 2048M
重启MySQL Server，进入查看设置已经生效。
(2)innodb_flush_log_at_trx_commit
主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。
mysql> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1 |
+--------------------------------+-------+
1 row in set (0.00 sec)
0表示当事务提交时，不做日志写入操作，而是每秒钟将log buffer中的数据写入日志文件并flush磁盘一次；
1表示在每秒钟或是每次事务提交都会引起日志文件写入并flush磁盘的操作，确保了事务的ACID；
2表示每次事务提交引起写入日志文件的动作，之后每秒完成一次flush磁盘操作。
实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。根据MySQL手册，在允许丢失最近部分事务危险的前提下，可以把该值设为0或2。
(3)innodb_thread_concurrency = 0
此参数用来设置innodb线程的并发数量，默认值为0表示不限制。
mysql> show variables like 'innodb_thread_concurrency';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| innodb_thread_concurrency | 0 |
+---------------------------+-------+
1 row in set (0.00 sec)
若要设置则与服务器的CPU核数相同或是cpu的核数的2倍，建议用默认设置，一般为8。
(4)innodb_log_buffer_size
此参数确定写日志文件所用的内存大小，以M为单位。
mysql> show variables like 'innodb_log_buffer_size';
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+
1 row in set (0.00 sec)
缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。
innodb_log_buffer_size=32M
(5)innodb_log_file_size = 50M
此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能。
mysql> show variables like 'innodb_log_file_size';
+----------------------+----------+
| Variable_name | Value |
+----------------------+----------+
| innodb_log_file_size | 50331648 |
+----------------------+----------+
1 row in set (0.00 sec)
(6)innodb_log_files_in_group = 3
为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3。
mysql> show variables like 'innodb_log_files_in_group';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| innodb_log_files_in_group | 2 |
+---------------------------+-------+
1 row in set (0.00 sec)
(7)read_buffer_size = 1M
MySQL读入缓冲区大小。
mysql> show variables like 'read_buffer_size';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| read_buffer_size | 131072 |
+------------------+--------+
1 row in set (0.00 sec)
对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。
(8)read_rnd_buffer_size = 16M
MySQL的随机读(查询操作)缓冲区大小。
mysql> show variables like 'read_rnd_buffer_size';
+----------------------+--------+
| Variable_name | Value |
+----------------------+--------+
| read_rnd_buffer_size | 262144 |
+----------------------+--------+
1 row in set (0.00 sec)
当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。
注意：
顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。
随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机的。
(9)bulk_insert_buffer_size = 64M
批量插入数据缓存大小，可以有效提高插入效率，默认为8M。
mysql> show variables like 'bulk_insert_buffer_size';
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| bulk_insert_buffer_size | 8388608 |
+-------------------------+---------+
1 row in set (0.00 sec)
(10)binary log
binlog_cache_size = 2M
为每个session分配的内存，在事务过程中用来存储二进制日志的缓存，提高记录bin-log的效率。
mysql> show variables like 'binlog_cache_size';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| binlog_cache_size | 32768 |
+-------------------+-------+
1 row in set (0.00 sec)
没有什么大事务，dml也不是很频繁的情况下可以设置小一点，如果事务大而且多，dml操作也频繁，则可以适当的调大一点。前者建议是–1M，后者建议是：即 2–4M。
max_binlog_cache_size = 8M
表示的是binlog能够使用的最大cache内存大小。
mysql> show variables like 'max_binlog_cache_size';
+-----------------------+----------------------+
| Variable_name | Value |
+-----------------------+----------------------+
| max_binlog_cache_size | 18446744073709547520 |
+-----------------------+----------------------+
1 row in set (0.00 sec)
max_binlog_size = 512M
指定binlog日志文件的大小，如果当前的日志大小达到max_binlog_size，还会自动创建新的二进制日志。
mysql> show variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)
你不能将该变量设置为大于1GB或小于4096字节。默认值是1GB。在导入大容量的sql文件时，建议关闭sql_log_bin，否则硬盘扛不住，而且建议定期做删除。
expire_logs_days = 7
定义了mysql清除过期日志的时间。
mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 0 |
+------------------+-------+
1 row in set (0.01 sec)
二进制日志自动删除的天数。默认值为0，表示“没有自动删除”。
mysqladmin flush-logs也可以重新开始新的binary log。
(11)log_queries_not_using_indexes
开启这个选项真实地记录了返回所有行的查询。
mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+
1 row in set (0.00 sec)
三、优化配置
1.优化之前执行mysqlslap工具进行测试
[root@localhost ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100,200 --iterations=1 --number-int-cols=20 --number-char-cols=30 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=myisam,innodb --number-of-queries=2000 -uroot -pasd123 --verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.364 seconds
Minimum number of seconds to run all queries: 0.364 seconds
Maximum number of seconds to run all queries: 0.364 seconds
Number of clients running queries: 100
Average number of queries per client: 20
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.416 seconds
Minimum number of seconds to run all queries: 0.416 seconds
Maximum number of seconds to run all queries: 0.416 seconds
Number of clients running queries: 200
Average number of queries per client: 10
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.998 seconds
Minimum number of seconds to run all queries: 0.998 seconds
Maximum number of seconds to run all queries: 0.998 seconds
Number of clients running queries: 100
Average number of queries per client: 20
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.809 seconds
Minimum number of seconds to run all queries: 0.809 seconds
Maximum number of seconds to run all queries: 0.809 seconds
Number of clients running queries: 200
Average number of queries per client: 10
2.优化之后执行mysqlslap工具进行测试
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
pid-file=/usr/local/mysql/data/mysqld.pid
log-error=/usr/local/mysql/data/mysqld.err
socket=/tmp/mysql.sock
slow_query_log = 1
slow_query_log_file = /usr/local/mysql/data/slow-query.log
long_query_time = 1
log-queries-not-using-indexes
max_connections = 1024
back_log = 128
wait_timeout = 60
interactive_timeout = 7200
key_buffer_size=256M
query_cache_size = 256M
query_cache_type=1
query_cache_limit=50M
max_connect_errors=20
sort_buffer_size = 2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=200
innodb_buffer_pool_size = 2048M
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
log-bin=mysql-bin
server-id=1
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
read_buffer_size=1M
read_rnd_buffer_size=16M
bulk_insert_buffer_size=64M
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL...... SUCCESS!
[root@localhost ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100,200 --iterations=1 --number-int-cols=20 --number-char-cols=30 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=myisam,innodb --number-of-queries=2000 -uroot -pasd123 --verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.720 seconds
Minimum number of seconds to run all queries: 0.720 seconds
Maximum number of seconds to run all queries: 0.720 seconds
Number of clients running queries: 100
Average number of queries per client: 20
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 1.018 seconds
Minimum number of seconds to run all queries: 1.018 seconds
Maximum number of seconds to run all queries: 1.018 seconds
Number of clients running queries: 200
Average number of queries per client: 10
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 1.060 seconds
Minimum number of seconds to run all queries: 1.060 seconds
Maximum number of seconds to run all queries: 1.060 seconds
Number of clients running queries: 100
Average number of queries per client: 20
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.848 seconds
Minimum number of seconds to run all queries: 0.848 seconds
Maximum number of seconds to run all queries: 0.848 seconds
Number of clients running queries: 200
Average number of queries per client: 10
3.相关优化参数总结
[mysqld]
slow_query_log = 1
slow_query_log_file = /usr/local/mysql/data/slow-query.log
long_query_time = 1
log-queries-not-using-indexes
max_connections = 1024
back_log = 128
wait_timeout = 60
interactive_timeout = 7200
key_buffer_size=256M
query_cache_size = 256M
query_cache_type=1
query_cache_limit=50M
max_connect_errors=20
sort_buffer_size = 2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=200
innodb_buffer_pool_size = 2048M
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
log-bin=mysql-bin
server-id=1
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
read_buffer_size=1M
read_rnd_buffer_size=16M
bulk_insert_buffer_size=64M
四、MySQL计划任务
MySQL自身也支持计划任务，可以通过event语句实现周期性的任务，如定时增删数据的操作。
1.开启MySQL计划任务
mysql> show variables like 'event%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
1 row in set (0.00 sec)
mysql> set global event_scheduler=1;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'event%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set (0.00 sec)
2.MySQL计划任务
语法格式
CREATE
[DEFINER = user]
EVENT
[IF NOT EXISTS]
event_name
ON SCHEDULE schedule
[ON COMPLETION [NOT] PRESERVE]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'string']
DO event_body;
schedule: {
AT timestamp [+ INTERVAL interval] ...
| EVERY interval
[STARTS timestamp [+ INTERVAL interval] ...]
[ENDS timestamp [+ INTERVAL interval] ...]
}
interval:
quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
(1)在指定时间清空表
mysql> use test;
Database changed
mysql> create table test(a int,b varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> create event test_event on schedule at timestamp '2021-1-18 12:00:00' do truncate table test;
Query OK, 0 rows affected (0.00 sec)
(2)每天定时清空表
mysql> use test;
Database changed
mysql> create event test_event1 on schedule every 1 day do truncate table test;
Query OK, 0 rows affected (0.00 sec)
(3)5天后开启每天定时清空表的
mysql> use test;
Database changed
mysql> create event test_event2 on schedule every 1 day starts current_timestamp + interval 5 day do truncate table test;
Query OK, 0 rows affected (0.00 sec)
(4)每天定时清空表，5天后停止该任务
mysql> use test;
Database changed
mysql> create event test_event3 on schedule every 1 day ends current_timestamp + interval 5 day do truncate table test;
Query OK, 0 rows affected (0.00 sec)
(5)5天后开启每天定时清空表的任务，一个月后停止。
mysql> use test;
Database changed
mysql> create event test_event4 on schedule every 1 day starts current_timestamp + interval 5 day ends current_timestamp + interval 1 month do truncate table test;
Query OK, 0 rows affected (0.00 sec)
3.查看计划任务
mysql> show events\G
*************************** 1. row ***************************
Db: test
Name: test_event
Definer: root@localhost
Time zone: SYSTEM
Type: ONE TIME
Execute at: 2021-01-18 12:00:00
Interval value: NULL
Interval field: NULL
Starts: NULL
Ends: NULL
Status: ENABLED
Originator: 0
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
Db: test
Name: test_event1
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: DAY
Starts: 2021-01-17 19:01:08
Ends: NULL
Status: ENABLED
Originator: 0
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
*************************** 3. row ***************************
Db: test
Name: test_event2
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: DAY
Starts: 2021-01-22 19:02:56
Ends: NULL
Status: ENABLED
Originator: 0
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
*************************** 4. row ***************************
Db: test
Name: test_event3
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: DAY
Starts: 2021-01-17 19:05:40
Ends: 2021-01-22 19:05:40
Status: ENABLED
Originator: 0
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
*************************** 5. row ***************************
Db: test
Name: test_event4
Definer: root@localhost
Time zone: SYSTEM
Type: RECURRING
Execute at: NULL
Interval value: 1
Interval field: DAY
Starts: 2021-01-22 19:06:53
Ends: 2021-02-17 19:06:53
Status: ENABLED
Originator: 0
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
5 rows in set (0.00 sec)
4.修改计划任务
语法格式
ALTER
[DEFINER = user]
EVENT event_name
[ON SCHEDULE schedule]
[ON COMPLETION [NOT] PRESERVE]
[RENAME TO new_event_name]
[ENABLE | DISABLE | DISABLE ON SLAVE]
[COMMENT 'string']
[DO event_body]
534
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
