Databases Reference
In-Depth Information
mysql>
SHOW TABLES FROM INFORMATION_SCHEMA LIKE '%_STATISTICS';
+---------------------------------------------+
| Tables_in_information_schema (%_STATISTICS) |
+---------------------------------------------+
| CLIENT_STATISTICS |
| INDEX_STATISTICS |
| TABLE_STATISTICS |
| THREAD_STATISTICS |
| USER_STATISTICS |
+---------------------------------------------+
We don't have space for examples of all the queries you can perform against these
tables, but a couple of bullet points won't hurt:
• You can find the most-used and least-used tables and indexes, by reads, updates,
or both.
• You can find unused indexes, which are candidates for removal.
• You can look at the
CONNECTED_TIME
versus the
BUSY_TIME
of the replication user to
see whether replication will likely have a hard time keeping up soon.
In MySQL 5.6, the Performance Schema adds tables that serve purposes similar to the
aforementioned tables.
Using strace
The
strace
tool intercepts system calls. There are several ways you can use it. One is to
time the system calls and print out a profile:
$
strace -cfp $(pidof mysqld)
Process 12648 attached with 17 threads - interrupt to quit
^CProcess 12648 detached
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
73.51 0.608908 13839 44 select
24.38 0.201969 20197 10 futex
0.76 0.006313 1 11233 3 read
0.60 0.004999 625 8 unlink
0.48 0.003969 22 180 write
0.23 0.001870 11 178 pread64
0.04 0.000304 0 5538 _llseek
[some lines omitted for brevity]
------ ----------- ----------- --------- --------- ----------------
100.00 0.828375 17834 46 total
In this way, it's a bit like
oprofile
. However,
oprofile
will profile the internal symbols of
the program, not just the system calls. In addition,
strace
uses a different technique for
intercepting the calls, which is a bit more unpredictable and adds a lot of overhead.
And
strace
measures wall-clock time, whereas
oprofile
measures where the CPU cycles
are spent. As an example,
strace
will show when I/O waits are a problem because it
measures from the beginning of a system call such as
read
or
pread64
to the end of the
