This blog details the experience of Partner Agent, Inc. on working towards a focused approach for monitoring their MySQL database servers.
The State of MySQL database server
Just like any enterprise, MySQL database servers are pretty much the backbone of Partner Agent Inc’s goal of providing a great customer experience. There are multiple things a DBA needs to take care in the engineering department. It’s no rocket science that a downtime for a MySQL server can cause huge losses to the company.
To keep up with the production demands, database administrators rely on the logs provided by MySQL like Slow query, General Query and Error log to measure the health status of their MySQL servers in the absence of a database monitoring tool. This needs a constant look-up to the files or querying the MySQL tables. In order to check the values for the MySQL variables, one need to query the MySQL server and get the value.
The database engineering team at Partner Agent Inc needs to ensure that the database servers are up and running, keep a tab on the configurations which can otherwise cause the application to run slow. The team is required to configure certain MySQL default variables so that they can suit the company’s MySQL environment. SQL query may take a long time to execute which could be because of improper use of indexes or query structure. This can exhaust the system resources like CPU, RAM etc. and the machine might start throttling. This has to be monitored effectively to track the overall performance of the system and the applications running on it.
Finding the right MySQL monitoring tool
To overcome these issues, the database engineering team explored multiple MySQL monitoring tools that could align with their monitoring needs. They initially considered the commercial version of MySQL but judged it to be costly. The need of the hour was a monitoring tool that spots problems in less time and helps them focus on improving and sustaining the performance of their MySQL database servers. They felt that the feature set of Monyog MySQL monitor assisted them seamlessly in their efforts to reach optimum levels of performance tuning of MySQL database servers and it was also cost-effective to their needs.
‘Monyog made it easy to monitor database performance and reduce downtime.’ – Yuji Tajima
Role of Monyog
The Dashboard feature in Monyog provided the team at Partner Agent Inc the flexibility to create their own set of charts – System charts, different MySQL charts – which updates the data after every user-defined interval of time. The system charts identified the spikes in case the load on the machine running the MySQL server increased and provided the ability to see the queries involved for that spike within a specific time-frame. This helped them narrow down the set of queries responsible for the spike and also the option to see explain plan output for each of the queries contributed in optimizing the queries.
‘Monyog helped monitor the health status of the MySQL database servers to keep up with the production demands.’
The 600+ monitors and advisors made use of key MySQL variables which gave them the overall health status of database servers. The monitor groups like “InnoDB cache”, “MyISAM cache” comes with different counters like Allocated/Free memory, cache misses, hit ratio etc.
One of the key things for the team is the ability of Monyog to set up a threshold long running query time, exceeding which it sends alerts to identify the problematic queries. The server config feature came in handy when they needed to compare and track the MySQL server configuration changes. It lists all the MySQL variables for different MySQL servers side by side making it easier to figure out the performance differences amongst the servers.
About Partner Agent, Inc.
Partner Agent, Inc. provides marriage information and support services in Japan. The company was founded in 2006 and has its headquarters in Tokyo. Services provided include introduction and consultation of married partners.