Hardware and performance

Here's a method to provide highly-available session storage for your users.  

It's well known that keeping Moodle sessions in the database can be a performance bottleneck.  Moreover, it can lead to unintentional denial of service because of MDL-42435. Someone falls asleep with their nose on the F5 key and uses up all the available database connections, locking everyone else out of your site (you included).

Memcache is the go-to session storage mechanism, but using a naked memcached server for session storage is a rotten way to treat your users.  If you evict because you're out of memory, your oldest session is destroyed; and every time you restart your memcached daemon, everyone gets logged out. 

An answer, if you're using MySQL 5.6 or newer, is to use the InnoDB Memcached Plugin.  This exposes a memcached interface on TCP port 11211, but uses InnoDB for persistent storage.  User session state is preserved following restarts of your MySQL server, and you can make it part of your replication topology to provide resilience in the event of hardware failure.

How to install

Add the following text to a temporary file, e.g. /tmp/innodb_memcached_moodle.sql

CREATE DATABASE IF NOT EXISTS innodb_memcache;
USE innodb_memcache;
CREATE TABLE IF NOT EXISTS `cache_policies` (
  `policy_name` varchar(40) NOT NULL,
  `get_policy` enum('innodb_only','cache_only','caching','disabled') NOT NULL,
  `set_policy` enum('innodb_only','cache_only','caching','disabled') NOT NULL,
  `delete_policy` enum('innodb_only','cache_only','caching','disabled') NOT NULL,
  `flush_policy` enum('innodb_only','cache_only','caching','disabled') NOT NULL,
  PRIMARY KEY (`policy_name`)
) ENGINE=InnoDB;
INSERT INTO `cache_policies` VALUES ('cache_policy','innodb_only','innodb_only','innodb_only','innodb_only');
CREATE TABLE IF NOT EXISTS `config_options` (
  `name` varchar(50) NOT NULL,
  `value` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB;
INSERT INTO `config_options` VALUES ('separator','|'),('table_map_delimiter','.');
CREATE TABLE IF NOT EXISTS `containers` (
  `name` varchar(50) NOT NULL,
  `db_schema` varchar(250) NOT NULL,
  `db_table` varchar(250) NOT NULL,
  `key_columns` varchar(250) NOT NULL,
  `value_columns` varchar(250) DEFAULT NULL,
  `flags` varchar(250) NOT NULL DEFAULT '0',
  `cas_column` varchar(250) DEFAULT NULL,
  `expire_time_column` varchar(250) DEFAULT NULL,
  `unique_idx_name_on_key` varchar(250) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB;
INSERT INTO `containers` VALUES ('default','moodlesessions','sessions','key','sessdata','0','0','0','PRIMARY');
CREATE DATABASE IF NOT EXISTS moodlesessions DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;
USE moodlesessions;
CREATE TABLE IF NOT EXISTS `sessions` (
  `key` varchar(128) NOT NULL DEFAULT '',
  `sessdata` longtext,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now log in to MySQL as root, prepare the tables for memcached and activate the plug-in:

mysql> source /tmp/innodb_memcached_moodle.sql
mysql> install plugin daemon_memcached soname "libmemcached.so";

Exit MySQL and check that mysqld is listening on TCP 11211. In your firewall, ensure TCP port 11211 is available to the Moodle web servers (and only to them; memcached has no security).

Your MySQL database is now ready to act as a session store for Moodle.

All the above can be done without an outage.

Be aware that mysqld will now be listening on 0.0.0.0:11211. If you want to bind it to a specific address, you need to use the -l option in my.cnf, e.g.

daemon_memcached_option = '-l192.168.0.1'

Configure your web servers

This cannot be done without trashing all the existing Moodle user sessions, so schedule downtime for this:

Add the following to config.php on each app server:

$CFG->session_handler_class = '\core\session\memcached';
$CFG->session_memcached_save_path = '<ip_of_moodle_db_server>:11211';
$CFG->session_memcached_prefix = 'memc.sess.key.';
$CFG->session_memcached_acquire_lock_timeout = 120;
$CFG->session_memcached_lock_expire = 7200;       // Ignored if memcached extension <= 2.1.0

Ensure you change "ip_of_moodle_db_server" to an IP address!

Restart your web server or PHP-FPM daemon, and release the application to the users.