Pantek Library
Hosting Provided By
CybrHost
High Speed Hosting
Mailing Lists: lists.mysql.com > commits > 07 > 060667.html (Request Expert lists.mysql.com Support)

svn commit - mysqldoc@docsrva: r6973 - in trunk: . refman-4.1 refman-5.0 refman-5.1 refman-5.2

From: <paul(at)mysql.com>
Date: Fri Jun 29 2007 - 19:50:37 EDT


Author: paul
Date: 2007-06-30 01:49:51 +0200 (Sat, 30 Jun 2007) New Revision: 6973

Log:
 r22296@frost: paul | 2007-06-29 17:11:30 -0500  Move general thread states from SQL chapter to thread information  section of optimization chapter.

Modified: 

   trunk/refman-4.1/optimization.xml
   trunk/refman-4.1/sql-syntax.xml
   trunk/refman-5.0/optimization.xml
   trunk/refman-5.0/sql-syntax.xml
   trunk/refman-5.1/optimization.xml
   trunk/refman-5.1/sql-syntax.xml
   trunk/refman-5.2/optimization.xml
   trunk/refman-5.2/sql-syntax.xml

Property changes on: trunk


Name: svk:merge
  • 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:27013 7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:22290 b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218 bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:18307 + 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:27013 7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:22296 b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218 bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:18307

Modified: trunk/refman-4.1/optimization.xml

  • trunk/refman-4.1/optimization.xml 2007-06-29 23:47:25 UTC (rev 6972) +++ trunk/refman-4.1/optimization.xml 2007-06-29 23:49:51 UTC (rev 6973) Changed blocks: 3, Lines Added: 587, Lines Deleted: 2; 21356 bytes

@@ -8732,6 +8732,12 @@

             Command and State
             indicate what the thread is doing.
           
+
+          
+            Most states correspond to very quick operations. If a thread
+            stays in a given state for many seconds, there might be a
+            problem that needs to be investigated.
+          
         
 
         

@@ -8763,8 +8769,9 @@

         Command values, and State
         values grouped by category. The meaning for some of these values
         is self-evident. For others, additional description is provided.
-        Additional State values are given in
-        .
+        See also ,
+        for additional information about thread states for replication
+        servers.

@@ -9410,6 +9417,584 @@
 

       </section>   

+      

+
+        General Thread States
+
+        
+          thread states
+          general
+        
+
+        
+          The following list describes thread State
+          values that are associated with general query processing and
+          not more specialized activities such as replication. Many of
+          these are useful only for finding bugs in the server.

Do you need more help?X

+        
+
+        
+          After create, allocating local
+          table, Analyzing,
+          Changing master, Checking master
+          version, Checking table,
+          cleaning up, closing
+          tables, Connecting to master,
+          converting HEAP to MyISAM, copy to
+          tmp table, Copying to group
+          table, Copying to tmp table,
+          Copying to tmp table on disk,
+          Creating delayed handler, Creating
+          index, Creating sort index,
+          Creating table from master dump,
+          creating table, Creating tmp
+          table, deleting from main table,
+          deleting from reference tables,
+          discard_or_import_tablespace,
+          end, Execution of
+          init_command, Finished reading one binlog;
+          switching to next binlog, Flushing
+          tables, freeing items,
+          FULLTEXT initialization, got
+          handler lock, got old table,
+          Has read all relay log; waiting for the slave I/O
+          thread to update it, Has sent all binlog to
+          slave; waiting for binlog to be updated,

Can we help you?X

+          info, init,
+          insert, Killed,
+          Killing slave, Locked,
+          logging slow query,
+          login, Making temp file
+          , Opening master dump table,
+          Opening table, Opening
+          tables, preparing,
+          Processing request, Purging old
+          relay logs, query end,
+          Queueing master event to the relay log,
+          Reading event from the relay log,
+          Reading from net, Reading master
+          dump table data, Rebuilding the index on
+          master dump table, Reconnecting after a
+          failed binlog dump request, Reconnecting
+          after a failed master event read,
+          Registering slave on master,
+          Removing duplicates, removing tmp
+          table, rename, rename
+          result table, Reopen tables,
+          Repair by sorting, Repair
+          done, Repair with keycache,
+          Requesting binlog dump,
+          reschedule, Rolling
+          back, Saving state,
+          Searching rows for update, Sending

