Maatkit tools for database administrators

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.

Listing 2

mk-loadavg

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.

Buy ADMIN Magazine

SINGLE ISSUES
 
SUBSCRIPTIONS
 
TABLET & SMARTPHONE APPS
Get it on Google Play

US / Canada

Get it on Google Play

UK / Australia

Related content

  • Stopping SQL Injection

    SQL injection can strike at any moment. GreenSQL is an effective remedy that sits between the database and application and filters out suspicious queries.

  • Blocking SQL injections with GreenSQL
    SQL injection can strike at any moment. GreenSQL is an effective remedy that sits between the database and application and filters out suspicious queries.
  • RSQL, a Microsoft SQL clone
    The open source relational database RSQL aims to completely replace slimmed-down instances of Microsoft SQL Server.
  • Chive

    Generations of web admins have used phpMyAdmin or SQL Buddy to communicate with their databases. Newcomer Chive has the potential to send the legacy tools into early retirement, thanks to its state-of-the-art Ajax interface and impressive feature scope.

  • Optimizing SQL with Execution Plans

    Understanding database execution plans is the key to assessing effectively the optimum potential of an SQL query or estimating your future resource requirements.

comments powered by Disqus