January 30 2019

Percona Toolkit

We profile our MySQL Queries with Percona Toolkit.

PERSA Toolkit Banner

SQL Query Profiling is a process that involves detailed analysis of the efficiency of an SQL query in a database. This process helps database developers identify potential bugs, bottlenecks, inefficiencies, or performance issues that can hinder the speed and efficiency of SQL queries.

Query profiling can be absolutely essential to ensure peak performance of an application. A significant number of performance issues can be resolved by optimizing SQL queries. These optimizations may include reducing the number of queries executed, modifying queries to use more efficient indexes, or redesigning data structures to reduce processing time.

Now let's look at some of the benefits of optimizing SQL queries:

  1. Performance Improvement: Optimized queries can perform operations faster than non-optimized ones. This results in an overall increase in application or system performance.
  2. Resource Efficiency: Efficient queries require less system resources, such as memory and CPU. This means that the system can handle multiple requests simultaneously, enhancing the ability to serve multiple users simultaneously.
  3. Faster Response: An optimized query reduces the waiting time of the end user. The end user experience will therefore be improved, as the user will not have to wait long to get answers to his requests.
  4. Scalability: A database with optimized queries will be easier to scale, as each query uses fewer resources and processing is done faster. This results in a more resilient system as the load increases.

Remember that the main goal is to ensure that your applications or systems are efficient, responsive and scalable. Profiling and optimizing SQL queries is a critical step in achieving this goal.

Introduction to Percona Toolkit.

Percona is an internationally recognized company for its expertise in the open source database industry, specializing in the provision of innovative software and services for MySQL, MariaDB, MongoDB, and PostgreSQL. It provides solutions to customers around the world to improve the efficiency, performance and scalability of their databases. Percona is committed to developing tools and techniques to help developers and database administrators better manage their systems.

One of Percona's best known and most widely used tools is the Percona Toolkit. This advanced toolset is designed to handle many of the complex and repetitive tasks that database administrators face. Percona Toolkit can help make these operations more efficient, more reliable and more secure.

The Toolkit is a collection of more than 30 advanced tools for MySQL administrators and developers. These tools address a variety of issues and tasks ranging from replication management, query profiling, data backup, to data consistency verification.

Among the main features of Percona Toolkit, the ability to profile SQL queries is among the most appreciated. The query profiling tool, known as pt-query-digest, can analyze MySQL query logs to determine where the database is spending its time. pt-query-digest provides a complete view of the SQL queries that have been executed, allowing database administrators to easily identify which queries are taking the most time or resources.

Another notable tool included in the Percona Toolkit is pt-table-checksum, which can be used to check data consistency between a master MySQL server and its slaves. This tool is especially useful for keeping data in sync in a replication environment.

Percona Toolkit offers a number of benefits for database administrators:

  1. Performance Optimization: Percona Toolkit can help identify the SQL queries that consume the most resources and time. This can lead to significant improvements in database performance.
  2. Security and Reliability: The tools included in the toolkit are developed with a strong focus on security and reliability, helping to prevent problems that can lead to data loss or service outages.
  3. Time saving: Percona Toolkit can automate many of the repetitive tasks that database administrators face, saving valuable time that can be used for other tasks.
  4. Compatibility: Percona Toolkit is compatible with MySQL, Percona Server for MySQL, MariaDB, and other MySQL variants.

Percona Toolkit is an essential tool for database administrators who want to improve the performance of their systems, save time, and maintain the security and reliability of their databases. Its powerful SQL query profiling capabilities make it a highly valuable tool for optimizing database performance.

Percona Query Digest Toolkit

The pt-query-digest is an essential component of the Percona Toolkit and was specifically designed to analyze, aggregate and report statistics on SQL queries executed against a MySQL database. This tool aims to identify which queries are the longest to run or consume the most resources, providing a detailed picture of database operations and allowing developers and administrators to focus on areas that need optimization.

Pt-query-digest can parse a variety of inputs, including slow MySQL query logs, running processlists, and binary log files. The tool can then produce a report summarizing the queries, sorting them in order of total cost (the total time spent executing a particular query). This makes it easy to identify which queries are negatively impacting database performance.

Furthermore, pt-query-digest not only detects slow queries, it can also identify anomalies and patterns in queries, which can be of great help in detecting potential problems or bugs in your application code.

Among the main advantages of pt-query-digest are:

  1. Identifying Problem Queries: Pt-query-digest allows database administrators to quickly identify the queries that are consuming the most time or resources, allowing them to focus on optimizing these queries.
  2. Prevention of Performance Problems: With its ability to identify patterns and anomalies in queries, pt-query-digest can help prevent performance problems before they become critical.
  3. Optimizing Database Performance: Pt-query-digest provides valuable data that can be used to optimize database performance. This can include modifying problematic queries, modifying the structure of the database, or modifying MySQL server settings.
  4. Detailed analysis: Pt-query-digest provides detailed analysis of SQL queries, making it possible to identify bottlenecks or inefficiencies in queries. This analysis can be very useful for performance tuning.