Can't find what you're looking for?X

+          binlog event to slave, Sending
+          data, setup, Sorting for
+          group, Sorting for order,
+          Sorting index, Sorting
+          result, starting slave,
+          statistics, storing row into
+          queue, System lock,
+          Table lock, Thread
+          initialized, update,
+          Updating, updating main
+          table, updating reference tables,
+          upgrading lock, User
+          lock, waiting for delay_list,
+          Waiting for event from ndbcluster,
+          waiting for handler insert,
+          waiting for handler lock, waiting
+          for handler open, Waiting for
+          INSERT, Waiting for master to send
+          event, Waiting for master update,
+          Waiting for slave mutex on exit,
+          Waiting for table, Waiting for
+          tables, Waiting for the next event in relay
+          log, Waiting for the slave SQL thread to
+          free enough relay log space, Waiting on
+          cond, Waiting to finalize
+          termination, Waiting to reconnect after a
+          failed binlog dump request, Waiting to

Don't know where to look next?X

+          reconnect after a failed master event read,
+          Writing to net
+        
+
+        
+
+          
+            
+              Checking table
+            
+
+            
+              The thread is performing a table check operation.
+            
+          
+
+          
+            
+              closing tables
+            
+
+            
+              Means that the thread is flushing the changed table data
+              to disk and closing the used tables. This should be a fast
+              operation. If not, you should verify that you do not have
+              a full disk and that the disk is not in very heavy use.
+            
+          
+
+          
+            
+              converting HEAP to MyISAM
+            
+
+            
+              The thread is converting an internal temporary table from
+              a MEMORY table to an on-disk
+              MyISAM table.
+            
+          
+
+          
+            
+              Copying to group table
+            
+
+            
+              If a statement has different ORDER BY
+              and GROUP BY criteria, the rows are
+              sorted by group and copied to a temporary table.
+            
+          
+
+          
+            
+              Copying to tmp table
+            
+
+            
+              The server is copying to a temporary table in memory.

Confused? Frustrated?X

+            
+          
+
+          
+            
+              Copying to tmp table on disk
+            
+
+            
+              The server is copying to a temporary table on disk. The
+              temporary result set was larger than
+              tmp_table_size and the thread is
+              changing the temporary table from in-memory to disk-based
+              format to save memory.
+            
+          
+
+          
+            
+              Creating delayed handler
+            
+
+            
+              The thread is creating a handler for
+              DELAYED inserts.
+            
+          
+
+          
+            
+              Creating tmp table
+            
+
+            
+              The thread is creating a temporary table in memory or on
+              disk. If the table is created in memory but later is
+              converted to an on-disk table, the state during that
+              operation will be Copying to tmp table on
+              disk.
+            
+          
+
+          
+            
+              deleting from main table
+            
+
+            
+              The server is executing the first part of a multiple-table
+              delete. It is deleting only from the first table, and
+              saving fields and offsets to be used for deleting from the
+              other (reference) tables.
+            
+          

Call Pantek today for Open Source Technical Support at 1-877-546-8934 - 24/7/365X

+
+          
+            
+              deleting from reference tables
+            
+
+            
+              The server is executing the second part of a
+              multiple-table delete and deleting the matched rows from
+              the other tables.
+            
+          
+
+          
+            
+              Flushing tables
+            
+
+            
+              The thread is executing FLUSH TABLES
+              and is waiting for all threads to close their tables.
+            
+          
+
+          
+            
+              FULLTEXT initialization
+            
+
+            
+              The server is preparing to perform a natural-language
+              full-text search.
+            
+          
+
+          
+            
+              Killed
+            
+
+            
+              Someone has sent a KILL statement to
+              the thread and it should abort next time it checks the
+              kill flag. The flag is checked in each major loop in
+              MySQL, but in some cases it might still take a short time
+              for the thread to die. If the thread is locked by some
+              other thread, the kill takes effect as soon as the other
+              thread releases its lock.
+            
+          
+
+          
+            
+              Locked
+            
+
+            
+              The query is locked by another query.
+            

