MySQL service struggling with Modx

670 Posts

Andy Jones Reply #1, 7 years, 10 months ago

Hi there.
I hope someone can shed some light on this.

I have a fairly high end Cloud server which runs several modx sites.
Every so often, the server grinds to a bit of a halt, and when I raise a ticket with the support team they say:
The MySQL service in the server is having a very increased CPU resource consumption, and this accounts for the high load values.

He then refers to several samples queries which they say are causing problems:

| 321208 | database_name | localhost | database_name | Query | 3495 | statistics | SELECT COUNT(DISTINCT `modResource`.`id`) FROM `modx_site_content` AS `modResource` WHERE ( ( modResource.parent IN (137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,169,171,172,173,174,176,177,179,182,189,190,191,192,178,194,195,199,201,187,210,211) AND `modResource`.`deleted` = '0' AND `modResource`.`published` = '1' AND `modResource`.`isfolder` = '0' ) AND ( (EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '%marketing or (1' AND tv.name = 'blogTags' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) OR EXISTS (SELECT 1 FROM `modx_site_tmplvars` tv WHERE tv.name = 'blogTags' AND tv.default_text LIKE '%marketing or (1' AND tv.id NOT IN (SELECT tmplvarid FROM `modx_site_tmplvar_contentvalues` WHERE contentid = modResource.id)) ) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '2)=(select*from(select name_const(CHAR(113' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '118' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '120' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '118' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '74' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '115' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '102' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '99' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '122)' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '1)' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE 'name_const(CHAR(113' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '118' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '120' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '118' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '74' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '115' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '102' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '99' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '122)' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '1))a) -- and 1=1%' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) ) ) | 0.000 |

and

| 321216 | database_name | localhost | database_name | Query | 3493 | statistics | SELECT COUNT(DISTINCT `modResource`.`id`) FROM `modx_site_content` AS `modResource` WHERE ( ( modResource.parent IN (137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,169,171,172,173,174,176,177,179,182,189,190,191,192,178,194,195,199,201,187,210,211) AND `modResource`.`deleted` = '0' AND `modResource`.`published` = '1' AND `modResource`.`isfolder` = '0' ) AND ( (EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '%marketing or (1' AND tv.name = 'blogTags' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) OR EXISTS (SELECT 1 FROM `modx_site_tmplvars` tv WHERE tv.name = 'blogTags' AND tv.default_text LIKE '%marketing or (1' AND tv.id NOT IN (SELECT tmplvarid FROM `modx_site_tmplvar_contentvalues` WHERE contentid = modResource.id)) ) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '2)=(select*from(select name_const(CHAR(119' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '67' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '98' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '105' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '109' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '121' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '108' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '80' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '119' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '88' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '114)' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '1)' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE 'name_const(CHAR(119' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '67' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '98' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '105' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '109' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '121' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '108' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '80' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '119' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '88' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '114)' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) AND EXISTS (SELECT 1 FROM `modx_site_tmplvar_contentvalues` tvr JOIN `modx_site_tmplvars` tv ON tvr.value LIKE '1))a) -- and 1=1%' AND tv.id = tvr.tmplvarid WHERE tvr.contentid = modResource.id) ) ) | 0.000 |

Then he goes on to say:
Please note that all these "select" queries are very resource intensive and can slow down your operations to a great extent. I would suggest you to check the database table structures and these "select queries" to make sure that the indexes you have in your DB tables correspond to the select operations which you're trying to run. When select/order_by/group_by/join operations are performed on tables with incorrect indexes, MySQL will be forced to create a new temporary table, copy data into it and perform these sorting operations on a temporary table. Such operations might take significant amount of CPU and Disk I/O bandwidth and can dramatically reduce performance of your sites!

I'm just not sure where to go with this?
Does anyone have any advice?
It's happened on two occasions on two separate sites/databases now.

Thanks in advance

Andy

463 Posts

chrisandy Reply #2, 7 years, 10 months ago

Andy - I could easily be very wrong here but I had similar behaviour on a site recently. Lightning fast for a while and then 10 seconds to load anything.

My solution was to change the database connection from localhost:3306 to ##.##.##.##:3306 - where ##.##.##.## is the ip address.

I'm probably wrong though!

Web site design in Nottingham UK by Chris Fickling http://www.chrisficklingdesign.co.uk

670 Posts

Andy Jones Reply #3, 7 years, 10 months ago

Quote from: chrisandy at Nov 13, 2018, 08:18 AM

Andy - I could easily be very wrong here but I had similar behaviour on a site recently. Lightning fast for a while and then 10 seconds to load anything.

My solution was to change the database connection from localhost:3306 to ##.##.##.##:3306 - where ##.##.##.## is the ip address.

I'm probably wrong though!

Hmm. I guess it's worth a try. Difficult to check/measure because it seems fairly sporadic.

24,544 Posts

BobRay Reply #4, 7 years, 10 months ago

It kind of looks like a getResources call with many TV filters. That would put quite a load on the server, especially if it's hit by multiple users at the same time. The database request also appears to be joining the same table multiple times in the same query, which I don't think should ever happen.

If you're storing a lot of data in TVs, you can make things *way* more efficient with a custom DB table or two. TVs are mainly meant to store page-specific data that will only be shown when that particular page is viewed. They are very inefficient when used to search or sort pages you are collecting.

I should also mention that if there are only a few 'field's of data, you can sometimes use unused resource fields (e.g., introtext, longtitle, etc.) rather then TVs, which will also be much easier on the server. [ed. note: BobRay last edited this post 7 years, 10 months ago.]

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

670 Posts

Andy Jones Reply #5, 7 years, 10 months ago

Quote from: BobRay at Nov 13, 2018, 12:23 PM

It kind of looks like a getResources call with many TV filters. That would put quite a load on the server, especially if it's hit by multiple users at the same time. The database request also appears to be joining the same table multiple times in the same query, which I don't think should ever happen.

If you're storing a lot of data in TVs, you can make things *way* more efficient with a custom DB table or two. TVs are mainly meant to store page-specific data that will only be shown when that particular page is viewed. They are very inefficient when used to search or sort pages you are collecting.

I should also mention that if there are only a few 'field's of data, you can sometimes use unused resource fields (e.g., introtext, longtitle, etc.) rather then TVs, which will also be much easier on the server.

Thanks Bob - I will have a look into this - I don't really know any other way of doing things, so I do rely quite a lot of TVs and output filters to get what i need. I will see if I can find any tutorials on custom DB fields.

Thanks
Andy

24,544 Posts

BobRay Reply #6, 7 years, 10 months ago

FYI:

https://bobsguides.com/blog.html/2014/06/02/why-extend-modresource/

https://bobsguides.com/classextender-class.html

ClassExtender comes with the getExtResources snippet, which is like getResources but allows you to use your custom table fields to search and sort. It's somewhat limited, so if you want to do something more complex, you may have to duplicate it and modify the PHP code. You would also have to move the data in your TVs into the custom fields ClassExtender uses.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

670 Posts

Andy Jones Reply #7, 7 years, 10 months ago

Quote from: BobRay at Nov 14, 2018, 03:18 PM

FYI:

https://bobsguides.com/blog.html/2014/06/02/why-extend-modresource/

https://bobsguides.com/classextender-class.html

ClassExtender comes with the getExtResources snippet, which is like getResources but allows you to use your custom table fields to search and sort. It's somewhat limited, so if you want to do something more complex, you may have to duplicate it and modify the PHP code. You would also have to move the data in your TVs into the custom fields ClassExtender uses.

Thank you - gives me some bed time reading for tonight!
Looking forward to extending my knowledge on this.