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

first_mysql_performance_tuning_at_builderscon2017

mamy1326
August 04, 2017

 first_mysql_performance_tuning_at_builderscon2017

builderscon 2017 8/4(Fri) 11:00 から登壇した際のスライドです。

mamy1326

August 04, 2017
Tweet

More Decks by mamy1326

Other Decks in Technology

Transcript

  1. ɹ.Z42-5VOFS $ wget -O mysqltuner.zip https://github.com/rackerhacker/ MySQLTuner-perl/archive/master.zip $ unzip mysqltuner.zip

    $ cd MySQLTuner-perl-master $ chmod 755 mysqltuner.pl $ perl mysqltuner.pl --user root --pass='mamy1326'
  2. ɹ.Z42-5VOFS $ wget -O mysqltuner.zip https://github.com/rackerhacker/ MySQLTuner-perl/archive/master.zip $ unzip mysqltuner.zip

    $ cd MySQLTuner-perl-master $ chmod 755 mysqltuner.pl $ perl mysqltuner.pl --user root --pass='mamy1326' HJUIVC͔Β [JQΛXHFU
  3. ɹ.Z42-5VOFS $ wget -O mysqltuner.zip https://github.com/rackerhacker/ MySQLTuner-perl/archive/master.zip $ unzip mysqltuner.zip

    $ cd MySQLTuner-perl-master $ chmod 755 mysqltuner.pl $ perl mysqltuner.pl --user root --pass='mamy1326' ղౚͱ ద੾ͳ࣮ߦݖݶ
  4. ɹ.Z42-5VOFS $ wget -O mysqltuner.zip https://github.com/rackerhacker/ MySQLTuner-perl/archive/master.zip $ unzip mysqltuner.zip

    $ cd MySQLTuner-perl-master $ chmod 755 mysqltuner.pl $ perl mysqltuner.pl --user root --pass='mamy1326' Ϣʔβʔɺ 18ࢦఆ͠ɺ࣮ߦ
  5. ɹ.Z42-5VOFS -------- Recommendations ----------------------------- General recommendations: Enable the slow query

    log to troubleshoot bad queries Set thread_cache_size to 4 as a starting value Variables to adjust: query_cache_size (>= 8M) thread_cache_size (start at 4) innodb_file_per_table=ON innodb_buffer_pool_size (>= 12G) if possible. innodb_log_file_size should be equals to 1/4 of buffer pool size (=128M) if possible. ⾣࣮ߦ݁Ռ
  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) DPNNJUΛ௨஌ޙ εϨʔϒ͕઀ଓͯ͠औಘ WBSMJCNZTRM NZTRMCJO999999
  7. ɹɹϚελʔόΠφϦϩάͷ֬ೝ 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͕࣮ߦ͞Εͨ ϙδγϣϯ
  8. ɹɹϚελʔόΠφϦϩάͷ֬ೝ 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) εϨʔϒઃఆͰ࢖༻͢ΔͨΊ ϝϞ͓͖ͯ͠·͠ΐ͏
  9. ɹɹεϨʔϒͷઃఆ֬ೝ 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)
  10. ɹɹϚελʔͷ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)
  11. ɹɹϚελʔͷ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) εϨʔϒઃఆͰ࢖༻͢ΔͨΊ ϝϞ͓͖ͯ͠·͠ΐ͏
  12. ɹɹϚελʔͷEVNQΛऔಘ ⾣EVNQऔಘ $ mysqldump -u root -p mamy1326 > gzip

    mamy1326.dump.gz ɾผίϯιʔϧ͔Β࣮ࢪ ɹˠRVJU͢ΔͱϩοΫ͕֎Ε·͢ ɾετϨʔδ༰ྔΛߟྀͯ͠H[JQ ɾTDQίϚϯυ౳ͰεϨʔϒʹసૹ
  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> 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
  17. ɹ࣮ߦঢ়گΛ؂ࢹ 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 ⾣ಉظ׬ྃ
  18. ɹ$IBSBTFU $PMMBUJPO [mysqld] # mysqld ϩάग़ྗઃఆ log-error=/var/log/mysql-error.log # ৽ن࡞੒͢ΔσʔλϕʔεͷCharaset character-set-server

    = utf8mb4 # ৽ن࡞੒͢ΔσʔλϕʔεͷCharaset collation_server = utf8mb4_bin [client] # ΫϥΠΞϯτ಺ͷจࣈॲཧͱαʔόʔͱͷ઀ଓͷCharaset loose-default-character-set = utf8mb4
  19. ɹ$IBSBTFU $PMMBUJPO [mysqld] # mysqld ϩάग़ྗઃఆ log-error=/var/log/mysql-error.log # ৽ن࡞੒͢ΔσʔλϕʔεͷCharaset character-set-server

    = utf8mb4 # ৽ن࡞੒͢ΔσʔλϕʔεͷCharaset collation_server = utf8mb4_bin [client] # ΫϥΠΞϯτ಺ͷจࣈॲཧͱαʔόʔͱͷ઀ଓͷCharaset loose-default-character-set = utf8mb4 ໌֬ʹ ΤϥʔϩάͰ͋Δ͜ͱΛ ࢦఆ͢Δ
  20. ɹ$IBSBTFU $PMMBUJPO [mysqld] # mysqld ϩάग़ྗઃఆ log-error=/var/log/mysql-error.log # ৽ن࡞੒͢ΔσʔλϕʔεͷCharaset character-set-server

    = utf8mb4 # ৽ن࡞੒͢ΔσʔλϕʔεͷCollation collation_server = utf8mb4_bin [client] # ΫϥΠΞϯτ಺ͷจࣈॲཧͱαʔόʔͱͷ઀ଓͷCharaset loose-default-character-set = utf8mb4 ͜͜Ͱઃఆ͍ͯ͠Ε͹ %#ɺςʔϒϧɺΧϥϜͰ ࢦఆ͢Δඞཁ͕ͳ͍
  21. ɹ$IBSBTFU $PMMBUJPO [mysqld] # mysqld ϩάग़ྗઃఆ log-error=/var/log/mysql-error.log # ৽ن࡞੒͢ΔσʔλϕʔεͷCharaset character-set-server

    = utf8mb4 # ৽ن࡞੒͢ΔσʔλϕʔεͷCharaset collation_server = utf8mb4_bin [client] # ΫϥΠΞϯτ಺ͷจࣈॲཧͱαʔόʔͱͷ઀ଓͷCharaset loose-default-character-set = utf8mb4 $IBSBTFUಉ༷ ͜͜Ͱઃఆ͓ͯ͘͠
  22. ɹ$IBSBTFU $PMMBUJPO [mysqld] # mysqld ϩάग़ྗઃఆ log-error=/var/log/mysql-error.log # ৽ن࡞੒͢ΔσʔλϕʔεͷCharaset character-set-server

    = utf8mb4 # ৽ن࡞੒͢ΔσʔλϕʔεͷCharaset collation_server = utf8mb4_bin [client] # ΫϥΠΞϯτ಺ͷจࣈॲཧͱαʔόʔͱͷ઀ଓͷCharaset loose-default-character-set = utf8mb4 ͨͩ͠ $)"3"$5&34&5͢Δͱ ແࢹ͞ΕΔͷͰ஫ҙ
  23. ɹ$IBSBTFU $PMMBUJPO [mysqld] # mysqld ϩάग़ྗઃఆ log-error=/var/log/mysql-error.log # ৽ن࡞੒͢ΔσʔλϕʔεͷCharaset character-set-server

    = utf8mb4 # ৽ن࡞੒͢ΔσʔλϕʔεͷCharaset collation_server = utf8mb4_bin [client] # ΫϥΠΞϯτ಺ͷจࣈॲཧͱαʔόʔͱͷ઀ଓͷCharaset loose-default-character-set = utf8mb4 ͜͜Ͱઃఆ͍ͯ͠Ε͹ ௨৴࣌ʹ΋ จࣈԽ͚Λ๷ࢭͰ͖Δ
  24. ɹ$IBSBTFU $PMMBUJPO mysql> show variables like 'char%'; +--------------------------+----------------------------+ | Variable_name

    | Value | +--------------------------+----------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8mb4 | | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+ ⾣$IBSBTFU֬ೝ
  25. ɹTMPX@MPHؔ࿈ [mysqld] # slow log ग़ྗઃఆ slow_query_log = ON #

    slow.log ͷ৔ॴ slow_query_log_file = /var/log/mysql/slow.log # ग़ྗର৅ඵ਺ long_query_time = 3 # no index ͷΫΤϦ΋ग़͔͢Ͳ͏͔ log_queries_not_using_indexes = 1 # no index Λϩάʹग़ྗ͢Δִؒʢ෼ʣ log_throttle_queries_not_using_indexes = 1 # εϩʔΫΤϦͰɺSELECTߦ਺͕ࢦఆ਺ҎԼͷΫΤϦ΋ϩάʹग़ྗ min_examined_row_limit = 0
  26. ɹTMPX@MPHؔ࿈ [mysqld] # slow log ग़ྗઃఆ slow_query_log = ON #

    slow.log ͷ৔ॴ slow_query_log_file = /var/log/mysql/slow.log # ग़ྗର৅ඵ਺ long_query_time = 3 # no index ͷΫΤϦ΋ग़͔͢Ͳ͏͔ log_queries_not_using_indexes = 1 # no index Λϩάʹग़ྗ͢Δִؒʢ෼ʣ log_throttle_queries_not_using_indexes = 1 # εϩʔΫΤϦͰɺSELECTߦ਺͕ࢦఆ਺ҎԼͷΫΤϦ΋ϩάʹग़ྗ min_examined_row_limit = 0 OPJOEFYͷΫΤϦΛ ϩάʹग़͢
  27. ɹTMPX@MPHؔ࿈ [mysqld] # slow log ग़ྗઃఆ slow_query_log = ON #

    slow.log ͷ৔ॴ slow_query_log_file = /var/log/mysql/slow.log # ग़ྗର৅ඵ਺ long_query_time = 3 # no index ͷΫΤϦ΋ग़͔͢Ͳ͏͔ log_queries_not_using_indexes = 1 # no index Λϩάʹग़ྗ͢Δִؒʢ෼ʣ log_throttle_queries_not_using_indexes = 1 # εϩʔΫΤϦͰɺSELECTߦ਺͕ࢦఆ਺ҎԼͷΫΤϦ΋ϩάʹग़ྗ min_examined_row_limit = 0 OPJOEFYͷΫΤϦΛ ϩάʹग़ִؒ͢ʢ෼ʣ
  28. ɹTMPX@MPHؔ࿈ [mysqld] # slow log ग़ྗઃఆ slow_query_log = ON #

    slow.log ͷ৔ॴ slow_query_log_file = /var/log/mysql/slow.log # ग़ྗର৅ඵ਺ long_query_time = 3 # no index ͷΫΤϦ΋ग़͔͢Ͳ͏͔ log_queries_not_using_indexes = 1 # no index Λϩάʹग़ྗ͢Δִؒʢ෼ʣ log_throttle_queries_not_using_indexes = 1 # εϩʔΫΤϦͰɺSELECTߦ਺͕ࢦఆ਺ҎԼͷΫΤϦ΋ϩάʹग़ྗ min_examined_row_limit = 0 4&-&$5ͷ ߦ਺ࢦఆ
  29. ɹ҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣UJNFTUBNQܕ [Warning] TIMESTAMP with implicit DEFAULT value is deprecated.

    Please use —explicit_defaults_for_timestamp server option (see documentation for more details).
  30. ɹ҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣UJNFTUBNQܕ [Warning] TIMESTAMP with implicit DEFAULT value is deprecated.

    Please use —explicit_defaults_for_timestamp server option (see documentation for more details). ͍ΘΏΔθϩλΠϜελϯϓ ‘0000-00-00 00:00:00’
  31. ɹ҉໧తͳଐੑ෇༩͸ඇਪ঑ ⾣ྫɿUJNFTUBNQܕ [Warning] TIMESTAMP with implicit DEFAULT value is deprecated.

    Please use —explicit_defaults_for_timestamp server option (see documentation for more details). ͍ΘΏΔθϩλΠϜελϯϓ ‘0000-00-00 00:00:00’ ҉໧తͳଐੑ෇༩Ͱ ҙਤ͠ͳ͍஋ʹͳΔ
  32. ɹ҉໧తͳଐੑ෇༩͸ඇਪ঑ [mysqld] # timestampܕͷ҉໧తͳσϑΥϧτ஋ explicit_defaults_for_timestamp = ON ⾣ઃఆ ⾣֬ೝ mysql>

    show variables like 'explicit_defaults_for_timestamp'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | explicit_defaults_for_timestamp | ON | +---------------------------------+-------+ 1 row in set (0.00 sec)