In conclusion, pt-query-digest is a powerful and versatile tool that can provide valuable insight into database performance, allowing administrators and developers to improve the efficiency and speed of their applications.

An example of practical use of Percona Toolkit Query Digest.

For the past few months, I've been using MySQL with some issues on a client's server. I had installed MySQL, MariaDB and Percona SQL Server with the same data.

After a few days I looked at the slow query logs, and I saw that the file size is around 300MB. Analyzing the file could have taken a very long time but the Percona Toolkit helped. The toolkit can work with MySQL, MariaDB or Percona SQL Server. There are a lot of commands in it, but I only have one to parse the slow query and report the results.

The slow query log is approximately 300 MB in size. It would take a lot of effort to scroll through and find out what happened and analyze all the records. Here's how Percona Toolkit has helped tremendously :

percona-toolkit's 'pt-query-digest' command takes the slow query log and generates a summary which is more or less similar to mysql.

pt-query-digest /var/log/mysql/slow_query_log

This contains the file and produces a report that is in line with the ADDM report we get in Oracle.

Below is a summary of the 300MB raw file analysis:

It all comes down to 6 queries that require our attention. Furthermore, among the six, the optimization of the first two of them benefited an improvement of almost 90%.

So if you are running a MySQL based database, percona-toolkit it's a tool indispensable .

I only used this single command out of necessity, despite being very powerful and robust, browse the other commands given in the link , here

Do you have doubts? Don't know where to start? Contact us!

We have all the answers to your questions to help you make the right choice.

Chat with us

Chat directly with our presales support.

0256569681

Contact us by phone during office hours 9:30 - 19:30

Contact us online

Open a request directly in the contact area.

INFORMATION

Managed Server Srl is a leading Italian player in providing advanced GNU/Linux system solutions oriented towards high performance. With a low-cost and predictable subscription model, we ensure that our customers have access to advanced technologies in hosting, dedicated servers and cloud services. In addition to this, we offer systems consultancy on Linux systems and specialized maintenance in DBMS, IT Security, Cloud and much more. We stand out for our expertise in hosting leading Open Source CMS such as WordPress, WooCommerce, Drupal, Prestashop, Joomla, OpenCart and Magento, supported by a high-level support and consultancy service suitable for Public Administration, SMEs and any size.

Red Hat, Inc. owns the rights to Red Hat®, RHEL®, RedHat Linux®, and CentOS®; AlmaLinux™ is a trademark of AlmaLinux OS Foundation; Rocky Linux® is a registered trademark of the Rocky Linux Foundation; SUSE® is a registered trademark of SUSE LLC; Canonical Ltd. owns the rights to Ubuntu®; Software in the Public Interest, Inc. holds the rights to Debian®; Linus Torvalds holds the rights to Linux®; FreeBSD® is a registered trademark of The FreeBSD Foundation; NetBSD® is a registered trademark of The NetBSD Foundation; OpenBSD® is a registered trademark of Theo de Raadt. Oracle Corporation owns the rights to Oracle®, MySQL®, and MyRocks®; Percona® is a registered trademark of Percona LLC; MariaDB® is a registered trademark of MariaDB Corporation Ab; REDIS® is a registered trademark of Redis Labs Ltd. F5 Networks, Inc. owns the rights to NGINX® and NGINX Plus®; Varnish® is a registered trademark of Varnish Software AB. Adobe Inc. holds the rights to Magento®; PrestaShop® is a registered trademark of PrestaShop SA; OpenCart® is a registered trademark of OpenCart Limited. Automattic Inc. owns the rights to WordPress®, WooCommerce®, and JetPack®; Open Source Matters, Inc. owns the rights to Joomla®; Dries Buytaert holds the rights to Drupal®. Amazon Web Services, Inc. holds the rights to AWS®; Google LLC holds the rights to Google Cloud™ and Chrome™; Microsoft Corporation holds the rights to Microsoft®, Azure®, and Internet Explorer®; Mozilla Foundation owns the rights to Firefox®. Apache® is a registered trademark of The Apache Software Foundation; PHP® is a registered trademark of the PHP Group. CloudFlare® is a registered trademark of Cloudflare, Inc.; NETSCOUT® is a registered trademark of NETSCOUT Systems Inc.; ElasticSearch®, LogStash®, and Kibana® are registered trademarks of Elastic NV Hetzner Online GmbH owns the rights to Hetzner®; OVHcloud is a registered trademark of OVH Groupe SAS; cPanel®, LLC owns the rights to cPanel®; Plesk® is a registered trademark of Plesk International GmbH; Facebook, Inc. owns the rights to Facebook®. This site is not affiliated, sponsored or otherwise associated with any of the entities mentioned above and does not represent any of these entities in any way. All rights to the brands and product names mentioned are the property of their respective copyright holders. Any other trademarks mentioned belong to their registrants. MANAGED SERVER® is a trademark registered at European level by MANAGED SERVER SRL, Via Enzo Ferrari, 9, 62012 Civitanova Marche (MC), Italy.

Back to top