Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

頑張るぞい٩( 'ω' )و #1

Open
IkezoeMakoto opened this issue Sep 8, 2019 · 25 comments

Comments

@IkezoeMakoto
Copy link
Owner

commented Sep 8, 2019

No description provided.

@cocoeyes02

This comment has been minimized.

Copy link
Collaborator

commented Sep 8, 2019

◢◤◢◤◢◤◢◤◢◤◢◤ 10:00~ ◢◤◢◤◢◤◢◤◢◤◢◤
当日マニュアル読み合わせ

@cocoeyes02

This comment has been minimized.

Copy link
Collaborator

commented Sep 8, 2019

初期バックアップ

@cocoeyes02

This comment has been minimized.

Copy link
Collaborator

commented Sep 8, 2019

アプリケーションを触ってみる、アプリ仕様理解

@cocoeyes02

This comment has been minimized.

Copy link
Collaborator

commented Sep 8, 2019

◢◤◢◤◢◤◢◤◢◤◢◤ 11:00~ ◢◤◢◤◢◤◢◤◢◤◢◤

ミドルウェアとツール準備

@cocoeyes02

This comment has been minimized.

Copy link
Collaborator

commented Sep 8, 2019

アプリケーションコード整理

@cocoeyes02

This comment has been minimized.

Copy link
Collaborator

commented Sep 8, 2019

分析とその他雑多作業

@cocoeyes02

This comment has been minimized.

Copy link
Collaborator

commented Sep 8, 2019

◢◤◢◤◢◤◢◤◢◤◢◤ 12:00~ ◢◤◢◤◢◤◢◤◢◤◢◤
ボトルネックを共有(issue立てるなど)し、どういうアプローチで攻めるか飯食べながら皆で決定

@cocoeyes02

This comment has been minimized.

Copy link
Collaborator

commented Sep 8, 2019

アプリチューニング

@IkezoeMakoto

This comment has been minimized.

Copy link
Owner Author

commented Sep 8, 2019

初期スコア

2,010

@BBCapMegane

This comment has been minimized.

Copy link
Collaborator

commented Sep 8, 2019

webapp/docs 読む

@IkezoeMakoto

This comment has been minimized.

Copy link
Owner Author

commented Sep 8, 2019

インスタンス作成

@BBCapMegane

This comment has been minimized.

Copy link
Collaborator

commented Sep 8, 2019

URL 一覧

@BBCapMegane BBCapMegane referenced this issue Sep 8, 2019
@BBCapMegane

This comment has been minimized.

Copy link
Collaborator

commented Sep 8, 2019

DB レコード調査

@BBCapMegane

This comment has been minimized.

Copy link
Collaborator

commented Sep 8, 2019

コード解析

@IkezoeMakoto

This comment has been minimized.

Copy link
Owner Author

commented Sep 8, 2019

デプロイの準備する

@IkezoeMakoto

This comment has been minimized.

Copy link
Owner Author

commented Sep 8, 2019

分析ツールを外す準備しておく

@BBCapMegane

This comment has been minimized.

Copy link
Collaborator

commented Sep 8, 2019

category テーブルの改善

@IkezoeMakoto

This comment has been minimized.

Copy link
Owner Author

commented Sep 8, 2019

2,620 イスコイン

@IkezoeMakoto

This comment has been minimized.

Copy link
Owner Author

commented Sep 8, 2019

Top 20 Sort By Count
Count    Total      Mean    Stddev    Min  P50.0  P90.0  P95.0  P99.0    Max  2xx  3xx  4xx  5xx  TotalBytes   MinBytes  MeanBytes   MaxBytes  Request
  111  328.892  2.962991  1.753937  0.021  3.286  4.894  5.660  5.826  7.284  104    0    7    0     2107707          0      18988      30213  GET /users/transactions.json HTTP/1.1
   62   18.877  0.304468  0.157548  0.079  0.316  0.502  0.555  0.648  0.648   54    0    8    0        5877         73         94        104  POST /login HTTP/1.1
   60   58.846  0.980767  0.782482  0.000  1.608  1.632  1.637  1.929  1.929   32    0   28    0        2154          0         35         49  POST /buy HTTP/1.1
   60    2.172  0.036200  0.071884  0.001  0.006  0.177  0.240  0.334  0.334   39    0   21    0        2194         13         36        106  POST /sell HTTP/1.1
   54    2.681  0.049648  0.056466  0.001  0.031  0.122  0.190  0.224  0.224   54    0    0    0      159949       2957       2962       2968  GET /settings HTTP/1.1
   44   22.718  0.516318  0.387251  0.000  0.806  0.814  0.816  0.819  0.819   26    0   18    0        1778         29         40         83  POST /ship_done HTTP/1.1
   41   21.280  0.519024  0.388419  0.001  0.810  0.818  0.819  0.833  0.833   29    0   12    0        2165         29         52         61  POST /ship HTTP/1.1
   26   18.619  0.716115  0.256234  0.006  0.807  0.812  0.814  0.814  0.814   26    0    0    0         884         34         34         34  POST /complete HTTP/1.1
   19    2.996  0.157684  0.139749  0.031  0.085  0.419  0.493  0.493  0.493   19    0    0    0      450013      23600      23684      23769  GET /new_items/20.json HTTP/1.1
   18    1.450  0.080556  0.060166  0.026  0.067  0.172  0.262  0.262  0.262   18    0    0    0      424404      23553      23578      23619  GET /new_items/50.json HTTP/1.1
   18    2.117  0.117611  0.106014  0.049  0.093  0.165  0.535  0.535  0.535   18    0    0    0      413792      22954      22988      23037  GET /new_items/60.json HTTP/1.1
   18    1.869  0.103833  0.115879  0.039  0.058  0.260  0.521  0.521  0.521   18    0    0    0      427765      23633      23764      23882  GET /new_items/10.json HTTP/1.1
   18    1.531  0.085056  0.078998  0.027  0.069  0.170  0.379  0.379  0.379   18    0    0    0      423722      23497      23540      23557  GET /new_items/30.json HTTP/1.1
   17    2.987  0.175706  0.139559  0.045  0.122  0.333  0.597  0.597  0.597   17    0    0    0      400389      23409      23552      23719  GET /new_items.json HTTP/1.1
   16    0.963  0.060188  0.069598  0.024  0.035  0.166  0.296  0.296  0.296   16    0    0    0      372443      23252      23277      23300  GET /new_items/1.json HTTP/1.1
   14    0.918  0.065571  0.029157  0.030  0.051  0.111  0.123  0.123  0.123   14    0    0    0      331062      23600      23647      23696  GET /new_items/40.json HTTP/1.1
   13    0.259  0.019923  0.030935  0.004  0.007  0.049  0.119  0.119  0.119   13    0    0    0        1180         90         90         91  POST /bump HTTP/1.1
   11    0.658  0.059818  0.027853  0.028  0.052  0.095  0.123  0.123  0.123   11    0    0    0      261004      23713      23727      23736  GET /new_items/30.json?created_at=1565624826&item_id=49623 HTTP/1.1
   10    0.674  0.067400  0.030017  0.028  0.064  0.127  0.127  0.127  0.127   10    0    0    0      239037      23883      23903      23906  GET /new_items/30.json?created_at=1565622102&item_id=46899 HTTP/1.1
   10    0.643  0.064300  0.031483  0.027  0.057  0.130  0.130  0.130  0.130   10    0    0    0      235718      23558      23571      23581  GET /new_items/30.json?created_at=1565622774&item_id=47566 HTTP/1.1

