MySQL – Identify Slow Query Without Slow Query Logs

MySQLperformanceslow-log

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.

Best Answer

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

Nov 24, 2011 : MySQL general query log performance effects
Apr 24, 2012 : Investigate peak in MySQL throughput
Jul 26, 2012 : What is running right now?

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 !!!

Related Solutions

PostgreSQL – Creating Index to Speed Up Aggregate LIKE Query

There is no index support for LIKE / ILIKE in PostgreSQL 8.4 - except for left anchored search terms.

Since PostgreSQL 9.1 the additional module pg_trgm provides operator classes for GIN and GiST trigram indices supporting LIKE / ILIKE or regular expressions (operators ~ and friends). Install once per database:

CREATE EXTENSION pg_trgm;

Example GIN index:

CREATE INDEX tbl_col_gin_trgm_idx ON tbl USING gin (col gin_trgm_ops);

MySQL – Why Are Slow Queries Not Logging?

I think I got the answer:

You need to put those options under the [mysqld] section

[mysqld]
slow-query-log=1
slow-query-log-file=/var/logs/my.slow.log
long_query_time=1

and restart mysql

UPDATE 2013-03-05 16:36 EST

I don't know why this is still happening, but please try this:

service mysql stop
rm -f /var/logs/my.slow.log
touch /var/logs/my.slow.log
chown mysql:mysql /var/logs/my.slow.log
service mysql start

then run SELECT SLEEP(10); and see if it lands in /var/logs/my.slow.log