What capabilities should PG database operation and maintenance tools cover?

Before the holidays, I collaborated with the PG China community to conduct an online live broadcast on how to use D-SMART to operate and maintain the PG database. It happened that one of my clients in the financial industry listened to my introduction and called to chat. Let’s chat. They are selecting database Xinchuang and have tried several domestic databases. Finally, they are going to choose TDSQL. I felt a little surprised at the time. They had been selecting domestic databases since 2020, but it seemed that the initial experience after using TDSQL was not very good. Later, after communication, I learned that they had just started using TDSQL's distributed database and found that the research and development requirements were too high, so they all chose TDSQL's centralized MYSQL instance. After using it, they found that it was very easy to use. The number of nodes in the entire database cloud has also expanded from the initial dozen to dozens.

Coincidentally, yesterday I chatted with another financial customer on WeChat about the selection of database Xinchuang, and they finally chose TDSQL. Similar to another customer, they also chose TDSQL's MYSQL centralized database instance. They have currently migrated dozens of databases, most of which are small databases ranging from tens to hundreds of GB. They feel that it is very convenient to migrate small databases directly to the TDSQL cloud platform. TDSQL's database cloud management platform and operation and maintenance tools can basically meet their daily operation and maintenance needs.

Through communication, I feel that the two customers chose TDSQL not because of how excellent TDSQL is as a database (TDSQL is actually not a database, but a database cloud platform solution. Regarding TDSQL’s future availability, I will write a detailed introduction), but its database cloud management platform is very good at managing a large number of small database instances. Users choose it not from the database technology, but from the convenience of use. Considered with reliability.

From the reasons why customers choose TDSQL, let’s take a look at the operation and maintenance of PG database. Talking about the operation and maintenance of PG database in general is a very huge topic, because different customers have their own special application scenarios, and the operation and maintenance management methods of PG database are also quite different. What's more complicated is that, unlike the two customers I mentioned who chose TDSQL, PG Database has both small databases and very large database systems. When some customers are replacing Xinchuang, they replace Oracle databases one-to-one. The hot data of many databases exceeds several TB. Faced with application scenarios with huge differences in scale and different operation and maintenance requirements, operation and maintenance tools really need to be carefully designed to adapt to the vastly different application scenarios.

The application of PG database in China has developed rapidly in the past two years. In addition, many domestic databases are also developed based on PG open source projects. They are very similar in application and operation and maintenance, so we can also They are classified as PG database products.

In the current domestic database, many products are based on the PG Community Edition code as the starting point for research and development, and some products are based on the openGauss open source project. The basic features of these databases are similar to the community version of the PG database, but they have also been expanded to some extent. However, in terms of use and operation and maintenance, many of their features are very similar to the community version of PG.

There are also some database products that are directly related to PG, but most of them are based on PG's distributed solutions PGXL/PGXC or CITUS. For example, Tencent's TBASE, NTU General's GBASE 8C distributed version, AsiaInfo's ANTDB, Xugu Database, etc. I won’t list them in detail here. An instance of these databases is also a PG database, and a specific instance can also be regarded as a PG database instance. However, when operating and maintaining distributed databases, you need to pay more attention to the issues of the entire cluster and network. The difference is still huge.

In summary, the operation and maintenance requirements of PG database are divided into five aspects, daily monitoring, fault warning, automated inspection, performance optimization and fault diagnosis.