Top 20 Sort By Total
Count    Total      Mean    Stddev    Min  P50.0  P90.0  P95.0  P99.0    Max  2xx  3xx  4xx  5xx  TotalBytes   MinBytes  MeanBytes   MaxBytes  Request
  111  328.892  2.962991  1.753937  0.021  3.286  4.894  5.660  5.826  7.284  104    0    7    0     2107707          0      18988      30213  GET /users/transactions.json HTTP/1.1
   60   58.846  0.980767  0.782482  0.000  1.608  1.632  1.637  1.929  1.929   32    0   28    0        2154          0         35         49  POST /buy HTTP/1.1
   44   22.718  0.516318  0.387251  0.000  0.806  0.814  0.816  0.819  0.819   26    0   18    0        1778         29         40         83  POST /ship_done HTTP/1.1
   41   21.280  0.519024  0.388419  0.001  0.810  0.818  0.819  0.833  0.833   29    0   12    0        2165         29         52         61  POST /ship HTTP/1.1
   62   18.877  0.304468  0.157548  0.079  0.316  0.502  0.555  0.648  0.648   54    0    8    0        5877         73         94        104  POST /login HTTP/1.1
   26   18.619  0.716115  0.256234  0.006  0.807  0.812  0.814  0.814  0.814   26    0    0    0         884         34         34         34  POST /complete HTTP/1.1
    2    9.923  4.961500  0.068500  4.893  5.030  5.030  5.030  5.030  5.030    2    0    0    0       44014      22007      22007      22007  GET /users/transactions.json?created_at=1565609725&item_id=34521 HTTP/1.1
    2    9.811  4.905500  0.034500  4.871  4.940  4.940  4.940  4.940  4.940    2    0    0    0       44513      22245      22256      22268  GET /users/transactions.json?created_at=1565618245&item_id=43038 HTTP/1.1
    2    9.788  4.894000  0.016000  4.878  4.910  4.910  4.910  4.910  4.910    2    0    0    0       45158      22579      22579      22579  GET /users/transactions.json?created_at=1565621888&item_id=46681 HTTP/1.1
    2    8.229  4.114500  0.052500  4.062  4.167  4.167  4.167  4.167  4.167    2    0    0    0       44684      22342      22342      22342  GET /users/transactions.json?created_at=1565612804&item_id=37598 HTTP/1.1
    2    8.146  4.073000  0.024000  4.049  4.097  4.097  4.097  4.097  4.097    2    0    0    0       43720      21860      21860      21860  GET /users/transactions.json?created_at=1565612870&item_id=37660 HTTP/1.1
    2    8.105  4.052500  0.004500  4.048  4.057  4.057  4.057  4.057  4.057    2    0    0    0       44157      22067      22078      22090  GET /users/transactions.json?created_at=1565617401&item_id=42197 HTTP/1.1
    2    7.717  3.858500  1.044500  2.814  4.903  4.903  4.903  4.903  4.903    1    0    1    0       21958          0      10979      21958  GET /users/transactions.json?created_at=1565586813&item_id=11603 HTTP/1.1
    1    7.067  7.067000  0.000000  7.067  7.067  7.067  7.067  7.067  7.067    1    0    0    0          31         31         31         31  POST /initialize HTTP/1.1
    2    6.667  3.333500  0.087500  3.246  3.421  3.421  3.421  3.421  3.421    2    0    0    0       42936      21468      21468      21468  GET /users/transactions.json?created_at=1565617282&item_id=42073 HTTP/1.1
    2    6.651  3.325500  0.028500  3.297  3.354  3.354  3.354  3.354  3.354    2    0    0    0       43854      21927      21927      21927  GET /users/transactions.json?created_at=1565621764&item_id=46560 HTTP/1.1
    2    6.643  3.321500  0.002500  3.319  3.324  3.324  3.324  3.324  3.324    2    0    0    0       43436      21718      21718      21718  GET /users/transactions.json?created_at=1565621831&item_id=46623 HTTP/1.1
    2    6.620  3.310000  0.052000  3.258  3.362  3.362  3.362  3.362  3.362    2    0    0    0       42808      21404      21404      21404  GET /users/transactions.json?created_at=1565622733&item_id=47523 HTTP/1.1
    2    6.620  3.310000  0.030000  3.280  3.340  3.340  3.340  3.340  3.340    2    0    0    0       44072      22036      22036      22036  GET /users/transactions.json?created_at=1565608821&item_id=33612 HTTP/1.1
    2    6.579  3.289500  0.010500  3.279  3.300  3.300  3.300  3.300  3.300    2    0    0    0       43984      21992      21992      21992  GET /users/transactions.json?created_at=1565617343&item_id=42138 HTTP/1.1

