关于mysql优化_关于mysql优化

1    关于mysql

Paxos/Raft引入MySQL

2    Mysql优化

底层存储配置,例如用SAS不是SATA、用RAID5还是RAID10，只要花钱就能解决的就不说了，如果不知道怎么做交给存储供应商给提供吧。

还有一些和钱无关的事情。

使用 noatime 和 nodirtime 来挂载文件系统,可以通过mount命令来查看。

设置 swappiness 的值为0,在数据库服务器上不用缓存文件，这个一般在Web服务器或桌面应用中使用。通过命令cat /proc/sys/vm/swappiness查看。

使用 NOOP 或 DEADLINE IO调度器 — CFQ 和ANTICIPATORY 调度器已经被证明比 NOOP 和 DEADLINE 慢,cat /sys/block/sda/queue/scheduler

文件系统使用xfs文件系统，不用ext3；ext4勉强可用，但业务量很大的话，可以使用xfs。

3    Mysql分析命令

showprocesslist; 查看当前进程链接及状态：

show status;显示状态信息

show variables;显示系统变量

show innodbstatus;显示InnoDB存储引擎的状态

4    Mysql数据库Tips

4.1    查看数据库大小

useinformation_schema;

a)查询所有数据的大小：

selectconcat(round(sum(data_length/1024/1024),2),'MB') as data from tables;

b)查看指定数据库的大小：

比如查看数据库home的大小

select concat(round(sum(data_length/1024/1024),2),'MB')as data from tables where table_schema='home';

c)查看指定数据库的某个表的大小

比如查看数据库home中 members 表的大小

selectconcat(round(sum(data_length/1024/1024),2),'MB') as data from tables wheretable_schema='home' and table_name='members';

5    mysql日志写问题

innodb_flush_log_at_trx_commit和sync_binlog 两个参数是控制MySQL 磁盘写入策略以及数据安全性的关键参数。

如果innodb_flush_log_at_trx_commit设置为0，log buffer将每秒一次地写入logfile中，并且log file的flush(刷到磁盘)操作同时进行.该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。

如果innodb_flush_log_at_trx_commit设置为1，每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去.

如果innodb_flush_log_at_trx_commit设置为2，每次事务提交时MySQL都会把log buffer的数据写入logfile.但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。

sync_binlog 的默认值是0，像操作系统刷其他文件的机制一样，MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。

当sync_binlog=N (N>0) ，MySQL 在每写 N次二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。

6    关于Mysql doublewrite

mysql中有个doublewrite参数， 存放在表空间中或是单独指定的某个文件中的一个buffer。

os写文件的写机制, 在mysql中，每次写数据(仅数据文件)到磁盘中的时候是以page作为单位的.

一般都是采用的是16KB大小，可以在数据库初始化的时候使用innodb_page_size来定义.

mysql> show global variables like'%innodb_page_size%';

而os级别写文件是以4KB作为单位的,每写一个innodb的page到磁盘上，在os级别上需要写4个块. 当mysql在写一个innodbpage到磁盘上时，如果在写这个page的过程中发生了意外的事件，比如断电，mysql崩溃等。会使page数据出现不一样的情形，从而形成一个"断裂"的page.使数据产生混乱.这个时候innodb对这种块错误错误是无能为力的。

doublewrite buffer表示每次innodb在准备写出一个page时，先把page写到doublewritebuffer中.如果在写doublewrite buffer时，发生了意外，但是数据文件中的原来的page不受影响，这样在下次启动时，可以通过innodb的redolog进行恢复。如果写doublewrite buffer成功后，mysql会把doublewrite buffer的内容写到数据文件中，如果在这个过程又出现了意外，重启后mysql可以通过从doublewrite buffer找到好的page，再用该好的page去覆盖磁盘上坏的page即可。

在正常的情况下mysql写数据page时，会写两遍到磁盘上，第一遍是写到doublewrite buffer,第二遍是从doublewrite buffer写到真正的数据文件中.

7    清理Mysql的binlog

方法1：PURGE MASTER LOGS

用于删除列于在指定的日志或日期之前的日志索引中的所有二进制日志。这些日志也会从记录在日志索引文件中的清单中被删除，这样被给定的日志成为第一个。

方法2：手动删除binlog日志文件

删除文件mysql-bin.00000X

编辑mysql-bin.index，删除指定的mysql-bin文件。

方法3：指定过期天数(expire_logs_days)

该参数为全局可动态调整参数，默认值为0，即关闭，取值范围0-99。

show variables like 'expire_logs_days';

方法4：重置binlog(reset master)

该方法可以删除列于索引文件中的所有二进制日志，把二进制日志索引文件重新设置为空，并创建一个以.000001为后缀新的二进制日志文件。

清理mysql的相关命令如下：

show master logs;

PURGE BINARY LOGS TO 'mysql-bin.000001';

PURGE BINARY LOGS BEFORE '2014-04-28 23:59:59';

show variables like 'expire_logs_days';

set global expire_logs_days = 60;

flush logs;

RESET MASTER;

8    参考

8.1    mysql参数优化

http://www.jb51.net/article/48082.htm

9    其他相关

9.1    Percona

Percona Server为 MySQL 数据库服务器进行了改进，在功能和性能上较 MySQL 有着很显著的提升。该版本提升了在高负载情况下的 InnoDB 的性能、为 DBA 提供一些非常有用的性能诊断工具；另外有更多的参数和命令来控制服务器行为。

10                          TIPS

10.1 挂载文件系统日志记录方式

mounted filesystem with ordered data mode.Opts: (null)

答：提供三种数据日志记录方式： data=writeback 、 data=ordered  (默认) data=journal。

mount -o data=journal /dev/sdb /home/test