MySQL系统调优及问题查找(三)
某一个或者某一类操作。
MySql MyISAM、INNODB类型表碎片优化
针对MyISAM表类型采用 OPTIMIZE TABLE table_name SQL语句清理碎片.
InnoDB 使用的 Clustered Index,索引和数据绑定在一起,重排序是不现实的.所以不支持 MyISAM 式的 OPTIMIZE,而是绑定到了ALTER TABLE 命令上面.可以通过执行以下语句来整理碎片,提高索引速度:
ALTER TABLE table_name ENGINE = Innodb;
这其实是一个NULL操作,表面上看什么也不做,实际上重新整理碎片了.当执行优化操作时,实际执行的是一个空的 ALTER 命令,但是这个命令也会起到优化的作用,它会重建整个表,删掉未使用的空白空间.
innodb 存储引擎优化
Innodb 存储引擎和 MyISAM 存储引擎最大区别主要有四点，第一点是缓存机制，第二点是事务支持 ，第三点是锁定实现，最后一点就是数据存储方式的差异。在整体性能表现方面， Innodb 和 MyISAM 两个存储引擎在不同的场景下差异比较大，主要原因也正是因为上面这四个主要区别所造成的。 www.2cto.com
Innodb_buffer_pool_size
假设是一台单独给 MySQL 使用的主机，物理内存总大小为 8G ， MySQL 最大连接数为 500 ，同时还使用了 MyISAM 存储引擎，这时候我们的整体内存该如何分配呢？
内存分配为如下几大部分：
1、系统使用，假设预留 800M ；
2、线程独享，约 2GB = 500 * (1MB + 1MB + 1MB + 512KB + 512KB) ，组成大概如下 :
sort_buffer_size ： 1MB
join_buffer_size ： 1MB
read_buffer_size ： 1MB
read_rnd_buffer_size ： 512KB
thread_statck ： 512KB
3、MyISAM Key Cache ，假设大概为 1.5GB ；
4、Innodb Buffer Pool 最大可用量： 8GB - 800MB - 2GB - 1.5GB = 3.7GB
>show status like 'Innodb_buffer_pool_%';
Innodb_Buffer_pool_HitRatio = ( Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads ) / Innodb_buffer_pool_read_requests * 100%
buffer pool 使用率 = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%
innodb_log_buffer_size 参数的使用
顾名思义，这个参数就是用来设置 Innodb 的 Log Buffer 大小的，系统默认值为 1MB 。 Log Buffer的主要作用就是缓冲 Log 数据，提高写 Log 的 IO 性能。一般来说，如果你的系统不是写负载非常高且以大事务居多的话， 8MB 以内的大小就完全足够了。
>show status like 'innodb_log%'; (查看innodb_log_buffer_size 设置是否合理)
Innodb 存储引擎的物理存储结构：
最小单位： page（默认16KB）——＞extent（64连续的page）——>segment（一个或多个extent）——>tablespace（最大的物理结构单位，由多个segment组成）
InnoDB 性能监控
>show innodb status\G
持续获取状态信息的方法： create table innodb_monitor(a int) engine=innodb;
创建一个innodb_monitor空表后，InnoDB就会每隔15秒输出一次信息并记录到Error Log中，通过删除该表停止监控
除此之外，我们还可以通过相同的方式打开和关闭 innodb_tablespace_monitor, innodb_lock_monitor, innodb_table_monitor 这三种监控功能
MySQL 高可用性方案
1、MySQL Replication
2、MySQL Cluster
3、DRDB