Top 20 Sort By Mean
Count    Total      Mean    Stddev    Min  P50.0  P90.0  P95.0  P99.0    Max  2xx  3xx  4xx  5xx  TotalBytes   MinBytes  MeanBytes   MaxBytes  Request
    1    7.067  7.067000  0.000000  7.067  7.067  7.067  7.067  7.067  7.067    1    0    0    0          31         31         31         31  POST /initialize HTTP/1.1
    2    9.923  4.961500  0.068500  4.893  5.030  5.030  5.030  5.030  5.030    2    0    0    0       44014      22007      22007      22007  GET /users/transactions.json?created_at=1565609725&item_id=34521 HTTP/1.1
    2    9.811  4.905500  0.034500  4.871  4.940  4.940  4.940  4.940  4.940    2    0    0    0       44513      22245      22256      22268  GET /users/transactions.json?created_at=1565618245&item_id=43038 HTTP/1.1
    2    9.788  4.894000  0.016000  4.878  4.910  4.910  4.910  4.910  4.910    2    0    0    0       45158      22579      22579      22579  GET /users/transactions.json?created_at=1565621888&item_id=46681 HTTP/1.1
    2    8.229  4.114500  0.052500  4.062  4.167  4.167  4.167  4.167  4.167    2    0    0    0       44684      22342      22342      22342  GET /users/transactions.json?created_at=1565612804&item_id=37598 HTTP/1.1
    1    4.090  4.090000  0.000000  4.090  4.090  4.090  4.090  4.090  4.090    1    0    0    0       22212      22212      22212      22212  GET /users/transactions.json?created_at=1565593152&item_id=17942 HTTP/1.1
    2    8.146  4.073000  0.024000  4.049  4.097  4.097  4.097  4.097  4.097    2    0    0    0       43720      21860      21860      21860  GET /users/transactions.json?created_at=1565612870&item_id=37660 HTTP/1.1
    2    8.105  4.052500  0.004500  4.048  4.057  4.057  4.057  4.057  4.057    2    0    0    0       44157      22067      22078      22090  GET /users/transactions.json?created_at=1565617401&item_id=42197 HTTP/1.1
    1    4.052  4.052000  0.000000  4.052  4.052  4.052  4.052  4.052  4.052    1    0    0    0       21835      21835      21835      21835  GET /users/transactions.json?created_at=1565587640&item_id=12432 HTTP/1.1
    2    7.717  3.858500  1.044500  2.814  4.903  4.903  4.903  4.903  4.903    1    0    1    0       21958          0      10979      21958  GET /users/transactions.json?created_at=1565586813&item_id=11603 HTTP/1.1
    2    6.667  3.333500  0.087500  3.246  3.421  3.421  3.421  3.421  3.421    2    0    0    0       42936      21468      21468      21468  GET /users/transactions.json?created_at=1565617282&item_id=42073 HTTP/1.1
    2    6.651  3.325500  0.028500  3.297  3.354  3.354  3.354  3.354  3.354    2    0    0    0       43854      21927      21927      21927  GET /users/transactions.json?created_at=1565621764&item_id=46560 HTTP/1.1
    2    6.643  3.321500  0.002500  3.319  3.324  3.324  3.324  3.324  3.324    2    0    0    0       43436      21718      21718      21718  GET /users/transactions.json?created_at=1565621831&item_id=46623 HTTP/1.1
    2    6.620  3.310000  0.052000  3.258  3.362  3.362  3.362  3.362  3.362    2    0    0    0       42808      21404      21404      21404  GET /users/transactions.json?created_at=1565622733&item_id=47523 HTTP/1.1
    2    6.620  3.310000  0.030000  3.280  3.340  3.340  3.340  3.340  3.340    2    0    0    0       44072      22036      22036      22036  GET /users/transactions.json?created_at=1565608821&item_id=33612 HTTP/1.1
    1    3.305  3.305000  0.000000  3.305  3.305  3.305  3.305  3.305  3.305    1    0    0    0       21408      21408      21408      21408  GET /users/transactions.json?created_at=1565592178&item_id=16970 HTTP/1.1
    2    6.579  3.289500  0.010500  3.279  3.300  3.300  3.300  3.300  3.300    2    0    0    0       43984      21992      21992      21992  GET /users/transactions.json?created_at=1565617343&item_id=42138 HTTP/1.1
    2    6.578  3.289000  0.027000  3.262  3.316  3.316  3.316  3.316  3.316    2    0    0    0       42351      21164      21175      21187  GET /users/transactions.json?created_at=1565604398&item_id=29193 HTTP/1.1
    2    6.482  3.241000  0.002000  3.239  3.243  3.243  3.243  3.243  3.243    2    0    0    0       44269      22123      22134      22146  GET /users/transactions.json?created_at=1565605260&item_id=30059 HTTP/1.1
  111  328.892  2.962991  1.753937  0.021  3.286  4.894  5.660  5.826  7.284  104    0    7    0     2107707          0      18988      30213  GET /users/transactions.json HTTP/1.1

Top 20 Sort By Standard Deviation
Count    Total      Mean    Stddev    Min  P50.0  P90.0  P95.0  P99.0    Max  2xx  3xx  4xx  5xx  TotalBytes   MinBytes  MeanBytes   MaxBytes  Request
  111  328.892  2.962991  1.753937  0.021  3.286  4.894  5.660  5.826  7.284  104    0    7    0     2107707          0      18988      30213  GET /users/transactions.json HTTP/1.1
    2    7.717  3.858500  1.044500  2.814  4.903  4.903  4.903  4.903  4.903    1    0    1    0       21958          0      10979      21958  GET /users/transactions.json?created_at=1565586813&item_id=11603 HTTP/1.1
    2    3.351  1.675500  0.796500  0.879  2.472  2.472  2.472  2.472  2.472    1    0    1    0       21199          0      10599      21199  GET /users/transactions.json?created_at=1565601212&item_id=26004 HTTP/1.1
   60   58.846  0.980767  0.782482  0.000  1.608  1.632  1.637  1.929  1.929   32    0   28    0        2154          0         35         49  POST /buy HTTP/1.1
    2    5.478  2.739000  0.510000  2.229  3.249  3.249  3.249  3.249  3.249    1    0    1    0       21702          0      10851      21702  GET /users/transactions.json?created_at=1565604274&item_id=29068 HTTP/1.1
   41   21.280  0.519024  0.388419  0.001  0.810  0.818  0.819  0.833  0.833   29    0   12    0        2165         29         52         61  POST /ship HTTP/1.1
   44   22.718  0.516318  0.387251  0.000  0.806  0.814  0.816  0.819  0.819   26    0   18    0        1778         29         40         83  POST /ship_done HTTP/1.1
    2    4.341  2.170500  0.283500  1.887  2.454  2.454  2.454  2.454  2.454    1    0    1    0       21106          0      10553      21106  GET /users/transactions.json?created_at=1565591850&item_id=16648 HTTP/1.1
   26   18.619  0.716115  0.256234  0.006  0.807  0.812  0.814  0.814  0.814   26    0    0    0         884         34         34         34  POST /complete HTTP/1.1
    2    0.374  0.187000  0.186000  0.001  0.373  0.373  0.373  0.373  0.373    2    0    0    0        7462       3731       3731       3731  GET /items/50007.json HTTP/1.1
   62   18.877  0.304468  0.157548  0.079  0.316  0.502  0.555  0.648  0.648   54    0    8    0        5877         73         94        104  POST /login HTTP/1.1
   19    2.996  0.157684  0.139749  0.031  0.085  0.419  0.493  0.493  0.493   19    0    0    0      450013      23600      23684      23769  GET /new_items/20.json HTTP/1.1
   17    2.987  0.175706  0.139559  0.045  0.122  0.333  0.597  0.597  0.597   17    0    0    0      400389      23409      23552      23719  GET /new_items.json HTTP/1.1
    5    0.650  0.130000  0.127491  0.041  0.088  0.382  0.382  0.382  0.382    5    0    0    0      117434      23473      23486      23496  GET /new_items/30.json?created_at=1565624096&item_id=48889 HTTP/1.1
    2    0.404  0.202000  0.123000  0.079  0.325  0.325  0.325  0.325  0.325    2    0    0    0       46326      23152      23163      23174  GET /users/2968.json HTTP/1.1
   18    1.869  0.103833  0.115879  0.039  0.058  0.260  0.521  0.521  0.521   18    0    0    0      427765      23633      23764      23882  GET /new_items/10.json HTTP/1.1
    8    1.055  0.131875  0.112193  0.020  0.156  0.345  0.345  0.345  0.345    8    0    0    0        4712        589        589        589  GET /users/3355.json HTTP/1.1
   18    2.117  0.117611  0.106014  0.049  0.093  0.165  0.535  0.535  0.535   18    0    0    0      413792      22954      22988      23037  GET /new_items/60.json HTTP/1.1
    5    0.615  0.123000  0.104046  0.045  0.093  0.325  0.325  0.325  0.325    5    0    0    0      118001      23582      23600      23605  GET /new_items/30.json?created_at=1565623761&item_id=48558 HTTP/1.1
    2    0.206  0.103000  0.102000  0.001  0.205  0.205  0.205  0.205  0.205    2    0    0    0        7386       3693       3693       3693  GET /items/50005.json HTTP/1.1

@IkezoeMakoto

This comment has been minimized.

Copy link
Owner Author

commented Sep 8, 2019

Reading mysql slow query log from /tmp/mysql-slow.log
Count: 96  Time=0.21s (20s)  Lock=0.00s (0s)  Rows=49.0 (4704), isucari[isucari]@localhost
  SELECT * FROM `items` WHERE `status` IN ('S','S') AND (`created_at` < 'S'  OR (`created_at` <= 'S' AND `id` < N)) ORDER BY `created_at` DESC, `id` DESC LIMIT N

Count: 64  Time=0.17s (11s)  Lock=0.00s (0s)  Rows=18.8 (1205), isucari[isucari]@localhost
  SELECT * FROM `items` WHERE `seller_id` = N AND `status` IN ('S','S','S') ORDER BY `created_at` DESC, `id` DESC LIMIT N

Count: 48  Time=0.21s (10s)  Lock=0.00s (0s)  Rows=49.0 (2352), isucari[isucari]@localhost
  SELECT * FROM `items` WHERE `status` IN ('S','S') AND category_id IN (N, N, N, N) AND (`created_at` < 'S'  OR (`created_at` <= 'S' AND `id` < N)) ORDER BY `created_at` DESC, `id` DESC LIMIT N

