$30 off During Our Annual Pro Sale. View Details »

ISHOCON1 反省会

ITO Shogo
September 01, 2016

ISHOCON1 反省会

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

ITO Shogo

September 01, 2016
Tweet

More Decks by ITO Shogo

Other Decks in Programming

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