Hello,

I have upgraded from Moodle 3.11.x to 4.1, 4.2 and then 4.3 via GIT and PHP from 7.4 to 8.0 and 8.1.

Upgrade was smooth, all conditions and parameters were satisfied. I have just solved some problems with plugins.

But I am sometimes getting this error and Moodle is horribly slow. I have really premium cloud hosting with Maria DB and I am only one user of the Moodle now during holiday. I did a lot of work on upgrade but I see, that I should revert at least on 4.1 or 3.11 branch. I don't know how to deal with this behavior.

Error

Error: Database connection failed

It is possible that the database is overloaded or otherwise not running properly.

The site administrator should also check that the database details have been correctly specified in config.php

Average of ratings: -

Re: Serious database preformace problems after upgrade

by Visvanath Ratnaweera - Monday, 19 August 2024, 9:01 PM

Does MariaDB run on the same server 'localhost'? It is in config.php. Better post the whole database related part of the config.php after screening any sensitive information.

And this happens sporadically? Can you relate it to some event, like heavy traffic or the times when a backup process runs in the background?

Either way, the information requested in Before you post.. read this.. are always useful.

Average of ratings: Useful (1)

Re: Serious database preformace problems after upgrade

by Jiří Procházka - Tuesday, 20 August 2024, 12:01 AM

Hello Visvanath,
Yes, MariaDB is running on the same server ('localhost'). Below is the relevant part of the config.php file related to the database configuration:

php

$CFG->dbtype = 'mariadb';
$CFG->dblibrary = 'native';
$CFG->dbhost = 'localhost';
$CFG->dbname = 'u117824311_wl8lms';
$CFG->dbuser = 'u117824311_wl8lms';
$CFG->dbpass = 'your_db_password'; // hidden for security reasons
$CFG->prefix = 'mdl_';
$CFG->dboptions = array (
'dbpersist' => false,
'dbsocket' => false,
'dbport' => '',
'dbhandlesoptions' => false,
'dbcollation' => 'utf8mb4_czech_ci',
);

Regarding the sporadic outages, they are not tied to any specific event like heavy traffic or backup processes as far as I can tell. The load average is normal most of the time, but Moodle experiences random 503 errors. The error log is clean, and cron jobs are running without failures.

On August 14th, there was a 500 error due to the hosting server running out of disk space. Moodle was in maintenance mode at that time. Could this disk space issue have caused any corruption in the Moodle installation or database? After clearing space, I completed the upgrade without any errors, and all checks passed successfully.

I will check the suggested resources from the forum post you linked, but I would appreciate any additional guidance on what to look for or how to further diagnose this issue.

Server is Litespeed, PHP 8.1, MariaDB 10.6.

Thank you
Jiri

Average of ratings: -

Re: Serious database preformace problems after upgrade

by Ken Task - Tuesday, 20 August 2024, 12:47 AM

Running out of disk space can corrupt a DB ... if lucky, maybe a few tables.

Strongly suggest running the command line only script in code/admin/cli/ called check_database_schema.php

Would also suggest installing MySQLTuner.pl

Run tuner with superuser creds (root) and pay attention to the lines at the top related to memory as well as the recommendations at the bottom of the run.

https://github.com/major/MySQLTuner-perl

and some more 'light reading' (NOT) on DB:

https://www.percona.com/blog/mysql-101-parameters-to-tune-for-mysql-performance/

'SoS', Ken

Average of ratings: Useful (1)

Re: Serious database preformace problems after upgrade

by Jiří Procházka - Tuesday, 20 August 2024, 6:11 AM

Hello Ken,
thank you for the suggestion. Here’s what we have done so far to diagnose the issues which I am able to do:

Database Integrity and Optimization Checks:
We performed an analysis and integrity check on all database tables. No errors or issues were found.

Disk Space Issue Review:
We confirmed that there was no disk space issue during the upgrade, and the database did not run out of space. It has dedicated space.

Running check_database_schema.php:
We ran the check_database_schema.php script from the command line on both our staging and production environments. The script highlighted the following inconsistencies in the organizer module:

organizer
* column 'grade' has incorrect type 'N', expected 'I'
* column 'gradeaggregationmethod' has default '1', expected '0' (I)
* column 'userslotsmin' has default '1', expected '0' (I)
* column 'userslotsmax' has default '1', expected '0' (I)