Do you need help?X

+          
+
+          
+            
+              Opening tables
+            
+
+            
+              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.
+            
+          
+
+          
+            
+              Purging old relay logs
+            
+
+            
+              The thread is removing unneeded relay log files.
+            
+          
+
+          
+            
+              Reading from net
+            
+
+            
+              The server is reading a packet from the network.
+            
+          
+
+          
+            
+              Removing duplicates
+            
+
+            
+              The query was using SELECT DISTINCT in
+              such a way that MySQL could not optimize away the distinct
+              operation at an early stage. Because of this, MySQL
+              requires an extra stage to remove all duplicated rows
+              before sending the result to the client.
+            
+          
+
+          
+            
+              rename
+            
+
+            
+              The thread is renaming a table.
+            
+          
+
+          

Do you need more help?X

+            
+              Reopen tables
+            
+
+            
+              The thread got a lock for the table, but noticed after
+              getting the lock that the underlying table structure
+              changed. It has freed the lock, closed the table, and is
+              trying to reopen it.
+            
+          
+
+          
+            
+              Repair by sorting
+            
+
+            
+              The repair code is using a sort to create indexes.
+            
+          
+
+          
+            
+              Repair with keycache
+            
+
+            
+              The repair code is using creating keys one by one through
+              the key cache. This is much slower than Repair by
+              sorting.
+            
+          
+
+          
+            
+              Searching rows for update
+            
+
+            
+              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.
+            
+          
+
+          
+            
+              Sending data
+            
+
+            
+              The thread is processing rows for a
+              SELECT statement and also is sending
+              data to the client.
+            
+          
+
+          

Can we help you?X

+            
+              Sorting for group
+            
+
+            
+              The thread is doing a sort to satisfy a GROUP
+              BY.
+            
+          
+
+          
+            
+              Sorting for order
+            
+
+            
+              The thread is doing a sort to satisfy a ORDER
+              BY.
+            
+          
+
+          
+            
+              statistics
+            
+
+            
+              The server is calculating statistics to develop a query
+              execution plan.
+            
+          
+
+          
+            
+              
+                external locking
+              
+
+              
+                locking
+                external
+              
+
+              System lock
+            
+
+            
+              The thread is is going to request or is waiting for an
+              external system lock for the table. If you are not using
+              multiple mysqld servers that are
+              accessing the same tables, you can disable system locks
+              with the  option.
+            
+          
+
+          
+            
+              Table lock
+            
+
+            
+              The next thread state after System
+              lock. The thread has acquired an external lock

Can't find what you're looking for?X

+              and is going to request an internal table lock.
+            
+          
+
+          
+            
+              Updating
+            
+
+            
+              The thread is searching for rows to update and is updating
+              them.
+            
+          
+
+          
+            
+              updating main table
+            
+
+            
+              The server is executing the first part of a multiple-table
+              update. It is updating only the first table, and saving
+              fields and offsets to be used for updating the other
+              (reference) tables.
+            
+          
+
+          
+            
+              updating reference tables
+            
+
+            
+              The server is executing the second part of a
+              multiple-table update and updating the matched rows from
+              the other tables.
+            
+          
+
+          
+            
+              upgrading lock
+            
+
+            
+              An INSERT DELAYED handler is trying to
+              get a lock for the table to insert rows.
+            
+          
+
+          
+            
+              User lock
+            
+
+            
+              The thread is going to request or is waiting for an
+              advisory lock requested with a
+              GET_LOCK() call.

Don't know where to look next?X

+            
+          
+
+          
+            
+              Waiting for event from ndbcluster
+            
+
+            
+              The server is acting as an SQL node in a MySQL Cluster,
+              and is connected to a cluster management node.
+            
+          
+
+          
+            
+              waiting for handler insert
+            
+
+            
+              An INSERT DELAYED handler has processed
+              all pending inserts and is waiting for new ones.
+            
+          
+
+          
+            
+              Waiting for tables
+            
+
+            
+              The thread got a notification that the underlying
+              structure for a table has changed and it needs to reopen
+              the table to get the new structure. However, to reopen the
+              table, it must wait until all other threads have closed
+              the table in question.
+            
+
+            
+              This notification takes place if another thread has used
+              FLUSH TABLES or one of the following
+              statements on the table in question: FLUSH TABLES
+              tbl_name,
+              ALTER TABLE, RENAME
+              TABLE, REPAIR TABLE,
+              ANALYZE TABLE, or OPTIMIZE
+              TABLE.
+            
+          
+
+          

