Order into Chaos

When the Going Gets Tough

Occasionally, database servers run queries very slowly, or not at all, because something is amiss in the system. The mk-loadavg tool can analyze a variety of server performance indicators. If the results are interesting, the tool will tell the administrator or query other system values and log them for analysis purposes. An interesting event could mean an active MySQL thread exceeding a defined threshold, the server starting to swap, the CPU running at full load, or a lack of responsiveness from the DBMS.

mk-loadavg -uroot -ppassword --watch "Status:innodb:Innodb_buffer_pool_pending_reads:>:15" --daemonize --pid /var/run/mk-loadavg.pid/--execute-command 'echo "DB Server hangs" | mail -s "falko@web.de" 017966666666@sms.web.de'

The call in Listing 2 runs as a daemon and queries the InnoDB status every 60 seconds. If more than 15 read requests are waiting to be processed, the tool uses a mail-to-text gateway to send a text message to the administrator, who can then resolve the issue.

Unused Indexes

Indexes should normally accelerate access to the records stored in your tables. As the system grows, or when you change things, indexes you used previously can become redundant. Creating and updating them still takes up valuable space and time, so you should find out which indexes are not being used for queries. To do so, all you need is a slow logfile. If the mysql-slow.log doesn't reveal much, you can also convert the normal logfile to slow log format using mk-query-digest, like this:

mk-query-digest --type genlog ↩
   --print /var/log/mysql/mysql.log.1 ↩
   > index.log

Then, mk-index-usage index.log -uroot -password --host localhost analyzes the logfile and checks for unused indexes. As a result, the tool suggests removing non-unique indexes and provides the matching ALTER-TABLE instruction (Figure 4). The mk-index-usage command is not restricted to non-unique indexes; you can use the --drop option to extend the criteria to primary and unique type indexes. You can restrict the tool's activity to selected tables and store the results in a database.

Figure 4: The mk-index-usage command lists unused indexes and provides SQL instructions for deleting them.

Heartbeat

If you use replicated systems, you might not immediately notice a slave failure. Maatkit includes the mk-heartbeat tool, which tests once only or continually to discover whether a slave is still alive. To do so, mk-heartbeat --database --update --uroot -ppassword writes continually (once a second by default) to a definable table on the master (Figure 5). Then, in a second (parallel) step, issues mk-heartbeat --database maatkit --monitor -ureply -ppassword -h slave to check when the slave applied the change.

Figure 5: The mk-heartbeat tool uses a tiny table on the master to keep the system's pulse beating.

A table lists how quickly the server responded on average (Figure 6). Time windows of 1, 5, and 15 minutes are used by default to evaluate responsiveness; however, you can change the defaults as needed. If a component fails, or if the synchronization process is too slow, you will soon notice. Besides continual monitoring, you can run --check instead of --monitor for an individual query, and --daemonize lets you run heartbeat monitoring as a background process.

Figure 6: The mk-heartbeat tool immediately notices any delays to replication.