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

mysql_first_performance_ tuning_y8

mysql_first_performance_ tuning_y8

初めてのMySQLパフォーマンスチューニング

mamy1326

May 27, 2017
Tweet

More Decks by mamy1326

Other Decks in Programming

Transcript

  1. ઃఆ஋ͷ֬ೝ NZTRM4)087"3*"#-&4-*,&bRVFSZ@DBDIF +------------------------------+-----------+ | Variable_name | Value | +------------------------------+-----------+ |

    have_query_cache | YES | | query_cache_limit | 16777216 | | query_cache_min_res_unit | 4096 | | query_cache_size | 536870912 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+-----------+
  2. ɹ൓өঢ়ଶͷݕূ NZTRM4)084&44*0/45"564-*,&b2DBDIF +-------------------------+-----------+ | Variable_name | Value | +-------------------------+-----------+ |

    Qcache_free_blocks | 791 | | Qcache_free_memory | 533156064 | | Qcache_hits | 1442086 | | Qcache_inserts | 291072 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 3599 | | Qcache_queries_in_cache | 1760 | | Qcache_total_blocks | 4361 | +-------------------------+-----------+
  3. ɹɹϚελʔόΠφϦϩάͷ֬ೝ mysql> SHOW MASTER STATUS\G ********* 1. row ********* File:

    mysql-bin.000075 Position: 588201136 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.01 sec)
  4. ɹɹϚελʔόΠφϦϩάͷ֬ೝ mysql> SHOW MASTER STATUS\G ********* 1. row ********* File:

    mysql-bin.000075 Position: 588201136 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.01 sec) DPNNJUΛ௨஌ޙ εϨʔϒ͕઀ଓͯ͠औಘ WBSMJCNZTRM NZTRMCJO999999
  5. ɹɹϚελʔόΠφϦϩάͷ֬ೝ mysql> SHOW MASTER STATUS\G ********* 1. row ********* File:

    mysql-bin.000075 Position: 588201136 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.01 sec) ϚελʔͰ DPNNJU͕࣮ߦ͞Εͨ ϙδγϣϯ
  6. ɹɹϚελʔόΠφϦϩάͷ֬ೝ mysql> SHOW MASTER STATUS\G ********* 1. row ********* File:

    mysql-bin.000075 Position: 588201136 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.01 sec) εϨʔϒઃఆͰ࢖༻͢ΔͨΊ ϝϞ͓͖ͯ͠·͠ΐ͏
  7. ɹɹεϨʔϒͷઃఆ֬ೝ mysql> SHOW SLAVE STATUS\G **************** 1. row **************** Slave_IO_State:

    Master_Host: [Ϛελʔͷϗετ໊] Master_User: repl Master_Log_File: mysql-bin.000009 Read_Master_Log_Pos: 303456264 Slave_IO_Running: No Slave_SQL_Running: No Seconds_Behind_Master: NULL Master_Server_Id: 0 1 row in set (0.01 sec)
  8. ɹɹϚελʔͷEVNQΛऔಘ ⾣ϚελʔΛϩοΫ mysql(master)> FLUSH TABLES WITH READ LOCK; Query OK,

    0 rows affected (0.00 sec) ⾣όΠφϦϩάͱϙδγϣϯऔಘ mysql(master)> SHOW MASTER STATUS; File: mysql-bin.000075 Position: 588201136 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.01 sec)
  9. ɹɹϚελʔͷEVNQΛऔಘ ⾣ϚελʔΛϩοΫ mysql(master)> FLUSH TABLES WITH READ LOCK; Query OK,

    0 rows affected (0.00 sec) ⾣όΠφϦϩάͱϙδγϣϯऔಘ mysql(master)> SHOW MASTER STATUS; File: mysql-bin.000075 Position: 588201136 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.01 sec) εϨʔϒઃఆͰ࢖༻͢ΔͨΊ ϝϞ͓͖ͯ͠·͠ΐ͏
  10. ɹɹϚελʔͷEVNQΛऔಘ ⾣EVNQऔಘ $ mysqldump -u root -p mamy1326 > gzip

    mamy1326.dump.gz ɾผίϯιʔϧ͔Β࣮ࢪ ɹˠRVJU͢ΔͱϩοΫ͕֎Ε·͢ ɾετϨʔδ༰ྔΛߟྀͯ͠H[JQ ɾTDQίϚϯυ౳ͰεϨʔϒʹసૹ
  11. [mysqld] # εϨʔϒͷαʔόʔID server-id=1002 # Ϛελʔͷঢ֨Λߟྀ͠ɺϩΪϯάΛ༗ޮʹ͢Δ log-bin=mysql-bin # όΠφϦϩάϑΝΠϧͷϩʔςʔτ೔਺ set-variable=expire_logs_days=3

    # όΠφϦϩάͷग़ྗઃఆ log_slave_updates ɹɹεϨʔϒͷNZDOGઃఆ όΠφϦϩάઃఆ Ϛελʔʹঢ֨ͨ͠৔߹ εϨʔϒʹ௨஌͢Δ
  12. [mysqld] # εϨʔϒͷαʔόʔID server-id=1002 # Ϛελʔͷঢ֨Λߟྀ͠ɺϩΪϯάΛ༗ޮʹ͢Δ log-bin=mysql-bin # όΠφϦϩάϑΝΠϧͷϩʔςʔτ೔਺ set-variable=expire_logs_days=3

    # όΠφϦϩάͷग़ྗઃఆ log_slave_updates ɹɹεϨʔϒͷNZDOGઃఆ εϨʔϒͰ΋ όΠφϦϩάΛग़ྗ͠ Ϛελʔঢ֨Մೳʹ
  13. ɹεϨʔϒઃఆมߋ ⾣ϚελʔͰϝϞͨ͠಺༰Λઃఆ mysql> STOP SLAVE; mysql> RESET SLAVE; mysql> CHANGE

    MASTER TO -> master_log_file = ’mysql-bin.000075’, -> master_log_pos = 588201136;
  14. mysql> STOP SLAVE; mysql> RESET SLAVE; mysql> CHANGE MASTER TO

    -> master_log_file = ’mysql-bin.000075’, -> master_log_pos = 588201136; ɹεϨʔϒઃఆมߋ ⾣ϚελʔͰϝϞͨ͠಺༰Λઃఆ εϨʔϒΛࢭΊ·͢
  15. mysql> STOP SLAVE; mysql> RESET SLAVE; mysql> CHANGE MASTER TO

    -> master_log_file = ’mysql-bin.000075’, -> master_log_pos = 588201136; ɹεϨʔϒઃఆมߋ ⾣ϚελʔͰϝϞͨ͠಺༰Λઃఆ εϨʔϒ͕อ͍࣋ͯͨ͠ ϨϓϦέʔγϣϯҐஔΛ Ϧηοτ͠·͢
  16. mysql> STOP SLAVE; mysql> RESET SLAVE; mysql> CHANGE MASTER TO

    -> master_log_file = ’mysql-bin.000075’, -> master_log_pos = 588201136; ɹεϨʔϒઃఆมߋ ⾣ϚελʔͰϝϞͨ͠಺༰Λઃఆ ϚελʔͷόΠφϦϩάɺ ϙδγϣϯΛઃఆ
  17. ɹϨϓϦέʔγϣϯ࣮ߦ ⾣ϨϓϦέʔγϣϯͷ֬ೝ mysql> SHOW SLAVE STATUS\G ***************** 1. row *****************

    Slave_IO_State: Waiting for master to send event Master_Log_File: mysql-bin.000075 Read_Master_Log_Pos: 878873047 Slave_IO_Running: Yes Slave_SQL_Running: Yes Exec_Master_Log_Pos: 605504154 Seconds_Behind_Master: 244615 Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Master_Server_Id: 1001
  18. ɹ࣮ߦঢ়گΛ؂ࢹ mysql> SHOW SLAVE STATUS\G **************** 1. row ************** Read_Master_Log_Pos:

    878873047 Exec_Master_Log_Pos: 605504154 Seconds_Behind_Master: 244615 ⾣࣮ߦ௚ޙ
  19. ɹ࣮ߦঢ়گΛ؂ࢹ mysql> SHOW SLAVE STATUS\G **************** 1. row ************** Read_Master_Log_Pos:

    878873047 Exec_Master_Log_Pos: 605504154 Seconds_Behind_Master: 244615 ⾣࣮ߦ௚ޙ mysql> SHOW SLAVE STATUS\G **************** 1. row ************** Read_Master_Log_Pos: 879066686 Exec_Master_Log_Pos: 879066686 Seconds_Behind_Master: 0 ⾣ಉظ׬ྃ