Confused? Frustrated?X

+            
+              Writing to net
+            
+
+            
+              The server is writing a packet to the network.
+            
+          
+
+        
+
+      

+
Do you need help?X

Modified: trunk/refman-4.1/sql-syntax.xml

  • trunk/refman-4.1/sql-syntax.xml 2007-06-29 23:47:25 UTC (rev 6972) +++ trunk/refman-4.1/sql-syntax.xml 2007-06-29 23:49:51 UTC (rev 6973) Changed blocks: 3, Lines Added: 10, Lines Deleted: 620; 23227 bytes

@@ -16364,9 +16364,10 @@

             </para>   

             
-              The type of command the thread is executing. The values
-              are described in . The
-              value of this column corresponds to the
+              The type of command the thread is executing. Descriptions
+              for thread commands can be found at
+              . The value of this
+              column corresponds to the
               COM_xxx
               commands of the client/server protocol. See
               

@@ -16390,107 +16391,15 @@

             
 
             
-              An action, event, or state, which can be one of the
-              following: After create,
-              allocating local table,
-              Analyzing, Changing
-              master, Checking master
-              version, Checking table,
-              cleaning up, closing
-              tables, Connecting to master,
-              converting HEAP to MyISAM,
-              copy to tmp table, Copying to
-              group table, Copying to tmp
-              table, Copying to tmp table on

Call Pantek today for Open Source Technical Support at 1-877-546-8934 - 24/7/365X

-              disk, Creating delayed
-              handler, Creating index,
-              Creating sort index, Creating
-              table from master dump, creating
-              table, Creating tmp table,
-              deleting from main table,
-              deleting from reference tables,
-              discard_or_import_tablespace,
-              end, Execution of
-              init_command, Finished reading one
-              binlog; switching to next binlog,
-              Flushing tables, freeing
-              items, FULLTEXT
-              initialization, got handler
-              lock, got old table,
-              Has read all relay log; waiting for the slave I/O
-              thread to update it, Has sent all
-              binlog to slave; waiting for binlog to be
-              updated, info,
-              init, insert,
-              Killed, Killing
-              slave, Locked,
-              logging slow query,
-              login, Making temp file
-              , Opening master dump table,
-              Opening table, Opening
-              tables, preparing,
-              Processing request, Purging

Do you need help?X

-              old relay logs, query end,
-              Queueing master event to the relay log,
-              Reading event from the relay log,
-              Reading from net, Reading
-              master dump table data, Rebuilding the
-              index on master dump table,
-              Reconnecting after a failed binlog dump
-              request, Reconnecting after a failed
-              master event read, Registering slave on
-              master, Removing duplicates,
-              removing tmp table,
-              rename, rename result
-              table, Reopen tables,
-              Repair by sorting, Repair
-              done, Repair with keycache,
-              Requesting binlog dump,
-              reschedule, Rolling
-              back, Saving state,
-              Searching rows for update,
-              Sending binlog event to slave,
-              Sending data, setup,
-              Sorting for group, Sorting for
-              order, Sorting index,
-              Sorting result, starting
-              slave, statistics,
-              storing row into queue, System

Do you need more help?X

-              lock, Table lock,
-              Thread initialized,
-              unauthenticated user,
-              update, Updating,
-              updating main table, updating
-              reference tables, upgrading
-              lock, User lock,
-              waiting for delay_list,
-              Waiting for event from ndbcluster,
-              waiting for handler insert,
-              waiting for handler lock,
-              waiting for handler open,
-              Waiting for INSERT, Waiting
-              for master to send event, Waiting for
-              master update, Waiting for slave mutex
-              on exit, Waiting for table,
-              Waiting for tables, Waiting
-              for the next event in relay log,
-              Waiting for the slave SQL thread to free enough
-              relay log space, Waiting on
-              cond, Waiting to finalize
-              termination, Waiting to reconnect after
-              a failed binlog dump request, Waiting
-              to reconnect after a failed master event read,
-              Writing to net
+              An action, event, or state that indicates what the thread
+              is doing. Descriptions for State values

