Where does this query come from?

[behemoth]

The following query is running intermittently, often multiple times concurrently, even when the site is in offline mode.

Code Select Expand


SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name               
FROM `xxxxx_virtuemart_products` as p LEFT JOIN `xxxxx_virtuemart_products_en_gb` as l
ON l.`virtuemart_product_id` = p.`virtuemart_product_id` LEFT JOIN `xxxxx_virtuemart_product_shoppergroups` as ps
ON p.`virtuemart_product_id` = `ps`.`virtuemart_product_id` LEFT JOIN `xxxxx_virtuemart_product_categories` as pc
ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id`
WHERE ( ( `ps`.`virtuemart_shoppergroup_id`= "1"  OR `ps`.`virtuemart_shoppergroup_id` IS NULL  ) 
AND  p.`published`="1" ) group by p.`virtuemart_product_id` ORDER BY `pc`.`ordering` ASC, `product_name` ASC LIMIT 0, 46

Because we have a lot of products, it takes a while to run and from time to time impacts the CPU usage of the server.

I know that the limit (46) is controlled by "Frontend default items per list view" within "Set the pagination sequence for the List Box" in the config.

I did wonder if it was related to the reported 404 issue, but I tried disabling 404 handling and this is still occurring.

[Studio 42]

SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name  is not from Virtuemart core modules or component.
Have you really set 46 as pagination limit ?
All query i know in Vm use
SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`
or
SELECT SQL_CALC_FOUND_ROWS ,p.*,l.* ....
Try to disable some third party modules and use Joomla debug to verify the queries.

[behemoth]

Hi Studio 42, thanks for your reply.

Why is 46 a bad pagination limit? It was originally 48 (so I could lay out eight rows of six products), but I amended it to determine which of the config options was controlling it. I will be setting it back once this is sorted unless there's a good reason not to.

How do you know what queries are used? I'm trying to work my way through the code but I'm no expert.

The problem I'm having isn't specifically related to the site itself; sometimes this query fires multiple times and it eventually brings down the server. I'm currently trying to determine what's triggering it.

[behemoth]

Ok, I've searched through files of all the addons that are installed on the site and none of them use "SELECT SQL_CALC_FOUND_ROWS" at all.

I did find this:
administrator\components\com_virtuemart\helpers\vmmodel.php:                    $q = 'SELECT SQL_CALC_FOUND_ROWS '.$select.$joinedTables;

It's within a function called exeSortSearchListQuery(), but I can't do anything that will trigger this query - I've tried browsing categories, searching, playing with the pagination, but I've not seen this query fire, yet there are times when we notice the CPU loads rising or the emails go down or whatever and this query is present in the processlist.

Any advice gratefully received.

[Studio 42]

Do you use last release or Vm2 ?
Have you try to update Virtuemart ?
I never use more then 24 or 36 because in some VM release the product Object can be very big. Especially if you display(or load) custom fields in Category view or have to much child products ...
The Query you send is not slow. You can check the slow mySql queries on activate debug in the Joomla configuration (on bottom of the template you have a "debug" zone with all speed and memory statistics.
Note that if you have already a slowdown problem, set pagination to 24 for eg the time you debug. Or you get a blank page because time limit or memory limit.

[behemoth]

It's not the latest VM, no. It's such an unpredictable symptom that I need to determine what's causing this so I can tell if upgrading has actualy fixed it.

The query itself takes a long time to run because we have a lot of products in the database (about 1.6 million). It's not particulary impacting site usage, it's just the MySQL CPU load. I'm trying to run something else and this query keeps spawning and hogging all the resources. Yesterday evening I had three instances of the query running at 17:45 which took 424, 417 and 395 seconds to complete!

I've tried reducing the pagination in the hope that the query takes less time to run, but I still don't know what's making it run at all...

[Studio 42]

Have you do some test directly in mysql using PHP Myadmin for eg ?
Perhaps some table optimisation ?
Fine adjust the mysql settings ....
If you have 1.6 million products, you cannot simply use a shop solution and hope it work, your server cache, mysql settings need certainly some changes.
Perhaps you need more memory, use SSD ... the query itself is not bad

[behemoth]

Hi,

I have added some additional indexing on the tables, and while not perfect the site is functioning reasonably. I just want to be able to replicate the situation where this query fires, especially when it fires multiple times. Is it a particular search? Viewing a particular category? Whenever I've taken steps to act in a way that I imagine should initiate the query, it doesn't appear in 'show processlist', but sometimes it fires and I don't know why. Is this other visitors? Is there something internally kicking it off? It's more noticeable when I'm performing my own import routine to get products into the database, but there isn't a direct correlation.

[Studio 42]

When you enable Joomla debug, you see the queries in the debug zone.
You have a pagination 46 as if this was a category view query, but no category is set. So perhaps the main shop(or category view root) if you display the products in this view

[Milbo]

Did you enable the product_name for the search? looks for me like the search query.

[behemoth]

product_name is enabled by default.

why would it need to run dozens of times a second?

I'm right in the middle of another spike in server load. I've tried disabling that. I'll report back later.

[Studio 42]

Or try to add an index for  : ordering + product_name, if you have not to long product name, the  ORDER BY `pc`.`ordering` ASC, `product_name` is certainly the slowdown because it use a full table scan
Using a combinated ordering + product_name permit to mysql to use an index only for this case.
Of course if you ave many other ordering, it can be slower to add index for each , so it depend really your own needs.

[behemoth]

This is what's happening in MySQL at the time:

Code Select Expand


mysql> show processlist;
+--------+----------------+-----------+-------------------------+---------+------+---------------------------+------------------------------------------------------------------------------------------------------+
| Id     | User           | Host      | db                      | Command | Time | State                     | Info                                                                                                 |
+--------+----------------+-----------+-------------------------+---------+------+---------------------------+------------------------------------------------------------------------------------------------------+
| 530446 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |    0 | init                      | show processlist                                                                                     |
| 548483 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  616 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 548489 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  616 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 548956 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  616 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549036 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  615 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549037 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  617 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549038 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  617 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549224 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  615 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549267 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  614 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549364 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  614 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549365 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  614 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549368 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  614 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549401 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  691 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549402 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  691 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549403 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  691 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549404 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  691 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549405 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  691 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549406 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  691 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549407 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  691 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549408 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  691 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549409 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  691 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549410 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  691 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549411 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  691 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549412 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  691 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 549413 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  691 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |

.
.
.
lines removed to fit within forum limit
.
.
.


| 554304 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  108 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 554350 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  103 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 554353 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  103 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 554356 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |  103 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 554421 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |   96 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 554422 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |   95 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 554426 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |   95 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 554778 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |   58 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 554937 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |   39 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 554938 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |   39 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 554941 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |   39 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 555000 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |   33 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 555005 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |   34 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 555010 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |   34 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 555217 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Query   |   12 | Sending data              | SELECT SQL_CALC_FOUND_ROWS  p.`virtuemart_product_id`, l.product_name
                FROM `xxxxx_virtuemart_prod |
| 555346 | azeeda_L505b60 | localhost | azeeda_AZ_live_20181120 | Sleep   |    0 |                           | NULL                                                                                                 |
+--------+----------------+-----------+-------------------------+---------+------+---------------------------+------------------------------------------------------------------------------------------------------+
144 rows in set (0.00 sec)
I've also attached a screen grab of the graph showing the server load.

[behemoth]

From research and investigation of this issue it looks like these queries are locking the tables and causing a bottleneck. Is it practical or even desirable to change tables from MyISAM to innodb and exploit the row-locking nature of that engine?

We really need to get to the bottom of this and I don't really know where to turn. Is there anybody who can help with this sort of forensic investigation? We can pay.

[behemoth]

Ok, I think I know what's going on.

The Joomla .htaccess is rewriting requests for images that come from the Amazon aranhabot (we upload loads of products to Amazon Marketplace too). These requests appear to be triggering this query but with a categoryid of -2, presumably because there's no actual visitor to the site and no category selected. At peak, this bot can visit many times per second, and the query takes time because we have so many products.

Not sure if this is a bug or whatever but I've had to hack the core (if someone could address this issue in a future version I'd be very grateful) in administrator/components/com_virtuemart/models/product.php

around line 789
was:

Code Select Expand


$product_ids = $this->exeSortSearchListQuery (2, $select, $joinedTables, $whereString, $groupBy, $orderBy, $filterOrderDir, $nbrReturnProducts);

now:

Code Select Expand


if($virtuemart_category_id!=-2) {
$product_ids = $this->exeSortSearchListQuery (2, $select, $joinedTables, $whereString, $groupBy, $orderBy, $filterOrderDir, $nbrReturnProducts);
}
else
{
$product_ids=array(1);
}


I'm sure this isn't the best approadh; I'm certainly no expert coder but it does appear to work.