Why Analyze Raw MySQL Query Logs
Alysha Perry
can analyze queries from MySQL slow, general, and binary logs. (Binary logs must first be converted to text, see --type). It can also use SHOW PROCESSLIST and MySQL protocol data from tcpdump. By default, the tool reports which queries are the slowest, and therefore the most important to optimize. More complex and custom-tailored reports can be created by using options like --group-by, --filter, and --embedded-attributes.
These are just some of the examples of what you can find out by querying raw slow query logs. They contain a ton of information about query execution (especially in Percona Server for MySQL) that allows you to use them both for performance analysis and some security and auditing purposes.
slow_query_log = 1: Use this statement to enable the query log log_slow_queries = /var/log/mysql/mysql-slow.log This statement informs MySQL to store the slow query logs within this file: var/log/mysql/mysql-slow.log
pt-query-digest analyzes MySQL queries from slow, general, and binary logfiles. It can also analyze queries from SHOW PROCESSLIST and MySQLprotocol data from tcpdump. By default, queries are grouped by fingerprintand reported in descending order of query time (i.e. the slowest queriesfirst). If no FILES are given, the tool reads STDIN. The optionalDSN is used for certain options like --since and --until.
pt-query-digest is a sophisticated but easy to use tool for analyzingMySQL queries. It can analyze queries from MySQL slow, general, and binarylogs. (Binary logs must first be converted to text, see --type).It can also use SHOW PROCESSLIST and MySQL protocol data from tcpdump.By default, the tool reports which queries are the slowest, and thereforethe most important to optimize. More complex and custom-tailored reportscan be created by using options like --group-by, --filter, and--embedded-attributes.
Query analysis is a best-practice that should be done frequently. Tomake this easier, pt-query-digest has two features: query review(--review) and query history (--history). When the --reviewoption is used, all unique queries are saved to a database. When thetool is ran again with --review, queries marked as reviewed inthe database are not printed in the report. This highlights new queriesthat need to be reviewed. When the --history option is used,query metrics (query time, lock time, etc.) for each unique query aresaved to database. Each time the tool is ran with --history, themore historical data is saved which can be used to trend and analyzequery performance over time.
pt-query-digest analyzes MySQL queries from slow, general, and binary log files. It can also analyze queries from SHOW PROCESSLIST and MySQL protocol data from tcpdump. By default, queries are grouped by fingerprint and reported in descending order of query time (i.e. the slowest queries first). If no FILES are given, the tool reads STDIN. The optional DSN is used for certain options like --since and --until.
This blog post includes a video that provides an overview of how MySQL slow query logs, the Log Analytics tool, and workbooks templates help to visualize Query Performance Insight data in Azure Database for MySQL - Flexible Server.
You need a tool to sift through the slow query log to get those statistics, and Percona has just the tool for it: pt-query-digest. This tool has many other tricks up its sleeve, but for this post, I just want to cover how it helps me analyze and summarize slow query logs so I can quickly dig into the worst queries that might be bringing down my production application or Drupal or other PHP-based website.
When Query Store is enabled on your server, you may see the queries like "CALL mysql.az_procedure_collect_wait_stats (900, 30);" logged in your slow query logs. This behavior is expected as the Query Store feature collects statistics about your queries.
If your tables are not indexed, setting the log_queries_not_using_indexes and log_throttle_queries_not_using_indexes parameters to ON may affect MySQL performance since all queries running against these non-indexed tables will be written to the slow query log.
If you plan on logging slow queries for an extended period of time, it is recommended to set log_output to "None". If set to "File", these logs are written to the local server storage and can affect MySQL performance.
For local server storage, you can list and download slow query logs using the Azure portal or the Azure CLI. In the Azure portal, navigate to your server in the Azure portal. Under the Monitoring heading, select the Server Logs page. For more information on Azure CLI, see Configure and access slow query logs using Azure CLI.
Azure Database for MySQL is integrated with Azure Monitor Diagnostic Logs. Once you have enabled slow query logs on your MySQL server, you can choose to have them emitted to Azure Monitor logs, Event Hubs, or Azure Storage. To learn more about how to enable diagnostic logs, see the how to section of the diagnostic logs documentation.
Once your slow query logs are piped to Azure Monitor Logs through Diagnostic Logs, you can perform further analysis of your slow queries. Below are some sample queries to help you get started. Make sure to update the below with your server name.
Due to obvious performance concerns, we did not consider using slow query log. We could set a threshold for queries and then log all the queries crossing the threshold in a file, which we could analyze later. The disadvantage of this approach is that it cannot capture all the queries. If you set the threshold to 0 to capture all queries, it can be catastrophic, because millions of queries will be hitting the server, and logging all of them to a file leads to high IO and drastically reduces the throughput. So, using slow query log is completely ruled out.
The benefits of Query Analyzer have been numerous. These include allowing our database engineers to identify problematic queries at a single glance, to compare a week-over-week overlay of query activity, and to troubleshoot database slowdowns quickly and efficiently. Developers and business analysts are able to visualize query trends, check the query load in a staging environment before entering development, and obtain metrics per table and database for things like number of inserts, updates, deletes, etc., through which they can analyze the business. From a security standpoint, Query Analyzer allows us to receive an alert whenever a new query hits the database, and we can also audit the queries that are accessing sensitive information. Lastly, analyzing the query load allows us to ensure that queries are distributed evenly across servers, and thereby optimize our hardware. We can also conduct capacity planning more accurately.
You can monitor the MySQL logs directly through the Amazon RDS console, Amazon RDSAPI, AWS CLI, or AWS SDKs. You can also access MySQL logs by directing the logs to a database table in the maindatabase and querying that table. You can use the mysqlbinlog utility to download a binary log.
I am trying to reproduce production activity in time for benchmarking purposes from the mysql general query log. I'd like to be able to use the date data from the log to reprduce the time intervals between queries. So if query 1 executes at 9AM and query 2 executes at 10AM then I want the benchmark to execute these queries an hour apart.
In this guide, logs provide the data source to be analyzed. However, you canapply the concepts from this guide to analysis of other complementarysecurity-related data from Google Cloud, such as security findingsfrom Security Command Center.Provided in Security Command Center Premium is a list of regularly-updated manageddetectors that are designed to identify threats, vulnerabilities, andmisconfigurations within your systems in near real-time. By analyzing thesesignals from Security Command Center and correlating them with logs ingested in yoursecurity analytics tool as described in this guide, you can achieve a broaderperspective of potential security threats.
The diagram starts with the following security data sources: logs fromCloud Logging, asset changes from Cloud Asset Inventory, and security findings fromSecurity Command Center. The diagram then shows these security data sources beingrouted into the security analytics tool of your choice: Log Analytics in Cloud Logging,BigQuery, Chronicle, or a third-party SIEM. Finally, thediagram shows using CSA queries with your analytics tool to analyze the collatedsecurity data.
Route logs: After identifying and enabling the logs to beanalyzed, the next step is to route and aggregate the logs from yourorganization, including any contained folders, projects, and billing accounts.How you route logs depends on the analytics tool that you use.
Analyze logs: After you route the logs into an analytics tool, the nextstep is to perform an analysis of these logs to identify anypotential security threats. How you analyze the logs depends on theanalytics tool that you use. If you use Log Analytics or BigQuery,you can analyze the logs by using SQL queries. If you use Chronicle,you analyze the logs by using YARA-L rules.If you are using a third-party SIEM tool, you use the query languagespecified by that tool.
In this guide, you'll find SQL queries that you can use to analyze thelogs in either Log Analytics or BigQuery. The SQL queriesprovided in this guide come from the Community Security Analytics (CSA) project. CSA is an open-source set of foundational security analyticsdesigned to provide you with a baseline of pre-built queries and rules thatyou can reuse to start analyzing your Google Cloud logs.
When you route logs to a log bucket upgraded to Log Analytics, you canview and query all log entries through a single log view with a unified schemafor all log types. Follow these steps to verify the logs are correctly routed.
You can run a broad range of queries against your audit and platform logs. Thefollowing list provides a set of sample security questions that you might want toask of your own logs. For each question in this list, there are two versions ofthe corresponding CSA query: one for use with Log Analytics and one for usewith BigQuery. Use the query version that matches the sink destinationthat you previously set up.
dd2b598166