Can we help you?X

+              can be found at .
             
 
             
-              The most common State values are
-              described in the rest of this section. Most of the other
-              State values are useful only for
-              finding bugs in the server. See also
-              , for
-              additional information about process states for
-              replication servers.
+              Most states correspond to very quick operations. If a
+              thread stays in a given state for many seconds, there
+              might be a problem that needs to be investigated.

@@ -16514,525 +16423,6 @@
 

         </itemizedlist>  

  • <para>
  • Some <literal>State</literal> values commonly seen in the
  • output from <literal>SHOW PROCESSLIST</literal>:
  • </para> -
  • <itemizedlist> -
  • <listitem>
  • <para>
  • <literal>Checking table</literal>
  • </para> -
  • <para>
  • The thread is performing a table check operation.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>closing tables</literal>
  • </para> -
  • <para>
  • Means that the thread is flushing the changed table data
  • to disk and closing the used tables. This should be a fast
  • operation. If not, you should verify that you do not have
  • a full disk and that the disk is not in very heavy use.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>Connect Out</literal>
  • </para> -
  • <para>
  • A replication slave is connecting to its master.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>converting HEAP to MyISAM</literal>
  • </para> -
  • <para>
  • The thread is converting an internal temporary table from
  • a <literal>MEMORY</literal> table to an on-disk
  • <literal>MyISAM</literal> table.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>Copying to group table</literal>
  • </para> -
  • <para>
  • If a statement has different <literal>ORDER BY</literal>
  • and <literal>GROUP BY</literal> criteria, the rows are
  • sorted by group and copied to a temporary table.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>Copying to tmp table</literal>
  • </para> -
  • <para>
  • The server is copying to a temporary table in memory.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>Copying to tmp table on disk</literal>
  • </para> -
  • <para>
  • The server is copying to a temporary table on disk. The
  • temporary result set was larger than
  • <literal>tmp_table_size</literal> and the thread is
  • changing the temporary table from in-memory to disk-based
  • format to save memory.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>Creating delayed handler</literal>
  • </para> -
  • <para>
  • The thread is creating a handler for
  • <literal>DELAYED</literal> inserts.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>Creating tmp table</literal>
  • </para> -
  • <para>
  • The thread is creating a temporary table in memory or on
  • disk. If the table is created in memory but later is
  • converted to an on-disk table, the state during that
  • operation will be <literal>Copying to tmp table on
  • disk</literal>.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>deleting from main table</literal>
  • </para> -
  • <para>
  • The server is executing the first part of a multiple-table
  • delete. It is deleting only from the first table, and
  • saving fields and offsets to be used for deleting from the
  • other (reference) tables.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>deleting from reference tables</literal>
  • </para> -
  • <para>
  • The server is executing the second part of a
  • multiple-table delete and deleting the matched rows from
  • the other tables.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>Flushing tables</literal>
  • </para> -
  • <para>
  • The thread is executing <literal>FLUSH TABLES</literal>
  • and is waiting for all threads to close their tables.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>FULLTEXT initialization</literal>
  • </para> -
  • <para>
  • The server is preparing to perform a natural-language
  • full-text search.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>Killed</literal>
  • </para> -
  • <para>
  • Someone has sent a <literal>KILL</literal> statement to
  • the thread and it should abort next time it checks the
  • kill flag. The flag is checked in each major loop in
  • MySQL, but in some cases it might still take a short time
  • for the thread to die. If the thread is locked by some
  • other thread, the kill takes effect as soon as the other
  • thread releases its lock.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>Locked</literal>
  • </para> -
  • <para>
  • The query is locked by another query.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>Opening tables</literal>
  • </para> -
  • <para>
  • The thread is trying to open a table. This is should be
  • very fast procedure, unless something prevents opening.
  • For example, an <literal>ALTER TABLE</literal> or a
  • <literal>LOCK TABLE</literal> statement can prevent
  • opening a table until the statement is finished.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>Purging old relay logs</literal>
  • </para> -
  • <para>
  • The thread is removing unneeded relay log files.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>Reading from net</literal>
  • </para> -
  • <para>
  • The server is reading a packet from the network.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>Removing duplicates</literal>
  • </para> -
  • <para>
  • The query was using <literal>SELECT DISTINCT</literal> in
  • such a way that MySQL could not optimize away the distinct
  • operation at an early stage. Because of this, MySQL
  • requires an extra stage to remove all duplicated rows
  • before sending the result to the client.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>rename</literal>
  • </para> -
  • <para>
  • The thread is renaming a table.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>Reopen tables</literal>
  • </para> -
  • <para>
  • The thread got a lock for the table, but noticed after
  • getting the lock that the underlying table structure
  • changed. It has freed the lock, closed the table, and is
  • trying to reopen it.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>Repair by sorting</literal>
  • </para> -
  • <para>
  • The repair code is using a sort to create indexes.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>Repair with keycache</literal>
  • </para> -
  • <para>
  • The repair code is using creating keys one by one through
  • the key cache. This is much slower than <literal>Repair by
  • sorting</literal>.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>Searching rows for update</literal>
  • </para> -
  • <para>
  • The thread is doing a first phase to find all matching
  • rows before updating them. This has to be done if the
  • <literal>UPDATE</literal> is changing the index that is
  • used to find the involved rows.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>Sending data</literal>
  • </para> -
  • <para>
  • The thread is processing rows for a
  • <literal>SELECT</literal> statement and also is sending
  • data to the client.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>Sleeping</literal>
  • </para> -
  • <para>
  • The thread is waiting for the client to send a new
  • statement to it.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>Sorting for group</literal>
  • </para> -
  • <para>
  • The thread is doing a sort to satisfy a <literal>GROUP
  • BY</literal>.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>Sorting for order</literal>
  • </para> -
  • <para>
  • The thread is doing a sort to satisfy a <literal>ORDER
  • BY</literal>.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>statistics</literal>
  • </para> -
  • <para>
  • The server is calculating statistics to develop a query
  • execution plan.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <indexterm>
  • <primary>external locking</primary>
  • </indexterm> -
  • <indexterm>
  • <primary>locking</primary>
  • <secondary>external</secondary>
  • </indexterm> -
  • <literal>System lock</literal>
  • </para> -
  • <para>
  • The thread is is going to request or is waiting for an
  • external system lock for the table. If you are not using
  • multiple <command>mysqld</command> servers that are
  • accessing the same tables, you can disable system locks
  • with the <option>--skip-external-locking</option> option.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>Table lock</literal>
  • </para> -
  • <para>
  • The next thread state after <literal>System
  • lock</literal>. The thread has acquired an external lock
  • and is going to request an internal table lock.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>unauthenticated user</literal>
  • </para> -
  • <para>
  • The state of a thread that has become associated with a
  • client connection but for which authentication of the
  • client user has not yet been done.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>Updating</literal>
  • </para> -
  • <para>
  • The thread is searching for rows to update and is updating
  • them.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>updating main table</literal>
  • </para> -
  • <para>
  • The server is executing the first part of a multiple-table
  • update. It is updating only the first table, and saving
  • fields and offsets to be used for updating the other
  • (reference) tables.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>updating reference tables</literal>
  • </para> -
  • <para>
  • The server is executing the second part of a
  • multiple-table update and updating the matched rows from
  • the other tables.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>upgrading lock</literal>
  • </para> -
  • <para>
  • An <literal>INSERT DELAYED</literal> handler is trying to
  • get a lock for the table to insert rows.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>User lock</literal>
  • </para> -
  • <para>
  • The thread is going to request or is waiting for an
  • advisory lock requested with a
  • <literal>GET_LOCK()</literal> call.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>Waiting for event from ndbcluster</literal>
  • </para> -
  • <para>
  • The server is acting as an SQL node in a MySQL Cluster,
  • and is connected to a cluster management node.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>waiting for handler insert</literal>
  • </para> -
  • <para>
  • An <literal>INSERT DELAYED</literal> handler has processed
  • all pending inserts and is waiting for new ones.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>Waiting for tables</literal>
  • </para> -
  • <para>
  • The thread got a notification that the underlying
  • structure for a table has changed and it needs to reopen
  • the table to get the new structure. However, to reopen the
  • table, it must wait until all other threads have closed
  • the table in question.
  • </para> -
  • <para>
  • This notification takes place if another thread has used
  • <literal>FLUSH TABLES</literal> or one of the following
  • statements on the table in question: <literal>FLUSH TABLES
  • <replaceable>tbl_name</replaceable></literal>,
  • <literal>ALTER TABLE</literal>, <literal>RENAME
  • TABLE</literal>, <literal>REPAIR TABLE</literal>,
  • <literal>ANALYZE TABLE</literal>, or <literal>OPTIMIZE
  • TABLE</literal>.
  • </para>
  • </listitem> -
  • <listitem>
  • <para>
  • <literal>Writing to net</literal>
  • </para> -
  • <para>
  • The server is writing a packet to the network.
  • </para>
  • </listitem> -
  • </itemizedlist> -
  • <para>
  • Most states correspond to very quick operations. If a thread
  • stays in any of these states for many seconds, there might be
  • a problem that needs to be investigated.
  • </para> - </section>

       <section id="show-status">

Modified: trunk/refman-5.0/optimization.xml

  • trunk/refman-5.0/optimization.xml 2007-06-29 23:47:25 UTC (rev 6972) +++ trunk/refman-5.0/optimization.xml 2007-06-29 23:49:51 UTC (rev 6973) Changed blocks: 3, Lines Added: 587, Lines Deleted: 2; 21362 bytes

@@ -11027,6 +11027,12 @@

             Command and State
             indicate what the thread is doing.
           
+
+          
+            Most states correspond to very quick operations. If a thread
+            stays in a given state for many seconds, there might be a
+            problem that needs to be investigated.
+          
         
 
         

@@ -11058,8 +11064,9 @@

         Command values, and State
         values grouped by category. The meaning for some of these values
         is self-evident. For others, additional description is provided.
-        Additional State values are given in
-        .
+        See also ,
+        for additional information about thread states for replication
+        servers.
       

Don't know where to look next?X
Can't find what you're looking for?X

@@ -11705,6 +11712,584 @@
 

       </section>   

+      

+
+        General Thread States
+
+        
+          thread states
+          general
+        
+
+        
+          The following list describes thread State
+          values that are associated with general query processing and
+          not more specialized activities such as replication. Many of
+          these are useful only for finding bugs in the server.
+        
+
+        
+          After create, allocating local
+          table, Analyzing,
+          Changing master, Checking master
+          version, Checking table,
+          cleaning up, closing
+          tables, Connecting to master,
+          converting HEAP to MyISAM, copy to
+          tmp table, Copying to group
+          table, Copying to tmp table,
+          Copying to tmp table on disk,
+          Creating delayed handler, Creating
+          index, Creating sort index,
+          Creating table from master dump,
+          creating table, Creating tmp
+          table, deleting from main table,
+          deleting from reference tables,
+          discard_or_import_tablespace,
+          end, Execution of

