MySQL Small white series _12 Sysbench

Pressure measurement 1. View Machine Load

• Top-load average 1m 5m 15m
• Cat/proc/loadavg
• is related to the number of CPUs, 1 load represents 1 CPUscat /proc/cpuinfo |grep processor

2.CPU usage Rate

• Top US sy ID wa hi si St
• Cat/proc/stat

3.QPS

• SSD query for QPS about 2w
• Evaluation qps= (Total PV * 80%)/(24 * 60 * 60 * 20%)
• Number of machines = Total qps/single machine limit QPS

Sysbench

• 1. Install

    wget https://github.com/akopytov/sysbench/archive/1.0.zip-O "Sysbench-1.0.zip" Unzip sysbench-1.0.zipcd Sysbench-1.0yum install Automake libtool-yexport ld_library_path=/usr/local/mysql/lib./ Autogen.sh./configure--with-mysql-includes=/usr/local/mysql/include/--with-mysql-libs=/usr/local/mysql/lib/ Create && make install  

• 2. Creating a Test table

    sysbench--test=/usr/local/share/ Sysbench/tests/include/oltp_legacy/select.lua--oltp-table-size=20000--mysql-table-engine=innodb--mysql-user= Root--mysql-password=123456--mysql-port=3306--mysql-host=localhost--mysql-db=test--max-requests=0--max-time=60 --oltp-tables-count=20--report-interval=10--num_threads=2 Prepare  

sysbench --test=oltp --oltp_tables_count=10 --oltp-table-size=100000 --mysql-user=root --mysql-password=123456 --num-threads=20 --max-time=120 --max-requests=0 --oltp-test-mode=complex prepare#-test=tests/db/oltp.lua 表示调用 tests/db/oltp.lua 脚本进行 oltp 模式测试#--oltp_tables_count=10 表示会生成 10 个测试表#--oltp-table-size=100000 表示每个测试表填充数据量为 100000 #--rand-init=on 表示每个测试表都是用随机数据来填充的#-num-threads=8 表示发起 8个并发连接#--oltp-read-only=off 表示不要进行只读测试，也就是会采用读写混合模式测试#--report-interval=10 表示每10秒输出一次测试进度报告#--rand-type=uniform 表示随机类型为固定模式，其他几个可选随机模式：uniform(固定),gaussian(高斯),special(特定的),pareto(帕累托)#--max-time=120 表示最大执行时长为 120秒#--max-requests=0 表示总请求数为 0，因为上面已经定义了总执行时长，所以总请求数可以设定为 0；也可以只设定总请求数，不设定最大执行时长#--percentile=99 表示设定采样比例，默认是 95%，即丢弃1%的长请求，在剩余的99%里取最大值

• 3. Start testing

    sysbench--test=/usr/local/share/sysbench/tests/include/oltp_legacy/select.lua--oltp-table-size= 20000--mysql-table-engine=innodb--mysql-user=root--mysql-password=123456--mysql-port=3306--mysql-host= localhost--mysql-db=test--max-requests=0--max-time=60--oltp-tables-count=20--report-interval=10--num_threads=2 Run  

  -------------LOAD-AVG-------cpu-usage------swap----qps--tps--hit%-------Threads------  Time |   1m 5m 15m |USR sys IDL iow|  Si so|     Ins upd del sel iud| Lor hit| Run con CRE cac|20:12:09|  0.02 0.05 0.12|    1 2 97 0|    0 0|       0 0 0 0 0|   0 100.00| 1 1 0 1|20:12:12|  0.02 0.05 0.12|    1 1 98 0|    0 0|       0 0 0 0 0|   0 100.00| 1 1 0 1|20:12:15| 0.02 0.05 0.12|    29 7 64 0|    0 0|    0 0 0 1822 0|   3855 100.00| 1 3 0 0|20:12:18| 0.25 0.10 0.14|    85 15 0 0|    0 0|   0 0 0 5015 0|   10536 100.00| 1 3 0 0|20:12:21| 0.25 0.10 0.14|    85 15 0 0|    0 0|   0 0 0 5427 0|   11394 100.00| 1 3 0 0|20:12:25| 0.55 0.16 0.16|    83 17 0 0|    0 0|   0 0 0 5199 0|   10879 100.00| 1 3 0 0|20:12:28| 0.75 0.21 0.17|  8614 0 0|    0 0|   0 0 0 5290 0|   11116 100.00| 1 3 0 0|20:12:31| 0.75 0.21 0.17|    76 24 0 0|    0 0|    0 0 0 4403 0|   9281 100.00| 1 3 0 0|20:12:34| 1.01 0.27 0.20|    74 26 0 0|    0 0|   0 0 0 4808 0|   10093 100.00| 1 3 0 0|20:12:38| 1.09 0.30 0.20|    82 18 0 0|    0 0|   0 0 0 5214 0|   10882 100.00| 1 3 0 0|20:12:41| 1.09 0.30 0.20|    83 17 0 0|    0 0|   0 0 0 5146 0|   10782 100.00| 1 3 0 0|20:12:44| 1.24 0.35 0.22|    82 18 0 0|    0 0|   0 0 0 5277 0|   11120 100.00| 1 3 0 0|20:12:47| 1.24 0.35 0.22|    83 17 0 0|    0 0|   0 0 0 5415 0|   11423 100.00| 1 3 0 0|20:12:50| 1.38 0.39 0.24|    83 17 0 0|    0 0|   0 0 0 5522 0|   11559 100.00| 1 3 0 0|20:12:54| 1.51 0.43 0.25|    83 17 0 0|    0 0|     00 0 5476 0|   11480 100.00| 1 3 0 0|

• 1m more than 1 represents more than 1 CPU usage
• USR uses CPU percentage on behalf of the user

• The SEL for QPS stands for select DML, at about 5000

