mysql性能测试

最新推荐文章于 2023-03-20 09:00:00 发布

ybn187

最新推荐文章于 2023-03-20 09:00:00 发布

阅读量746

点赞数

分类专栏： sql c++

本文链接：https://blog.csdn.net/ybn187/article/details/105386352

版权

c++ 同时被 2 个专栏收录

27 篇文章 1 订阅

订阅专栏

sql

4 篇文章 0 订阅

订阅专栏

mysql性能测试

版本5.6.5

默认参数

//0：事务提交时，不做日志写入操作，而是每秒钟将log buffer中的日志写入文件并且flush磁盘一次。
//1：每秒钟或者事务提交时，都会引起日志写入和flush磁盘操作，这样设计也是最安全的。
//2：每次事务提交时，进行日志写入，但此时没有flush磁盘操作，而是在每秒钟时进行flush磁盘操作。
//值为0时性能最好，但是会丢失数据，断电或者mysql崩溃，值为2时性能次之，mysql崩溃不会丢失数据，断电会
//值为2时安全性数据完整性最好，不会丢失数据，性能最差
//默认为1
innodb_flush_log_at_trx_commit		1

//innodb引擎缓存索引和行数据的大小，越大越能减少磁盘io和表的读取，但不能超过物理内存的80%，
//32位系统上单进程不能设置超过2-3.5G
//默认23M
innodb_buffer_pool_size				23068672

//InnoDB存储引擎用来存放数据字典信息以及一些内部数据结构的内存空间大小
//当数据库对象非常多的时候，需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。
//MySQL 会记录 Warning 信息到数据库的 error log 中，这时候你就知道该调整这个参数大小了
//show variables like 'log%' 查看日志位置
//默认2M
innodb_additional_mem_pool_size		2097152

//logfile就是事务日志，用来在mysql crash后的恢复
//默认为10M,越小恢复时越快，写数据库越频繁，越大写的频率越低，恢复时越慢
innodb_log_file_size				10485760

//确保有足够大的日志缓冲区来保存脏数据在被写入到日志文件之前, 1M
innodb_log_buffer_size				1048576

//控制着innodb数据文件及redo log的打开、刷写模式，有三个值：fdatasync(默认)，O_DSYNC，O_DIRECT
//默认是fdatasync，调用fsync()去刷数据文件与redo log的buffer
//为O_DSYNC时，innodb会使用O_SYNC方式打开和刷写redo log,使用fsync()刷写数据文件
//为O_DIRECT时，innodb使用O_DIRECT打开数据文件，使用fsync()刷写数据文件跟redo log
innodb_flush_method	

//线程池的线程数量
thread_cache						17

//0, MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。性能最好，风险最大。
//一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失
#sync_binlog>0，表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去
//等于1，表示每次事务提交时都会调用文件系统的刷新，最安全，最耗时
sync_binlog

测试代码

// mysqlTest.cpp : Defines the entry point for the console application.
//

#include "stdafx.h"

#include <windows.h>
#include <cstdlib>
#include <iostream>
#include <string>

#include "Poco/Data/SessionPool.h"
#include "Poco/DateTimeFormatter.h"
#include "Poco/Data/MySQL/Connector.h"

#if defined(_MSC_VER) && (_MSC_VER >= 1500)
#include <memory>
namespace std
{
	using std::tr1::shared_ptr;
	using std::tr1::enable_shared_from_this;
}
#define FOUND_SHARED_PTR

#endif //_MSC_VER

typedef std::shared_ptr< Poco::Data::SessionPool >     SessionPoolPtr;

SessionPoolPtr  m_sessionPool;

bool checkDBOpen()
{
	Poco::Timestamp ts;

	try
	{
		Poco::Data::Session session = m_sessionPool->get();
		return session.isConnected();
	}
	catch (Poco::IOException& ex)
	{
		std::cout<<"can not open db. ex: "<<ex.message().c_str()<<std::endl;
		return false;
	}

	return true;
}

