The biggest traffic source for the web site is search engines traffic. There is a lot what can be written on search engine optimization (SEO). But I just realized that often web masters or web site owners missing quite important application from the Google itself
That’s a valuable data on what Google “knows” about your web site, definetely worth checking and should be a starting point of the search engines optimization.
If you are doing web development, then you know that you need to have all recent versions of the IE browser to troubleshoot your sites. Here is one way to have them all, thanks to Edskes Software collection – just download pack and install all versions you want.
We are using FogBugz application for the bugs and support requests tracking. And it’s configured to receive support@companyname.com emails (means tons of spam). Once the database size hit approximately 20,000 requests, we started really wonder: why so slow? It looks like there was another table BugEvent which size is around 200,000 records at that time.
It could be some other application, could be your own code. Doesn’t matter – just it happens, one database size grows, performance of the application is degrading.
First step to troubleshoot – enable slow queries log in the MySQL config file (/etc/my.cnf is popular location). Open file in editor and add lines
log_slow_queries = /var/log/mysql-slow.log
long_query_time = 10(path for the log file is totally up to you, I use MySQL 5.0*, so in other versions syntax can be different)
Those lines tell mysql to log all queries lasted 10 seconds or more. Restart MySQL now and wait.
After a while run mysqldumpslow as root. If there are any slow queries logged, you will see summary, here is mine:
Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 36 Time=549.81s (19793s) Lock=0.00s (0s) Rows=0.0 (0)
SELECT Bug.ixBug AS ix, Bug.ixBugEventLatest AS ixChild,
Area.nTypeAs nAreaType
FROM (Bug INNER JOIN Area ON Bug.ixArea = Area.ixArea)
WHERE ixBugEventLatest <= N
AND ixBugEventLatest >= N AND Bug.ixBug IN
(SELECT ix FROM IndexDelta WHERE sType = 'S'
AND fDeleted = N)
AND -N = -N
ORDER BY ixBugEventLatest DESC
LIMIT NQuery listed above appears to take around 10 minutes to execute. Now check the tables type in the MySQL
mysql> show table status;
In our scenario I found that all tables are MyISAM. That means, that while query is executed (10 minutes) tables used in it will be locked for writing. So, if someone tries to update bug, they have to wait up to 10 minutes.
Check what MySQL engines you have:
mysql> show engines;
+---------+----------+---------------------------
| Engine | Support | Comment
+---------+----------+---------------------------
| MyISAM | DEFAULT | Default engine as of MySQL
| MEMORY | YES | Hash based, stored in memory
| InnoDB | YES | Supports transactions, row-
...If InnoDB is supported, then all what has to be done to speed up application is switching to InnoDB in this situation. This will not make query above run any faster, but, that will not lock the whole table for the time query is running. There are some benefits of MyISAM table type (well, it’s faster!), so you may don’t want to convert all tables to InnoDB at this point. But large tables with concurrent updates and reads will benefit.
If InnoDB is not supported, it can be turned on in MySQL configuration file, just comment out skip-innodb line.
In case of FogBugz I found the following tables to be worst offenders:
Bug, BugEvent, BugRelation, SorterToken, TokenAssociation.
To convert those, use:
mysql> alter table Bug engine=InnoDB;
After conversion performance of the application increased immediately, as there are no more locks on popular tables.
There are additional benefits of InnoDB engine, such as foreign keys. Read more on InnoDB: