Chinaunix首页 | 论坛 | 博客
  • 博客访问: 334240
  • 博文数量: 95
  • 博客积分: 2030
  • 博客等级: 大尉
  • 技术积分: 738
  • 用 户 组: 普通用户
  • 注册时间: 2006-08-12 09:11
文章分类

全部博文(95)

文章存档

2018年(4)

2017年(16)

2016年(61)

2015年(1)

2013年(3)

2006年(10)

我的朋友

分类: Mysql/postgreSQL

2017-01-04 13:22:54

一、安装工具
  地址:https://www.percona.com/downloads/percona-toolkit/
二、开启mysql慢查询
  方式一: 通过配置/etc/mysql/my.cnf文件
   
    1. [mysqld]
    2. slow_query_log=on
    3. slow_query_log_file=/tmp/slow_query_log.txt
    4. long_query_time=1
    5. slow_launch_time=1
  重启mysql数据库
  
  1. sudo /etc/init.d/mysql restart
三、分析慢查询
   
  1. pt-query-digest /tmp/slow_query_log.txt
结果:
  1. # A software update is available:
  2. # * The current version for Percona::Toolkit is 2.2.20.
  3. # 420ms user time, 20ms system time, 38.95M rss, 127.20M vsz
  4. # Current date: Wed Jan 4 13:21:52 2017
  5. # Hostname: wind
  6. # Files: /tmp/slow_query_log.txt
  7. # Overall: 7 total, 2 unique, 0.03 QPS, 0.14x concurrency ________________
  8. # Time range: 2017-01-04 13:13:46 to 13:17:45
  9. # Attribute total min max avg 95% stddev median
  10. # ============ ======= ======= ======= ======= ======= ======= =======
  11. # Exec time 33s 2s 20s 5s 19s 6s 2s
  12. # Lock time 595us 0 114us 85us 113us 36us 84us
  13. # Rows sent 601 1 100 85.86 97.36 33.72 97.36
  14. # Rows examine 40.05M 0 6.68M 5.72M 6.61M 2.31M 6.61M
  15. # Query size 365 41 54 52.14 51.63 3.91 51.63
  16. # Profile
  17. # Rank Query ID Response time Calls R/Call V/M Item
  18. # ==== ================== ============= ===== ======= ===== ==============
  19. # 1 0xA46990F88417662A 20.0003 61.3% 1 20.0003 0.00 SELECT dual
  20. # 2 0x65E47A879ED591AC 12.6266 38.7% 6 2.1044 0.01 SELECT stock.SC
  21. # Query 1: 0 QPS, 0x concurrency, ID 0xA46990F88417662A at byte 0 ________
  22. # This item is included in the report because it matches --limit.
  23. # Scores: V/M = 0.00
  24. # Time range: all events occurred at 2017-01-04 13:13:46
  25. # Attribute pct total min max avg 95% stddev median
  26. # ============ === ======= ======= ======= ======= ======= ======= =======
  27. # Count 14 1
  28. # Exec time 61 20s 20s 20s 20s 20s 0 20s
  29. # Lock time 0 0 0 0 0 0 0 0
  30. # Rows sent 0 1 1 1 1 1 0 1
  31. # Rows examine 0 0 0 0 0 0 0 0
  32. # Query size 11 41 41 41 41 41 0 41
  33. # String:
  34. # Databases stock
  35. # Hosts localhost
  36. # Users stock
  37. # Query_time distribution
  38. # 1us
  39. # 10us
  40. # 100us
  41. # 1ms
  42. # 10ms
  43. # 100ms
  44. # 1s
  45. # 10s+ ################################################################
  46. # Tables
  47. # SHOW TABLE STATUS FROM `stock` LIKE 'dual'\G
  48. # SHOW CREATE TABLE `stock`.`dual`\G
  49. # EXPLAIN /*!50100 PARTITIONS*/
  50. SELECT sleep(20) from dual
  51. LIMIT 0, 50000\G
  52. # Query 2: 0.09 QPS, 0.20x concurrency, ID 0x65E47A879ED591AC at byte 424
  53. # This item is included in the report because it matches --limit.
  54. # Scores: V/M = 0.01
  55. # Time range: 2017-01-04 13:16:41 to 13:17:45
  56. # Attribute pct total min max avg 95% stddev median
  57. # ============ === ======= ======= ======= ======= ======= ======= =======
  58. # Count 85 6
  59. # Exec time 38 13s 2s 2s 2s 2s 155ms 2s
  60. # Lock time 100 595us 85us 114us 99us 113us 13us 110us
  61. # Rows sent 99 600 100 100 100 100 0 100
  62. # Rows examine 100 40.05M 6.68M 6.68M 6.68M 6.68M 0 6.68M
  63. # Query size 88 324 54 54 54 54 0 54
  64. # String:
  65. # Databases stock
  66. # Hosts localhost
  67. # Users stock
  68. # Query_time distribution
  69. # 1us
  70. # 10us
  71. # 100us
  72. # 1ms
  73. # 10ms
  74. # 100ms
  75. # 1s ################################################################
  76. # 10s+
  77. # Tables
  78. # SHOW TABLE STATUS FROM `stock` LIKE 'SC'\G
  79. # SHOW CREATE TABLE `stock`.`SC`\G
  80. # EXPLAIN /*!50100 PARTITIONS*/
  81. SELECT * FROM stock.SC
  82. where c_id='100'
  83. LIMIT 0, 50000\G





阅读(210) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册