Identifikasi Slow mySQL Queries

Hari ini aku membaca email dari 000webhost.com tentang adanya slow mysqlqueries. Slow queries tersebut disebabkan penggunaan punbb, yakni pada code:Tulisan ini telah dipindahkan ke Tips Ghobro  : http://www.ghobro.com/programming/mysql/mengidentifikasi-slow-mysql-queries.html

insert into punbb_search_matches (post_id, word_id, subject_match) select 4384, id, 0 from punbb_sea ....

Bagaimana hosting bisa tahu adanya query-query bertipe slow queries tersebut dengan username yang banyak dan aneka ragam query?

Rupanya ada cara-cara tertentu seperti yang diungkapkan cpanelconfig.com sebagai berikut:

\Login to your server as root

1. Open my.cnf with your favorite editor. Example: 
   • pico /etc/my.cnf
2. Into the [mysqld] section add the fallowing lines 
   • log-slow-queries = /var/log/mysql-slow.log
   • long_query_time = 3
   • This is just an example. You can use any file name that you want and you can modify the long_query_time to any value. In this example I will be logging to /var/log/mysql-slow.log any queries that are taking longer then 3 seconds.
3. Go ahead and save the configuration. 
   • For pico: CTRL+X and YES
   
   
4. Now we have to actually create the log file. 
   • touch /var/log/mysql-slow.log
5. Now we are changing the owner of the file so that mysql and actually write to it. 
   • chown mysql.root /var/log/mysql-slow.log
6. Now we restart mysql 
   • service mysql restart
   • It should restart successfully. If it doesn't check that you didnot brake my.cnf
   
   
7. Wait a few minutes and then examine the slow queries log