mysql 高性能压力测试_mysql 高性能压力测试（总结了好久）

前言：根据业务需要，需测试mysql性能和压力测试.这里我说下测试方法和测试过程以及测试中出错的问题估计很多人跟我一样要测试mysql的性能压力，网上找了很多资

前言：根据业务需要，需测试mysql 性能和压力测试.

这里我说下测试方法和 测试过程以及测试中出错的问题

估计很多人 跟我一样 要测试mysql 的性能 压力，网上找了很多资料有很多，但是很乱，所以这里我做个总结，拿自己的真实服务器测试的。并且 想测多少数据 都能模拟出来！这里只介绍方法。

MySQL从5.1.4版开始带有一个压力测试工具mysqlslap，通过模拟多个并发客户端访问

mysql来执行测试。[root@localhost smacks]# mysql -V

mysql Ver 14.14 Distrib 5.1.69, for redhat-linux-gnu (x86_64) using readline 5.1

mysqlslap --delimiter=";" --create="CREATE TABLE a (b int);INSERT INTO a VALUES (23)" --query="SELECT * FROM a" --concurrency=50 --iterations=200 -uadmin -p123456–auto-generate-sql, -a

自动生成测试表和数据

–auto-generate-sql-load-type=type

测试语句的类型。取值包括：read，key，write，update和mixed(默认)。

–number-char-cols=N, -x N

自动生成的测试表中包含多少个字符类型的列，默认1

–number-int-cols=N, -y N

自动生成的测试表中包含多少个数字类型的列，默认1

–number-of-queries=N

总的测试查询次数(并发客户数×每客户查询次数)

–query=name,-q

使用自定义脚本执行测试，例如可以调用自定义的一个存储过程或者sql语句来执行测试。

–create-schema

测试的schema，MySQL中schema也就是database

–commint=N

多少条DML后提交一次

–compress, -C

如果服务器和客户端支持都压缩，则压缩信息传递

–concurrency=N, -c N

并发量，也就是模拟多少个客户端同时执行select。可指定多个值，以逗号或者–delimiter参数指定的值做为分隔符

–engine=engine_name, -e engine_name

创建测试表所使用的存储引擎，可指定多个

–iterations=N, -i N

测试执行的迭代次数

–detach=N

执行N条语句后断开重连

–debug-info, -T

打印内存和CPU的信息

–only-print

只打印测试语句而不实际执行

mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info -uadmin -p123456

分别 测试 myisam 和 innodb 引擎Benchmark

Running for engine myisam

Average number of seconds to run all queries: 0.638 seconds

Minimum number of seconds to run all queries: 0.565 seconds

Maximum number of seconds to run all queries: 0.924 seconds

Number of clients running queries: 50

Average number of queries per client: 20

Benchmark

Running for engine myisam

Average number of seconds to run all queries: 0.691 seconds

Minimum number of seconds to run all queries: 0.686 seconds

Maximum number of seconds to run all queries: 0.696 seconds

Number of clients running queries: 100

Average number of queries per client: 10

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 13.189 seconds

Minimum number of seconds to run all queries: 12.886 seconds

Maximum number of seconds to run all queries: 13.337 seconds

Number of clients running queries: 50

Average number of queries per client: 20

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 12.449 seconds

Minimum number of seconds to run all queries: 12.299 seconds

Maximum number of seconds to run all queries: 12.592 seconds

Number of clients running queries: 100

Average number of queries per client: 10

这个工具 无法 自己规定 多少数据，所以我后来 有拿了 super smack 工具测试

要去网上下载 vegan.net/tony/supersmack/super-smack-1.3.tar.gz

tar zxvf super-smack-1.3.tar.gz

cd super-smack-1.3

./configure --prefix=/usr/local/super-smack-1.3 --with-mysql=/usr/local/mysql/ --with-mysql-lib=/usr/local/mysql/lib/mysql/ --with-mysql-include=/usr/local/mysql/include/mysql/

编译过程后 make 会报错重点说明一下--with-mysql的编译配置：

编译的时候要把/usr/lib64目录下的libmysqlclient.so.16放到/usr/lib目录下，否则会出现下面的错误：

configure: error: Could not find libmysqlclient in ' /usr/lib /usr/lib/mysql

/usr/local/lib /usr/local/lib/mysql

/usr/local/mysql/lib

configure: error: Could not find mysql.h in ' /usr/include /usr/include/mysql

/usr/local/include /usr/local/include/mysql

/usr/local/mysql/include'

由于我的mysql是自行编译的，所以我这样操作

cd /usr/include; ln -s /usr/local/mysql/include/mysql

cd /usr/lib; ln -s /usr/local/mysql/lib/mysql

而且还要修改配置文件dictionary.h: In member function ‘void Unique_dictionary::set_template(const char*)’:

dictionary.h:93:28: error: ‘strlen’ was not declared in this scope

super-smack.cc: In function ‘void set_parse_file(char*)’:

super-smack.cc:65:55: warning: deprecated conversion from string constant to ‘char*’

super-smack.cc: In function ‘void set_db_type(const char*)’:

super-smack.cc:72:48: warning: deprecated conversion from string constant to ‘char*’

super-smack.cc: In function ‘void print_engines()’:

super-smack.cc:126:55: error: ‘strlen’ was not declared in this scope

make[2]: *** [super-smack.o] 错误 1

make[1]: *** [all-recursive] 错误 1

make: *** [all-recursive-am] 错误 2

要修改相应的配置文件才行编译之前修改文件super-smack-1.3/src/query.cc

第193行：

< int len = 0, num_recs = 0;

修改成：

> long len = 0; int num_recs = 0;

第199,200行

< int str_len = (*i).first.length();

< if((unsigned)p + str_len + 3 *sizeof(int) < (unsigned)p_end )

修改成：

> long str_len = (*i).first.length();

> if((long)p + str_len + 3 *sizeof(int) < (long)p_end )

第219行

< len = (unsigned)p - (unsigned)buf;

修改成：

> len = (long)p - (long)buf;

这个我是看了相应教程 找了好久才发现的！make

make install

介绍相应参数：

开头要修改 密码默认文件

vi select-key.smack