As a database service provider, the most frequently encountered problems from customers include: How can I be sure that the database has reached the best running state, how to track a SQL statement with poor performance, if my database crushed, what principle should I follow to troubleshoot?
The database holds and manages the core data of the enterprise, and the efficient operation of the database is the key guarantee for the external service provided to the business frontend. But there are so many technologies contained in the database, involving hardware, network, storage, resources, and other aspects, when we do database monitoring and management, what are the core indicators that we should pay most attention to, and further, which indicators need our real-time attention?
today let’s talk about these core indicators. And to help you with O &M workload, I 'd like also to show you how to monitor and analyze these indicators on the Bethune X platform to help users improve efficiency and quality of O&M.
I have summarized these core indicators into six categories.
The first category is configuration-related indicators
Which including system configuration and the resource configuration. Regarding the configuration of the operating system itself, it does not belong to the scope of the database, so we will not talk about it further in this article. As for the configuration of the database system resources, we have encountered more than once of “highly tuned” servers have crashed or running slowly, or even fail to withstand pressure during peak business hours, however when we looked into the problems, found that it was caused by a misconfiguration of resources. That happened so many times.
For the database, the most basic configuration includes CPU, Memory, Buffer pool, Logfile size, etc. These parameters are very critical for the database in the running state. For example， the configuration of Redo log size, if the log size is too small, it will increase the frequency of log switching. During busy business hours, a group of logs may be filled soon and then need to write another, however other groups haven’t finished archive, then write may fail. If the size is set too large, when the database crashes, the data recovery time will be extended, and the risk of data loss may also increase. Therefore, for each parameter, a reasonable configuration should be made to meet the demands of the peak business and minimize the waste of resources during the idle period.
For example, Bethune’s monitoring information on CPU and Memory indicators is as follows, so you can track the usage of the system resource in both peak hours and idle hours, and then try to optimize the allocation.
The second category is data synchronization information between nodes
For any highly available database architecture, such as Oracle’s RAC, ADG, MySQL’s MGR, whether data synchronization between multiple nodes is normal, and the delay will directly determine whether the database provides services normally (all nodes are running) or enter self-healing (such as choosing a new cluster administrator), or even elapsed directly in some extreme cases. In different databases, many kinds of synchronization technologies and components are used to achieve this function. For example, Oracle uses the Redo logo for synchronization, MySQL uses binlog, and of course there are tools that use logical synchronization to transfer data blocks and execute them again on the backup nodes. Either way, the ultimate goal is to ensure multiple copies of core business data and achieve synchronization with optimal efficiency.
In Bethune X, the system’s high availability architecture and data synchronization information between nodes are illustrated in details.
The third category is business access information
Such data is closely related to the busyness of the business. Including the number of connection establishments, the number of visits initiated by the service, and active sessions per second. These indicators also have different expressions in different databases, such as MySQL’s QPS, pg’s current_query, Oracle’s current process and Active sessions. In addition to direct business access, cross-databases access also needs attention, such as Oracle’s DBlink
Here shows the QPS for MySQL Database, and active session for Oracle.
The fourth category is the consumption and call of database resources
This kind of information is the most important information we should pay attention to in operation and maintenance. The resource invocation and consumption show the overall health status of the database for the most part. Usually these information result from many factors.
For example, IOPS represents the number of IOs generated by the database per second, and CPU usage. By comparing the configuration of the CPU, you can also analyze whether the configuration is reasonable, Dbtime in the oracle database, represents the time the database is running.
The fifth category is the use of space capacity
Many DBAs should have a deep understanding of this. When doing database management at the beginning, the most important and usual thing to do is to expand the database’s capacity, such as adding data files, expanding data files, increasing or decreasing table space, and adding disks. However, if the capacity is expanded according to business needs every day, not only is it inefficient, but it is also likely to bring huge risks to the database during the peak business period . Therefore, timely attention to capacity, and planned expansion instead of emergency expansion, can greatly improve the efficiency of operation and maintenance.
Bethune X knows your database better than you do, based on the historical record of expansion operation and history usage, Bethune X generates the risk increase report for users, so even though the capacity is still sufficient, but the risk operation occurs and may result in an abnormal increase in data volume, you would get the most timely warning.
The sixth category, inefficient SQL statements
This kind of information is basically closest related to the running performance of the database, and it is also a big challenge for most DBAs. In a database, even if the optimal table structure is designed, great indexes are established, and the resource allocation is reasonable, but if the SQL statement of the business query is written badly, the final performance result would still be quite poor.
Writing high-quality SQL statements, or optimizing low-efficiency SQL statements, is the key to verifying a DBA’s technical capabilities. But before that, finding the SQL that had the most impact on the system was already a challenging intention. In order to optimize SQL statements, it is necessary to deeply understand the principle of SQL running in the system.
Taking querying SQL as an example, we regard it as a task of system , it is composed of a series of sub-tasks, each sub-task will access different data, consuming a certain amount of time and system resources. The essence of optimizing these SQL statements is how to optimize these sub-tasks so that the amount of data accessed by the sub-tasks is minimized, accessed with the best road , and consumes the least resources.
Bethune X has a well-designed user interface that can intuitively display the key information of various types of databases , and has built-in intelligent algorithms to help users filter out unnecessary information and display the indicators that users need to pay most attention to. For the management of database performance, it supports scaling for time periods, such as selecting a time period with abnormal performance, and can also be classified according to the actual resources consumed and access data. Whether for complex performance optimization, for guidance and advice in fault diagnosis, or to improve the efficiency and quality of O&M, Bethune X is of great help to users, your best choice.
Bethune X can bring tremendous value to the enterprises with large-scale databases, fewer resources for operation and maintenance teams, weaker data management capabilities, and lack of automated operation and maintenance.
for more details, please visit bethune.enmotech.com
or you wanna have a try? please leave your message below.