Large Scale Installation is Sluggish

Description of Problem:
I'm helping out with a very large installation of Revolution, in which an excess of two hundred sites are residing. Somewhat unsurprisingly, we're facing issues with it being sluggish - from document saving times taking an excess of 30 seconds, to the resource tree taking about 20 seconds to load. To distribute the load, we have the installation configured as http://develop.modx.com/blog/2011/revolution-supports-mysql-replication-master-slave/ describes. However, with the performance as it is, I have my doubts about whether that is working properly. Adding to the doubts is the fact that every day the size of the error log exceeds 500 MB, primarily containing error messages like the second of the two pasted below.

My first question concerns how to correct such errors. Of course my first thought was to remove the --read-only option from the database that was specified. However, my understanding of the current configuration is that writes are funneled through the master database and automatically reflected (somehow with no regard for read-only) on the two slaves, while reads are primarily directed towards the slaves, lightening the load on the master. I didn't want to make one of the databases writable if that wasn't necessary.

Ultimately my question is - how else might I improve the overall performance?

Thanks.
Chris

• MODX Version: Revolution 2.2.2-pl
• PHP Version: 5.3.13
• Database Version: MySQL Ver 14.14 Distrib 5.5.20
• Additional Server Info: AWS, fairly high-spec
• Error Log Contents:
[2012-11-27 03:06:05] (ERROR @ /index.php) Error HY000 executing statement:
INSERT INTO `modx_session` (`id`, `access`, `data`) VALUES ('d4nhsbkgog1d5d8j17kbtmajl6', 1353953165,
'modx.user.contextTokens|a:0:{}')
Array
(
[0] => HY000
[1] => 1290
[2] => The MySQL server is running with the --read-only option so it cannot execute this statemen
t
)

[2012-11-27 03:06:07] (ERROR @ /index.php) Error HY000 executing statement:
INSERT INTO `modx_session` (`id`, `access`, `data`) VALUES ('jjim9pdfbh5ah187gnh2b36977', 1353953167,
'modx.user.0.resourceGroups|a:1:{s:24:\"<omitted>\";a:0:{}}modx.user.0.attributes|a:1
:{s:24:\"<omitted>\";a:4:{s:16:\"modAccessContext\";a:2:{s:3:\"web\";a:1:{i:0;a:3:{s:9
:\"principal\";i:0;s:9:\"authority\";s:1:\"0\";s:6:\"policy\";a:1:{s:4:\"load\";b:1;}}}s:20:\"<omitted>\";a:1:{i:0;a:3:{s:9:\"principal\";i:0;s:9:\"authority\";s:1:\"0\";s:6:\"policy\";a:1:{s
:4:\"load\";b:1;}}}}s:22:\"modAccessResourceGroup\";a:0:{}s:17:\"modAccessCategory\";a:0:{}s:28:\"sou
rces.modAccessMediaSource\";a:0:{}}}modx.user.contextTokens|a:0:{}')
Array
(
[0] => HY000
[1] => 1290
[2] => The MySQL server is running with the --read-only option so it cannot execute this statemen
t
)

Hi Chris,

I am not an expert in MySQL replication (i.e. consult a DBAdmin not me), but from the error message it appears MySQL is using a user without permissions to write to the master -- or the requests are not being routed correctly. Can you turn on query logging for all databases and trace some through?

Regarding other ways: I've done plenty of hacks to serve static HTML content, and I believe similar hacks have recently been packaged in the xFPC extra. If most of your traffic is non-logged-in users then these will greatly help.

However, have you worked out what the real reason for the speed issue is? Is the DB too large to keep in memory, so you've the joy of AWS 'disk' IO? A CPU bottleneck? What does profiling tell you?

This is simply a session problem.

In order to effectively use the master/slave configuration, you will need to turn off sessions on the front-end, or change the session handler for the front-end contexts to the standard file-based PHP sessions (set the session_handler_class System Setting to empty and it will use the session handler specified in your php.ini). Otherwise, the database session handler would require a writable connection on every request that created or altered session data. This is simply incompatible with a master/slave configuration.

Quote from: opengeek at Nov 29, 2012, 12:42 PM

This is simply incompatible with a master/slave configuration.

PHP database-backed sessions should not be incompatible with this configuration. They'll be inefficient, but so long as other queries are the reason read-only slaves are needed, it should function.

I did some more digging and wonder @chrisf if you have your xPDO connection correctly setup for master/slave?

He may have it setup correctly, but the session handling does not automatically connect to a mutable connection (i.e. master).