Databases Reference
In-Depth Information
Info: update link84.link_in84 set url_to =
replace(replace(url_to,'&','&'),'%20','+'), url_prefix=repl
There are several tools (such as
innotop
) that can show you an updating view of the
process list.
You can also retrieve this information from a table in the
INFORMATION_SCHEMA
. Percona
Server and MariaDB add more useful information to this table, such as a high-resolution
time column or a column that indicates how much work the query has done, which
you can use as a progress indicator.
The
Command
and
State
columns are where the thread's “status” is really indicated.
Notice that the first of our processes is running a query and sending data while the
second has been killed, probably because it took a very long time to complete and
someone deliberately terminated it with the
KILL
command. A thread can remain in
this state for some time, because a kill might not complete instantly. For example, it
might take a while to roll back the thread's transaction.
SHOW FULL PROCESSLIST
(with the added
FULL
keyword) shows the full text of each query,
which is otherwise truncated after 100 characters.
SHOW ENGINE INNODB MUTEX
SHOW ENGINE INNODB MUTEX
returns detailed InnoDB mutex information and is mostly
useful for gaining insight into scalability and concurrency problems. Each mutex pro-
tects a critical section in the code, as explained previously.
The output varies depending on the MySQL version and compile options. Here's a
sample from a MySQL 5.5 server:
mysql>
SHOW ENGINE INNODB MUTEX;
+--------+------------------------------+-------------+
| Type | Name | Status |
+--------+------------------------------+-------------+
| InnoDB | &table->autoinc_mutex | os_waits=1 |
| InnoDB | &table->autoinc_mutex | os_waits=1 |
| InnoDB | &table->autoinc_mutex | os_waits=4 |
| InnoDB | &table->autoinc_mutex | os_waits=1 |
| InnoDB | &table->autoinc_mutex | os_waits=12 |
| InnoDB | &dict_sys->mutex | os_waits=1 |
| InnoDB | &log_sys->mutex | os_waits=12 |
| InnoDB | &fil_system->mutex | os_waits=11 |
| InnoDB | &kernel_mutex | os_waits=1 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=2 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=54 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=1 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=31 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=41 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=12 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=1 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=90 |
| InnoDB | &dict_table_stats_latches[i] | os_waits=1 |
