Forum is on MySQL. Should be on PostgreSQL for maximum robustness.

[kcbrown]

(Note: be sure to read the bottom.  The following suggestion is now obsolete in light of it)

When the forum was on its way back up and I went to view one of the threads, I got back a "table is marked as crashed and should be repaired" error.

That error is issued by MySQL, and is issued when the table in question is a MyISAM table.

MyISAM tables are nontransactional, and are the least robust way to store data in a database.

When examining the forum software's documentation, I found the requirements for it, which includes which databases are supported.  Those requirements are here: http://wiki.simplemachines.org/smf/SMF2.0:Requirements_and_recommendations

PostgreSQL is one of the database engines that the forum software supports.  It is the most robust database engine in the list of supported database backends.  At the company I work for, we have a product that uses MySQL as the backend and another that uses PostgreSQL as the backend.  Despite the latter backend being under substantially heavier loads, not once have I ever heard of it losing any data, while MySQL occasionally does even though it's using one of the most robust forms of table that MySQL supplies (InnoDB).

And based on the error message I got from this forum today, it appears that this forum is using the least robust form of table that MySQL makes available for at least some of what it does.

Given that this isn't the first time the database has given this forum trouble, I strongly suggest that the content be moved to PostgreSQL, especially if future growth of the forum is expected (PostgreSQL generally scales well with load).



EDITED TO ADD: Well, it seems that despite the claims in SMF's documentation, PostgreSQL support is actually rather poor in SMF, apparently almost an afterthought.  So in light of that, I can't actually in good conscience continue to support my above suggestion.  The only alternatives are going to be things like using the "data=journal" mount option to ext3/ext4, and using a UPS on the server to minimize the chance that it goes down due to power failure.  And obviously, the "data=journal" option would have to be tested because it is likely to have an effect on the write performance of the filesystem.

If it weren't for the fact that the schema creation for SMF explicitly specifies the MyISAM table format for all the tables, I might have recommended at least switching them to the InnoDB table type.  But since MyISAM is explicitly specified in the schema, I suspect that switching the table type may not be feasible.  I'll have to do some further research on that.

[nctnico]

If the forum is using MyISAM tables then the admin should get a real hard slap in the face  . Nobody in their right mind would use MyISAM tables nowadays (and in the last 15 years) due to their inherent instability!
MyISAM was a great invention for a data retrieval system with an SQL frontend (aka MySQL) to create websites with mostly static content in a database but for handling a decent amount of writes MyISAM is totally unsuitable because there is basically zero fault tolerance.

I strongly second the suggestion for Postgresql!

[T3sl4co1l]

I suspect you'll have a hard time getting Dave to make a change though, unless you can provide an exact, simple, step-by-step method to convert.  This doesn't sound like a simple thing.

Tim

[mariush]

A lot of prducts have databases that are mixed, some tables are using innodb and some are myisam.

Innodb is slower and safer, transactional etc .. myisam is faster for full text search and just storing stuff that doesn't change often. 

Perhaps one of those myisam tables got corrupted, which was fixed in the meantime.

[kcbrown]

I suspect you'll have a hard time getting Dave to make a change though, unless you can provide an exact, simple, step-by-step method to convert.  This doesn't sound like a simple thing.

Tim

Does Dave manage the forum system directly, or is it managed by someone else?   He seems awfully busy to be managing it himself, but I certainly wouldn't rule that possibility out.

I know nothing about the forum software itself.  There may already be a documented procedure for moving between the two databases. 

If there isn't, then of course someone would have to develop it.  How hard it would be would depend a lot on how much MySQL-specific stuff there is in the database schema, but the fact that the forum software directly supports three rather different database backends suggests that it probably keeps the database-specific stuff to a minimum, which would make migration quite a lot easier.



(Sent with Tapatalk, so apologies for the lackluster formatting)

[nctnico]

