PHP and MariaDB settings as a site grows beyond the OS default values?

PHP and MariaDB settings as a site grows beyond the OS default values?

by David M -
Number of replies: 6
Our Moodle site (Moodle 3.9) is gradually getting larger, and recently we have started to have issues with backups of some of the larger courses (both the daily automated backups, and backups made manually by course admins) failing.

We have a good understanding of most of the PHP config settings that often need to be changed as a site gets larger:

  • max_execution_time
  • post_max_size
  • upload_max_filesize
(the two size settings needing to be at least as large as the largest file that you will need to upload)

There is also memory_limit, which we have also recently increased from the default 128M to (arbitrarily) 512M, which may also have helped, but we are unsure how to guesstimate a sensible value for this (presumably you do not want to make this too large, even if you have quite a lot of RAM, as there are likely to be a number of Apache/PHP processes running at the same time, depending on number of users, which will all add up and would have the total system RAM shared between them?). Does this setting also need to be at least as large as the largest file upload, or does it depend on other things instead?

Regarding the DB config (in our case, MariaDB), we are aware that max_allowed_packet may now be a limiting factor. Until now, our DB has been using the default 16M value. Should/must this setting also be increased in lockstep with the PHP post/filesize limits, or is it not actually related to those settings? (I am assuming that Moodle stores all uploaded files as files in the filesystem, and not within the database, so there may be no or little correlation between these settings?)

Might we also need to increase max_connections from the default (and slightly odd) 151?

Our server stats are as follows: we don't know exactly how many students are using the server at the same time, but we think it will be something between 100 - 500 students at a given time, from a larger total number of enrolled users (we will obviously need to look into this further, now that use is growing more); we have 16 GB RAM but rarely seem to use more than­ 1 – 2 GB (possibly because of the current PHP/DB limits), 4 CPUs (rarely at more than ~20% load), and plenty of file storage space.

Thank you if you can offer any advice.
Average of ratings: -
In reply to David M

Re: PHP and MariaDB settings as a site grows beyond the OS default values?

by Ken Task -
Picture of Particularly helpful Moodlers
Fact that max_connections is default 151 is kinda an indication DB server config has had little attention ... until now. :|

Strongly suggest you install MySQLTuner on both your test system and production.  It can be run on the production server while it's in use, just not at prime time.

It's a perl script.  Please see: https://github.com/major/MySQLTuner-perl

In particular ...
max connections 151 is the default.  The extra 1 is reserved for superuser to
be able to connect to the DB.

Large course backups - auto or manual, max_input_vars increased to at least 5000 should help.  That's a PHP tweak.

Run tuner with super user creds.
Sections to pay attention to:

-------- Storage Engine Statistics
Total fragmented tables

The recommendations section will even give you the mysql client command to defrag.

-------- Performance Metrics
Physical Memory
Max MySQL memory
Other process memory
Maximum reached memory usage
Maximum possible memory usage
Overall possible memory usage with other process exceeded memory
Slow queries
Highest usage of available connections

-------- InnoDB Metrics
InnoDB buffer pool / data size
InnoDB buffer pool instances

and at the end
-------- Recommendations
which you don't have to follow if you don't want to.

On systems that I have administered, I run tuner at least once a week - more often just before the academic session starts ... and after the academic session ends - admins in moodle and teachers are cleaning up etc.

'SoS', Ken

Average of ratings: Useful (3)
In reply to Ken Task

Re: PHP and MariaDB settings as a site grows beyond the OS default values?

by David M -
Thanks (again), Ken. MySQLTuner looks really useful. (As for not yet having changed from the default settings, "If it ain't broke, don't try to fix it" is often (but not always) a good code to live by (until, or ideally just before, the situation when that no longer applies)… 😉)

Here is what MySQLTuner flags up as potential issues:

[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 185M selects)
[!!] Joins performed without indexes: 79769
[!!] Temporary tables created on disk: 29% (317K on disk / 1M total)
[!!] Key buffer used: 18.2% (24M used / 134M cache)
[!!] Cannot calculate MyISAM index size - re-run script as root user
[!!] InnoDB buffer pool / data size: 128.0M/49.1G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 96.0M * 1/128.0M should be equal to 25%
[!!] InnoDB Write Log efficiency: 84.36% (248285 hits/ 294308 total)

and its recommendations:

General recommendations:
    Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Performance schema should be activated for better diagnostics
    Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL
    Consider installing Sys schema from https://github.com/good-dba/mariadb-sys for MariaDB
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    join_buffer_size (> 256.0K, or always use indexes with JOINs)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    performance_schema = ON enable PFS
    innodb_buffer_pool_size (>= 49.1G) if possible.
    innodb_log_file_size should be (=32M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.


(I'm not sure about the 'mariadb.sys' account test that it does, trying to connect to mariadb with this account doesn't work (when initially setting up the DB system, we did run mysql_secure_installation). The DB is also only accessible locally from localhost, not over the network.)

I am a bit wary of changing settings that I don't know what they mean, but from the output, it does sounds as though it may be desirable to change the innodb_buffer_pool_size and innodb_log_file_size settings, at least?

(I should add that after now having increased max_allowed_packet considerably (to be greater than the size of our largest course (I still don't know if it really needs to be (quite) as large as that or not?), backups seem to be taking place without problems again, which is a relief!)
Average of ratings: -
In reply to David M

Re: PHP and MariaDB settings as a site grows beyond the OS default values?

by Ken Task -
Picture of Particularly helpful Moodlers
First, yeah ... had that philosophy in the past ... but if a Moodle is used, due to dynamic nature of the app ... best to try to get ahead of anything *before* it becomes a necessity.   And one can do that to an extent.

One doesn't have to make all the recommended changes ... Tuner even warns saying that.

Did see one ...  Cannot calculate MyISAM index size - re-run script as root user
Did advise to run the script as the super user (ie, root).   So run it again using superuser creds.  Makes a difference!

The biggy:
[!!] InnoDB buffer pool / data size: 128.0M/49.1G
related: innodb_buffer_pool_instances

You have a single server ... right?   So the DB server + databases and the web service are on the same machine.   Balancing act but DB's run *best* when one can fit as much of the DB in memory as possible ... less disk IO/faster is result.

But one may not be able to do 100% of DB in memory ... cause you have to have enough for web server and other operating system things that require, from time to time, memory, etc.   Think the best I've been able to achieve in an all-in-one box was around 68% ... and that was because of how moodle was used ... web services/asynchronous access to the app.

Not so big, but ...
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1

Every access to the DB checks if the client should have access - DNS wise ... even with localhost config.   DNS queries slow things down a little.

It will never be perfect!

So it didn't mention that any tables needed optimization?
Storage Engine Statistics

And what of: Aborted connections:

You said before your server sees from 100 to 500 concurrent users.  With max_connections set to default 151 there should have been some complaints.

mysql> show variables like 'max_connections';

'SoS', Ken




Average of ratings: Useful (2)
In reply to David M

Re: PHP and MariaDB settings as a site grows beyond the OS default values?

by Ken Task -
Picture of Particularly helpful Moodlers
A follow up ...
Run tuner with superuser creds, what did your server show under section at top:

-------- Performance Metrics
[--] Physical Memory
[--] Max MySQL memory

and just below that:

Maximum reached memory usage: xG (% of installed RAM)
Maximum possible memory usage: xG (% of installed RAM)
Overall possible memory usage with other process exceeded memory

max_allowed_packet
"to be greater than the size of our largest course"
How did you determine the size of the largest course - from size of backup file?

Probably doesn't need to be that large.

You'd have to uncompress that backup, then look at the .xml files for tags that
had 'embedded' content - that piece would be a large chunk of data .... thus affecting max_allowed_packet.

From a live system
du -h ./backup-moodle2-course-99-geo-20220729-2026-nu.mbz
Note that's a no user backup file.
2.8G in size ... and that's compressed.

on same live server have:
max_input_vars = 5000 in php.ini
and in db settings
show variables like 'max_%';
+----------------------------+----------------------+
| Variable_name              | Value                |
+----------------------------+----------------------+
| max_allowed_packet         | 67108864

No issues with course backups on that instance.

BTW, setting for my sandbox server with mulltiple versions of Moodle, but not used on a daily basis my settings there are not the same as the live production server for a K12 entity that has 'opened flood gates' - bring your own device.

That to say ... no 2 servers will be exactly alike ... not even your cloned dev server from your production server.

'SoS', Ken

Average of ratings: Useful (1)
In reply to Ken Task

Re: PHP and MariaDB settings as a site grows beyond the OS default values?

by David M -
Hi Ken,

Sorry, I posted the output slightly too hastily yesterday, as I had to leave shortly after for a meeting, and I just grepped for all of the warning ("!!") lines. mysqltuner was run as root, and I should have removed the MyISAM ouput, as there are no MyISAM DBs.

No mention of any tables needing optimization, or of aborted connections.

Regarding the memory output:

[--] Physical Memory     : 15.6G
[--] Max MySQL memory    : 856.4M
[--] Other process memory: 0B
[--] Total buffers: 417.0M global + 2.9M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 457.7M (2.86% of installed RAM)
[OK] Maximum possible memory usage: 856.4M (5.35% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available

Regarding max_allowed_packet, yes, I guesstimated a new larger value based on the course backup mbz file sizes (but like you, I have a hunch that it probably doesn't need to be quite as large as that).

Regarding max_input_vars, we are starting to do a bit of experimenting (on our dev server) in preparation for Moodle 4.1 LTS, so we have already factored that new custom setting into our PHP config file, thanks.
Average of ratings: -
In reply to David M

Re: PHP and MariaDB settings as a site grows beyond the OS default values?

by Ken Task -
Picture of Particularly helpful Moodlers
On a stand-alone, all in one (DB server, web service) moodle server, it's a balancing act.

Run top ... and at the top of top, how much swap space is your server using?  As you watch top in realtime, you'll see the heaviest process shoot to the top of the listing from time to time AND it also displays % of memory it's consuming.

Assuming your running apache and that daemon is known to your system as httpd, what does:
ps aux |grep httpd
look like (your server might be apache2)

An [!!] item ... InnoDB buffer pool / data size: 128.0M/49.1G
Recommendations:
innodb_buffer_pool_size (>= 49.1G) if possible.
note it says IF POSSIBLE!

Is it possible with 16G total memory that has to be used by web service and other server processes?

Tuner is reporting that your total database size for the moodle is 49.1 Gig.
yet config for DB is allowing 128M.

Can't fit 49.1 Gig into memory of a 16G box.   That would be 100%++ of memory DB wants to use .... like I said, best I've been able to get is around 60%.

So what are your options?
Increase actual memory of server from 16G to 64G.
If one can't do that, spin up another server that will be dedicated to DB only on same IP segment with at least 50G memory.
Other?

'SoS', Ken

Average of ratings: Useful (1)