Hi,
At ULPGC we are experiencing some problems with the database. I am a teacher, not a sysadmin and have no experience with database performance.
We are having what I see as "a lot" of incidences due to corruption of database tables. Almost one per week, sometimes affecting several tables. Is this rate normal? We have Moodle 1.4.3 (2004083132) installed in a server with RH 8.1, Apache 2.0.46, MySQL 3.23.58 and PHP 4.3.2. Our site is growing big, over 20.000 users, and with some courses enrolling all users.
Those episodes of table corruption can have significant results. For instance, all events displayed in the Calendar disappearing. Once the "News" forum replicated itself on every page loading, due to News block not able to read the table. Teachers phoned alarmed because a virus was replicating in the system. We have not experienced any data loss for the moment, and all have got fixed with a "repair". But those episodes scared people, and may send a message of Moodle as an "unstable" or "immature".
I am asking here advice from experienced database administrators. What kind of measures we may adopt to prevent, or at least detect early these faults?.
I have speculated with an script to check all tables in Moodle are running well. Something like a simple SQL statement that should return a known set of data, if not, there is something wrong with the table. Can repair be run on a live database table? Is it possible to run a "repair" every night, with cron?
At least, such a script would indicate to the human admin every morning if there is any faulty table.
Are there any other pro-active measures we may take?
TIA,
- Enrique -
In reply to Enrique Castro
Re: Databases: Any measures against MySQL corruption?
by Zbigniew Fiedorowicz -
I've noticed that in recent weeks moodle.org has been experiencing some problems. About once a week I notice brief outages here, with error messages indicating that moodle could not connect to the database server. In one case there was also some database corruption.
I also notice that the NZVLE project is using PostgreSQL rather than MySQL. Do they think the former is more reliable for heavy use?
I also notice that the NZVLE project is using PostgreSQL rather than MySQL. Do they think the former is more reliable for heavy use?
In reply to Zbigniew Fiedorowicz
Re: Databases: Any measures against MySQL corruption?
by Martin Dougiamas -
MySQL wasn't to blame for any of these though, it's just a symptom of other problems I was having.
The problem in recent weeks has been because I've been moving stuff off this RHE server to a new Debian server and changing the way backups work. I have Cpanel/WHM on this machine, though, which is a bit smart about self-repairing and without me knowing it it had been recreating the old CPU-heavy backup jobs again. The time it picked was a bad one - right at the same time other work was happening! The resultant overload was causing severe slowdown, which caused processing to pile up, which caused MySQL process limits to be reached, which causes those error messages. I think I've nailed Cpanel to the floor now though.
My best advice for keeping MySQL smooth is to shut it down properly, and if you don't, make sure you run a REPAIR process over all the tables.
We've talked before about putting some regular automatic database maintenance (like REPAIR) into Moodle itself and I think I'll do this now.
The problem in recent weeks has been because I've been moving stuff off this RHE server to a new Debian server and changing the way backups work. I have Cpanel/WHM on this machine, though, which is a bit smart about self-repairing and without me knowing it it had been recreating the old CPU-heavy backup jobs again. The time it picked was a bad one - right at the same time other work was happening! The resultant overload was causing severe slowdown, which caused processing to pile up, which caused MySQL process limits to be reached, which causes those error messages. I think I've nailed Cpanel to the floor now though.
My best advice for keeping MySQL smooth is to shut it down properly, and if you don't, make sure you run a REPAIR process over all the tables.
We've talked before about putting some regular automatic database maintenance (like REPAIR) into Moodle itself and I think I'll do this now.
In reply to Martin Dougiamas
Re: Databases: Any measures against MySQL corruption?
by Martin Dougiamas -
Naturally, having mentioned it there was a bad downage again this afternoon (while I was out!) ... I may have been wrong about the backups being to blame ... something else seems to be happening here. I'm trying to sort it out.
In reply to Martin Dougiamas
Re: Databases: Any measures against MySQL corruption?
by Martín Langhoff -
What's the plan for this "automatic db maintenance"?
In reply to Martín Langhoff
Re: Databases: Any measures against MySQL corruption?
by Martin Dougiamas -
I was thinking a simple setting in config where you set the time of day (or "never"), and then once a day some database specific SQL would be run. For MySQL it would just be something like this for every table:
REPAIR TABLE assignment
What would be some good commands for PostgreSQL?
REPAIR TABLE assignment
What would be some good commands for PostgreSQL?
In reply to Martin Dougiamas
Re: Databases: Any measures against MySQL corruption?
by Martín Langhoff -
Hmmm. I've never needed one and... there isn't one as far as I know. We have been running some major systems on Postgres here at Catalyst, stuff you just wouldn't want to get wrong, and we haven't seen a need for repairing tables. Postgres is ACID, so when you commit, it doesn't return until everything is on-disk and well stored.
There's vacuum, but Moodle shouldn't run that. If anyone wants it to happen automagically, you tell Postgres and it does it for you.
There's vacuum, but Moodle shouldn't run that. If anyone wants it to happen automagically, you tell Postgres and it does it for you.
In reply to Martín Langhoff
Re: Databases: Any measures against MySQL corruption?
by Martin Dougiamas -
Ah yes, VACUUM, that was the command I was trying to remember ...
A customer of mine who uses Postgres was recently having severe performance problems even though they were runing a "normal" vacuum every day. I was poring through dbperformance.php trying to track the SQL that was causing it (and actually found some SQL I could improve! but it didn't help).
In the end we discovered that he needed to do a FULL vacuum ... that fixed it up! If anyone's having performance problems (not corruption problems) with Postgres, give that a try!
How do you tell Postgres to run this automagically? Should Moodle be running a full vacuum occasionally when it knows it isn't busy (because it locks the tables).
A customer of mine who uses Postgres was recently having severe performance problems even though they were runing a "normal" vacuum every day. I was poring through dbperformance.php trying to track the SQL that was causing it (and actually found some SQL I could improve! but it didn't help).
In the end we discovered that he needed to do a FULL vacuum ... that fixed it up! If anyone's having performance problems (not corruption problems) with Postgres, give that a try!
How do you tell Postgres to run this automagically? Should Moodle be running a full vacuum occasionally when it knows it isn't busy (because it locks the tables).
In reply to Martin Dougiamas
Re: Databases: Any measures against MySQL corruption?
by Martín Langhoff -
You usually have the "autovacuum" daemon running alongside Postgres. AFAIK, it collects stats from Postmaster and runs VACUUM/VACUUM ANALYZE/VACUUM FULL according to some policies of its own.
Our sysadmins here tend to switch it off and run a cronjob at known times instead, to avoid autovacuum running at unexpected times. Which reminds me: any repair/vacuum feature we do in Moodle needs to be easy for sysadmins to disable.
If I think about it, Moodle depends on the DB enough that trying to run a table-locking VACUUM FULL interactively could end up not working -- HTTP timeouts and session locking problems. Cron.php perhaps?
Our sysadmins here tend to switch it off and run a cronjob at known times instead, to avoid autovacuum running at unexpected times. Which reminds me: any repair/vacuum feature we do in Moodle needs to be easy for sysadmins to disable.
If I think about it, Moodle depends on the DB enough that trying to run a table-locking VACUUM FULL interactively could end up not working -- HTTP timeouts and session locking problems. Cron.php perhaps?
In reply to Zbigniew Fiedorowicz
Re: Databases: Any measures against MySQL corruption?
by Martín Langhoff -
Both databases are extremely reliable. MySQL is somewhat more forgiving, but for heavy use you need to tune them carefully.
For really heavy use, I tend to trust Postgres more. And if you put your indexes in the right place, and write your SQL reasonably well, it scales a lot better when you are dealing with millions of records. And small to medium installations, Postgres may be more trouble than it's worth ;)
Having said that, I hope MD finds what's messing with his DB. When you are dealing with lots of traffic, relatively small things can hamper DB performance and really ruin your day.
For really heavy use, I tend to trust Postgres more. And if you put your indexes in the right place, and write your SQL reasonably well, it scales a lot better when you are dealing with millions of records. And small to medium installations, Postgres may be more trouble than it's worth ;)
Having said that, I hope MD finds what's messing with his DB. When you are dealing with lots of traffic, relatively small things can hamper DB performance and really ruin your day.
In reply to Martín Langhoff
Re: Databases: Any measures against MySQL corruption?
by Enrique Castro -
Hi "the other Moodle Martin",
Everybody says MySQL and postgreSQL are extremely reliable. But most of Spanish universities have their student management systems on Oracle. Database "gurus" at my University are quite pride or their Oracle expertise and tend to dismiss MySQL. What I need in order to argue back with them is an estimation of this rate of table corruption. Is it normal to have some tables corrupted every month?
And going to preventive measures, from MD response, can I infer that we can perform a "repair" on every table of the database automated with the cron at, say 04:00 in the morning?
- Enrique -
Everybody says MySQL and postgreSQL are extremely reliable. But most of Spanish universities have their student management systems on Oracle. Database "gurus" at my University are quite pride or their Oracle expertise and tend to dismiss MySQL. What I need in order to argue back with them is an estimation of this rate of table corruption. Is it normal to have some tables corrupted every month?
And going to preventive measures, from MD response, can I infer that we can perform a "repair" on every table of the database automated with the cron at, say 04:00 in the morning?
- Enrique -
In reply to Enrique Castro
Re: Databases: Any measures against MySQL corruption?
by Martín Langhoff -
No, table corruption on MySQL is not normal. There's always a cause for it, and as soon as you remove the source of the problem, you should never see it again. Well, perhaps every two or three years some cosmic ray...
In short... get a mySQL guru. A single Oracle install usually needs 3 or more Oracle-trained DBAs; with MySQL you don't need that kind of show, but it pays to get someone with a good understanding of MySQL to spend a few hours on it and fix it for good.
Fixing it in on a cronjob is a way to hide the problem. It doesn't work in the long run. There must be some script locking the database, or someone/something killing mysql processes. In my experience, it always boils down to one of those.
In short... get a mySQL guru. A single Oracle install usually needs 3 or more Oracle-trained DBAs; with MySQL you don't need that kind of show, but it pays to get someone with a good understanding of MySQL to spend a few hours on it and fix it for good.
Fixing it in on a cronjob is a way to hide the problem. It doesn't work in the long run. There must be some script locking the database, or someone/something killing mysql processes. In my experience, it always boils down to one of those.
In reply to Enrique Castro
Re: Databases: Any measures against MySQL corruption?
by Martin Dougiamas -
I agree with Martin L that such errors are always the cause of badly-behaved software that shuts down mysql uncleanly or keeps processes running. MySQL itself is very stable.
As a bit of bandaid, you can easily run mysqlcheck as a shell command to do a regular sweep for small corruptions and catch them before anyone notices, eg:
mysqlcheck --repair --all-databases --fast
You can use cron to run something like this regularly if you like.
I know there is something similar to this for PostgreSQL too ...
As a bit of bandaid, you can easily run mysqlcheck as a shell command to do a regular sweep for small corruptions and catch them before anyone notices, eg:
mysqlcheck --repair --all-databases --fast
You can use cron to run something like this regularly if you like.
I know there is something similar to this for PostgreSQL too ...
In reply to Martin Dougiamas
Re: Databases: Any measures against MySQL corruption?
by Enrique Castro -
OK, thanks to both Martins
Now I have better undertansting and feel that I can argue with IT staff with someting solid beneath my feet. I have the impression that in the end those problems may trace to human action: rapid hacks, just "do it quick& dirt" and all that stuff. Our Moodle installation has been changed the physical server, the OS and libraries several times. I will try to setup a more rigid policy around mySQL.
Here our DB guys are Oracle experts (you are right Oracle needs a team). This closed team sees the Oracle database as an untouchable treasure. No other application but theirs has direct interaction (even read-only) with Oracle. They serve data as files upon request. At teh same time, I think they feel mySQl is a "toy" you can move, mess and kick without worries. In the end, it's not serious business, if it were it will use Oracle.
- Enrique -
Now I have better undertansting and feel that I can argue with IT staff with someting solid beneath my feet. I have the impression that in the end those problems may trace to human action: rapid hacks, just "do it quick& dirt" and all that stuff. Our Moodle installation has been changed the physical server, the OS and libraries several times. I will try to setup a more rigid policy around mySQL.
Here our DB guys are Oracle experts (you are right Oracle needs a team). This closed team sees the Oracle database as an untouchable treasure. No other application but theirs has direct interaction (even read-only) with Oracle. They serve data as files upon request. At teh same time, I think they feel mySQl is a "toy" you can move, mess and kick without worries. In the end, it's not serious business, if it were it will use Oracle.
- Enrique -
In reply to Martin Dougiamas
Re: Databases: Any measures against MySQL corruption?
by Martín Langhoff -
For Postgres, just stop it and start it. If it thinks the data is inconsistent, it'll discard the incomplete commits, perhaps replay part of it's journal, and that's it.
I was a bit surprised when I realized we don't have a repair tool. But the truth is that I've done a lot of messing about with postgres, including unclean shutdowns while running big jobs (on my dev box, naturally) and it's never shown any signs of corruption.
The commandline pg_filedump can be used, too. But needing to use pg_filedump is invariably an indication of a bad disk drive (or some crazy sysadmin running the disk or Postgres async).
I was a bit surprised when I realized we don't have a repair tool. But the truth is that I've done a lot of messing about with postgres, including unclean shutdowns while running big jobs (on my dev box, naturally) and it's never shown any signs of corruption.
The commandline pg_filedump can be used, too. But needing to use pg_filedump is invariably an indication of a bad disk drive (or some crazy sysadmin running the disk or Postgres async).