If the forum is using MyISAM tables then the admin should get a real hard slap in the face  . Nobody in their right mind would use MyISAM tables nowadays (and in the last 15 years) due to their inherent instability!
MyISAM was a great invention for a data retrieval system with an SQL frontend (aka MySQL) to create websites with mostly static content in a database but for handling a decent amount of writes MyISAM is totally unsuitable because there is basically zero fault tolerance.

I strongly second the suggestion for Postgresql!

Perhaps you're correct, but way to go for how to win friends and influence people.

Well, how friendly are you going to be if someone has a regulator in a (production) design which is almost overheating at room temperature? Using MyIsam tables is just as bad. The problem in both cases is that it will work OK for a while.

[Zbig]

Does Dave manage the forum system directly, or is it managed by someone else?   He seems awfully busy to be managing it himself, but I certainly wouldn't rule that possibility out.

I believe the EEVBlog server administration is done by a guy who goes by the nick "gnif" on this forum.

[EEVblog]

Does Dave manage the forum system directly, or is it managed by someone else?   He seems awfully busy to be managing it himself, but I certainly wouldn't rule that possibility out.

I believe the EEVBlog server administration is done by a guy who goes by the nick "gnif" on this forum.

Yes, gnif takes care of any really technical stuff when it needs it (gratis). other than that the forum just runs itself, and occasionally problems like this database thing happen and I know how to fix them myself.
I don't see the big deal, the forum has run almost flawlessly for over 6 years now, and we have barely lost a single post. So a database table gets corrupted a couple of times a year, no big deal, fixed in a jiffy with a table repair.
Who knows the problems that may lurk with changing entire database structures and what-not, it's not something that should be done "just because" IMO.

[nctnico]

Don't take my word for it. Just read about the opinion of others regarding the use of MyIsam tables for things like a forum.
In the late 90's / early 0's I developed a website for looking up phone numbers and addresses for companies (like the yellow pages but way better) and later on also CMS/webshop systems. After the first 2 database crashes I started to read into the reasons why the tables where crashing. It turned out that MyIsam tables are great for retrieval but not for inserting/changing data. This new (back then) knowledge made me convert everything to Innodb tables and the problems where solved. I never had a single table corruption in over a decade after that.

[EEVblog]

Don't take my word for it. Just read about the opinion of others regarding the use of MyIsam tables for things like a forum.

A forum which has been working perfectly fine for over 6 years.

In the late 90's / early 0's I developed a website for looking up phone numbers and addresses for companies (like the yellow pages but way better) and later on also CMS/webshop systems. After the first 2 database crashes I started to read into the reasons why the tables where crashing. It turned out that MyIsam tables are great for retrieval but not for inserting/changing data. This new (back then) knowledge made me convert everything to Innodb tables and the problems where solved. I never had a single table corruption in over a decade after that.

Table corruptions don't just magically happen. This event, like all others that I recall, have been due to the server briefly going down (for whatever reason). From what I understand that has the potential to corrupt the last entry that was written or whatever. It's fixed in a minute (once I got the message), and resulted in (AFAIK) no loss of data. The world isn't going to end if I don't switch to Innodb, the SMF forum seems to handle these issues just fine, always has.

FYI, when it comes to servers, databases, and everything else, I get advice from countless people who purport to be experts, have this experience and that etc, and usually they all advise something different. That's one of the big reasons why I often stick with the if it ain't broke, don't fix it camp.

[nctnico]

Well the database getting corrupted a few times per year is far from my definition when something is running fine but that is just a difference in opinion and priorities. My phone used to ring immediately when something was wrong with the server so I had more incentive to invest time in testing and going for the most reliable solution.

Getting good advise is hard indeed but fortunately there are places (fora) where there has been good debate over what is suitable for a certain situation and what is not so you can base a decission on consensus.

[kcbrown]

Does Dave manage the forum system directly, or is it managed by someone else?   He seems awfully busy to be managing it himself, but I certainly wouldn't rule that possibility out.

I believe the EEVBlog server administration is done by a guy who goes by the nick "gnif" on this forum.

