mysqlへの乗換 #130

8398a7 · 2015-07-03T18:39:12Z

mysqlとpostgresqlで軽いベンチマークを行い，
有用性が高いようならmysqlへ乗り換える．

バージョン
- mysql Ver 14.14 Distrib 5.6.24, for osx10.10 (x86_64) using EditLine wrapper
- postgres (PostgreSQL) 9.4.1
ベンチマーク手法
ApacheBenchmark

ab -k -c 5 -n 30 http://127.0.0.1/abilitysheet

比較対称ページ
モデルのデータ
2015/07/03時点の本番データ
動作環境
nginx+unicornによるproduction環境

8398a7 · 2015-07-03T18:52:21Z

mysql

http://127.0.0.1/abilitysheet/users

Concurrency Level:      5
Time taken for tests:   52.128 seconds
Complete requests:      30
Failed requests:        0
Keep-Alive requests:    30
Total transferred:      2739810 bytes
HTML transferred:       2715720 bytes
Requests per second:    0.58 [#/sec] (mean)
Time per request:       8688.023 [ms] (mean)
Time per request:       1737.605 [ms] (mean, across all concurrent requests)
Transfer rate:          51.33 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.1      0       0
Processing:  3692 8238 2063.5   7541   10899
Waiting:     3692 8237 2063.5   7540   10898
Total:       3692 8238 2063.5   7541   10899

Percentage of the requests served within a certain time (ms)
  50%   7541
  66%   9650
  75%  10085
  80%  10664
  90%  10861
  95%  10878
  98%  10899
  99%  10899
 100%  10899 (longest request)

http://127.0.0.1/abilitysheet/logs/8594-9652/sheet

Concurrency Level:      5
Time taken for tests:   45.368 seconds
Complete requests:      30
Failed requests:        0
Keep-Alive requests:    30
Total transferred:      1411830 bytes
HTML transferred:       1387740 bytes
Requests per second:    0.66 [#/sec] (mean)
Time per request:       7561.345 [ms] (mean)
Time per request:       1512.269 [ms] (mean, across all concurrent requests)
Transfer rate:          30.39 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.1      0       0
Processing:  3775 7222 1976.1   7547   11264
Waiting:     3773 7222 1976.2   7547   11264
Total:       3775 7222 1976.1   7547   11265

Percentage of the requests served within a certain time (ms)
  50%   7547
  66%   7940
  75%   8067
  80%   9016
  90%  10191
  95%  10636
  98%  11265
  99%  11265
 100%  11265 (longest request)

http://127.0.0.1/abilitysheet/logs/8594-9652/graph

Concurrency Level:      5
Time taken for tests:   21.207 seconds
Complete requests:      30
Failed requests:        0
Keep-Alive requests:    30
Total transferred:      362370 bytes
HTML transferred:       338280 bytes
Requests per second:    1.41 [#/sec] (mean)
Time per request:       3534.491 [ms] (mean)
Time per request:       706.898 [ms] (mean, across all concurrent requests)
Transfer rate:          16.69 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.1      0       0
Processing:  1298 3335 925.4   3152    5020
Waiting:     1298 3334 925.5   3152    5020
Total:       1298 3335 925.4   3152    5020

Percentage of the requests served within a certain time (ms)
  50%   3152
  66%   3982
  75%   4009
  80%   4161
  90%   4500
  95%   4792
  98%   5020
  99%   5020
 100%   5020 (longest request)

8398a7 · 2015-07-03T18:59:26Z

mysql(tuning)

[mysqld]
innodb_buffer_pool_size=768M
innodb_log_file_size=128M
innodb_flush_log_at_trx_commit=2

http://127.0.0.1/abilitysheet/users

Concurrency Level:      5
Time taken for tests:   52.853 seconds
Complete requests:      30
Failed requests:        0
Keep-Alive requests:    30
Total transferred:      2739810 bytes
HTML transferred:       2715720 bytes
Requests per second:    0.57 [#/sec] (mean)
Time per request:       8808.784 [ms] (mean)
Time per request:       1761.757 [ms] (mean, across all concurrent requests)
Transfer rate:          50.62 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.1      0       0
Processing:  3781 8362 2105.4   7519   11034
Waiting:     3780 8362 2105.4   7519   11034
Total:       3781 8362 2105.3   7520   11034

Percentage of the requests served within a certain time (ms)
  50%   7520
  66%  10070
  75%  10433
  80%  10544
  90%  10913
  95%  10986
  98%  11034
  99%  11034
 100%  11034 (longest request)

http://127.0.0.1/abilitysheet/logs/8594-9652/sheet

Concurrency Level:      5
Time taken for tests:   46.285 seconds
Complete requests:      30
Failed requests:        0
Keep-Alive requests:    30
Total transferred:      1411830 bytes
HTML transferred:       1387740 bytes
Requests per second:    0.65 [#/sec] (mean)
Time per request:       7714.133 [ms] (mean)
Time per request:       1542.827 [ms] (mean, across all concurrent requests)
Transfer rate:          29.79 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.1      0       0
Processing:  3344 7369 2034.1   7446   11484
Waiting:     3343 7369 2034.2   7445   11484
Total:       3344 7369 2034.1   7446   11484

Percentage of the requests served within a certain time (ms)
  50%   7446
  66%   8306
  75%   8783
  80%   9224
  90%  10168
  95%  10540
  98%  11484
  99%  11484
 100%  11484 (longest request)

http://127.0.0.1/abilitysheet/logs/8594-9652/graph

Concurrency Level:      5
Time taken for tests:   23.864 seconds
Complete requests:      30
Failed requests:        0
Keep-Alive requests:    30
Total transferred:      362370 bytes
HTML transferred:       338280 bytes
Requests per second:    1.26 [#/sec] (mean)
Time per request:       3977.329 [ms] (mean)
Time per request:       795.466 [ms] (mean, across all concurrent requests)
Transfer rate:          14.83 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.1      0       0
Processing:  1247 3806 1091.7   3607    5552
Waiting:     1247 3806 1091.7   3607    5552
Total:       1247 3806 1091.6   3607    5552

Percentage of the requests served within a certain time (ms)
  50%   3607
  66%   4288
  75%   4725
  80%   5009
  90%   5198
  95%   5246
  98%   5552
  99%   5552
 100%   5552 (longest request)

8398a7 · 2015-07-03T19:47:44Z

postgresql

http://127.0.0.1/abilitysheet/users

Concurrency Level:      5
Time taken for tests:   38.238 seconds
Complete requests:      30
Failed requests:        1
   (Connect: 0, Receive: 0, Length: 1, Exceptions: 0)
Keep-Alive requests:    29
Total transferred:      2736653 bytes
HTML transferred:       2712591 bytes
Requests per second:    0.78 [#/sec] (mean)
Time per request:       6373.069 [ms] (mean)
Time per request:       1274.614 [ms] (mean, across all concurrent requests)
Transfer rate:          69.89 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.1      0       0
Processing:  2412 6005 1487.7   5980    8233
Waiting:     2412 6004 1487.9   5980    8232
Total:       2412 6005 1487.6   5980    8233

Percentage of the requests served within a certain time (ms)
  50%   5980
  66%   6818
  75%   7313
  80%   7504
  90%   8006
  95%   8199
  98%   8233
  99%   8233
 100%   8233 (longest request)

http://127.0.0.1/abilitysheet/logs/8594-9652/sheet

Concurrency Level:      5
Time taken for tests:   46.860 seconds
Complete requests:      30
Failed requests:        0
Keep-Alive requests:    30
Total transferred:      1411830 bytes
HTML transferred:       1387740 bytes
Requests per second:    0.64 [#/sec] (mean)
Time per request:       7810.070 [ms] (mean)
Time per request:       1562.014 [ms] (mean, across all concurrent requests)
Transfer rate:          29.42 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.1      0       0
Processing:  2951 7467 2539.4   7626   13587
Waiting:     2951 7467 2539.3   7626   13586
Total:       2951 7467 2539.4   7626   13587

Percentage of the requests served within a certain time (ms)
  50%   7626
  66%   7865
  75%   9225
  80%   9483
  90%  11499
  95%  12399
  98%  13587
  99%  13587
 100%  13587 (longest request)

http://127.0.0.1/abilitysheet/logs/8594-9652/graph

Concurrency Level:      5
Time taken for tests:   19.436 seconds
Complete requests:      30
Failed requests:        0
Keep-Alive requests:    30
Total transferred:      362370 bytes
HTML transferred:       338280 bytes
Requests per second:    1.54 [#/sec] (mean)
Time per request:       3239.356 [ms] (mean)
Time per request:       647.871 [ms] (mean, across all concurrent requests)
Transfer rate:          18.21 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.0      0       0
Processing:  1184 3039 863.3   2923    4560
Waiting:     1184 3039 863.4   2923    4560
Total:       1184 3039 863.3   2923    4560

Percentage of the requests served within a certain time (ms)
  50%   2923
  66%   3591
  75%   3738
  80%   3940
  90%   4178
  95%   4277
  98%   4560
  99%   4560
 100%   4560 (longest request)

8398a7 · 2015-07-03T19:58:30Z

postgresql(tuning)

pool: 5 => 10

http://127.0.0.1/abilitysheet/users

Concurrency Level:      5
Time taken for tests:   32.221 seconds
Complete requests:      30
Failed requests:        28
   (Connect: 0, Receive: 0, Length: 28, Exceptions: 0)
Keep-Alive requests:    28
Total transferred:      2732986 bytes
HTML transferred:       2708952 bytes
Requests per second:    0.93 [#/sec] (mean)
Time per request:       5370.171 [ms] (mean)
Time per request:       1074.034 [ms] (mean, across all concurrent requests)
Transfer rate:          82.83 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.1      0       0
Processing:  2436 5062 1251.1   5223    7422
Waiting:     2436 5062 1251.1   5222    7421
Total:       2437 5062 1251.1   5223    7422

Percentage of the requests served within a certain time (ms)
  50%   5223
  66%   5908
  75%   5950
  80%   6013
  90%   6922
  95%   6959
  98%   7422
  99%   7422
 100%   7422 (longest request)

http://127.0.0.1/abilitysheet/logs/8594-9652/sheet

Concurrency Level:      5
Time taken for tests:   50.766 seconds
Complete requests:      30
Failed requests:        0
Keep-Alive requests:    30
Total transferred:      1411830 bytes
HTML transferred:       1387740 bytes
Requests per second:    0.59 [#/sec] (mean)
Time per request:       8460.921 [ms] (mean)
Time per request:       1692.184 [ms] (mean, across all concurrent requests)
Transfer rate:          27.16 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.1      0       0
Processing:  2962 8081 2448.5   7878   12240
Waiting:     2962 8081 2448.4   7877   12240
Total:       2963 8081 2448.4   7878   12240

Percentage of the requests served within a certain time (ms)
  50%   7878
  66%   9036
  75%  10152
  80%  10489
  90%  11313
  95%  12038
  98%  12240
  99%  12240
 100%  12240 (longest request)

http://127.0.0.1/abilitysheet/logs/8594-9652/graph

Concurrency Level:      5
Time taken for tests:   17.772 seconds
Complete requests:      30
Failed requests:        8
   (Connect: 0, Receive: 0, Length: 8, Exceptions: 0)
Keep-Alive requests:    30
Total transferred:      362362 bytes
HTML transferred:       338272 bytes
Requests per second:    1.69 [#/sec] (mean)
Time per request:       2962.010 [ms] (mean)
Time per request:       592.402 [ms] (mean, across all concurrent requests)
Transfer rate:          19.91 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.1      0       0
Processing:  1341 2800 751.3   2735    3855
Waiting:     1341 2800 751.3   2734    3855
Total:       1341 2800 751.2   2735    3855

Percentage of the requests served within a certain time (ms)
  50%   2735
  66%   3199
  75%   3525
  80%   3664
  90%   3804
  95%   3811
  98%   3855
  99%   3855
 100%   3855 (longest request)

8398a7 · 2015-07-03T20:09:04Z

結論，何もしてないpostgresqlが良い．
よって，mysql移行は行わない．

8398a7 · 2016-02-10T18:40:55Z

postgresql

ab -k -c 15 -n 100 http://127.0.0.1/users

Server Software:        nginx/1.8.1
Server Hostname:        127.0.0.1
Server Port:            80

Document Path:          /users
Document Length:        78510 bytes

Concurrency Level:      15
Time taken for tests:   8.807 seconds
Complete requests:      100
Failed requests:        6
   (Connect: 0, Receive: 0, Length: 6, Exceptions: 0)
Keep-Alive requests:    100
Total transferred:      7927794 bytes
HTML transferred:       7850994 bytes
Requests per second:    11.35 [#/sec] (mean)
Time per request:       1321.035 [ms] (mean)
Time per request:       88.069 [ms] (mean, across all concurrent requests)
Transfer rate:          879.08 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.2      0       1
Processing:   261 1249 323.7   1282    1991
Waiting:      261 1248 323.5   1282    1991
Total:        262 1249 323.5   1282    1991

Percentage of the requests served within a certain time (ms)
  50%   1282
  66%   1324
  75%   1406
  80%   1489
  90%   1606
  95%   1803
  98%   1969
  99%   1991
 100%   1991 (longest request)

ab -k -c 15 -n 100 http://127.0.0.1/sheets/8594-9652/hard

Server Software:        nginx/1.8.1
Server Hostname:        127.0.0.1
Server Port:            80

Document Path:          /sheets/8594-9652/hard
Document Length:        34704 bytes

Concurrency Level:      15
Time taken for tests:   14.856 seconds
Complete requests:      100
Failed requests:        0
Keep-Alive requests:    100
Total transferred:      3547200 bytes
HTML transferred:       3470400 bytes
Requests per second:    6.73 [#/sec] (mean)
Time per request:       2228.429 [ms] (mean)
Time per request:       148.562 [ms] (mean, across all concurrent requests)
Transfer rate:          233.17 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.2      0       1
Processing:   365 2086 407.1   2189    2450
Waiting:      365 2086 407.2   2187    2450
Total:        365 2086 406.9   2189    2450

Percentage of the requests served within a certain time (ms)
  50%   2189
  66%   2248
  75%   2283
  80%   2300
  90%   2346
  95%   2373
  98%   2422
  99%   2450
 100%   2450 (longest request)

8398a7 · 2016-02-10T18:45:57Z

mysql 5.7

ab -k -c 15 -n 100 http://127.0.0.1/users

mysql用に最適化してないので余計に遅いのはある

Server Software:        nginx/1.8.1
Server Hostname:        127.0.0.1
Server Port:            80

Document Path:          /users
Document Length:        78490 bytes

Concurrency Level:      15
Time taken for tests:   79.770 seconds
Complete requests:      100
Failed requests:        1
   (Connect: 0, Receive: 0, Length: 1, Exceptions: 0)
Keep-Alive requests:    99
Total transferred:      7921929 bytes
HTML transferred:       7845157 bytes
Requests per second:    1.25 [#/sec] (mean)
Time per request:       11965.482 [ms] (mean)
Time per request:       797.699 [ms] (mean, across all concurrent requests)
Transfer rate:          96.98 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.1      0       1
Processing:  1714 11108 2681.4  11333   15840
Waiting:     1713 11107 2681.3  11332   15840
Total:       1714 11108 2681.3  11333   15840

Percentage of the requests served within a certain time (ms)
  50%  11333
  66%  12097
  75%  12412
  80%  12910
  90%  14074
  95%  14952
  98%  15807
  99%  15840
 100%  15840 (longest request)

ab -k -c 15 -n 100 http://127.0.0.1/sheets/8594-9652/hard

Server Software:        nginx/1.8.1
Server Hostname:        127.0.0.1
Server Port:            80

Document Path:          /sheets/8594-9652/hard
Document Length:        34704 bytes

Concurrency Level:      15
Time taken for tests:   20.892 seconds
Complete requests:      100
Failed requests:        0
Keep-Alive requests:    100
Total transferred:      3547200 bytes
HTML transferred:       3470400 bytes
Requests per second:    4.79 [#/sec] (mean)
Time per request:       3133.843 [ms] (mean)
Time per request:       208.923 [ms] (mean, across all concurrent requests)
Transfer rate:          165.81 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.2      0       1
Processing:   643 2911 592.9   2990    3865
Waiting:      643 2910 592.8   2990    3864
Total:        644 2911 592.8   2990    3865

Percentage of the requests served within a certain time (ms)
  50%   2990
  66%   3047
  75%   3177
  80%   3291
  90%   3607
  95%   3701
  98%   3769
  99%   3865
 100%   3865 (longest request)

8398a7 added the enhancement label Jul 3, 2015

8398a7 self-assigned this Jul 3, 2015

8398a7 added a commit that referenced this issue Jul 3, 2015

refs #130 create environment for mysql

b76e89e

8398a7 mentioned this issue Jul 3, 2015

close #130 create environment for mysql #131

Closed

8398a7 closed this as completed Jul 3, 2015

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

mysqlへの乗換 #130

mysqlへの乗換 #130

8398a7 commented Jul 3, 2015

8398a7 commented Jul 3, 2015

8398a7 commented Jul 3, 2015

8398a7 commented Jul 3, 2015

8398a7 commented Jul 3, 2015

8398a7 commented Jul 3, 2015

8398a7 commented Feb 10, 2016

8398a7 commented Feb 10, 2016

mysqlへの乗換 #130

mysqlへの乗換 #130

Comments

8398a7 commented Jul 3, 2015

8398a7 commented Jul 3, 2015

mysql

8398a7 commented Jul 3, 2015

mysql(tuning)

8398a7 commented Jul 3, 2015

postgresql

8398a7 commented Jul 3, 2015

postgresql(tuning)

8398a7 commented Jul 3, 2015

8398a7 commented Feb 10, 2016

postgresql

ab -k -c 15 -n 100 http://127.0.0.1/users

ab -k -c 15 -n 100 http://127.0.0.1/sheets/8594-9652/hard

8398a7 commented Feb 10, 2016

mysql 5.7

ab -k -c 15 -n 100 http://127.0.0.1/users

mysql用に最適化してないので余計に遅いのはある

ab -k -c 15 -n 100 http://127.0.0.1/sheets/8594-9652/hard