In the recent years, many of us were facing the problem of "database overload" and "High CPU usage overload" problem. After a benchmark testing about this issue, I reach at point where I found, there were too many database connections, unnecessary queries execution and unnecessary HTTP request. So, Today I am really interested to share my little knowledge with you.
I feel this problem when I have made penny auction. If you are made swoopo/madbid clone or similar type of penny/Live auction then I'm sure you guys used Ajax or jQuery function which you made to call in a second because there must be display recent updated data like winner name, his bids and auction countdown timer without any page refresh. If you are in product details page then there must be display recent 10 bids history without any refresh.
In the similar type of web development, what I have realized that there were too many database connections, unnecessary query execution and unnecessary HTTP request. These problems were creating the database overload problem and then CPU usage goes to 100%. Why?
Because there might be call PHP page in each second through JavaScript to update recent bidding information. Now suppose if there are 1000 users in your website, 1000 database connection was created and if you have made 5 queries for getting updated bidding information, then 1000*5= 5000 queries are execute in a second which is big problem for any server and due to these causes High CPU usage overload problem, database overload problem and too many database connection problem occurs. These things happen when users open one page of your website if they are open 2 or 3 or more then you imagine how many connections will open and queries will execute in a second.
Now your question is how I have resolved these problems?
Well! First of all we have to completely remove database connection by using file handling and accordingly this we need to maintain some functions.
Second, I have taken current updated bidder information and bid history from database when user place a bid. So, there are only one connection happen not multiple. Due to the bidbutler or autobid causes also I have face overload problem so I have remove this function from frontend and handle it from cron scheduler.
Now the main query is how to reduce High CPU usage overload problem cause by MySql?
Here are some of the following points which will help you to reduce High CUP usage overload or Database overload problem.
- Establishes a persistent connection: Persistent connection (mysql_pconnect) gives two major benefits than MySql connection. First, when connecting, the function would first try to find a (persistent) link that's already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection. Second, the connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use (mysql_close() will not close links established by mysql_pconnect()).
- Make a database connection and closed connection: Make database connection at the top of the page and closed connection in the bottom of the page.
- Change database type from MyISAM to Innodb: we also need to change MyISAM to Innodb because InnoDB supports some newer features: Transactions, row-level locking, foreign keys. InnoDB is for high volume, high performance.
- Create Temporary Tables: The best place to use temporary tables is when we need to pull a bunch of data from multiple tables. In the penny auction, you need when you display bid history where we required to display only last 10 bidder history and latest updated winner information like auction price, winner name and auction end date.
- Select only those elements from database which are required: Don't select all the values from all table, only select that element which we required.
- Optimize database query:Optimizing your queries can help them run more efficiently, which can save a significant amount of time.
- MySQL Query Cache: MySQL Query Cache is simply speed up your query performance. As we know, speed is always the most important element in developing a website especially for those high traffic database driven website. You can try to turn on query cache to speed up query. Whenever query cache is enable, it will cache the query in memory and boost query performance.
- Create indexes (Single or combine) based on requirement: Indexes are used for reading the data from the table with comparatively faster execution time.
- Distribute Cron scheduler load: Never handle entire function from one cron scheduler. Try to distribute in multiple scheduler.
- Check your server configuration: Due to the low server configuration or distributed server also you have face high CPU usage problem or database overloaded problem. (check mysqltuner.pl query_cache_size and tmp_table_size haven't been set)
REFERENCES
http://www.sks.com.np/article/4/reduce-high-cpu-usage-overload-problem-caused-by-mysql.html
http://www.howtoforge.com/tuning-mysql-performance-with-mysqltuner
http://cruncht.com/89/drupal-lamp-server-tuning/
