MySQL performance slow + queries stuck on “Copying to tmp table”

[slumley]

I have again had a problem regarding the server slowing down to a point that you can not load the web pages and I had to call support to kill the processes.

The support guy's at my ISP have told me that the MySQL is not releasing the data in the TEMP file, and that it is due to some poorly coded PHP file.

Can anyone please assist in solving the problem.


This is happening when we are importing data with CSVI only using two fields "product_sku" and "product_in_stock"  with about 1200 lines.

Before we were able to do over 10,000 lines without any problems.


Can someone be so kind as to share the fix, Thanks



Below is a screenshot of the servers activity.


Joomla! 2.5.19
VirtueMart 2.6.8



+--------+--------------+-------------------------------+----------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+

| Id     | User         | Host                          | db             | Command | Time | State                | Info                                                                                                 |

+--------+--------------+-------------------------------+----------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+

| 320555 | home_1 | dedi..1host-h.net:54938 | home-1_db1 | Query   | 312  | Copying to tmp table | SELECT SUM(CASE WHEN p.product_parent_id=0 THEN 1 ELSE 0 END) AS counter,langt.category_name AS name |

| 320556 | home_1 | dedi..1host-h.net:54939 | home-1_db1 | Query   | 312  | Copying to tmp table | SELECT SUM(CASE WHEN p.product_parent_id=0 THEN 1 ELSE 0 END) AS counter,langt.category_name AS name |

| 320559 | home_1 | dedi..1host-h.net:54941 | home-1_db1 | Query   | 310  | Copying to tmp table | SELECT SUM(CASE WHEN p.product_parent_id=0 THEN 1 ELSE 0 END) AS counter,langt.category_name AS name |

..............................................................



| 320647 | home_1 | dedi..1host-h.net:55046 | home-1_db1 | Query   | 9    | Copying to tmp table | SELECT SUM(CASE WHEN p.product_parent_id=0 THEN 1 ELSE 0 END) AS counter,langt.category_name AS name |

| 320648 | home_1 | dedi..1host-h.net:55048 | home-1_db1 | Query   | 3    | Copying to tmp table | SELECT SUM(CASE WHEN p.product_parent_id=0 THEN 1 ELSE 0 END) AS counter,langt.category_name AS name |

| 320649 | home_1 | dedi..1host-h.net:55049 | home-1_db1 | Query   | 2    | Copying to tmp table | SELECT SUM(CASE WHEN p.product_parent_id=0 THEN 1 ELSE 0 END) AS counter,langt.category_name AS name |

| 320650 | root         | localhost                     |                | Query   | 0    |                      | show processlist                                                                                     |

+--------+--------------+-------------------------------+----------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+

[Milbo]

I would ask CSVI about that

[slumley]

Hi Milbo,

Thanks for your reply, I only used CSVI as and example as that is when I can replicate the problem.

However this is also happening when no one is working on the backend of the site, and purely when users are browsing the website from time to time (about 3 times a week, sometimes more often)

I have called my ISP and they have found that the same problem is happening with many open MySql queries that do not seem to be closing or some seem to be in a loop.

Is there a patch or mod I can try to see if we can stop this problem happening.

[Studio 42]

I had the same type of problem,
This can come from any extention, when they use Exit at the place of jexit(), if you have errors or crash.
This mean the problem can be outside of virtuemart and why not outside of joomla, in a plugin, to many users ...
If you have only the problem 2 or 3 times per week, it's very hard to know if this is from VM or from your  server setting and limits.

[slumley]

This morning the web pages were again taking forever to load and we had not used CSVI for some time since yesterday so i called my ISP and I have managed to get more info from my ISP. See below...

For some reason the items are not closing and using up all the MySql slots .

Please can anyone assist?


