How to find Slow MySQL queries and Optimize your Tables
Posted on Feb 12, 2008 by
Paul White
Here is the situation, you have a
ASP.NET site running on a
shared account with your
hosting provider, and it starts to get slow. You only do a few Gigs of transfer, and your traffic is modest. If your
hosting company isn't packing a few thousand
websites on the same server yours is running on then it must be your code. You read all over the web that you need to optimize your
mySQL database, but you don't know what that means. After some research you find that
MySQL has a slow query log that documents where your database and web applications could use some help. But the only way to see this is if you have root access to the machine. If you are on a
shared hosting account its doubtful they will give you root access to the DB server. On top of that most hosts don't have the slow query log turned on because it puts a performance hit on the server. So I came up with my own little way of finding these troublesome queries. The result was my site runs much faster. If you have a site that is running like a dog I highly recommend this.
Introduction of my setup
I run most of my SQL calls through helper funcitons. These are little functions that take in a query in the form of a string and then return the information as a single string, single integer, or a DataTable. I also have a helper function for transactional stuff like inserts and deletes. We will start with my helper function called. GetDataTable().
/////////////////////////////////////////////////////////////////////
// Get Data Table from SQL
////////////////////////////////////////////////////////////////////
public DataTable GetDataTable(string sql) {
DataTable rt = new DataTable();
DataSet ds = new DataSet();
MySqlDataAdapter da = new MySqlDataAdapter();
MySqlConnection con = new MySqlConnection(GetConnection());
con.Open();
MySqlCommand cmd = new MySqlCommand(sql, con);
da.SelectCommand = cmd;
da.Fill(ds);
try {
rt = ds.Tables[0];
}
catch {
rt = null;
}
con.Close();
return rt;
}
The most probably situation is you have a query that is lagging.
Usually a big query. Things like Inserts, Deletes, and updates usually
aren't the source of a slow down. Its normally that query you wrote
that has a few sub selects within it. Single Select Statements don't
get too bad even if you don't have an index. But if you are running
Selects with Sub selects this can have a huge impact if your indexes
are not setup. So to start we use GetDataTable() to pull our queries.
We just need to mod this function to record its performance.
Before we Mod it lets setup a new
MySQL table to store the logs
Table Name: mysql_logs
id BigInt Auto Inc // Unique ID
type VARCHAR(45) // name of function calling
mycode VARCHAR(4000) // MySQL statement called
page VARCHAR(500) // Name of page calling
date_created DATETIME // when it was called
delay FLOAT // Time took to run query
What we want to do is log all queries performance. So we create a new function for MySQL loggin
/////////////////////////////////////////////////////////////////////
// Log MySQL
////////////////////////////////////////////////////////////////////
public void log_mysql(string type, string mycode, string delay, string pagename){
////////////////////////////////////////
// Easy way to enable or disable logging
///////////////////////////////////////
bool active=false;
if(active){
insertToDB("INSERT INTO mysql_logs(type, mycode, date_created, delay, page) VALUES('"+type+"','"+fixquotes(mycode)+"',NOW(),'"+delay+"','"+fixquotes(pagename)+"')");
}
}
Then we modify our GetDataTable() function to log its performance
/////////////////////////////////////////////////////////////////////
// Get Data Table from SQL
////////////////////////////////////////////////////////////////////
public DataTable GetDataTable(string sql) {
//////////////////////////////////
// get start time
//////////////////////////////////
System.DateTime start = System.DateTime.Now;
DataTable rt = new DataTable();
DataSet ds = new DataSet();
MySqlDataAdapter da = new MySqlDataAdapter();
MySqlConnection con = new MySqlConnection(GetConnection());
con.Open();
MySqlCommand cmd = new MySqlCommand(sql, con);
da.SelectCommand = cmd;
da.Fill(ds);
try {
rt = ds.Tables[0];
}
catch {
rt = null;
}
con.Close();
////////////////////////////////////////////
// get time finished
////////////////////////////////////////////
System.DateTime finish = System.DateTime.Now;
///////////////////////////////////////////
// calculate how long it took
///////////////////////////////////////////
System.TimeSpan delay = finish-start;
///////////////////////////////////////////
// get full URL of page
///////////////////////////////////////////
string pagename = Request.ServerVariables["URL"]+"?"+Request.ServerVariables["QUERY_STRING"];
//////////////////////////////////////////
// Log results
//////////////////////////////////////////
log_mysql("GetDataTable",sql,Convert.ToString(delay.Seconds)+"."+Convert.ToString(delay.Milliseconds),pagename);
return rt;
}
How does it work?
What we do is capture the datetime before and after we make our query. Then we calculate the TimeSpan between these two DateTime objects( in Milliseconds ). Along with capture the pagename and querystring. Then we pass this to our logging function, where that data gets saved. After you get this code implimented let the server run for an hour or so. Make sure if you impliment this for your Inserts and Deletes that you don't use these same helper functions within your logging function. Else you will get caught in an endless loop of logging your logging function. After you have run your logging for an hour or so, disable it. Now you should have several thousand logs of your Database's performance.
Next we use
MySQL query Analizer to look at our logs, sorting them by delay Decending.
If you have anything over 15ms you might want to look at the query.
To see a detailed explaination of what your query is doing. use the Explain command.
Example
EXPLAIN SELECT * FROM photos WHERE gallery='3' AND date_created>'2008-1-1 12:00:00'
This will show you if your query is optimized or not.
How do I optimize my query?
Easy just create an index for all the WHERES. Even though you might already have a primary index. Its good to create
an index for each WHERE situation. So make one index for when you just are looking for your gallery, another for just the date_created, and then maybe one with both. Even though having multiple indexs can give you a small hit on writes it will dramatically increase your reads.
I will add some screen shots later, making this a more complete tutorial.
Discussion
No Comments have been submitted
WhiteSites Blog