Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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. 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);
  2. 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);
  3. 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
  4. ͍ͭͰʹ… 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 ࠶ىಈ ༨༟͕Ͱ͖ͨ
  5. # ݪҼ͸͍ͭͩ͜ 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)
  6. • 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) ΞϓϦέʔγϣϯվળ఺
  7. • 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) ΞϓϦέʔγϣϯվળ఺
  8. • GET /products/:id SELECT * FROM comments ͍ͯ͠Δ͚Ͳ view Ͱ͸ͦ

    ͷ৘ใΛ࢖͍ͬͯͳ͍ͷͰ࡟আ: 52659 (+2500) • GET /logout Nginx Ͱ Cookie ͷ஋Λ࡟আͯ͠(ϩάΞ΢τॲཧ)ɺ੩తϑΝ ΠϧΛฦ͢: 55236 (+2500) • GET /users/:id ߪೖ߹ܭֹۚΛ Redis Ͱܭࢉɻˠ DB͔ΒߪೖཤྺΛ LIMIT 30 ͰͱΕΔ: 63489 (+8000) ΞϓϦέʔγϣϯվળ఺