Technology TidBits

Answers to various technical questions on php programming, mysql, linux, and many more categories.
25% off Hosting at HostGator.com:
Use Code techbits25



How can I log slow queries in mysql?

Last updated: 05/21/2012

Add these lines to your my.cnf (usually in /etc/my.cnf)

log-slow-queries = slow.log
long_query_time = 20

This logs any queries that take longer than 20 seconds to a file called "slow.log" in your data directory.  Your data directory should be in /var/db/mysql, or /var/lib/mysql on a unix system.

NOTE:  You need to restart mysql to have these changes take effect - usually run this command: 

/etc/init.d/mysql restart 

Anyway the logs generated here should help you pinpoint any queries that are really bogging down. 

AFAIK, this setting doesn't slow down the server much, unless a lot of queries are getting logged.  But if you are not actively checking for slow queries, you should probably turn this off. 

blog comments powered by Disqus
Have your own Tech-bit to contribute? Submit it here

Other questions in this category:
What causes an error 150 when loading data into a mysql database?
How can I display long records in mysql without wrapping?
How can I import data in mysql
How can I get mysql table definitions in my php script?
How can I execute commands on mysql using a php script?
What causes mysql to give an error 145?
How can I add a conditional statement in the field list of my sql statement?
How can I dump table definitions (with no data) using mysqldump?
How can I manipulate a table with a reserved word as a column name?
How can I find out table information in mysql?
How can I rename a field in a mysql table?
How can a change a user password in mysql?
How can I reset the auto_increment id field in my table?
How can I see the syntax used to create a table in mysql?
How can I delete or remove a field from a mysql table?
What does an error 'Got error 122 from storage engine' mean?
How can I run sql commands from a file or import tables from the mysqldump?
How can I create a CSV file from a query?
How can I dump selected records using mysqldump?
How can I sum a field in mySQL only if it contains a value?
How can I find out all the unique values stored in a specific field?
How can I give a custom name to a field in my SQL query?
How can I copy a table from one database to another?
How can I do a search and replace in a mysql table?
How can I load in a sql file using the mysql command line?
How can I setup a field to record the last update of a record in mysql?
I need a php function to put all rows from a mysql query into an array
How can I find old or expired records in a database?
What's an easy backup script for my mysql databases?
How can I load a mysql query into a hash in php?
How to match a mysql datetime field for a specific day
How can I total up the number of entries in a database table by month?
SQL basics
Why use Mysql?
How do I remove / delete a field from a table?
How can I get rid of strange characters in my database output?
Tuning your mysql database.
How can a rearrange or reorder the column order in my table?
How can I fix this error: Can't create/write to file '/tmp/#sql_1111.MYD' (Errcode: 17)?
How do I reset my mysql quota on a 1&1 shared account?
How can I figure out the next auto_increment value for my table?
How can I see how big each mysql table is?
How can I remove a primary key from my database table?
How can I restore part of a table from a mysqldump backup file?
How to use Mysql subqueries instead of a join
How can I re-order the fields in my mysql database?
How can I recreate all the users in my database to a new database?
I'm seeing a "Too many connections" when my mysql scripts run



Powered by KnowledgebasePublisher 1.1
Host Gator
Content provided by Roberts WebForge, Inc.