Mysql – Open Source tools to visualise MySQL/MariaDB performance metrics (show global status with interval)

chart;mariadbMySQLperformancetools

I'm searching for an Open Source tool to visualise MySQL/MariaDB performance metrics (output of show global status in nice graphs).

In MongoDB world there is the Closed Source Ops Manager and the Open Source mtools. I'm searching for something similar.

In official Oracle training for MySQL 5.6 there are diagrams like

While searching the internet I also found some RRDtool based graphs.

So my question is how to generate ad hoc nice visualisations from MariaDB performance metrics? Is there some Open Source project which helps in this task? How do other DBAs do this?

Best Answer

There's a lot of choices out there:

Percona Monitoring plugins for Zabbix, Cacti, Nagios
Percona Monitoring and Management
Monyog
Severalnines Clustercontrol

Equally capable but not on-premise (afaik) is Vividcortex.

Related Solutions

Mysql – what does SHOW /!50000 GLOBAL / STATUS do

The "50000" refers to a MySQL version -- it means that only version 5.0.0 and above should pay attention to that command. 5.0.0+ will see SHOW GLOBAL STATUS, while earlier versions will see just SHOW STATUS.

Example:

mysql> /*!50511 select 1 */;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> /*!50512 select 1 */;
Query OK, 0 rows affected (0.00 sec)

(I have MySQL 5.5.11 -- i.e. 5.05.11).

Mysql – Why would thesql “show global status” query be taking 15 minutes

That sounds very unusual, but not surprising. All it takes to run SHOW GLOBAL STATUS; is the USAGE privilege, which is just general connectivity as stated in the last sentence of the first paragraph of the MySQL Documentation on SHOW STATUS:

This statement does not require any privilege. It requires only the ability to connect to the server.

Given these facts, there can only be two possible causes:

POSSIBLE CAUSE #1

In MySQL 5.1 and MySQL 5.5, values for global status are accessible via the INFORMATION-SCHEMA database

Here is the table layout:

mysql> show create table information_schema.global_status\G
*************************** 1. row ***************************
       Table: GLOBAL_STATUS
Create Table: CREATE TEMPORARY TABLE `GLOBAL_STATUS` (
  `VARIABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `VARIABLE_VALUE` varchar(1024) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Notice it is a MEMORY table. Like MyISAM, all INSERTs, UPDATEs, and DELETEs into a MEMORY table require a full table lock (See Locking Granularity for MEMORY Storage Engine). There has to be some intermittent lock to populate values into this table. Since this table is local to the DB Connection, only the DB Connection is affected.

POSSIBLE CAUSE #2

User authentication may be blocked for a time due to host caching and DNS. Note paragraph 2 the MySQL Documentation on DNS Lookup Optimization and the Host Cache:

For each new client connection, the server uses the client IP address to check whether the client host name is in the host cache. If not, the server attempts to resolve the host name. First, it resolves the IP address to a host name and resolves that host name back to an IP address. Then it compares the result to the original IP address to ensure that they are the same. The server stores information about the result of this operation in the host cache.

If you do not have skip-host-cache and skip-name-resolve configured, please add these to my.cnf:

[mysqld]
skip-host-cache
skip-name-resolve

and restart mysql ASAP.

EPILOGUE

Cause #2 is a bit of a stretch since authentication should not trigger an entry into the slow query log.

Cause #1 is a lot more likely because if the global status is being loaded into a table, it is an SQL query with discernible passage of time.Thus, if the query's running time (in seconds) exceeds long_query_time, it will get recorded into the slow query log.