ModX 1.0.8 overloads mysql server consistantly

38 Posts

AndrewBrg Reply #1, 13 years, 4 months ago

Hi Guys,

I would really appreciate any help you could offer regarding this matter.

We have recently taken a modx evolution driven website live to replace an old and outdated we used to have. The site new works great however under even low visitor load 50-90 visitors modx seems to be eating the server and causing site-wide slow downs across all users to the point that the entire site is rendered unusable.

Via server monitoring it seems that the mysql process spikes to 800% CPU usage at times and is consistently going over 200/300% even with this low amount of traffic!!

The server runs Centos 6 with Apache in Fast CGI mode.

The site makes use of the following plugins:

YAMS (http://modx.com/extras/package/yams)
Easy Gallery (http://modx.com/extras/package/easy2gallery)

From the mysql slow logs only the following SQL statement crops up:

# Time: 130622 12:38:15
# User@Host: newbetat_beta[newbetat_beta] @ localhost []
# Query_time: 8.318775  Lock_time: 0.037026 Rows_sent: 256  Rows_examined: 1968694
SET timestamp=1371901095;
(SELECT sc.id AS docid, st.name AS name, IF(stc.value != '',stc.value,st.default_text) AS value, st.display AS display, st.display_params AS display_params, st.type AS$
# Time: 130622 12:38:17
# User@Host: newbetat_beta[newbetat_beta] @ localhost []
# Query_time: 8.088842  Lock_time: 0.024015 Rows_sent: 256  Rows_examined: 1968694
SET timestamp=1371901097;
(SELECT sc.id AS docid, st.name AS name, IF(stc.value != '',stc.value,st.default_text) AS value, st.display AS display, st.display_params AS display_params, st.type AS$
# Time: 130622 12:38:18
# User@Host: newbetat_beta[newbetat_beta] @ localhost []
# Query_time: 7.981864  Lock_time: 0.028773 Rows_sent: 256  Rows_examined: 1968694
SET timestamp=1371901098;
(SELECT sc.id AS docid, st.name AS name, IF(stc.value != '',stc.value,st.default_text) AS value, st.display AS display, st.display_params AS display_params, st.type AS$

As you can see the query time is through the roof.

The server error logs show a lot of the following:

[warn] [client 54.224.71.248] mod_fcgid: read data timeout in 45 seconds, referer:
[error] [client 54.224.71.248] Premature end of script headers: index.php, referer:

The site itself is highly ajax driven and pulls a lot of various sections via ajax pages at regular intervals.

The ajax pages also make use of ModX in API mode. The following is an extract of the php code used to initialize the ModX class in the ajax pages:

<?php
header('Content-Type: text/html; charset=UTF-8');

// Setup the MODx API
define('MODX_API_MODE', true);
define('MODX_BASE_URL', '/');
define('MODX_SITE_URL', '/');
include_once($_SERVER['DOCUMENT_ROOT'].'/index.php');

if ($modx instanceof DocumentParser)
{
   //Rest of php code goes in here with access to the $modx class.
}

?>

☆ A M B ☆
24,524 Posts

sottwell Reply #2, 13 years, 4 months ago

The problem with a lot of AJAX is that it can't take advantage of the caching subsystem. Sounds like you have a lot of Ditto or similar calls that are not cached. Perhaps you could use a plugin to make one query, cache the results to an object or set of arrays, then your AJAX calls could get their data from that object, instead of each one making its own queries.

Studying MODX in the desert - http://sottwell.com
Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
Join the Slack Community - http://modx.org

38 Posts

AndrewBrg Reply #3, 13 years, 4 months ago

Thank you for your reply.

There is not much i can do in terms of caching the ajax requests i'm afraid as the response is in itself dynamic and user relevant.

An ajax call made now will return a different value than the same call made in 10 seconds time.

However it seems strange to me that the only sql queries that were taking long to execute (therefore could possible be the ones slowing the site down to a snails pace) are the ones listed in my first post.

They seem to be internal modx queries.

It should also be mentioned that the site itself make a considerable number of SOAP calls to our backend(not modx) in order to retrieve data for the frontend.

The SOAP client is initialised as follows, however i do not imagine that this is relevant since the CPU hog by far is the mysqld process.

$this->client = new SoapClient($passed_ServiceURL,array(
		'soap_version' 		 => SOAP_1_1,
		'keep_alive' 		 => false,
		'connection_timeout' => 5,
		'stream_context' 	 => $context,
		'cache_wsdl'		 => WSDL_CACHE_BOTH,
		'compression' 		 => SOAP_COMPRESSION_ACCEPT | SOAP_COMPRESSION_GZIP
		));