172.16.0.254 dell vostro 成本3500
mysql-community-server-5.7.13-1.el7.x86_64
----------------------CPU:
[yeqiang@localhost ~]$ cat /proc/cpuinfo | grep "model n"
model name : Intel(R) Core(TM) i5-4460 CPU @ 3.20GHz
model name : Intel(R) Core(TM) i5-4460 CPU @ 3.20GHz
model name : Intel(R) Core(TM) i5-4460 CPU @ 3.20GHz
model name : Intel(R) Core(TM) i5-4460 CPU @ 3.20GHz
单核测试
[root@localhost /]# sysbench cpu --cpu-max-prime=10000 run
sysbench 1.0.8 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Prime numbers limit: 10000
Initializing worker threads...
Threads started!
CPU speed:
events per second: 973.06
General statistics:
total time: 10.0009s
total number of events: 9733
Latency (ms):
min: 1.02
avg: 1.03
max: 2.21
95th percentile: 1.08
sum: 9998.21
Threads fairness:
events (avg/stddev): 9733.0000/0.00
execution time (avg/stddev): 9.9982/0.00
----------------------Memory:
[yeqiang@localhost ~]$ free -h
total used free shared buff/cache available
Mem: 15G 3.9G 1.9G 61M 9.8G 11G
Swap: 8.0G 0B 8.0G
[root@localhost /]# sysbench memory run
sysbench 1.0.8 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Running memory speed test with the following options:
block size: 1KiB
total size: 102400MiB
operation: write
scope: global
Initializing worker threads...
Threads started!
Total operations: 53573923 (5356496.49 per second)
52318.28 MiB transferred (5230.95 MiB/sec)
General statistics:
total time: 10.0000s
total number of events: 53573923
Latency (ms):
min: 0.00
avg: 0.00
max: 0.03
95th percentile: 0.00
sum: 4466.43
Threads fairness:
events (avg/stddev): 53573923.0000/0.00
execution time (avg/stddev): 4.4664/0.00
----------------------Disk:
[yeqiang@localhost ~]$ sudo smartctl --all /dev/sdb
smartctl 6.5 2016-05-07 r4318 [x86_64-linux-4.4.14-200.fc22.x86_64] (local build)
Copyright (C) 2002-16, Bruce Allen, Christian Franke, www.smartmontools.org
=== START OF INFORMATION SECTION ===
Device Model: Samsung SSD 750 EVO 250GB
Serial Number: S3J2NWAHB32667D
LU WWN Device Id: 5 002538 d70454c2f
Firmware Version: MAT01B6Q
User Capacity: 250,059,350,016 bytes [250 GB]
Sector Size: 512 bytes logical/physical
Rotation Rate: Solid State Device 固态硬盘!!!
Form Factor: 2.5 inches
Device is: Not in smartctl database [for details use: -P showall]
ATA Version is: ACS-2, ATA8-ACS T13/1699-D revision 4c
SATA Version is: SATA 3.1, 6.0 Gb/s (current: 3.0 Gb/s)
Local Time is: Fri Aug 11 13:50:45 2017 CST
SMART support is: Available - device has SMART capability.
SMART support is: Enabled
[root@localhost /]# sysbench fileio --file-total-size=2G prepare
sysbench 1.0.8 (using bundled LuaJIT 2.1.0-beta2)
128 files, 16384Kb each, 2048Mb total
Creating files for the test...
Extra file open flags: 0
Creating file test_file.0
Creating file test_file.1
Creating file test_file.2
Creating file test_file.3
Creating file test_file.4
Creating file test_file.5
Creating file test_file.6
Creating file test_file.7
Creating file test_file.8
Creating file test_file.9
Creating file test_file.10
Creating file test_file.11
Creating file test_file.12
Creating file test_file.13
Creating file test_file.14
Creating file test_file.15
Creating file test_file.16
Creating file test_file.17
Creating file test_file.18
Creating file test_file.19
Creating file test_file.20
Creating file test_file.21
Creating file test_file.22
Creating file test_file.23
Creating file test_file.24
Creating file test_file.25
Creating file test_file.26
Creating file test_file.27
Creating file test_file.28
Creating file test_file.29
Creating file test_file.30
Creating file test_file.31
Creating file test_file.32
Creating file test_file.33
Creating file test_file.34
Creating file test_file.35
Creating file test_file.36
Creating file test_file.37
Creating file test_file.38
Creating file test_file.39
Creating file test_file.40
Creating file test_file.41
Creating file test_file.42
Creating file test_file.43
Creating file test_file.44
Creating file test_file.45
Creating file test_file.46
Creating file test_file.47
Creating file test_file.48
Creating file test_file.49
Creating file test_file.50
Creating file test_file.51
Creating file test_file.52
Creating file test_file.53
Creating file test_file.54
Creating file test_file.55
Creating file test_file.56
Creating file test_file.57
Creating file test_file.58
Creating file test_file.59
Creating file test_file.60
Creating file test_file.61
Creating file test_file.62
Creating file test_file.63
Creating file test_file.64
Creating file test_file.65
Creating file test_file.66
Creating file test_file.67
Creating file test_file.68
Creating file test_file.69
Creating file test_file.70
Creating file test_file.71
Creating file test_file.72
Creating file test_file.73
Creating file test_file.74
Creating file test_file.75
Creating file test_file.76
Creating file test_file.77
Creating file test_file.78
Creating file test_file.79
Creating file test_file.80
Creating file test_file.81
Creating file test_file.82
Creating file test_file.83
Creating file test_file.84
Creating file test_file.85
Creating file test_file.86
Creating file test_file.87
Creating file test_file.88
Creating file test_file.89
Creating file test_file.90
Creating file test_file.91
Creating file test_file.92
Creating file test_file.93
Creating file test_file.94
Creating file test_file.95
Creating file test_file.96
Creating file test_file.97
Creating file test_file.98
Creating file test_file.99
Creating file test_file.100
Creating file test_file.101
Creating file test_file.102
Creating file test_file.103
Creating file test_file.104
Creating file test_file.105
Creating file test_file.106
Creating file test_file.107
Creating file test_file.108
Creating file test_file.109
Creating file test_file.110
Creating file test_file.111
Creating file test_file.112
Creating file test_file.113
Creating file test_file.114
Creating file test_file.115
Creating file test_file.116
Creating file test_file.117
Creating file test_file.118
Creating file test_file.119
Creating file test_file.120
Creating file test_file.121
Creating file test_file.122
Creating file test_file.123
Creating file test_file.124
Creating file test_file.125
Creating file test_file.126
Creating file test_file.127
2147483648 bytes written in 9.32 seconds (219.68 MiB/sec).
[root@localhost /]# sysbench fileio --threads=20 --file-total-size=2G --file-test-mode=rndrw --report-interval=1 run
sysbench 1.0.8 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 20
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Extra file open flags: 0
128 files, 16MiB each
2GiB total file size
Block size 16KiB
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random r/w test
Initializing worker threads...
Threads started!
[ 1s ] reads: 70.18 MiB/s writes: 46.82 MiB/s fsyncs: 9481.72/s latency (ms,95%): 6.913
[ 2s ] reads: 73.12 MiB/s writes: 48.73 MiB/s fsyncs: 9940.90/s latency (ms,95%): 6.669
[ 3s ] reads: 74.09 MiB/s writes: 49.36 MiB/s fsyncs: 10155.95/s latency (ms,95%): 6.550
[ 4s ] reads: 69.36 MiB/s writes: 46.27 MiB/s fsyncs: 9457.20/s latency (ms,95%): 7.430
[ 5s ] reads: 73.14 MiB/s writes: 48.73 MiB/s fsyncs: 9969.50/s latency (ms,95%): 7.039
[ 6s ] reads: 69.38 MiB/s writes: 46.25 MiB/s fsyncs: 9578.49/s latency (ms,95%): 7.167
[ 7s ] reads: 71.20 MiB/s writes: 47.55 MiB/s fsyncs: 9616.94/s latency (ms,95%): 7.039
[ 8s ] reads: 69.39 MiB/s writes: 46.24 MiB/s fsyncs: 9502.53/s latency (ms,95%): 6.790
[ 9s ] reads: 65.65 MiB/s writes: 43.72 MiB/s fsyncs: 8950.42/s latency (ms,95%): 7.430
[ 10s ] reads: 73.13 MiB/s writes: 48.75 MiB/s fsyncs: 9998.06/s latency (ms,95%): 6.433
File operations:
reads/s: 4534.63
writes/s: 3022.92
fsyncs/s: 9663.97
Throughput:
read, MiB/s: 70.85
written, MiB/s: 47.23
General statistics:
total time: 10.0017s
total number of events: 172271
Latency (ms):
min: 0.00
avg: 1.16
max: 31.86
95th percentile: 6.91
sum: 199944.55
Threads fairness:
events (avg/stddev): 8613.5500/357.27
execution time (avg/stddev): 9.9972/0.00
[root@localhost ~]# mysqlslap --concurrency=100 --iterations=1 --number-int-cols=5 --number-char-cols=10 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=myisam,innodb --number-of-queries=1000000 --create-schema=sqltest -uroot -h 172.16.0.254 -p
Enter password:
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 38.022 seconds
Minimum number of seconds to run all queries: 38.022 seconds
Maximum number of seconds to run all queries: 38.022 seconds
Number of clients running queries: 100
Average number of queries per client: 10000
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 299.110 seconds
Minimum number of seconds to run all queries: 299.110 seconds
Maximum number of seconds to run all queries: 299.110 seconds
Number of clients running queries: 100
Average number of queries per client: 10000
========================================================================
MySQL Cluster:
ndbd 2台 参考140配置
mysqld 与ndbd部署在一起
ndb_mgmd 2台普通服务器(无明显计算负载,不考虑其性能)
[root@localhost ~]# mysqlslap --concurrency=100 --iterations=1 --number-int-cols=5 --number-char-cols=10 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=ndbcluster --number-of-queries=1000000 --create-schema=sqltest -uroot -h 172.16.1.160 -p
Enter password:
mysqlslap: Cannot run query INSERT INTO t1 VALUES (NULL,1581550967,2034371439,84567553,102238231,1059295781,'kCzuOBZCrhwd6Bf3MKoSSSpbiEEDRluCqmyEpXIgCFAAIhDZ12tLMiw4o2HGoBKepIkep3BuILwc221v4u9IcxMH0fZoitQO3A2s55eNQbsKdt8ZoHrGE5Z6kyuIjC','imFdXKi1c9cSL5DTeteLAtQbr2QmL9q3o5gJQNKuOpMluRFPLlmNe5qW7G8SQyOEwwPEJd8bIMLDpSWAD8PkDG6LY6hqw54suL6DrEh9SuEIEkKJtm36u1JQ7t94qD','okRvqI1XRWPigfI0yBeuAYvLrvE0r55fhOQXiS22qJkWy2Wp61b8PMTGHk80h6YMWym5Io788jeyd1fjuErJdDSmpcnMaJk59WASB9cBs71T8h52noDGth3iAi4kth','h23rWEs7HkEJ6Es38XjDodvrJ5ZGhQxNK06rFzqOj4jhARcAzKNnxAW8FvOxDLKZEQOLHEl1ALaCoBMfNeQKowKwrdl4yxeCNuPZ9BaBY0OC3esQGAex7ztqcenQbH','Xq3wNCzpGXigklISCReqOdh9SHn7cEGz4JvKOv1gIjKuTWMhp1NDe5MyE1xqFDpCPdYpOZLi9hde3rBtkayFfD5uE3cM8IQZvqEJFbtO8M24EDpyonDJsJDzEiLE0f','gvTu6kMz9MM3QSHFIx3YGnjMmp4z34ejr7epssotE2whM5wh2r5BXoZHdTHgZvGG3u5nelYsgSPuQMbS5gfcK4mxZ3WyqmEtYBYApwuLHKFjarBXNj0knmhLp3JfhQ','PDTpFImAf3Wvm4epb1YblKDJZ2EsawAr9gYPyARWDxHrtvZKo5oQH1liwrRWndDpHlE8oZWtMnlFaAbM7NgPHPzDHQbw4olDPrDDOjzmWctwmTJtkzA1HAEQcpEgT0','mnrzs8jru5Ro2D9Dzl49nDJtfobmKwm6jT6SdhBOmf4ok51bq6ldJwNrkyTxmF3Wc1q6i1WLYQ9HX3kL1TrCI6vu4OrqXvC8ouEXwckS2tdZoMmpjTu1bpwXEfEdaI','lNDrm9RWTLFzm4DPloufiWLW1R9F4caqrfZTgtz9E60125QgmCJSigyczzr4bHmIwJncCeeJlWCfSWJEifiPBY1BNk7zutHiCxBHbhcK3osXIBlsiJHmjAp7nwyZpF','Ft4I2gzWsvAkQSw4LeNWGzZ56K14liB0SGBWw21oxBPOYLKlrj78ag5gs7kdhoW94hYsjPYZcxx0AHd2sl2RP7zA6cnoO9pKqLCQmkzFio7sxlmhMo8no0pucda0eG') ERROR : Got temporary error 410 'REDO log files overloaded (decrease TimeBetweenLocalCheckpoints or increase NoOfFragmentLogFiles)' from NDBCLUSTER
修改ndb_mgmd config.ini配置文件
TimeBetweenLocalCheckpoints=6
NoOfFragmentLogFiles=256M
重启ndb_mgm节点
[root@localhost ~]# mysqlslap --concurrency=100 --iterations=1 --number-int-cols=5 --number-char-cols=10 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=ndbcluster --number-of-queries=1000000 --create-schema=sqltest -uroot -h 172.16.1.160 -p
Enter password:
Benchmark
Running for engine ndbcluster
Average number of seconds to run all queries: 286.537 seconds
Minimum number of seconds to run all queries: 286.537 seconds
Maximum number of seconds to run all queries: 286.537 seconds
Number of clients running queries: 100
Average number of queries per client: 10000
========================================================================
172.16.1.140 dell r720 成本20000
[root@localhost ~]# cat /proc/cpuinfo | grep "model n"
model name : Intel(R) Xeon(R) CPU E5-2609 v2 @ 2.50GHz
model name : Intel(R) Xeon(R) CPU E5-2609 v2 @ 2.50GHz
model name : Intel(R) Xeon(R) CPU E5-2609 v2 @ 2.50GHz
model name : Intel(R) Xeon(R) CPU E5-2609 v2 @ 2.50GHz
model name : Intel(R) Xeon(R) CPU E5-2609 v2 @ 2.50GHz
model name : Intel(R) Xeon(R) CPU E5-2609 v2 @ 2.50GHz
model name : Intel(R) Xeon(R) CPU E5-2609 v2 @ 2.50GHz
model name : Intel(R) Xeon(R) CPU E5-2609 v2 @ 2.50GHz
[root@localhost /]# sysbench cpu --cpu-max-prime=10000 run
sysbench 1.0.8 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Prime numbers limit: 10000
Initializing worker threads...
Threads started!
CPU speed:
events per second: 815.71
General statistics:
total time: 10.0006s
total number of events: 8161
Latency (ms):
min: 1.22
avg: 1.23
max: 2.60
95th percentile: 1.23
sum: 9998.43
Threads fairness:
events (avg/stddev): 8161.0000/0.00
execution time (avg/stddev): 9.9984/0.00
[root@localhost ~]# smartctl --all /dev/sda
smartctl 6.2 2013-07-26 r3841 [x86_64-linux-3.10.0-514.el7.x86_64] (local build)
Copyright (C) 2002-13, Bruce Allen, Christian Franke, www.smartmontools.org
=== START OF INFORMATION SECTION ===
Vendor: DELL
Product: PERC H310
Revision: 2.12
User Capacity: 6,000,069,312,512 bytes [6.00 TB]
Logical block size: 512 bytes
Logical Unit id: 0x614187704bf9b0001ef09ff40ce17306
Serial number: 000673e10cf49ff01e00b0f94b708741
Device type: disk
Local Time is: Fri Aug 11 01:54:11 2017 EDT
SMART support is: Unavailable - device lacks SMART capability.
3快3T做RAID5整列(以后做raid10整列)
[root@localhost /]# sysbench fileio --threads=20 --file-total-size=2G --file-test-mode=rndrw --report-interval=1 run
sysbench 1.0.8 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 20
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Extra file open flags: 0
128 files, 16MiB each
2GiB total file size
Block size 16KiB
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random r/w test
FATAL: Cannot open file 'test_file.0' errno = 2 (No such file or directory)
WARNING: Did you forget to run the prepare step?
[root@localhost /]# sysbench fileio --file-total-size=2G prepare
sysbench 1.0.8 (using bundled LuaJIT 2.1.0-beta2)
128 files, 16384Kb each, 2048Mb total
Creating files for the test...
Extra file open flags: 0
Creating file test_file.0
Creating file test_file.1
Creating file test_file.2
Creating file test_file.3
Creating file test_file.4
Creating file test_file.5
Creating file test_file.6
Creating file test_file.7
Creating file test_file.8
Creating file test_file.9
Creating file test_file.10
Creating file test_file.11
Creating file test_file.12
Creating file test_file.13
Creating file test_file.14
Creating file test_file.15
Creating file test_file.16
Creating file test_file.17
Creating file test_file.18
Creating file test_file.19
Creating file test_file.20
Creating file test_file.21
Creating file test_file.22
Creating file test_file.23
Creating file test_file.24
Creating file test_file.25
Creating file test_file.26
Creating file test_file.27
Creating file test_file.28
Creating file test_file.29
Creating file test_file.30
Creating file test_file.31
Creating file test_file.32
Creating file test_file.33
Creating file test_file.34
Creating file test_file.35
Creating file test_file.36
Creating file test_file.37
Creating file test_file.38
Creating file test_file.39
Creating file test_file.40
Creating file test_file.41
Creating file test_file.42
Creating file test_file.43
Creating file test_file.44
Creating file test_file.45
Creating file test_file.46
Creating file test_file.47
Creating file test_file.48
Creating file test_file.49
Creating file test_file.50
Creating file test_file.51
Creating file test_file.52
Creating file test_file.53
Creating file test_file.54
Creating file test_file.55
Creating file test_file.56
Creating file test_file.57
Creating file test_file.58
Creating file test_file.59
Creating file test_file.60
Creating file test_file.61
Creating file test_file.62
Creating file test_file.63
Creating file test_file.64
Creating file test_file.65
Creating file test_file.66
Creating file test_file.67
Creating file test_file.68
Creating file test_file.69
Creating file test_file.70
Creating file test_file.71
Creating file test_file.72
Creating file test_file.73
Creating file test_file.74
Creating file test_file.75
Creating file test_file.76
Creating file test_file.77
Creating file test_file.78
Creating file test_file.79
Creating file test_file.80
Creating file test_file.81
Creating file test_file.82
Creating file test_file.83
Creating file test_file.84
Creating file test_file.85
Creating file test_file.86
Creating file test_file.87
Creating file test_file.88
Creating file test_file.89
Creating file test_file.90
Creating file test_file.91
Creating file test_file.92
Creating file test_file.93
Creating file test_file.94
Creating file test_file.95
Creating file test_file.96
Creating file test_file.97
Creating file test_file.98
Creating file test_file.99
Creating file test_file.100
Creating file test_file.101
Creating file test_file.102
Creating file test_file.103
Creating file test_file.104
Creating file test_file.105
Creating file test_file.106
Creating file test_file.107
Creating file test_file.108
Creating file test_file.109
Creating file test_file.110
Creating file test_file.111
Creating file test_file.112
Creating file test_file.113
Creating file test_file.114
Creating file test_file.115
Creating file test_file.116
Creating file test_file.117
Creating file test_file.118
Creating file test_file.119
Creating file test_file.120
Creating file test_file.121
Creating file test_file.122
Creating file test_file.123
Creating file test_file.124
Creating file test_file.125
Creating file test_file.126
Creating file test_file.127
2147483648 bytes written in 142.26 seconds (14.40 MiB/sec).
[root@localhost /]# sysbench fileio --threads=20 --file-total-size=2G --file-test-mode=rndrw --report-interval=1 run
sysbench 1.0.8 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 20
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Extra file open flags: 0
128 files, 16MiB each
2GiB total file size
Block size 16KiB
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random r/w test
Initializing worker threads...
Threads started!
[ 1s ] reads: 10.25 MiB/s writes: 6.83 MiB/s fsyncs: 1265.71/s latency (ms,95%): 55.824
[ 2s ] reads: 7.52 MiB/s writes: 5.02 MiB/s fsyncs: 1117.16/s latency (ms,95%): 74.464
[ 3s ] reads: 7.50 MiB/s writes: 5.00 MiB/s fsyncs: 1037.99/s latency (ms,95%): 77.194
[ 4s ] reads: 7.50 MiB/s writes: 5.00 MiB/s fsyncs: 1004.02/s latency (ms,95%): 82.959
[ 5s ] reads: 8.44 MiB/s writes: 5.63 MiB/s fsyncs: 1092.01/s latency (ms,95%): 68.053
[ 6s ] reads: 7.50 MiB/s writes: 5.00 MiB/s fsyncs: 1077.00/s latency (ms,95%): 77.194
[ 7s ] reads: 7.50 MiB/s writes: 5.00 MiB/s fsyncs: 1051.01/s latency (ms,95%): 74.464
[ 8s ] reads: 8.44 MiB/s writes: 5.62 MiB/s fsyncs: 1049.99/s latency (ms,95%): 74.464
[ 9s ] reads: 7.50 MiB/s writes: 5.00 MiB/s fsyncs: 1088.00/s latency (ms,95%): 73.135
[ 10s ] reads: 7.50 MiB/s writes: 5.00 MiB/s fsyncs: 1048.02/s latency (ms,95%): 69.289
File operations:
reads/s: 505.21
writes/s: 336.81
fsyncs/s: 1075.50
Throughput:
read, MiB/s: 7.89
written, MiB/s: 5.26
General statistics:
total time: 10.0910s
total number of events: 19357
Latency (ms):
min: 0.00
avg: 10.39
max: 459.54
95th percentile: 74.46
sum: 201035.26
Threads fairness:
events (avg/stddev): 967.8500/193.24
execution time (avg/stddev): 10.0518/0.02
[root@localhost ~]# free -h
total used free shared buff/cache available
Mem: 125G 800M 124G 8.7M 396M 124G
Swap: 4.0G 0B 4.0G
[root@localhost /]# sysbench memory run
sysbench 1.0.8 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Running memory speed test with the following options:
block size: 1KiB
total size: 102400MiB
operation: write
scope: global
Initializing worker threads...
Threads started!
Total operations: 33786047 (3377140.21 per second)
32994.19 MiB transferred (3297.99 MiB/sec)
General statistics:
total time: 10.0002s
total number of events: 33786047
Latency (ms):
min: 0.00
avg: 0.00
max: 0.05
95th percentile: 0.00
sum: 4351.38
Threads fairness:
events (avg/stddev): 33786047.0000/0.00
execution time (avg/stddev): 4.3514/0.00
[yeqiang@localhost ~]$ mysqlslap --concurrency=100 --iterations=1 --number-int-cols=5 --number-char-cols=10 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=myisam,innodb --number-of-queries=1000000 --create-schema=sqltest -uroot -h 172.16.1.140 -p
Enter password:
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 48.247 seconds
Minimum number of seconds to run all queries: 48.247 seconds
Maximum number of seconds to run all queries: 48.247 seconds
Number of clients running queries: 100
Average number of queries per client: 10000
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 318.824 seconds
Minimum number of seconds to run all queries: 318.824 seconds
Maximum number of seconds to run all queries: 318.824 seconds
Number of clients running queries: 100
Average number of queries per client: 10000
总结:
MySQL Cluster表现出来的性能低于预期,但是服务器CPU、磁盘IO,网络IO等还有很大余量
在不考虑稳定性及可靠性情况下,2万块的dell r720表现出来的性能对比三千多的台式机没有明显优势,仅仅在两块E5-2609 v2(共8核)上有一定计算优势及多大125G内存系统内存。
固态硬盘对MySQL性能表现有积极影响。
对于一些最求高速读写又不需要数据库事物控制的表,采用myisam引擎可以带来接近一个数量级的读写性能提升。
mysql-community-server-5.7.13-1.el7.x86_64
----------------------CPU:
[yeqiang@localhost ~]$ cat /proc/cpuinfo | grep "model n"
model name : Intel(R) Core(TM) i5-4460 CPU @ 3.20GHz
model name : Intel(R) Core(TM) i5-4460 CPU @ 3.20GHz
model name : Intel(R) Core(TM) i5-4460 CPU @ 3.20GHz
model name : Intel(R) Core(TM) i5-4460 CPU @ 3.20GHz
单核测试
[root@localhost /]# sysbench cpu --cpu-max-prime=10000 run
sysbench 1.0.8 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Prime numbers limit: 10000
Initializing worker threads...
Threads started!
CPU speed:
events per second: 973.06
General statistics:
total time: 10.0009s
total number of events: 9733
Latency (ms):
min: 1.02
avg: 1.03
max: 2.21
95th percentile: 1.08
sum: 9998.21
Threads fairness:
events (avg/stddev): 9733.0000/0.00
execution time (avg/stddev): 9.9982/0.00
----------------------Memory:
[yeqiang@localhost ~]$ free -h
total used free shared buff/cache available
Mem: 15G 3.9G 1.9G 61M 9.8G 11G
Swap: 8.0G 0B 8.0G
[root@localhost /]# sysbench memory run
sysbench 1.0.8 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Running memory speed test with the following options:
block size: 1KiB
total size: 102400MiB
operation: write
scope: global
Initializing worker threads...
Threads started!
Total operations: 53573923 (5356496.49 per second)
52318.28 MiB transferred (5230.95 MiB/sec)
General statistics:
total time: 10.0000s
total number of events: 53573923
Latency (ms):
min: 0.00
avg: 0.00
max: 0.03
95th percentile: 0.00
sum: 4466.43
Threads fairness:
events (avg/stddev): 53573923.0000/0.00
execution time (avg/stddev): 4.4664/0.00
----------------------Disk:
[yeqiang@localhost ~]$ sudo smartctl --all /dev/sdb
smartctl 6.5 2016-05-07 r4318 [x86_64-linux-4.4.14-200.fc22.x86_64] (local build)
Copyright (C) 2002-16, Bruce Allen, Christian Franke, www.smartmontools.org
=== START OF INFORMATION SECTION ===
Device Model: Samsung SSD 750 EVO 250GB
Serial Number: S3J2NWAHB32667D
LU WWN Device Id: 5 002538 d70454c2f
Firmware Version: MAT01B6Q
User Capacity: 250,059,350,016 bytes [250 GB]
Sector Size: 512 bytes logical/physical
Rotation Rate: Solid State Device 固态硬盘!!!
Form Factor: 2.5 inches
Device is: Not in smartctl database [for details use: -P showall]
ATA Version is: ACS-2, ATA8-ACS T13/1699-D revision 4c
SATA Version is: SATA 3.1, 6.0 Gb/s (current: 3.0 Gb/s)
Local Time is: Fri Aug 11 13:50:45 2017 CST
SMART support is: Available - device has SMART capability.
SMART support is: Enabled
[root@localhost /]# sysbench fileio --file-total-size=2G prepare
sysbench 1.0.8 (using bundled LuaJIT 2.1.0-beta2)
128 files, 16384Kb each, 2048Mb total
Creating files for the test...
Extra file open flags: 0
Creating file test_file.0
Creating file test_file.1
Creating file test_file.2
Creating file test_file.3
Creating file test_file.4
Creating file test_file.5
Creating file test_file.6
Creating file test_file.7
Creating file test_file.8
Creating file test_file.9
Creating file test_file.10
Creating file test_file.11
Creating file test_file.12
Creating file test_file.13
Creating file test_file.14
Creating file test_file.15
Creating file test_file.16
Creating file test_file.17
Creating file test_file.18
Creating file test_file.19
Creating file test_file.20
Creating file test_file.21
Creating file test_file.22
Creating file test_file.23
Creating file test_file.24
Creating file test_file.25
Creating file test_file.26
Creating file test_file.27
Creating file test_file.28
Creating file test_file.29
Creating file test_file.30
Creating file test_file.31
Creating file test_file.32
Creating file test_file.33
Creating file test_file.34
Creating file test_file.35
Creating file test_file.36
Creating file test_file.37
Creating file test_file.38
Creating file test_file.39
Creating file test_file.40
Creating file test_file.41
Creating file test_file.42
Creating file test_file.43
Creating file test_file.44
Creating file test_file.45
Creating file test_file.46
Creating file test_file.47
Creating file test_file.48
Creating file test_file.49
Creating file test_file.50
Creating file test_file.51
Creating file test_file.52
Creating file test_file.53
Creating file test_file.54
Creating file test_file.55
Creating file test_file.56
Creating file test_file.57
Creating file test_file.58
Creating file test_file.59
Creating file test_file.60
Creating file test_file.61
Creating file test_file.62
Creating file test_file.63
Creating file test_file.64
Creating file test_file.65
Creating file test_file.66
Creating file test_file.67
Creating file test_file.68
Creating file test_file.69
Creating file test_file.70
Creating file test_file.71
Creating file test_file.72
Creating file test_file.73
Creating file test_file.74
Creating file test_file.75
Creating file test_file.76
Creating file test_file.77
Creating file test_file.78
Creating file test_file.79
Creating file test_file.80
Creating file test_file.81
Creating file test_file.82
Creating file test_file.83
Creating file test_file.84
Creating file test_file.85
Creating file test_file.86
Creating file test_file.87
Creating file test_file.88
Creating file test_file.89
Creating file test_file.90
Creating file test_file.91
Creating file test_file.92
Creating file test_file.93
Creating file test_file.94
Creating file test_file.95
Creating file test_file.96
Creating file test_file.97
Creating file test_file.98
Creating file test_file.99
Creating file test_file.100
Creating file test_file.101
Creating file test_file.102
Creating file test_file.103
Creating file test_file.104
Creating file test_file.105
Creating file test_file.106
Creating file test_file.107
Creating file test_file.108
Creating file test_file.109
Creating file test_file.110
Creating file test_file.111
Creating file test_file.112
Creating file test_file.113
Creating file test_file.114
Creating file test_file.115
Creating file test_file.116
Creating file test_file.117
Creating file test_file.118
Creating file test_file.119
Creating file test_file.120
Creating file test_file.121
Creating file test_file.122
Creating file test_file.123
Creating file test_file.124
Creating file test_file.125
Creating file test_file.126
Creating file test_file.127
2147483648 bytes written in 9.32 seconds (219.68 MiB/sec).
[root@localhost /]# sysbench fileio --threads=20 --file-total-size=2G --file-test-mode=rndrw --report-interval=1 run
sysbench 1.0.8 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 20
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Extra file open flags: 0
128 files, 16MiB each
2GiB total file size
Block size 16KiB
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random r/w test
Initializing worker threads...
Threads started!
[ 1s ] reads: 70.18 MiB/s writes: 46.82 MiB/s fsyncs: 9481.72/s latency (ms,95%): 6.913
[ 2s ] reads: 73.12 MiB/s writes: 48.73 MiB/s fsyncs: 9940.90/s latency (ms,95%): 6.669
[ 3s ] reads: 74.09 MiB/s writes: 49.36 MiB/s fsyncs: 10155.95/s latency (ms,95%): 6.550
[ 4s ] reads: 69.36 MiB/s writes: 46.27 MiB/s fsyncs: 9457.20/s latency (ms,95%): 7.430
[ 5s ] reads: 73.14 MiB/s writes: 48.73 MiB/s fsyncs: 9969.50/s latency (ms,95%): 7.039
[ 6s ] reads: 69.38 MiB/s writes: 46.25 MiB/s fsyncs: 9578.49/s latency (ms,95%): 7.167
[ 7s ] reads: 71.20 MiB/s writes: 47.55 MiB/s fsyncs: 9616.94/s latency (ms,95%): 7.039
[ 8s ] reads: 69.39 MiB/s writes: 46.24 MiB/s fsyncs: 9502.53/s latency (ms,95%): 6.790
[ 9s ] reads: 65.65 MiB/s writes: 43.72 MiB/s fsyncs: 8950.42/s latency (ms,95%): 7.430
[ 10s ] reads: 73.13 MiB/s writes: 48.75 MiB/s fsyncs: 9998.06/s latency (ms,95%): 6.433
File operations:
reads/s: 4534.63
writes/s: 3022.92
fsyncs/s: 9663.97
Throughput:
read, MiB/s: 70.85
written, MiB/s: 47.23
General statistics:
total time: 10.0017s
total number of events: 172271
Latency (ms):
min: 0.00
avg: 1.16
max: 31.86
95th percentile: 6.91
sum: 199944.55
Threads fairness:
events (avg/stddev): 8613.5500/357.27
execution time (avg/stddev): 9.9972/0.00
[root@localhost ~]# mysqlslap --concurrency=100 --iterations=1 --number-int-cols=5 --number-char-cols=10 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=myisam,innodb --number-of-queries=1000000 --create-schema=sqltest -uroot -h 172.16.0.254 -p
Enter password:
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 38.022 seconds
Minimum number of seconds to run all queries: 38.022 seconds
Maximum number of seconds to run all queries: 38.022 seconds
Number of clients running queries: 100
Average number of queries per client: 10000
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 299.110 seconds
Minimum number of seconds to run all queries: 299.110 seconds
Maximum number of seconds to run all queries: 299.110 seconds
Number of clients running queries: 100
Average number of queries per client: 10000
========================================================================
MySQL Cluster:
ndbd 2台 参考140配置
mysqld 与ndbd部署在一起
ndb_mgmd 2台普通服务器(无明显计算负载,不考虑其性能)
[root@localhost ~]# mysqlslap --concurrency=100 --iterations=1 --number-int-cols=5 --number-char-cols=10 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=ndbcluster --number-of-queries=1000000 --create-schema=sqltest -uroot -h 172.16.1.160 -p
Enter password:
mysqlslap: Cannot run query INSERT INTO t1 VALUES (NULL,1581550967,2034371439,84567553,102238231,1059295781,'kCzuOBZCrhwd6Bf3MKoSSSpbiEEDRluCqmyEpXIgCFAAIhDZ12tLMiw4o2HGoBKepIkep3BuILwc221v4u9IcxMH0fZoitQO3A2s55eNQbsKdt8ZoHrGE5Z6kyuIjC','imFdXKi1c9cSL5DTeteLAtQbr2QmL9q3o5gJQNKuOpMluRFPLlmNe5qW7G8SQyOEwwPEJd8bIMLDpSWAD8PkDG6LY6hqw54suL6DrEh9SuEIEkKJtm36u1JQ7t94qD','okRvqI1XRWPigfI0yBeuAYvLrvE0r55fhOQXiS22qJkWy2Wp61b8PMTGHk80h6YMWym5Io788jeyd1fjuErJdDSmpcnMaJk59WASB9cBs71T8h52noDGth3iAi4kth','h23rWEs7HkEJ6Es38XjDodvrJ5ZGhQxNK06rFzqOj4jhARcAzKNnxAW8FvOxDLKZEQOLHEl1ALaCoBMfNeQKowKwrdl4yxeCNuPZ9BaBY0OC3esQGAex7ztqcenQbH','Xq3wNCzpGXigklISCReqOdh9SHn7cEGz4JvKOv1gIjKuTWMhp1NDe5MyE1xqFDpCPdYpOZLi9hde3rBtkayFfD5uE3cM8IQZvqEJFbtO8M24EDpyonDJsJDzEiLE0f','gvTu6kMz9MM3QSHFIx3YGnjMmp4z34ejr7epssotE2whM5wh2r5BXoZHdTHgZvGG3u5nelYsgSPuQMbS5gfcK4mxZ3WyqmEtYBYApwuLHKFjarBXNj0knmhLp3JfhQ','PDTpFImAf3Wvm4epb1YblKDJZ2EsawAr9gYPyARWDxHrtvZKo5oQH1liwrRWndDpHlE8oZWtMnlFaAbM7NgPHPzDHQbw4olDPrDDOjzmWctwmTJtkzA1HAEQcpEgT0','mnrzs8jru5Ro2D9Dzl49nDJtfobmKwm6jT6SdhBOmf4ok51bq6ldJwNrkyTxmF3Wc1q6i1WLYQ9HX3kL1TrCI6vu4OrqXvC8ouEXwckS2tdZoMmpjTu1bpwXEfEdaI','lNDrm9RWTLFzm4DPloufiWLW1R9F4caqrfZTgtz9E60125QgmCJSigyczzr4bHmIwJncCeeJlWCfSWJEifiPBY1BNk7zutHiCxBHbhcK3osXIBlsiJHmjAp7nwyZpF','Ft4I2gzWsvAkQSw4LeNWGzZ56K14liB0SGBWw21oxBPOYLKlrj78ag5gs7kdhoW94hYsjPYZcxx0AHd2sl2RP7zA6cnoO9pKqLCQmkzFio7sxlmhMo8no0pucda0eG') ERROR : Got temporary error 410 'REDO log files overloaded (decrease TimeBetweenLocalCheckpoints or increase NoOfFragmentLogFiles)' from NDBCLUSTER
修改ndb_mgmd config.ini配置文件
TimeBetweenLocalCheckpoints=6
NoOfFragmentLogFiles=256M
重启ndb_mgm节点
[root@localhost ~]# mysqlslap --concurrency=100 --iterations=1 --number-int-cols=5 --number-char-cols=10 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=ndbcluster --number-of-queries=1000000 --create-schema=sqltest -uroot -h 172.16.1.160 -p
Enter password:
Benchmark
Running for engine ndbcluster
Average number of seconds to run all queries: 286.537 seconds
Minimum number of seconds to run all queries: 286.537 seconds
Maximum number of seconds to run all queries: 286.537 seconds
Number of clients running queries: 100
Average number of queries per client: 10000
========================================================================
172.16.1.140 dell r720 成本20000
[root@localhost ~]# cat /proc/cpuinfo | grep "model n"
model name : Intel(R) Xeon(R) CPU E5-2609 v2 @ 2.50GHz
model name : Intel(R) Xeon(R) CPU E5-2609 v2 @ 2.50GHz
model name : Intel(R) Xeon(R) CPU E5-2609 v2 @ 2.50GHz
model name : Intel(R) Xeon(R) CPU E5-2609 v2 @ 2.50GHz
model name : Intel(R) Xeon(R) CPU E5-2609 v2 @ 2.50GHz
model name : Intel(R) Xeon(R) CPU E5-2609 v2 @ 2.50GHz
model name : Intel(R) Xeon(R) CPU E5-2609 v2 @ 2.50GHz
model name : Intel(R) Xeon(R) CPU E5-2609 v2 @ 2.50GHz
[root@localhost /]# sysbench cpu --cpu-max-prime=10000 run
sysbench 1.0.8 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Prime numbers limit: 10000
Initializing worker threads...
Threads started!
CPU speed:
events per second: 815.71
General statistics:
total time: 10.0006s
total number of events: 8161
Latency (ms):
min: 1.22
avg: 1.23
max: 2.60
95th percentile: 1.23
sum: 9998.43
Threads fairness:
events (avg/stddev): 8161.0000/0.00
execution time (avg/stddev): 9.9984/0.00
[root@localhost ~]# smartctl --all /dev/sda
smartctl 6.2 2013-07-26 r3841 [x86_64-linux-3.10.0-514.el7.x86_64] (local build)
Copyright (C) 2002-13, Bruce Allen, Christian Franke, www.smartmontools.org
=== START OF INFORMATION SECTION ===
Vendor: DELL
Product: PERC H310
Revision: 2.12
User Capacity: 6,000,069,312,512 bytes [6.00 TB]
Logical block size: 512 bytes
Logical Unit id: 0x614187704bf9b0001ef09ff40ce17306
Serial number: 000673e10cf49ff01e00b0f94b708741
Device type: disk
Local Time is: Fri Aug 11 01:54:11 2017 EDT
SMART support is: Unavailable - device lacks SMART capability.
3快3T做RAID5整列(以后做raid10整列)
[root@localhost /]# sysbench fileio --threads=20 --file-total-size=2G --file-test-mode=rndrw --report-interval=1 run
sysbench 1.0.8 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 20
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Extra file open flags: 0
128 files, 16MiB each
2GiB total file size
Block size 16KiB
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random r/w test
FATAL: Cannot open file 'test_file.0' errno = 2 (No such file or directory)
WARNING: Did you forget to run the prepare step?
[root@localhost /]# sysbench fileio --file-total-size=2G prepare
sysbench 1.0.8 (using bundled LuaJIT 2.1.0-beta2)
128 files, 16384Kb each, 2048Mb total
Creating files for the test...
Extra file open flags: 0
Creating file test_file.0
Creating file test_file.1
Creating file test_file.2
Creating file test_file.3
Creating file test_file.4
Creating file test_file.5
Creating file test_file.6
Creating file test_file.7
Creating file test_file.8
Creating file test_file.9
Creating file test_file.10
Creating file test_file.11
Creating file test_file.12
Creating file test_file.13
Creating file test_file.14
Creating file test_file.15
Creating file test_file.16
Creating file test_file.17
Creating file test_file.18
Creating file test_file.19
Creating file test_file.20
Creating file test_file.21
Creating file test_file.22
Creating file test_file.23
Creating file test_file.24
Creating file test_file.25
Creating file test_file.26
Creating file test_file.27
Creating file test_file.28
Creating file test_file.29
Creating file test_file.30
Creating file test_file.31
Creating file test_file.32
Creating file test_file.33
Creating file test_file.34
Creating file test_file.35
Creating file test_file.36
Creating file test_file.37
Creating file test_file.38
Creating file test_file.39
Creating file test_file.40
Creating file test_file.41
Creating file test_file.42
Creating file test_file.43
Creating file test_file.44
Creating file test_file.45
Creating file test_file.46
Creating file test_file.47
Creating file test_file.48
Creating file test_file.49
Creating file test_file.50
Creating file test_file.51
Creating file test_file.52
Creating file test_file.53
Creating file test_file.54
Creating file test_file.55
Creating file test_file.56
Creating file test_file.57
Creating file test_file.58
Creating file test_file.59
Creating file test_file.60
Creating file test_file.61
Creating file test_file.62
Creating file test_file.63
Creating file test_file.64
Creating file test_file.65
Creating file test_file.66
Creating file test_file.67
Creating file test_file.68
Creating file test_file.69
Creating file test_file.70
Creating file test_file.71
Creating file test_file.72
Creating file test_file.73
Creating file test_file.74
Creating file test_file.75
Creating file test_file.76
Creating file test_file.77
Creating file test_file.78
Creating file test_file.79
Creating file test_file.80
Creating file test_file.81
Creating file test_file.82
Creating file test_file.83
Creating file test_file.84
Creating file test_file.85
Creating file test_file.86
Creating file test_file.87
Creating file test_file.88
Creating file test_file.89
Creating file test_file.90
Creating file test_file.91
Creating file test_file.92
Creating file test_file.93
Creating file test_file.94
Creating file test_file.95
Creating file test_file.96
Creating file test_file.97
Creating file test_file.98
Creating file test_file.99
Creating file test_file.100
Creating file test_file.101
Creating file test_file.102
Creating file test_file.103
Creating file test_file.104
Creating file test_file.105
Creating file test_file.106
Creating file test_file.107
Creating file test_file.108
Creating file test_file.109
Creating file test_file.110
Creating file test_file.111
Creating file test_file.112
Creating file test_file.113
Creating file test_file.114
Creating file test_file.115
Creating file test_file.116
Creating file test_file.117
Creating file test_file.118
Creating file test_file.119
Creating file test_file.120
Creating file test_file.121
Creating file test_file.122
Creating file test_file.123
Creating file test_file.124
Creating file test_file.125
Creating file test_file.126
Creating file test_file.127
2147483648 bytes written in 142.26 seconds (14.40 MiB/sec).
[root@localhost /]# sysbench fileio --threads=20 --file-total-size=2G --file-test-mode=rndrw --report-interval=1 run
sysbench 1.0.8 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 20
Report intermediate results every 1 second(s)
Initializing random number generator from current time
Extra file open flags: 0
128 files, 16MiB each
2GiB total file size
Block size 16KiB
Number of IO requests: 0
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random r/w test
Initializing worker threads...
Threads started!
[ 1s ] reads: 10.25 MiB/s writes: 6.83 MiB/s fsyncs: 1265.71/s latency (ms,95%): 55.824
[ 2s ] reads: 7.52 MiB/s writes: 5.02 MiB/s fsyncs: 1117.16/s latency (ms,95%): 74.464
[ 3s ] reads: 7.50 MiB/s writes: 5.00 MiB/s fsyncs: 1037.99/s latency (ms,95%): 77.194
[ 4s ] reads: 7.50 MiB/s writes: 5.00 MiB/s fsyncs: 1004.02/s latency (ms,95%): 82.959
[ 5s ] reads: 8.44 MiB/s writes: 5.63 MiB/s fsyncs: 1092.01/s latency (ms,95%): 68.053
[ 6s ] reads: 7.50 MiB/s writes: 5.00 MiB/s fsyncs: 1077.00/s latency (ms,95%): 77.194
[ 7s ] reads: 7.50 MiB/s writes: 5.00 MiB/s fsyncs: 1051.01/s latency (ms,95%): 74.464
[ 8s ] reads: 8.44 MiB/s writes: 5.62 MiB/s fsyncs: 1049.99/s latency (ms,95%): 74.464
[ 9s ] reads: 7.50 MiB/s writes: 5.00 MiB/s fsyncs: 1088.00/s latency (ms,95%): 73.135
[ 10s ] reads: 7.50 MiB/s writes: 5.00 MiB/s fsyncs: 1048.02/s latency (ms,95%): 69.289
File operations:
reads/s: 505.21
writes/s: 336.81
fsyncs/s: 1075.50
Throughput:
read, MiB/s: 7.89
written, MiB/s: 5.26
General statistics:
total time: 10.0910s
total number of events: 19357
Latency (ms):
min: 0.00
avg: 10.39
max: 459.54
95th percentile: 74.46
sum: 201035.26
Threads fairness:
events (avg/stddev): 967.8500/193.24
execution time (avg/stddev): 10.0518/0.02
[root@localhost ~]# free -h
total used free shared buff/cache available
Mem: 125G 800M 124G 8.7M 396M 124G
Swap: 4.0G 0B 4.0G
[root@localhost /]# sysbench memory run
sysbench 1.0.8 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Running memory speed test with the following options:
block size: 1KiB
total size: 102400MiB
operation: write
scope: global
Initializing worker threads...
Threads started!
Total operations: 33786047 (3377140.21 per second)
32994.19 MiB transferred (3297.99 MiB/sec)
General statistics:
total time: 10.0002s
total number of events: 33786047
Latency (ms):
min: 0.00
avg: 0.00
max: 0.05
95th percentile: 0.00
sum: 4351.38
Threads fairness:
events (avg/stddev): 33786047.0000/0.00
execution time (avg/stddev): 4.3514/0.00
[yeqiang@localhost ~]$ mysqlslap --concurrency=100 --iterations=1 --number-int-cols=5 --number-char-cols=10 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=myisam,innodb --number-of-queries=1000000 --create-schema=sqltest -uroot -h 172.16.1.140 -p
Enter password:
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 48.247 seconds
Minimum number of seconds to run all queries: 48.247 seconds
Maximum number of seconds to run all queries: 48.247 seconds
Number of clients running queries: 100
Average number of queries per client: 10000
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 318.824 seconds
Minimum number of seconds to run all queries: 318.824 seconds
Maximum number of seconds to run all queries: 318.824 seconds
Number of clients running queries: 100
Average number of queries per client: 10000
总结:
MySQL Cluster表现出来的性能低于预期,但是服务器CPU、磁盘IO,网络IO等还有很大余量
在不考虑稳定性及可靠性情况下,2万块的dell r720表现出来的性能对比三千多的台式机没有明显优势,仅仅在两块E5-2609 v2(共8核)上有一定计算优势及多大125G内存系统内存。
固态硬盘对MySQL性能表现有积极影响。
对于一些最求高速读写又不需要数据库事物控制的表,采用myisam引擎可以带来接近一个数量级的读写性能提升。