| Status information that the server provides may be used to assess how hard it is working: Several status variables displayed by SHOW STATUS provide load information. For example, Questions indicates the number of queries the server has processed and Uptime indicates the number of seconds the server has been running. Combining these, the ratio Questions/Uptime tells you how many queries per second the server has processed. Slow_queries indicates the number of queries that take a long time to process. Ideally, its value should increase slowly or not at all. If it increases quickly, you might have a problem with certain queries. The slow query log shows the text of slow queries and provides information about how long they took. Restart the server with the slow query log enabled, let it run for a while, and then take a look at the log to see which queries turn up there. You can use this log to identify queries that might need optimizing. General information about the slow query log and its use in optimization is given in Section 37.1, "Identifying Candidates for Query Analysis." SHOW PROCESSLIST displays information about the activity of each currently connected client. For example, the presence of a large number of blocked queries might indicate that another connection is running a query that is inefficient and should be examined to see whether it can be optimized. The SHOW PROCESSLIST statement always shows your own queries. If you have the PROCESS privilege, it also shows queries being run by other accounts. To get a concise report of the server's load status from within the mysql client program, use its STATUS (or \s) command to display a general snapshot of the current connection state. The last part of the output provides some information about the server load: mysql> STATUS; -------------- mysql Ver 14.12 Distrib 5.0.10-beta, for pc-linux-gnu (i686) Connection id: 34816 Current database: world Current user: myname@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.0.10-beta-log Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /tmp/mysql.sock Uptime: 51 days 3 hours 40 min 37 sec Threads: 4 Questions: 2910900 Slow queries: 1053 Opens: 3400 Flush tables: 3 Open tables: 64 Queries per second avg: 0.720 --------------
The final part of the output also can be obtained by issuing a mysqladmin status command.
The preceding items describe how to obtain information that the server provides during the course of its normal operation. The server also provides diagnostic information about exceptional conditions in the form of error messages, which it writes to an error log. Some of these messages pertain to errors that are not fatal but might affect performance, such as aborted connections. (See Section 24.5.4, "The Error Log.") For a discussion of ways to reduce server load by helping it work more effectively, see Section 39.3, "Tuning Memory Parameters," and Section 39.4, "Using the Query Cache." |