Count: 40  Time=0.21s (8s)  Lock=0.00s (0s)  Rows=49.0 (1960), isucari[isucari]@localhost
  SELECT * FROM `items` WHERE `status` IN ('S','S') AND category_id IN (N, N, N, N, N, N) AND (`created_at` < 'S'  OR (`created_at` <= 'S' AND `id` < N)) ORDER BY `created_at` DESC, `id` DESC LIMIT N

Count: 54  Time=0.14s (7s)  Lock=0.00s (0s)  Rows=49.0 (2646), isucari[isucari]@localhost
  SELECT * FROM `items` WHERE `status` IN ('S','S') AND category_id IN (N, N, N, N, N) AND (`created_at` < 'S'  OR (`created_at` <= 'S' AND `id` < N)) ORDER BY `created_at` DESC, `id` DESC LIMIT N

Count: 29  Time=0.18s (5s)  Lock=0.00s (0s)  Rows=33.4 (968), isucari[isucari]@localhost
  SELECT * FROM `items` WHERE `seller_id` = N AND `status` IN ('S','S','S') AND (`created_at` < 'S'  OR (`created_at` <= 'S' AND `id` < N)) ORDER BY `created_at` DESC, `id` DESC LIMIT N

Count: 20  Time=0.16s (3s)  Lock=0.00s (0s)  Rows=9.1 (182), isucari[isucari]@localhost
  SELECT * FROM `items` WHERE (`seller_id` = N OR `buyer_id` = N) AND `status` IN ('S','S','S','S','S') ORDER BY `created_at` DESC, `id` DESC LIMIT N

Count: 13  Time=0.14s (1s)  Lock=0.00s (0s)  Rows=11.0 (143), isucari[isucari]@localhost
  SELECT * FROM `items` WHERE (`seller_id` = N OR `buyer_id` = N) AND `status` IN ('S','S','S','S','S') AND (`created_at` < 'S'  OR (`created_at` <= 'S' AND `id` < N)) ORDER BY `created_at` DESC, `id` DESC LIMIT N

Count: 6  Time=0.17s (1s)  Lock=0.00s (0s)  Rows=49.0 (294), isucari[isucari]@localhost
  SELECT * FROM `items` WHERE `status` IN ('S','S') AND category_id IN (N, N, N, N, N, N) ORDER BY created_at DESC, id DESC LIMIT N

Count: 5  Time=0.18s (0s)  Lock=0.00s (0s)  Rows=49.0 (245), isucari[isucari]@localhost
  SELECT * FROM `items` WHERE `status` IN ('S','S') ORDER BY `created_at` DESC, `id` DESC LIMIT N

Count: 3  Time=0.21s (0s)  Lock=0.00s (0s)  Rows=49.0 (147), isucari[isucari]@localhost
  SELECT * FROM `items` WHERE `status` IN ('S','S') AND category_id IN (N, N, N, N) ORDER BY created_at DESC, id DESC LIMIT N

Count: 4  Time=0.11s (0s)  Lock=0.00s (0s)  Rows=49.0 (196), isucari[isucari]@localhost
  SELECT * FROM `items` WHERE `status` IN ('S','S') AND category_id IN (N, N, N, N, N) ORDER BY created_at DESC, id DESC LIMIT N
@IkezoeMakoto

This comment has been minimized.

Copy link
Owner Author

commented Sep 8, 2019

nginx のチューニングをする

@IkezoeMakoto

This comment has been minimized.

Copy link
Owner Author

commented Sep 8, 2019

goで静的ファイルを返している部分があるので、そいつをサーバ上に保存、サーバからnginxを使って返すようにしたい

@IkezoeMakoto

This comment has been minimized.

Copy link
Owner Author

commented Sep 8, 2019

複数台構成を考える

@IkezoeMakoto

This comment has been minimized.

Copy link
Owner Author

commented Sep 8, 2019

blowfishが重い

@IkezoeMakoto

This comment has been minimized.

Copy link
Owner Author

commented Sep 8, 2019