These issues were found consistently on both installations.

While the schema issues are present in the organizer module, we haven't identified any direct correlation between these and the sporadic database connection errors. We are continuing to monitor the situation and would appreciate any further insights or recommendations you might have on resolving these schema issues or other potential sources of the problem.

We are ok with removing Organizer, it is not used.
The outage did not appear again since I have started my thread and What I did not expect, after turning on the debug, Moodle is faster than before.

Best regards,
Jiri

Average of ratings: -

Re: Serious database preformace problems after upgrade

by Ken Task - Tuesday, 20 August 2024, 7:21 AM

Since that plugin isn't used, deleting it will remove schema checks issues.

How do you "performed an analysis and integrity check"?

Find any tables in need of optimizing? If so and that was done, that could account for appearing to be faster.

It has dedicated space. ... does that mean it's a dedicated DB server? If so, it's always been a recommendation to turn off 'networking' - has to do with DNS lookups on every query by a client (moodle code is a client). And, if you install MySQLTuner, run it from both servers - web server (involves networking) and also from DB server itself. You will get different recommendations. Tuner, BTW, should be compat with MariaDB.

Also, you've mentioned MariaDB. Does it now have TLS like MySQL 8?

'SoS', Ken

Average of ratings: Useful (1)

Re: Serious database preformace problems after upgrade

by Jiří Procházka - Wednesday, 21 August 2024, 12:58 AM

Hello Ken,
The Organizer plugin will be removed today, as it is not actively used. This should resolve the schema check issues we identified.

We performed an Analyze and Check operation on all tables using phpMyAdmin. All tables returned "OK" without any detected issues.
I haven’t performed a table optimization yet, given that we're using InnoDB. I understand that optimization may have limited impact with InnoDB, but I'm open to further advice on this.

We’re using a managed cloud hosting service without SUDO access. During the incident when disk space was exhausted due to backups, the database itself did not reach its storage limit, so I believe the database integrity remains intact.
The database is running on localhost, but given the cloud environment, I'm uncertain what this implies regarding the server setup and networking.

I haven't used MySQLTuner yet. If it requires SUDO privileges, it may not be possible to run it in our hosting environment. I will check with the hosting provider to see if it's an option.

Our MariaDB version is 10.11.8, and it is configured to use OpenSSL 1.1.1k for TLS connections.

I will test the installation as possible, we can move my topic to the Hardware and optimization forum, because I am not able to reproduce the outage.
I hope the installation and everything is ok and the DB outage was caused by hosting. To clarify, Moodle is on the "shared" hosting, but all websites there are under my maintenance and monitoring.

With best regards,
Jiri

Average of ratings: -

Re: Serious database preformace problems after upgrade

by Ken Task - Wednesday, 21 August 2024, 2:00 AM

'shared hosting' ... any provider ... is not normally good hosting for a moodle ... due to the need for resources of a moodle site. You only get slices of the pie. And as you have already discovered, can't do things one might need to do.

But then you say "managed cloud hosting service" ... managed means the provider does the heavy lifting ... if it's a dedicated server and NOT shared then they should do what you request within technical limits of what you have leased - space/memory/processing power/etc.

All services on one server ... web server + DB server (localhost) is a memory balancing act between web server handling connections and making request and the DB server - it's ability to respond.

Haven't seen phpmyadmin tools but do know MySQLTuner shows important information about the localhost DB server and databases that are significant for config and DB performance.

Clips from a live K12 site 7493 users - 160 courses - using Tuner:

-------- Performance Metrics

[--] Up for: 4d 7h 25m 9s (17M q [46.249 qps], 94K conn, TX: 20G, RX: 2G)
[--] Reads / Writes: 92% / 8%
[--] Binary logging is enabled
* [--] Physical Memory : 31.1G
* [--] Max MySQL memory : 13.8G

