Reduce High CPU usage overload problem caused by MySql

February 16, 2009

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.

  1. 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()).
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. Optimize database query:Optimizing your queries can help them run more efficiently, which can save a significant amount of time.
  7. 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.
  8. Create indexes (Single or combine) based on requirement: Indexes are used for reading the data from the table with comparatively faster execution time.
  9. Distribute Cron scheduler load: Never handle entire function from one cron scheduler. Try to distribute in multiple scheduler.
  10. 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.

Post a Comment

Your email address will not be published. Required fields are marked *

*


  • Recent Posts

    • Secure Your CodeIgniter Application using CSRF Token

      27.10.2014
      by admin
      In this tutorial, we will learn how to make our website more secure & protect from CSRF when we are using CodeIgniter web application framework. We thanks CodeIgniter team who giving in built support Cross Site Rquest Forgery (CSRF or XSRF). To enable CSRF protection CodeIgniter Framework will automatically protect forms or AJAX calls from CSRF when […]
    • Multiple Themes in CodeIgniter like WordPress

      07.08.2014
      by admin
      Create multiple themes and select one of them from backend like wordpress is drawbacks of a framework. Similar CodeIgniter have not an option to select one theme from multiple theme concept but we can re arrange using CodeIgniter template concept. We are going to handle multiple themes in a very simple way, in this example […]
    • How to create REST API in PHP?

      13.07.2014
      by admin
      REST (Representational State Transfer) is the standard design architecture for developing web services API. It is simple easy to understand and developing client-server relationship API. REST takes advantage of the HTTP request methods to layer itself into the existing HTTP architecture. We can handle GET, PUT, POST, DELETE operations through it. A REST API allows […]
    • Basic security vulnerabilities in php code

      16.10.2013
      by admin
      Today I have informed PHP programmers of common security mistakes that can be overlooked in PHP scripts. In the beginning programmers fail to understand about the PHP security issues or how to make secure script. The wise programmer knows that the real question is how secure a site is. Here I have focus how to […]
    • Generating CAPTCHA Image Using PHP

      29.12.2009
      by admin
      The CAPTCHA is a very very useful test to prevent abuse on the websites. When you create a web form like registration, login, contact us, blog comment etc…, We are suffering day by day with unwanted email or web spam abuse. So if you use CAPTCHA on your website forms, this can help in stopping […]
Copyright © 2012-2013 Sujit Shah. .
Tags: PHP Programmer Nepal, Web Developer Nepal, Website Designer, CodeIgniter Developer, Yii Framework, Wordpres, OpenCart, Drupal, Front End Developer, Responsive Developer, Bootstrap, Foundation, Freelance PHP MySql Programmer Nepal, Expert Programmer Nepal, PHP, MySql, LAMP, Linux, Apache, SVN, JavaScript, Ajax, jQuery, HTML, HTML5, CSS, CSS3, XML, SOAP, PSD to XHTML, Responsive Web Design, Web Development, CMS, E-commerce, Classified, Job portal, Travel & tours, Penny Auction, Lowest Unique Bid Auction, Reverse Auction, Price Reveal Auction, Payment Gateway Integration, Nepal, India, Hong Kong, Australia, UK, USA, Singapore, Germany, Canada, Netherlands, New Zealand, Norway, Italy