High CPU Usage

[aztecmedia]

We are getting high CPU usage on the D/B backend for a large Prestashop website.

 

We've had this issue before and it brought down the site. It's similar to last time, same queries as last time (I’ve attached one of those queries).

 

We've exhausted our investigations at this end, it is the mysql daemon using all the CPU and it’s only those queries constantly running.

 

I’ve truncated some of the connection stats tables to reduce the size of the D/B but this has had no effect, just a shot in the dark because those queries running don’t appear to be related to those tables but does not harm to reduce the D/B size.

 

It's an old version (1.4.2.5), but unable to upgrade due to modifications and client budgets.

 

Any help greatly appreciated!

query.txt

[musicmaster]

This is the indexation. Indexation takes always a lot of resources.

 

You can switch indexation to manual - meaning that you need to run it yourself when you have changed something - at Preferences->Search

[aztecmedia]

Thank you for your help, that does make sense. We're running an old version of Prestashop, it's version 1.4.2.5. I can't see how to turn off indexation in there? I've attached a screenshot if that helps.

 

We've set up a cron job that runs once a week to add the missing products to the search.

Edited October 20, 2016 by aztecmedia (see edit history)

[musicmaster]

As far as I can see there is no switch for this in 1.4. The indexing is done by the function indexation() in the file classes\search.php. 

 

However, I am a bit puzzled by your latest picture. Almost 4000 products not indexed. It looks like something is going wrong there. Any idea? 

[aztecmedia]

That's what we thought, the newer versions have the switch to enable or disable automatic indexation.

 

We recently added a bulk load of products to the website via CSV import. There was about 8,000 in total. I ran the indexation through the Preferences panel at first for the first few, but we setup the cron job for the others.

 

It was meant to run on Sunday, but doesn't seem to have done if there are still products missing from the search. We're currently running it now.

 

If all the products are successfully indexed, should we still be seeing these SQL queries or are they only for this purpose? Is there anything in Prestashop that fires them other than this indexation? Perhaps when someone searches?

[musicmaster]

Product names, products descriptions, tags, manufacturers and quite a few other fields are indexed. So whenever you change for example the name of a product indexation will be called when you press save. But it should only re-index that one product and as such not take that much time.

[aztecmedia]

Thank you.

 

Do you know how long a re-index or adding new indexes should take via Prestashop admin?

 

We're not sure if we should leave this running overnight if still running etc or whether it should only be taking a matter of minutes.

 

There's about 3,500 products that are currently missing from the index.

[aztecmedia]

All the indexing has completed but we are still having queries continuously running.

 

We have alleviated the issue a little by increasing the number of connections but this isn’t really a solution because more and more queries are just raised, it hits the connection  limit and then people cannot access the website.

 

I’m confident this isn’t a server issue so there is little more I can do, if these queries are indeed indexing then something has gone wrong because in the Prestashop admin all products are now showing as indexed.

 

We need to find out what is triggering this constant indexing if possible. Does anyone have any ideas?

[musicmaster]

You might have a look at the product id's that are re-indexed. Maybe you find some pattern.

[aztecmedia]

I've had a look through the MySQL queries as attached above again and all of them seem to reference the search function.

 

There's always a reference to a search keyword such as: "AND (sw.word LIKE 'roof%')"

 

I've disabled the search from the website and the problem seems to have stopped. We get the odd similar call in MySQL, but think it's people using a cached version of the website.

 

Obviously having the search function disabled isn't ideal so I want to fix this issue. I've tried turning it back on, but just disabling the AJAX search to see if this might be it. However doing this seems to kill the search function altogether.

 

Any ideas why?
 

[musicmaster]

I've had a look through the MySQL queries as attached above again and all of them seem to reference the search function.

 

There's always a reference to a search keyword such as: "AND (sw.word LIKE 'roof%')"

 

I've disabled the search from the website and the problem seems to have stopped. We get the odd similar call in MySQL, but think it's people using a cached version of the website.

 

Obviously having the search function disabled isn't ideal so I want to fix this issue. I've tried turning it back on, but just disabling the AJAX search to see if this might be it. However doing this seems to kill the search function altogether.

 

Any ideas why?

 

 "AND (sw.word LIKE 'roof%')" sounds like a customer doing a search.

 

Might it be that some hacker is spamming your site with search commands? Maybe you can have a look at your access log.

[Dh42]

That can be true with that version. You can also control the output through querystrings so you can get it to dump all the products on one page. That could be what is happening as well.