bool openDB()
{
	Poco::Data::MySQL::Connector::registerConnector();

	std::string dbIP = "127.0.0.1";
	Poco::UInt16 dbPort = 3306;
	std::string user = "root";
	std::string passwd = "6000";
	std::string name = "nvs4002";

	std::string autoStr;
	char buffer[256] = {0};
	sprintf(buffer,
		"host=%s;port=%d;user=%s;password=%s;db=%s;compress=true;auto-reconnect=true;character-set=gbk",
		dbIP.c_str(), 
		dbPort,
		user.c_str(), 
		passwd.c_str(),
		name.c_str());
	std::string strConnection(buffer);


	m_sessionPool.reset(new Poco::Data::SessionPool(
		Poco::Data::MySQL::Connector::KEY, 
		strConnection));

	int tryCount = 10;
	bool done = false;
	for (int i = 0; i < tryCount; i ++)
	{
		if (checkDBOpen())
		{
			done = true;
			break;
		}

		Poco::Thread::sleep(1000);
	}

	return done;
}

void closeDB()
{
	m_sessionPool.reset();
	Poco::Data::MySQL::Connector::unregisterConnector();
}

int _tmain(int argc, _TCHAR* argv[])
{
	std::cout<<"open db"<<std::endl;
	if (!openDB())
	{
		return 0;
	}
	Poco::Data::Session session = m_sessionPool->get();
	int i = 0;
	std::cout<<"open db succes"<<std::endl;
	DWORD begin = GetTickCount();
	for (; i < 10000; i++)
	{
		std::string sql ="insert into nvs_alarm_info(id,no,lno,type,starttime,param,status,description,recvtime,savetime,mark) values('111', '125', '12','10', '2020-04-07 10:00:00','2','fail','hdd','','2020-04-07 10:01:00', '123')";
		try
		{
			Poco::Data::Statement statement(session);
			statement << sql;

			statement.setAsync(false);
			while (!statement.done())
			{
				statement.execute();
			}
		}
		catch (Poco::Exception& ex)
		{
			std::cout<<"DataSession.execute exception. sql:"<<sql<<std::endl;
			std::cout<<ex.code()<<":"<<ex.displayText().c_str()<<std::endl;

			if (ex.code() != 2013)
			{
				break;
			}
		}

		if (i % 1000 == 0)
		{
			unsigned long time = GetTickCount() - begin;
			std::cout<<"insert count:"<<i<<",time:"<<time<<std::endl;
		}
	}

	unsigned long time = GetTickCount() - begin;
	std::cout<<"time:"<<time<<", total count:"<<i<<std::endl;
	closeDB();
	return 0;
}

默认情况测试

insert count:0,time:109
insert count:1000,time:32641
insert count:2000,time:64875
insert count:3000,time:93484
insert count:4000,time:122000
insert count:5000,time:150469
insert count:6000,time:195063
insert count:7000,time:240234
insert count:8000,time:280266
insert count:9000,time:308672
time:340656, total count:10000

每千条数据插入大概为30s左右

优化测试

首先测试在其他参数不变的情况下，修改innodb_flush_log_at_trx_commit的值，测试性能
然后保存innodb_flush_log_at_trx_commit的值，提高缓存，测试性能

innodb_flush_log_at_trx_commit的值改为2

修改完成，清空表数据，重启MySQL

insert count:0,time:94
insert count:1000,time:828
insert count:2000,time:1172
insert count:3000,time:1688
insert count:4000,time:1969
insert count:5000,time:2235
insert count:6000,time:2750
insert count:7000,time:3016
insert count:8000,time:3547
insert count:9000,time:3813
time:4172, total count:10000

平均千条数据大概为420ms左右

innodb_flush_log_at_trx_commit的值改为0

修改完成，清空表数据，重启MySQL

insert count:0,time:47
insert count:1000,time:297
insert count:2000,time:469
insert count:3000,time:640
insert count:4000,time:937
insert count:5000,time:1094
insert count:6000,time:1265
insert count:7000,time:1437
insert count:8000,time:1594
insert count:9000,time:2062
time:2219, total count:10000

