As with any other dynamic running process, the MySQL database also needs sustained management and administration of which monitoring is an important part. Database monitoring allows you to find out mis-configurations, under or over resource allocation, inefficient schema design, security issues, poor performance that include slowness, connection aborts, process hanging, partial resultsets etc. It is giving you an opportunity to fine tune your platform server, database server, its configuration and other performance factors. This article provides you an insight into monitoring your MySQL database through tracking and analyzing important database metrics, variables and logs using mysqladmin.

Is your MySQL server running?
Obviously availability or up-time is the primary concern of any production deployment. But at the same time it does not guarantee that the running server will accept connection or whether it is running out of space. Still, it is worth to know about the server status. There are many methods to detect a running MySQL server:
1. Ping the server
mysqladmin –u root –p ping
Returns 0 if server is running and 1 if not. Even for access denial cases, 0 will be returned since server is still running. A remote server can be pinged by specifying the host name (IP or Domain if DNS resolution is available).
Eg: mysqladmin -h db.myserver.com –u root –p ping
or
Eg: mysqladmin -h 100.100.100.100 –u root –p ping
A running server will return:
mysqld is alive
2. Get the database server status
Eg: mysqladmin -h 100.100.100.100 -u root -p status
or
service mysqld status
3. Get server version
mysqladmin -u root -p version
The results will include version, uptime, threads, slow queries etc.

Get the MySQL Server Metrics and Optimize Database
The important metrics (server status variables) that reflect the health and status of a MySQL server are:

Aborted_clients: number of connections aborted because client did not closed the connection properly. Possible reasons are: client did not close connection using mysql_close() before exiting, client was sleeping for a time interval (seconds) more than that is set in the variables wait_timeout or interactive_timeout without notifying server and client program ended abruptly during a data transfer. After aborting a connection, server will log the details in error log:
[Note] Aborted connection 854 to db: ‘customers’ user: ‘john’
Aborted_connects: number of failed connection attempts. Possible reasons could be: a client without required privileges tried to access a database, client used wrong password, the connection packet was missing some required information, and there was a delay more than the connect_timeout seconds to receive a packet from the client. Check error log to find out the affected user and grant needed privileges. It can also be due to an attack like spamming, DOS or brute force.
A common reason for above scenarios can be: the max_allowed_packet variable that contains the maximum memory size in bytes is too small or queries needed more than the allocated memory for mysqld. You can increase this memory through any of the following methods:

[mysqld]
max_allowed_packet=32M
OR
shell> mysqld –max_allowed_packet=32M
OR
shell> mysql –max_allowed_packet=32M

Other reasons could be related to network protocols, faulty hardware, OS thread level issues etc.

Connections: number of connection attempts (both successful and failed)
Uptime: number of seconds server has been up. A smaller value my point to intermediate shutdowns or server crashes.
Threads: number of active threads (client connections). This should reflect an average concurrent amount of connections. Otherwise the Aborted_clients and Aborted_connections should be examined for high values.
Threads_connected: number of threads created to connect clients at the current moment. A zero and too high values can be issues. Too high can be due to either smaller thread_cache_size value or too many concurrent client connections happening. This need to be investigated against database attacks especially DOS.
Threads_created: Threads created to handle connections. An optimal setup ensures threads to be reused through thread cache. A too high value for this variable can be due to smaller thread_cache_size value and it should be increased.
Questions: number of queries received from clients since last server start.
Slow queries: number of queries that have taken more than long_query_time seconds. Identify the slow queries and optimize them for improving performance. Check whether the database uses proper indexing and queries are utilizing this.
Open tables: number of tables that are currently open. A low value for this and high value set for table_cache means memory is free and table_cache size can be reduced and vice versa.
Bytes_received: bytes of data received from clients.
Bytes_sent: bytes sent to clients.
Max_connections: maximum permitted number of simultaneous client connections.
Max_used_connections: maximum number of concurrent connections occurred at any time. If max_used_connections/max_connections is high like more than 75%, there are chances for server to run out of connection slots and denies further connections.
Max_execution_time_exceeded: number of SELECT statements that caused a time out in execution. Check the slow query log and error log to find and optimize slow queries, if this value is high.

Check above 2 values for abnormal hike in traffic.

The server status variables can be displayed by issuing the SHOW STATUS command:
mysql> SHOW GLOBAL STATUS;
+————————–+————+
| Variable_name | Value |
+————————–+————+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Bytes_received | 155372598 |
| Bytes_sent | 1176560426 |
| Connections | 30023 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 8340 |
| Created_tmp_files | 60 |
…
| Open_tables | 1 |
| Open_files | 2 |
| Open_streams | 0 |
| Opened_tables | 44600 |
| Questions | 2026873 |
…
| Table_locks_immediate | 1920382 |
| Table_locks_waited | 0 |
| Threads_cached | 0 |
| Threads_created | 30022 |
| Threads_connected | 1 |
| Threads_running | 1 |
| Uptime | 80380 |
+————————–+————+
OR
shell> mysqladmin -u root -p extended-status processlist
+—-+——-+———–+—-+———+——+——-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——-+———–+—-+———+——+——-+——————+
| 51 | monty | localhost | | Query | 0 | | show processlist |
+—-+——-+———–+—-+———+——+——-+——————+
Uptime: 1473624 Threads: 1 Questions: 39487
Slow queries: 0 Opens: 541 Flush tables: 1
Open tables: 19 Queries per second avg: 0.0268

To see values of specific variables, use LIKE clause with or without patterns:
$ mysql -u root -p
mysql> SHOW GLOBAL STATUS LIKE ‘aborted_connects’;
mysql> SHOW STATUS LIKE ‘Key%’;
+——————–+———-+
| Variable_name | Value |
+——————–+———-+
| Key_blocks_used | 14955 |
| Key_read_requests | 96854827 |
| Key_reads | 162040 |
| Key_write_requests | 7589728 |
| Key_writes | 3813196 |

Check Logs Frequently
The slow query log and error log needs to be checked regularly to detect issues. An effective method is to monitor and investigate both logs and server status variables to identify issues and space for further optimization.

InnoDB Table Monitoring
The command SHOW ENGINE INNODB STATUS can be used to detect aborted or rolled back transactions. This indicates presence of deadlocks and the InnoDB configuration as well as client application’s deadlock handling mechanism should be enhanced. Below are some server status variables for InnoDB that need to be monitored:

Innodb_row_lock_waits: number of times operations on InnoDB tables had to wait for a row lock.
Innodb_buffer_pool_wait_free: number of times InnoDB had waited for memory pages to be flushed. This happens when there are no free pages available for writing and InnoDB flushes some pages first and waits for this operation to be finished. A too high value indicates that innodb_buffer_pool_size is too small and needs to adjust according to the work load.

Summary
The mysqladmin tool along with other MySQL administrative tools is sufficient enough for properly monitoring the MySQL database. Regular monitoring, investigation of any issues and performance degradations and taking corrective measures on a daily basis will make your database robust and well optimized through an evolution process. This is the desired and feasible way of adapting your MySQL database to fulfill its responsibilities like a faultless engine working harmoniously.