MySQL queries taking a long time
Permalink
My organization has a legacy Concrete5 website (5.6.1.2) which couldn't be updated easily due to years of spaghetti code and custom packages. The website is at radiok.org. According to a colleague, a contributor had been on the site when it went down. We don't have much information on what they were doing, but I have no idea what they could have done that would have messed things up this bad.
Basically, the site was timing out all the time. I did some investigation and MySQL was close to 800% CPU. If I restarted apache2 and mysql services, the site returns, but as more people visit, MySQL rises in CPU. Using the MySQL slow query logging, I found that it's some pretty generic (though complicated) Concrete5 queries for loading pages that is taking over a minute to load. Below is a sample query. There are many other queries with a little bit different info (retrieving different amounts of items, retrieving different types of pages, sorting, etc.).
I discovered that by placing the site in maintenance mode, the mysql process no longer climbs into the ridiculous level of CPU %. I assume that this is because no one is visiting the public-facing site which triggers the slow queries like above for retrieving pages.
We're not really sure what to do. I tried rolling back some of the package updates that had been made recently, and that had no effect at all. I double checked the db.xml in one of our most recently updated packages and everything seems valid. I even dropped the added tables (even though they were empty) to see if I could figure that out. I also tried backing up the database through the dashboard but the process hangs and doesn't succeed. Also originally contacted our server host (Linode) and they correctly diagnosed that it was an internal issue that there was nothing they could do about it.
Has anyone encountered something like this? Is there a way to fix this that doesn't require a giant wipe of our site? Professional referral would also be helpful. We are a student club with high turnaround (due to graduation) and do not have a lot of experience with C5 or managing servers.
Below is environment info.
Basically, the site was timing out all the time. I did some investigation and MySQL was close to 800% CPU. If I restarted apache2 and mysql services, the site returns, but as more people visit, MySQL rises in CPU. Using the MySQL slow query logging, I found that it's some pretty generic (though complicated) Concrete5 queries for loading pages that is taking over a minute to load. Below is a sample query. There are many other queries with a little bit different info (retrieving different amounts of items, retrieving different types of pages, sorting, etc.).
Count: 3 Time=751.72s (2255s) Lock=0.00s (0s) Rows=10.0 (30), radiok[radiok]@localhost select p1.cID, pt.ctHandle from Pages p1 left join Pages p2 on (p1.cPointerID = p2.cID) left join PagePaths on (PagePaths.cID = p1.cID and PagePaths.ppIsCanonical = 1) left join PageSearchIndex psi on (psi.cID = if(p2.cID is null, p1.cID, p2.cID)) inner join CollectionVersions cv on (cv.cID = if(p2.cID is null, p1.cID, p2.cID) and cvID = (select cvID from CollectionVersions where cvIsApproved = 1 and cID = cv.cID)) left join PageTypes pt on pt.ctID = cv.ctID inner join Collections c on (c.cID = if(p2.cID is null, p1.cID, p2.cID)) left join CollectionSearchIndexAttributes on (CollectionSearchIndexAttributes.cID = if (p2.cID is null, p1.cID, p2.cID)) where 1=1 and cvName != '' and pt.ctID = '20' and (ak_exclude_page_list = 0 or ak_exclude_page_list is null) and PagePaths.cPath like '/features/%' and PagePaths.ppIsCanonical = '1' and cvIsApproved = '1' and (p1.cIsTemplate = 0 or p2.cIsTemplate = 0) and ((select count(cID) from PagePermissionAssignments ppa1 inner join PermissionAccessList pa1 on ppa1.paID = pa1.paID where ppa1.cID = if(p2.cID is null, p1.cInheritPermissionsFromCID, p2.cInheritPermissionsFromCID) and pa1.accessType = 10 and pa1.pdID in (0) and pa1.peID in (3) and (if(pa1.peID = 10 and p1.uID <>0, false, true)) and (ppa1.pkID = 1 and cv.cvIsApproved = 1 or ppa1.pkID = 2)) > 0 or (p1.cPointerExternalLink !='' AND p1.cPointerExternalLink IS NOT NULL)) and ((select count(cID) from PagePermissionAssignments ppaExclude inner join PermissionAccessList paExclude on ppaExclude.paID = paExclude.paID where ppaExclude.cID = if(p2.cID is null, p1.cInheritPermissionsFromCID, p2.cInheritPermissionsFromCID) and accessType = -1 and pdID in (0) and paExclude.peID in (3) and (if(paExclude.peID = 10 and p1.uID <>0, false, true)) and (ppaExclude.pkID = 1 and cv.cvIsApproved = 1 or ppaExclude.pkID = 2)) = 0) and p1.cIsActive = '1' order by cvDatePublic desc limit 630,10
I discovered that by placing the site in maintenance mode, the mysql process no longer climbs into the ridiculous level of CPU %. I assume that this is because no one is visiting the public-facing site which triggers the slow queries like above for retrieving pages.
We're not really sure what to do. I tried rolling back some of the package updates that had been made recently, and that had no effect at all. I double checked the db.xml in one of our most recently updated packages and everything seems valid. I even dropped the added tables (even though they were empty) to see if I could figure that out. I also tried backing up the database through the dashboard but the process hangs and doesn't succeed. Also originally contacted our server host (Linode) and they correctly diagnosed that it was an internal issue that there was nothing they could do about it.
Has anyone encountered something like this? Is there a way to fix this that doesn't require a giant wipe of our site? Professional referral would also be helpful. We are a student club with high turnaround (due to graduation) and do not have a lot of experience with C5 or managing servers.
Below is environment info.
# concrete5 Version 5.6.1.2 # concrete5 Packages Basic Forum (1.0), Clean Temp Sessions (0.5.8), Clevyr Tweets (1.0), Dae Marquee (2.0.4), Discussion (1.8.8), Galleria image gallery (2.0), Gallery (1.8.1), Google Docs Viewer (1.0), HTML5 Audio Player Basic (1.1.6), Html5 Music (1.0.3), K New Carousel (.1), Music (1.0.4), Open Graph Tags Lite (1.5), Page List Teasers (1.2), Pro Blog (12.4.4), Pro Events (11.5.0), Radio K (2.15.27), Simple SoundCloud Player (0.7), Social Share Lite (1.3.1), Ticker (1.0.1), Ticker Messages (1.0), Tweetcrete (1.6.8), Twitter Search Block by David K Uspal (2.0), Vimeo Player (1.0.6). # concrete5 Overrides blocks/image, blocks/hide_asmiller_gallery, blocks/autonav, blocks/content, blocks/page_list, blocks/slideshow, blocks/next_previous, controllers/featuresearch.php, controllers/donatetest.php, jobs/clean_temp_sessions.php, js/site.jquery.ui.js, css/images, css/site.jquery.ui.css, libraries/ad_library, single_pages/register.php, single_pages/donatetest.php, themes/radiok # Server Software Apache/2.2.22 (Ubuntu) # Server API apache2handler # PHP Version 5.3.10-1ubuntu3.26 # PHP Extensions apache2handler, bcmath, bz2, calendar, Core, ctype, date, dba, dom, ereg, exif, fileinfo, filter, ftp, gd, gettext, hash, iconv, json, libxml, mbstring, mcrypt, mhash, mysql, mysqli, openssl, pcre, PDO, pdo_mysql, Phar, posix, Reflection, session, shmop, SimpleXML, soap, sockets, SPL, standard, sysvmsg, sysvsem, sysvshm, tokenizer, wddx, XCache, xml, xmlreader, xmlwriter, zip, zlib. # PHP Settings
Viewing 15 lines of 41 lines. View entire code block.
Thanks for your reply! We had tried clearing the cache and tmp files before.
Our database is 241MB. I think PageStatistics is off because there are only 22 rows and they're from 2013.
Our database is 241MB. I think PageStatistics is off because there are only 22 rows and they're from 2013.
Once you have an export (ie backup copy), you could run Extreme Clean https://www.concrete5.org/marketplace/addons/extreme-clean... to slim it down.
We're unfortunately on a C5 version (5.6.1.2) outside of Extreme Clean's range.
This definitely led us in the correct direction. We're a radio station and whoever set up the site initially had every song saved in the database with no purging. We had 541k records for every song played since 2011. I purged that to just the past year (in reality it should be shorter but this is OK for now) and now everything is working! Gonna set up a task to automatically purge anything older than maybe a week or 3 days.
Excellent news and well done for sticking with it!
If you export it using phpmyadmin, what size is it?
It could be so bloated that it is struggling to cope.
Check the size of the PageStatistics table and if necessary truncate it using this sql
You can also delete everything in the files/cache folder and the files/tmp folder.