mysql性能优化学习与实战
|
《mysql性能优化学习与实战》要点： 环境 mysql5.7+centos6+sysbench 常用命令 [root@centos1 ~]# uname -a Linux 内核名称 centos1 主机名称 2.6.32-431.el6.x86_64 内核版本号 #1 SMP Fri Nov 22 03:15:09 UTC 2013 x86_64 处理器 x86_64 硬件平台类型 x86_64 GNU/Linux 操作系统名称 [root@centos1 ~]# head -n 1 /etc/issue CentOS release 6.5 (Final) 系统版本 //我的只是一个虚拟机,有些参数可能不正常 [root@centos1 ~]# cat /proc/cpuinfo processor : 0 vendor_id : GenuineIntel cpu family : 6 model : 60 model name : Intel(R) Core(TM) i3-4160 CPU @ 3.60GHz stepping : 3 cpu MHz : 3591.769 cache size : 3072 KB fpu : yes fpu_exception : yes cpuid level : 13 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts mmx fxsr sse sse2 ss syscall nx pdpe1gb rdtscp lm constant_tsc up arch_perfmon pebs bts xtopology tsc_reliable nonstop_tsc aperfmperf unfair_spinlock pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm arat epb xsaveopt pln pts dts fsgsbase bmi1 avx2 smep bmi2 invpcid bogomips : 7183.53 clflush size : 64 cache_alignment : 64 address sizes : 42 bits physical,48 bits virtual power management: 查看系统磁盘空间 参数 -b、-k、-m、-g 分别代表以 字节、K字节、M字节、G字节为单位. [root@centos1 ~]# free -m total used free shared buffers cached Mem: 980 349 631 0 23 132 -/+ buffers/cache: 193 787 Linux下清理内存和Cache办法 /proc/sys/vm/drop_caches,默认值是0 To free pagecache: * echo 1 > /proc/sys/vm/drop_caches To free dentries and inodes: * echo 2 > /proc/sys/vm/drop_caches To free pagecache,dentries and inodes: * echo 3 > /proc/sys/vm/drop_caches Swap: 1983 0 1983 SWAP便是LINUX下的虚拟内存分区,它的作用是在物理内存使用完之后,将磁盘空间(也便是SWAP分区)虚拟成内存来使用.它和Windows系统的交换文件作用类似,但是它是一段连续的磁盘空间,并且对用户不可见. 需要注意的是,虽然这个SWAP分区能够作为"虚拟"的内存,但它的速度比物理内存可是慢多了,因此如果需要更快的速度的话,并不能寄厚望于SWAP,最好的方法仍然是加大物理内存.SWAP分区只是临时的解决方法. 交换分区(swap)的合理值一般在内存的2 倍左右,可以适当加大.实际上具体还是以实际应用为准 df -h 和df -i显示的占用率差别大 [root@centos1 ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda2 28G 4.3G 22G 17% / tmpfs 491M 0 491M 0% /dev/shm /dev/sda1 291M 34M 242M 13% /boot [root@centos1 ~]# df -i Filesystem Inodes IUsed IFree IUse% Mounted on /dev/sda2 1823248 109709 1713539 7% / tmpfs 125551 1 125550 1% /dev/shm /dev/sda1 76912 38 76874 1% /boot 原因：删除了一些文件,但是运行的程序仍然占用着过期的文件句柄,导致不一样 [root@centos1 ~]# lsof / | grep deleted mysqld 3123 mysql 4u REG 8,2 0 1446090 /tmp/ibaboxht (deleted) mysqld 3123 mysql 5u REG 8,2 0 1446092 /tmp/iba9jBR9 (deleted) mysqld 3123 mysql 6u REG 8,2 0 1446093 /tmp/ib6oOFrQ (deleted) mysqld 3123 mysql 7u REG 8,2 0 1446095 /tmp/ibbF8cDd (deleted) mysqld 3123 mysql 11u REG 8,2 0 1446096 /tmp/ibzrMOhU (deleted) 查看指定文件大小 [root@centos1 ~]# du -sh /usr/local/mysql/ 816M /usr/local/mysql/ 查看链接的用户数量 [root@centos1 ~]# uptime 05:55:49 up 3:04,3 users,load average: 0.00,0.00,0.00 [root@centos1 ~]# date -s '2016-11-09 13:56:00' Wed Nov 9 13:56:00 PST 2016 [root@centos1 ~]# clock -w 定时任务组件安装 [root@CentOS ~]# yum -y install vixie-cron [root@CentOS ~]# yum -y install crontabs 说明： vixie-cron 软件包是 cron 的主程序； crontabs 软件包是用来安装、卸装、或列举用来驱动 cron 守护进程的表格的程序. sysbench使用 sysbench是一个模块化的、跨平台、多线程基准测试工具,主要用于评估测试各种不同系统参数下的数据库负载情况.关于这个项目的详细介绍请看：http://sysbench.sourceforge.net. 它主要包含以下几种方式的测试： 1、cpu性能 2、磁盘io性能 3、调度程序性能 4、内存分配及传输速度 5、POSIX线程性能 6、数据库性能(OLTP基准测试) 安装 文件传输组件安装 sz/rz yum install lrzsz 下载sysbench yum install -y automake //下载[libtool](ftp://ftp.gnu.org/gnu/libtool/libtool-1.4.3.tar.gz) tar -zvxf libtool-1.4.3.tar.gz ./configure --prefix=/usr/local/libtool && make && make install 安装sysbench ./configure --prefix=/usr/local/sysbench --with-mysql-includes=/usr/local/mysql/include/ --with-mysql-libs=/usr/local/mysql/lib/ make && make install 使用 CPU测试 测试素数的加法,所有计算都会采用64位整数 //增加sysbench环境变量 vim /etc/profile [root@centos1 sysbench]#sysbench --test=cpu --cpu-max-prime=20000 --num-threads=4 run libmysqlclient.so.18 not found 可是我在mysql/lib下面明明看到这个文件为什么不行呢?详见压测问题动态链接库问题 [root@centos1 sysbench]#sysbench --test=cpu --cpu-max-prime=20000 --num-threads=4 run sysbench 0.4.12.10: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 4 Random number generator seed is 0 and will be ignored Doing CPU performance benchmark Primer numbers limit: 20000 Threads started! Done. General statistics: total time: 20.2200s total number of events: 10000 total time taken by event execution: 80.8197 response time: min: 1.88ms avg: 8.08ms max: 48.07ms approx. 95 percentile: 11.54ms Threads fairness: events (avg/stddev): 2500.0000/3.39 execution time (avg/stddev): 20.2049/0.01 注意: 服务器类型,有偏运算型的,有偏存储,所必要的指标不一样.偏运算的(如视频转码服务器)要求CPU强,而存储则优先选择大容量和快速存储备. 测试的数据,孤立起来看,是没有意义的.数据要有比拟才有意义,比如多台服务器的测试数据,比拟CPU性能. IO性能测试 测试文件读取速度,这个速度跟mysql数据查询直接挂钩 解释:针对1G文件,做随机读写,测试IO –file-test-mode 还可以为 seqwr：顺序写入 seqrewq：顺序重写 seqrd：顺序读取 rndrd：随机读取 rndwr：随机写入 rndrw：混合随机读写 [root@centos1 sysbench]# sysbench --test=fileio --file-total-size=1G prepare Creating file test_file.127 1073741824 bytes written in 46.61 seconds (21.97 MB/sec). //给我创建了127个8M的小文件在sysbench目录下 ls -h //顺序读 [root@centos1 sysbench]# sysbench --test=fileio --file-total-size=1G --file-test-mode=seqrd run sysbench 0.4.12.10: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 1 Random number generator seed is 0 and will be ignored Extra file open flags: 0 128 files,8Mb each 1Gb total file size Block size 16Kb Periodic FSYNC enabled,calling fsync() each 100 requests. Calling fsync() at the end of test,Enabled. Using synchronous I/O mode Doing sequential read test Threads started! Done. Operations performed: 65536 reads,0 writes,0 Other = 65536 Total Read 1Gb Written 0b Total transferred 1Gb (261.94Mb/sec) 16764.17 Requests/sec executed General statistics: total time: 3.9093s total number of events: 65536 total time taken by event execution: 3.8426 response time: min: 0.00ms avg: 0.06ms max: 35.02ms approx. 95 percentile: 0.46ms Threads fairness: events (avg/stddev): 65536.0000/0.00 execution time (avg/stddev): 3.8426/0.00 //随机读 [root@centos1 sysbench]# sysbench --test=fileio --file-total-size=1G --file-test-mode=rndrd run sysbench 0.4.12.10: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 1 Random number generator seed is 0 and will be ignored Extra file open flags: 0 128 files,8Mb each 1Gb total file size Block size 16Kb Number of random requests for random IO: 10000 Read/Write ratio for combined random IO test: 1.50 Periodic FSYNC enabled,Enabled. Using synchronous I/O mode Doing random read test Threads started! Done. Operations performed: 10000 reads,0 Other = 10000 Total Read 156.25Mb Written 0b Total transferred 156.25Mb (8.1738Mb/sec) 523.13 Requests/sec executed General statistics: total time: 19.1159s total number of events: 10000 total time taken by event execution: 19.0684 response time: min: 0.00ms avg: 1.91ms max: 57.75ms approx. 95 percentile: 9.42ms Threads fairness: events (avg/stddev): 10000.0000/0.00 execution time (avg/stddev): 19.0684/0.00 顺序读取262M/S随机读取8M/S,相差32倍!!mysql中合理的索引(后面文章将会跟上解释)加上where语句会尽量达成顺序读. mysql事务测试 mysql配置文件 [root@centos1 sysbench]# grep -v "^#" /etc/my.cnf | grep -v "^$" [client] port=3306 [mysql] default-character-set=utf8 [mysqld] server-id=2 port=3306 basedir=/usr/local/mysql datadir=/usr/local/mysql/data character-set-server=utf8 default-storage-engine=INNODB sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" max_connections=100 query_cache_size=0 table_cache=256 tmp_table_size=34M thread_cache_size=8 myisam_max_sort_file_size=100G myisam_sort_buffer_size=67M key_buffer_size=54M read_buffer_size=64K read_rnd_buffer_size=256K sort_buffer_size=256K innodb_additional_mem_pool_size=3M innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=2M innodb_buffer_pool_size=105M innodb_log_file_size=53M innodb_thread_concurrency=10 准备数据,创建测试库test //sbtest表会自动创建,我准备了500W的测试数据,1G左右数据 [root@centos1 sysbench]# sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --mysql-password=123456 --db-driver=mysql --mysql-db=test --oltp-table-size=5000000 prepare sysbench 0.4.12.10: multi-threaded system evaluation benchmark FATAL: unable to connect to MySQL server,aborting... FATAL: error 1045: Access denied for user 'root'@'localhost' (using password: YES) FATAL: failed to connect to database server! FATAL: Failed to create test tables [root@centos1 sysbench]# sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --mysql-password=123456 --db-driver=mysql --mysql-db=test --oltp-table-size=5000000 prepare sysbench 0.4.12.10: multi-threaded system evaluation benchmark Creating table 'sbtest'... Creating 5000000 records in table 'sbtest'... 测试mysql事务性能 [root@centos1 sysbench]# sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --mysql-password=123456 --db-driver=mysql --mysql-db=test --oltp-table-size=5000000 run sysbench 0.4.12.10: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 1 Random number generator seed is 0 and will be ignored Doing OLTP test. Running mixed OLTP test Using Special distribution (12 iterations,1 pct of values are returned in 75 pct cases) Using "BEGIN" for starting transactions Using auto_inc on the id column Maximum number of requests for OLTP test is limited to 10000 Using 1 test tables Threads started! Done. OLTP test statistics: queries performed: read: 140000 write: 50000 other: 20000 total: 210000 transactions: 10000 (36.33 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 190000 (690.24 per sec.) other operations: 20000 (72.66 per sec.) General statistics: total time: 275.2683s total number of events: 10000 total time taken by event execution: 275.0810 response time: min: 1.90ms avg: 27.51ms max: 581.77ms approx. 95 percentile: 77.63ms Threads fairness: events (avg/stddev): 10000.0000/0.00 execution time (avg/stddev): 275.0810/0.00 第二波 –num-threads=1 表示发起 1个并发连接 –oltp-read-only=off 表示不要进行只读测试,也便是会采用读写混合模式测试 –report-interval=10 表示每10秒输出一次测试进度申报 –percentile=99 表示设定采样比例,默认是 95%,即丢弃1%的长哀求,在剩余的99%里取最大值 真实测试场景中,建议持续压测时长不小于1个小时,根据线上环境而论,否则测试数据可能不具参考意义. [root@centos1 sysbench]# sysbench --test=oltp --mysql-table-engine=innodb --mysql-user=root --mysql-password=123456 --db-driver=mysql --mysql-db=test --oltp-table-size=5000000 --num-threads=1 --oltp-read-only=off --report-interval=10 --percentile=99 run sysbench 0.4.12.10: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 1 Report intermediate results every 10 second(s) Random number generator seed is 0 and will be ignored Doing OLTP test. Running mixed OLTP test Using Special distribution (12 iterations,1 pct of values are returned in 75 pct cases) Using "BEGIN" for starting transactions Using auto_inc on the id column Maximum number of requests for OLTP test is limited to 10000 Using 1 test tables Threads started! --每10秒钟申报一次测试结果,tps、每秒读、每秒写、99%以上的响应时长统计 [ 10s] Intermediate results: 1 threads,tps: 245.887991,reads/s: 3442.431872,writes/s: 1229.439954 response time: 27.147781ms (99%) [ 20s] Intermediate results: 1 threads,tps: 188.598939,reads/s: 2640.385141,writes/s: 942.994693 response time: 63.163764ms (99%) [ 30s] Intermediate results: 1 threads,tps: 195.203516,reads/s: 2732.849229,writes/s: 976.017582 response time: 58.050724ms (99%) [ 40s] Intermediate results: 1 threads,tps: 174.599703,reads/s: 2444.395837,writes/s: 872.998513 response time: 74.445546ms (99%) Done. OLTP test statistics: queries performed: read: 140000 write: 50000 other: 20000 --其他操作总数(SELECT、INSERT、UPDATE、DELETE之外的操作,例如COMMIT等) total: 210000 transactions: 10000 (201.29 per sec.) -- 总事务数(每秒事务数) deadlocks: 0 (0.00 per sec.)--死锁数 read/write requests: 190000 (3824.60 per sec.)-- 读写总数(每秒读写次数) other operations: 20000 (402.59 per sec.) General statistics: total time: 49.6784s total number of events: 10000 -- 共发生多少事务数 total time taken by event execution: 49.5798 -- 所有事务耗时相加(不考虑并行因素) response time: --响应统计 min: 1.94ms avg: 4.96ms max: 698.99ms approx. 99 percentile: 47.36ms Threads fairness: events (avg/stddev): 10000.0000/0.00 execution time (avg/stddev): 49.5798/0.00
编程之家PHP培训学院每天发布《mysql性能优化学习与实战》等实战技能，PHP、MYSQL、LINUX、APP、JS,CSS全面培养人才。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |