Project

General

Profile

Bug #9012

Improved PHP session handling

Added by Boone Gorges almost 4 years ago. Updated almost 3 years ago.

Status:
Resolved
Priority name:
Normal
Assignee:
Category name:
WordPress Plugins
Target version:
Start date:
2017-12-15
Due date:
% Done:

0%

Estimated time:

Description

Ray has a theory that recent performance issues may be tied to PHP sessions holding open connections. https://pressjitsu.com/blog/wordpress-sessions-performance/

I've put a drop-in replacement for the default session handler in place as a hotfix on the production site. https://github.com/pressjitsu/wpdb-phpsessions/blob/master/wpdb-phpsessions.php Let's monitor performance, and also be sure that the old sessions are cleared out (should be after 24 hours).

History

#1 Updated by Raymond Hoh almost 4 years ago

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.)

#2 Updated by Boone Gorges almost 4 years ago

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?

#3 Updated by Boone Gorges almost 4 years ago

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.

#4 Updated by Boone Gorges over 3 years ago

  • Target version changed from 1.12.5 to 1.12.6

Let's continue to look at this in January.

#5 Updated by Boone Gorges over 3 years ago

  • Target version changed from 1.12.6 to 1.12.7

#6 Updated by Boone Gorges over 3 years ago

  • Target version changed from 1.12.7 to 1.12.8

#7 Updated by Boone Gorges over 3 years ago

  • Category name set to WordPress Plugins
  • Assignee set to Boone Gorges

For 1.13, I'm going to do a sort of audit of plugins using sessions, to see if any can be mitigated. Anthologize will be at the top of that list.

#8 Updated by Boone Gorges over 3 years ago

  • Target version changed from 1.12.8 to 1.13

#9 Updated by Boone Gorges over 3 years ago

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

#10 Updated by Boone Gorges over 3 years ago

  • Target version changed from 1.13 to 1.14

I've released the new version of Anthologize and it's in the Commons codebase in https://github.com/cuny-academic-commons/cac/commit/03ca6e3514cbd4323e070927e6686a5f6a462d65

I am going to bump this ticket to 1.14 for further plugin review.

#11 Updated by Boone Gorges about 3 years ago

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.

#12 Updated by Boone Gorges about 3 years ago

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.

#13 Updated by Boone Gorges almost 3 years ago

  • Status changed from New to Resolved

This has been running a while and is looking good, so let's close the ticket out.

Also available in: Atom PDF