平均千条数据大概为220ms左右

加大缓存
修改参数如下：

name	value	description
innodb_flush_log_at_trx_commit	2
innodb_buffer_pool_size	230686720	大约230M
innodb_log_buffer_size	209715200	大约200M

insert count:0,time:31
insert count:1000,time:328
insert count:2000,time:672
insert count:3000,time:875
insert count:4000,time:1047
insert count:5000,time:1234
insert count:6000,time:1468
insert count:7000,time:1734
insert count:8000,time:1922
insert count:9000,time:2093
time:2281, total count:10000

平均千条数据大概为220ms左右

加大innodb_buffer_pool_size

修改参数如下：

name	value	description
innodb_flush_log_at_trx_commit	2
innodb_buffer_pool_size	440M	大约440M
innodb_log_buffer_size	209715200	大约200M

测试结果如下：

insert count:0,time:15
insert count:1000,time:250
insert count:2000,time:437
insert count:3000,time:640
insert count:4000,time:921
insert count:5000,time:1234
insert count:6000,time:1437
insert count:7000,time:1609
insert count:8000,time:1796
insert count:9000,time:1968
time:2250, total count:10000

相比之前，性能提高有限

继续

innodb_flush_log_at_trx_commit为1时加大内存

测试1

name	value	description
innodb_flush_log_at_trx_commit	2
innodb_buffer_pool_size	880M	880M
innodb_log_buffer_size	400M	400M

测试结果：

insert count:0,time:31
insert count:1000,time:344
insert count:2000,time:531
insert count:3000,time:703
insert count:4000,time:937
insert count:5000,time:1109
insert count:6000,time:1375
insert count:7000,time:1547
insert count:8000,time:1719
insert count:9000,time:1891
time:2062, total count:10000

千条插入大约200ms

测试2

name	value	description
innodb_flush_log_at_trx_commit	2
innodb_buffer_pool_size	880M	880M
innodb_log_buffer_size	400M	400M
innodb_log_file_size	100M

insert count:0,time:47
insert count:1000,time:312
insert count:2000,time:515
insert count:3000,time:703
insert count:4000,time:875
insert count:5000,time:1047
insert count:6000,time:1219
insert count:7000,time:1453
insert count:8000,time:1625
insert count:9000,time:1797
time:1969, total count:10000

千条插入大约196ms,相比之前的innodb_log_file_size的10M,性能提升不明显

测试3

name	value	description
innodb_flush_log_at_trx_commit	2
innodb_buffer_pool_size	880M
innodb_log_buffer_size	100M
innodb_log_file_size	10M

insert count:0,time:0
insert count:1000,time:219
insert count:2000,time:406
insert count:3000,time:609
insert count:4000,time:922
insert count:5000,time:1109
insert count:6000,time:1297
insert count:7000,time:1469
insert count:8000,time:1703
insert count:9000,time:1984
time:2187, total count:10000

千条插入大约218ms,相比测试1的innodb_log_buffer_size的400M,性能降低不明显

测试4

name	value	description
innodb_flush_log_at_trx_commit	2
innodb_buffer_pool_size	880M
innodb_log_buffer_size	10M
innodb_log_file_size	10M

insert count:0,time:94
insert count:1000,time:359
insert count:2000,time:531
insert count:3000,time:734
insert count:4000,time:906
insert count:5000,time:1078
insert count:6000,time:1328
insert count:7000,time:1516
insert count:8000,time:1672
insert count:9000,time:1844
time:2016, total count:10000

千条插入大约200ms, 相比测试3，差别不大

测试5
|name|value|description|
|—|---|—|
|innodb_flush_log_at_trx_commit|0||
|innodb_buffer_pool_size|880M||
|innodb_log_buffer_size|10M||
|innodb_log_file_size|10M||

insert count:1000,time:187
insert count:2000,time:359
insert count:3000,time:516
insert count:4000,time:687
insert count:5000,time:844
insert count:6000,time:1016
insert count:7000,time:1281
insert count:8000,time:1437
insert count:9000,time:1609
time:1766, total count:10000