|
| 48207 | homecgwxzp_1 | dedi..1host-h.net:35952 | home-1_db1 | Query   | 218  | Copying to tmp table | SELECT SUM(CASE WHEN p.product_parent_id=0 THEN 1 ELSE 0 END) AS counter,langt.category_name AS name, langt.virtuemart_category_id AS id ,cx.category_parent_id ,cx.category_child_id
FROM jos_virtuemart_categories_en_gb AS langt
LEFT JOIN jos_virtuemart_product_categories ON langt.virtuemart_category_id=jos_virtuemart_product_categories.virtuemart_category_id
LEFT JOIN `jos_virtuemart_products` AS p ON jos_virtuemart_product_categories.virtuemart_product_id = p.`virtuemart_product_id`
INNER JOIN jos_virtuemart_category_categories AS cx ON cx.category_child_id=langt.virtuemart_category_id  INNER JOIN jos_virtuemart_categories AS vc ON vc.virtuemart_category_id=langt.virtuemart_category_id
WHERE (p.published=1  OR vc.virtuemart_category_id IN(6,7,9,13,14,15,30,31,32,34,55,60,72,83,91,102,111,112,115,161,226,259,317,356,363,369,372,403,413,430,453,484,513,521,522,528,542,547,566,573,590,634,647,648,694,699,815)) AND vc.published=1
GROUP BY cx.category_child_id
ORDER BY cx.category_parent_id,vc.ordering |
| 48208 | homecgwxzp_1 | dedi..1host-h.net:35953 | home-1_db1 | Query   | 216  | Copying to tmp table | SELECT SUM(CASE WHEN p.product_parent_id=0 THEN 1 ELSE 0 END) AS counter,langt.category_name AS name, langt.virtuemart_category_id AS id ,cx.category_parent_id ,cx.category_child_id
FROM jos_virtuemart_categories_en_gb AS langt
LEFT JOIN jos_virtuemart_product_categories ON langt.virtuemart_category_id=jos_virtuemart_product_categories.virtuemart_category_id
LEFT JOIN `jos_virtuemart_products` AS p ON jos_virtuemart_product_categories.virtuemart_product_id = p.`virtuemart_product_id`
INNER JOIN jos_virtuemart_category_categories AS cx ON cx.category_child_id=langt.virtuemart_category_id  INNER JOIN jos_virtuemart_categories AS vc ON vc.virtuemart_category_id=langt.virtuemart_category_id
WHERE (p.published=1  OR vc.virtuemart_category_id IN(6,7,9,13,14,15,30,31,32,34,55,60,72,83,91,102,111,112,115,161,226,259,317,356,363,369,372,403,413,430,453,484,513,521,522,528,542,547,566,573,590,634,647,648,694,699,815)) AND vc.published=1
GROUP BY cx.category_child_id
ORDER BY cx.category_parent_id,vc.ordering |
| 48210 | homecgwxzp_1 | dedi..1host-h.net:35963 | home-1_db1 | Query   | 214  | Copying to tmp table | SELECT SUM(CASE WHEN p.product_parent_id=0 THEN 1 ELSE 0 END) AS counter,langt.category_name AS name, langt.virtuemart_category_id AS id ,cx.category_parent_id ,cx.category_child_id
FROM jos_virtuemart_categories_en_gb AS langt
LEFT JOIN jos_virtuemart_product_categories ON langt.virtuemart_category_id=jos_virtuemart_product_categories.virtuemart_category_id
LEFT JOIN `jos_virtuemart_products` AS p ON jos_virtuemart_product_categories.virtuemart_product_id = p.`virtuemart_product_id`
INNER JOIN jos_virtuemart_category_categories AS cx ON cx.category_child_id=langt.virtuemart_category_id  INNER JOIN jos_virtuemart_categories AS vc ON vc.virtuemart_category_id=langt.virtuemart_category_id
WHERE (p.published=1  OR vc.virtuemart_category_id IN(6,7,9,13,14,15,30,31,32,34,55,60,72,83,91,102,111,112,115,161,226,259,317,356,363,369,372,403,413,430,453,484,513,521,522,528,542,547,566,573,590,634,647,648,694,699,815)) AND vc.published=1
GROUP BY cx.category_child_id
ORDER BY cx.category_parent_id,vc.ordering |

