MySQL5.1性能优化方案

MySQL5.1性能优化方案

1.平台数据库

1.1.操作系统

Red Hat Enterprise Linux Server release 5.4 (Tikanga)

ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, stripped

32位Linux服务器，单独作为MySQL服务器使用。

1.2.M ySQL

系统使用的是MySQL5.1，最新的MySQL5.5较之老版本有了大幅改进。主要体现在以下几个方面：

1）默认存储引擎更改为InnoDB

InnoDB作为成熟、高效的事务引擎，目前已经广泛使用，但MySQL5.1之前的版本默认引擎均为MyISAM，此次MySQL5.5终于将默认数据库存储引擎改为InnoDB，并且引进了Innodb plugin 1.0.7。此次更新对数据库的好处是显而易见的：InnoDB的数据恢复时间从过去的一个甚至几个小时，缩短到几分钟(InnoDB plugin 1.0.7，InnoDB plugin 1.1，恢复时采用红-黑树)。InnoDB Plugin 支持数据压缩存储，节约存储，提高内存命中率，并且支持adaptive flush checkpoint, 可以在某些场合避免数据库出现突发性能瓶颈。

Multi Rollback Segments：原来InnoDB只有一个Segment，同时只支持1023的并发。现已扩充到128个Segments，从而解决了高并发的限制。

2）多核性能提升

Metadata Locking (MDL) Framework替换LOCK_open mutex (lock)，使得MySQL5.1及过去版本在多核心处理器上的性能瓶颈得到解决。

3）制功能(Replication)加强

过去的异步复制方式意味着极端情况下的数据风险，MySQL5.5将首次支持半同步(semi-sync replication)在MySQL的高可用方案中将产生更多更加可靠的方案。

4）增强表分区功能

MySQL 5.5的分区更易于使用的增强功能，以及TRUNCATE PARTITION命令都可以为管理和维护数据库节省大量的时间，并且具有更加灵活高效的分区方式。

1.3.C PU

系统所用CPU是单个4核CPU。对于CPU密集的负载，MySQL通常从更快的CPU中获益，而不是更多CPU。MySQL5.1的架构对多CPU的扩展性不好，并且MySQL不能在多个CPU上并行地运行某个查询，因此在对于单个CPU进行密集的查询时，CPU速度限制了响应时间。为了实现低延迟，即快速响应时间，需要快速的CPU，因为单个查询只能使用一个CPU。值得注意的是，MySQL5.5在多核心处理器上的性能有了很大的提升。另外，MySQL在64位架构上工作得更好，比32位架构更能有效地使用大量内存。

尽管本系统使用的是32位操作系统，CPU运行在32位模式下，但它仍支持64位计算。（cat /proc/cpuinfo | grep flags | grep ' lm ' | wc -l）

1.4.磁盘空间

系统的磁盘空间目前没有压力。

1.5.内存

内存总大小为4G，只供操作系统和数据库使用。

1.6.数据库的表和文件

数据库addb共有339张表：其中InnoDB表303张，MyISAM表34张，MEMORY表2张。

InnoDB数据文件ibdata1大小为30138MB，一周后ibdata1大小为30234MB，

MyISAM数据文件（包括表结构、索引及数据）总大小约为1642MB，一周后约为1639MB。可以看出，数据库的数据量较稳定，InnoDB数据文件增加了约106MB，总大小一周内没有大的变化。MyISAM表中，值得注意的是表terminalalarm_bak，该表总大小约为1623MB，占整个MyISAM 表总大小比重近99%。

二进制日志单个文件大小为1GB，二进制日志文件总大小接近20GB。

1.7.数据分布情况

服务器某时间点非精确值：

1< rows<1万136张（MyISAM表9张，MEMORY表2张）

rows=0（无数据）140张

观察系统中数据量很大且未进行表分区的InnoDB表

adrotateresultdetail_fail的数据量达到4千万，createTime列是datatime类型，且有索引，意味着存在以该列为查询条件或关联条件查询的需求，因此可以在该列上以自然月份进行表分区。

terminalalarm的数据量也突破千万，AlarmTime列是datatime类型，且有索引，意味着存在以该列为查询条件或关联条件查询的需求，因此可以在该列上以自然月份进行表分区。在事件ev_terminalalarm中会查询该表，若进行表分区，也能一定程度上提高事件的执行效率。

terminalalarminfo表仅自增列有索引，主要用于存储数据，可不用分区。

Terminallogin表的loginTime列是datatime类型，且有索引，意味着存在以该列为查询条件或关联条件查询的需求，因此可以在该列上以自然月份进行表分区。

adplayinfo_bak表存在多个以INT类型为索引的列，根据实际业务情况选择查询频率高且能以范围值来分区的整型列对该表进行分区。

adrotateresultdetail的createTime列是datatime类型，且有索引，意味着存在以该列为查询条件或关联条件查询的需求，因此可以在该列上以自然月份进行表分区。

upfile_bak表仅自增列有索引，若存在查询或者统计业务则可以createTime列进行分区，若该表没有查询方面业务可不必进行分区。

除去配置参数等属性表，对于数据量大且不断递增的业务数据表，最直接的办法可以按照时间字段进行分区，或是根据查询业务来选择合适的列进行表分区和创建索引，这样能够有效提高存储和查询效率。