Slow SELECT queries causing CPU usage spikes. Topic is solved

[DJBenz]

in /home/mgfred1/public_html/components/com_ampz/controller.php on line 261

Joomla! Instance :: Joomla! 3.8.12-Stable (Amani) 28-August-2018
Joomla! Platform :: Joomla Platform 13.1.0-Stable (Curiosity) 24-Apr-2013
Joomla! Configured :: Yes | Read-Only (444) | Owner: --protected-- . (uid: 1/gid: 1) | Group: --protected-- (gid: 1) | Valid For: 3.8
Configuration Options :: Offline: 0 | SEF: 1 | SEF Suffix: 1 | SEF ReWrite: 1 | .htaccess/web.config: Yes | GZip: 1 | Cache: 1 | CacheTime: 30 | CacheHandler: file | CachePlatformPrefix: 0 | FTP Layer: 0 | Proxy: 0 | LiveSite: | Session lifetime: 15 | Session handler: database | Shared sessions: 0 | SSL: 2 | Error Reporting: default | Site Debug: 0 | Language Debug: 0 | Default Access: 1 | Unicode Slugs: 0 | dbConnection Type: mysqli | PHP Supports J! 3.8.12: Yes | Database Credentials Present: Yes |

Host Configuration :: OS: Linux | OS Version: 3.10.0-714.10.2.lve1.5.9.el7.x86_64 | Technology: x86_64 | Web Server: LiteSpeed | Encoding: gzip | Doc Root: --protected-- | System TMP Writable: Yes | Free Disk Space : 3933.68 GiB |

PHP Configuration :: Version: 7.1.20 | PHP API: litespeed | Session Path Writable: Yes | Display Errors: 0 | Error Reporting: 32759 | Log Errors To: error_log | Last Known Error: 30th August 2018 06:28:37. | Register Globals: | Magic Quotes: | Safe Mode: | Open Base: | Uploads: 1 | Max. Upload Size: 8M | Max. POST Size: 8M | Max. Input Time: -1 | Max. Execution Time: 120 | Memory Limit: 128M

Database Configuration :: Version: 5.5.5-10.2.15-MariaDB-cll-lve (Client:mysqlnd 5.0.12-dev - 20150407 - $Id: 38fea24f2847fa7519001be390c98ae0acafe387 $) | Host: --protected-- (--protected--) | Localhost: Yes | Collation: utf8_general_ci (Character Set: utf8) | Database Size: 716.78 MiB | #of Tables:  151

PHP Extensions :: Core (7.1.20) | date (7.1.20) | libxml (7.1.20) | openssl (7.1.20) | pcre (7.1.20) | zlib (7.1.20) | filter (7.1.20) | hash (1.0) | Reflection (7.1.20) | SPL (7.1.20) | session (7.1.20) | standard (7.1.20) | litespeed () | bcmath (7.1.20) | ctype (7.1.20) | curl (7.1.20) | dom (20031129) | fileinfo (1.0.5) | ftp (7.1.20) | gd (7.1.20) | gmp (7.1.20) | iconv (7.1.20) | imap (7.1.20) | json (1.5.0) | mbstring (7.1.20) | mcrypt (7.1.20) | mysqlnd (mysqlnd 5.0.12-dev - 20150407 - $Id: 38fea24f2847fa7519001be390c98ae0acafe387 $) | PDO (7.1.20) | Phar (2.0.2) | posix (7.1.20) | SimpleXML (7.1.20) | snmp (0.1) | soap (7.1.20) | sqlite3 (7.1.20) | tokenizer (7.1.20) | xml (7.1.20) | xmlwriter (7.1.20) | xsl (7.1.20) | zip (1.13.5) | mysqli (7.1.20) | pdo_mysql (7.1.20) | pdo_sqlite (7.1.20) | wddx (7.1.20) | xmlreader (7.1.20) | xmlrpc (7.1.20) | timezonedb (2018.5) | ionCube Loader () | Zend OPcache (7.1.20) | Zend Engine (3.1.0) |
Potential Missing Extensions ::
Disabled Functions :: symlink | shell_exec | exec | proc_close | proc_open | popen | system | dl | passthru |

Switch User Environment (Experimental) :: PHP CGI: No | Server SU: No | PHP SU: No | Custom SU (LiteSpeed/Cloud/Grid): Yes
Potential Ownership Issues: No

Core Folders :: images/ (755) | components/ (755) | modules/ (755) | plugins/ (755) | language/ (755) | templates/ (755) | cache/ (755) | logs/ (755) | tmp/ (755) | administrator/components/ (755) | administrator/modules/ (755) | administrator/language/ (755) | administrator/templates/ (755) | administrator/logs/ (755) |

Elevated Permissions (First 10) ::

Database statistics :: Uptime: 90150 | Threads: 19 | Questions: 34472151 | Slow queries: 0 | Opens: 384222 | Flush tables: 1 | Open tables: 2000 | Queries per second avg: 382.386 |

