How To Monitor Database Usage

I recently did some mysql queries that took many many hours to complete, so I had to break out the man pages and figure out how to see what is going on inside…  At least, I had to see what queries were being made and figure out how to monitor database usage.  So, I figured out that mysqladmin has a command called processlist which displays a table of user, host, db, command, running time, state (very helpful) and info (very very helpful).  This is what the output looked like from the command…

+------+------+-----------------+----------------+---------+-------+-------------------+--------------------------------------------------------------------+
| Id   | User | Host            | db             | Command | Time  | State             | Info                                                               |
+------+------+-----------------+----------------+---------+-------+-------------------+--------------------------------------------------------------------+
| 2140 | user1  | localhost       | housesdb            | Sleep   | 27496 |                   |                                                                    |
| 2143 | rt   | localhost:36931 | mydb | Sleep   | 27086 |                   |                                                                    |
| 2158 | rt   | localhost:42456 | mydb | Sleep   | 3136  |                   |                                                                    |
| 2179 | rt   | localhost:42569 | mydb | Sleep   | 19662 |                   |                                                                    |
| 2188 | rt   | localhost:34695 | mydb | Sleep   | 17232 |                   |                                                                    |
| 2249 | rt   | localhost:47419 | mydb | Sleep   | 1826  |                   |                                                                    |
| 2250 | rt   | localhost:47420 | mydb | Sleep   | 7781  |                   |                                                                    |
| 2286 | user1  | localhost       | housesdb            | Sleep   | 5574  |                   |                                                                    |
| 2332 | rt   | localhost:45332 | mydb | Sleep   | 4071  |                   |                                                                    |
| 2346 | root | localhost       | musicapi       | Sleep   | 3317  |                   |                                                                    |
| 2348 | root | localhost       | musicapi       | Query   | 121   | copy to tmp table | alter table tracks add column (total_votes int not null default 0) |
| 3180 | root | localhost       |                | Query   | 0     |                   | show processlist                                                   |
+------+------+-----------------+----------------+---------+-------+-------------------+--------------------------------------------------------------------+

How to monitor database usage continuously

This was super helpful for me, I think that it is annoying have to repeatedly run the command to monitor database usage, so I decided to tie it together with unix’s watch command.  This worked really well.

Here is the command
mysqladmin -u root –password=”xxxxxx” processlist

Here is the command within watch
watch –interval=5 ‘mysqladmin -u root –password=”xxxxxx” processlist’

Note: I am interested in other ways for monitoring the database usage, and if you have any methods, please post them below.

How to monitor database usage remotely across a network

If you are working remotely, then you can just run this watch command in a screen.  If you aren’t familiar with screen, it is like a shell program that can keep running while you are away, and you can log in and check from anywhere.  The way that I use it for something like this is to ssh in to the remote server and type ‘screen’.  I like to use a couple of windows, so then I type ‘^ac’ and now I can see my windows by typing ‘^a”‘.  In one screen window I start my mysql command.  In the other I start my watch command.  Now I can disconnect ‘^ad’ and then log in later and check progress.  The way to log in later is to type ‘screen -R’.

Hopefully this can help you get started on how to monitor database usage.