Question Shop Query

[Home Alone]

Can you tell me what this query is doing? All of a sudden it's running non-stop and causing the forum to slow down.

SELECT xf_dbtech_shop_item., xf_user_User_1., xf_permission_cache_content_Permissions_2.*

 

[Home Alone]

Here's more information regarding this query from our slow query log:

# User@Host: newlsa[newlsa] @ [192.168.0.73]
# Thread_id: 159278854 Schema: newlsa QC_hit: No
# Query_time: 5.635619 Lock_time: 0.000034 Rows_sent: 1185 Rows_examined: 5923
# Rows_affected: 0 Bytes_sent: 14608779
SET timestamp=1619476484;
SELECT xf_dbtech_shop_item., xf_user_User_1., xf_permission_cache_content_Permissions_2., xf_dbtech_shop_item_watch_Watch_3., xf_reaction_content_Reactions_4., xf_dbtech_shop_category_Category_5., xf_permission_cache_content_Permissions_6., xf_dbtech_shop_category_watch_Watch_7.
FROM xf_dbtech_shop_item
LEFT JOIN xf_user AS xf_user_User_1 ON (xf_user_User_1.user_id = xf_dbtech_shop_item.user_id)
LEFT JOIN xf_permission_cache_content AS xf_permission_cache_content_Permissions_2 ON (xf_permission_cache_content_Permissions_2.content_type = 'dbtech_shop_item' AND xf_permission_cache_content_Permissions_2.content_id = xf_dbtech_shop_item.item_id AND xf_permission_cache_content_Permissions_2.permission_combination_id = '2')
LEFT JOIN xf_dbtech_shop_item_watch AS xf_dbtech_shop_item_watch_Watch_3 ON (xf_dbtech_shop_item_watch_Watch_3.item_id = xf_dbtech_shop_item.item_id AND xf_dbtech_shop_item_watch_Watch_3.user_id = '526821')
LEFT JOIN xf_reaction_content AS xf_reaction_content_Reactions_4 ON (xf_reaction_content_Reactions_4.content_type = 'dbtech_shop_item' AND xf_reaction_content_Reactions_4.content_id = xf_dbtech_shop_item.item_id AND xf_reaction_content_Reactions_4.reaction_user_id = '526821')
LEFT JOIN xf_dbtech_shop_category AS xf_dbtech_shop_category_Category_5 ON (xf_dbtech_shop_category_Category_5.category_id = xf_dbtech_shop_item.category_id)
LEFT JOIN xf_permission_cache_content AS xf_permission_cache_content_Permissions_6 ON (xf_permission_cache_content_Permissions_6.content_type = 'dbtech_shop_category' AND xf_permission_cache_content_Permissions_6.content_id = xf_dbtech_shop_category_Category_5.category_id AND xf_permission_cache_content_Permissions_6.permission_combination_id = '2')
LEFT JOIN xf_dbtech_shop_category_watch AS xf_dbtech_shop_category_watch_Watch_7 ON (xf_dbtech_shop_category_watch_Watch_7.category_id = xf_dbtech_shop_category_Category_5.category_id AND xf_dbtech_shop_category_watch_Watch_7.user_id = '526821');

 

[Fillip H.]

Can you please re-post the query inside [CODE] BBCode tags? The formatting makes it impossible to read.

 

[Home Alone]

SELECT `xf_dbtech_shop_item`.*, `xf_user_User_1`.*, `xf_permission_cache_content_Permissions_2`.*, `xf_dbtech_shop_item_watch_Watch_3`.*, `xf_reaction_content_Reactions_4`.*, `xf_dbtech_shop_category_Category_5`.*, `xf_permission_cache_content_Permissions_6`.*, `xf_dbtech_shop_category_watch_Watch_7`.*
                        FROM `xf_dbtech_shop_item`
                        LEFT JOIN `xf_user` AS `xf_user_User_1` ON (`xf_user_User_1`.`user_id` = `xf_dbtech_shop_item`.`user_id`)
