Document page performance issue....
Submitted by jenelso2 on Wed, 09/19/2018 - 11:58
Recently we started seeing a lot more slowness on our site related to document pages. Specifically, documents that had a lot of pages. Our planetarium page for example (https://www.wsc.edu/info/20091/planetarium/711/fulldome_shows), up until a week ago that page took roughly 40-50 seconds to load. We also started seeing a lot of very high CPU utilization on our db server that didn't correlate to anything specific (cronjobs, etc.).
I ran some xdebug traces and tracked down what appears to be the root cause of the problem. MySQL doesn't properly optimize queries where ORDER BY and LIMIT are used together. When the CMS queries for the live version of a page, it's performing an "ORDER BY id DESC LIMIT 1" query. In this case, when a document page is loaded on our site, the left hand navigation that displays all other pages within the document is built by querying the database for the live version of the document for each page.
Long story short, I rewrote the query so that it forces the proper optimization on MySQL so that ORDER BY + LIMIT still works but takes a fraction of the time.
In the file /jadu/JaduVersions.php on line 169 I added:
// BEGIN - Added by Jeremy N. to fix page load times - 201809141010
$query = 'SELECT O.* FROM (SELECT ' . implode(', ', $columns) . ' ' .
'FROM ' . $this->table . ' ' .
'WHERE objectID = ' . intval($this->objectID) . ' ' .
'AND category = ' . $this->db->qstr($this->category) . ') AS O ' .
'ORDER BY id DESC';
// END
This modifies Jadu core files, which I know is a no-no. I've submitted this to Jadu Support in hopes that it will be added to core, but just in case anyone else has noticed or lived with slow document page loads for documents with lots of pages, maybe this will help.
-Jeremy
General Concept:
Jadu Product:
Jadu Version: