Httpd - 100% CPU utilization - Mysql server remote location

Question

Availability of databases on a local and other database on a remote server. Basics - cakePHP Mysql: 5.1.71 - "Remote Location" PHP Version 5.5.14

Now, using the local mysql database, the processor load is normal for the httpd request when checking with the TOP command.

However, when I use the remote server database. CPU utilization will be up to 70% or sometime 100% for visiting the same page.

Below are some basic settings for php.ini and httpd.conf files.

php.ini

memory_limit = 128M max_input_time = 60 post_max_size = 8M 

httpd.conf

 <IfModule prefork.c> StartServers 8 MinSpareServers 5 MaxSpareServers 20 ServerLimit 256 MaxClients 256 MaxRequestsPerChild 4000 </IfModule> <IfModule worker.c> StartServers 4 MaxClients 300 MinSpareThreads 25 MaxSpareThreads 75 ThreadsPerChild 25 MaxRequestsPerChild 0 </IfModule> KeepAlive Off MaxKeepAliveRequests 100 KeepAliveTimeout 15 

What could be the reason for the high processor load for httpd in this case.

Any help would be appreciated.

Thanks Sudir

Answer 1

I used to have two mysql servers that were bound in this way. I would have several scenarios that would do a few things. One could tell me what the action of the connection (concurrrent connection of the session on the server) looked like and register it. The next thing that will check the use of services, and if the threshold reaches 70%, I would start killing connections and send me some letters to find out what the deal was.

It turned out that during your normal use, when you expect to see users browsing your site, arranging my server).

Some of the things that I have done to reduce this are other than killing sessions (which is not cool). Is the normalization of my database, refactoring tables that have seen heavy use. Then I would restore and redo it.

I also realized that some things go beyond setting. We create redundancy.

I had to mirror my MySQL server and then route users based on query parameters.

In the end, I realized that I needed better load balancing, and in the end I got 4 MySQL servers and load balancing and traffic control.

So, your approach is at the query level and sees what you can do there, and then refactoring the data and the redexes.

Then create scripts with simultaneous connection to the connection and target users who are inactive and redirect them somewhere, or simply fixed them and gave way to the heavyweight on your site.

Since then, I have done a lot of things, but it all started that very day.

I hope this is helpful, or at least makes me think of an approach