Components :: SITE ::
Core :: com_wrapper (3.0.0) 1 | com_mailto (3.0.0) 1 |
3rd Party:: WF_FILESYSTEM_JOOMLA_TITLE (2.6.32) 1 | WF_LINKS_JOOMLALINKS_TITLE (2.6.32) 1 | WF_POPUPS_JCEMEDIABOX_TITLE (2.6.32) 1 | WF_POPUPS_WINDOW_TITLE (2.6.32) 1 | WF_AGGREGATOR_VINE_TITLE (2.6.32) 1 | WF_AGGREGATOR_[youtube]_TITLE (2.6.32) 1 | WF_AGGREGATOR_VIMEO_TITLE (2.6.32) 1 | WF_AGGREGATOR_DAILYMOTION_TITL (2.6.32) 1 | WF_LINK_SEARCH_TITLE (2.6.32) 1 | WF_KITCHENSINK_TITLE (2.6.32) 1 | WF_CLEANUP_TITLE (2.6.32) 1 | WF_LISTS_TITLE (2.6.32) 1 | WF_PREVIEW_TITLE (2.6.32) 1 | WF_CAPTION_TITLE (2.6.32) 1 | WF_STYLE_TITLE (2.6.32) 1 | WF_MICRODATA_TITLE (2.6.32) 1 | WF_NONBREAKING_TITLE (2.6.32) 1 | WF_TEMPLATEMANAGER_TITLE (2.6.32) 1 | WF_VISUALCHARS_TITLE (2.6.32) 1 | WF_FONTCOLOR_TITLE (2.6.32) 1 | WF_FONTSIZESELECT_TITLE (2.6.32) 1 | WF_TABLE_TITLE (2.6.32) 1 | WF_STYLESELECT_TITLE (2.6.32) 1 | WF_SEARCHREPLACE_TITLE (2.6.32) 1 | WF_AUTOSAVE_TITLE (2.6.32) 1 | WF_ANCHOR_TITLE (2.6.32) 1 | WF_IMGMANAGER_TITLE (2.6.32) 1 | WF_XHTMLXTRAS_TITLE (2.6.32) 1 | WF_TEXTCASE_TITLE (2.6.32) 1 | WF_IFRAME_TITLE (2.6.32) 1 | WF_LAYER_TITLE (2.6.32) 1 | WF_VISUALBLOCKS_TITLE (2.6.32) 1 | WF_PRINT_TITLE (2.6.32) 1 | WF_BROWSER_TITLE (2.6.32) 1 | WF_SPELLCHECKER_TITLE (2.6.32) 1 | WF_IMGMANAGER_EXT_TITLE (2.6.32) 1 | WF_LINK_TITLE (2.6.32) 1 | WF_FONTSELECT_TITLE (2.6.32) 1 | WF_FULLSCREEN_TITLE (2.6.32) 1 | WF_CONTEXTMENU_TITLE (2.6.32) 1 | WF_ARTICLE_TITLE (2.6.32) 1 | WF_TEXTPATTERN_TITLE (2.6.32) 1 | WF_FORMATSELECT_TITLE (2.6.32) 1 | WF_SOURCE_TITLE (2.6.32) 1 | WF_DIRECTIONALITY_TITLE (2.6.32) 1 | WF_MEDIAMANAGER_TITLE (2.6.32) 1 | WF_CLIPBOARD_TITLE (2.6.32) 1 | WF_FILEMANAGER_TITLE (2.6.32) 1 | WF_MEDIA_TITLE (2.6.32) 1 | WF_EMOTIONS_TITLE (2.6.32) 1 | WF_CHARMAP_TITLE (2.6.32) 1 | WF_INLINEPOPUPS_TITLE (2.6.32) 1 | WF_HR_TITLE (2.6.32) 1 |

Components :: ADMIN ::
Core :: com_installer (3.0.0) 1 | com_templates (3.0.0) 1 | com_redirect (3.0.0) 1 | com_menus (3.0.0) 1 | com_languages (3.0.0) 1 | com_finder (3.0.0) 1 | com_joomlaupdate (3.6.2) 1 | com_newsfeeds (3.0.0) 1 | com_ajax (3.2.0) 1 | com_weblinks (3.6.0) 1 | com_categories (3.0.0) 1 | com_banners (3.0.0) 1 | com_postinstall (3.2.0) 1 | com_search (3.0.0) 1 | com_associations (3.7.0) 1 | com_contenthistory (3.2.0) 1 | com_modules (3.0.0) 1 | com_tags (3.1.0) 1 | com_cpanel (3.0.0) 1 | com_fields (3.7.0) 1 | com_messages (3.0.0) 1 | com_config (3.0.0) 1 | com_login (3.0.0) 1 | com_admin (3.0.0) 1 | com_media (3.0.0) 1 | com_users (3.0.0) 1 | com_cache (3.0.0) 1 | com_plugins (3.0.0) 1 | com_content (3.0.0) 1 | com_checkin (3.0.0) 1 |
3rd Party:: JW_DISQUS (3.6.0) 0 | com_ampz (3.5) 1 | COM_GANTRY (4.1.35) 1 | COM_JCE (2.6.32) 1 | GSD (3.1.9) 1 | COM_OSMAP (4.2.18) 1 | com_contenttemplater (7.4.6) 1 | AcyMailing table of contents g (1.0.0) 1 | AcyMailing JCE integration (5.10.4) 1 | AcyMailing Tag : Date / Time (5.10.4) 1 | AcyMailing Tag and filter : Co (3.7.2) 1 | AcyMailing Tag and filter : Co (3.7.2) 1 | AcyMailing Editor (5.10.4) 1 | AcyMailing : (auto)Subscribe d (5.10.4) 1 | AcyMailing : share on social n (1.0.0) 1 | AcyMailing Manage text (1.0.0) 1 | AcyMailing : trigger Joomla Co (3.7.0) 1 | AcyMailing Tag : content inser (3.7.0) 1 | AcyMailing : Statistics Plugin (3.7.0) 1 | AcyMailing Tag : Manage the Su (5.10.4) 1 | AcyMailing Tag : Website links (3.7.0) 1 | AcyMailing Tag : Subscriber in (5.10.4) 1 | AcyMailing Template Class Repl (5.10.4) 1 | AcyMailing Module (3.7.0) 1 | AcyMailing Tag : Joomla User I (5.10.4) 1 | AcyMailing (5.10.4) 1 | RokSprocket (2.1.24) 1 | Admintools (5.1.3) 1 | plg_installer_sh404sef (4.15.1.3863) 1 | sh404sef - Analytics plugin (4.15.1.3863) 1 | sh404sef - Offline code plugin (4.15.1.3863) 1 | sh404sef - Similar urls plugin (4.15.1.3863) 1 | PLG_SH404SEFCORE_SH404SEFSOCIA (4.15.1.3863) 1 | sh404sef - Default component s (4.15.1.3863) 1 | sh404sef - System plugin (4.15.1.3863) 1 | plg_system_shlib (0.4.0.678) 1 | sh404sef - System mobile templ (4.15.1.3863) 1 | sh404SEF (4.15.1.3863) 1 | Akeeba (6.1.1) 1 | com_dbreplacer (6.1.0) 1 |

Modules :: SITE ::
Core :: mod_articles_categories (3.0.0) 1 | mod_menu (3.0.0) 1 | mod_search (3.0.0) 1 | mod_wrapper (3.0.0) 1 | mod_related_items (3.0.0) 1 | mod_banners (3.0.0) 1 | mod_articles_news (3.0.0) 1 | mod_tags_similar (3.1.0) 1 | mod_tags_popular (3.1.0) 1 | mod_whosonline (3.0.0) 1 | mod_weblinks (3.6.0) 1 | mod_finder (3.0.0) 1 | mod_footer (3.0.0) 1 | mod_syndicate (3.0.0) 1 | mod_random_image (3.0.0) 1 | mod_articles_category (3.0.0) 1 | mod_breadcrumbs (3.0.0) 1 | mod_users_latest (3.0.0) 1 | mod_custom (3.0.0) 1 | mod_login (3.0.0) 1 | mod_stats (3.0.0) 1 | mod_articles_latest (3.0.0) 1 | mod_languages (3.5.0) 1 | mod_articles_archive (3.0.0) 1 | mod_feed (3.0.0) 1 | mod_articles_popular (3.0.0) 1 |
3rd Party:: RokSprocket Module (2.1.24) 1 | RokNavMenu (2.0.9) 1 | AcyMailing Module (3.7.0) 1 |

Modules :: ADMIN ::
Core :: mod_menu (3.0.0) 1 | mod_title (3.0.0) 1 | mod_latest (3.0.0) 1 | mod_submenu (3.0.0) 1 | mod_toolbar (3.0.0) 1 | mod_multilangstatus (3.0.0) 1 | mod_custom (3.0.0) 1 | mod_login (3.0.0) 1 | mod_version (3.0.0) 1 | mod_logged (3.0.0) 1 | mod_status (3.0.0) 1 | mod_sampledata (3.8.0) 1 | mod_popular (3.0.0) 1 | mod_stats_admin (3.0.0) 1 | mod_feed (3.0.0) 1 | mod_quickicon (3.0.0) 1 |
3rd Party:: mod_cachecleaner (6.3.0) 1 |