[GJC Web Design]

Did a quick search in the VM files for an upper case 'CASE' from the query

Can't find a single one -- so as no one else complains of this and the query doesn't look like coming from VM you have to start looking further afield as Studio 45 suggested

3rd party ext would seem to be the cause - VM related as the query is VM related

disable some...

[Studio 42]

Hi,
i'm pretty sure this comes from a 3party module or plugin.
I cannot remember field prefix langt, cx in the mysql queries.
Your queries have to make a full table search > SELECT SUM(CASE WHEN p.product_parent_id=0 THEN 1 ELSE 0 END)
Perhaps changing this query solve the bug.

Code Select Expand


SELECT count(virtuemart_product_id) as counter
....
WHERE product_parent_id = 0

or similar can do the job without having to do a full table check
or perhaps changing the mysql types, can boost the query.
that you know
mediumint can have 0 to 16,777,215 values
smallint 0 to 65,535

virtuemart_product_id   int > mediumint
product_parent_id int > mediumint
product_in_stock int > smallint
low_stock_notification int > smallint

Of course this is considerate as a hack but boost all query for product table.
If you do same improvment in
virtuemart_products_en_gb
virtuemart_product_categories
virtuemart_product_customfields
virtuemart_product_manufacturers
virtuemart_product_medias
virtuemart_product_prices
virtuemart_product_shoppergroups

you see imediatly the speed difference.

Of course, this is only needed when you have query speed problems, with 500 products and 20 categories, this change nothing.
And please do a backup before

[slumley]

Thanks "Studio 42" for the replies and guidance so far,

Please can you please provide more details on what file this is that must be edited?.

[GJC Web Design]

thats the whole point - the query isn't from VM - you have to find where it comes from!

[Studio 42]

Hi,
Your query come from 3party module or plugin.
I cannot help you more without debugging directly your site.

If you need more help then try to contact the author of this plugin or module(it's certainly a categorie or search filter module.)

If not then you have many developper in the forum doing custom work and are happy to work for you.

Patrick

[slumley]

I have had a developer look at the my site and have provided me the following feedback below,

I have set the virtuemart to a one page checkout in that past and perhaps this is what is causing the problem,
Can anyone confirm this and what they feel we should try further....



Hi,

The database as got indexing applied to it. However I feel that this is not sufficient.

After setting up 4home on my local machine, I tailed the MYSQL server load locally on the cart page.

The MySQL bandwidth and query amount usage on this section is tremendous.

The SQLstatement "SELECT SUM(CASE WHEN p.product_parent_id=0 THEN 1 ELSE 0 END) AS counter,langt.category_name AS name" forms part of a larger sql query statement which is made on the fly.

Attached is a graph showing the bandwidth usage and query amount per section of the cart page with one item listed.

​So what I am trying to say is that the way the system is written is not correct and basically the queries need to be rewritten as they are highly inefficient.​



1. modules/mod_cf_filtering/optionsHelper.php : Lines: 347, 508, 831,

2. vt/modules/mod_cf_filtering/optionsHelper.php: Lines: 347, 508, 831
The "SUM(CASE WHEN p.product_parent_id=0 THEN 1 ELSE 0 END) AS counter" forms part of a compiled query, nested in conditional logic, in order to fix this will have to reverse engineer these sections and hack the VM plugin, which will disable the client from updating their plugin in future

[jenkinhill]

mod_cf_filtering is not part of default VirtueMart, but is part of a third party extension, http://breakdesigns.net/extensions/custom-filters

[Studio 42]

Hi,

SUM(CASE WHEN p.product_parent_id=0 THEN 1 ELSE 0 END) AS counter

Of course this give slow mySql query, especially if you use it many times.
This is the problem with some Modules, correctly coded but not optimized. As Said jenkinhill, this is not a VM code(i hope)