15

I am wondering is there any other way to to check our slow queries without logging slow query. Suppose, I have a highily busy server can't afford to log much to save memory and I/Os. Then, is there any other way available to check if I have a slow query? I know, we can do profiling of the query but still not sure what exactly to do to identify which query is the one taking most of the time and memory.

Just started mysql administration and not sure how to handle this. Any guidance will be highly appreciated.

Improve this question
3
  • 1
    You can enable slow query log for 5-10 minutes. Check the logged queries. Correct them (rewrite, indexes, etc). Update application. Rerun until you have no slow queries or performance is good enough. All these steps could/should be first done in a test environament of course, without affecting your production.
    – ypercubeᵀᴹ
    May 15, 2015 at 14:01
  • Thanks for the update. But is there no way available to do this without log?
    – Astha
    May 15, 2015 at 14:10
  • 1
    Astha: There are other tools which allow you to do this without the slow-query log, but I believe they are all outside the base MySQL packages. Look at Percona Toolkit (as Rolando says below), MySQL Enterprise, and MySQL Workbench (a desktop client). Most of these tools work best with MySQL 5.5 and above, and MySQL 5.6 introduces more features which allow better performance monitoring. I'm upgrading all of our databases to 5.6 because I like the new Performance Schema.
    – Stefan Lasiewski
    May 15, 2015 at 15:57

2 Answers 2

Reset to default
8

If you do not want to enable the slow query log at all, I have a suggestion

You can use pt-query-digest over an interval of time.

I have suggested this a few times in the DBA StackExchange

If you look at my Nov 24, 2011 link, I provided a shell script you can crontab to launch pt-query-digest.

GIVE IT A TRY !!!

Improve this answer
2
  • 1
    Why are you shouting?
    – surfmuggle
    Oct 3, 2020 at 11:49
  • 1
    @surfmuggle I am just enthusiastic !!!
    – RolandoMySQLDBA
    Oct 6, 2020 at 16:10
9

You can run the following statement in a loop in a script that triggers the statement every 10 seconds for example.

mysql -e 'SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where time>10 and command<>"Sleep"'

You can customize it to give you more or less info depending on the query you issue.

mysql> desc INFORMATION_SCHEMA.PROCESSLIST;
+---------------+---------------------+------+-----+---------+-------+
| Field         | Type                | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+-------+
| ID            | bigint(21) unsigned | NO   |     | 0       |       |
| USER          | varchar(16)         | NO   |     |         |       |
| HOST          | varchar(64)         | NO   |     |         |       |
| DB            | varchar(64)         | YES  |     | NULL    |       |
| COMMAND       | varchar(16)         | NO   |     |         |       |
| TIME          | int(7)              | NO   |     | 0       |       |
| STATE         | varchar(64)         | YES  |     | NULL    |       |
| INFO          | longtext            | YES  |     | NULL    |       |
| TIME_MS       | bigint(21)          | NO   |     | 0       |       |
| ROWS_SENT     | bigint(21) unsigned | NO   |     | 0       |       |
| ROWS_EXAMINED | bigint(21) unsigned | NO   |     | 0       |       |
+---------------+---------------------+------+-----+---------+-------+
11 rows in set (0.00 sec)

In order not to save the same query many times, you may use the hash of the query as a unique key.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.