Several people I know have been "kicked out" without notice from shared hosting. Not a very nice feeling.
But then you move to a dedicated server, and suddenly you feel like kicking yourself out, since your server starts complaining and stops serving the users...
Time to go over your queries, add indexes and make other necessary changes to lower (or spread) the load.
But WHERE DO WE START? There are a billion different query types in our application. How do we know which one is the current bottle-neck?
MySQL logs to the rescue.
There are two main MySQL logs you should use:
1. mysqld.log - a log of ALL the queries
2. mysqld-slow.log - a log of queries that took more then x seconds to run.
These logs tend to fill up really quickly..
Your hava-amina (first thought) might be to look in the slow-queries log. But in it you will find many queries that ran slowly because the server was already loaded. a problem...
To the RESCUE: MyProfi. A MySQL log analyser.
MyProfi is a command line tool that parses mysql query log and outputs statistics of most frequently used queries, sorting them by number of times they appear in a log file. The similar queries are groupped together. Thus, select id from user where login="admin" and select id from user where login='root' become select id from user where login={} - the variable parts of queries are replaced with {} markers, this make statistics analysis as easy as revewing the top of most frequently occured queries' patterns.
You can get MyProfi at: http://myprofi.sourceforge.net/
Example of its output:
Queries by type:
================
select 346 887 [84.11%]
set 29 643 [ 7.19%]
insert 23 748 [ 5.76%]
update 11 916 [ 2.89%]
execute 155 [ 0.04%]
Queries by pattern:
===================
1. 118 471 [28.73%] - select*from visitedurl where siteid={}and pagetypeid={}
2. 27 713 [ 6.72%] - set names utf8
3. 20 476 [ 4.97%] - select mimage,timage from images where id={}limit{}
4. 20 475 [ 4.96%] - select*from users where id={}
See the 28.73%? This can tell you exactly where to look and what to change. Notice that these are not actual queries, but QUERY-TYPES instead. the {} in id={} replaces various different ids that were entered.
Also notice the SET NAMES UTF8 query. This can be set in my.ini and never again be needed. (Lowering 6.72% of your query load)
