优化mysql server、I/O及应用优化

优化Mysql Server
一、查看Mysqlserver当前参数
1. 查看服务器参数默认值：
mysqld --verbose --help

2. 查看服务器参数实际值：
shell>mysqladmin variables
或者
mysql>SHOW VARIABLES;
3. 查看服务器运行状态值：
shell>mysqladmin extended-status
或者
mysql>SHOW STATUS；

二、影响Mysql性能的重要参数
1. Key_buffer_size
说明：键缓存(变量key_buffer_size)被所有线程共享；服务器使用的其它缓存则根据需要分配。此参数只适用于myisam存储引擎。
使用方法：
mysql 5.1以前只允许使用一个系统默认的key_buffer
mysql 5.1以后提供了多个key_buffer，可以将指定的表索引缓存入指定的key_buffer，这样可以更小的降低线程之间的竞争.

2. table_cache的设置
说明：数据库中打开表的缓存数量。table_cache与max_connections有关。例如，对于200个并行运行的连接，应该让表的缓存至少有200*N，这里N是可以执行的查询的一个联接中表的最大数量。还需要为临时表和文件保留一些额外的文件描述符。
设置技巧：
可以通过检查mysqld的状态变量Opened_tables确定表缓存是否太小：
mysql> SHOW STATUS LIKE 'Opened_tables';
+---------------+-------+
|Variable_name|Value|
+---------------+-------+
|Opened_tables|2741 |
+---------------+-------+
如果值很大，即使你没有发出许多FLUSHTABLES语句，也应增加表缓存的大小。

3. innodb_buffer_pool_size 的设置：
缓存InnoDB数据和索引的内存缓冲区的大小。你把这个值设得越高，访问表中数据需要得磁盘I/O越少。在一个专用的数据库服务器上，你可以设置这个参数达机器物理内存大小的80%。尽管如此，还是不要把它设置得太大，因为对物理内存的竞争可能在操作系统上导致内存调度。

4. innodb_flush_log_at_trx_commit 的设置：
0：日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新，但是在一个事务提交不做任何操作。
1：在每个事务提交时，日志缓冲被写到日志文件，对日志文件做到磁盘操作的刷新。
2：在每个提交，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新。对日志文件每秒刷新一次。

默认值是1，也是最安全的设置，即每个事务提交的时候都会从log buffer写到日志文件，而且会实际刷新磁盘，但是这样性能有一定的损失。如果可以容忍在数据库崩溃的时候损失一部分数据，那么设置成0或者2都会有所改善。设置成0，则在数据库崩溃的时候会丢失那些没有被写入日志文件的事务，最多丢失1秒钟的事务，这种方式是最不安全的，也是效率最高的。设置成2的时候，因为只是没有刷新到磁盘，但是已经写入日志文件，所以只要操作系统没有崩溃，那么并没有丢失数据 ，比设置成0更安全一些。
在mysql的手册中，为了确保事务的持久性和复制设置的耐受性、一致性，都是建议将这个参数设置为1的。

5. innodb_additional_mem_pool_size：
InnoDB用来存储数据目录信息和其它内部数据结构的内存池的大小。默认值是1MB。应用程序里的表越多，你需要在这里分配越多的内存。如果InnoDB用光了这个池内的内存，InnoDB开始从操作系统分配内存，并且往MySQL错误日志写警告信息。
没有必要给这个缓冲池分配非常大的空间，在应用相对稳定的情况下，这个缓冲池的大小也相对稳定。

6. innodb_table_locks：
InnoDB重视LOCK TABLES，直到所有其它线程已经释放他们所有对表的锁定，MySQL才从LOCK TABLE..WRITE返回。默认值是1，这意为LOCKT ABLES让InnoDB内部锁定一个表。在使用AUTOCOMMIT=1的应用里，InnoDB的内部表锁定会导致死锁。可以通过设置innodb_table_locks=0来消除这个问题。

7. innodb_lock_wait_timeout：
Mysql可以自动的监测行锁导致的死锁并进行相应的处理，但是对于表锁导致的死锁不能自动的监测，所以该参数主要被用来在出现类似情况的时候对锁定进行的后续处理。默认值是50秒，根据应用的需要进行调整。

8. innodb_support_xa：
通过该参数设置是否支持分布式事务，默认值是ON或者1，表示支持分布式事务。如果确认应用中不需要使用分布式事务，则可以关闭这个参数，减少磁盘刷新的次数并获得更好的InnoDB性能。

9. innodb_doublewrite：
默认地，InnoDB存储所有数据两次，第一次存储到doublewrite缓冲，然后存储到确实的数据文件。如果对性能的要求高于对数据完整性的要求，那么可以通过--skip-innodb-doublewrite关闭这个设置。

10. innodb_log_buffer_size:
默认的设置在中等强度写入负载以及较短事务的情况下，服务器性能还可以。如果存在更新操作峰值或者负载较大，就应该考虑加大它的值了。如果它的值设置太高了，可能会浪费内存--它每秒都会刷新一次，因此无需设置超过1秒所需的内存空间。通常8-16MB就足够了。越小的系统它的值越小。

11. innodb_log_file_size：
在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高，但是要注意到可能会增加恢复时间。

