MySQL 慢查询日志分析
1. pt-query-digest分析慢查询日志
pt-query-digest --report slow.log
2. 报告最近半个小时的慢查询：
pt-query-digest --report --since 1800s slow.log
3. 报告一个时间段的慢查询：
pt-query-digest --report --since '2013-02-10 21:48:59' --until '2013-02-16 02:33:50' slow.log
4. 报告只含select语句的慢查询:
pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow.log
5. 报告针对某个用户的慢查询:
pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' slow.log
6. 报告所有的全表扫描或full join的慢查询:
pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") || (($event->{Full_join} || "") eq "yes")' slow.log
更多filter的 事件属性
将慢查询日志的分析结果可视化
使用pt-query-digest分析慢查询日志并将查询分析数据保存到MySQL数据库表中.然后使用 Query-Digest-UI 来展示分析结果.
由于Query-Digest-UI是基于PHP的Web应用程序,因此需要LAMP环境的支持.
查询分析结果可视化步骤如下: 1)创建相关数据库表
-- install.sql-- Create the database needed for the Query-Digest-UIDROP DATABASE IF EXISTS slow_query_log;CREATE DATABASE slow_query_log;USE slow_query_log;-- Create the global query review tableCREATE TABLE `global_query_review` (`checksum` bigint(20) unsigned NOT NULL,`fingerprint` text NOT NULL,`sample` longtext NOT NULL,`first_seen` datetime DEFAULT NULL,`last_seen` datetime DEFAULT NULL,`reviewed_by` varchar(20) DEFAULT NULL,`reviewed_on` datetime DEFAULT NULL,`comments` text,`reviewed_status` varchar(24) DEFAULT NULL,PRIMARY KEY (`checksum`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- Create the historical query review tableCREATE TABLE `global_query_review_history` (`hostname_max` varchar(64) NOT NULL,`db_max` varchar(64) DEFAULT NULL,`checksum` bigint(20) unsigned NOT NULL,`sample` longtext NOT NULL,`ts_min` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',`ts_max` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',`ts_cnt` float DEFAULT NULL,`Query_time_sum` float DEFAULT NULL,`Query_time_min` float DEFAULT NULL,`Query_time_max` float DEFAULT NULL,`Query_time_pct_95` float DEFAULT NULL,`Query_time_stddev` float DEFAULT NULL,`Query_time_median` float DEFAULT NULL,`Lock_time_sum` float DEFAULT NULL,`Lock_time_min` float DEFAULT NULL,`Lock_time_max` float DEFAULT NULL,`Lock_time_pct_95` float DEFAULT NULL,`Lock_time_stddev` float DEFAULT NULL,`Lock_time_median` float DEFAULT NULL,`Rows_sent_sum` float DEFAULT NULL,`Rows_sent_min` float DEFAULT NULL,`Rows_sent_max` float DEFAULT NULL,`Rows_sent_pct_95` float DEFAULT NULL,`Rows_sent_stddev` float DEFAULT NULL,`Rows_sent_median` float DEFAULT NULL,`Rows_examined_sum` float DEFAULT NULL,`Rows_examined_min` float DEFAULT NULL,`Rows_examined_max` float DEFAULT NULL,`Rows_examined_pct_95` float DEFAULT NULL,`Rows_examined_stddev` float DEFAULT NULL,`Rows_examined_median` float DEFAULT NULL,`Rows_affected_sum` float DEFAULT NULL,`Rows_affected_min` float DEFAULT NULL,`Rows_affected_max` float DEFAULT NULL,`Rows_affected_pct_95` float DEFAULT NULL,`Rows_affected_stddev` float DEFAULT NULL,`Rows_affected_median` float DEFAULT NULL,`Rows_read_sum` float DEFAULT NULL,`Rows_read_min` float DEFAULT NULL,`Rows_read_max` float DEFAULT NULL,`Rows_read_pct_95` float DEFAULT NULL,`Rows_read_stddev` float DEFAULT NULL,`Rows_read_median` float DEFAULT NULL,`Merge_passes_sum` float DEFAULT NULL,`Merge_passes_min` float DEFAULT NULL,`Merge_passes_max` float DEFAULT NULL,`Merge_passes_pct_95` float DEFAULT NULL,`Merge_passes_stddev` float DEFAULT NULL,`Merge_passes_median` float DEFAULT NULL,`InnoDB_IO_r_ops_min` float DEFAULT NULL,`InnoDB_IO_r_ops_max` float DEFAULT NULL,`InnoDB_IO_r_ops_pct_95` float DEFAULT NULL,`InnoDB_IO_r_bytes_pct_95` float DEFAULT NULL,`InnoDB_IO_r_bytes_stddev` float DEFAULT NULL,`InnoDB_IO_r_bytes_median` float DEFAULT NULL,`InnoDB_IO_r_wait_min` float DEFAULT NULL,`InnoDB_IO_r_wait_max` float DEFAULT NULL,`InnoDB_IO_r_wait_pct_95` float DEFAULT NULL,`InnoDB_IO_r_ops_stddev` float DEFAULT NULL,`InnoDB_IO_r_ops_median` float DEFAULT NULL,`InnoDB_IO_r_bytes_min` float DEFAULT NULL,`InnoDB_IO_r_bytes_max` float DEFAULT NULL,`InnoDB_IO_r_wait_stddev` float DEFAULT NULL,`InnoDB_IO_r_wait_median` float DEFAULT NULL,`InnoDB_rec_lock_wait_min` float DEFAULT NULL,`InnoDB_rec_lock_wait_max` float DEFAULT NULL,`InnoDB_rec_lock_wait_pct_95` float DEFAULT NULL,`InnoDB_rec_lock_wait_stddev` float DEFAULT NULL,`InnoDB_rec_lock_wait_median` float DEFAULT NULL,`InnoDB_queue_wait_min` float DEFAULT NULL,`InnoDB_queue_wait_max` float DEFAULT NULL,`InnoDB_queue_wait_pct_95` float DEFAULT NULL,`InnoDB_queue_wait_stddev` float DEFAULT NULL,`InnoDB_queue_wait_median` float DEFAULT NULL,`InnoDB_pages_distinct_min` float DEFAULT NULL,`InnoDB_pages_distinct_max` float DEFAULT NULL,`InnoDB_pages_distinct_pct_95` float DEFAULT NULL,`InnoDB_pages_distinct_stddev` float DEFAULT NULL,`InnoDB_pages_distinct_median` float DEFAULT NULL,`QC_Hit_cnt` float DEFAULT NULL,`QC_Hit_sum` float DEFAULT NULL,`Full_scan_cnt` float DEFAULT NULL,`Full_scan_sum` float DEFAULT NULL,`Full_join_cnt` float DEFAULT NULL,`Full_join_sum` float DEFAULT NULL,`Tmp_table_cnt` float DEFAULT NULL,`Tmp_table_sum` float DEFAULT NULL,`Filesort_cnt` float DEFAULT NULL,`Filesort_sum` float DEFAULT NULL,`Tmp_table_on_disk_cnt` float DEFAULT NULL,`Tmp_table_on_disk_sum` float DEFAULT NULL,`Filesort_on_disk_cnt` float DEFAULT NULL,`Filesort_on_disk_sum` float DEFAULT NULL,`Bytes_sum` float DEFAULT NULL,`Bytes_min` float DEFAULT NULL,`Bytes_max` float DEFAULT NULL,`Bytes_pct_95` float DEFAULT NULL,`Bytes_stddev` float DEFAULT NULL,`Bytes_median` float DEFAULT NULL,UNIQUE KEY `hostname_max` (`hostname_max`,`checksum`,`ts_min`,`ts_max`),KEY `ts_min` (`ts_min`),KEY `checksum` (`checksum`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2) 创建数据库账号
$ mysql -uroot -p -h 192.168.1.190 <p>3) 配置Query-Digest-UI</p><pre class="brush:php;toolbar:false">git clone https://github.com/kormoc/Query-Digest-UI.gitcd Query-Digest-UIcp config.php.example config.phpvi config.php$reviewhost = array(// Replace hostname and database in this setting// use host=hostname;port=portnum if not the default port 'dsn' => 'mysql:host=192.168.1.190;port=3306;dbname=slow_query_log', 'user' => 'slowlog', 'password' => '123456',// See http://www.percona.com/doc/percona-toolkit/2.1/pt-query-digest.html#cmdoption-pt-query-digest--review 'review_table'=> 'global_query_review',// This table is optional. You don't need it, but you lose detailed stats// Set to a blank string to disable// See http://www.percona.com/doc/percona-toolkit/2.1/pt-query-digest.html#cmdoption-pt-query-digest--review-history 'history_table' => 'global_query_review_history',);
4)使用pt-query-digest分析日志并将分析结果导入数据库
pt-query-digest --user=slowlog --password=123456 --review h=192.168.1.190,D=slow_query_log,t=global_query_review --review-history h=192.168.1.190,D=slow_query_log,t=global_query_review_history --no-report --limit=0% --filter=" /$event->{Bytes} = length(/$event->{arg}) and /$event->{hostname}=/"$HOSTNAME/"" /usr/local/mysql/data/slow.log5)访问web界面查看可视化结果
转自：http://www.zrwm.com/?p=2668
文章标题: 本文地址: 除非注明，博客文章均为"运维生存时间"原创，转载请标明本文地址Q群：39514058
Adding Users to MySQL: The Complete TutorialMay 12, 2025 am 12:14 AMMastering the method of adding MySQL users is crucial for database administrators and developers because it ensures the security and access control of the database. 1) Create a new user using the CREATEUSER command, 2) Assign permissions through the GRANT command, 3) Use FLUSHPRIVILEGES to ensure permissions take effect, 4) Regularly audit and clean user accounts to maintain performance and security.
Mastering MySQL String Data Types: VARCHAR vs. TEXT vs. CHARMay 12, 2025 am 12:12 AMChooseCHARforfixed-lengthdata,VARCHARforvariable-lengthdata,andTEXTforlargetextfields.1)CHARisefficientforconsistent-lengthdatalikecodes.2)VARCHARsuitsvariable-lengthdatalikenames,balancingflexibilityandperformance.3)TEXTisidealforlargetextslikeartic
MySQL: String Data Types and Indexing: Best PracticesMay 12, 2025 am 12:11 AMBest practices for handling string data types and indexes in MySQL include: 1) Selecting the appropriate string type, such as CHAR for fixed length, VARCHAR for variable length, and TEXT for large text; 2) Be cautious in indexing, avoid over-indexing, and create indexes for common queries; 3) Use prefix indexes and full-text indexes to optimize long string searches; 4) Regularly monitor and optimize indexes to keep indexes small and efficient. Through these methods, we can balance read and write performance and improve database efficiency.
MySQL: How to Add a User RemotelyMay 12, 2025 am 12:10 AMToaddauserremotelytoMySQL,followthesesteps:1)ConnecttoMySQLasroot,2)Createanewuserwithremoteaccess,3)Grantnecessaryprivileges,and4)Flushprivileges.BecautiousofsecurityrisksbylimitingprivilegesandaccesstospecificIPs,ensuringstrongpasswords,andmonitori
The Ultimate Guide to MySQL String Data Types: Efficient Data StorageMay 12, 2025 am 12:05 AMTostorestringsefficientlyinMySQL,choosetherightdatatypebasedonyourneeds:1)UseCHARforfixed-lengthstringslikecountrycodes.2)UseVARCHARforvariable-lengthstringslikenames.3)UseTEXTforlong-formtextcontent.4)UseBLOBforbinarydatalikeimages.Considerstorageov
MySQL BLOB vs. TEXT: Choosing the Right Data Type for Large ObjectsMay 11, 2025 am 12:13 AMWhen selecting MySQL's BLOB and TEXT data types, BLOB is suitable for storing binary data, and TEXT is suitable for storing text data. 1) BLOB is suitable for binary data such as pictures and audio, 2) TEXT is suitable for text data such as articles and comments. When choosing, data properties and performance optimization must be considered.
MySQL: Should I use root user for my product?May 11, 2025 am 12:11 AMNo,youshouldnotusetherootuserinMySQLforyourproduct.Instead,createspecificuserswithlimitedprivilegestoenhancesecurityandperformance:1)Createanewuserwithastrongpassword,2)Grantonlynecessarypermissionstothisuser,3)Regularlyreviewandupdateuserpermissions
MySQL String Data Types Explained: Choosing the Right Type for Your DataMay 11, 2025 am 12:10 AMMySQLstringdatatypesshouldbechosenbasedondatacharacteristicsandusecases:1)UseCHARforfixed-lengthstringslikecountrycodes.2)UseVARCHARforvariable-lengthstringslikenames.3)UseBINARYorVARBINARYforbinarydatalikecryptographickeys.4)UseBLOBorTEXTforlargeuns
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
WebStorm Mac version
Useful JavaScript development tools
SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.
mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Atom editor mac version download
The most popular open source editor
