ºÝºÝߣ

ºÝºÝߣShare a Scribd company logo

MySQL ?? ??? ?? ? ??

?
?
I Goo Lee
I Goo Lee

MySQL ?? ??? ?? ? ??

Convert to study materialsBETA

Transform any presentation into ready-made study material¡ªselect from outputs like summaries, definitions, and practice questions.

1 of 28
Downloaded 90 times
MySQL State Message ?? 2015.11.28 MySQL Power Group 3? ??? SK Telecom ??? 1
MySQL State Message ?? ??? ?? 2003 ~ 2006 ???? ??? Windows + SQL Server ?? 2006 ~ 2014 ???? ? DBA MySQL + Oracle 2014 ~ ???? ?? DBA MySQL + Oracle Nickname : ??? ??? Blog : seuis398.blog.me 2
?? MySQL ?? ??? ?? 1) GLOBAL STATUS (??? ?? ?? ????) Query Type ? ?? ?? InnoDB Buffer pool activity (Logical & Physical Read, Dirty Buffer byte) InnoDB row operations MySQL Handler status Sort ? temporary table(memory, disk) ?? ?? 2) Performance Schema (?? ??? ??? ????) statement/* : Query ??? ?? ??(execution count), 1? ?? ??/?? latency stage/sql/* : ?? statement ?? ??? 1? ?? ??/?? latency wait/io/file/* : ???, ???, ?? ?? ?? ?? IO ??? (??, 1? ?? ??/?? latency) wait/io/table/* : MySQL - Storage Engine ? IO ??? (??, 1? ?? ??/?? latency) ??? ??!!! 3 MySQL State Message ??
?? MySQL State Message ¡°show processlist¡± ?? ¡°show profile¡± ???? ?? ?? ?? performance_schema.events_statements_xxx ?????? ?? ?? MySQL 5.6 ?? 109?, MySQL 5.7 ?? 128?? Message? ?? 4 MySQL State Message ??
?? ?? ????? ????? ?? ??? ? starting, init, preparing? ???? ? end? query end? ???? ? freeing items? cleaning up? ???? ? update, updating? ???? ? MySQL Reference Manual (https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html) 5 MySQL State Message ??
?? ?? MySQL source code? ??? ??!! https://www.github.com/mysql/mysql-server 6 MySQL State Message ??
?? ?? ???? 1) sql parse ???? ?? query handler ??? ???? ??. ??? ???. @_@; 2) MySQL ??? ?? ??? ?? ??? ??? ??? ??? ????. Writing to net => Sending to client 3) State Message? ??? bug? ??. 5.6 ?? ?? prepared statement ??? ???? ?? ?? ??? statistics ??? ??? 4) 5.6 ????? 5.7 ??? ? ?? ??. ??? ?? ?? ??? ??. ?? ?? State Message? ??? ???? ??? ????, State Message? ?? ??? ???? ??. 7 MySQL State Message ??
?? ????? ? SHOW PROFILE SOURCE ???? ?? ??!! ? ½YÕß½âÖ® sql_parser.cc? dispatch_command?? ??!! +----------------------+----------+-----------------------+----------------------+-------------+ | Status | Duration | Source_function | Source_file | Source_line | +----------------------+----------+-----------------------+----------------------+-------------+ | starting | 0.000060 | NULL | NULL | NULL | | checking permissions | 0.000008 | check_access | sql_authorization.cc | 824 | | Opening tables | 0.000017 | open_tables | sql_base.cc | 5522 | | init | 0.000018 | handle_query | sql_select.cc | 121 | | System lock | 0.000010 | mysql_lock_tables | lock.cc | 321 | | optimizing | 0.000005 | optimize | sql_optimizer.cc | 151 | | statistics | 0.000015 | optimize | sql_optimizer.cc | 353 | | preparing | 0.000013 | optimize | sql_optimizer.cc | 459 | | executing | 0.000003 | exec | sql_executor.cc | 120 | | Sending data | 0.000060 | exec | sql_executor.cc | 196 | | end | 0.000005 | handle_query | sql_select.cc | 198 | | query end | 0.000007 | mysql_execute_command | sql_parse.cc | 4659 | | closing tables | 0.000007 | mysql_execute_command | sql_parse.cc | 4704 | | freeing items | 0.000009 | mysql_parse | sql_parse.cc | 5274 | | cleaning up | 0.000012 | dispatch_command | sql_parse.cc | 1700 | +----------------------+----------+-----------------------+----------------------+-------------+ 8 MySQL State Message ??
Query handler Parser?? Query Handler?? Parser sql_parse.cc dispatch_command() : COMMAND ? ?? ?? ¡­ COM_STMT_PREPARE COM_STMT_EXECUTE COM_STMT_CLOSE COM_QUERY COM_PROCESS_INFO COM_SET_OPTION ¡­ sql_select.cc Handler for SELECT, INSERT ¡­ SELECT Multi Table Update Multi Table Delete sql_update.cc sql_delete.cc sql_insert.cc sql_optimizer.cc sql_executor.cc sql_base.cc 9 MySQL State Message ??
Parser?? ???? Message sql_parse.cc dispatch_command() sql_parse.cc mysql_parse() sql_parse.cc mysql_execute_command() COM_QUERY? ?? starting Statement ??? Query Handler ?? closing tables cleaning up opening tables sql_base.cc open_tables() close_thread_tables() query end 10 MySQL State Message ?? freeing items
Query Handler?? ???? Message #1 sql_select.cc handle_query() sql_optimizer.cc JOIN::optimize() sql_execuor.cc JOIN::exec() init executing sending data end optimizing statistics preparing SELECT INSERT ¡­ SELECT Multi Table Update Multi Table Delete lock.cc mysql_lock_tables() system lock 11 MySQL State Message ??
Query Handler?? ???? Message #2 sql_updatet.cc mysql_update() sql_delete.cc mysql_delete() sql_insert.cc mysql_insert() init update Single Update Single Delete Insert end init updating end init searching rows for update end lock.cc mysql_lock_tables() system lock updating 12 MySQL State Message ??
State Message? ?? Fencepost? ?? ? ?? ???? ??? ??? ??? ??? ??? ??? ?? ?? ? MySQL code ?? ???? ?? ?? ??? ?????? ??? ?? (??) ?????? ??? ?? ??? http://www.wikitree.co.kr/main/news_view.php?id=78452 Oracle - Oracle Wait Interface (OWI) Class Wait Event Duration ??? ?????_?? 30? ??? ?????_??? 20? ???? ?????? 10? ???? ????_???? 1?? 30? ???? ????_???? 3?? 30? MySQL - State Message State Duration ?? 1?? 10? ?? 1?? 15? ?? 1?? 00? ?? 2?? 15? ?? 1?? 20? ? ??? ??? ??? ? ? ?? 13 MySQL State Message ??
State Message? ?? Fencepost? ?? ? ?) LOAD DATA ??? ???? handler(sql_load.cc)??? State ??? ???? ?? mysql> LOAD DATA INFILE £§/MYSQL/test.txt£§ INTO TABLE my_test_table; Query OK, 1000000 rows affected (35.42 sec) Records: 1000000 Deleted: 0 Skipped: 0 Warnings: 0 mysql> show profile for query 1; +------------------------------+-----------+ | Status | Duration | +------------------------------+-----------+ | starting | 0.000250 | | checking permissions | 0.000019 | | Opening tables | 0.000044 | | System lock | 35.417423 | ? ??? ???? ???? | query end | 0.000012 | 1) System Lock(external table lock) ??? 35? ??? | closing tables | 0.000016 | 2) System lock ?? ?? ???? query end ?? ?? | freeing items | 0.000035 | ???? 35? ??? (?? ??? ?? ??) | cleaning up | 0.000009 | +------------------------------+-----------+ 14 MySQL State Message ??
State Message? ?? MySQL Reference Manual? ??? ??? ?? ???? ?? ? system lock The thread is going to request or is waiting for an internal or external system lock for the table. If this state is being caused by requests for external locks and you are not using multiple mysqld servers that are accessing the same MyISAM tables, you can disable external system locks with the --skip-external-locking option. However, external locking is disabled by default, so it is likely that this option will have no effect. For SHOW PROFILE, this state means the thread is requesting the lock (not waiting for it). ? InnoDB? external locking ?? ??? ?????, MySQL? Table ?? ??? external locking? ??? Storage Engine?? MySQL?? ???? interface? ?? ???? ??, InnoDB ?? ??? ?? mysql_lock_tables() => table_handler->external_lock(thd,locktype) ? ?? external locking ??? ?? ??? ?? Storage Engine?? ????, ???? ?? bypass ?? (?) Memory(Heap) Storage Engine ¨C ha_heap.cc 15 MySQL State Message ??
State Message? ?? MySQL Reference Manual? ??? ??? ?? ???? ?? ? ??? External locking ??? Transaction-aware Storage Engine?? ?? ??? ??? => ?? ??? ???? ??, MySQL??? system lock ??? ??? 16 MySQL State Message ??
???? DML Query? State ?? Review starting opening tables init system lock optimizing statistics preparing executing sending data searching rows for update update end updating query endclosing tablesfreeing items 17 MySQL State Message ?? cleaning up
???? DML Query? State ?? Review starting opening tables init system lock optimizing statistics preparing executing sending data searching rows for update update end updating query endclosing tables ?? ?? query id ?? Parser ?? ??? ?? query ?? ?? ?? ?? status ?? ?? (Questions) Query cache ???? ?? ?? Statement ?? ? handler ?? 18 MySQL State Message ?? freeing items cleaning up The thread has processed one command and is preparing to free memory and reset certain state variables. ====================================================== ?? ?? (?? ??) DB ??? ???? query ?? ?? ?? ? ??? ?? DB ?? ?? Sleep?? ?? The thread has executed a command. Some freeing of items done during this state involves the query cache. This state is usually followed by cleaning up. =========================================================== ?? ?? (Parser ??) Parser ?? ?? ?? ¡ù query cache ?? ??? ??? ??
???? DML Query? State ?? Review starting opening tables init system lock optimizing statistics preparing executing sending data searching rows for update update end updating query endclosing tables 19 MySQL State Message ?? freeing items cleaning up The thread is flushing the changed table data to disk and closing the used tables. This should be a fast operation. If not, verify that you do not have a full disk and that the disk is not in very heavy use. ==================================================== ??? ?? ? ??? table closing ? table open cache? ?? metadata lock ?? ¡ù closing tables ??? ??? ???? ?? Lock_open mutex ?? ??? ?? ¡ù ?? ??? flushing? ???? ?? The thread is trying to open a table. This is should be very fast procedure, unless something prevents opening. For example, an ALTER TABLE or a LOCK TABLE statement can prevent opening a table until the statement is finished. It is also worth checking that your table_open_cache value is large enough. ============================================================== ??? ??? ? ??? ?? Lock Mode ?? (DDL, Lock Table? ?? Metadata Lock ?? ??) Metadata version ?? ? table definition cache ?? table open ¡ù Opening tables ??? ??? ???? ?? MySQL 5.5 ???? Lock_open mutex? table open/close? ?? table open cache ??? ??? ?? ??, ?? ????? ?? ??
???? DML Query? State ?? Review starting opening tables init system lock optimizing statistics preparing executing sending data searching rows for update update end updating query endclosing tables 20 MySQL State Message ?? freeing items cleaning up This state occurs after processing a query but before the freeing items state. ============================================== ?? ?? ?? ?? ?? ????? ???? ???? ?? ?? ¡ù ?? ???? ?? ? ???? ???? ?? ??? query end ??? ???. ?? ???? ? ??? ?? ???? ????, ?? ?? ??? SHOW ENGINE INNODB STATUS ??? undo log record ? ??? ?? ??
???? DML Query? State ?? Review starting opening tables init system lock optimizing statistics preparing executing sending data searching rows for update update end updating query endclosing tables 21 MySQL State Message ?? This occurs at the end but before the cleanup of ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, or UPDATE statements. ============================================= ?? ???? ?? ?? Statement ?? ??? - query cache invalidation - binlog writing ¡ù 5.7 code ???? insert ??? ?? query cache invalidation ? binlog writing? end stage ?? ?? ??? update ???? ??? (feature?? bug?? ?? ??) freeing items cleaning up This occurs before the initialization of ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE statements. Actions taken by the server in this state include flushing the binary log, the InnoDB log, and some query cache cleanup operations. For the end state, the following operations could be happening: - Removing query cache entries after data in a table is changed - Writing an event to the binary log - Freeing memory buffers, including for blobs ========================================================================== ?? ???? ?? ?? ?? ?? ??? ??? ?? ?) ??? ???? ??, ???? ?? ???? ?? (updatable view) ¡ù ?? Flushing ?? ??? ???? ??
???? DML Query? State ?? Review starting opening tables init system lock optimizing statistics preparing executing sending data searching rows for update update end updating query endclosing tables 22 MySQL State Message ?? freeing items cleaning up The thread is going to request or is waiting for an internal or external system lock for the table. If this state is being caused by requests for external locks and you are not using multiple mysqld servers that are accessing the same MyISAM tables, you can disable external system locks with the --skip-external-locking option. However, external locking is disabled by default, so it is likely that this option will have no effect. For SHOW PROFILE, this state means the thread is requesting the lock (not waiting for it). ================================================================================ ? ???? external locking ?? ?, Transactional ????? ?? ?? ??? - ???? ??? statement? ?????? ?? - savepoint ?? ?? ?
???? DML Query? State ?? Review starting opening tables init system lock optimizing statistics preparing executing sending data searching rows for update update end updating query endclosing tables 23 # Logical transformations: - Outer to inner joins transformation. - Equality/constant propagation. - Partition pruning. - COUNT(*), MIN(), MAX() constant substitution in case of implicit grouping. - ORDER BY optimization. # Perform cost-based optimization of table order and access path selection. # Post-join order optimization: - Create optimal table conditions from the where clause and the join conditions. - Inject outer-join guarding conditions. - Adjust data access methods after determining table condition - Optimize ORDER BY/DISTINCT. # Code generation - Set data access functions. - Try to optimize away sorting/distinct. - Setup temporary table usage for grouping and/or sorting. MySQL State Message ?? freeing items cleaning up The server is performing initial optimizations for a query. The server is calculating statistics to develop a query execution plan. If a thread is in this state for a long time, the server is probably disk-bound performing other work. This state occurs during query optimization. ================================================== ?? Rewrite(Transformation) ? ??? Join ? access path ?? ¡ù ??? ??? ???? SQL? statistics ???? ??? ?????, ?? ?? ?? ???? optimizer ? ??? ???? ??? ??? ? ? ??.
???? DML Query? State ?? Review starting opening tables init system lock optimizing statistics preparing executing sending data searching rows for update update end updating query endclosing tables 24 MySQL State Message ?? The thread has begun executing a statement. freeing items cleaning up The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query. ================================================ ?? ??? Client?? ??? ?? ?? Storage Engine?? ?? ? Filter ?? ? ??? ? ?? ???? ??? ??
???? DML Query? State ?? Review starting opening tables init system lock optimizing statistics preparing executing sending data searching rows for update update end updating query endclosing tables 25 MySQL State Message ?? freeing items cleaning up The thread is getting ready to start updating the table. ================================================== INSERT ??? ?? ??? ?? update state? ?? INSERT ?? ????? ??
???? DML Query? State ?? Review starting opening tables init system lock optimizing statistics preparing executing sending data searching rows for update update end updating query endclosing tables 26 The thread is doing a first phase to find all matching rows before updating them. This has to be done if the UPDATE is changing the index that is used to find the involved rows. ================================================== ¡ù searching rows for update state ??? ???? ?, ??? ?? ??? ??? ???? ?? ??? ????. The thread is searching for rows to update and is updating them. ================================================== Update ?? Delete ?? ??? ?? ??? ?? MySQL State Message ?? freeing items cleaning up
MySQL State Message ?? ?? 1) State Message? ?? Statement? profiling? ?? ??? ????. 2) ??? Message ??? ??? ????, ??? ??? ? ? ??. 3) ?? ?? ???? ????, ??? ?? ??. 4) Oracle & MySQL community?? ? ??? ?? ?? ??? ???? ???. (???? ???? ??) 27
?????. 28

More Related Content

MySQL ?? ??? ?? ? ??

  • 1. MySQL State Message ?? 2015.11.28 MySQL Power Group 3? ??? SK Telecom ??? 1
  • 2. MySQL State Message ?? ??? ?? 2003 ~ 2006 ???? ??? Windows + SQL Server ?? 2006 ~ 2014 ???? ? DBA MySQL + Oracle 2014 ~ ???? ?? DBA MySQL + Oracle Nickname : ??? ??? Blog : seuis398.blog.me 2
  • 3. ?? MySQL ?? ??? ?? 1) GLOBAL STATUS (??? ?? ?? ????) Query Type ? ?? ?? InnoDB Buffer pool activity (Logical & Physical Read, Dirty Buffer byte) InnoDB row operations MySQL Handler status Sort ? temporary table(memory, disk) ?? ?? 2) Performance Schema (?? ??? ??? ????) statement/* : Query ??? ?? ??(execution count), 1? ?? ??/?? latency stage/sql/* : ?? statement ?? ??? 1? ?? ??/?? latency wait/io/file/* : ???, ???, ?? ?? ?? ?? IO ??? (??, 1? ?? ??/?? latency) wait/io/table/* : MySQL - Storage Engine ? IO ??? (??, 1? ?? ??/?? latency) ??? ??!!! 3 MySQL State Message ??
  • 4. ?? MySQL State Message ¡°show processlist¡± ?? ¡°show profile¡± ???? ?? ?? ?? performance_schema.events_statements_xxx ?????? ?? ?? MySQL 5.6 ?? 109?, MySQL 5.7 ?? 128?? Message? ?? 4 MySQL State Message ??
  • 5. ?? ?? ????? ????? ?? ??? ? starting, init, preparing? ???? ? end? query end? ???? ? freeing items? cleaning up? ???? ? update, updating? ???? ? MySQL Reference Manual (https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html) 5 MySQL State Message ??
  • 6. ?? ?? MySQL source code? ??? ??!! https://www.github.com/mysql/mysql-server 6 MySQL State Message ??
  • 7. ?? ?? ???? 1) sql parse ???? ?? query handler ??? ???? ??. ??? ???. @_@; 2) MySQL ??? ?? ??? ?? ??? ??? ??? ??? ????. Writing to net => Sending to client 3) State Message? ??? bug? ??. 5.6 ?? ?? prepared statement ??? ???? ?? ?? ??? statistics ??? ??? 4) 5.6 ????? 5.7 ??? ? ?? ??. ??? ?? ?? ??? ??. ?? ?? State Message? ??? ???? ??? ????, State Message? ?? ??? ???? ??. 7 MySQL State Message ??
  • 8. ?? ????? ? SHOW PROFILE SOURCE ???? ?? ??!! ? ½YÕß½âÖ® sql_parser.cc? dispatch_command?? ??!! +----------------------+----------+-----------------------+----------------------+-------------+ | Status | Duration | Source_function | Source_file | Source_line | +----------------------+----------+-----------------------+----------------------+-------------+ | starting | 0.000060 | NULL | NULL | NULL | | checking permissions | 0.000008 | check_access | sql_authorization.cc | 824 | | Opening tables | 0.000017 | open_tables | sql_base.cc | 5522 | | init | 0.000018 | handle_query | sql_select.cc | 121 | | System lock | 0.000010 | mysql_lock_tables | lock.cc | 321 | | optimizing | 0.000005 | optimize | sql_optimizer.cc | 151 | | statistics | 0.000015 | optimize | sql_optimizer.cc | 353 | | preparing | 0.000013 | optimize | sql_optimizer.cc | 459 | | executing | 0.000003 | exec | sql_executor.cc | 120 | | Sending data | 0.000060 | exec | sql_executor.cc | 196 | | end | 0.000005 | handle_query | sql_select.cc | 198 | | query end | 0.000007 | mysql_execute_command | sql_parse.cc | 4659 | | closing tables | 0.000007 | mysql_execute_command | sql_parse.cc | 4704 | | freeing items | 0.000009 | mysql_parse | sql_parse.cc | 5274 | | cleaning up | 0.000012 | dispatch_command | sql_parse.cc | 1700 | +----------------------+----------+-----------------------+----------------------+-------------+ 8 MySQL State Message ??
  • 9. Query handler Parser?? Query Handler?? Parser sql_parse.cc dispatch_command() : COMMAND ? ?? ?? ¡­ COM_STMT_PREPARE COM_STMT_EXECUTE COM_STMT_CLOSE COM_QUERY COM_PROCESS_INFO COM_SET_OPTION ¡­ sql_select.cc Handler for SELECT, INSERT ¡­ SELECT Multi Table Update Multi Table Delete sql_update.cc sql_delete.cc sql_insert.cc sql_optimizer.cc sql_executor.cc sql_base.cc 9 MySQL State Message ??
  • 10. Parser?? ???? Message sql_parse.cc dispatch_command() sql_parse.cc mysql_parse() sql_parse.cc mysql_execute_command() COM_QUERY? ?? starting Statement ??? Query Handler ?? closing tables cleaning up opening tables sql_base.cc open_tables() close_thread_tables() query end 10 MySQL State Message ?? freeing items
  • 11. Query Handler?? ???? Message #1 sql_select.cc handle_query() sql_optimizer.cc JOIN::optimize() sql_execuor.cc JOIN::exec() init executing sending data end optimizing statistics preparing SELECT INSERT ¡­ SELECT Multi Table Update Multi Table Delete lock.cc mysql_lock_tables() system lock 11 MySQL State Message ??
  • 12. Query Handler?? ???? Message #2 sql_updatet.cc mysql_update() sql_delete.cc mysql_delete() sql_insert.cc mysql_insert() init update Single Update Single Delete Insert end init updating end init searching rows for update end lock.cc mysql_lock_tables() system lock updating 12 MySQL State Message ??
  • 13. State Message? ?? Fencepost? ?? ? ?? ???? ??? ??? ??? ??? ??? ??? ?? ?? ? MySQL code ?? ???? ?? ?? ??? ?????? ??? ?? (??) ?????? ??? ?? ??? http://www.wikitree.co.kr/main/news_view.php?id=78452 Oracle - Oracle Wait Interface (OWI) Class Wait Event Duration ??? ?????_?? 30? ??? ?????_??? 20? ???? ?????? 10? ???? ????_???? 1?? 30? ???? ????_???? 3?? 30? MySQL - State Message State Duration ?? 1?? 10? ?? 1?? 15? ?? 1?? 00? ?? 2?? 15? ?? 1?? 20? ? ??? ??? ??? ? ? ?? 13 MySQL State Message ??
  • 14. State Message? ?? Fencepost? ?? ? ?) LOAD DATA ??? ???? handler(sql_load.cc)??? State ??? ???? ?? mysql> LOAD DATA INFILE £§/MYSQL/test.txt£§ INTO TABLE my_test_table; Query OK, 1000000 rows affected (35.42 sec) Records: 1000000 Deleted: 0 Skipped: 0 Warnings: 0 mysql> show profile for query 1; +------------------------------+-----------+ | Status | Duration | +------------------------------+-----------+ | starting | 0.000250 | | checking permissions | 0.000019 | | Opening tables | 0.000044 | | System lock | 35.417423 | ? ??? ???? ???? | query end | 0.000012 | 1) System Lock(external table lock) ??? 35? ??? | closing tables | 0.000016 | 2) System lock ?? ?? ???? query end ?? ?? | freeing items | 0.000035 | ???? 35? ??? (?? ??? ?? ??) | cleaning up | 0.000009 | +------------------------------+-----------+ 14 MySQL State Message ??
  • 15. State Message? ?? MySQL Reference Manual? ??? ??? ?? ???? ?? ? system lock The thread is going to request or is waiting for an internal or external system lock for the table. If this state is being caused by requests for external locks and you are not using multiple mysqld servers that are accessing the same MyISAM tables, you can disable external system locks with the --skip-external-locking option. However, external locking is disabled by default, so it is likely that this option will have no effect. For SHOW PROFILE, this state means the thread is requesting the lock (not waiting for it). ? InnoDB? external locking ?? ??? ?????, MySQL? Table ?? ??? external locking? ??? Storage Engine?? MySQL?? ???? interface? ?? ???? ??, InnoDB ?? ??? ?? mysql_lock_tables() => table_handler->external_lock(thd,locktype) ? ?? external locking ??? ?? ??? ?? Storage Engine?? ????, ???? ?? bypass ?? (?) Memory(Heap) Storage Engine ¨C ha_heap.cc 15 MySQL State Message ??
  • 16. State Message? ?? MySQL Reference Manual? ??? ??? ?? ???? ?? ? ??? External locking ??? Transaction-aware Storage Engine?? ?? ??? ??? => ?? ??? ???? ??, MySQL??? system lock ??? ??? 16 MySQL State Message ??
  • 17. ???? DML Query? State ?? Review starting opening tables init system lock optimizing statistics preparing executing sending data searching rows for update update end updating query endclosing tablesfreeing items 17 MySQL State Message ?? cleaning up
  • 18. ???? DML Query? State ?? Review starting opening tables init system lock optimizing statistics preparing executing sending data searching rows for update update end updating query endclosing tables ?? ?? query id ?? Parser ?? ??? ?? query ?? ?? ?? ?? status ?? ?? (Questions) Query cache ???? ?? ?? Statement ?? ? handler ?? 18 MySQL State Message ?? freeing items cleaning up The thread has processed one command and is preparing to free memory and reset certain state variables. ====================================================== ?? ?? (?? ??) DB ??? ???? query ?? ?? ?? ? ??? ?? DB ?? ?? Sleep?? ?? The thread has executed a command. Some freeing of items done during this state involves the query cache. This state is usually followed by cleaning up. =========================================================== ?? ?? (Parser ??) Parser ?? ?? ?? ¡ù query cache ?? ??? ??? ??
  • 19. ???? DML Query? State ?? Review starting opening tables init system lock optimizing statistics preparing executing sending data searching rows for update update end updating query endclosing tables 19 MySQL State Message ?? freeing items cleaning up The thread is flushing the changed table data to disk and closing the used tables. This should be a fast operation. If not, verify that you do not have a full disk and that the disk is not in very heavy use. ==================================================== ??? ?? ? ??? table closing ? table open cache? ?? metadata lock ?? ¡ù closing tables ??? ??? ???? ?? Lock_open mutex ?? ??? ?? ¡ù ?? ??? flushing? ???? ?? The thread is trying to open a table. This is should be very fast procedure, unless something prevents opening. For example, an ALTER TABLE or a LOCK TABLE statement can prevent opening a table until the statement is finished. It is also worth checking that your table_open_cache value is large enough. ============================================================== ??? ??? ? ??? ?? Lock Mode ?? (DDL, Lock Table? ?? Metadata Lock ?? ??) Metadata version ?? ? table definition cache ?? table open ¡ù Opening tables ??? ??? ???? ?? MySQL 5.5 ???? Lock_open mutex? table open/close? ?? table open cache ??? ??? ?? ??, ?? ????? ?? ??
  • 20. ???? DML Query? State ?? Review starting opening tables init system lock optimizing statistics preparing executing sending data searching rows for update update end updating query endclosing tables 20 MySQL State Message ?? freeing items cleaning up This state occurs after processing a query but before the freeing items state. ============================================== ?? ?? ?? ?? ?? ????? ???? ???? ?? ?? ¡ù ?? ???? ?? ? ???? ???? ?? ??? query end ??? ???. ?? ???? ? ??? ?? ???? ????, ?? ?? ??? SHOW ENGINE INNODB STATUS ??? undo log record ? ??? ?? ??
  • 21. ???? DML Query? State ?? Review starting opening tables init system lock optimizing statistics preparing executing sending data searching rows for update update end updating query endclosing tables 21 MySQL State Message ?? This occurs at the end but before the cleanup of ALTER TABLE, CREATE VIEW, DELETE, INSERT, SELECT, or UPDATE statements. ============================================= ?? ???? ?? ?? Statement ?? ??? - query cache invalidation - binlog writing ¡ù 5.7 code ???? insert ??? ?? query cache invalidation ? binlog writing? end stage ?? ?? ??? update ???? ??? (feature?? bug?? ?? ??) freeing items cleaning up This occurs before the initialization of ALTER TABLE, DELETE, INSERT, SELECT, or UPDATE statements. Actions taken by the server in this state include flushing the binary log, the InnoDB log, and some query cache cleanup operations. For the end state, the following operations could be happening: - Removing query cache entries after data in a table is changed - Writing an event to the binary log - Freeing memory buffers, including for blobs ========================================================================== ?? ???? ?? ?? ?? ?? ??? ??? ?? ?) ??? ???? ??, ???? ?? ???? ?? (updatable view) ¡ù ?? Flushing ?? ??? ???? ??
  • 22. ???? DML Query? State ?? Review starting opening tables init system lock optimizing statistics preparing executing sending data searching rows for update update end updating query endclosing tables 22 MySQL State Message ?? freeing items cleaning up The thread is going to request or is waiting for an internal or external system lock for the table. If this state is being caused by requests for external locks and you are not using multiple mysqld servers that are accessing the same MyISAM tables, you can disable external system locks with the --skip-external-locking option. However, external locking is disabled by default, so it is likely that this option will have no effect. For SHOW PROFILE, this state means the thread is requesting the lock (not waiting for it). ================================================================================ ? ???? external locking ?? ?, Transactional ????? ?? ?? ??? - ???? ??? statement? ?????? ?? - savepoint ?? ?? ?
  • 23. ???? DML Query? State ?? Review starting opening tables init system lock optimizing statistics preparing executing sending data searching rows for update update end updating query endclosing tables 23 # Logical transformations: - Outer to inner joins transformation. - Equality/constant propagation. - Partition pruning. - COUNT(*), MIN(), MAX() constant substitution in case of implicit grouping. - ORDER BY optimization. # Perform cost-based optimization of table order and access path selection. # Post-join order optimization: - Create optimal table conditions from the where clause and the join conditions. - Inject outer-join guarding conditions. - Adjust data access methods after determining table condition - Optimize ORDER BY/DISTINCT. # Code generation - Set data access functions. - Try to optimize away sorting/distinct. - Setup temporary table usage for grouping and/or sorting. MySQL State Message ?? freeing items cleaning up The server is performing initial optimizations for a query. The server is calculating statistics to develop a query execution plan. If a thread is in this state for a long time, the server is probably disk-bound performing other work. This state occurs during query optimization. ================================================== ?? Rewrite(Transformation) ? ??? Join ? access path ?? ¡ù ??? ??? ???? SQL? statistics ???? ??? ?????, ?? ?? ?? ???? optimizer ? ??? ???? ??? ??? ? ? ??.
  • 24. ???? DML Query? State ?? Review starting opening tables init system lock optimizing statistics preparing executing sending data searching rows for update update end updating query endclosing tables 24 MySQL State Message ?? The thread has begun executing a statement. freeing items cleaning up The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query. ================================================ ?? ??? Client?? ??? ?? ?? Storage Engine?? ?? ? Filter ?? ? ??? ? ?? ???? ??? ??
  • 25. ???? DML Query? State ?? Review starting opening tables init system lock optimizing statistics preparing executing sending data searching rows for update update end updating query endclosing tables 25 MySQL State Message ?? freeing items cleaning up The thread is getting ready to start updating the table. ================================================== INSERT ??? ?? ??? ?? update state? ?? INSERT ?? ????? ??
  • 26. ???? DML Query? State ?? Review starting opening tables init system lock optimizing statistics preparing executing sending data searching rows for update update end updating query endclosing tables 26 The thread is doing a first phase to find all matching rows before updating them. This has to be done if the UPDATE is changing the index that is used to find the involved rows. ================================================== ¡ù searching rows for update state ??? ???? ?, ??? ?? ??? ??? ???? ?? ??? ????. The thread is searching for rows to update and is updating them. ================================================== Update ?? Delete ?? ??? ?? ??? ?? MySQL State Message ?? freeing items cleaning up
  • 27. MySQL State Message ?? ?? 1) State Message? ?? Statement? profiling? ?? ??? ????. 2) ??? Message ??? ??? ????, ??? ??? ? ? ??. 3) ?? ?? ???? ????, ??? ?? ??. 4) Oracle & MySQL community?? ? ??? ?? ?? ??? ???? ???. (???? ???? ??) 27