root@isucon9-1:/home/isucon/isucari# sudo cat /var/log/nginx/access.log | /tmp/kataribe -f /tmp/kataribe.toml
Top 20 Sort By Count
Count    Total      Mean    Stddev    Min  P50.0  P90.0  P95.0  P99.0    Max  2xx  3xx  4xx  5xx  TotalBytes   MinBytes  MeanBytes   MaxBytes  Request
  125  336.092  2.688736  1.975040  0.032  3.237  4.917  5.043  7.358  7.383  118    0    7    0     2266947          0      18135      28606  GET /users/transactions.json HTTP/1.1
   63   18.466  0.293111  0.159916  0.001  0.290  0.527  0.537  0.728  0.728   54    0    9    0        5926         73         94        107  POST /login HTTP/1.1
   61   57.216  0.937967  0.787357  0.000  1.607  1.618  1.624  1.697  1.697   32    0   29    0        2187          0         35         49  POST /buy HTTP/1.1
   59    1.611  0.027305  0.047275  0.001  0.006  0.120  0.144  0.181  0.181   38    0   21    0        2181         13         36        106  POST /sell HTTP/1.1
   57    4.241  0.074404  0.087180  0.001  0.035  0.200  0.249  0.350  0.350   57    0    0    0      168646       2857       2958       2971  GET /settings HTTP/1.1
   47   25.127  0.534617  0.381227  0.000  0.806  0.813  0.816  0.817  0.817   29    0   18    0        1880         29         40         83  POST /ship_done HTTP/1.1
   45   22.406  0.497911  0.390861  0.000  0.810  0.821  0.825  0.841  0.841   30    0   15    0        2292          0         50         61  POST /ship HTTP/1.1
   27   19.512  0.722667  0.252858  0.006  0.810  0.821  0.825  0.827  0.827   27    0    0    0         918         34         34         34  POST /complete HTTP/1.1
   22    2.638  0.119909  0.098657  0.043  0.074  0.299  0.342  0.392  0.392   22    0    0    0      522735      23618      23760      23928  GET /new_items/20.json HTTP/1.1
   21    2.770  0.131905  0.125857  0.045  0.073  0.358  0.392  0.471  0.471   21    0    0    0      493046      23352      23478      23662  GET /new_items.json HTTP/1.1
   20    2.790  0.139500  0.141149  0.044  0.080  0.424  0.560  0.560  0.560   20    0    0    0      457460      22822      22873      23024  GET /new_items/60.json HTTP/1.1
   18    1.652  0.091778  0.095807  0.029  0.051  0.157  0.455  0.455  0.455   18    0    0    0      423806      23512      23544      23578  GET /new_items/30.json HTTP/1.1
   18    1.457  0.080944  0.063800  0.034  0.055  0.180  0.296  0.296  0.296   18    0    0    0      424356      23517      23575      23621  GET /new_items/50.json HTTP/1.1
   17    1.773  0.104294  0.117128  0.029  0.047  0.248  0.478  0.478  0.478   17    0    0    0      402566      23663      23680      23701  GET /new_items/10.json HTTP/1.1
   15    1.006  0.067067  0.016599  0.045  0.067  0.086  0.099  0.099  0.099   15    0    0    0      355085      23656      23672      23686  GET /new_items/40.json HTTP/1.1
   14    0.940  0.067143  0.070992  0.022  0.039  0.185  0.280  0.280  0.280   14    0    0    0      324808      23187      23200      23210  GET /new_items/1.json?created_at=1565619668&item_id=44460 HTTP/1.1
   14    1.617  0.115500  0.210125  0.024  0.037  0.261  0.843  0.843  0.843   14    0    0    0      326099      23278      23292      23301  GET /new_items/1.json HTTP/1.1
   14    0.857  0.061214  0.065872  0.023  0.034  0.155  0.267  0.267  0.267   14    0    0    0      327091      23349      23363      23372  GET /new_items/1.json?created_at=1565623486&item_id=48284 HTTP/1.1
   14    1.321  0.094357  0.138659  0.022  0.041  0.329  0.513  0.513  0.513   14    0    0    0      327431      23370      23387      23393  GET /new_items/1.json?created_at=1565624524&item_id=49318 HTTP/1.1
   14    0.891  0.063643  0.061653  0.025  0.044  0.147  0.259  0.259  0.259   14    0    0    0      326327      23296      23309      23319  GET /new_items/1.json?created_at=1565622263&item_id=47056 HTTP/1.1

Top 20 Sort By Total
Count    Total      Mean    Stddev    Min  P50.0  P90.0  P95.0  P99.0    Max  2xx  3xx  4xx  5xx  TotalBytes   MinBytes  MeanBytes   MaxBytes  Request
  125  336.092  2.688736  1.975040  0.032  3.237  4.917  5.043  7.358  7.383  118    0    7    0     2266947          0      18135      28606  GET /users/transactions.json HTTP/1.1
   61   57.216  0.937967  0.787357  0.000  1.607  1.618  1.624  1.697  1.697   32    0   29    0        2187          0         35         49  POST /buy HTTP/1.1
   47   25.127  0.534617  0.381227  0.000  0.806  0.813  0.816  0.817  0.817   29    0   18    0        1880         29         40         83  POST /ship_done HTTP/1.1
   45   22.406  0.497911  0.390861  0.000  0.810  0.821  0.825  0.841  0.841   30    0   15    0        2292          0         50         61  POST /ship HTTP/1.1
   27   19.512  0.722667  0.252858  0.006  0.810  0.821  0.825  0.827  0.827   27    0    0    0         918         34         34         34  POST /complete HTTP/1.1
   63   18.466  0.293111  0.159916  0.001  0.290  0.527  0.537  0.728  0.728   54    0    9    0        5926         73         94        107  POST /login HTTP/1.1
    2    9.806  4.903000  0.019000  4.884  4.922  4.922  4.922  4.922  4.922    2    0    0    0       43799      21888      21899      21911  GET /users/transactions.json?created_at=1565617048&item_id=41840 HTTP/1.1
    2    8.257  4.128500  0.016500  4.112  4.145  4.145  4.145  4.145  4.145    2    0    0    0       44340      22170      22170      22170  GET /users/transactions.json?created_at=1565600429&item_id=25219 HTTP/1.1
    2    8.188  4.094000  0.053000  4.041  4.147  4.147  4.147  4.147  4.147    2    0    0    0       43949      21963      21974      21986  GET /users/transactions.json?created_at=1565608525&item_id=33319 HTTP/1.1
    2    8.169  4.084500  0.010500  4.074  4.095  4.095  4.095  4.095  4.095    2    0    0    0       44692      22335      22346      22357  GET /users/transactions.json?created_at=1565618309&item_id=43100 HTTP/1.1
    2    8.162  4.081000  0.005000  4.076  4.086  4.086  4.086  4.086  4.086    2    0    0    0       44550      22275      22275      22275  GET /users/transactions.json?created_at=1565613835&item_id=38631 HTTP/1.1
    2    8.144  4.072000  0.007000  4.065  4.079  4.079  4.079  4.079  4.079    2    0    0    0       45194      22597      22597      22597  GET /users/transactions.json?created_at=1565587812&item_id=12605 HTTP/1.1
    2    8.142  4.071000  0.020000  4.051  4.091  4.091  4.091  4.091  4.091    2    0    0    0       45017      22497      22508      22520  GET /users/transactions.json?created_at=1565609383&item_id=34175 HTTP/1.1
    1    7.034  7.034000  0.000000  7.034  7.034  7.034  7.034  7.034  7.034    1    0    0    0          31         31         31         31  POST /initialize HTTP/1.1
    2    6.612  3.306000  0.069000  3.237  3.375  3.375  3.375  3.375  3.375    2    0    0    0       43159      21579      21579      21580  GET /users/transactions.json?created_at=1565621525&item_id=46321 HTTP/1.1
    1    6.563  6.563000  0.000000  6.563  6.563  6.563  6.563  6.563  6.563    1    0    0    0       22490      22490      22490      22490  GET /users/transactions.json?created_at=1565596353&item_id=21146 HTTP/1.1
    2    6.504  3.252000  0.010000  3.242  3.262  3.262  3.262  3.262  3.262    2    0    0    0       43566      21783      21783      21783  GET /users/transactions.json?created_at=1565621481&item_id=46280 HTTP/1.1
    1    5.751  5.751000  0.000000  5.751  5.751  5.751  5.751  5.751  5.751    1    0    0    0       22487      22487      22487      22487  GET /users/transactions.json?created_at=1565595649&item_id=20448 HTTP/1.1
    2    5.080  2.540000  0.003000  2.537  2.543  2.543  2.543  2.543  2.543    2    0    0    0       43547      21762      21773      21785  GET /users/transactions.json?created_at=1565622082&item_id=46874 HTTP/1.1
    2    4.990  2.495000  0.046000  2.449  2.541  2.541  2.541  2.541  2.541    2    0    0    0       42688      21344      21344      21344  GET /users/transactions.json?created_at=1565596809&item_id=21603 HTTP/1.1