Yes, gnif takes care of any really technical stuff when it needs it (gratis). other than that the forum just runs itself, and occasionally problems like this database thing happen and I know how to fix them myself.
I don't see the big deal, the forum has run almost flawlessly for over 6 years now, and we have barely lost a single post. So a database table gets corrupted a couple of times a year, no big deal, fixed in a jiffy with a table repair.

It's your forum.  If you want to live with the risk of greater data corruption than you've seen (or noticed!  How do you know what data has actually been lost?), that's your call.

But you're playing with fire here.  I'm telling you that you've been lucky to not lose more data than you apparently have, perhaps because the system hasn't been terribly busy when it went down, or something like that.

The table repair mechanism fixes the structure, not the data.  You cannot recover data that has been lost, cannot repair data itself because the data itself is arbitrary, not known in advance.  And what you are losing is not necessarily just data in a single table.   You are likely losing data in multiple tables and possibly incurring damage to the integrity of the references between tables.   How important that is depends on the schema of the database.

Who knows the problems that may lurk with changing entire database structures and what-not, it's not something that should be done "just because" IMO.

Oh, I quite agree.  It's something to be done for solid reasons, just as with any such change.   But let me ask you this: would you put up with a barely adequate component (in terms of reliability and robustness) in the most critical portion of one of your electronic products for public consumption?   Would you put up with, say, your scope's power supply occasionally generating enough noise to corrupt the signal you're trying to measure?  I doubt it.

You stress, again and again, good engineering practices on your blog.  That is something I commend you for, because I believe good engineering practices are a critical foundation for everything we build.  Well, we're telling you what good system design practices are for this.  What you're using for this forum's database backend is like selecting a Hung Wei Lo mains capacitor for your power supply.  It is substantially more likely to fail you than a Nichicon capacitor is, and that's why you harshly criticize companies for using lesser brands of capacitors for their power supplies.   How is this any different?

[Howardlong]

Folks

People, for goodness sake please get a grip! Nobody died. This is not your bank accounts or health records. It's an internet forum.

Please relax.