I/O 问题
磁盘搜索是巨大的性能瓶颈。当数据量变得非常大以致于缓存性能变得不可能有效时，该问题变得更加明显。对于大数据库，其中你或多或少地随机访问数据，你可以确信对读取操作需要至少一次硬盘搜索，写操作需要多次硬盘搜索。要想使该问题最小化，应使用搜索次数较少的磁盘。
1. 使用磁盘阵列或虚拟文件卷分布I/O
2. 使用Symbolic Links分布I/O
可以将表和数据库从数据库目录移动到其它的位置并且用指向新位置的符号链接进行替换。推荐的方法只需要将数据库通过符号链接指到不同的磁盘。符号链接表仅作为是最后的办法。
符号链接一个数据库的方法是，首先在一些有空闲空间的硬盘上创建一个目录，然后从MySQL数据目录中创建它的一个符号链接。
shell>mkdir /dr1/databases/test
shell>ln -s /dr1/databases/test /path/to/datadir
注意：只有MyISAM表完全支持符号链接。对于其它表类型，如果试图在操作系统中的文件上用前面的任何语句使用符号链接，可能会出现奇怪的问题。
对于MyISAM表的符号链接的处理如下：
1. 在数据目录指，一定会有表定义文件、数据文件和索引文件。数据文件和索引文件可以移到别处和在数据目录中符号链接替代。表定义文件不能进行符号链接替换。
2. 可以分别通过符号链接将数据文件和索引文件指到不同的目录。
3. 如果mysqld没有运行，符号链接可以从服务器命令行使用ln-s手动完成。同样，通过使用DATADIRECTORY和INDEXDIRECTORY选项创建表，你可以指示运行的MySQL服务器执行符号链接。
4. myisamchk不用数据文件或索引文件替换符号链接。它直接工作在符号链接指向的文件。任何临时文件创建在数据文件或索引文件所处的目录中。
5. 注释：当你删掉一个表时，如果该表使用了符号链接，符号链接和该符号链接指向的文件都被删除掉。这就是你不应以系统root用户运行mysqld或允许系统用户对MySQL数据库目录有写访问权限的原因。
6. 如果你用ALTERTABLE...RENAME重命名一个表并且不将表移到另一个数据库，数据库目录中的符号链接被重新命名为一个新名字并且数据文件和索引文件也相应地重新命名。
7. 如果你用ALTERTABLE...RENAME移动一个表到另一个数据库，表移动到另一个数据库目录。旧的符号链接和其所指向的文件被删除。换句话说，新表不再被链接。
8. 如果不使用符号链接，你应对mysqld使用--skip-symbolic-links选项以确保没有人能够使用mysqld来删除或重新命名数据目录之外的文件。

表符号链接还不支持以下操作：
1. ALTER TABLE忽略DATA DIRECTORY和INDEX DIRECTORY表选项。
2. BACKUP TABLE和RESTORE TABLE不考虑符号链接。
3. .frm文件必须绝不能是一个符号链接（如前面所述，只有数据和索引文件可以是符号链接）。如果试图这样做（例如，生成符号链接）会产生不正确的结果。

应用优化
1. 使用连接池
2. 减少对Mysql的访问
2.1 避免对同一数据做重复检索
2.2 使用mysql query cache
作用：查询缓存存储SELECT查询的文本以及发送给客户端的相应结果。如果随后收到一个相同的查询，服务器从查询缓存中重新得到查询结果，而不再需要解析和执行查询。
适用范围：不发生数据更新的表。当表更改（包括表结构和表数据）后，查询缓存值的相关条目被清空。
查询缓存的主要参数设置：
show variables like ‘%query_cache%’;
have_query_cache表明服务器在安装使已经配置了高速缓存
query_cache_size表明缓存区大小，单位为M
query_cache_type的变量值从0到2，含义分别为
0或者off（缓存关闭）
1或者on（缓存打开，使用sql_no_cache的select除外）
2或者demand（只有带sql_cache的select语句提供高速缓存）
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             | 33554432 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+

在SHOW STATUS中，你可以监视查询缓存的性能：
# mysqladmin -uroot [e-mail] extended-status|grep Qcache
| Qcache_free_blocks                       | 1           |
| Qcache_free_memory                       | 33536864    |
| Qcache_hits                              | 0           |
| Qcache_inserts                           | 0           |
| Qcache_lowmem_prunes                     | 0           |
| Qcache_not_cached                        | 36          |
| Qcache_queries_in_cache                  | 0           |
| Qcache_total_blocks                      | 1           |

变量                         含义
Qcache_queries_in_cache 在缓存中已注册的查询数目
Qcache_inserts 被加入到缓存中的查询数目
Qcache_hits 缓存采样数数目
Qcache_lowmem_prunes 因为缺少内存而被从缓存中删除的查询数目
Qcache_not_cached 没有被缓存的查询数目(不能被缓存的，或由于QUERY_CACHE_TYPE)
Qcache_free_memory 查询缓存的空闲内存总数
Qcache_free_blocks 查询缓存中的空闲内存块的数目
Qcache_total_blocks 查询缓存中的块的总数目

2.3 加cache层
Cache（高速缓存）、Memory（内存）、Harddisk（硬盘）都是数据存取单元，但存取速度却有很大差异，呈依次递减的顺序。

3 负载均衡
3.1 利用mysql复制分流查询操作
3.2 采用分布式数据库架构