过分的反范式化为表建立太多的列
过分的范式造成太多的表关联,关联表控制在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 } ' "$@"