Mysql+压力测试报告

Mysql参数调试报告一、测试服务器centos6.5mysql5.6.28阿里云（IO优化型）CPU：Intel(R)Xeon(R)CPUE5-2650v2@2.60GHz2CPU内存：4G硬盘：20GSSD对磁盘IO性能的测试--生产16个文件总大小为2G/usr/local/sysbench-0.4.12/bin/sysbench--test=fileio--file-num=16--file-total-size=2Gprepare--接下来开始对这些文件进行测试，使用16个线程随机读进行测试结果如下/usr/local/sysbench-0.4.12/bin/sysbench--test=fileio--file-total-size=2G--file-test-mode=rndrd--max-time=180--max-requests=100000000--num-threads=16--init-rng=on--file-num=16--file-extra-flags=direct--file-fsync-freq=0--file-block-size=16384runOperationsperformed:107964reads,0writes,0Other=107964TotalRead1.6474GbWritten0bTotaltransferred1.6474Gb(9.3705Mb/sec)599.71Requests/secexecuted一般的统计数据totaltimetotalnumberofeventstotaltimetakenbyeventexecution180.0260s1079642879.9951s响应时间minavgmaxapprox.99percentile0.80ms26.68ms62.83ms28.43ms线程公平events(avg/stddev):6747.7500/5.49executiontime(avg/stddev):179.9997/0.01可以看到随机读取的性能为9.3705Mb/sec，随机读的IOPS为599.71Requests/sec初始化参数：[client]user=rootpassword=123456[mysqld]########基础配置########server-id=11port=3306user=mysqlskip_name_resolve=1max_connections=800max_connect_errors=1000datadir=/mydata/mydata56transaction_isolation=REPEATABLE-READexplicit_defaults_for_timestamp=1join_buffer_size=2Mtmp_table_size=64Mtmpdir=/tmpmax_allowed_packet=16Msql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USERinteractive_timeout=1800wait_timeout=1800read_buffer_size=8Mread_rnd_buffer_size=8Msort_buffer_size=8M########log相关配置########log_error=error.logslow_query_log=1slow_query_log_file=slow.loglog_queries_not_using_indexes=1log_slow_admin_statements=1log_slow_slave_statements=1log_throttle_queries_not_using_indexes=10expire_logs_days=90long_query_time=2min_examined_row_limit=100########同步配置########master_info_repository=TABLErelay_log_info_repository=TABLElog_bin=bin.logsync_binlog=0gtid_mode=onenforce_gtid_consistency=1log_slave_updatesbinlog_format=rowrelay_log=relay.logrelay_log_recovery=1binlog_gtid_simple_recovery=1slave_skip_errors=ddl_exist_errors########innodb配置########innodb_page_size=8192innodb_buffer_pool_size=6Ginnodb_buffer_pool_instances=8innodb_buffer_pool_load_at_startup=1innodb_buffer_pool_dump_at_shutdown=1innodb_lru_scan_depth=2000innodb_lock_wait_timeout=5innodb_io_capacity=4000innodb_io_capacity_max=8000innodb_flush_method=O_DIRECTinnodb_file_format=Barracudainnodb_file_format_max=Barracudainnodb_log_group_home_dir=/redolog/innodb_undo_directory=/undolog/innodb_undo_logs=128innodb_undo_tablespaces=3innodb_flush_neighbors=1innodb_log_file_size=2Ginnodb_log_buffer_size=128Minnodb_purge_threads=4innodb_large_prefix=1innodb_thread_concurrency=64innodb_print_all_deadlocks=1innodb_strict_mode=1innodb_sort_buffer_size=64M########semisyncreplicationsettings########plugin_dir=/usr/local/mysql/lib/pluginplugin_load=rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.soloose_rpl_semi_sync_master_enabled=1loose_rpl_semi_sync_slave_enabled=1loose_rpl_semi_sync_master_timeout=5000二、测试计划：1、测试innodb_buffer_pool_size分别为1G（25%）、2G（50%）、3G（75%）时的性能。2、测试innodb_log_file_size分别为2G、4G时的性能，(两个组相加)。3、测试innodb_log_buffer_size分别为16M、64M、128M时的性能。4、测试innodb_max_dirty_pages_pct分别为50、75、90时的性能。5、测试innodb_additional_mem_pool_size分别为32M、64M、128M时的性能。6、测试innodb_thread_concurrency分别为0、4、8时的性能。7、测试innodb_buffer_pool_instances分别为1、4、8时的性能。三、测试工具：Percona的TPCC-MySQL测试工具每次测试完成:1、重启mysql2、执行四、配置文件关键参数：key_buffer_size=16Mmax_allowed_packet=16Mtable_open_cache=512sort_buffer_size=2Mread_buffer_size=2Mread_rnd_buffer_size=8Mmyisam_sort_buffer_size=32Mthread_cache_size=8query_cache_size=32Minnodb_thread_concurrency=16innodb_buffer_pool_size=valueinnodb_additional_mem_pool_size=valueinnodb_log_file_size=valueinnodb_log_buffer_size=valueinnodb_flush_log_at_trx_commit=2innodb_lock_wait_timeout=50innodb_max_dirty_pages_pct=valueinnodb_file_per_tableinnodb_thread_concurrency=valueinnodb_buffer_pool_instances=value五、测试：整个测试过程，采用的预热时间、测试时间、warehouse数目和测试数据集（除innodb_log_file_size测试外）都是一致的。预热时间为：300；测试时间：600；warehouse：50；连接数100。其中数据总量约4.2G1、innodb_buffer_pool_size测试参数设置其他参数的设置为：innodb_additional_mem_pool_size=32Minnodb_log_file_size=2Ginnodb_log_buffer_size=256Minnodb_max_dirty_pages_pct=75innodb_thread_concurrency=64innodb_buffer_pool_instances=3测试结果及分析测试innodb_buffer_pool_size分别为1G、3G、4G、6G下的性能。innodb_buffer_pool_sizeTPMC和总内存占比1G3564.900TpmC1/83G9675.600TpmC3/84G10555.200TpmC4/86G11163.300TpmC6/8测试结论：innodb_buffer_pool_size在6G时，此时性能最优。原因是内存大于实际数据量。查看innodb_.txt可以看到innodb_buffer的使用状况。推荐innodb_buffer_pool_size大为总内存的75%。过大会妨碍到mysql其他参数对内存的使用，当mysql使用过大的内存后系统会使用swap。这里为了更合理测试其他参数，将innodb_buffer_pool_size配置为4G预热时间为：300；测试时间：1800；warehouse：50;连接数：64。其中数据总量约4.2G（不断增长）2、innodb_log_buffer_size测试参数设置其他参数的设置为：innodb_additional_mem_pool_size=32Minnodb_log_file_size=2Ginnodb_buffer_pool_size=4Ginnodb_max_dirty_pages_pct=75innodb_thread_concurrency=16innodb_buffer_pool_instances=3测试结果及分析测试innodb_log_buffer_size分别为8M、64M、256M下的性能。innodb_log_buffer_sizeTPMC8M9541.667TpmC64M1022