Jump to content
moldbody

MYSQL problem: How to solve CPU overloaded of Database?

Recommended Posts

Hi everybody:

My site based on Oscommerce 2.2 is restricted by my server provider almost one week. After optimization of the Database and removing some suspicious files, they still told me the MySQL resources were stressed heavily during the short 3 minutes period, and more than half of the resources they allow over time were exhausted. Does it happen on anybody here?

Have to tell the background: This site we use many years, same as this hosting plan provider. I didn't do too much customization on this site cause I don't know programming,  especially for the code about Database. I dont know why it suddenly caused such a problem, we don't have lots of visitors, 300 or 400 hundred visits per day. Recently I bought this company's SSL to keep site safe, then they suggest me to upgrade to a separated server I tried but failed cause of version of PHP.(yes the site  is quite old version, we should and will upgrade it. )

Now, the problem comes, the site is restricted, and I received the mail to ask me optimize, then I did optimization of database, and other things i can do like to check files, compare the codes related between new and old version, the site is still blocked.

 I want to ask if the problem is produced by our data, for example, there are 16300 records in table "products"(data shows 1.2MiB, Index 395Kib). Is it too many?

Now we just stuck at this point don't know what else we can do. Are there anybody can give me a help, I will really appreciate it.

 

website issue overload cpu.jpg

QQ20190604-0.png

Share this post


Link to post
Share on other sites

https://apps.oscommerce.com/rktSo&database-optimizer-v-1-0

You need to talk with your host to pin point why the limit is beeing reached, is it a sudden change or has it just slowly reached the limit. Run virus scan on server, It could be that you have just reached the limit of your shared server capability ( good news your site is geting a lot of visitors!) or has it just been hacked and some one is spamming from your site? Only you and your host can sort this out.


 

Share this post


Link to post
Share on other sites
Posted (edited)

"It is always a good idea to keep your databases' tables optimized.

Fortunately, making this optimization is quite easy when using the phpMyAdmin tool available in cPanel.

To perform the optimization, log in to your phpMyAdmin and select the database whose tables you wish to optimize.

A list with all the database's tables will appear. Tick the tables you wish to optimize, or simply click [Check All] to select all tables.

From the [With selected:] drop-down menu choose Optimize table. This will execute the OPTIMIZE TABLE SQL query on the selected tables and they will be updated and optimized."

Edited by JcMagpie

 

Share this post


Link to post
Share on other sites

THank you @JcMagpie for your help. I've optimized the database at first moment when the provider told me. So this should not be the problem. And do you think one table has over 10000 records is too many?  could it be a problem?  Thank you again.

Share this post


Link to post
Share on other sites

Sounds good, I just think my datas are very small compared with big site, thanks. btw, I download that Database Optimizer1.0 before, i cannot figure out how to use it cause it always says no right permission access, after you send me the link, I reinstall it again, it shows the same . I will continue to sort this out first and look at the files if there are any suspicious codes, really not too much to do for me... And  I've mailed my provider so as to get more information. 

QQ20190604-1.png

Share this post


Link to post
Share on other sites

@moldbodyIt's important to understand that the shop with just one visitor on it won't cause such a problem. The problem is caused when there are many visitors (customers, search engines and hackers). If they all hit the site at one time then the problem is more likely to happen. When there are more connections, the number of MySQL calls increase and that is what your host is seeing. Hosts usually mistakenly see the problem as bad code. It could be partially due to bad code, especially in an older version of oscommerce. My guess is that your database is not using indexes and that will cause a big slowdown. Many hosts will suggest upgrading to a VPS or even a Dedicated server when something like this happens. That is almost never needed but it can solve (hide) the problem because the resource limits of the server are higher.

So I don't think optimizing things will fix it. Doing so will help and may be enough to hide the problem, especially adding indexes, if needed.  But as soon as the site gets busy it will probably happen again. And busy doesn't mean how many customer-type visitors are on the site. There are a lot of connections that are normally hidden from you in normal operation. A bad search bot can be on a site for hours just bouncing around from page to page and you would not see it. You can look at the accounts raw access log to see that but they are difficult to read if you haven't done so. You can also install View Counter. It will let you see what is actually going on and to block IP's that causing a problem. Its country blocking option can cut down the number of connections dramatically, depending upon your situation.