Some companies are already migrating some core systems to PG databases. For these systems, there is a daily need for monitoring. Therefore, the most basic ability that a database operation and maintenance tool needs to have is the monitoring ability. It can understand the overall operating status of the database instance at any time through the operation and maintenance tool. D-SMART displays the running status of the database through a health model. In addition, if we need to be on duty on some major dates (such as the company's year-end accounts, special duty such as National Day, etc.), then we also need some tools that can support the on-duty of key systems.

In D-SMART, we provide "Monitoring Center", "Daily Inspection Center", "Alarm Center", "Performance Optimization Center", "Report Center", "Capacity Management Center", " The centralized function combination of "Security Center" and "Tool Center" can meet the needs of different users and users in different application scenarios.

For daily monitoring functions, D-SMART provides three main operation and maintenance monitoring tools: "Today's Kanban", "My Monitoring", and "Key SQL Monitoring". Today's Kanban can centrally view the comprehensive information of user-monitored databases. "My Monitoring" allows users to use traditional monitoring methods to define the indicators they want to monitor for major escort monitoring. "Key SQL Monitoring" is a special monitoring tool provided for the core business systems of enterprises. When there is a problem with the key SQL of a core business system (such as the execution speed slows down, the execution plan changes, etc.), timely alarms can be generated to ensure the safe operation of the core business.

For a large number of small database instances, comprehensive monitoring is not realistic. If a team of more than a dozen people has to operate and maintain hundreds or thousands of database instances, then comprehensive monitoring of these databases is neither necessary nor possible. Therefore, this operation and maintenance scenario should turn a large amount of monitoring work into automated tasks, which can be automatically completed by the monitoring system.

"Database Daily Inspection" is a very effective automated operation and maintenance tool. It automatically analyzes the database's operating data and some rules in the middle of the night every day, and forms a concise and comprehensive daily inspection summary report. The operation and maintenance personnel can directly By reading these reports, you can learn about some common problems that exist in the hundreds of database instances you operate and maintain, so you can determine whether you need to make corresponding changes to certain database instances today or in the near future.

When we need to operate and maintain a large number of small database instances, early warning becomes very difficult. The effect of the traditional "baseline alarm" has become very useless. In addition to database instance downtime, other early warnings are difficult to make accurately. Massive warning information will make early warning meaningless. Therefore, "operation and maintenance experience alarms" based on fault models have become particularly important. Complex rules built through expert experience and past experience can not only provide more accurate early warnings, but also allow operation and maintenance personnel to more quickly locate problems and eliminate hidden dangers after alarms are generated.

"Database inspection" is a function that most DBAs find very useless. The main problem is that this work must be done, but to do a truly effective inspection requires both the participation of a large number of professional DBAs and the Doing a lot of repetitive work, overall, the price/performance ratio is not high. On the other hand, comprehensive and high-quality inspections can help us discover some system hidden dangers and help prevent them before they occur. In response to this contradiction, if high-quality automated inspections can be achieved, the problem will be easily solved. A few months ago, we helped a user conduct a remote inspection. The user sent the monitoring data collected by D-SMART to our laboratory. Our database experts used the inspection report generated by the remote data to conduct an inspection on nearly 30 database systems. We conducted a remote consultation and helped users discover more than 200 problems of various types, and this work only took less than 2 man-days. Through automation, if the efficiency of database inspection can be improved, then the inspection work will not be so useless.

In addition to inspections, some audit work is also very critical, such as security audit, capacity audit, SQL audit, etc. Because these audits require very professional skills and a large workload, when faced with a large number of database instances, they become as useless as inspections. It is too expensive to do it well, and it means nothing if it is not done well. Do. However, if these tasks can be completed automatically by automated tools, then they can play a very important role.

In fact, in addition to these operation and maintenance monitoring tasks, a large number of database instance management tasks and many automated operations are very much needed by DBAs. This is also the main reason why the two customers I mentioned at the beginning chose TDSQL. . To manage a large number of database instances, a database cloud platform is a must-have. However, these automated management functions themselves are very complex. Building an independent database cloud platform based on the characteristics of the enterprise is a major project in itself. Of course, if the RDS service of the enterprise cloud platform can meet your database application needs, then it is enough to directly use the RDS of the cloud platform. Of course, in the face of the current demand for information innovation, the enterprise's RDS needs to not only support the open source MYSQL/PG database, but also support domestic database products. ​

The above is the detailed content of What capabilities should PG database operation and maintenance tools cover?. For more information, please follow other related articles on the PHP Chinese website!