[OK] Maximum reached memory usage: 4.6G (14.85% of installed RAM)
[OK] Maximum possible memory usage: 13.8G (44.25% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (20/17M)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
* [OK] InnoDB buffer pool / data size: 4.0G/2.9G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 512.0M * 2/4.0G should be equal to 25%
[OK] InnoDB buffer pool instances: 4

* above is important

Does phpmyadmin give you such infomation?

'all websites' ... on same server?

How many? And what are they? Do you have a WordPress/Moodle mix?

Then there is consideration of clients to your moodle. The above clips came from a K12 entity that sometimes uses the Moodle synchronously ... teacher F-2-F with X number of students who are also using a course in the moodle at the same time.

Your clients? ... asynchronously? ... pop in at any time ... there are no set 'class times' - where instructors/teachers are to be online same time as particiants in a class for whatever purpose ... exception would be zoom (other class meetings).

So how are courses conducted in your Moodle?

'SoS', Ken

Average of ratings: Useful (2)

Re: Serious database preformace problems after upgrade

by Ken Task - Wednesday, 21 August 2024, 6:52 AM

"I haven't used MySQLTuner yet. If it requires SUDO privileges, it may not be possible to run it in our hosting environment."

Here's a dirty secret about every hosting provider I've had access to running localhost DB. User root to the DB has no password.

Try it: from command line located anywhere:

mysql -u root[ENTER]

MySQLTuner is a perl (.pl) script ... as long as server has the perl supports to run it one doesn't need sudo ... wouldn't want it anyway as you want to see all related to DB's and DB server.

With 30 sites ... all of them DB backended, you need to see the total picture.

You got command line so what does:

free -h

df -h

look like?

'SoS'. Ken

Average of ratings: -

Re: Serious database preformace problems after upgrade

by Visvanath Ratnaweera - Wednesday, 21 August 2024, 12:48 PM

Something to try out if '$ mysql -u root' gives only "guest" access is a slight variation: '$mysql -u root -p' and simply press Enter when the password is requested.

Average of ratings: -

Re: Serious database preformace problems after upgrade

by Ken Task - Wednesday, 21 August 2024, 4:13 PM

@Mr. V ... Really???!!! Guest access???!!! Who would do such a thing?

2 other dirty secrets ... command line 'passwordless' access ... a hidden .mycnf file in users account path that contains superuser password.

And if one issues env [ENTER] and look for the PATH variable - if one sees at the tail end of that /home/accountlogin/bin/ one can install things like moosh ... even MySQLTuner.pl if Pearl has the supports for it.

Every system? Maybe not ... but I've found those in many!

And it does make sense for tech support of shared cheapo package leases to set things up that way!

As long as I'm 'jaberwalking' ... one of these days cPanel ... maybe even Plesk people ... will include a 'support account' that is restricted to certain modules of the panel - one of them being shell - just for 3rd party support purposes. Customer in need of assistance could get assitance without 'bothering' under staffed help desk.

'SoS', Ken

Average of ratings: -

Re: Serious database preformace problems after upgrade

by Jiří Procházka - Wednesday, 21 August 2024, 2:42 AM

I have probably new important indication related to running out of space. But this is not related with performance.

The file browser in the main moodle installation (4.3) does not work. I am not able to list and manage files from site administration, for example to see selected course image. On my secondary test installation (4.4) it works. I think this may have cause in running out of space few days ago, because moodle data storage is very sophisticated. It is possible to fix it or should I use nuclear option to roll back all data and database from the backup?

I see only blank textarea box with inactive icons and I am getting error in JS console:

Average of ratings: -

Re: Serious database preformace problems after upgrade

by Ken Task - Wednesday, 21 August 2024, 4:12 AM

There are 26+ FQDN's on that IP address. So you are on shared hosting.

".... main moodle installation ...
secondary test installation ..."

Are those on the same server? or do they have different FQDN's and IP addresses?

"It is possible to fix it or should I use nuclear option to roll back all data and database from the backup?"

What you've shown is a JavaScript issue in talking to Yahoo library.
Not sure what the video was supposed to show ... missing backup files that were supposed to be there?

We don't really know what the problem is so not sure anyone could suggest a fix. Debugging reports nothing?

At this point I'd vote for the nuke option! Assuming that there are no drive issues, you have good backups, and you have the resources to restore the site. And that's a full site restore ... code + DB + moodledata.

'SoS', Ken

Average of ratings: Useful (1)

Re: Serious database preformace problems after upgrade

by Jiří Procházka - Wednesday, 21 August 2024, 5:00 AM

Yes, I have only one hosting with about 30 domains on single dedicated IP address. We are smallest possible high school with 4 classes. We haven't noticed any performance problems since I January, when I have migrated here (Hostinger). I was very satisfied with the performance of Moodle and Wordpress sites.

I have the debug info:
[20-Aug-2024 19:02:55 Europe/Prague] Default exception handler: The required parameter was missing (contextid) Debug:
Error code: missingparam
* line 620 of /lib/moodlelib.php: moodle_exception thrown
* line 31 of /backup/backupfilesedit.php: call to required_param()

Many errors of removed Organizer plugin...

and also later:

[20-Aug-2024 20:59:38 Europe/Prague] Default exception handler: error in program - please inform developer: Invalid json in request: syntax error Debug:
Error code: codingerror
* line 65 of /lib/ajax/service.php: coding_exception thrown

[20-Aug-2024 20:59:39 Europe/Prague] REST exception handler: invalid token - token not found Debug:
Error code: invalidtoken
* line 1160 of /webservice/lib.php: moodle_exception thrown
* line 1054 of /webservice/lib.php: call to webservice_server->authenticate_by_token()
* line 1312 of /webservice/lib.php: call to webservice_server->authenticate_user()
* line 44 of /webservice/rest/server.php: call to webservice_base_server->run()

I will do the backup, try to upgrade to 4.4 and if the problems will not solved, I will restore one week old backup before the disk space issue.
Thank you for your help.

Average of ratings: -

Re: Serious database preformace problems after upgrade

by Ken Task - Wednesday, 21 August 2024, 6:02 AM

Let's start here:

"Many errors of removed Organizer plugin"

Sounds like it wasn't removed properly.

Code + related DB tables for that plugin must be removed.

Try using code/admin/cli/uninstall_plugins.php script.

First clip of error you shared looks like when you were trying to restore a backup.

Wonder if the other 2 clips were related to you or someone trying to access site from a 'smart device' - cell phone.

"I have only one hosting with about 30 domains on single dedicated IP address."

Memory/Space? Pushing limits of resources? Even a 'small moodle' could require more than you think ... kinda depends upon how it's being used.

'SoS', Ken

Average of ratings: -

Re: Serious database preformace problems after upgrade

by Jiří Procházka - Monday, 26 August 2024, 8:03 PM

Restore is was successfully done. I have restored only the version 4.1 3 days back. Fire browser module is working. I cannot confirm that file browser issue is related to the disk space outage, because I id not have to restore older backup. Sorry fo this subthread, this is solved and I will focus only for the DB outages, which are still randomly but rarely appearing.

Average of ratings: -

Re: Serious database preformace problems after upgrade

by Jiří Procházka - Wednesday, 21 August 2024, 9:27 PM

I see, that this topic has been discussed here by Visvanath:
https://moodle.org/mod/forum/discuss.php?d=366360

But probably the restore was the solution. I am just lunching restore now.
I was not able to debug the cause of the error in JS.

Average of ratings: -

Re: Serious database preformace problems after upgrade

by Visvanath Ratnaweera - Thursday, 22 August 2024, 2:22 AM

Do you mean the problem was something spurious like a stale cache or other browser phenomena and never returned? Yep, nothing against marking the discussion as closed.

Average of ratings: -

Re: Serious database preformace problems after upgrade

by Visvanath Ratnaweera - Tuesday, 20 August 2024, 2:04 AM

About the $CFG->db* settings: The only thing I notice is the 'dbcollation' => 'utf8mb4_czech_ci'. utf8mb4_unicode_ci is advised. But that can not be the cause of sporadic errors. Still worth checking the Administration via command line#Converting character set and collation. Your config.php file looks like a shared hosting. Hope, you have CLI access.

5xx errors come from the web server - most of the time. So it is logical that Moodle doesn't log them. You need to search the web server error logs.

About the "resources" I linked: They happen to be called "Before you post.. Read this.."!

Average of ratings: Useful (1)

Re: Serious database preformace problems after upgrade

by Jiří Procházka - Wednesday, 21 August 2024, 5:50 AM

Thank you for your notice about the collation. I specifically chose utf8mb4_czech_ci to ensure proper sorting and handling of Czech language characters, especially for names and other text fields where Czech alphabetical rules are important.

Given this context, is it problematic to maintain this collation setting in Moodle, or would you still recommend switching to utf8mb4_unicode_ci? I want to ensure that the system continues to handle Czech-specific sorting correctly without introducing other issues.

As for the other troubleshooting steps, I am from the beginning investigating the causes on my web hosting thoroughly and cooperating with the support team. I manage all my WP websites and Moodle installations via the CLI. Now, I see much more in the logs, which were previously flooded by errors from problematic plugins (marked as compatible with 4.3). Step by step, I am on the good way.

Average of ratings: -

Re: Serious database preformace problems after upgrade

by Howard Miller - Monday, 19 August 2024, 9:30 PM

I very much doubt that this is related to the version of Moodle. The database layer doesn't change much.

Firstly, turn on Debugging and get the full trace when you get that error. What is the database doing when it fails ('show processlist' at the very least)?

Average of ratings: Useful (1)

Re: Serious database preformace problems after upgrade

by Jiří Procházka - Tuesday, 20 August 2024, 12:04 AM

Hello Howard,
thank you for your response.

I understand that the database layer in Moodle hasn't changed much, so it's less likely that the version upgrade itself is the root cause. However, I have been encountering sporadic database connection issues and occasional 503 errors since the upgrade.

I already had Debugging turned on during the entire upgrade process, but I didn’t capture any errors specifically related to the database. I have now enabled detailed Debugging again and will monitor the full trace the next time the issue occurs.

As for the database, I've run SHOW PROCESSLIST when these issues arise, and the results don't indicate any obvious long-running queries or locked processes. The processlist typically shows processes in the Sleep state or quick-running queries, even during these outages.

Given the sporadic nature of the issue and the lack of any clear error messages or process locks, I’m wondering if there could be other underlying issues such as a misconfiguration or a hardware resource problem that might only manifest under specific conditions.

Do you have any suggestions on specific configurations to check or additional diagnostics to run in this case?

Thank you for your support.

Best regards,
Jiri

Average of ratings: -

Re: Serious database preformace problems after upgrade

by Howard Miller - Tuesday, 20 August 2024, 4:06 AM

Just a random thought - you've not set up database sessions have you?

Average of ratings: Useful (2)

Re: Serious database preformace problems after upgrade

by Jiří Procházka - Tuesday, 20 August 2024, 6:17 AM

Hallo Howard,

that’s an interesting suggestion. While we haven't set up database sessions, we do have Memcached available on our server. Given that, we’re considering switching to Memcached for session handling to see if it alleviates the issues we’ve been experiencing.

The relevant section from our config.php, which is currently commented out.

Thank you for the insight!

Best regards,
Jiri

Average of ratings: -

Re: Serious database preformace problems after upgrade

by Howard Miller - Tuesday, 20 August 2024, 2:55 PM

Just to be clear... I meant DO NOT use the database for sessions

Memcached is a bit "old school". If you have a choice, use Redis. I would definitely switch to a proper cache (for caching, obviously I hope) and for sessions if you have the option. Do it anyway!

Average of ratings: Useful (2)

Re: Serious database preformace problems after upgrade

by Jiří Procházka - Tuesday, 20 August 2024, 11:55 PM

Ok thanks, I understand your message wrong way

I have no Redis, I am using Litespeed cache on the other websites, but I will check if it is supported by Moodle.

Average of ratings: -

Re: Serious database preformace problems after upgrade

by Howard Miller - Wednesday, 21 August 2024, 3:42 AM

I thought that LSCache was an entirely different beast - for caching web pages. Memcached, Redis etc. are "simple" name/value caches in essence.

I could, as usual, be completely wrong.

Average of ratings: -

Re: Serious database preformace problems after upgrade

by Jiří Procházka - Wednesday, 21 August 2024, 5:30 AM

You're right—LSCache is different from Memcached and Redis

LSCache can work with Memcached or Redis to cache data and improve server performance on LiteSpeed server.

Average of ratings: -

Re: Serious database preformace problems after upgrade

by Visvanath Ratnaweera - Tuesday, 20 August 2024, 11:51 AM

You might want to go through the starting points in the case of performance problems in the documentation: Performance and Performance FAQ.

There is a dedicated forum on Hardware and performance. This discussion might be ultimately moved to that forum. Normally it is a good idea to search the discussions there. But since timing has no correlation to anything, a systematic search is not possible.

Average of ratings: Useful (1)