Confused? Frustrated?X

+          init_command, Finished reading one binlog;
+          switching to next binlog, Flushing
+          tables, freeing items,
+          FULLTEXT initialization, got
+          handler lock, got old table,
+          Has read all relay log; waiting for the slave I/O
+          thread to update it, Has sent all binlog to
+          slave; waiting for binlog to be updated,
+          info, init,
+          insert, Killed,
+          Killing slave, Locked,
+          logging slow query,
+          login, Making temp file
+          , Opening master dump table,
+          Opening table, Opening
+          tables, preparing,
+          Processing request, Purging old
+          relay logs, query end,
+          Queueing master event to the relay log,
+          Reading event from the relay log,
+          Reading from net, Reading master
+          dump table data, Rebuilding the index on
+          master dump table, Reconnecting after a
+          failed binlog dump request, Reconnecting
+          after a failed master event read,
+          Registering slave on master,
+          Removing duplicates, removing tmp

Call Pantek today for Open Source Technical Support at 1-877-546-8934 - 24/7/365X

+          table, rename, rename
+          result table, Reopen tables,
+          Repair by sorting, Repair
+          done, Repair with keycache,
+          Requesting binlog dump,
+          reschedule, Rolling
+          back, Saving state,
+          Searching rows for update, Sending
+          binlog event to slave, Sending
+          data, setup, Sorting for
+          group, Sorting for order,
+          Sorting index, Sorting
+          result, starting slave,
+          statistics, storing row into
+          queue, System lock,
+          Table lock, Thread
+          initialized, update,
+          Updating, updating main
+          table, updating reference tables,
+          upgrading lock, User
+          lock, waiting for delay_list,
+          Waiting for event from ndbcluster,
+          waiting for handler insert,
+          waiting for handler lock, waiting
+          for handler open, Waiting for
+          INSERT, Waiting for master to send
+          event, Waiting for master update,