Share this post


Link to post
Share on other sites

From what you post looks like your host is indicating the problem might be on the product_info.php page

Hacker bots like to attack the unprotected forms of the contact and create account pages. They don't really go onto the product pages.

 

Share this post


Link to post
Share on other sites

True. But if you have a form allowing the customization of a product, it is also part of the hackers' targets ...


Get the latest Responsive osCommerce CE (community edition) here .

(Live   : OsC 2.2, php 5.4 & UTF-8  |  Local : OsC 234BS php7.2 Edge for future shop)

Share this post


Link to post
Share on other sites
6 minutes ago, bonbec said:

True. But if you have a form allowing the customization of a product, it is also part of the hackers' targets ...

i should have added unless there are forms on the product pages to try to exploit.

Either way, without a link to the site, or more information, its not possible to narrow it down.

Share this post


Link to post
Share on other sites
33 minutes ago, Hotclutch said:

Hacker bots like to attack the unprotected forms of the contact and create account pages. They don't really go onto the product pages.

That's correct. But data skimmers will spend a lot of time on product pages because that is really what they are after.

Share this post


Link to post
Share on other sites

Have a look at the Who's Online tool and see if there's anything suspicious going on there.  It could be that your site is being tested for vulnerabilities by bots and creating many connections at once (look to see if you notice the same IP over and over again).  The best thing to do first is locate what the most traffic is going to and from there it will help determine what action to take.

If the traffic is suspicious or there isn't one particular page they're targeting then it could be that they're targeting many different pages at once, if that's the case then it could be a process that's being triggered from application_top.php as it's a shared resource.

If it's none of the above then it could be that there's a SQL query that's been poorly designed (might have many joins, no indexes, primary keys etc) and needs optimisation.  


If it still don't work, hit it again!

Senior PHP Dev with 18+ years of commercial experience for hire, all requirements considered, see profile for more information.

Is your version of osC up to date? You'll find the latest osC version (the community-supported responsive version) here.

Share this post


Link to post
Share on other sites
6 hours ago, moldbody said:

removing some suspicious files, they still told me the MySQL resources were stressed heavily during the short 3 minutes period

This is the key to your problem. Your host is the one who will be able to help resolve this.


 

Share this post


Link to post
Share on other sites
3 hours ago, Hotclutch said:

Hacker bots like to attack the unprotected forms of the contact and create account pages. They don't really go onto the product pages.

There's a difference between "hacker bots" (those that are testing for vulnerabilities) and spam/form bots.  From my experience, penetration/vulnerability tests will be performed on all pages, if they notice an odd result from one of those pages they'll switch tactics from a wide attack and concentrate their efforts in a focused attack on the page that showed an odd result from previous.  So that could well be a product page, even more chance if it's been modified/had addons added to it.


If it still don't work, hit it again!

Senior PHP Dev with 18+ years of commercial experience for hire, all requirements considered, see profile for more information.

Is your version of osC up to date? You'll find the latest osC version (the community-supported responsive version) here.

Share this post


Link to post
Share on other sites

Hi thanks everybody, I have to use VPN to access this forum, sometime it doesn't work, lol.

I'm working on new version of oscommerce, at the same time, I'm still waiting for the reply of my host after I find some miners in the code and moved it.

@Hotclutch I use very stupid way to check this product_info.php file, I find I changed a part of code in 2009 sorry about that I don't remember where i copy it and why change it....

//document.write('<?php echo '<a href="javascript:popupWindow(\\\'' . tep_href_link(FILENAME_POPUP_IMAGE, 'pID=' . $product_info['products_id']) . '\\\')">' . tep_image(DIR_WS_IMAGES . $product_info['products_image'], addslashes($product_info['products_name']), SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT, 'hspace="5" vspace="5"') . '<br>' . TEXT_CLICK_TO_ENLARGE . '</a>'; ?>');

I changed to:

document.write('<?php echo '<a href="images/' . $product_info['products_image'] . '" rel="lightbox" title="' . $product_info['products_name'] . '">' . tep_image(DIR_WS_IMAGES . $product_info['products_image'], addslashes($product_info['products_name']), SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT, 'hspace="5" vspace="5"') . '<br>' . TEXT_CLICK_TO_ENLARGE . '</a>'; ?>');

 

@Jack_mcsI try to compare the index between OS2.2 and 2.3, for example: table products, 2.2 has two index,(1st pic) 2.3(2nd pic) has three, I"m not a programmer so i have to change everything carefully.

@bonbec emmm, I'm still looking for this from my site. I didn't do a lot of customization on my site based on 2.2-ms2, so if there is any form allow the customization of a product in the original version of 2.2ms2, it should be there. Now what I finish to check is avoid of permission like 777, there are only 644 and 755 for folders( this part I dare to change).

 

I just think the most possibilité is hacker bots, I find some codes like CoinHive, and this is not the first time i find it. But I don't know I'm still waiting for my host to run another evaluation.

1901835585_jsscriptaculous.jpg.a570242c17a1698b84229048f0a27c36.jpg

Btw, is it very difficult to run test of CPU and MYSQL cause my host takes many days to do one time? Every time I ask them, they said they're still working on it,  last time they rush it in 5 days to finish a test. Can I do it by my side?

 

Thanks for your warm reply, I think it 'd better learn some basic knowledge of PHP.

20190605 0s.png

20190605 0s.png

20190605 2 os.png

Share this post


Link to post
Share on other sites

Have you used the Who's online tool yet?

It will help identify what URLs are being accessed and what they're trying to pass on to those URLs. 


If it still don't work, hit it again!

Senior PHP Dev with 18+ years of commercial experience for hire, all requirements considered, see profile for more information.

Is your version of osC up to date? You'll find the latest osC version (the community-supported responsive version) here.

Share this post


Link to post
Share on other sites
3 hours ago, moldbody said:

I find some codes like CoinHive

So your site has been hacked, You probably still have active script in your code if you are still using high CPU resorces

"Coinhive is a cryptocurrency mining service that relies on a small chunk of computer code designed to be installed on Web sites. The code uses some or all of the computing power of any browser that visits the site in question, enlisting the machine in a bid to mine bits of the Monero cryptocurrency."

This is a well know hack so is nothing new.

https://www.forcepoint.com/blog/security-labs/coinhive-cryptocurrency-mining-script-injected-1000s-government-websites

Your only solution is to do a deep scan of all your code and find the remaning mining scripts. It could be inserted into any file so you will need to do a full scan.


 

Share this post


Link to post
Share on other sites
4 hours ago, moldbody said:

I try to compare the index between OS2.2 and 2.3, for example: table products, 2.2 has two index,(1st pic) 2.3(2nd pic) has three, I"m not a programmer so i have to change everything carefully.

If you can find the RC2 version of oscommerce, it has the MySQL commands to add all of the indexes, as I recall.

Share this post


Link to post
Share on other sites

Is there a reason you are still running osC 2.2? It is horribly obsolete, and vulnerable to many attacks, and doesn't run on PHP 7 (much faster and more secure than PHP 4 or 5), and isn't responsive (mobile-friendly). You said you hadn't done much in the way of customization, so it doesn't sound like it would be much work at all to migrate to the current store version, which is 2.3.4.1BS "Frozen". Note that this is not an official release (osC apparently no longer has official releases), but has to be obtained from GitHub and installed manually. Then your data needs to be migrated over (mostly, bringing your database up to the current standard). It's more tedious than complex work, but might be a bit much for your skill level. What you'll get out of the process is a store that's much more up to date (fewer vulnerabilities), runs on PHP 7.1, and is responsive (works on a phone). Anyway, think about it (try a test install in a side directory). The Frozen link (and patches) are in my signature below.

Not all cycle-sucker visitors are malicious in intent. I've encountered some scrapers that are just poorly written, starting to read and process the next page before finishing the one they're on. ahrefs.com is a particularly egregious example. They bring my site to its knees with rapid-fire page requests, and then offer to sell me the data they've collected! I had to ban them in .htaccess (all except robots.txt, which informs them that they are robota non grata).

Share this post


Link to post
Share on other sites

Thanks for all advice,

@peterbuzzin I'll go to check this.

@Jack_mcs you do think the index has problem, right? 

@MrPhil  Look, I'm working on the upgraded version,  but during the time of wait,  we cannot close the shop just to wait for the upgrading, that is why I want to have some work on the old shop to keep it running for a while. atst, it worked over ten yrs without huge problems( thankful for oscommerce),  this is the first time they restrict us I just think it should have some trigger like virus or sth. else.

Anyway, thank you all, i'm still waiting for the reply from the host after optimization, and problems will support me to learn more to finish the upgrade afap.

Share this post


Link to post
Share on other sites
5 hours ago, moldbody said:

it worked over ten yrs without huge problems( thankful for oscommerce),  this is the first time they restrict us I just think it should have some trigger like virus or sth. else.

@moldbody

You now have a huge problem, your site has been compromised.  The comprise has proven that it's vulnerable somewhere so don't worry about Indexes for now, that's comparable to applying a plaster/bandaid to a massive wound. 

Depending on how that coinhive script was added, they may have full access to your site and customer data (if you're within the EU or sell to the EU you should be reporting this as a potential data breach right now under GDPR as is your obligation). 

Your priority should be securing the site and patching the holes that have allowed hackers to compromise the site in the first place.  They may well be flooding your tables with data or have added additional tables which they're writing to or have uploaded other scripts that are using your resources.

With regards to whether your hosts should have "trigger like virus or..." unfortunately in terms of hosting you do get what you pay for, but even with virus detection that would only detect actual viruses that have been uploaded.  With all hosting services it's the responsibility of the customer to ensure whatever scripts you're using (like osCommerce) are secure and this generally comes from ensuring the stores are kept up-to-date/security patches are applied.

My advice (in this order):

  1. Attempt to secure your site first - change any FTP passwords, Admin logins, database password and make sure they're complex.
    • If you don't do this, any changes you make from this point can easily be reverted back by the hackers if they're using FTP or they'll try different less obvious approaches.
  2. Delete admin/file_manager.php (if you do actually use file_manager.php, learn to use FTP instead).
    • From my memory this came with MS 2.2 and had many vulnerabilities (so bad that it was removed from later versions).
  3. Update your site.
    • Your site has been compromised, you are now a known target, your domain will be shared amongst hackers and they will keep returning.
  4. Consider database schema optimisations
  5. Consider SQL query optimisations

After doing 1, 2 and 3 you may find that you don't even need to worry about 4 & 5.  You'll also be able to use a version of PHP greater than 5.2 which will give you faster processing times.


If it still don't work, hit it again!

Senior PHP Dev with 18+ years of commercial experience for hire, all requirements considered, see profile for more information.

Is your version of osC up to date? You'll find the latest osC version (the community-supported responsive version) here.

Share this post


Link to post
Share on other sites
6 hours ago, moldbody said:

you do think the index has problem, right? 

With regards to optimization to help reduce the load your host mentioned, absolutely, assuming indexes do not already exist. Adding them won't help with security in any way so if you are having security issues, those should be handled first.

Share this post


Link to post
Share on other sites
9 hours ago, moldbody said:

I'm working on the upgraded version,  but during the time of wait,  we cannot close the shop just to wait for the upgrading

You don't need to close your existing shop. You can install, configure, and tune Frozen in parallel with running your existing shop. You might have to briefly close your shop in order to update the new shop to the latest customers, sales, inventory, etc., when cutting over to the new shop, but carefully planned and done right, that shouldn't be more than a few hours. It's not like your store will be down for days or weeks while you move over to the new one!

Share this post


Link to post
Share on other sites

Still big thanks for everybody. I'll do what you advice right now, changing pwd, delete files first, upgrade site. Thanks a lot!

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×