mysql qps提升_上亿数据MYSQL的innodb优化 QPS轻松过5000

最近2周时间， 对于MYSQL 5.5.15-log Windows XP版本，进行了各种测试和优化。目前mysql的许多单表数据已经超过了1亿条记录，MYSQL在经过各种优化后，处理上亿条记录非常轻松，性能远比想象的要好的多。

目前， 硬件情况是 intel Q9300 CPU 2.5G 4核心 775针的老CPU， 内存是2.6G DDR2, 硬盘是5900转2T的希捷节能硬盘, 都是很普通廉价的硬件。

MYSQL的各数据表全部采用innodb引擎，开始使用autocommit, 相当于每一条语句都提交，测试的QPS是：单机2.4万qps，后来修改为取消autocommit, 并且修改代码，缓存了部分中间数据，大幅度减少了一些重复简单的SELECT的命令数量，并且多条记录一次性提交，测试情况是大约 5000 QPS，实际上5000 QPS的等效性能更好一些，因为基本上都是insert和update命令。

中间还测试了memory引擎，定时也测试过，随时插入memory, 定时从memory写到innodb,想这样实现更高的性能，思路是利用内存数据库的高性能，结果不理想， 本来想将insert先插入到memory引擎缓冲，结果，非常失望，memory引擎的并发能力非常有限，偶然会出现插入错误，只好取消了这个思路。 现在看memory引擎的表锁，有时不能给其他进程等待的机会，对于我的应用，本来的思路是先缓冲到memory引擎，满1000条记录，再集中插入innodb, 定时时间设置为30秒，60秒，300秒测试都一样，一旦从memory向innodb写记录，基本其他进程的insert Memory就可能失败，期望是有一个等待的机会，实际测试不行，即使只有几百条记录的缓冲也会锁死， 结果，出现部分插入失败，写入log日志中， 其它进程不能得到应该容忍的等待时间，问题在哪里， 估计是memory只使用表锁，造成的问题。 总之，这个思路受困于Memory差劲的并发能力。

主要的优化：

# 设定默认的事务隔离级别.可用的级别如下:

# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE

# 默认是REPEATABLE_READ,  查看你的程序是否可以运行在READ-COMMITED 隔离模式下,如果可以,

# 这个选项有一些性能的优势,特别是在5.0,5.1版和行级别的复制方面.

# 设置隔离等级， 默认是REPEATABLE-READ

transaction_isolation=READ-COMMITTED

#*** INNODB Specific options ***

innodb_use_sys_malloc =0

# 系统默认 innodb_use_sys_malloc =1, 表示使用操作系统自带的，

#*** INNODB Specific options ***

innodb_use_sys_malloc =0

# 系统默认 innodb_use_sys_malloc =1, 表示使用操作系统自带的，

innodb_data_home_dir      = "E:/mysql/data/"

innodb_data_file_path=ibdata1:20G

innodb_log_group_home_dir = "R:/"

# 设置log文件到虚拟内存盘中去，这样可以提高性能，同时设置innodb_fast_shutdown=0，

# 可以保证关机前数据全部保存到硬盘，这个是最牛的设置，这样等于REDO日志的性能和内存是一样快的。

#innodb_log_arch_dir      = "C:/mysqllog/"

innodb_log_files_in_group=2

innodb_autoextend_increment = 32

innodb_additional_mem_pool_size=8M

innodb_flush_log_at_trx_commit=2

innodb_log_buffer_size=1M

innodb_buffer_pool_size=640M

innodb_log_file_size=63M

innodb_file_io_threads=4

# InnoDB 中的文件 I/O 线程。 通常设置为 4，

# 但是在 Windows 下可以设定一个更大的值以提高磁盘 I/O。

innodb_max_dirty_pages_pct=75

# 默认为75%, 感觉太高了点，以前的默认值更高位90.

innodb_fast_shutdown=0

# 默认为允许快速关闭=1, innodb_fast_shutdown影响着innodb表的行为，该参数可设置为0，1，2

#0 表示当MySQL关闭时，InnoDB需要完成所有的full purge和merge insert buffer操作。耗时比较长。

#1 是默认值。表示不需要完成full purge和merge insert buffer操作，但是在缓冲池中的一些数据脏页会刷新到磁盘。

#2 不完成full purge和merge insert buffer操作，不将缓冲池中的数据脏页写回到磁盘，将日志写入到日志文件。MySQL下次启动执行恢复操作。

# close double write, 默认是打开的

# innodb_doublewrite=0

# 不关闭校验和检查(提供更好的性能)

# innodb_checksums=0

# 不打开单表空间

#innodb_file_per_table = 0

innodb_io_capacity=100

innodb_read_io_threads=1

innodb_open_files=300

innodb_max_purge_lag=2000000

#innodb_import_table_from_xtrabackup=1

innodb_support_xa=0

##如果使用复制， 那么一定要innodb_support_xa=1， 即默认参数

innodb_thread_sleep_delay=40

设置thread延迟sleep时间为40微秒，默认为10000，这个我与默认参数的分歧很大。

innodb_thread_concurrency=4

# InnoDB 会试图将 InnoDB 服务的使用的操作系统进程小于或等于这里所设定的数值。

# 此参数默认值为 8。如果计算机系统性能较低或 innodb_monitor 显示有很多线程等侍信号，

# 应该将这个值设小一点。如果你的计算机系统有很多处理器与磁盘系统，则可以将这个值设高一点以充分利用你的系统资源。建议设值为(处理器数目+ 磁盘数目)*2。默认是建议设置为10，我设置为4，这个经过测试，对于本机同时跑20个spider进程的情况下，设置为4的性能最好。

[mysqldump]

# 不要在将内存中的整个结果写入磁盘之前缓存. 在导出非常巨大的表时需要此项

quick

max_allowed_packet = 16M

最后修改于 2012-07-01 23:45

阅读(?)评论(0)