Top 20 Sort By Mean
Count    Total      Mean    Stddev    Min  P50.0  P90.0  P95.0  P99.0    Max  2xx  3xx  4xx  5xx  TotalBytes   MinBytes  MeanBytes   MaxBytes  Request
    1    7.034  7.034000  0.000000  7.034  7.034  7.034  7.034  7.034  7.034    1    0    0    0          31         31         31         31  POST /initialize HTTP/1.1
    1    6.563  6.563000  0.000000  6.563  6.563  6.563  6.563  6.563  6.563    1    0    0    0       22490      22490      22490      22490  GET /users/transactions.json?created_at=1565596353&item_id=21146 HTTP/1.1
    1    5.751  5.751000  0.000000  5.751  5.751  5.751  5.751  5.751  5.751    1    0    0    0       22487      22487      22487      22487  GET /users/transactions.json?created_at=1565595649&item_id=20448 HTTP/1.1
    2    9.806  4.903000  0.019000  4.884  4.922  4.922  4.922  4.922  4.922    2    0    0    0       43799      21888      21899      21911  GET /users/transactions.json?created_at=1565617048&item_id=41840 HTTP/1.1
    1    4.878  4.878000  0.000000  4.878  4.878  4.878  4.878  4.878  4.878    1    0    0    0       22536      22536      22536      22536  GET /users/transactions.json?created_at=1565591024&item_id=15822 HTTP/1.1
    1    4.183  4.183000  0.000000  4.183  4.183  4.183  4.183  4.183  4.183    1    0    0    0       22275      22275      22275      22275  GET /users/transactions.json?created_at=1565587092&item_id=11885 HTTP/1.1
    1    4.131  4.131000  0.000000  4.131  4.131  4.131  4.131  4.131  4.131    1    0    0    0       21622      21622      21622      21622  GET /users/transactions.json?created_at=1565591625&item_id=16417 HTTP/1.1
    2    8.257  4.128500  0.016500  4.112  4.145  4.145  4.145  4.145  4.145    2    0    0    0       44340      22170      22170      22170  GET /users/transactions.json?created_at=1565600429&item_id=25219 HTTP/1.1
    1    4.116  4.116000  0.000000  4.116  4.116  4.116  4.116  4.116  4.116    1    0    0    0       21812      21812      21812      21812  GET /users/transactions.json?created_at=1565587409&item_id=12200 HTTP/1.1
    2    8.188  4.094000  0.053000  4.041  4.147  4.147  4.147  4.147  4.147    2    0    0    0       43949      21963      21974      21986  GET /users/transactions.json?created_at=1565608525&item_id=33319 HTTP/1.1
    2    8.169  4.084500  0.010500  4.074  4.095  4.095  4.095  4.095  4.095    2    0    0    0       44692      22335      22346      22357  GET /users/transactions.json?created_at=1565618309&item_id=43100 HTTP/1.1
    2    8.162  4.081000  0.005000  4.076  4.086  4.086  4.086  4.086  4.086    2    0    0    0       44550      22275      22275      22275  GET /users/transactions.json?created_at=1565613835&item_id=38631 HTTP/1.1
    2    8.144  4.072000  0.007000  4.065  4.079  4.079  4.079  4.079  4.079    2    0    0    0       45194      22597      22597      22597  GET /users/transactions.json?created_at=1565587812&item_id=12605 HTTP/1.1
    2    8.142  4.071000  0.020000  4.051  4.091  4.091  4.091  4.091  4.091    2    0    0    0       45017      22497      22508      22520  GET /users/transactions.json?created_at=1565609383&item_id=34175 HTTP/1.1
    1    3.384  3.384000  0.000000  3.384  3.384  3.384  3.384  3.384  3.384    1    0    0    0       21705      21705      21705      21705  GET /users/transactions.json?created_at=1565595519&item_id=20316 HTTP/1.1
    2    6.612  3.306000  0.069000  3.237  3.375  3.375  3.375  3.375  3.375    2    0    0    0       43159      21579      21579      21580  GET /users/transactions.json?created_at=1565621525&item_id=46321 HTTP/1.1
    1    3.274  3.274000  0.000000  3.274  3.274  3.274  3.274  3.274  3.274    1    0    0    0       21429      21429      21429      21429  GET /users/transactions.json?created_at=1565600147&item_id=24945 HTTP/1.1
    1    3.270  3.270000  0.000000  3.270  3.270  3.270  3.270  3.270  3.270    1    0    0    0       21250      21250      21250      21250  GET /users/transactions.json?created_at=1565604622&item_id=29414 HTTP/1.1
    2    6.504  3.252000  0.010000  3.242  3.262  3.262  3.262  3.262  3.262    2    0    0    0       43566      21783      21783      21783  GET /users/transactions.json?created_at=1565621481&item_id=46280 HTTP/1.1
  125  336.092  2.688736  1.975040  0.032  3.237  4.917  5.043  7.358  7.383  118    0    7    0     2266947          0      18135      28606  GET /users/transactions.json HTTP/1.1