Plugins :: SITE ::
Core :: plg_twofactorauth_totp (3.2.0) 0 | plg_twofactorauth_yubikey (3.2.0) 0 | plg_authentication_cookie (3.0.0) 1 | plg_authentication_ldap (3.0.0) 0 | plg_authentication_joomla (3.0.0) 1 | plg_authentication_gmail (3.0.0) 0 | plg_system_logout (3.0.0) 1 | plg_system_debug (3.0.0) 1 | plg_system_log (3.0.0) 1 | plg_system_languagecode (3.0.0) 0 | plg_system_stats (3.5.0) 1 | plg_system_updatenotification (3.5.0) 1 | plg_system_cache (3.0.0) 0 | plg_system_languagefilter (3.0.0) 0 | plg_system_fields (3.7.0) 0 | plg_system_p3p (3.0.0) 1 | plg_system_remember (3.0.0) 0 | plg_system_sef (3.0.0) 1 | plg_system_redirect (3.0.0) 0 | plg_system_highlight (3.0.0) 1 | plg_system_sessiongc (3.8.6) 1 | plg_content_vote (3.0.0) 1 | plg_content_loadmodule (3.0.0) 1 | plg_content_pagebreak (3.0.0) 1 | plg_content_pagenavigation (3.0.0) 1 | plg_content_fields (3.7.0) 1 | plg_content_finder (3.0.0) 0 | plg_content_joomla (3.0.0) 1 | plg_content_emailcloak (3.0.0) 1 | plg_editors-xtd_image (3.0.0) 1 | plg_editors-xtd_menu (3.7.0) 1 | plg_editors-xtd_pagebreak (3.0.0) 1 | plg_editors-xtd_fields (3.7.0) 1 | plg_editors-xtd_article (3.0.0) 1 | plg_editors-xtd_module (3.5.0) 1 | plg_editors-xtd_readmore (3.0.0) 1 | plg_fields_usergrouplist (3.7.0) 1 | plg_fields_textarea (3.7.0) 1 | plg_fields_integer (3.7.0) 1 | plg_fields_url (3.7.0) 1 | plg_fields_imagelist (3.7.0) 1 | plg_fields_text (3.7.0) 1 | plg_fields_calendar (3.7.0) 1 | plg_fields_color (3.7.0) 1 | plg_fields_list (3.7.0) 1 | plg_fields_checkboxes (3.7.0) 1 | plg_fields_editor (3.7.0) 1 | plg_fields_radio (3.7.0) 1 | plg_fields_sql (3.7.0) 1 | plg_fields_media (3.7.0) 1 | plg_fields_user (3.7.0) 1 | plg_extension_joomla (3.0.0) 1 | plg_finder_contacts (3.0.0) 1 | plg_finder_categories (3.0.0) 1 | plg_finder_content (3.0.0) 1 | plg_finder_tags (3.0.0) 1 | plg_finder_newsfeeds (3.0.0) 1 | plg_finder_weblinks (3.6.0) 1 | plg_captcha_recaptcha (3.4.0) 1 | plg_installer_packageinstaller (3.6.0) 1 | PLG_INSTALLER_FOLDERINSTALLER (3.6.0) 1 | PLG_INSTALLER_URLINSTALLER (3.6.0) 1 | plg_search_contacts (3.0.0) 1 | plg_search_categories (3.0.0) 1 | plg_search_content (3.0.0) 1 | plg_search_tags (3.0.0) 1 | plg_search_newsfeeds (3.0.0) 1 | plg_search_weblinks (3.6.0) 1 | plg_user_profile (3.0.0) 0 | plg_user_contactcreator (3.0.0) 0 | plg_user_joomla (3.0.0) 1 | plg_quickicon_phpversioncheck (3.7.0) 1 | plg_quickicon_joomlaupdate (3.0.0) 1 | plg_quickicon_extensionupdate (3.0.0) 1 |
3rd Party:: System - Gantry 4 (4.1.35) 1 | plg_system_jce (2.6.32) 1 | plg_system_regularlabs (18.8.5928) 1 | System - Canonical (3.1.1M) 0 | plg_system_ossystem (1.3.0) 1 | manage.myJoomla.com Secure Plu (n/a) 1 | System - RokExtender (2.0.0) 1 | System - RokCommon (3.2.6) 1 | PLG_SYS_ADMINEXILE (3.16.3) 1 | plg_system_contenttemplater (7.4.6) 1 | plg_system_gsd (3.1.9) 1 | System - ampz (3.5) 1 | System - Admin Tools (5.1.3) 1 | plg_system_cachecleaner (6.3.0) 1 | plg_system_betterpreview (6.1.1) 1 | System - RokSprocket (2.1.24) 1 | System - Cloudflare Universal (1.0.0) 1 | plg_system_nrframework (3.1.14) 1 | System - Disqus Comments (for (3.6.0) 1 | AcyMailing : (auto)Subscribe d (5.10.4) 1 | AcyMailing JCE integration (5.10.4) 1 | sh404sef - System plugin (4.15.1.3863) 1 | plg_system_shlib (0.4.0.678) 1 | sh404sef - System mobile templ (4.15.1.3863) 1 | System - Admin Forever (0.9.2) 1 | PLG_SYSTEM_AKEEBAUPDATECHECK_T (1.1) 1 | PLG_SYSTEM_BACKUPONUPDATE_TITL (3.7) 1 | PLG_SEOFLI (3.3.4) 1 | PLG_AUTHORINFOBOX (3.1.4) 1 | plg_content_jce (2.6.32) 1 | AllVideos (by JoomlaWorks) (4.8.0) 1 | AllVideos (by JoomlaWorks) (4.8.0) 1 | Content - RokInjectModule (2.1.24) 1 | Disqus Comments (for Joomla) [ (3.6.0) 1 | Art Sexy Lightbox (2.1.82) 1 | plg_editors-xtd_contenttemplat (7.4.6) 1 | PLG_EDITORS-XTD_AMPZ (3.5) 0 | plg_editors-xtd_betterpreview (6.1.1) 1 | OSMap - Kunena Plugin (3.3.0) 0 | OSMap - WebLinks Plugin (3.3.0) 0 | OSMap - Mosets Tree Plugin (3.3.0) 0 | OSMap - Virtuemart Plugin (3.3.0) 0 | OSMAP_PLUGIN_K2 (3.3.0) 0 | OSMap - SobiPro Plugin (3.3.0) 0 | PLG_OSMAP_JOOMLA (4.2.18) 1 | PLG_GSD_MENUS (1.0) 0 | PLG_GSD_HIKASHOP (1.0) 1 | PLG_GSD_K2 (1.0) 1 | PLG_GSD_CONTENT (1.0) 1 | PLG_GSD_RSBLOG (1.0) 1 | PLG_GSD_EASYBLOG (1.0) 1 | PLG_GSD_ZOO (1.0) 1 | PLG_GSD_JSHOPPING (1.0) 1 | PLG_GSD_SPPAGEBUILDER (1.0) 1 | PLG_GSD_VIRTUEMART (1.0) 1 | plg_editors_codemirror (5.38.0) 1 | plg_editors_jce (2.6.32) 1 | AcyMailing Editor (5.10.4) 1 | plg_editors_tinymce (4.5. 1 | plg_fields_mediajce (2.6.32) 1 | plg_extension_jce (2.6.32) 1 | AcyMailing Tag : Joomla User I (5.10.4) 1 | AcyMailing : share on social n (1.0.0) 1 | AcyMailing : trigger Joomla Co (3.7.0) 1 | AcyMailing Tag : Date / Time (5.10.4) 1 | AcyMailing Tag : Website links (3.7.0) 1 | AcyMailing Manage text (1.0.0) 1 | AcyMailing : Statistics Plugin (3.7.0) 1 | AcyMailing Tag : content inser (3.7.0) 1 | AcyMailing table of contents g (1.0.0) 1 | AcyMailing Template Class Repl (5.10.4) 1 | AcyMailing Tag : Manage the Su (5.10.4) 1 | AcyMailing Tag : Subscriber in (5.10.4) 1 | AcyMailing Tag and filter : Co (3.7.2) 1 | AcyMailing Tag and filter : Co (3.7.2) 1 | plg_installer_jce (2.6.32) 1 | plg_installer_sh404sef (4.15.1.3863) 1 | PLG_USER_AIBPROFILE (3.1.4) 1 | plg_quickicon_jce (2.6.32) 1 | plg_quickicon_akeebabackup (1.0) 1 | sh404sef - Analytics plugin (4.15.1.3863) 1 | sh404sef - Offline code plugin (4.15.1.3863) 1 | sh404sef - Similar urls plugin (4.15.1.3863) 1 | PLG_SH404SEFCORE_SH404SEFSOCIA (4.15.1.3863) 1 | sh404sef - Default component s (4.15.1.3863) 1 |

Templates :: SITE :: protostar (1.0) 1 | rt_oculus (1.9) 1 | beez3 (3.1.0) 1 |
Templates :: ADMIN :: hathor (3.0.0) 1 | isis (1.0) 1 |

[DJBenz]

This issue started a day or so ago. I'm not sure if it coincided with the update to 3.8.12. The other change that happened at the same time was I updated the AMPZ Social Sharing plugin from 3.0.8 to 3.5 (its auto-update feature wasn't working and I found the update during a manual sweep of extensions). I tried disabling said extension, but it didn't seem to make a difference.

My host has said this about the spikes:

We noticed a spike in database usage during that period and the MySQL governor we have in place to restrict MySQL usage and kill off slow SELECT queries also indicated a lot of slow SELECT queries killed around that period like the following ones related to the database user '[db_user]' to the database '[database]'.

-----------------------------------------------------------------------------------------------------
[Tue Aug 28 06:54:09 2018] Query killed - [db_user] : SELECT A.ID, A.TITLE, A.ALIAS, A.INTROTEXT, A.`FULLTEXT`, A.CATID, A.STATE, A.ACCESS, A.CREATED, A.CREATED_BY, A.CREATED_BY_ALIAS, A.MODIFIED, A.FEATURED, A.LANGUAGE, A.HITS, A.PUBLISH_UP, A.PUBLISH_DOWN, A.IMAGES, A.URLS, A.LANGUAGE, A.METAKEY, A.METADESC, A.METADATA,C.TITLE AS CATEGORY_TITLE, C.ALIAS AS CATEGORY_ALIAS,UA.NAME AS AUTHOR_NAME,UM.NAME AS LAST_MODIFIED_BY,ROUND(V.RATING_SUM / V.RATING_COUNT, 0) AS RATING, V.RATING_COUNT AS RATING_COUNT,VL.TITLE AS ACCESS_TITLE,L.TITLE AS LANGUAGE_TITLE
FROM XXXX_CONTENT AS A
LEFT JOIN XXXX_CATEGORIES AS C ON C.ID = A.CATID
LEFT JOIN XXXX_USER
------------------------------------------------------------------------------------------------------

Currently the number of seconds while slow request can be finished is configured to 60 seconds for avoiding MySQL service interruption server wide. Otherwise, it will be cancelled.

We also noticed a lot of database queries/connections related to the database/db user '[database]' and '[db_user]' in the MySQL process stats and all of them were actively sending data during that period, with respect to the aforementioned slow SELECT queries.

-------------------------------------------------------------------------------------------------------
| 47211 | [db_user] | localhost | [database] | Query | 30 | Sending data | SELECT a.id, a.title, a.alias, a.introtext, a.`fulltext`, a.catid, a.state, a.access, a.created, a.c | 0.000 |
| 47259 | [db_user] | localhost | [database] | Query | 39 | Sending data | SELECT a.id, a.title, a.alias, a.introtext, a.`fulltext`, a.catid, a.state, a.access, a.created, a.c | 0.000 |
| 47260 | [db_user] | localhost | [database] | Query | 39 | Sending data | SELECT a.id, a.title, a.alias, a.introtext, a.`fulltext`, a.catid, a.state, a.access, a.created, a.c | 0.000 |
| 47261 | [db_user] | localhost | [database] | Query | 39 | Sending data | SELECT a.id, a.title, a.alias, a.introtext, a.`fulltext`, a.catid, a.state, a.access, a.created, a.c | 0.000 |
| 47297 | [db_user] | localhost | [database] | Query | 30 | Sending data | SELECT a.id, a.title, a.alias, a.introtext, a.`fulltext`, a.catid, a.state, a.access, a.created, a.c | 0.000 |
| 47312 | [db_user] | localhost | [database] | Query | 28 | Sending data | SELECT a.id, a.title, a.alias, a.introtext, a.`fulltext`, a.catid, a.state, a.access, a.created, a.c | 0.000 |
| 47313 | [db_user] | localhost | [database] | Query | 29 | Sending data | SELECT a.id, a.title, a.alias, a.introtext, a.`fulltext`, a.catid, a.state, a.access, a.created, a.c | 0.000 |
| 47319 | [db_user] | localhost | [database] | Query | 28 | Sending data | SELECT a.id, a.title, a.alias, a.introtext, a.`fulltext`, a.catid, a.state, a.access, a.created, a.c | 0.000 |
| 47328 | [db_user] | localhost | [database] | Query | 27 | Sending data | SELECT a.id, a.title, a.alias, a.introtext, a.`fulltext`, a.catid, a.state, a.access, a.created, a.c | 0.000 |
| 47330 | [db_user] | localhost | [database] | Query | 26 | Sending data | SELECT a.id, a.title, a.alias, a.introtext, a.`fulltext`, a.catid, a.state, a.access, a.created, a.c | 0.000 |
| 47337 | [db_user] | localhost | [database] | Query | 24 | Sending data | SELECT a.id, a.title, a.alias, a.introtext, a.`fulltext`, a.catid, a.state, a.access, a.created, a.c | 0.000 |
| 47338 | [db_user] | localhost | [database] | Query | 24 | Sending data | SELECT a.id, a.title, a.alias, a.introtext, a.`fulltext`, a.catid, a.state, a.access, a.created, a.c | 0.000 |
| 47339 | [db_user] | localhost | [database] | Query | 24 | Sending data | SELECT a.id, a.title, a.alias, a.introtext, a.`fulltext`, a.catid, a.state, a.access, a.created, a.c | 0.000 |
| 47395 | [db_user] | localhost | [database] | Query | 9 | Sending data | SELECT a.id, a.title, a.alias, a.introtext, a.`fulltext`, a.catid, a.state, a.access, a.created, a.c | 0.000 |
| 47396 | [db_user] | localhost | [database] | Query | 9 | Sending data | SELECT a.id, a.title, -a.alias, a.introtext, a.`fulltext`, a.catid, a.state, a.access, a.created, a.c | 0.000 |
--------------------------------------------------------------------------------------------------------

Any ideas where these slow SELECT queries are coming from? cPanel usage graph below showing the point at which things started to spike. It has settled down somewhat now, but still getting the occasional spike in every 24 hour period.

[SharkyKZ]

Any ideas where these slow SELECT queries are coming from?

Click on Call Stack to see exactly where it's coming from.

[DJBenz]

Any ideas where these slow SELECT queries are coming from?

Click on Call Stack to see exactly where it's coming from.

Thanks. Where can I find Call Stack to click on? (sorry, I'm a bit lacking in debugging knowledge).

EDIT: I see it now in the debugging console. I guess I would need to try it during one of the periods of CPU spiking? I can't really see any of the queries that match other than:

Code: Select all

SELECT a.id, a.title, a.checked_out, a.checked_out_time,  a.access, a.created, a.created_by, a.created_by_alias, a.featured, a.state, a.publish_up, a.publish_down,l.title AS language_title, l.image AS language_image,uc.name AS editor,ag.title AS access_level,c.title AS category_title,ua.name AS author_name,COALESCE(NULLIF(ROUND(v.rating_sum  / v.rating_count, 0), 0), 0) AS rating, 
					COALESCE(NULLIF(v.rating_count, 0), 0) as rating_count

  FROM xxxxx_content AS a

  LEFT JOIN `xxxxx_languages` AS l 
  ON l.lang_code = a.language

  LEFT JOIN xxxxx_users AS uc 
  ON uc.id=a.checked_out

  LEFT JOIN xxxxx_viewlevels AS ag 
  ON ag.id = a.access

  LEFT JOIN xxxxx_categories AS c 
  ON c.id = a.catid

  LEFT JOIN xxxxx_users AS ua 
  ON ua.id = a.created_by

  LEFT JOIN xxxxx_content_rating AS v 
  ON a.id = v.content_id

  ORDER BY created DESC 
  LIMIT 5

[DJBenz]

I could use a little help on this issue as it's still happening and the spikes in MySQL queries, along with slow SELECT queries are causing regular downtime.

[PhilTaylor-Prazgod]

Solution: Get a better webhost!

These issues are not Joomla issues, they are mysql hosting issues.

To prove this without moving your hosting, use an external database provider like https://www.rackspace.com/cloud/databases as a test, and you will see the problem magically disappears.

Joomla core sql queries have all (mostly) been performance checked and EXPLAIN'ed to ensure that correct indexes are used.

[DJBenz]

Solution: Get a better webhost!

These issues are not Joomla issues, they are mysql hosting issues.

To prove this without moving your hosting, use an external database provider like https://www.rackspace.com/cloud/databases as a test, and you will see the problem magically disappears.

Joomla core sql queries have all (mostly) been performance checked and EXPLAIN'ed to ensure that correct indexes are used.

Thanks Phil, could you elaborate more on what the MySQL hosting issues are? The host is very helpful (they have actually offered to move me to a VPS at the same cost as my shared hosting account) and if they could improve anything to make Joomla run more smoothly I'm sure they would.

(I'm a MyJoomla customer, btw).

[SharkyKZ]

Given the high number of 3rd party extensions, this could easily be a problem in your Joomla installation. Is there any way to view the full queries that have been stopped? The ones in the posts above seem to be truncated. If 'Sending data' is the slowest process, it could be that some extension selects multiple rows from a large table without setting any limit.

[PhilTaylor-Prazgod]

The sql queries in the reports above are core Joomla queries.

Buy and use https://extensions.joomla.org/extension/acl-manager/

If that doesnt help (It will check your assets table) - get a better webhost. Your webhost should be knowledgeable on how to configure a mysql server, if they are not, or incompetent your only choice is to move. Its not for you to educate them.

the MySQL governor we have in place to restrict MySQL usage

So by their own admission they are throttling your mysql usage!.....

[SharkyKZ]

The first query is not a Joomla query. Quick search shows it's coming from RokSprocket.

[PhilTaylor-Prazgod]

The first query is not a Joomla query. Quick search shows it's coming from RokSprocket.

Well you can believe what you want to believe then up to you.

[DJBenz]

the MySQL governor we have in place to restrict MySQL usage

So by their own admission they are throttling your mysql usage!.....

Well, yes, because the high usage by my site was going over resource on the server and affecting other shared hosting customers.

I'll check out the extension you suggested. Appreciated.

[DJBenz]

This query, according to the debug console, is taking a long time (866.77ms, query memory 1.039MB)

Code: Select all

SELECT a.id, a.title, a.alias, a.introtext, a.`fulltext`, a.catid, a.state, a.access, a.created, a.created_by, a.created_by_alias, a.modified, a.featured, a.language, a.hits, a.publish_up, a.publish_down, a.images, a.urls, a.language, a.metakey, a.metadesc, a.metadata,c.title AS category_title, c.alias AS category_alias,ua.name AS author_name,um.name AS last_modified_by,ROUND(v.rating_sum / v.rating_count, 0) AS rating, v.rating_count as rating_count,vl.title AS access_title,l.title AS language_title

  FROM xxxxx_content as a

  LEFT JOIN xxxxx_categories AS c 
  ON c.id = a.catid

  LEFT JOIN xxxxx_users AS ua 
  ON ua.id = a.created_by

  LEFT JOIN xxxxx_users AS um 
  ON um.id = a.modified_by

  LEFT JOIN xxxxx_content_rating AS v 
  ON a.id = v.content_id

  LEFT JOIN xxxxx_viewlevels AS vl 
  ON a.access = vl.id

  LEFT JOIN xxxxx_languages AS l 
  ON a.language = l.lang_code

  LEFT JOIN xxxxx_contentitem_tag_map AS ct 
  ON ct.content_item_id = a.id

  LEFT JOIN xxxxx_tags AS t 
  ON t.id = ct.tag_id

  WHERE ((a.access IN(1,1,2,3,6) 
  AND (a.state != -2)) 
  AND a.featured IN (1) 
  AND a.catid IN (21,40,44,15,17,19,59,20,4,47,48,53,54,58) 
  AND a.state IN (1))

  GROUP BY id

  ORDER BY a.created DESC

Call stack is:

Code: Select all

#	Caller	File and line number
16	JDatabaseDriverMysqli->execute()	JROOT/libraries/joomla/database/driver.php:1691
15	JDatabaseDriver->loadObjectList()	JROOT/components/com_roksprocket/lib/RokSprocket/Provider/AbstarctJoomlaBasedProvider.php:58
14	RokSprocket_Provider_AbstarctJoomlaBasedProvider->getItems()	JROOT/components/com_roksprocket/lib/RokSprocket.php:294
13	RokSprocket::getItems()	JROOT/components/com_roksprocket/lib/RokSprocket.php:230
12	RokSprocket::getItemsWithParams()	JROOT/administrator/components/com_roksprocket/models/module.php:1072
11	RokSprocketModelModule->getArticles()	JROOT/administrator/components/com_roksprocket/views/module/view.html.php:56
10	RokSprocketViewModule->display()	JROOT/libraries/src/MVC/Controller/BaseController.php:672
9	Joomla\CMS\MVC\Controller\BaseController->display()	JROOT/administrator/components/com_roksprocket/controller.php:77
8	RokSprocketController->display()	JROOT/libraries/src/MVC/Controller/BaseController.php:710
7	Joomla\CMS\MVC\Controller\BaseController->execute()	JROOT/administrator/components/com_roksprocket/roksprocket.php:28
6	require_once JROOT/administrator/components/com_roksprocket/roksprocket.php	JROOT/libraries/src/Component/ComponentHelper.php:382
5	Joomla\CMS\Component\ComponentHelper::executeComponent()	JROOT/libraries/src/Component/ComponentHelper.php:357
4	Joomla\CMS\Component\ComponentHelper::renderComponent()	JROOT/libraries/src/Application/AdministratorApplication.php:101
3	Joomla\CMS\Application\AdministratorApplication->dispatch()	JROOT/libraries/src/Application/AdministratorApplication.php:159
2	Joomla\CMS\Application\AdministratorApplication->doExecute()	JROOT/libraries/src/Application/CMSApplication.php:195
1	Joomla\CMS\Application\CMSApplication->execute()	JROOT/administrator/index.php:51

This is when I access the module manager for Roksprocket as suggested by SharkyKZ.

[DJBenz]

UPDATE: I think I found the reason for this

I have two Roksprocket modules on the homepage; one uses the 'Features' layout to showcase the latest articles. Limit is 10 articles, selected where the category is one of nine and the status is published and featured.

The other uses the 'Lists' layout and showcases latest news articles, Limit 12, selected where category is 'News' and status is published and featured... or so I thought.

It was actually set to draw from published only; in the region of 2000 articles. So every time the module was making the query, it was selecting 2000 articles and only displaying 12 of them. I set the module to published and featured and the MySQL usage graphs have dropped right off.

Thanks for everyone's help.

[PhilTaylor-Prazgod]

866.77ms, query memory 1.039MB is NOT excessively long or big for a decent mysql service.

2000 rows is not an excessively big amount to pull from a database - probably bigger than most results, but not excessive.

A decent mysql service should be able to do this without even pulling a sweat. Without even a spike in a monitoring graph.

Pulling 2000 rows to only show a few is just bad code on the part of the developer for not adding any LIMIT statements in the sql query.