optimizing mysql for Moodle

Following

https://docs.moodle.org/405/en/Performance_recommendations#MySQL_Performance

That page pointed to
https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html

SELECT @@innodb_buffer_pool_size/1024/1024/1024;
+------------------------------------------+
| @@innodb_buffer_pool_size/1024/1024/1024 |
+------------------------------------------+
|                           0.125000000000 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> \q

Can increase at least to 2G.

https://stackoverflow.com/questions/19534144/how-to-set-global-innodb-buffer-pool-size
mysql -u root -p
SET GLOBAL innodb_buffer_pool_size=2G;
ERROR 1232 (42000): Incorrect argument type to variable 'innodb_buffer_pool_size'

https://convertlive.com/u/convert/gigabytes/to/bytes#2

SET GLOBAL innodb_buffer_pool_size=2147483648;
Query OK, 0 rows affected (0.01 sec)

Running mysqltuner
https://www.linode.com/docs/guides/how-to-optimize-mysql-performance-using-mysqltuner/

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Check warning line(s) in /var/log/mysql/error.log file
    Check error line(s) in /var/log/mysql/error.log file
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=ON
    We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
             See https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_join_buffer_size
    Be careful, increasing innodb_redo_log_capacity means higher crash recovery mean time
Variables to adjust:
    skip-name-resolve=ON
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    table_definition_cache (2000) > 3098 or -1 (autosizing if supported)
    innodb_buffer_pool_size (>= 12.4G) if possible.
    innodb_redo_log_capacity should be (=512M) if possible, so InnoDB Redo log Capacity equals 25% of buffer pool size.
    innodb_buffer_pool_instances(=2)
    innodb_log_buffer_size (> 16M)

So, on our Ubuntu 24.04 server,

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

skip-name-resolve=ON is not found
    join_buffer_size (> 256.0K, or always use indexes with JOINs) is not found

Tried editing my.cnf directly and restarted.
But restart failed. Need to enter with spaces between = ?
innodb_buffer_pool_size = 2G
This change worked OK on restarting mysql.

innodb_buffer_pool_size         = 2G
#join_buffer_size               = 256.0K this crashes.
innodb_redo_log_capacity        = 512M
innodb_buffer_pool_instances    = 2
innodb_log_buffer_size          = 16M

The above worked. Not doing the other recommendations, as a mysql restart crashes.

Some more recommendations, which we have not done:

Consider using postgresql instead.

Optimize your tables weekly and after upgrading Moodle. It is good practice to also optimize your tables after performing a large data deletion exercise, e.g. at the end of your semester or academic year. This will ensure that index files are up to date. Backup your database first and then use:
mysql>CHECK TABLE mdl_tablename;
mysql>OPTIMIZE TABLE mdl_tablename;

    The common tables in Moodle to check are mdl_course_sections, mdl_forum_posts, mdl_log and mdl_sessions (if using dbsessions). Any errors need to be corrected using REPAIR TABLE (see the MySQL manual and this forum script).

    Maintain the key distribution. Every month or so it is a good idea to stop the mysql server and run these myisamchk commands.

#myisamchk -a -S /pathtomysql/data/moodledir/*.MYI

    Warning: You must stop the mysql database process (mysqld) before running any myisamchk command. If you do not, you risk data loss.