パフォーマンス - Githubissues

team-tsukuba / isucon6q-test-rerost

0 stars 0 forks source link

パフォーマンス #6

Open rerost opened 7 years ago

rerost commented 7 years ago

Oct 07 14:08:00 ubuntu-xenial bundle[6105]: lib/isuda/web.rb
Oct 07 14:08:00 ubuntu-xenial bundle[6105]:                |  77          salt = 1.upto(20).map { chars.sample }.join('')
Oct 07 14:08:00 ubuntu-xenial bundle[6105]:                |  78          salted_password = encode_with_salt(password: pw, salt: salt)
Oct 07 14:08:00 ubuntu-xenial bundle[6105]:    2.6ms     2 |  79          db.xquery(%|
Oct 07 14:08:00 ubuntu-xenial bundle[6105]:                |  80            INSERT INTO user (name, salt, password, created_at)
Oct 07 14:08:00 ubuntu-xenial bundle[6105]:                |  81            VALUES (?, ?, ?, NOW())
Oct 07 14:08:00 ubuntu-xenial bundle[6105]:               .......
Oct 07 14:08:00 ubuntu-xenial bundle[6105]:                |  182      end
Oct 07 14:08:00 ubuntu-xenial bundle[6105]:                |  183
Oct 07 14:08:00 ubuntu-xenial bundle[6105]:    3.0ms     1 |  184      post '/register' do
Oct 07 14:08:00 ubuntu-xenial bundle[6105]:                |  185        name = params[:name] || ''
Oct 07 14:08:00 ubuntu-xenial bundle[6105]:                |  186        pw   = params[:password] || ''
Oct 07 14:08:00 ubuntu-xenial bundle[6105]:                |  187        halt(400) if (name == '') || (pw == '')
Oct 07 14:08:00 ubuntu-xenial bundle[6105]:                |  188
Oct 07 14:08:00 ubuntu-xenial bundle[6105]:    2.7ms     1 |  189        user_id = register(name, pw)
Oct 07 14:08:00 ubuntu-xenial bundle[6105]:                |  190        session[:user_id] = user_id
Oct 07 14:08:00 ubuntu-xenial bundle[6105]:                |  191

これだとSQLのinsertが遅い -> redis を使うか、innoDBあたりのキャッシュを増やせば良さそう・・・？

Oct 07 17:30:48 ubuntu-xenial bundle[6979]: lib/isuda/web.rb
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  41          user_id = session[:user_id]
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  42          if user_id
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:    1.4ms     4 |  43            user = db.xquery(%| select name from user where id = ? |, user_id).first
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  44            @user_id = user_id
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  45            @user_name = user[:name]
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:               .......
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  50
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  51      set(:authenticate) do |value|
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:   12.1ms     1 |  52        condition {
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  53          halt(403) unless @user_id
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  54        }
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:               .......
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  61              _, _, attrs_part = settings.dsn.split(':', 3)
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  62              attrs = Hash[attrs_part.split(';').map {|part| part.split('=', 2) }]
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:    2.2ms     2 |  63              mysql = Mysql2::Client.new(
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  64                username: settings.db_user,
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  65                password: settings.db_password,
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:               .......
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  89
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  90        def is_spam_content(content)
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:    0.7ms     4 |  91          isupam_uri = URI(settings.isupam_origin)
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:   11.1ms     2 |  92          res = Net::HTTP.post_form(isupam_uri, 'content' => content)
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  93          validation = JSON.parse(res.body)
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  94          validation['valid']
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:               .......
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  216      end
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  217
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:    6.1ms     1 |  218      post '/keyword', set_name: true, authenticate: true do
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  219        keyword = params[:keyword] || ''
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  220        halt(400) if keyword == ''
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:               .......
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  223
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  224        bound = [@user_id, keyword, description] * 2
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:    2.2ms     2 |  225        db.xquery(%|
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  226          INSERT INTO entry (author_id, keyword, description, created_at, updated_at)
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  227          VALUES (?, ?, ?, NOW(), NOW())
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:               .......
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  230        |, *bound)
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  231
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:    0.2ms     1 |  232        redirect_found '/'
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  233      end
Oct 07 17:30:48 ubuntu-xenial bundle[6979]:                |  234

Oct 07 17:47:40 ubuntu-xenial bundle[6983]: [Rack::Lineprof] ===============================================================
Oct 07 17:47:40 ubuntu-xenial bundle[6983]: lib/isutar/web.rb
Oct 07 17:47:40 ubuntu-xenial bundle[6983]:                |  67        keyword = params[:keyword]
Oct 07 17:47:40 ubuntu-xenial bundle[6983]:                |  68
Oct 07 17:47:40 ubuntu-xenial bundle[6983]:    0.3ms     2 |  69        isuda_keyword_url = URI(settings.isuda_origin)
Oct 07 17:47:40 ubuntu-xenial bundle[6983]:                |  70        isuda_keyword_url.path = '/keyword/%s' % [Rack::Utils.escape_path(keyword)]
Oct 07 17:47:40 ubuntu-xenial bundle[6983]:  270.0ms     1 |  71        res = Net::HTTP.get_response(isuda_keyword_url)
Oct 07 17:47:40 ubuntu-xenial bundle[6983]:                |  72        halt(404) unless Net::HTTPSuccess === res
Oct 07 17:47:40 ubuntu-xenial bundle[6983]:                |  73
Oct 07 17:47:40 ubuntu-xenial bundle[6983]:                |  74        user_name = params[:user]
Oct 07 17:47:40 ubuntu-xenial bundle[6983]:    1.3ms     2 |  75        db.xquery(%|
Oct 07 17:47:40 ubuntu-xenial bundle[6983]:                |  76          INSERT INTO star (keyword, user_name, created_at)
Oct 07 17:47:40 ubuntu-xenial bundle[6983]:                |  77          VALUES (?, ?, NOW())

高速化できそうな部分

[ ] login
- [ ] login を redisで行う
- [ ] redisへの書き込み
- [ ] mysql への同期（再起動時にデータを保持している必要があるので）
[ ] keyword へのpost
- [ ] conditionが遅いのはなぜ・・・？
- [ ] URIが遅いので事前にしておく
- [ ] 挿入が遅い
[x] stars へのpost
- [x] keywordへ話しかけることは必要か
- [x] keyword の存在確認
- [x] isutar の使うDBを isudaへ
- [x] isutar を完全にisudaにまとめる

rerost commented 7 years ago

isucon@ubuntu-xenial:~$ ./alp_linux -f nginx_access.log --sum -r | head -20
+-------+--------+--------+----------+--------+--------+--------+--------+--------+------------+------------+--------------+------------+--------+------------------------------------------------------------+
| COUNT |  MIN   |  MAX   |   SUM    |  AVG   |   P1   |  P50   |  P99   | STDDEV | MIN(BODY)  | MAX(BODY)  |  SUM(BODY)   | AVG(BODY)  | METHOD |                            URI                             |
+-------+--------+--------+----------+--------+--------+--------+--------+--------+------------+------------+--------------+------------+--------+------------------------------------------------------------+
|   121 |  2.116 | 15.041 | 1020.031 |  8.430 |  0.000 |  8.251 | 15.010 |  3.879 |   7446.000 | 252100.000 |  5282842.000 |  43659.851 | GET    | /                                                          |
|   174 |  0.004 |  3.195 |  307.038 |  1.765 |  0.000 |  2.125 |  3.022 |  1.285 |      0.000 |    182.000 |     1274.000 |      7.322 | POST   | /login                                                     |
|    63 |  0.009 |  3.058 |  121.194 |  1.924 |  0.009 |  1.997 |  3.054 |  1.092 |      0.000 |      0.000 |        0.000 |      0.000 | POST   | /keyword                                                   |
|    55 |  0.005 |  3.021 |   74.377 |  1.352 |  0.005 |  0.756 |  3.009 |  1.135 |      0.000 |     15.000 |      600.000 |     10.909 | POST   | /stars                                                     |
|     7 |  2.139 |  7.482 |   31.635 |  4.519 |  2.139 |  2.798 |  6.683 |  2.093 |   4404.000 |   4696.000 |    31749.000 |   4535.571 | GET    | /keyword/南蟹谷村                                          |
|     3 |  0.819 | 15.008 |   22.351 |  7.450 |  0.819 |  0.819 |  6.524 |  5.830 |  12085.000 |  12214.000 |    24299.000 |   8099.667 | GET    | /keyword/串本駅                                            |
|     3 |  0.658 | 13.707 |   19.131 |  6.377 |  0.658 |  0.658 |  4.766 |  5.448 |   9058.000 |   9357.000 |    27507.000 |   9169.000 | GET    | /keyword/安藤英男                                          |
|     2 |  4.046 | 15.007 |   19.053 |  9.527 |  4.046 |  4.046 |  4.046 |  5.481 |      0.000 |   3255.000 |     3255.000 |   1627.500 | GET    | /keyword/観音橋                                            |
|     2 |  3.060 | 15.007 |   18.067 |  9.034 |  3.060 |  3.060 |  3.060 |  5.973 |      0.000 |   5637.000 |     5637.000 |   2818.500 | GET    | /keyword/枇杷島橋                                          |
|     2 |  2.877 | 15.008 |   17.885 |  8.942 |  2.877 |  2.877 |  2.877 |  6.066 |      0.000 |   5400.000 |     5400.000 |   2700.000 | GET    | /keyword/G15                                               |
|     1 | 15.013 | 15.013 |   15.013 | 15.013 | 15.013 | 15.013 | 15.013 |  0.000 |      0.000 |      0.000 |        0.000 |      0.000 | GET    | /keyword/竹下潤                                            |
|     1 | 15.012 | 15.012 |   15.012 | 15.012 | 15.012 | 15.012 | 15.012 |  0.000 |      0.000 |      0.000 |        0.000 |      0.000 | GET    | /keyword/オリオンツアー                                    |
|     2 |  4.997 |  8.615 |   13.612 |  6.806 |  4.997 |  4.997 |  4.997 |  1.809 |   3512.000 |   3513.000 |     7025.000 |   3512.500 | GET    | /keyword/ウーズ                                            |
|     4 |  1.928 |  4.742 |   13.387 |  3.347 |  1.928 |  2.335 |  4.382 |  1.230 |   3819.000 |   3945.000 |    15470.000 |   3867.500 | GET    | /keyword/トイズ                                            |
|     2 |  4.650 |  8.712 |   13.362 |  6.681 |  4.650 |  4.650 |  4.650 |  2.031 |   9216.000 |   9217.000 |    18433.000 |   9216.500 | GET    | /keyword/イギリス政府                                      |
|     1 | 13.008 | 13.008 |   13.008 | 13.008 | 13.008 | 13.008 | 13.008 |  0.000 |   4398.000 |   4398.000 |     4398.000 |   4398.000 | GET    | /keyword/京都府道129号花園停車場大将軍線                   |
|     8 |  0.002 |  6.022 |   12.896 |  1.612 |  0.002 |  0.929 |  2.791 |  1.911 |      0.000 |      0.000 |        0.000 |      0.000 | GET    | /logout                                                    |

POST: [login, keyword, stars] を最適化

rerost commented 7 years ago

isuda と isutar を統合した

isucon@ubuntu-xenial:~$ cat nginx_access.log | ./alp_linux --sum -r | head -20
+-------+-------+--------+---------+-------+-------+-------+--------+--------+------------+------------+--------------+------------+--------+-------------------------------------------+
| COUNT |  MIN  |  MAX   |   SUM   |  AVG  |  P1   |  P50  |  P99   | STDDEV | MIN(BODY)  | MAX(BODY)  |  SUM(BODY)   | AVG(BODY)  | METHOD |                    URI                    |
+-------+-------+--------+---------+-------+-------+-------+--------+--------+------------+------------+--------------+------------+--------+-------------------------------------------+
|    41 | 4.010 | 15.001 | 406.723 | 9.920 | 4.010 | 9.414 | 15.000 |  2.702 |  37695.000 |  65229.000 |  1980894.000 |  48314.488 | GET    | /                                         |
|    59 | 0.003 |  3.021 | 108.741 | 1.843 | 0.003 | 2.188 |  3.010 |  1.227 |      0.000 |      0.000 |        0.000 |      0.000 | POST   | /login                                    |
|    19 | 0.129 |  3.003 |  27.438 | 1.444 | 0.129 | 1.169 |  3.002 |  0.885 |      0.000 |      0.000 |        0.000 |      0.000 | POST   | /keyword                                  |
|    21 | 0.003 |  3.007 |  21.924 | 1.044 | 0.003 | 0.021 |  3.001 |  1.219 |      0.000 |     15.000 |      255.000 |     12.143 | POST   | /stars                                    |
|     2 | 1.950 |  8.502 |  10.452 | 5.226 | 1.950 | 1.950 |  1.950 |  3.276 |   7442.000 |   7515.000 |    14957.000 |   7478.500 | GET    | /keyword/淡路五色ケーブルテレビ           |
|     3 | 0.003 |  6.662 |   9.627 | 3.209 | 0.003 | 0.003 |  2.962 |  2.724 |      0.000 |      0.000 |        0.000 |      0.000 | GET    | /logout                                   |
|     2 | 3.782 |  5.039 |   8.821 | 4.410 | 3.782 | 3.782 |  3.782 |  0.628 |   3844.000 |   3844.000 |     7688.000 |   3844.000 | GET    | /keyword/トイズ                           |
|     1 | 8.641 |  8.641 |   8.641 | 8.641 | 8.641 | 8.641 |  8.641 |  0.000 |   9309.000 |   9309.000 |     9309.000 |   9309.000 | GET    | /keyword/古平町                           |
|     2 | 3.914 |  4.399 |   8.313 | 4.157 | 3.914 | 3.914 |  3.914 |  0.242 |  10527.000 |  10527.000 |    21054.000 |  10527.000 | GET    | /keyword/菅山かおる                       |
|     2 | 3.829 |  4.481 |   8.310 | 4.155 | 3.829 | 3.829 |  3.829 |  0.326 |   4517.000 |   4517.000 |     9034.000 |   4517.000 | GET    | /keyword/南蟹谷村                         |
|     1 | 8.237 |  8.237 |   8.237 | 8.237 | 8.237 | 8.237 |  8.237 |  0.000 |  13750.000 |  13750.000 |    13750.000 |  13750.000 | GET    | /keyword/不法投棄                         |
|     1 | 7.935 |  7.935 |   7.935 | 7.935 | 7.935 | 7.935 |  7.935 |  0.000 |   6633.000 |   6633.000 |     6633.000 |   6633.000 | GET    | /keyword/選抜高等学校野球大会入場行進曲   |
|     2 | 3.574 |  4.234 |   7.808 | 3.904 | 3.574 | 3.574 |  3.574 |  0.330 |   7719.000 |   7719.000 |    15438.000 |   7719.000 | GET    | /keyword/船戸山                           |
|     1 | 7.695 |  7.695 |   7.695 | 7.695 | 7.695 | 7.695 |  7.695 |  0.000 |   7252.000 |   7252.000 |     7252.000 |   7252.000 | GET    | /keyword/大沼綾子                         |
|     1 | 7.547 |  7.547 |   7.547 | 7.547 | 7.547 | 7.547 |  7.547 |  0.000 |   4879.000 |   4879.000 |     4879.000 |   4879.000 | GET    | /keyword/1058年                           |
|     1 | 7.513 |  7.513 |   7.513 | 7.513 | 7.513 | 7.513 |  7.513 |  0.000 |   4024.000 |   4024.000 |     4024.000 |   4024.000 | GET    | /keyword/315年                            |
|     1 | 6.978 |  6.978 |   6.978 | 6.978 | 6.978 | 6.978 |  6.978 |  0.000 |   4223.000 |   4223.000 |     4223.000 |   4223.000 | GET    | /keyword/三頭山                           |

rerost commented 7 years ago

スコアが変化しなかったので、topページの最適化

rerost commented 7 years ago

top ページのhtmplifyを最適化する

[x] 取得するカラムの最小化
[x] 文字数
[ ] Regexpを事前に計算してしまう

rerost commented 7 years ago

取得カラムの最小化と文字数を入れるようのカラムを作った


isucon@ubuntu-xenial:~$ cat nginx_access.log | ./alp_linux --sum -r | head -20
+-------+-------+-------+---------+-------+-------+-------+-------+--------+------------+------------+--------------+------------+--------+----------------------------------------------------------------------------+
| COUNT |  MIN  |  MAX  |   SUM   |  AVG  |  P1   |  P50  |  P99  | STDDEV | MIN(BODY)  | MAX(BODY)  |  SUM(BODY)   | AVG(BODY)  | METHOD |                                    URI                                     |
+-------+-------+-------+---------+-------+-------+-------+-------+--------+------------+------------+--------------+------------+--------+----------------------------------------------------------------------------+
|    96 | 1.010 | 6.536 | 381.209 | 3.971 | 1.010 | 3.830 | 6.220 |  1.200 |  35805.000 | 129389.000 |  5905358.000 |  61514.146 | GET    | /                                                                          |
|    60 | 0.004 | 3.006 |  67.764 | 1.129 | 0.004 | 1.115 | 3.000 |  0.778 |      0.000 |     15.000 |      795.000 |     13.250 | POST   | /stars                                                                     |
|    68 | 0.002 | 3.009 |  64.692 | 0.951 | 0.002 | 0.738 | 3.001 |  0.835 |      0.000 |      0.000 |        0.000 |      0.000 | POST   | /login                                                                     |
|    40 | 0.215 | 3.065 |  55.356 | 1.384 | 0.215 | 1.215 | 3.001 |  0.750 |      0.000 |      0.000 |        0.000 |      0.000 | POST   | /keyword                                                                   |
|     2 | 1.907 | 4.282 |   6.189 | 3.095 | 1.907 | 1.907 | 1.907 |  1.188 |  14111.000 |  14114.000 |    28225.000 |  14112.500 | GET    | /keyword/Sugar (韓国の音楽グループ)                                        |
|     2 | 2.377 | 3.510 |   5.887 | 2.943 | 2.377 | 2.377 | 2.377 |  0.567 |   8897.000 |   9044.000 |    17941.000 |   8970.500 | GET    | /keyword/イギリス政府                                                      |
|     2 | 1.960 | 3.234 |   5.194 | 2.597 | 1.960 | 1.960 | 1.960 |  0.637 |   3841.000 |   3842.000 |     7683.000 |   3841.500 | GET    | /keyword/トイズ                                                            |
|     2 | 1.858 | 2.803 |   4.661 | 2.330 | 1.858 | 1.858 | 1.858 |  0.472 |   3959.000 |   3959.000 |     7918.000 |   3959.000 | GET    | /keyword/八田小学校                                                        |
|     6 | 0.002 | 1.943 |   4.623 | 0.770 | 0.002 | 0.461 | 1.507 |  0.730 |      0.000 |      0.000 |        0.000 |      0.000 | GET    | /logout                                                                    |
|     2 | 1.192 | 3.259 |   4.451 | 2.225 | 1.192 | 1.192 | 1.192 |  1.034 |   8425.000 |   8595.000 |    17020.000 |   8510.000 | GET    | /keyword/内田修平                                                          |
|     2 | 1.551 | 2.759 |   4.310 | 2.155 | 1.551 | 1.551 | 1.551 |  0.604 |  19997.000 |  20000.000 |    39997.000 |  19998.500 | GET    | /keyword/野辺地駅                                                          |
|     1 | 4.256 | 4.256 |   4.256 | 4.256 | 4.256 | 4.256 | 4.256 |  0.000 |  63085.000 |  63085.000 |    63085.000 |  63085.000 | GET    | /keyword/エアバスA310                                                      |
|     2 | 1.610 | 2.495 |   4.105 | 2.053 | 1.610 | 1.610 | 1.610 |  0.443 |   9319.000 |   9321.000 |    18640.000 |   9320.000 | GET    | /keyword/北消防署                                                          |
|     1 | 4.094 | 4.094 |   4.094 | 4.094 | 4.094 | 4.094 | 4.094 |  0.000 |  20464.000 |  20464.000 |    20464.000 |  20464.000 | GET    | /keyword/ISO 8601                                                          |
|     2 | 1.870 | 2.217 |   4.087 | 2.043 | 1.870 | 1.870 | 1.870 |  0.173 |  35087.000 |  35087.000 |    70174.000 |  35087.000 | GET    | /keyword/予土線                                                            |
|     2 | 0.930 | 3.149 |   4.079 | 2.039 | 0.930 | 0.930 | 0.930 |  1.109 |   3304.000 |   3409.000 |     6713.000 |   3356.500 | GET    | /keyword/ウーズ                                                            |
|     2 | 0.898 | 3.045 |   3.943 | 1.972 | 0.898 | 0.898 | 0.898 |  1.073 |   5425.000 |   5551.000 |    10976.000 |   5488.000 | GET    | /keyword/山本幸一                         
```                                 |

rerost commented 7 years ago

DB にインデックスを貼る
- entry の keyword に hash
- entry の keyword_length に b-tree
- star の keyword に hash
- star の keyword -> user_name の複合インデックス

を貼る

InnoDBを使う場合b-tree しか使えなかった・・・

rerost commented 7 years ago

インデックスを貼って、load_star でuser_nameだけを取るようにした。

+-------+-------+--------+---------+-------+-------+-------+-------+--------+------------+------------+--------------+------------+--------+-----------------------------------------------+
| COUNT |  MIN  |  MAX   |   SUM   |  AVG  |  P1   |  P50  |  P99  | STDDEV | MIN(BODY)  | MAX(BODY)  |  SUM(BODY)   | AVG(BODY)  | METHOD |                      URI                      |
+-------+-------+--------+---------+-------+-------+-------+-------+--------+------------+------------+--------------+------------+--------+-----------------------------------------------+
|    93 | 1.076 | 10.214 | 358.654 | 3.856 | 1.076 | 3.760 | 8.020 |  1.426 |   7377.000 | 132549.000 |  4250464.000 |  45703.914 | GET    | /                                             |
|    71 | 0.004 |  3.001 |  79.570 | 1.121 | 0.004 | 1.111 | 3.000 |  0.861 |      0.000 |      0.000 |        0.000 |      0.000 | POST   | /login                                        |
|    75 | 0.003 |  2.721 |  65.338 | 0.871 | 0.003 | 0.558 | 2.647 |  0.759 |      0.000 |     15.000 |     1050.000 |     14.000 | POST   | /stars                                        |
|    42 | 0.057 |  3.005 |  65.020 | 1.548 | 0.057 | 1.358 | 3.002 |  0.870 |      0.000 |      0.000 |        0.000 |      0.000 | POST   | /keyword                                      |
|     2 | 2.523 |  5.812 |   8.335 | 4.168 | 2.523 | 2.523 | 2.523 |  1.645 |   9671.000 |   9672.000 |    19343.000 |   9671.500 | GET    | /keyword/バーニー・フェデルコ                 |
|     1 | 5.732 |  5.732 |   5.732 | 5.732 | 5.732 | 5.732 | 5.732 |  0.000 |  21534.000 |  21534.000 |    21534.000 |  21534.000 | GET    | /keyword/ひらかた大菊人形                     |
|     1 | 5.351 |  5.351 |   5.351 | 5.351 | 5.351 | 5.351 | 5.351 |  0.000 |   7240.000 |   7240.000 |     7240.000 |   7240.000 | GET    | /keyword/マリアンヌ                           |
|     3 | 1.047 |  1.968 |   4.636 | 1.545 | 1.047 | 1.047 | 1.621 |  0.380 |   9530.000 |   9537.000 |    28602.000 |   9534.000 | GET    | /keyword/梅迫駅                               |
|     6 | 0.003 |  1.449 |   3.778 | 0.630 | 0.003 | 0.541 | 0.968 |  0.511 |      0.000 |      0.000 |        0.000 |      0.000 | GET    | /logout                                       |
|     2 | 1.513 |  2.212 |   3.725 | 1.863 | 1.513 | 1.513 | 1.513 |  0.350 |  10535.000 |  10535.000 |    21070.000 |  10535.000 | GET    | /keyword/菅山かおる                           |
|     1 | 3.606 |  3.606 |   3.606 | 3.606 | 3.606 | 3.606 | 3.606 |  0.000 |  25018.000 |  25018.000 |    25018.000 |  25018.000 | GET    | /keyword/海浜幕張駅                           |
|     2 | 1.634 |  1.866 |   3.500 | 1.750 | 1.634 | 1.634 | 1.634 |  0.116 |   3839.000 |   3840.000 |     7679.000 |   3839.500 | GET    | /keyword/トイズ                               |
|     1 | 3.491 |  3.491 |   3.491 | 3.491 | 3.491 | 3.491 | 3.491 |  0.000 |  26734.000 |  26734.000 |    26734.000 |  26734.000 | GET    | /keyword/世界ボクシング評議会                 |
|     1 | 3.380 |  3.380 |   3.380 | 3.380 | 3.380 | 3.380 | 3.380 |  0.000 |  17108.000 |  17108.000 |    17108.000 |  17108.000 | GET    | /keyword/桶川駅                               |
|     2 | 1.642 |  1.732 |   3.374 | 1.687 | 1.642 | 1.642 | 1.642 |  0.045 |   6024.000 |   6024.000 |    12048.000 |   6024.000 | GET    | /keyword/舞鶴西インターチェンジ               |
|     2 | 0.627 |  2.562 |   3.189 | 1.595 | 0.627 | 0.627 | 0.627 |  0.967 |   6335.000 |   6335.000 |    12670.000 |   6335.000 | GET    | /keyword/空印寺                               |
|     1 | 3.161 |  3.161 |   3.161 | 3.161 | 3.161 | 3.161 | 3.161 |  0.000 |  32718.000 |  32718.000 |    32718.000 |  32718.000 | GET    | /keyword/東村山駅                             |

rerost commented 7 years ago

スコアが上がらなかった。

top すると
  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
14440 isucon    20   0  254520  69312   7588 S  39.7  3.4   0:58.36 bundle
14437 isucon    20   0  253604  68400   7844 R  35.7  3.3   0:56.58 bundle
14434 isucon    20   0  252876  67632   7832 R  34.0  3.3   0:55.68 bundle
14431 isucon    20   0  259168  73380   7512 R  33.7  3.6   0:57.90 bundle
14428 isucon    20   0  255852  70696   7832 R  33.3  3.5   0:54.54 bundle
 1223 mysql     20   0 1554328 305056  17112 S  18.7 14.9   5:03.80 mysqld

unicorn がCPUをほとんど使い切っているので遅いみたい。 unicorn -> pumaを考える

rerost commented 7 years ago

メモリ使い切っているわけではないのでunicornでいく。 isudaを殺し忘れていた・・・

-> 殺したら若干スコア上がった。

試しにproductionで走らせる => 3570・・・

rerost commented 7 years ago

ログイン周りをredisでキャッシュすることにした

isucon@ubuntu-xenial:~$ ./alp_linux -f nginx_access.log --sum -r | head -10
+-------+-------+-------+---------+-------+-------+-------+-------+--------+------------+------------+--------------+------------+--------+-------------------------------------------------------+
| COUNT |  MIN  |  MAX  |   SUM   |  AVG  |  P1   |  P50  |  P99  | STDDEV | MIN(BODY)  | MAX(BODY)  |  SUM(BODY)   | AVG(BODY)  | METHOD |                          URI                          |
+-------+-------+-------+---------+-------+-------+-------+-------+--------+------------+------------+--------------+------------+--------+-------------------------------------------------------+
|   103 | 0.995 | 6.456 | 372.733 | 3.619 | 0.995 | 3.612 | 6.211 |  1.187 |   7379.000 |  88835.000 |  5153627.000 |  50035.214 | GET    | /                                                     |
|    68 | 0.004 | 2.990 |  74.652 | 1.098 | 0.004 | 0.920 | 2.701 |  0.816 |      0.000 |      0.000 |        0.000 |      0.000 | POST   | /login                                                |
|    71 | 0.003 | 3.004 |  63.926 | 0.900 | 0.003 | 0.771 | 3.000 |  0.803 |      0.000 |     15.000 |      945.000 |     13.310 | POST   | /stars                                                |
|    42 | 0.094 | 3.001 |  55.897 | 1.331 | 0.094 | 1.186 | 2.987 |  0.751 |      0.000 |      0.000 |        0.000 |      0.000 | POST   | /keyword                                              |
|     6 | 0.002 | 2.293 |   6.423 | 1.071 | 0.002 | 0.611 | 1.806 |  0.788 |      0.000 |      0.000 |        0.000 |      0.000 | GET    | /logout                                               |
|     2 | 2.538 | 2.803 |   5.341 | 2.670 | 2.538 | 2.538 | 2.538 |  0.133 |   9216.000 |   9216.000 |    18432.000 |   9216.000 | GET    | /keyword/イギリス政府                                 |
|     2 | 1.899 | 3.248 |   5.147 | 2.574 | 1.899 | 1.899 | 1.899 |  0.675 |  13976.000 |  14109.000 |    28085.000 |  14042.500 | GET    | /keyword/Sugar (韓国の音楽グループ)                   |

rerost commented 7 years ago

star をredisに移行

isucon@ubuntu-xenial:~$ ./alp_linux -f nginx_access.log --sum -r | head -20
+-------+-------+-------+---------+-------+-------+-------+-------+--------+------------+------------+--------------+------------+--------+------------------------------------------+
| COUNT |  MIN  |  MAX  |   SUM   |  AVG  |  P1   |  P50  |  P99  | STDDEV | MIN(BODY)  | MAX(BODY)  |  SUM(BODY)   | AVG(BODY)  | METHOD |                   URI                    |
+-------+-------+-------+---------+-------+-------+-------+-------+--------+------------+------------+--------------+------------+--------+------------------------------------------+
|   111 | 1.063 | 6.927 | 369.690 | 3.331 | 1.063 | 3.251 | 5.867 |  1.070 |   7377.000 |  90263.000 |  5952011.000 |  53621.721 | GET    | /                                        |
|    75 | 0.003 | 2.721 |  71.852 | 0.958 | 0.003 | 0.814 | 2.703 |  0.709 |      0.000 |      0.000 |        0.000 |      0.000 | POST   | /login                                   |
|    75 | 0.002 | 2.297 |  68.583 | 0.914 | 0.002 | 0.806 | 2.235 |  0.624 |      0.000 |     15.000 |     1050.000 |     14.000 | POST   | /stars                                   |
|    50 | 0.102 | 3.001 |  66.845 | 1.337 | 0.102 | 1.033 | 3.001 |  0.864 |      0.000 |      0.000 |        0.000 |      0.000 | POST   | /keyword                                 |
|     6 | 0.001 | 1.898 |   5.312 | 0.885 | 0.001 | 0.764 | 1.360 |  0.608 |      0.000 |      0.000 |        0.000 |      0.000 | GET    | /logout                                  |

rerost commented 7 years ago

vagrantを再構築したのと、redisをつかって色々した

isucon@ubuntu-xenial:~$ ./tmp/alp -f nginx_access.log --sum -r | head -20
+-------+--------+--------+---------+--------+--------+--------+--------+--------+------------+------------+--------------+------------+--------+-----------------------------------+
| COUNT |  MIN   |  MAX   |   SUM   |  AVG   |   P1   |  P50   |  P99   | STDDEV | MIN(BODY)  | MAX(BODY)  |  SUM(BODY)   | AVG(BODY)  | METHOD |                URI                |
+-------+--------+--------+---------+--------+--------+--------+--------+--------+------------+------------+--------------+------------+--------+-----------------------------------+
|    34 |  5.856 | 15.004 | 338.535 |  9.957 |  5.856 |  9.609 | 15.002 |  2.747 |      0.000 |  60415.000 |  1289493.000 |  37926.265 | GET    | /                                 |
|    74 |  0.008 |  3.018 | 153.096 |  2.069 |  0.008 |  2.997 |  3.010 |  1.163 |      0.000 |      0.000 |        0.000 |      0.000 | POST   | /login                            |
|    21 |  0.635 |  3.011 |  49.746 |  2.369 |  0.635 |  3.000 |  3.006 |  0.847 |      0.000 |      0.000 |        0.000 |      0.000 | POST   | /keyword                          |
|     2 | 10.118 | 10.838 |  20.956 | 10.478 | 10.118 | 10.118 | 10.118 |  0.360 |  10154.000 |  10207.000 |    20361.000 |  10180.500 | GET    | /keyword/線引小切手               |
|    37 |  0.010 |  3.002 |  15.937 |  0.431 |  0.010 |  0.037 |  3.002 |  0.869 |     15.000 |     15.000 |      495.000 |     13.378 | POST   | /stars                            |

MAXやAVGが大きくなりすぎてる・・・

ホスト側でCPU使いすぎてた・・・

rerost commented 7 years ago

ホストCPUの使用率減らした

isucon@ubuntu-xenial:~$ ./tmp/alp -f nginx_access.log --sum -r | head -20
+-------+-------+--------+---------+-------+-------+-------+--------+--------+------------+------------+--------------+------------+--------+----------------------------------------------+
| COUNT |  MIN  |  MAX   |   SUM   |  AVG  |  P1   |  P50  |  P99   | STDDEV | MIN(BODY)  | MAX(BODY)  |  SUM(BODY)   | AVG(BODY)  | METHOD |                     URI                      |
+-------+-------+--------+---------+-------+-------+-------+--------+--------+------------+------------+--------------+------------+--------+----------------------------------------------+
|    87 | 1.396 | 15.097 | 478.228 | 5.497 | 1.396 | 5.719 | 15.001 |  2.366 |  36049.000 |  68877.000 |  4674025.000 |  53724.425 | GET    | /                                            |
|    69 | 0.057 |  2.305 |  46.706 | 0.677 | 0.057 | 0.587 |  2.012 |  0.428 |      0.000 |      0.000 |        0.000 |      0.000 | POST   | /keyword                                     |
|    93 | 0.003 |  1.746 |  43.115 | 0.464 | 0.003 | 0.400 |  1.575 |  0.367 |      0.000 |      0.000 |        0.000 |      0.000 | POST   | /login                                       |
|    99 | 0.002 |  1.191 |  26.613 | 0.269 | 0.002 | 0.222 |  0.800 |  0.229 |      0.000 |     15.000 |     1350.000 |     13.636 | POST   | /stars                                       |

https://github.com/team-tsukuba/isucon6q-test-rerost/issues/6#issuecomment-334978984 と比べると、リクエストの数が減っている・・・

rerost commented 7 years ago

unicorn -> puma メモリ使用率は減ったがCPU使用率も減った・・・そしてスコアも減った・・・9000 -> 2500

rerost commented 7 years ago

コンテキストスイッチがどれだけの頻度でどれくらい起きてるか知りたい

rerost commented 7 years ago

CPU使用率が完全にボトルネックになってる・・・

rerost commented 7 years ago

-wall

isucon@ubuntu-xenial:~/webapp/ruby$ ~/.local/ruby/bin/bundle exec stackprof tmp/stackprof/stackprof-wall-9783-1507834503.dump
==================================
  Mode: wall(1000)
  Samples: 301 (54.80% miss rate)
  GC: 59 (19.60%)
==================================
     TOTAL    (pct)     SAMPLES    (pct)     FRAME
       278  (92.4%)         182  (60.5%)     Isuda::Web#htmlify
        11   (3.7%)          11   (3.7%)     #<Module:0x005601e8799330>.parse
         3   (1.0%)           3   (1.0%)     Redis::Connection::SocketMixin#read
         3   (1.0%)           3   (1.0%)     IO#write_nonblock
         5   (1.7%)           3   (1.0%)     <top (required)>
         3   (1.0%)           2   (0.7%)     <top (required)>
         3   (1.0%)           2   (0.7%)     Sinatra::Base.middleware
        12   (4.0%)           2   (0.7%)     #<Module:0x005601e8e99ae0>.new
         2   (0.7%)           2   (0.7%)     #<Module:0x005601e82d03a8>.escape_html
         5   (1.7%)           2   (0.7%)     Sinatra::Helpers#uri
         2   (0.7%)           2   (0.7%)     <module:Mime>
         1   (0.3%)           1   (0.3%)     <module:Rack>
         1   (0.3%)           1   (0.3%)     Isuda::Web.session_secret?
         5   (1.7%)           1   (0.3%)     Sinatra::Base.setup_sessions
         1   (0.3%)           1   (0.3%)     <module:Abstract>
         1   (0.3%)           1   (0.3%)     <module:Protection>
         1   (0.3%)           1   (0.3%)     <module:Protection>
         1   (0.3%)           1   (0.3%)     <module:Protection>
         1   (0.3%)           1   (0.3%)     <module:Protection>
         1   (0.3%)           1   (0.3%)     ERB::Compiler#content_dump
         1   (0.3%)           1   (0.3%)     ERB::Compiler::Buffer#push
         1   (0.3%)           1   (0.3%)     Rack::Session::Cookie#generate_hmac
         1   (0.3%)           1   (0.3%)     block in <class:Base>
       442 (146.8%)           1   (0.3%)     Rack::Lineprof#call
         1   (0.3%)           1   (0.3%)     Sinatra::Base.settings
         1   (0.3%)           1   (0.3%)     #<Module:0x005601e911db68>.key_hash_as_symbols
         2   (0.7%)           1   (0.3%)     Mysql2::Client#initialize
         1   (0.3%)           1   (0.3%)     Isuda::Web#redis
         1   (0.3%)           1   (0.3%)     Redis::Connection::SocketMixin#gets
         1   (0.3%)           1   (0.3%)     Rack::Request#scheme

-cpu

isucon@ubuntu-xenial:~/webapp/ruby$ ~/.local/ruby/bin/bundle exec stackprof tmp/stackprof/stackprof-cpu-9993-1507834731.dump
==================================
  Mode: cpu(1000)
  Samples: 5 (0.00% miss rate)
  GC: 1 (20.00%)
==================================
     TOTAL    (pct)     SAMPLES    (pct)     FRAME
         1  (20.0%)           1  (20.0%)     <top (required)>
         3  (60.0%)           1  (20.0%)     Rack::Builder#to_app
         2  (40.0%)           1  (20.0%)     #<Module:0x0055fc8255a530>.new
         1  (20.0%)           1  (20.0%)     <module:Multipart>
         1  (20.0%)           0   (0.0%)     Sinatra::Base.setup_sessions
         1  (20.0%)           0   (0.0%)     Sinatra::Base.setup_default_middleware
         1  (20.0%)           0   (0.0%)     Sinatra::Base.build
         3  (60.0%)           0   (0.0%)     Sinatra::Base.new
         3  (60.0%)           0   (0.0%)     Sinatra::Base.prototype
         8 (160.0%)           0   (0.0%)     Sinatra::Base.call
         4  (80.0%)           0   (0.0%)     Sinatra::Base.synchronize
         4  (80.0%)           0   (0.0%)     StackProf::Middleware#call
         4  (80.0%)           0   (0.0%)     Rack::TempfileReaper#call
         4  (80.0%)           0   (0.0%)     Rack::Lint#_call
         4  (80.0%)           0   (0.0%)     Rack::Lint#call
         4  (80.0%)           0   (0.0%)     Rack::ShowExceptions#call
         4  (80.0%)           0   (0.0%)     Rack::CommonLogger#call
         4  (80.0%)           0   (0.0%)     Rack::CommonLogger#call
         4  (80.0%)           0   (0.0%)     Rack::Chunked#call
         4  (80.0%)           0   (0.0%)     Rack::ContentLength#call
         4  (80.0%)           0   (0.0%)     Unicorn::HttpServer#process_client
         4  (80.0%)           0   (0.0%)     Unicorn::HttpServer#worker_loop
         4  (80.0%)           0   (0.0%)     Unicorn::HttpServer#spawn_missing_workers
         4  (80.0%)           0   (0.0%)     Unicorn::HttpServer#start
         4  (80.0%)           0   (0.0%)     <top (required)>
         4  (80.0%)           0   (0.0%)     <top (required)>
         4  (80.0%)           0   (0.0%)     Bundler::CLI::Exec#kernel_load
         4  (80.0%)           0   (0.0%)     Bundler::CLI::Exec#run
         4  (80.0%)           0   (0.0%)     Bundler::CLI#exec
         4  (80.0%)           0   (0.0%)     Bundler::Thor::Command#run

htmlify絶対殺す

rerost commented 7 years ago

htmlifyをキャッシュするようにした（キーワード追加のたびクリア）スコア13458

rerost commented 7 years ago

production で走らせたスコア14968

isucon@ubuntu-xenial:~$ ./tmp/alp -f nginx_access.log --sum -r | head -20
+-------+--------+--------+---------+--------+--------+--------+--------+--------+------------+------------+--------------+------------+--------+-------------------------------------+
| COUNT |  MIN   |  MAX   |   SUM   |  AVG   |   P1   |  P50   |  P99   | STDDEV | MIN(BODY)  | MAX(BODY)  |  SUM(BODY)   | AVG(BODY)  | METHOD |                 URI                 |
+-------+--------+--------+---------+--------+--------+--------+--------+--------+------------+------------+--------------+------------+--------+-------------------------------------+
|   234 |  0.006 | 15.009 | 502.204 |  2.146 |  0.006 |  0.104 | 10.753 |  3.101 |  45384.000 | 649809.000 | 11109320.000 |  47475.726 | GET    | /                                   |
|   309 |  0.008 |  1.091 |  64.427 |  0.209 |  0.016 |  0.122 |  0.851 |  0.217 |      0.000 |      0.000 |        0.000 |      0.000 | POST   | /keyword                            |
|     1 | 11.054 | 11.054 |  11.054 | 11.054 | 11.054 | 11.054 | 11.054 |  0.000 |  62150.000 |  62150.000 |    62150.000 |  62150.000 | GET    | /keyword/エアバスA310               |
|   441 |  0.001 |  3.093 |   8.781 |  0.020 |  0.001 |  0.002 |  0.164 |  0.206 |      0.000 |      0.000 |        0.000 |      0.000 | POST   | /login                              |
|     3 |  0.051 |  3.057 |   6.048 |  2.016 |  0.051 |  0.051 |  2.940 |  1.390 |  18617.000 |  18638.000 |    55893.000 |  18631.000 | GET    | /keyword/唐詩選                     |

わりともう最適化する場所はなさそう・・・？

rerost commented 7 years ago

明日時間がアレばさくらクラウドを使ってスコア計測

rerost commented 7 years ago

さくらのクラウドで計測。ただし、ベンチマーカーは手元で動かしたスコア8321

rerost commented 7 years ago

https://twitter.com/hokaccha/status/777465117000605696?ref_src=twsrc%5Etfw&ref_url=http%3A%2F%2Fhokaccha.hatenablog.com%2Fentry%2F2016%2F09%2F19%2F201800

なるほどなって思った。LIKE検索は遅そうって思って無意識に避けてたあとCPU使用率ばかりに目が行っていたが、alpのPxx あたりを見ようと思った。

スコア21939

rerost commented 7 years ago

initから以外userテーブルへのアクセスをなくしたスコア24879