http://blog.csdn.net/oahz4699092zhao/article/details/53332105

4.TPS

• DML performance, number of transactions per second

• Can be randomly measured with the Olut.lua script

  -------------LOAD-AVG-------cpu-usage------swap----qps--tps--hit%-------Threads------  Time |   1m 5m 15m |USR sys IDL iow|  Si so|     Ins upd del sel iud| Lor hit| Run con CRE cac|00:09:42|  0.97 0.29 0.14|    2 2 96 0|    0 0|       0 0 0 0 0|   0 100.00| 0 0 0 0|00:09:45|  0.98 0.30 0.14|    2 13 52 33|    0 0|       0 0 0 0 0|   0 100.00| 1 1 0 2|00:09:48|  0.98 0.30 0.14|    2 9 76 13|    0 0|       0 0 0 0 0|   0 100.00| 1 1 0 2|00:09:52|  1.06 0.33 0.15|    4 18 43 35|    0 0|      0 0 0 0 0|   49 100.00| 1 1 0 2|00:09:55| 1.06 0.33 0.15|    18 22 34 26|   0 0|    15 31 15 220 62|   3322 100.00| 3 3 0 0|00:09:58| 1.21 0.37 0.17|    19 25 28 29|   0 0|    18 36 18 257 73|   3912 100.00| 3 3 0 0|00:10:01| 1.28 0.40 0.18|  2032 17 31|   0 0|    17 34 17 243 69|   3600 100.00| 3 3 0 0|00:10:04| 1.28 0.40 0.18|    16 25 31 27|   0 0|    15 30 15 210 60|   2756 100.00| 3 3 0 0|00:10:07| 1.41 0.44 0.19|    23 32 27 18|   0 0|    20 40 20 285 81|   4518 100.00| 3 3 0 0|00:10:10| 1.62 0.50 0.21|    20 30 30 21|   0 0|    20 40 20 285 81|   4040 100.00| 3 3 0 0|00:10:13| 1.62 0.50 0.21|    22 30 22 26|   0 0|    19 39 19 280 79|   3540 100.00| 3 3 0 0|00:10:17| 1.65 0.52 0.22|    16 24 32 28|   0 0|    15 30 15 210 61|   3112 100.00| 3 3 0 0|00:10:20| 1.65 0.52 0.22|    21 25 38 15|   0 0|    21 42 21 299 85|   4237 100.00| 3 3 0 0|00:10:23| 1.84 0.58 0.24|    19 21 43 17|   0 0|    20 40 20 285 81|   4069 100.00| 3 3 0 0|00:10:26| 2.09 0.66 0.27|    20 22 35 23|   0 0| 20 41 20 290 82|   4736 100.00| 3 3 0 0|-------------load-avg-------cpu-usage------swap----qps--tps--hit%-----  --threads------Time |   1m 5m 15m |USR sys IDL iow|  Si so|     Ins upd del sel iud| Lor hit| Run con CRE cac|00:10:29| 2.09 0.66 0.27|    21 22 34 24|   0 0|    21 43 21 304 86|   4324 100.00| 3 3 0 0|00:10:32| 2.33 0.73 0.29|    18 20 39 24|   0 0|    18 36 18 257 73|   3739 100.00| 3 3 0 0|00:10:35| 2.33 0.73 0.29|    22 24 33 22|   0 0|    23 46 23 327 93|   4719 100.00| 3 3 0 0|00:10:38| 2.54 0.80 0.32|    19 22 41 19|   0 0|    20 41 20 290 82|   4186 100.00| 3 3 0 0|00:10:41| 2.58 0.83 0.33|    22 23 36 19|   0 0|    22 44 22 308 88|   4458 100.00| 3 3 0 0|00:10:44| 2.58 0.83 0.33|    19 23 32 26|   0 0|    20 40 20 285 81| 4166 100.00| 3 3 0 0|00:10:47| 2.61 0.87 0.35|    19 24 36 22|   0 0|    22 45 22 318 90|   4448 100.00| 3 3 0 0|00:10:50| 2.72 0.92 0.37|    24 26 36 14|   0 0|    28 56 28 397 113|   6076 100.00| 3 3 0 0|00:10:54| 2.72 0.92 0.37|    12 16 55 18|   0 0|    12 24 12 173 49|   2718 100.00| 1 1 0 2|00:10:57|  2.66 0.94 0.37|    3 36 48 13|    0 0|       0 0 0 0 0|   0 100.00| 1 1 0 2|

Index design of sub-table sub-Library 1. Order primary Key ID

• (1) Length 26 bits (170325131201 000000010 01 01)
• (2) Format: [Yymmddhhmmss (12 bit)] [loop increment sequence (9 bit)] [library ordinal (2 bit)] [table ordinal (2 bit)]
• (3) Intermediate variable =userid% (number of libraries * number of tables per library)
• (4) Library ordinal = Intermediate variable/number of tables per library
• (5) ordinal = Intermediate variable% number of tables per library

2.order_num Unique Index

There are 20% to Order_num (order number) to check the data, please order_num (order number) How to design to quickly bring to find the order record is in which library which table?

Twiiter Global Unique ID

• (1) 41-bit time series
• (2) 10-bit machine ID
• (3) 12-bit count sequence number
• (4) Length 64 bits (41 + 10 + 12)

The order number is a little long.

Order number

• (1) To find out the library number
• (2) To find the table number of the mold

Http://www.bubuko.com/infodetail-2152233.html

3. Non-primary key index

There are 10% non-primary key fields (such as fields, channels, commodities, schedule, brand, recipient information, amount, etc.) to check the record, non-primary key field how to design to quickly take the location to the record?

Or is the primary key index being routed through a level two index?

= = Not Completed = =

MySQL Small white series _12 Sysbench