CUNY Academic Commons

Boone, we can monitor the MySQL process threads via wp-cli with wp db query "SHOW PROCESSLIST;"

Process list looks good so far. Only one process for the Commons DB! (Basically zero, because I initiated the SHOW PROCESSLIST command.)

This seems to be working well so far, but this morning I woke up to a notice that, just after midnight, the db service restarted. The processlist around that time looks like this:

2283661    commons_admin    lw3a.mgt.gc.cuny.edu:42724    commons_wp    Query    159    Waiting for table flush    SELECT `data` FROM `pj_wpdb_sessions` WHERE `id` = '49hn7asah7qkurgfnh9rt9t1d4'    0    0
2283662    commons_admin    lw3a.mgt.gc.cuny.edu:42730    commons_wp    Query    159    wsrep in pre-commit stage    UPDATE `wp_1013_options` SET `option_value` = '1513834643' WHERE `option_name` = 'wysija_last_php_cr    0    1
2283663    commons_admin    lw3a.mgt.gc.cuny.edu:42734    commons_wp    Query    160    Waiting for table flush    SELECT `data` FROM `pj_wpdb_sessions` WHERE `id` = 'c13cob5atlu2t2k4vsb7kgsu13'    0    0
2283665    commons_admin    lw3a.mgt.gc.cuny.edu:42742    commons_wp    Query    159    Waiting for table flush    SELECT `data` FROM `pj_wpdb_sessions` WHERE `id` = 'tcceb1kdii3v567dhk0qnfe5m6'    0    0
2283667    commons_admin    lw3a.mgt.gc.cuny.edu:42752    commons_wp    Query    158    wsrep in pre-commit stage    UPDATE `wp_885_options` SET `option_value` = 'a:17:{i:1513834437;a:1:{s:17:\\"jetpack_sync_cron\\";a:1    0    1
2283668    commons_admin    lw3a.mgt.gc.cuny.edu:42758    commons_wp    Query    158    Waiting for table flush    SELECT `data` FROM `pj_wpdb_sessions` WHERE `id` = 'cu4ul702d0o9b0nbgi5n6advq3'    0    0
2283670    commons_admin    lw3a.mgt.gc.cuny.edu:42768    commons_wp    Query    159    Waiting for table flush    SELECT `data` FROM `pj_wpdb_sessions` WHERE `id` = 'uah6tode67njf44bju8383b017'    0    0

So, it looks like there might be some sort of weirdness happening during a cron job. I'm not sure whether the session stuff is a symptom or the cause. "Waiting for table flush" makes me wonder whether this is being triggered by a backup routine, running with --lock-tables. See eg https://www.percona.com/blog/2012/03/23/how-flush-tables-with-read-lock-works-with-innodb-tables/. Ray, does that sound reasonable? Might it be worth asking Lihua if we can exclude this table from backup, or at least run it without table lock?

On a separate but related issue, I wonder if we might try to weed out some of the plugins that run `session_start()`. Some of them may actually need it; some may run it in a way that could easily be converted to something else (like Anthologize); some may be doing it for no reason at all, especially for logged-out users. The session table currently has about 115,000 items in it, and it seems like we might be able to get this number down by trimming some of the worst offenders.

I've made the necessary changes to Anthologize to remove PHP sessions. The new release should be ready for Commons 1.13.

The drop-in has been working successfully on the Commons for a while, so I've moved it to the repo. It's disabled by default. To enable it locally, add the following to your environment file:

define( 'CAC_USE_WPDB_PHPSESSIONS', true );

https://github.com/cuny-academic-commons/cac/commit/4372ef8e5ea7a4ef370e405a3f0bd1147db286b5

Note that this is active in the 1.13.x branch. I'll continue to work on individual plugins for the 1.14 release.

During a database restart last night, I noticed that the log had lots of entries of the format:

0
897190 commons_admin lw3b.gc.cuny.edu:50798 commons_wp Query 57 Waiting for table flush SELECT `data` FROM `pj_wpdb_sessions` WHERE `id` = 'ts8mj8unq9vbpudsblh27411s2' 0 0

This might be a symptom of a different problem (like long running queries) rather than a problem in itself, but it made me take a second look into session handling. Specifically, Memcached feels like the appropriate place to store session data - rather than the default files or our custom database handler. I did a bit of work to figure out how this sort of handling ought to work, and here's what I've done as an experiment (cac-env-config.php):

//define( 'CAC_USE_WPDB_PHPSESSIONS', true );
session_save_path('localhost:11211?persistent=1&weight=2&timeout=10&retry_interva l=10');
session_module_name('memcached');

I'd like to leave this in place for a while to see if it has any effect on database usage.