Do you need help?X

+          Waiting for slave mutex on exit,
+          Waiting for table, Waiting for
+          tables, Waiting for the next event in relay
+          log, Waiting for the slave SQL thread to
+          free enough relay log space, Waiting on
+          cond, Waiting to finalize
+          termination, Waiting to reconnect after a
+          failed binlog dump request, Waiting to
+          reconnect after a failed master event read,
+          Writing to net
+        
+
+        
+
+          
+            
+              Checking table
+            
+
+            
+              The thread is performing a table check operation.
+            
+          
+
+          
+            
+              closing tables
+            
+
+            
+              Means that the thread is flushing the changed table data
+              to disk and closing the used tables. This should be a fast
+              operation. If not, you should verify that you do not have
+              a full disk and that the disk is not in very heavy use.
+            
+          
+
+          
+            
+              converting HEAP to MyISAM
+            
+
+            
+              The thread is converting an internal temporary table from
+              a MEMORY table to an on-disk
+              MyISAM table.
+            
+          

Do you need more help?X

+
+          
+            
+              Copying to group table
+            
+
+            
+              If a statement has different ORDER BY
+              and GROUP BY criteria, the rows are
+              sorted by group and copied to a temporary table.
+            
+          
+
+          
+            
+              Copying to tmp table
+            
+
+            
+              The server is copying to a temporary table in memory.
+            
+          
+
+          
+            
+              Copying to tmp table on disk
+            
+
+            
+              The server is copying to a temporary table on disk. The
+              temporary result set was larger than
+              tmp_table_size and the thread is
+              changing the temporary table from in-memory to disk-based
+              format to save memory.
+            
+          
+
+          
+            
+              Creating delayed handler
+            
+
+            
+              The thread is creating a handler for
+              DELAYED inserts.
+            
+          
+
+          
+            
+              Creating tmp table
+            
+
+            
+              The thread is creating a temporary table in memory or on
+              disk. If the table is created in memory but later is
+              converted to an on-disk table, the state during that
+              operation will be Copying to tmp table on

Can we help you?X

+              disk.
+            
+          
+
+          
+            
+              deleting from main table
+            
+
+            
+              The server is executing the first part of a multiple-table
+              delete. It is deleting only from the first table, and
+              saving fields and offsets to be used for deleting from the
+              other (reference) tables.
+            
+          
+
+          
+            
+              deleting from reference tables
+            
+
+            
+              The server is executing the second part of a
+              multiple-table delete and deleting the matched rows from
+              the other tables.
+            
+          
+
+          
+            
+              Flushing tables
+            
+
+            
+              The thread is executing FLUSH TABLES
+              and is waiting for all threads to close their tables.
+            
+          
+
+          
+            
+              FULLTEXT initialization
+            
+
+            
+              The server is preparing to perform a natural-language
+              full-text search.
+            
+          
+
+          
+            
+              Killed
+            
+
+            
+              Someone has sent a KILL statement to
+              the thread and it should abort next time it checks the

Can't find what you're looking for?X