{"id":18949,"date":"2023-10-14T08:51:06","date_gmt":"2023-10-14T08:51:06","guid":{"rendered":"https:\/\/webhostinggeeks.com\/howto\/?p=18949"},"modified":"2023-10-14T08:51:06","modified_gmt":"2023-10-14T08:51:06","slug":"how-to-use-percona-toolkit-to-test-database-performance-on-a-linux-machine","status":"publish","type":"post","link":"https:\/\/webhostinggeeks.com\/howto\/how-to-use-percona-toolkit-to-test-database-performance-on-a-linux-machine\/","title":{"rendered":"How to Use Percona Toolkit to Test Database Performance on a Linux Machine"},"content":{"rendered":"

\"How<\/p>\n

Database performance is a critical aspect of ensuring that applications run efficiently and provide a seamless user experience. One of the challenges faced by database administrators and developers is identifying performance bottlenecks and optimizing queries for better execution. Thankfully, there are tools available that can assist in this endeavor. One such tool is the Percona Toolkit, a collection of advanced command-line tools to perform a variety of MySQL and MongoDB server and system tasks.<\/p>\n

In this guide, we will focus on how to use the Percona Toolkit on a Linux machine to assess the efficiency of database operations and evaluate query performance and latency. <\/p>\n

Let’s get started.<\/p>\n

1. Installing Percona Toolkit<\/h2>\n

To begin, you need to install the Percona Toolkit on your Linux machine:<\/p>\n

\r\nsudo apt-get update\r\nsudo apt-get install percona-toolkit\r\n<\/pre>\n
2. Using pt-query-digest for Query Analysis<\/h2>\n
One of the most powerful tools in the Percona Toolkit is pt-query-digest. This tool analyzes MySQL queries from slow logs, binary logs, or other log files.<\/p>\n
To analyze a slow log:<\/p>\n
\r\npt-query-digest \/path\/to\/slow.log\r\n<\/pre>\n
This will provide a detailed report of the queries, showing execution times, latencies, and other vital metrics.<\/p>\n
For example:<\/p>\n
\r\n# Query 1: 0.35 QPS, 0.02x concurrency, ID 0x1234567890ABCDEF\r\n# This item is included in the report because it matches --limit.\r\n# Scores: V\/M = 0.02\r\n# Time range: 2023-06-21 08:00:01 to 08:10:01\r\n# Attribute    pct   total     min     max     avg     95%  stddev  median\r\n# ============ === ======= ======= ======= ======= ======= ======= =======\r\n# Count         25     210\r\n# Exec time     20      10s     1ms     2s   100ms   500ms    10ms    10ms\r\n# Lock time     15       5s     1ms   100ms    50ms   100ms    10ms    10ms\r\n# Rows sent     30  21.58k      10     100  105.00  102.32   20.00   97.29\r\n# Rows examine  25  20.75k      10     100  100.00   97.29   20.00   97.29\r\n# Query size    20   2.82M  10.00k  20.00k  13.75k  19.46k   2.91k  12.58k\r\n<\/pre>\n
3. Evaluating Database Operations with pt-stalk<\/h2>\n
pt-stalk is a tool that collects data when a server’s condition meets specific criteria. It’s useful for diagnosing intermittent issues that might not be caught during regular monitoring.<\/p>\n
To start pt-stalk:<\/p>\n
\r\npt-stalk --threshold=95 --cycles=5 --interval=30\r\n<\/pre>\n
This command will trigger data collection when 95% or more of the CPU is used for five consecutive intervals of 30 seconds.<\/p>\n
For example:<\/p>\n
\r\nCollecting data since 95% or more of the CPU is used for five consecutive intervals of 30 seconds...\r\nData collection started at 2023-06-21 08:15:01\r\nWriting data to \/var\/lib\/pt-stalk\/2023-06-21_08:15:01\/\r\nData collection completed at 2023-06-21 08:20:01\r\n<\/pre>\n
4. Assessing Table Performance with pt-table-checksum<\/h2>\n
pt-table-checksum is useful for verifying that tables are in sync across replicas. It can also help identify tables that might be causing performance issues.<\/p>\n
To run a checksum on all tables:<\/p>\n
\r\npt-table-checksum h=localhost\r\n<\/pre>\n
For example:<\/p>\n
\r\n            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE\r\n06-21T08:25:01      0      0    21000       7       0   0.021 database1.table1\r\n06-21T08:25:02      0      0    10500       3       0   0.011 database2.table2\r\n<\/pre>\n
5. Monitoring with pt-mysql-summary<\/h2>\n
pt-mysql-summary provides an overview of the MySQL environment, including configuration, status variables, and hardware stats.<\/p>\n
To get a summary:<\/p>\n
\r\npt-mysql-summary\r\n<\/pre>\n
For example:<\/p>\n
\r\nMySQL 8.0.23-0ubuntu0.20.04.1\r\nUptime: 1 day, 2 hours, 3 minutes, 4 seconds\r\n\r\nService: mysql\r\nPort: 3306\r\nDatadir: \/var\/lib\/mysql\/\r\nLog: \/var\/log\/mysql\/error.log\r\n\r\nKey Buffer used: 100.00% (8M out of 8M)\r\nInnoDB Buffer Pool: 80.00% (1.6G out of 2G)\r\nInnoDB Data Reads: 1.5M (500\/s)\r\nInnoDB Data Writes: 1.2M (400\/s)\r\nInnoDB OS Log Writes: 900k (300\/s)\r\n<\/pre>\n
Commands Mentioned<\/h2>\n