千条插入大概为176ms

相比innodb_flush_log_at_trx_commit为0和其他参数默认的情况下，性能提升约20%

测试6

name	value	description
innodb_flush_log_at_trx_commit	2
innodb_buffer_pool_size	880M
innodb_log_buffer_size	100M
innodb_log_file_size	10M
innodb_additional_mem_pool_size	20M

insert count:0,time:47
insert count:1000,time:250
insert count:2000,time:437
insert count:3000,time:609
insert count:4000,time:812
insert count:5000,time:984
insert count:6000,time:1281
insert count:7000,time:1453
insert count:8000,time:1625
insert count:9000,time:1797
time:1969, total count:10000

innodb_additional_mem_pool_size由 2M 改为 20M，性能提升不明显

测试7

name	value	description
innodb_flush_log_at_trx_commit	2
innodb_buffer_pool_size	200M
innodb_log_buffer_size	10M
innodb_log_file_size	10M
innodb_additional_mem_pool_size	2M

insert count:0,time:0
insert count:1000,time:250
insert count:2000,time:421
insert count:3000,time:609
insert count:4000,time:828
insert count:5000,time:1000
insert count:6000,time:1171
insert count:7000,time:1343
insert count:8000,time:1515
insert count:9000,time:1687
time:1968, total count:10000

测试8

name	value	description
innodb_flush_log_at_trx_commit	2
innodb_buffer_pool_size	23M
innodb_log_buffer_size	10M
innodb_log_file_size	10M
innodb_additional_mem_pool_size	2M

insert count:0,time:0
insert count:1000,time:203
insert count:2000,time:640
insert count:3000,time:890
insert count:4000,time:1125
insert count:5000,time:1281
insert count:6000,time:1703
insert count:7000,time:1890
insert count:8000,time:2093
insert count:9000,time:2297
time:2515, total count:10000

测试9

name	value	description
innodb_flush_log_at_trx_commit	1
innodb_buffer_pool_size	800M
innodb_log_buffer_size	100M
innodb_log_file_size	100M
innodb_additional_mem_pool_size	2M

insert count:0,time:187
insert count:1000,time:44015
insert count:2000,time:83609
insert count:3000,time:121859
insert count:4000,time:158437
insert count:5000,time:186297
insert count:6000,time:214422
insert count:7000,time:240672
insert count:8000,time:270890
insert count:9000,time:304750
time:339859, total count:10000

相比innodb_flush_log_at_trx_commit值为0或2时，提高缓存带来性能的提升并不明显

综合考虑

name	value	description
innodb_flush_log_at_trx_commit	2	提高性能的情况下,尽量减少数据丢失
innodb_buffer_pool_size	200M	默认23M,在可能的情况下,尽量达到100M往上
innodb_log_buffer_size	100M	默认1M,尽可能达到10M往上，降低IO频率
innodb_log_file_size	50M	默认10M, 基本满足需求

参考

https://blog.csdn.net/cke63021/article/details/100222034?depth_1-utm_source=distribute.pc_relevant.none-task-blog-OPENSEARCH-2&utm_source=distribute.pc_relevant.none-task-blog-OPENSEARCH-2
https://blog.csdn.net/qing_gee/article/details/42742241
https://blog.csdn.net/vivenwan/article/details/53257114
https://www.cnblogs.com/lzy1991/p/4778786.html

ybn187

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
mysql性能测试

mysql性能测试版本5.6.32默认参数//0：事务提交时，不做日志写入操作，而是每秒钟将log buffer中的日志写入文件并且flush磁盘一次。//1：每秒钟或者事务提交时，都会引起日志写入和flush磁盘操作，这样设计也是最安全的。//2：每次事务提交时，进行日志写入，但此时没有flush磁盘操作，而是在每秒钟时进行flush磁盘操作。//值为0时性能最好，但是会丢失数据...
复制链接

扫一扫