LEFT JOIN `xf_permission_cache_content` AS `xf_permission_cache_content_Permissions_2` ON (`xf_permission_cache_content_Permissions_2`.`content_type` = 'dbtech_shop_item' AND `xf_permission_cache_content_Permissions_2`.`content_id` = `xf_dbtech_shop_item`.`item_id` AND `xf_permission_cache_content_Permissions_2`.`permission_combination_id` = '156081')
LEFT JOIN `xf_dbtech_shop_item_watch` AS `xf_dbtech_shop_item_watch_Watch_3` ON (`xf_dbtech_shop_item_watch_Watch_3`.`item_id` = `xf_dbtech_shop_item`.`item_id` AND `xf_dbtech_shop_item_watch_Watch_3`.`user_id` = '130500')
LEFT JOIN `xf_reaction_content` AS `xf_reaction_content_Reactions_4` ON (`xf_reaction_content_Reactions_4`.`content_type` = 'dbtech_shop_item' AND `xf_reaction_content_Reactions_4`.`content_id` = `xf_dbtech_shop_item`.`item_id` AND `xf_reaction_content_Reactions_4`.`reaction_user_id` = '130500')
LEFT JOIN `xf_dbtech_shop_category` AS `xf_dbtech_shop_category_Category_5` ON (`xf_dbtech_shop_category_Category_5`.`category_id` = `xf_dbtech_shop_item`.`category_id`)
LEFT JOIN `xf_permission_cache_content` AS `xf_permission_cache_content_Permissions_6` ON (`xf_permission_cache_content_Permissions_6`.`content_type` = 'dbtech_shop_category' AND `xf_permission_cache_content_Permissions_6`.`content_id` = `xf_dbtech_shop_category_Category_5`.`category_id` AND `xf_permission_cache_content_Permissions_6`.`permission_combination_id` = '156081')
LEFT JOIN `xf_dbtech_shop_category_watch` AS `xf_dbtech_shop_category_watch_Watch_7` ON (`xf_dbtech_shop_category_watch_Watch_7`.`category_id` = `xf_dbtech_shop_category_Category_5`.`category_id` AND `xf_dbtech_shop_category_watch_Watch_7`.`user_id` = '130500');

 

[Fillip H.]

Thank you.

This would appear to be a problem with your database server. When a query is slow due to a badly designed query, this part: Rows_examined: 5923 will be very large.
For instance, let's say you had 1 million rows in the xf_dbtech_credits_transaction table. If a query was badly designed and indexes not used properly, it would say Rows_examined: 1000000 or something to that effect.

The fact that there are only 5923 rows that were examined indicates that your database server is poorly tuned, as it should not take five seconds to examine 5923 rows.

To answer the question from the OP: It appears to return the item list.

 

[Home Alone]

To be clear, this is just a query that returns the list of items in the shop? Why is it looking at reaction content?

 

[Home Alone]

And this is 1,000 to 2,000 people at once looking at the shop. When there aren't a lot of people on the site there are no issues. During peak hours it becomes a problem.

 

[Fillip H.]

To be clear, this is just a query that returns the list of items in the shop? Why is it looking at reaction content?

Because you can react to items when you're not the item owner:

The Reactions table is properly indexed and is set up to be scalable, so it's unlikely to be the issue.

I don't know what else to say other than that you might need to throw more hardware at the problem, and/or hire someone like eva2000 (George Liu) to optimise your MySQL server.

 

[Home Alone]

I disabled the shop on Sunday and the server ran very fast and without any issues. We had nearly 8000 people on the site without any issues at all. It's only when the shop is enabled that the site slows down.

 

[Home Alone]

If i disable the ability to react to items would that part of the query go away?

 

[Fillip H.]

If i disable the ability to react to items would that part of the query go away?

No, and as I said, it's unlikely to be the cause. If it was the cause, it would also not work with posts.

As I said in my previous post, taking 5 seconds to examine that few rows indicates the bottleneck is your server, not this add-on. If this was a widespread problem with this add-on, then many people running shared hosts or low-power VPSes would have reported similar problems.

If someone like eva2000 investigates and comes back with concrete changes to the schema or to the query itself, I would be happy to implement them, but as it stands there's nothing I can do. "It works fine when I disable Shop" unfortunately does not help resolve the problem.

 

[Home Alone]

I blocked access to the shop and this query is still running. Are you sure all it does is list the items in the shop?

 

[Home Alone]

No, and as I said, it's unlikely to be the cause. If it was the cause, it would also not work with posts.

As I said in my previous post, taking 5 seconds to examine that few rows indicates the bottleneck is your server, not this add-on. If this was a widespread problem with this add-on, then many people running shared hosts or low-power VPSes would have reported similar problems.

If someone like eva2000 investigates and comes back with concrete changes to the schema or to the query itself, I would be happy to implement them, but as it stands there's nothing I can do. "It works fine when I disable Shop" unfortunately does not help resolve the problem.

The site is hosted on a cluster managed by Giganet. They looked at the db server and it is optimized. SSD Drives in a RAID 10 configuration with 512GB of RAM. They looked at the my.cnf and it was optimized.

The slow query log shows that the query I posted runs anywhere from 1 to 6 seconds. It runs continuously and shows it's being run for users who aren't even looking at the shop. It even ran when the forum was inactive. it would really help us if you can tell us what exactly this query does.

I see where another user here posted that the more items he added to his shop the slower the site became. I think that's what's happening here. Our shop has over 500 items. Does the query I posted look at the permissions for each item whenever a user looks at an item?

 

[Home Alone]

We reduced the number of items in the shop from 1200 to 200 and the problems went away. The more items we added to the shop the slower the site became. Having this query run continously for every item in the shop and for every member viewing the shop caused the DB server to run out of available connections -- and that was after setting the max connections to 2000. 2 - 3 thousand members x 1200 items running that query -- the DB server couldn't keep up. A server on SSD drives and with 512GB of RAM that was optimized.