This Blog is intended to collect information of my various Intrests,pen my opinion on the information gathered and not intended to educate any one of the information posted,but are most welcome to share there view on them

Thursday, September 15, 2011

Make a query on your Queries!

MySQL is an important service in the Web-Hosting industry. Almost 80% of websites on the Internet are database driven. In a shared hosting environment, the availability of this service is critical.

Many times, I have come across the problem of some particular database query taking more time to execute or using more resources. Eventually the result is high load on the server. This can be due to a sequential query to select a particular value from a large table which is not optimized.

A simple approach can be adopted to find the query and table involved. To do this you can enable slow query logging.

How to enable slow query logging?

The MySQL server’s main configuration file is /etc/my.cnf. Open this file and add the following line below the section “[mysqld]“, then save and quit.

log-slow-queries = /var/log/mysql-slow.loglong_query_time = 5

Create the slow query log file using the following command. Make sure MySQL user can write to this file.

Follow it up with a restart of the MySQL daemon. If you check the slow query log file, you can find all the queries that take more than 5 secs to run. Once you find the query, you can inform the customer(to whom the database belongs to) to optimize his database and query.

The destructive way

Until the customer optimizes his query and database, the situation of high resource usage can prevail. You can setup a script(given below) to find out the query that takes more time to run, say more than 100 seconds and kill that mysql query. 100 seconds is a safe value. Usually no query takes more than 10 seconds to run. The script will also help to keep the load caused from mysql to a minimum. You can also make use of the testmysql.txt file to find out which query is causing the problem.

Redmi 4

About Me

Hi This is Suresh Kumar Pakalapati . I am a person who is positive about every aspect of life. There are many things I like to do, to see, and to experience. I like to feel the music flowing on my face, I like good books and romantic, action,cartoon movies. I like the land and the nature, And, I like to keep smile on my face. I always wanted to be a great and successful person in the world and I know it is needed More and More education, More work, Success Never come in a short Time.....