ISHOCON1 反省会

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

Transcript

1. ISHOCON1 ൓লձ
   showwin
   

   View Slide

2. ॳظঢ়ଶ είΞ: 204
   by top
   Ҏ߱͢΂ͯRuby࣮૷ͷείΞͰ͢
   

   View Slide

3. 1
   2
   index ෇͚Δ͚ͩͰ଎͘ͳΓͦ͏
   by qt-query-digest
   

   View Slide

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);
   

   View Slide

5. είΞ: 5761 (+5500)
   ·ͩMySQL͕ϘτϧωοΫʹͳ͍ͬͯΔ by top
   

   View Slide

6. 1
   ͜ͷΫΤϦͷͲ͕͜஗͍ͷ͔ௐࠪ͢Δ
   by qt-query-digest
   

   View Slide

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);
   

   View Slide

8. είΞ: 10840 (+5000)
   MySQLͷෛՙ͕͍ͩͿݮͬͨ
   by top
   

   View Slide

9. ಛʹॏ͍ͨΫΤϦ͸ͳͦ͞͏
   by qt-query-digest
   

   View Slide

10. ΞΫηεϩάΛݟͯΈΔ
    Nginx ͷϩά
    Unicorn ͷϩά by kataribe
    

    View Slide

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
    

    View Slide

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 ࠶ىಈ
    ༨༟͕Ͱ͖ͨ
    

    View Slide

13. ෛՙΛߴ͘͢Δ benchmark --workload 5
    είΞ: 24163 (+6300)
    $ ./benchmark --workload 5
    by top
    

    View Slide

14. είΞ: 24163
    24000(status 200) - 19000(image) = 5000 ݅ͷΞΫηε
    ͳͷʹɺ19ສճ΋ϦΫΤετ͕ users ςʔϒϧʹ…
    by kataribe
    by qt-query-digest
    

    View Slide

15. ͔͜͜ΒΞϓϦέʔγϣϯͷ
    վળͩʂʂʂ
    

    View Slide

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)
    

    View Slide

17. $ unicorn -c unicorn_config.rb -E production
    είΞ: 31882 (+3500)
    ͦ͏ͩɺproduction mode ʹ͠Α͏
    

    View Slide

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)
    ΞϓϦέʔγϣϯվળ఺
    

    View Slide

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)
    ΞϓϦέʔγϣϯվળ఺
    

    View Slide

20. ͜͏ͳΔͱ Go Ͱॻ͖ͨ͘ͳΔ…
    by top
    

    View Slide

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)
    ΞϓϦέʔγϣϯվળ఺
    

    View Slide

22. ͓ΘΓ
    

    View Slide