ISHOCON1 反省会

https://github.com/showwin/ISHOCON1 を社内ISUCONの問題として使用した時の解説スライドです。

Transcript

1. ISHOCON1 ൓লձ showwin
2. ॳظঢ়ଶ είΞ: 204 by top Ҏ߱͢΂ͯRuby࣮૷ͷείΞͰ͢
3. 1 2 index ෇͚Δ͚ͩͰ଎͘ͳΓͦ͏ by qt-query-digest
4. comments.product_id ʹ INDEX ΛுΔ # MySQL > show create table comments; CREATE TABLE `comments` ( `id` int(11) NOT NULL AUTO_INCREMENT, `product_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `content` varchar(128) NOT NULL, `created_at` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=200073 DEFAULT CHARSET=utf8mb4 > alter table comments add index pid_idx(product_id);
5. είΞ: 5761 (+5500) ·ͩMySQL͕ϘτϧωοΫʹͳ͍ͬͯΔ by top
6. 1 ͜ͷΫΤϦͷͲ͕͜஗͍ͷ͔ௐࠪ͢Δ by qt-query-digest
7. EXPLAIN ͯ͠ΈΔ # MySQL > explain SELECT p.id, p.name, p.description, p.image_path, p.price, h.created_at FROM histories as h LEFT OUTER JOIN products as p ON h.product_id = p.id WHERE h.user_id = '1234' ORDER BY h.id DESC; +----+-------------+-------+--------+---------------+---------+---------+-----------------------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+---------------+---------+---------+-----------------------+--------+-------------+ | 1 | SIMPLE | h | index | NULL | PRIMARY | 4 | NULL | 500636 | Using where | | 1 | SIMPLE | p | eq_ref | PRIMARY | PRIMARY | 4 | ishocon1.h.product_id | 1 | | +----+-------------+-------+--------+---------------+---------+---------+-----------------------+--------+-------------+ # history.user_id ͷݕࡧʹ ref => NULL Ͱશݕࡧ͍ͯ͠Δ > alter table histories add index uid_idx(user_id);
8. είΞ: 10840 (+5000) MySQLͷෛՙ͕͍ͩͿݮͬͨ by top
9. ಛʹॏ͍ͨΫΤϦ͸ͳͦ͞͏ by qt-query-digest
10. ΞΫηεϩάΛݟͯΈΔ Nginx ͷϩά Unicorn ͷϩά by kataribe
11. images Λ Nginx ͔Β഑৴ $ sudo vim /etc/nginx/nginx.conf location /images/ { root /home/ishocon/webapp/ruby/public; } location / { proxy_pass http://app; } $ sudo service nginx restart # 403ʹͳΔ $ pwd /home $ ll drwx------ 3 ec2-user ec2-user 4096 Oct 5 2015 ec2-user drwx------ 11 ishocon ishocon 4096 Aug 9 20:38 ishocon $ chmod 777 ishocon
12. ͍ͭͰʹ… TCP → Unix domain Socket είΞ: 17585 (΄ͱΜͲมΘΓͳ͍…) $ vim unicorn_config.rb #listen 8080 listen ‘/tmp/unicorn.sock' $ sudo vim /etc/nginx/nginx.conf upstream app { #server localhost:8080; server unix:/tmp/unicorn.sock; } # unicorn ͱ Nginx ࠶ىಈ ༨༟͕Ͱ͖ͨ
13. ෛՙΛߴ͘͢Δ benchmark --workload 5 είΞ: 24163 (+6300) $ ./benchmark --workload 5 by top
14. είΞ: 24163 24000(status 200) - 19000(image) = 5000 ݅ͷΞΫηε ͳͷʹɺ19ສճ΋ϦΫΤετ͕ users ςʔϒϧʹ… by kataribe by qt-query-digest
15. ͔͜͜ΒΞϓϦέʔγϣϯͷ վળͩʂʂʂ
16. # ݪҼ͸͍ͭͩ͜ def current_user db.xquery('SELECT * FROM users WHERE id = ?', session[:user_id]).first end # Α͘ݟΔͱଞͷ৔ॴͰ͸3ύλʔϯͷ࢖ΘΕํ͕͋Δ # 1. current_user[:id] # 2. current_user[:name] # 3. unless current_user # ໊લ৘ใ΋ session ʹೖΕͯ͠·͑ʂ def current_user return unless session[:user_id] { id: session[:user_id] name: session[:user_name] } end είΞ: 28104 (+4000)
17. $ unicorn -c unicorn_config.rb -E production είΞ: 31882 (+3500) ͦ͏ͩɺproduction mode ʹ͠Α͏
18. • update_last_login (࠷ऴϩάΠϯ࣌ؒͷߋ৽) ॲཧ͸ଞͰ࢖༻͍ͯ͠ͳͯ͘ෆཁͳͷ Ͱ࡟আ: 32465 (+600) • GET /logout ͸ redirect login ͢Δͱ status code 3xx ʹͳΔͷͰ login ͷςϯϓϨʔ τΛ render ͯ͠ 200 Ͱฦ͢: 32866 (+400) • GET /users/:id user[:name] Λ Redis Ͱ user[:id] → user[:name] ͱGETͰ͖ΔΑ͏ ʹ͢Δ: 32405 (ޡࠩ…) • ίϝϯτͷ݅਺Λ Redis (INCR) Ͱ؅ཧ: 33308 (+900) • GET /users/:id ͷ঎඼આ໌70จࣈΛSQLͰ LEFT(p.description, 70) ʹͯऔಘ: 35479 (+2000) ΞϓϦέʔγϣϯվળ఺
19. • GET / ֤ product ͷ࠷৽ίϝϯτ5ͭͷ id Λ Redis ͷϦετͰอଘɻදࣔ ͢Δ50ݸͷ product ͷ comment_id Λશ෦औಘͯ͠ɺSELECT * FROM comments WHERE id IN (…) ORDER BY product_id DESC, created_at Ͱ औಘ͢Δɻ͜Εͩͱίϝϯτͨ͠Ϣʔβ໊͕औಘͰ͖ͳ͍ͷͰɺ user_id→user_name ʹม͑ͨ comments_user_name ςʔϒϧΛ৽͘͠ ࡞Δ: 45463 (+10000) • GET / ͷϖʔδωʔγϣϯ LIMIT 50 OFFSET page*50 Λ WHERE IN (ids) ʹ͢Δ: 50193 (+5000) ΞϓϦέʔγϣϯվળ఺
20. ͜͏ͳΔͱ Go Ͱॻ͖ͨ͘ͳΔ… by top
21. • GET /products/:id SELECT * FROM comments ͍ͯ͠Δ͚Ͳ view Ͱ͸ͦ ͷ৘ใΛ࢖͍ͬͯͳ͍ͷͰ࡟আ: 52659 (+2500) • GET /logout Nginx Ͱ Cookie ͷ஋Λ࡟আͯ͠(ϩάΞ΢τॲཧ)ɺ੩తϑΝ ΠϧΛฦ͢: 55236 (+2500) • GET /users/:id ߪೖ߹ܭֹۚΛ Redis Ͱܭࢉɻˠ DB͔ΒߪೖཤྺΛ LIMIT 30 ͰͱΕΔ: 63489 (+8000) ΞϓϦέʔγϣϯվળ఺
22. ͓ΘΓ