ISUCON練習日記2021-03-13 ISUCON10予選
ISUCON10予選問題で練習した。色々忘れないようにメモ。
DB分割
DBの設定複製 estate/chairを触るSQLごとに振り分け
・DBインスタンス複製
分割前測定
{"pass":true,"score":902,"messages":[],"reason":"OK","language":"go"}
1,2号機CPU
## アプリプロファイル 16:20 key count sum avg main.searchChairs 1729 154460 150.25 main.searchChairs: part2 1729 114261 111.15 main.searchEstateNazotte 102 101176 991.92 main.searchEstateNazotte: part2 102 88169 864.40 main.searchEstates 2824 87232 84.86 main.searchEstates: part2 2824 60437 58.79 main.getEstateDetail 4712 43916 42.72 main.getChairDetail 3848 42513 41.36 main.searchChairs: part3 1729 38794 37.74 main.searchEstates: part3 2824 24082 23.43 main.searchEstateNazotte: part1 102 12918 126.65 main.getLowPricedEstate 744 11152 14.99 main.searchRecommendedEstateWithChair 459 11142 24.27 main.getLowPricedChair 744 10286 13.83 16:20 ## nginxログ 16:20 +-------+-----+-------+-----+-----+-----+--------+------------------------------+---------+-------+-------+-------+-----------+-----------+ | COUNT | 1XX | 2XX | 3XX | 4XX | 5XX | METHOD | URI | SUM | AVG | MIN | MAX | MIN(BODY) | MAX(BODY) | +-------+-----+-------+-----+-----+-----+--------+------------------------------+---------+-------+-------+-------+-----------+-----------+ | 1729 | 0 | 1714 | 0 | 15 | 0 | GET | /api/chair/search | 224.296 | 0.130 | 0.005 | 1.132 | 0.000 | 17925.000 | | 2824 | 0 | 2811 | 0 | 13 | 0 | GET | /api/estate/search | 203.415 | 0.072 | 0.004 | 1.062 | 0.000 | 27095.000 | | 4712 | 0 | 4667 | 0 | 45 | 0 | GET | /api/estate/\d+ | 171.501 | 0.036 | 0.004 | 0.984 | 0.000 | 705.000 | | 3848 | 0 | 3823 | 0 | 25 | 0 | GET | /api/chair/\d+ | 154.742 | 0.040 | 0.004 | 1.080 | 0.000 | 647.000 | | 102 | 0 | 99 | 0 | 3 | 0 | POST | /api/estate/nazotte | 100.232 | 0.983 | 0.676 | 1.464 | 0.000 | 33942.000 | | 744 | 0 | 744 | 0 | 0 | 0 | GET | /api/estate/low_priced | 12.096 | 0.016 | 0.004 | 0.340 | 13383.000 | 13426.000 | | 459 | 0 | 459 | 0 | 0 | 0 | GET | /api/recommended_estate/\d+ | 11.752 | 0.026 | 0.004 | 0.404 | 13081.000 | 13712.000 | | 744 | 0 | 743 | 0 | 1 | 0 | GET | /api/chair/low_priced | 10.976 | 0.015 | 0.004 | 0.684 | 0.000 | 12186.000 | | 674 | 0 | 674 | 0 | 0 | 0 | POST | /api/estate/req_doc/\d+ | 5.288 | 0.008 | 0.004 | 0.148 | 0.000 | 0.000 | | 228 | 0 | 228 | 0 | 0 | 0 | POST | /api/chair/buy/\d+ | 4.048 | 0.018 | 0.004 | 0.168 | 0.000 | 0.000 | | 1 | 0 | 1 | 0 | 0 | 0 | POST | /initialize | 4.044 | 4.044 | 4.044 | 4.044 | 23.000 | 23.000 | | 3 | 0 | 3 | 0 | 0 | 0 | POST | /api/estate | 3.404 | 1.135 | 0.528 | 1.516 | 0.000 | 0.000 | | 4 | 0 | 3 | 0 | 1 | 0 | POST | /api/chair | 3.403 | 0.851 | 0.187 | 1.380 | 0.000 | 0.000 | | 414 | 0 | 414 | 0 | 0 | 0 | GET | /api/estate/search/condition | 0.444 | 0.001 | 0.000 | 0.008 | 2563.000 | 2563.000 | ... +-------+-----+-------+-----+-----+-----+--------+------------------------------+---------+-------+-------+-------+-----------+-----------+ | 16720 | 0 | 16617 | 0 | 103 | 0 | +-------+-----+-------+-----+-----+-----+--------+------------------------------+---------+-------+-------+-------+-----------+-----------+ # Rank Query ID Response time Calls R/Call V/M # ==== =================================== ============== ===== ====== === # 1 0xB718722664D79180DE3386CFE9C21BF6 84.6158 23.1% 102 0.8296 0.02 SELECT estate # 2 0x4E8EAE12D58B73C2F3EFAD4903F0402F 27.5122 7.5% 281 0.0979 0.01 SELECT chair # 3 0x929E86BFE887C4E10B23D92EDD8D61BC 11.7012 3.2% 204 0.0574 0.01 SELECT estate # 4 0xE010F89F59F07FE84177E58CAC7321CE 10.8306 3.0% 102 0.1062 0.01 SELECT estate # 5 0xED731470FE91D25E31E27AD9C15C891F 10.3488 2.8% 191 0.0542 0.01 SELECT estate # 6 0x85EDDA9F02FE0A0E65DBB57A0B593F83 7.3063 2.0% 133 0.0549 0.01 SELECT chair # 7 0x3636444F1E726DFB63C508D1F648A860 6.6427 1.8% 80 0.0830 0.00 SELECT estate # MISC 0xMISC 207.6529 56.6% 6234 0.0333 0.0 <279 ITEMS>
分割後測定
{"pass":true,"score":1261,"messages":[],"reason":"OK","language":"go"}
1.5倍くらいになった。DB分割の効果がでている。
1,2,3号機CPU（右端の山）
## アプリプロファイル 16:30 key count sum avg main.searchEstateNazotte 210 162128 772.04 main.searchEstateNazotte: part2 210 138214 658.16 main.searchChairs 2784 125966 122.53 main.searchEstates 3374 117236 114.04 main.searchChairs: part2 2784 79401 77.24 main.searchEstates: part2 3374 74178 72.16 main.getEstateDetail 8151 51884 50.47 main.searchChairs: part3 2784 50158 48.79 main.searchEstates: part3 3374 35627 34.66 main.getChairDetail 7103 35234 34.27 main.searchEstateNazotte: part1 210 23718 112.94 main.getLowPricedEstate 1022 20305 19.87 main.searchRecommendedEstateWithChair 710 19414 27.34 main.postEstateRequestDocument 910 13805 15.17 16:30 ## nginxログ 16:30 +-------+-----+-------+-----+-----+-----+--------+------------------------------+---------+-------+-------+-------+-----------+-----------+ | COUNT | 1XX | 2XX | 3XX | 4XX | 5XX | METHOD | URI | SUM | AVG | MIN | MAX | MIN(BODY) | MAX(BODY) | +-------+-----+-------+-----+-----+-----+--------+------------------------------+---------+-------+-------+-------+-----------+-----------+ | 8158 | 0 | 8112 | 0 | 46 | 0 | GET | /api/estate/\d+ | 385.097 | 0.047 | 0.016 | 1.168 | 538.000 | 713.000 | | 3374 | 0 | 3366 | 0 | 8 | 0 | GET | /api/estate/search | 307.486 | 0.091 | 0.004 | 0.940 | 0.000 | 20715.000 | | 2784 | 0 | 2772 | 0 | 12 | 0 | GET | /api/chair/search | 287.663 | 0.103 | 0.012 | 1.084 | 0.000 | 20851.000 | | 7103 | 0 | 7075 | 0 | 28 | 0 | GET | /api/chair/\d+ | 237.831 | 0.033 | 0.004 | 0.820 | 0.000 | 659.000 | | 210 | 0 | 206 | 0 | 4 | 0 | POST | /api/estate/nazotte | 161.540 | 0.769 | 0.062 | 1.560 | 0.000 | 34291.000 | | 1022 | 0 | 1022 | 0 | 0 | 0 | GET | /api/estate/low_priced | 22.344 | 0.022 | 0.008 | 0.564 | 13323.000 | 13427.000 | | 710 | 0 | 707 | 0 | 3 | 0 | GET | /api/recommended_estate/\d+ | 20.386 | 0.029 | 0.004 | 0.684 | 0.000 | 13727.000 | | 910 | 0 | 910 | 0 | 0 | 0 | POST | /api/estate/req_doc/\d+ | 14.880 | 0.016 | 0.004 | 0.908 | 0.000 | 0.000 | | 1022 | 0 | 1022 | 0 | 0 | 0 | GET | /api/chair/low_priced | 14.548 | 0.014 | 0.004 | 0.264 | 12139.000 | 12186.000 | | 7 | 0 | 7 | 0 | 0 | 0 | POST | /api/estate | 11.976 | 1.711 | 0.464 | 2.352 | 0.000 | 0.000 | | 7 | 0 | 7 | 0 | 0 | 0 | POST | /api/chair | 11.736 | 1.677 | 0.484 | 2.768 | 0.000 | 0.000 | | 1 | 0 | 1 | 0 | 0 | 0 | POST | /initialize | 7.304 | 7.304 | 7.304 | 7.304 | 23.000 | 23.000 | | 351 | 0 | 351 | 0 | 0 | 0 | POST | /api/chair/buy/\d+ | 5.736 | 0.016 | 0.004 | 0.120 | 0.000 | 0.000 | | 458 | 0 | 458 | 0 | 0 | 0 | GET | /api/estate/search/condition | 0.620 | 0.001 | 0.004 | 0.020 | 2563.000 | 2563.000 | ... +-------+-----+-------+-----+-----+-----+--------+------------------------------+---------+-------+-------+-------+-----------+-----------+ | 26478 | 0 | 26377 | 0 | 101 | 0 | +-------+-----+-------+-----+-----+-----+--------+------------------------------+---------+-------+-------+-------+-----------+-----------+ # Rank Query ID Response time Calls R/Call V/M # ==== =================================== ============== ===== ====== === # 1 0x4E8EAE12D58B73C2F3EFAD4903F0402F 11.5774 5.1% 328 0.0353 0.02 SELECT chair # 2 0x4FA7F17A15BCD31A08C4C2F052598EB7 5.8728 2.6% 74 0.0794 0.02 SELECT chair # 3 0x929787B1FE641900E3DD23571CAC3AB6 5.5240 2.4% 72 0.0767 0.02 SELECT chair # 4 0x17813360728C84E63F19DC57445EC358 4.3238 1.9% 29 0.1491 0.10 SELECT chair # 5 0x9CCCD1D3E32D61BB18B4ECD908E723B4 4.1910 1.8% 59 0.0710 0.02 SELECT chair # 6 0xF6CBE5E7B4D4ECE4095626C611F2D411 3.8432 1.7% 47 0.0818 0.01 SELECT chair # 7 0x7A3943FC5D195AC5C0824B77FDBE8C4F 2.9514 1.3% 24 0.1230 0.01 SELECT chair # MISC 0xMISC 190.5975 83.3% 4891 0.0390 0.0 <567 ITEMS> # Rank Query ID Response time Calls R/Call V/M # ==== =================================== ============== ===== ====== === # 1 0xB718722664D79180DE3386CFE9C21BF6 135.2677 41.9% 207 0.6535 0.06 SELECT estate # 2 0xE010F89F59F07FE84177E58CAC7321CE 17.7821 5.5% 207 0.0859 0.01 SELECT estate # 3 0x3636444F1E726DFB63C508D1F648A860 9.3767 2.9% 129 0.0727 0.01 SELECT estate # 4 0x3A6102F5BB1888CE18692940986AC2AD 8.2232 2.5% 111 0.0741 0.00 SELECT estate # 5 0xAA809559207975DC00087902517AC50D 6.6264 2.1% 97 0.0683 0.01 SELECT estate # 6 0x9874FCB909778990D4BDA82D2EB9C996 6.5708 2.0% 90 0.0730 0.00 SELECT estate # 7 0x63EDCA18912C7FEE67A0CB124E70256F 5.7084 1.8% 74 0.0771 0.01 SELECT estate # MISC 0xMISC 133.2300 41.3% 4788 0.0278 0.0 <135 ITEMS>
