Skip to main content

Still troubleshooting slow queries through slow logs? Outdated, now it's all about this

For a DBA, the stability of a company's database system can reflect their capabilities. An excellent DBA not only ensures the stable operation of the database but also has the ability to effectively handle various unexpected performance issues of the database. The most common performance issue is slow queries.

Our company is quite large, with more than 100 database instances for projects of all sizes. The responsibility of ensuring the stable operation of these databases lies entirely with me. Every day, I need to meticulously investigate each issue one by one. This is a rather daunting task. It's common to make mistakes, and if the impact is not significant, it's fine. However, if it involves corporate interests, then pray that the next company doesn't have so many databases.

img

Due to the presence of many slow query issues in each database instance, it is obviously unrealistic for me to investigate manually. After trying many automated tools, I finally found a tool that perfectly solves my problem: NineData's slow query analysis feature. It can automatically collect and record all slow queries in the database. What stands out is that it provides optimization suggestions by performing performance diagnostics on each slow query, including adding or modifying indexes, adjusting table structures, etc. At the same time, it can also configure SQL development standards based on business types. After configuration, the system will also diagnose slow SQL based on these standards.

No more talk, let's go straight to the pictures.

  • Slow Query Trend Chart: This is a slow query trend chart from a single data source perspective. First, you can see the total number of slow queries within a certain period of time, and then you can clearly see that there were many slow queries on December 8th. Hovering the mouse over the curve will show the details.

    image-20231211143928331

  • Slow Query Statistics: Displays detailed information about all slow queries generated by the database during a certain period. SQL Template indicates the SQL framework without specific parameters. All slow queries that use the same SQL template will be recorded under one template. Expanding the template will show all slow SQL statements, and the information included is also very rich, such as execution time, query time, user executing the query, host name, etc.

    image-20231211145259295

  • Diagnostic Optimization: Perform performance diagnostics on slow query SQL statements. The results of the performance diagnosis include excessive execution time, low effective reads, high waiting time ratio, low cache hit rate, etc.; Standard review audits SQL statements based on the SQL development standards configured by the administrator; Index recommendations are based on the CBO cost model to provide index recommendations, helping DBAs easily optimize database performance.

    image-20231211165018598

This tool is like a timely rain for me. It has all the features a DBA needs. As a social animal who used to work overtime until 22:00 every day, I have recently started to leave work on time.

img

Latest Update​

Recently, they have released a data dashboard and report download feature for the slow query analysis function.

As mentioned earlier, the company has more than 100 database instances, and each instance has a large number of slow queries... So here comes the question, if I want to manage slow queries, I need to click on each data source one by one, find the data source that needs to be managed, and then proceed with the management, which is very laborious. This data dashboard is the finishing touch!

  • Slow Query Dashboard: Supports viewing by data source, environment, tag, and data source type, making it clear at a glance which data source generates the most slow queries.

    image-20231211153329641

  • TOP 100 Data Sources: Displays the top 100 data sources with the most slow queries from high to low. Clicking on the details will quickly take you to the slow query page of the data source for management, which is very convenient.

    image-20231211154156600

  • Report Download: This feature is very useful when I need to submit optimization requirements to developers. On the data source slow query details page, all slow SQL within the target period can be integrated into a PDF document, which includes all the details that need to be rectified for developers to optimize.

    image-20231211155137077

Epilogue​

Since using this tool, every morning when I come to the company, I leisurely make a cup of coffee, then log in to the NineData console on the computer, check the slow query report from the previous day, and within a few minutes, I have a comprehensive understanding of the overall condition of our company's database in the past few days. I can directly start working on more core rectification work without spending a lot of time and energy investigating problems as before, and the work efficiency has improved significantly.

Most importantly, the reliability of this tool is very high. After a few days of use, there have been no omissions, and every small detail is captured and presented by it. The boss no longer needs to spend a lot of money hiring N DBAs to deal with these issues, and finally, there is no need to roll anymore.

Last updated on