(I did chuckle when I first saw this thread earlier today, I was expecting half a dozen different solutions from half a dozen different experts, and indeed we got it. If you care to look me up on Linked-In you'll see I have nearly 30 years of enterprise database experience, I know all about corrupt databases from the old days and thankfully things have improved vastly over the intervening years, it's been a couple of decades since I last had to hex edit a database, but the forum is simply not a candidate an enterprise database with 5x9s, SLAs, RTOs and RPOs. If you want all that stuff, you're going to have to pay for it in terms of expertise and staffing.

In the real world it's not all about getting nerdy about technology it's also about assessing and weighing up the technical risks in terms of costs and reputational damage, and TBH this minor incident is not exactly something that's going to hit The Register. Please remember there is also a risk and cost to moving to any new platform in terms of re-skilling. Sometimes it's better the devil you know, and in this case I really can't see why you'd invest many hours of effort in migrating plus many man days over time learning how to fix something new. There's a balance, and the balance has already been struck).

[Zbig]

[..] and TBH this minor incident is not exactly something that's going to hit The Register.

Whaaaa? So no "Resistance rises as EE nerds' forum goes titsup" headline for me? Bummer.

[kcbrown]

Folks

People, for goodness sake please get a grip! Nobody died. This is not your bank accounts or health records. It's an internet forum.

Please relax.

Well, if anyone's getting worked up over this, it's not I.  I'm just pointing out superior alternatives.  If Dave wants to soldier on with what he's got, that's his call.

(I did chuckle when I first saw this thread earlier today, I was expecting half a dozen different solutions from half a dozen different experts, and indeed we got it. If you care to look me up on Linked-In you'll see I have nearly 30 years of enterprise database experience, I know all about corrupt databases from the old days and thankfully things have improved vastly over the intervening years, it's been a couple of decades since I last had to hex edit a database, but the forum is simply not a candidate an enterprise database with 5x9s, SLAs, RTOs and RPOs. If you want all that stuff, you're going to have to pay for it in terms of expertise and staffing.

Right.  But fortunately, robust databases are no longer something you absolutely must pay through the nose for.  The march of technology has seen the rise of freely available database engines that are highly reliable.  PostgreSQL is probably the best of them. 

In the real world it's not all about getting nerdy about technology it's also about assessing and weighing up the technical risks in terms of costs and reputational damage, and TBH this minor incident is not exactly something that's going to hit The Register. Please remember there is also a risk and cost to moving to any new platform in terms of re-skilling. Sometimes it's better the devil you know, and in this case I really can't see why you'd invest many hours of effort in migrating plus many man days over time learning how to fix something new. There's a balance, and the balance has already been struck).

No doubt.  But as with anything else, you can either pay up front or you can pay over time.  If Dave is content with how his forum performs, then that's fine.  All we can do is point out the alternatives.  It's his choice as to what to do with them.

Choosing a Hung Wei Lo mains capacitor for your scope's power supply will probably be okay.  Most of the resulting scopes will probably function just fine under most circumstances.  But we can rightly criticize that choice just the same.  And so it is here.  The choice of this forum's backend database technology will probably function just fine under most circumstances.  But it is not the normal circumstances where the differences show themselves, it's the exceptional ones where they do.   A good design properly accounts for the edge cases, not just the typical ones.

Why do we insist on levying criticism at hardware manufacturers for selecting dodgy components, while simultaneously insisting that to do so in a different area (forum implementation) is perfectly okay?   That seems a bit inconsistent, don't you think?

[Howardlong]

Well, if anyone's getting worked up over this, it's not I.

Excellent! That's alright then.

[nctnico]

This is not your bank accounts or health records. It's an internet forum.

Yes and no. You can put a monetary value on the knowledge collected in a forum like this so losing data means losing money. For example: a forum about fixing cars I used to frequent sold the data in the forum to a company who made a living from a car-fixing QA database.

[Brumby]

... and thankfully things have improved vastly over the intervening years,

I did find a reference to database problems of 25 years ago somewhat amusing.

In the real world it's not all about getting nerdy about technology it's also about assessing and weighing up the technical risks in terms of costs and reputational damage, and TBH this minor incident is not exactly something that's going to hit The Register. Please remember there is also a risk and cost to moving to any new platform in terms of re-skilling. Sometimes it's better the devil you know, and in this case I really can't see why you'd invest many hours of effort in migrating plus many man days over time learning how to fix something new. There's a balance, and the balance has already been struck).

There's just so much about this I want to reinforce ... but it comes down to a risk management issue.

This forum is NOT a critical system and any inconveniences will not cause any major collapses.  The biggest risk is faced by Dave in how such events will affect his business.

The comparative time investment alone is enough to question a migration exercise.  What sense is there in throwing a bucketful of man-hours into such a project when that same amount of time could cover the next 20 years of 'the odd fix now and then'?  Also, who's to say the new environment won't have it's own foibles that require even more attention than the current one?

Change is, in itself, a risk - and major changes are major risks.  Anyone who knows anything about Westpac's CS90 project knows what sounded like a technologically good idea can turn into a failed, embarrassing disaster, without any trouble at all.  I know it's on a somewhat different scale, but it's still an example where seemingly good ideas are actually bad ideas.

As for the concept of getting any IT project running perfectly - that just doesn't happen, not in the real, commercial world.  Management decisions - nearly always based on time and cost - will see projects divided into phases, where the essential function is encapsulated in 'Phase I' and the 'nice to haves' (even if they are really part of the core function) will be relegated to Phase II ... which is left to die and rot in the sun.  People working with computer systems all around the world are very familiar with the imperfect nature of these beasts - and they come to find a balance which works for them.

I can easily understand Dave's decision and the fact that he has found this balance and is comfortable with it, is enough for me to let the subject rest.

[EEVblog]

Yes and no. You can put a monetary value on the knowledge collected in a forum like this so losing data means losing money. For example: a forum about fixing cars I used to frequent sold the data in the forum to a company who made a living from a car-fixing QA database.

The database (and all forum files like attachments etc) are backed up to amazon S3 daily. The only question is how long it takes to recover. At present if we had to ever recover from the backup, then it could take quite some time, maybe a day or two to fix. There is no redundant forum backup mirror we can automatically switch in.

[Brumby]

This is not your bank accounts or health records. It's an internet forum.

Yes and no. You can put a monetary value on the knowledge collected in a forum like this so losing data means losing money.

Maybe somewhere else, but is that relevant HERE?

Anyway, the answer is only relevant to Dave and it's part of his risk management responsibility.

[EEVblog]

Also, who's to say the new environment won't have it's own foibles that require even more attention than the current one?

Yes. Just getting the current forum and eevblog server running as smooth as it does has taken years of tweaking many minor things. Many issues you don't know about until it becomes a problem and you investigate it.
To think you can "just change the database to InnoDB" and that's that is quite unlikely not a realistic way it would unfold.

[timb]

Yes and no. You can put a monetary value on the knowledge collected in a forum like this so losing data means losing money. For example: a forum about fixing cars I used to frequent sold the data in the forum to a company who made a living from a car-fixing QA database.

The database (and all forum files like attachments etc) are backed up to amazon S3 daily. The only question is how long it takes to recover. At present if we had to ever recover from the backup, then it could take quite some time, maybe a day or two to fix. There is no redundant forum backup mirror we can automatically switch in.

And this is why you might want to think about an alternative, better database.

MySQL has been going downhill since Oracle bought them out. At the very least I'd urge you to switch to MariaDB. (The people who originally developed MySQL forked the code after Oracle bought it and started a new company to continue development under the name MariaDB.)

It's not only faster than MySQL but much more stable.

That said, PostgreSQL is an even better alternative. I spent nearly 15 years in deep in the trenches of the web hosting business and I can assure you, your thinking *will* lead to a catastrophic failure sooner rather than later. Your database tables shouldn't be corrupting. That's not something that should "just happen" and it's far from ideal.

The server hosting the forum shouldn't be having power failures, either. Why doesn't it have redundant power supplies? A UPS? Heck, it shouldn't even be a single server; for the type of traffic the forum (and your site) gets I'd kind of expect it to be on a shared cluster and not just a single box.

Anyway, if you'd like any help, I'd be happy to draw up an action plan for you, or do a once over of your current setup and let you know if there's any areas for improvement.

Also, we wouldn't just change the DB over and be done with it. If I were doing it, I'd create a shadow copy of the forum with the new DB on another server and write scripts to stress test it for a week. You don't just change it over and hope for the best. There's procedures for this sort of thing. I've migrated databases 1000x more complex for Fortune 1000 companies. It's completely doable with zero downtime.

[Brumby]

It's completely doable with zero downtime.

Downtime isn't the issue.  It's Dave time.

[timb]

It's completely doable with zero downtime.

Downtime isn't the issue.  It's Dave time.

I wouldn't expect Dave to do it. That's why I offered to help. Others have as well.

Not that Dave couldn't do it. He could, I'm sure. But it would take a lot of time, which he doesn't have. But, a few of us here *do* have the knowledge *and* time to help. If it were me, I'd take advantage of that.

[Cerebus]

But phrases like "nobody in their right mind" or "slap in the face" and so on are not conducive to gaining instant buy-in of your POV.

You may be right, most probably are, about the 'conducive' aspect but "nobody in their right mind" is apt as far as using MyISAM tables is concerned. They are dangerous and ought to have been ripped out of MySQL shortly after it got a backend that actually supported transactions. I was dumbfounded the day I discovered that MySQL didn't support transactions and I'm still dumbfounded that it's still possible to configure it so it doesn't. Someone who worked for me and configured it thus in a production environment would be quite sternly re-educated* unless they had a very, very good reason and a very, very good backup plan for when it eventually broke something.

On the flip side, I can't speak highly enough of postgresql. I've run it as the core critical production database of two companies and for the last six years as the core database of my home phone system and it has never once let me down. It's so reliable in the phone system that I'd almost forgotten it was there.

*As in re-education camp and salt mining resort.