Top 20 Sort By Standard Deviation
Count    Total      Mean    Stddev    Min  P50.0  P90.0  P95.0  P99.0    Max  2xx  3xx  4xx  5xx  TotalBytes   MinBytes  MeanBytes   MaxBytes  Request
  125  336.092  2.688736  1.975040  0.032  3.237  4.917  5.043  7.358  7.383  118    0    7    0     2266947          0      18135      28606  GET /users/transactions.json HTTP/1.1
    2    3.964  1.982000  1.270000  0.712  3.252  3.252  3.252  3.252  3.252    1    0    1    0       21814          0      10907      21814  GET /users/transactions.json?created_at=1565604034&item_id=28832 HTTP/1.1
   61   57.216  0.937967  0.787357  0.000  1.607  1.618  1.624  1.697  1.697   32    0   29    0        2187          0         35         49  POST /buy HTTP/1.1
    2    3.793  1.896500  0.593500  1.303  2.490  2.490  2.490  2.490  2.490    1    0    1    0       21693          0      10846      21693  GET /users/transactions.json?created_at=1565609352&item_id=34142 HTTP/1.1
   45   22.406  0.497911  0.390861  0.000  0.810  0.821  0.825  0.841  0.841   30    0   15    0        2292          0         50         61  POST /ship HTTP/1.1
   47   25.127  0.534617  0.381227  0.000  0.806  0.813  0.816  0.817  0.817   29    0   18    0        1880         29         40         83  POST /ship_done HTTP/1.1
   27   19.512  0.722667  0.252858  0.006  0.810  0.821  0.825  0.827  0.827   27    0    0    0         918         34         34         34  POST /complete HTTP/1.1
   14    1.617  0.115500  0.210125  0.024  0.037  0.261  0.843  0.843  0.843   14    0    0    0      326099      23278      23292      23301  GET /new_items/1.json HTTP/1.1
    3    0.728  0.242667  0.191538  0.083  0.133  0.512  0.512  0.512  0.512    3    0    0    0       68805      22920      22935      22943  GET /new_items/60.json?created_at=1565625009&item_id=49800 HTTP/1.1
    2    0.379  0.189500  0.168500  0.021  0.358  0.358  0.358  0.358  0.358    2    0    0    0       15582       7791       7791       7791  GET /users/3688.json?created_at=1565582791&item_id=7586 HTTP/1.1
   63   18.466  0.293111  0.159916  0.001  0.290  0.527  0.537  0.728  0.728   54    0    9    0        5926         73         94        107  POST /login HTTP/1.1
    2    0.336  0.168000  0.144000  0.024  0.312  0.312  0.312  0.312  0.312    2    0    0    0       47423      23700      23711      23723  GET /users/3688.json HTTP/1.1
   20    2.790  0.139500  0.141149  0.044  0.080  0.424  0.560  0.560  0.560   20    0    0    0      457460      22822      22873      23024  GET /new_items/60.json HTTP/1.1
   14    1.321  0.094357  0.138659  0.022  0.041  0.329  0.513  0.513  0.513   14    0    0    0      327431      23370      23387      23393  GET /new_items/1.json?created_at=1565624524&item_id=49318 HTTP/1.1
   21    2.770  0.131905  0.125857  0.045  0.073  0.358  0.392  0.471  0.471   21    0    0    0      493046      23352      23478      23662  GET /new_items.json HTTP/1.1
    5    0.615  0.123000  0.124865  0.040  0.076  0.371  0.371  0.371  0.371    5    0    0    0      117433      23473      23486      23496  GET /new_items/30.json?created_at=1565624096&item_id=48889 HTTP/1.1
   10    1.360  0.136000  0.122524  0.039  0.082  0.416  0.416  0.416  0.416   10    0    0    0      235627      23549      23562      23572  GET /new_items/10.json?created_at=1565624249&item_id=49044 HTTP/1.1
    3    0.327  0.109000  0.122330  0.022  0.023  0.282  0.282  0.282  0.282    3    0    0    0       70115      23371      23371      23372  GET /users/3300.json?created_at=1565604295&item_id=29085 HTTP/1.1
    8    1.251  0.156375  0.117563  0.036  0.203  0.308  0.308  0.308  0.308    8    0    0    0        4664        583        583        583  GET /users/1697.json HTTP/1.1
   17    1.773  0.104294  0.117128  0.029  0.047  0.248  0.478  0.478  0.478   17    0    0    0      402566      23663      23680      23701  GET /new_items/10.json HTTP/1.1

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
3 participants
You can’t perform that action at this time.