internet

Mengidentifikasi slow mysql queries

Hari ini aku membaca email dari 000webhost.com tentang adanya slow mysqlqueries. Slow queries tersebut disebabkan penggunaan punbb, yakni pada code:

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:

1. Login to your server as root
2. Open my.cnf with your favorite editor. Example:
   • pico /etc/my.cnf
3. 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.
4. Go ahead and save the configuration.
   • For pico: CTRL+X and YES
5. Now we have to actually create the log file.
   • touch /var/log/mysql-slow.log
6. 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
7. Now we restart mysql
   • service mysql restart
   • It should restart successfully. If it doesn’t check that you didn’t brake my.cnf
8. Wait a few minutes and then examine the slow queries log

Tips ini dipindahkan dari : http://computer-hafiz.blogspot.com/2009/07/identifikasi-slow-mysql-queries.html