All you need for monitoring MySQL servers. Data security is paramount for individuals and corporations alike. Today, it is inevitable not to notice the importance levied on hosted MySQL like RDS, Aurora, etc. Managing MySQL servers is becoming a daunting task, given the exponential growth in data and the number of users with each working days.
Below are the key aspects that need to be part of your wishlist.
1) Leveraging Performance Schema(P_S) and SYS Schema
Performance Schema was introduced in MySQL 5.5.3 and further upgraded through version 5.7. It has its own storage engine, provides access to low-level server execution information while minimizing the impact on server performance. There is a trade-off between the time you can save by using Performance Schema and the load it adds to the server. It can optimize server performance and save time when used in an efficient manner.
You can also target queries that run less than a second using events_statements_summary_by_digest from Performance Schema with little-to-no overhead that helps us get the fast running queries that were not available before.
How is it important?
- It takes minimum disk storage since it employs the use of temporary tables or views
- As all allocation is carried out during the server startup, there is no further memory re-allocation or sizing after the startup
Performance Schema on MySQL contains queries executed on server along with the following information:
- Number of rows sent and examined
- Number of temporary tables created on disk
- Number of temporary tables created on memory
- Number of joins performed and the type of join
- Whether sorting happened and find the kind of sorting
- Whether index was used and implemented
Starting from MySQL version 5.7.7 and above the sys Schema was introduced with an objective to help the DBAs understand and use the data collected by Performance Schema. In later versions, MySQL introduced progress reporting for long running transactions. Using performance schema at an enterprise level can help you in effective monitoring.
2) Suitable monitoring of Indexes
There are various reasons why DBAs use indexes:
- Help reduce the amount of data the server captures while looking for specific fragments of data
- Make inputs and outputs sequential
- Reduce the number of temporary tables and sorting that the server has to undertake
However, indexing can have its set of drawbacks if not done in the right manner. One of the major causes that hamper the performance of MySQL is either the overuse, underuse or no use of indexes. Although there are different ways to deal with each of the above cases regarding index usage, the first step comprises of finding the culprit and the nature of problem related to indexing.
You can monitor various aspects of Index usage in the ways listed using PERFORMANCE_SCHEMA.
Performance_schema implements tables as shown below:
- Table_io_waits_summary_by_table
- Table_io_waits_summary_by_index_usage
These tables give an account if the IO and usage activity of the indexes.
Using the SYS Schema:
It provides a favorable view making it easy to identify unused indexes:
~ mysql> select * from sys.schema_unused_indexes;
The above example shows indexes which haven't been used since the last restart of MySQL when PERFORMANCE_SCHEMA statistics are reset at startup time.
3) Reliable and concise Configuration Management
At an enterprise level, a few hours of downtime can cost millions of dollars. DBAs, at times, can lose track of server configurations when they are working simultaneously on numerous servers.
Thus, a reliable configuration management system is needed that helps you check 100s of servers at a given point of time. It is important because there are servers that share the same amount of load but perform differently due to change in configurations.
It becomes imperative to seek and find a MySQL management tool that provides the option to compare and track system configurations on multiple servers.
4) Query Analyses
“I am looking to eliminate or optimize problematic queries!” – The most common issue faced by thousands of DBAs around the world. Eliminating this requires systems that provide for a macro and micro view of the queries. These systems further provide filtering options that help to optimize the query logs.
5) MySQL Server Logs
Referring the appropriate logs is an essential element in spotting the problems. MySQL server provides extensive information about the various aspects through critical logs such as error log, slow query log, general query log, etc. These logs help to spot problems that one may encounter during starting, running or stopping of MySQL – queries that take more time than mentioned to execute and more (it's not a complete sentence).
Though by default, these logs are disabled, these logs can be enabled depending upon the requirement of the user to track the happenings on the server.
6) Bash Scripts for alerts
Monitoring is incomplete without an effective alerting system for proactive actions. Setting up alerts helps the user in receiving reports that showcase how variables and corresponding values appear on the server. Sometimes an admin needs to keep a check on any particular action undertaken on the database. It can be handled by using appropriate scripts that send the admin a notification over email that alerts him about the action.
While none of this can be overlooked, it is important to find the right monitoring tool for your servers. Managing the performance of your MySQL servers can help in a quick analysis of your problems, save time in monitoring and increase the overall productivity.
You can give our monitoring tool MONyog a spin. Considered by thousands of corporates as the most secure and scalable MySQL monitoring tool, MONyog ensures optimum performance of your MySQL servers.