mysql设计对性能的影响和mysql基准测试

过分的反范式化为表建立太多的列

过分的范式造成太多的表关联，关联表控制在10个以内

在OLTP环境中使用不恰当的分区表

使用外键保证数据的完整性（效率低，尽量不要使用外键约束）

 

优化顺序：

1、数据库结构设计和SQL语句：表结构优化，索引优化，sql语句的优化

2、数据库存储引擎的选择和参数配置：存储引擎的选择和内存缓存池的配置，不要混合

使用不同的存储引擎

3、系统选择及性能

4、硬件升级 

 

基准测试：1、计划和设计基准测试---对整个系统还是某个组件，使用什么样的数据

2、准备基准测试及数据收集脚本

CPU使用率、io、网络流量、状态和计数器信息

3、运行基准信息

4、保存及分析基准测试结果

 

基准测试容易忽略的问题：

1使用生产数据时只使用了部分数据

2在多用户场景下，只做单用户的测试

3在单服务器上测试分布式应用

4反复执行同一查询

 

常用mysql基准测试工具：mysqlslap

mysqlslap:特点--可以模拟服务器负载，并输出相关统计信息。可以指定也可以自动生成查询语句

参数说明：--auto-generate-sql 由系统自动生成SQL脚本进行测试

--auto-generate-sql-add-autoincrement 在生成的表中增加自增ID

--auto-generate-sql-load-type 指定测试中使用的查询类型

--auto-generate-sql-write-number指定初始化数据时生成的数据量

--concurrency指定并发线程的数量

--engine 指定测试表的存储引擎，可以用多个逗号分割多个存储引擎

--no-drop指定不清理测试数据

--iterations指定测试运行的次数

--number-of-queries指定每个线程执行的查询数量

--debug-info 指定输出额外内存及CPU统计信息

--number-int-cols指定测算表中包含的int类型列的数量

--number-char-cols指定测算表中包含的char类型列的数量

--create-schema指定用于执行测试的数据库名字

--query用于指定自定义SQL的脚本

--only-print并不运行测试脚本，而是把生成的脚本打印出来

 

mysqlslap --concurrency=1,50,100,200 --iterations=3 --number-int-cols=5 --number-char-cols=5 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=myisam,innodb --number-of-queries=10 --create-schema=sbtest

 

mysqlslap --concurrency=1,50,100,200 --iterations=3 --number-int-cols=5 --number-char-cols=5 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=myisam,innodb --number-of-queries=10 --create-schema=sbtest  --only-print ->2.sql

 

 

mysql基准测试工具之sysbench:

安装说明https://github.com/akopytov/sysbench/tree/0.5

uzip 

./autogen.sh

./configure --with-mysql-includes=/usr/local/mysql/include/ --with-mysql-libs=/usr/local/mysql/lib/

make && make install

使用sysbench --help

常用参数：多线程

--test 用于指定要执行的测试类型，支持以下参数：Fileio 文件系统I/O性能测试，CPU cpu性能测试，memory 内存性能测试，Oltp测试

要指定具体的lua脚本

Lua脚本位于sysbench -0.5/sysbench/tests/db(先test看有没有错误）

查看cpu配置cat /proc/cpuinfo

测试cpu命令：sysbench --test=cpu --cpu-max-prime=10000 run （只测试单核cpu)

测试IO命令：首先创建要测试用的文件（要大于内存大小，否则会缓存到内存中，free -m 查看内存大小，df -lh查看磁盘空间）
sysbench --test=fileio --file-total-size=3G prepare

其次进行磁盘IO测试

sysbench --test=fileio --num-threads=8 --int-rng=on --file-total-size=3G --file-test-mode=rndrw --report-interval=1 run

OLTP测试

先建库，赋予新用户权限create database imooc;grant all privileges on *.* to sbest@'localhost' identified by '123456';

sysbench --test=./oltp.lua --mysql-table-engine=innodb --oltp-table-size=10000 --mysql-db=imooc --mysql-user=sbest --mysql-password=123456 --oltp-tables-count=10 --mysql-socket=/tmp/mysql.sock prepare

上面命令会创建10个表

sysbench --test=./oltp.lua --mysql-table-engine=innodb --oltp-table-size=10000 --mysql-db=imooc --mysql-user=sbest --mysql-password=123456 --oltp-tables-count=10 --mysql-socket=/tmp/mysql.sock run(运行测试）

后台运行脚部bash ./Get_Test_info.sh &

#!/bin/bash
#定义脚部运行间隔
INTERVAL=5
#状态信息收集存储目录
PREFIX=/home/imooc/benchmarks/$INTERVAL-sec-status
#指定运行标识，如何存在标识则运行脚部在运行，如果想停止脚本，则删除标识
RUNFILE=/home/imooc/benchmarks/running
#实际生成运行标识文件
echo "1" > $RUNFILE
#指定mysql命令所在位置
MYSQL=/usr/local/mysql/bin/mysql
#记录mysql设置信息
$MYSQL -e "show global variables" >> mysql-variables
#循环（标识存在会一直循环下去）
while test -e $RUNFILE; do
#定义运行脚本文件名，脚本运行间隔，运行时间
   file=$(date +%F_%I)
   sleep=$(date +%s.%N | awk '{print 5 - ($1 % 5)}')
   sleep $sleep
   ts="$(date +"TS %s.%N %F %T")"
   #负载情况并记录到文件中
   loadavg="$(uptime)"
   echo "$ts $loadavg" >> $PREFIX-${file}-status
   #收集mysql全局状态信息
   $MYSQL -e "show global status" >> $PREFIX-${file}-status &
   echo "$ts $loadavg" >> $PREFIX-${file}-innodbstatus
   #记录存储引擎innodb状态信息
   $MYSQL -e "show engine innodb status" >> $PREFIX-${file}-innodbstatus &
   echo "$ts $loadavg" >> $PREFIX-${file}-processlist
   #mysql当前连接线程的情况
   $MYSQL -e "show full processlist\G" >> $PREFIX-${file}-processlist &
   echo $ts
done
echo Exiting because $RUNFILE does not exists

会在/home/imooc/benchmarks/生成running文件和*-status,*-innodbstatus和*-processlist四个文件

上面运行测试run结束后 运行脚部analyze.sh 可看生成文件中的具体内容，如下，看mysql全局状态信息，测试时间，测试时负载，qps

 

#!/bin/bash
awk '
   BEGIN {
     printf "#ts date time load QPS";
     fmt=" %.2f";
   }
   /^TS/ {
   ts = substr($2,1,index($2,".")-1);
   load = NF -2;
   diff = ts - prev_ts;
   printf "\n%s %s %s %s",ts,$3,$4,substr($load,1,length($load)-1);
   prev_ts=ts;
   }
   /Queries/{
   printf fmt,($2-Queries)/diff;
   Queries=$2
   }
   ' "$@"