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.

[KE5FX]

Slight tangent: one thing that happened after the latest crash was that the URL that I previously used as a bookmark (https://www.eevblog.com/forum) now returns "502 Bad Gateway / No need to report this error, we're working on it." 

This has been the case for several days now.  I was wondering if you'd been hacked and then hit by a bus.   I finally tried going straight to eevblog.com and entering the forum that way, which (obviously) worked.  It may not be a widespread problem, but if you've seen a falloff in forum activity at all over the past few days, the URL change could be a factor.

[DimitriP]

Slight tangent: one thing that happened after the latest crash was that the URL that I previously used as a bookmark (https://www.eevblog.com/forum) now returns "502 Bad Gateway / No need to report this error, we're working on it." 

This has been the case for several days now.  I was wondering if you'd been hacked and then hit by a bus.   I finally tried going straight to eevblog.com and entering the forum that way, which (obviously) worked.  It may not be a widespread problem, but if you've seen a falloff in forum activity at all over the past few days, the URL change could be a factor.

Hitting refresh a couple of times has cleared it for me in the past .

[zapta]

Database? What is it? I just use the forums and they work pretty well.

Seriously, it's Dave's call to decide if there is a business case for database migration. Perfection is the enemy of good.

[Nerull]

I hope you guys never have to find out how many major businesses you use every day are running on MySQL - or worse. Big fad databases like MongoDB have well documented data loss problems, and major businesses use them. The world has yet to come to an end.

Or, alternatively, chill out. Imagine someone writing you a nasty letter asking why you didn't use 1% resistors instead of 5% for a non-critical value. That's pretty much precisely what you're doing.

People demanding a change on a forum are the last people in the world I would recruit to help with such an effort - the chances of running into Dunning-Kruger are astronomical.

[KE5FX]

Slight tangent: one thing that happened after the latest crash was that the URL that I previously used as a bookmark (https://www.eevblog.com/forum) now returns "502 Bad Gateway / No need to report this error, we're working on it." 

This has been the case for several days now.  I was wondering if you'd been hacked and then hit by a bus.   I finally tried going straight to eevblog.com and entering the forum that way, which (obviously) worked.  It may not be a widespread problem, but if you've seen a falloff in forum activity at all over the past few days, the URL change could be a factor.

Hitting refresh a couple of times has cleared it for me in the past .

Hmm, you're right.  Weird.  I didn't think to try that.

[kcbrown]

I hope you guys never have to find out how many major businesses you use every day are running on MySQL - or worse. Big fad databases like MongoDB have well documented data loss problems, and major businesses use them. The world has yet to come to an end.

Or, alternatively, chill out. Imagine someone writing you a nasty letter asking why you didn't use 1% resistors instead of 5% for a non-critical value. That's pretty much precisely what you're doing.

People demanding a change on a forum are the last people in the world I would recruit to help with such an effort - the chances of running into Dunning-Kruger are astronomical.

Who's demanding change here?  I don't see anyone doing that.

This is the suggestion forum.  So as a result, it seems reasonable that you should expect to see suggestions made here.

 

[kcbrown]

I just love the utter apathy by some here for how things are built.  From engineers, no less!

Is there no pride in craftsmanship anymore?  You know, where you select components that are better than than what is strictly necessary, and design things to be better than they absolutely have to be, simply as a matter of pride?

Just astonishing ...

[KE5FX]

I just love the utter apathy by some here for how things are built.  From engineers, no less!

Is there no pride in craftsmanship anymore?  You know, where you select components that are better than than what is strictly necessary, and design things to be better than they absolutely have to be, simply as a matter of pride?

Just astonishing ...

Any idiot can build a bridge that will stand up.  It takes an engineer to build a bridge that will barely stand up.

[Macbeth]

It seems reasonable enough to me that someone other than Dave, like gnif or Dave2 who seem competent enough could take a snapshot of the forum and run a simple conversion/upgrade from MySQL to MariaDB or Postgresql, just to see how it works. Host it on a different port and invite people who know these things to stress test it.

While we are at it, finally fix that awful cruddy problem of not allowing frikkin' international characters on an international engineering forum! Why is my post preview perfect with ohms, sigmas, deltas, greek, cyrillic, chinese,  then after I post they all turn into ? question marks...?

It's not the forum itself, as can be witnessed with preview, but the writing to database character set enforcement is the problem.

The LaTeX addon is not the answer either.

[kcbrown]

Any idiot can build a bridge that will stand up.  It takes an engineer to build a bridge that will barely stand up.

True as that may be, it also takes an engineer to know that building a bridge that will barely stand up is a bad idea, because a bridge that will barely stand up will eventually fall down when the conditions inevitably exceed the barely sufficient design.

Put another way, you always overdesign something by some decent amount, unless you simply don't care how long the thing you're building will last.

There's a reason we admire companies like the HP of old, Agilent, Fluke, etc., and we don't admire companies like Siglent or Rigol in the same way.

If you could get a Fluke multimeter for the same price as a Uni-T of equal claimed capability, wouldn't you buy the Fluke?  I would.  Even if it required a little more up-front effort on my part, and especially if it meant a lower amount of periodic effort later on, and most especially if it meant avoiding disaster (even if I believed such disaster to be relatively unlikely).  Having solid, reliable tools is worth at least that, at least to me.


I suspect the reason the forum is using MySQL may be historical -- SMF 2.0 wasn't released until mid 2011.   SMF 1.0.x only supported MySQL.

Dave is right to be wary of the tuning that would be required in the event the database engine is changed.  But such wariness ignores one fundamental fact: additional tuning will almost certainly be required as a result of growth of the forum regardless.


Changing the underlying database to PostgreSQL (MariaDB would still leave you with MyISAM tables, and it's the fundamental characteristics of that table type which are really at issue here) is just a suggestion, but it's a suggestion akin to changing out that Wun Hung Lo mains capacitor for a Nichicon one.  The Wun Hung Lo capacitor may work fine under most circumstances, and for some reasonable amount of time.  That's not why you change it.  You change it because you want it to work all of the time, under all foreseeable circumstances.

[hammy]

Entertaining discussion.
Changing a database without proper analysis of the root cause is just blind actionism.
You all think the problem is the database. Is this true? The whole discussion is about an error message someone saw in his browser, whithout access to the system. Gazing deeply into a crystal ball?
Maybe the issue is just a symptom and not the root cause?

The Wun Hung Lo capacitor may work fine under most circumstances, and for some reasonable amount of time.  That's not why you change it.  You change it because you want it to work all of the time, under all foreseeable circumstances.

Even a wun hung lo cap can do a perfect job during the lifetime of the device. And even a quality cap can fail early. If this happens, you analyze the root cause.

Swapping a cap is not like swapping a database backend. A forum in this size needs several adjustments and database specific configuration changes. A database change is _never_ a drop in replacement for a system of this size.

Dave and his Webserver-Team are perfectly capable to handle this on their own. Only they know how much work they already put into frontend and backend of this forum.

For my feeeling the forum is stable, fast and reliable.  A restart now and then is ok, nothing is perfect. And there is no magic database out there. If Dave/Gnif/whoever has a good knowledge about mysql and no experience with postgres, then they should keep the mysql.

It is just a forum. It is Daves forum. He can do whatever he want.

And please no activism without root cause analysis. If you do this all the work and effort might be useless.

Cheers
hammy

[nctnico]

Never turn analogies in semantic discussions because analogies are just there to explain things in terms the other person can understand or relate to.

[Macbeth]

I would be happy if the character sets were fixed. That can be done on the MySQL database I am sure.

It was clearly just a lazy oversight on the initial configuration install and has never been addressed since.

Certainly if the back end DB is upgraded to something better I hope international characters are allowed. Perhaps trial it on the current DB. It only needs a staging copy to test it - host it on a different HTML port and let the hackers amongst is give it hell... If it works just fine (which is 99.9% likely) then that simple change can be implemented on the live DB with a stroke of the bash command line.

[KM4FER]

OK guys I have two questions:

1.  If myISAM is so bad please explain how for 12 years I was able to run a mySQL database using myISAM tables with the only table corruption occurring due to the new kid killing the server without following proper shutdown procedures.  By the way this database had over 10⁸ entries with multiple indicies, 2-6x10⁵ updates/adds/deletes per day, and there report queries running constantly.  I believe it will be a long time before EEVBlog reaches these numbers.

2.  Assume, if you will, that the EEVBlog database was hopelessly corrupted for whatever reason and the backups were unreadable.  In other words the entire forum database is gone, never to be seen again.  What's the significance of such an occurrence?  Will the world economy crash, will nations go to war against each other, will even one person die?  What is in the database that could be considered so important that any of these situations would be precipitated?  Hell, Dave's a smart fellow.  He'd just make a video about it and garner another 10k subscribers.

So guys, why don't we just let this topic go to sleep.

If YOU want to run your business with PostgreSQL please feel free to do that but don't make it a religion and insist that everyone else must follow your ENLIGHTENED lead.

earl

[Brumby]

I would not argue with that.

The engineer in me likes the idea that something is bulletproof - but the pragmatist has to ask "Is it worth it?".  The opportunity cost of chasing something that might be a problem cannot be ignored.

Years of working in commercial application development and disaster recovery projects leads me to state very simply - It's a matter of risk management.

One nation wide organisation I know of did a full business risk analysis and found that one section came up on the top of the list - very clearly. It was so important that if the computer systems were not back up and running in a few days - from even an event like this eBook cover portrays - the entire organisation would fold. THAT was a risk that demanded serious attention. The EEVBlog website and forum risks aren't anywhere near that magnitude

It's all about risk management.  It's Dave's risk, so I, for one, am happy to let him manage it.

[kcbrown]

OK guys I have two questions:

1.  If myISAM is so bad please explain how for 12 years I was able to run a mySQL database using myISAM tables with the only table corruption occurring due to the new kid killing the server without following proper shutdown procedures.  By the way this database had over 10⁸ entries with multiple indicies, 2-6x10⁵ updates/adds/deletes per day, and there report queries running constantly.  I believe it will be a long time before EEVBlog reaches these numbers.

And that could be the case.

But power failures happen.  Hardware failures happen.  Improper shutdowns happen.  Operating system crashes happen.

Robustness exists to handle the exceptional cases, not the normal ones.

2.  Assume, if you will, that the EEVBlog database was hopelessly corrupted for whatever reason and the backups were unreadable.  In other words the entire forum database is gone, never to be seen again.  What's the significance of such an occurrence?  Will the world economy crash, will nations go to war against each other, will even one person die?  What is in the database that could be considered so important that any of these situations would be precipitated?  Hell, Dave's a smart fellow.  He'd just make a video about it and garner another 10k subscribers.

With the engineers no longer having a forum in which to debate which oscilloscope is best, the world economy might tank after all!    

If YOU want to run your business with PostgreSQL please feel free to do that but don't make it a religion and insist that everyone else must follow your ENLIGHTENED lead.

Who's saying that Dave must do these things we're suggesting?

Not me.  In fact, I've already stated more than once that it's his call.  Its his forum to keep or to lose.


Where do people get the idea that anyone's forcing anything on anyone else here???

[Nerull]

I just love the utter apathy by some here for how things are built.  From engineers, no less!

Is there no pride in craftsmanship anymore?  You know, where you select components that are better than than what is strictly necessary, and design things to be better than they absolutely have to be, simply as a matter of pride?

Just astonishing ...

Engineers know that replacing a working system carries risks, and shouldn't be done just because someone read a reddit post that said X is better than Y and decided to go on a crusade.

Yes, MySQL can lose data on power loss. So can Postgres. Postgres claims better write reliability only if you disable all disk caching, which may not even be possible depending on storage type. It has had pretty major data loss bugs in the past too.

Would you, as an engineer, throw away all of your equipment the instant a company comes out with something that might theoretically be better? Even if your current equipment works fine?

[kcbrown]

Entertaining discussion.
Changing a database without proper analysis of the root cause is just blind actionism.
You all think the problem is the database. Is this true? The whole discussion is about an error message someone saw in his browser, whithout access to the system. Gazing deeply into a crystal ball?
Maybe the issue is just a symptom and not the root cause?

No, the message in this case is very clear, and there is only one thing in the system that emits that specific error message.  It has a specific meaning.

The Wun Hung Lo capacitor may work fine under most circumstances, and for some reasonable amount of time.  That's not why you change it.  You change it because you want it to work all of the time, under all foreseeable circumstances.

Even a wun hung lo cap can do a perfect job during the lifetime of the device. And even a quality cap can fail early. If this happens, you analyze the root cause.

That's precisely my point.  A Wun Hung Lo cap is perfectly fine under many circumstances.  So why, then, do we criticize the choice to use such caps?

Simple: because we like robustness.  We like quality.  We admire high quality designs and high quality implementations.  It appeals to the engineer in us.  And we dislike the absence of those qualities.

So when I see engineers saying that, on one hand, a Wun Hung Lo cap is a questionable choice for, e.g., a power supply, but those same engineers are perfectly happy with the database equivalent of a Wun Hung Lo cap as the data storage mechanism for a forum they frequently use and enjoy, well, it makes me wonder just how strong their engineering principles really are.

Swapping a cap is not like swapping a database backend. A forum in this size needs several adjustments and database specific configuration changes. A database change is _never_ a drop in replacement for a system of this size.

It depends entirely on how the forum software is structured.  A migration script would have to be written.  But I've done that kind of thing in the past.  It requires getting the details right, obviously, but it's not impossible by any stretch.   Such a script might even already exist.

For my feeeling the forum is stable, fast and reliable.  A restart now and then is ok, nothing is perfect. And there is no magic database out there. If Dave/Gnif/whoever has a good knowledge about mysql and no experience with postgres, then they should keep the mysql.

That's definitely a consideration.

But ask yourself this: how do you know that forum data has not been lost in the past?  All we know is that nobody has noticed that data has gone missing.  Maybe it has and maybe it hasn't.

That's the problem with data loss due to database crashes.  What you lose may not become apparent until well after the fact.  And the effects can be subtle.  You might see, for instance, some very strange behavior out of the system that you hadn't seen before, and might not see it until well after the database crash, which might well lead you to believe that the root cause is something else entirely.
 

I wouldn't even have made the suggestion I did unless the error message was a definitive one.  You can Google the message and see for yourself what it really is if you're so inclined.

[kcbrown]

Engineers know that replacing a working system carries risks, and shouldn't be done just because someone read a reddit post that said X is better than Y and decided to go on a crusade.

No doubt.  Engineers understand the reasons that one choice is better than another, and make the decision on that basis.

We're not talking about something that's purely a matter of opinion here.  The reasons for favoring one type of database over another in this case are rooted in the same reasons that a well-respected brand of cap is favored over a Wun Hung Lo brand of cap: experience and an understanding of the behavior and design.

[kcbrown]

Yes, MySQL can lose data on power loss. So can Postgres. Postgres claims better write reliability only if you disable all disk caching, which may not even be possible depending on storage type. It has had pretty major data loss bugs in the past too.

You should probably read this before we go too deep into this, but put simply, PostgreSQL takes steps to minimize the chance that a write will fail to reach the disk when intended even in the face of disk caching.

But yes, there exist forms of caching that would prevent PostgreSQL's efforts from bearing fruit.

Would you, as an engineer, throw away all of your equipment the instant a company comes out with something that might theoretically be better? Even if your current equipment works fine?

Nope.  I would base the decision on what is empirically better.  And PostgreSQL simply is empirically better than MyISAM tables in MySQL.  But even if that were the case, I'd still come up with a migration plan and a test procedure, and would test the alternative first, to at least ensure that it provided the characteristics that the current system lacked.


Nobody's making unsubstantiated claims here.

[Nerull]

A database administrator who relied purely on the database system to preserve his data integrity wouldn't be an employed database administrator for very long.

Databases fail. MySQL fails. Postgres fails. MSSQL fails. Oracle fails.

The database this forum runs on does not affect you in any way. It isn't a safety issue. It isn't a business issue. You want them to change for what might possibly on the off chance happen, because of your opinion that the world will come crashing down if they don't.

And yet, with MySQL being one of the most popular databases in the world, it doesn't happen. "Because its shiny!" isn't a reason.

How about this: The SMF devs strongly recommend against using SMF with Postgres. It is not well supported and regularly has major issues, because the devs don't test against postgres. Everything is developed against MySQL, and then quickly hacked to support Postgres, not always well. Known bugs are left unfixed for many versions simply because the devs can't be bothered.

That is a real, concrete, engineering reason. You seem to have overlooked it in favor of which one is shinier.

While we're talking about what we'd do to "someone who worked for me" - I know what I'd do to a DBA who replaced a working database system just because he liked the other one better without ensuring that the platform we were using was well supported with that database.

[kcbrown]

A database administrator who relied purely on the database system to preserve his data integrity wouldn't be an employed database administrator for very long.

Yeah, but I'm not arguing that a better choice of database would eliminate the need for the other steps that would need to be taken to ensure the survival of the data in the event of disaster.

Good choice of a database system is necessary, but it's not sufficient.

Databases fail. MySQL fails. Postgres fails. MSSQL fails. Oracle fails.

Wun Hung Lo caps fail.  Nichicon caps fail.  Nippon Chemi-con caps fail.

It's not a question of whether any of these things fail, it's a question of how often and under what circumstances.

The database this forum runs on does not affect you in any way. It isn't a safety issue. It isn't a business issue. You want them to change for what might possibly on the off chance happen, because of your opinion that the world will come crashing down if they don't.

Funny, I thought the suggestion forum was for, you know, suggestions.

Why are you treating my suggestion as anything other than just that?

And yet, with MySQL being one of the most popular databases in the world, it doesn't happen. "Because its shiny!" isn't a reason.

Of course it's not a reason.  Proven robustness is a reason.

How about this: The SMF devs strongly recommend against using SMF with Postgres. It is not well supported and regularly has major issues, because the devs don't test against postgres. Everything is developed against MySQL, and then quickly hacked to support Postgres, not always well. Known bugs are left unfixed for many versions simply because the devs can't be bothered.

That is a real, concrete, engineering reason. You seem to have overlooked it in favor of which one is shinier.

Yes, that is a real, concrete engineering reason.  I didn't overlook it.  I simply wasn't even aware of it.  That's a SMF-specific thing.

I didn't see anything in the SMF 2.0 recommendations and requirements saying anything about it.  I do see some forum statements to that effect.

My apologies.  I took the SMF documentation at its word.  That apparently was a mistake.

I'll modify my original message to reflect this revelation.

While we're talking about what we'd do to "someone who worked for me" - I know what I'd do to a DBA who replaced a working database system just because he liked the other one better without ensuring that the platform we were using was well supported with that database.

I completely agree.

[EEVblog]

Who's saying that Dave must do these things we're suggesting?
Not me.  In fact, I've already stated more than once that it's his call.  Its his forum to keep or to lose.
Where do people get the idea that anyone's forcing anything on anyone else here???

Your 2nd sentence above. You are implying that if I don't as you recommend, that I will lose my forum.

[kcbrown]

Who's saying that Dave must do these things we're suggesting?
Not me.  In fact, I've already stated more than once that it's his call.  Its his forum to keep or to lose.
Where do people get the idea that anyone's forcing anything on anyone else here???

Your 2nd sentence above. You are implying that if I don't as you recommend, that I will lose my forum.

Ah.   Yes, I can see how that might be one's conclusion.  That was undoubtedly a poor choice of words on my part.  I meant that statement as an indication of ownership.   Which is to say, this forum is yours, so you can do whatever you want with it, no matter what the consequences of that might be, even if those consequences would lead to loss of the forum.  I didn't mean by that to imply that failing to follow my suggestion here would lead to the loss of the forum!

That phrasing is my fault.  The resulting misunderstanding is thus also my fault and my responsibility.


In this particular case, you've got backups, so losing the forum is extremely unlikely regardless of what database backend you use.   While some of those backups have been taken after the forum has gone down and has been through at least one table repair cycle, it's still unlikely that any data of consequence has been lost.  It's still possible, though, unless the forum software maintains some kind of data consistency checking mechanism that would alert you to such an event (the MyISAM table type doesn't really do anything of that nature), so there's the small possibility that your backups are missing some data that existed prior to one of the previous crashes.

Whether that matters or not depends entirely on the nature of what, if anything, has been lost, and the value people place on it.  I've no idea how often people search these forums for prior messages.

Another thing in your favor is that due to the nature of the forum, most of the operations to the database will add data, which means the data that is most likely to be lost is the data most recently written.  That isn't always the case, but it's probably the way to bet.


But this should make it clear that SMF backed by MyISAM under MySQL is by no means immune to data loss in the event of a system crash: http://www.simplemachines.org/community/index.php?topic=416871.0


Dave, frankly, I don't see any truly good alternatives for you here -- your best bet is to simply stay the course and to make the underlying machine itself as robust as you can.  I've modified my original message that started this thread to reflect the fact that PostgreSQL support under SMF appears to be something of an afterthought.  Under those circumstances, a move to PostgreSQL is not something I can recommend anymore.   I'm quite annoyed that SMF is apparently that way about it.  It seems like a nice piece of forum software other than that.

[nctnico]

2.  Assume, if you will, that the EEVBlog database was hopelessly corrupted for whatever reason and the backups were unreadable.  In other words the entire forum database is gone, never to be seen again.  What's the significance of such an occurrence?  Will the world economy crash, will nations go to war against each other, will even one person die?  What is in the database that could be considered so important that any of these situations would be precipitated?  Hell, Dave's a smart fellow.  He'd just make a video about it and garner another 10k subscribers.

From a business perspective you are grossly underestimating the monetary value of this forum. You never heard about online communities like Facebook, Lindedin, Twitter, Yahoo, MSN? All of these are based on people communicating and they all are or where worth sh*t loads of money. Besides that I think Dave would have a serious problem keeping the regulars on if the forum got erased and it will take several years to get it back to where it is now. EEVblog is not the only electronics forum and the 'forum-du-jour' can shift quickly (see the downfall of Altavista and the rising of Google). It is all about keeping momentum. Ask yourself what would happen with Google if their search engine is offline for (in total) a couple of days per year?

No matter how you look at it it cannot be denied that MyIsam tables have inherent flaws which lead to loss of data so it makes sense to use a table structure which is more robust and thus improve the forum availability.

[StuUK]

Just read this thread..

Oh boy! another this database vs that database discussion.... Absolutely nothing wrong with using MySQL and obviously the amount of (or should I say lack of) downtime clearly demonstrates no disernable value vs the effort of swapping databases, why would you unless it's grinding to a halt or falling over every day, which it clearly isn't and even then the database may not be the issue.

The only real issue I can see is the reference to special symbols/characters which could be resolved by changing the character set on a number of tables to UTF etc which is no big deal.

And the idea that swapping to PostgreSQL gives maximum robustness clearly demonstrates a naivety in respect to IT systems. There are many many components that contribute to robustness, the database is just one of them and a single instance of poorly configured PostgreSQL is no more robust than a single instance of a poorly configured MySQL. There is more chance of an Amazon region failure or instance outage causing an issue than the MySQL database suddenly corrupting itself.

I could just as easily say throw out this piece of forum software for another piece of forum software to achieve more robustness...

 

[kcbrown]

Well, anyway, sorry for bringing up this suggestion, guys.  I thought it would be a reasonable suggestion, given my rather horrid experience with MySQL's MyISAM tables and the opposite experience I've had with PostgreSQL, and the claim by Simple Machines that PostgreSQL is supported by SMF 2.0...

 

[timb]

Well, anyway, sorry for bringing up this suggestion, guys.  I thought it would be a reasonable suggestion, given my rather horrid experience with MySQL's MyISAM tables and the opposite experience I've had with PostgreSQL, and the claim by Simple Machines that PostgreSQL is supported by SMF 2.0...

 

It's your own fault for even suggesting such a thing! What do you think this is, some kind of *suggestion sub-forum* or something?

Oh, wait...

[kcbrown]

LOL.  Reminds me of that famous movie quote.  "Hey, you can't fight in here!  This is the War Room!"    


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

[EEVblog]

No matter how you look at it it cannot be denied that MyIsam tables have inherent flaws which lead to loss of data so it makes sense to use a table structure which is more robust and thus improve the forum availability.

The forum already has like 99.99% uptime, it's fine, relax.

[nctnico]

99.99% is still over to 3.5 days of outage per year!

[StuUK]

99.99% is still over to 3.5 days of outage per year!

Seriously, have you any idea about just how good that is in the real IT world..... most service providers never achieve it even for critical systems....

[nctnico]

99.99% is still over to 3.5 days of outage per year!

Seriously, have you any idea about just how good that is in the real IT world..... most service providers never achieve it even for critical systems....

I have 'some' experience with system administration but stopped doing that a long time ago that because it was utterly boring. Anyway: 3.5 days unscheduled downtime per year? Not on my watch! If you maintain systems used for trading stocks and options with a daily trading value of a couple of billion euro you'll have to get things running smoothly and reliable. I also used to co-own a webdesign company and we had way better availability than 99.99%! Maybe 3 or 4 hours per year downtime.

[EEVblog]

99.99% is still over to 3.5 days of outage per year!

Err, your math is several orders out, it's 0.0365 days (0.8 hours)
Ok, so it's maybe 99.9% (0.365 days a year), even that is still pretty good and a figure that most host providers will guarantee.
And those failures are almost always because of power or other dedicated server failure. If it is a database table corruption (happens maybe 1 in 10 times the server hiccups), then it will stay down until I or gnif notice it and fix it (i.e we are sleeping).
Again, a database table corruption has never happened just on it's own, it's always the result of hardware or something else going down on the server.

[MatthewMorgan]

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.

Making things more cacheable would reduce server load,
Maybe if possible show all logged out users a html version that is refused when you come to a maximum of once per minute. (cache)

[EEVblog]

I also used to co-own a webdesign company and we had way better availability than 99.99%! Maybe 3 or 4 hours per year downtime.

That's what the EEVblog server basically gets, a handful of real server hours per year of server downtime. As I said, the extra downtime is the result of not having someone respond immediately to fix database problems. There have also been a few cloudflare issues, but these are not related to the server.
My host have staff to fix actual server problems swiftly when they happen, but they do not monitor this forum or any other running databases.

[EEVblog]

Making things more cacheable would reduce server load,
Maybe if possible show all logged out users a html version that is refused when you come to a maximum of once per minute. (cache)

The server goes through Cloudflare, and has caching of various sorts, about as good as you can get without going to a multiple distributed server database structure thingo (I don't know the correct term). The server has very little real load, currently:
0.49 0.47 0.36 according to WHM

[StuUK]

99.99% is still over to 3.5 days of outage per year!

Seriously, have you any idea about just how good that is in the real IT world..... most service providers never achieve it even for critical systems....

I have 'some' experience with system administration but stopped doing a long time ago that because it was utterly boring. Anyway: 3.5 days unscheduled downtime per year? Not on my watch! If you maintain systems used for trading stocks and options with a daily trading value of a couple of billion euro you'll have to get things running smoothly and reliable. I also used to co-own a webdesign company and we had way better availability than 99.99%! Maybe 3 or 4 hours per year downtime.

Apart from the dodgy maths, trading systems are looking at 5 9's (or better) or zero point of failure and spend the money to achieve it... hardly think Dave needs to worry about that kind of expenditure for his forum and irrespective of what Dave does with his software config he's still reliant upon the Amazon cloud infrastructure which is significantly more likely to cause problems than ISAM tables.....

[nctnico]

99.99% is still over to 3.5 days of outage per year!

Err, your math is several orders out, it's 0.0365 days (0.8 hours)

You are right! But all in all the EEVblog is out for at least several days if you add it all up so that is why I didn't detect the error.

[EEVblog]

Apart from the dodgy maths, trading systems are looking at 5 9's (or better) or zero point of failure and spend the money to achieve it... hardly think Dave needs to worry about that kind of expenditure for his forum and irrespective of what Dave does with his software config he's still reliant upon the Amazon cloud infrastructure which is significantly more likely to cause problems than ISAM tables.....

I don't use the amazon cloud, I have a dedicated Xeon server box in a bunker somewhere in Texas.

[EEVblog]

You are right! But all in all the EEVblog is out for at least several days if you add it all up so that is why I didn't detect the error.

Shit happens, you can't easily and cheaply avoid it.

[SeanB]

Most of the server downtime generally is fixed fast, or at least within 2 hours from my perspective and times of use. All in all the biggest outages are mostly Cloudflare shitting itself locally, or a local interruption which drops connectivity. Migrating or moving is something I think Dave really will only do as a last resort, though I do hope he has at least tested his disaster recovery by at least trying to restore a full backup to a test server, probably easiest to do using a few small PC units running on a closed network, one as simple DNS, pop and NTP to emulate the real internet, the restore server and then a machine to test from, so he can restore the full configuration and emulate the cloudflare requests and such, and see if the restored server reacts correctly.

No good having a backup of the data if the configuration is all back to blank or the default sets, it is a massive pain to get it all the same again afterwards.

[StuUK]

Apart from the dodgy maths, trading systems are looking at 5 9's (or better) or zero point of failure and spend the money to achieve it... hardly think Dave needs to worry about that kind of expenditure for his forum and irrespective of what Dave does with his software config he's still reliant upon the Amazon cloud infrastructure which is significantly more likely to cause problems than ISAM tables.....

I don't use the amazon cloud, I have a dedicated Xeon server box in a bunker somewhere in Texas.

Apologies, must have misread something somewhere about you using Amazon... which probably explains why you've got such good uptime

[EEVblog]

though I do hope he has at least tested his disaster recovery by at least trying to restore a full backup to a test server, probably easiest to do using a few small PC units running on a closed network, one as simple DNS, pop and NTP to emulate the real internet, the restore server and then a machine to test from, so he can restore the full configuration and emulate the cloudflare requests and such, and see if the restored server reacts correctly.
No good having a backup of the data if the configuration is all back to blank or the default sets, it is a massive pain to get it all the same again afterwards.

The backups are standard WHM backup requests of the entire server files + databases, stored both locally on the server on a separate drive, and also Amazon S3.
So if the WHM backup system doesn't work, then I suspect there will be millions of other systems that don't work either. No I have not tried to restore the 25GB backup file.
Que everyone saying how I'm doing my backups wrong in 3.. 2.. 1..

[EEVblog]

Most of the server downtime generally is fixed fast, or at least within 2 hours from my perspective and times of use. All in all the biggest outages are mostly Cloudflare shitting itself locally, or a local interruption which drops connectivity.

Correct.

[RGB255_0_0]

99.99% is still over to 3.5 days of outage per year!

Seriously, have you any idea about just how good that is in the real IT world..... most service providers never achieve it even for critical systems....

That would not class as mission-critical. Heck, if your Internet was down for more than 12 hours you'd be on the phone to your service provider. With RAID redundancy, hot-swappable hardware, clusters across different countries and UPS 3 days is far too long, even over a major holiday like Christmas.

[RGB255_0_0]

Making things more cacheable would reduce server load,
Maybe if possible show all logged out users a html version that is refused when you come to a maximum of once per minute. (cache)

The server goes through Cloudflare, and has caching of various sorts, about as good as you can get without going to a multiple distributed server database structure thingo (I don't know the correct term). The server has very little real load, currently:

Not really wise to disclose that info if it's actually yours. There could be disclosed bugs not patched

[SeanB]

though I do hope he has at least tested his disaster recovery by at least trying to restore a full backup to a test server, probably easiest to do using a few small PC units running on a closed network, one as simple DNS, pop and NTP to emulate the real internet, the restore server and then a machine to test from, so he can restore the full configuration and emulate the cloudflare requests and such, and see if the restored server reacts correctly.
No good having a backup of the data if the configuration is all back to blank or the default sets, it is a massive pain to get it all the same again afterwards.

The backups are standard WHM backup requests of the entire server files + databases, stored both locally on the server on a separate drive, and also Amazon S3.
So if the WHM backup system doesn't work, then I suspect there will be millions of other systems that don't work either. No I have not tried to restore the 25GB backup file.
Que everyone saying how I'm doing my backups wrong in 3.. 2.. 1..

No, that is a good backup. The whole kitchen sink is good, though hopefully there is more than a single copy, so you can at least have the last week, month just in case. If you do Amazon Glacier then the tape store will be cheap, and you can simply delete the earliest one if you are hitting the limits of the price you want to pay.

At work we do a similar, DLT drive to copy the RAID to tape, with a 2 week tape rotation to an off site secure storage, plus the server warns if a drive goes failed, when there is a mad scramble to drop in a new one fast to do a slow rebuild. Luckily load is very low for the most part, most usage is the actual backup itself which might get the jet turbines to move off the lowest settings for speed.

[nctnico]

though I do hope he has at least tested his disaster recovery by at least trying to restore a full backup to a test server, probably easiest to do using a few small PC units running on a closed network, one as simple DNS, pop and NTP to emulate the real internet, the restore server and then a machine to test from, so he can restore the full configuration and emulate the cloudflare requests and such, and see if the restored server reacts correctly.
No good having a backup of the data if the configuration is all back to blank or the default sets, it is a massive pain to get it all the same again afterwards.

The backups are standard WHM backup requests of the entire server files + databases, stored both locally on the server on a separate drive, and also Amazon S3.
So if the WHM backup system doesn't work, then I suspect there will be millions of other systems that don't work either. No I have not tried to restore the 25GB backup file.
Que everyone saying how I'm doing my backups wrong in 3.. 2.. 1..

I just assume there is some history build in. Say you backup a completely non-working database... At one of my employers I lost about 2 weeks of work due to the server crashing and a wrongly implemented backup scheme (on tape) which had a hole in the history. Guess where my work dissapeared into.

[Brumby]

I wasn't going to say anything, but nctnico raised the topic.  I know this is not going to be enthusiastically received, but it applies to anyone who does backups .....

Do you know your backups will have everything you need to recover?  (Not just the things you thought of.)

The only way to really know is to take your backups and restore onto a blank machine.  If you haven't done that exercise when it isn't required, how do you know it will work when it IS?


I'm not jumping on anyone's head and I'm not casting any aspersions on Dave.  This is just a question - and one that opens up the much bigger topic of Disaster Recovery Planning.

[nctnico]

One of the reasons I have my backups on active hard drives (spread over several devices) nowadays is because I can check them very easely for completeness.

[StuUK]

99.99% is still over to 3.5 days of outage per year!

Seriously, have you any idea about just how good that is in the real IT world..... most service providers never achieve it even for critical systems....

That would not class as mission-critical. Heck, if your Internet was down for more than 12 hours you'd be on the phone to your service provider. With RAID redundancy, hot-swappable hardware, clusters across different countries and UPS 3 days is far too long, even over a major holiday like Christmas.

Think E2E not just hardware.....

[Brumby]

One of the reasons I have my backups on active hard drives (spread over several devices) nowadays is because I can check them very easely for completeness.

The fact that you have backups on active drives does address the one somewhat important and seldom thought about issue: "Can you be sure your backups are readable?".  That's one issue you shouldn't have.  But, all too often, people will assume that they will be able to just grab their backups and everything will be there and readable.  This is particularly a risk if you use flash drives.  You're not going to be happy if your "32GB" flash drive is actually a hacked 4GB one (that wasn't checked before use).  You won't get any write errors during the backup, but the data available will be a lottery.


But here's my question: "How do you know they are complete?"  I'm not picking on you - this question applies to everyone who does backups.

Data like names and addresses, inventory, sales and accounting is the obvious - but the list of candidates is much more than this and includes.

• Program installation files
• Program updates
• Program patches
• Product keys
• Parameter and configuration files
• Operating system installation files (Anybody running XP?)
• Operating system updates
• Operating system patches
• Network configuration (routers and such)

I'm sure people could add a few other things if they sat down and thought about it.  You aren't really going to know unless you do the recovery exercise.

[Brumby]

I'll also go just one more step into the DRP dilemma....

Do you have any hardware that is necessary to run a particular application?  For example, in the case of a dongle that gets lost in a fire, what will you do?


Apologies ... I'm getting a bit off topic.

[EEVblog]

No, that is a good backup. The whole kitchen sink is good, though hopefully there is more than a single copy, so you can at least have the last week, month just in case.

I have the last 5 days, plus twice a month.

[EEVblog]

I wasn't going to say anything, but nctnico raised the topic.  I know this is not going to be enthusiastically received, but it applies to anyone who does backups .....
Do you know your backups will have everything you need to recover?  (Not just the things you thought of.)
The only way to really know is to take your backups and restore onto a blank machine.  If you haven't done that exercise when it isn't required, how do you know it will work when it IS?

Because it's the standard WHM backup that backs up all files and databases. How many millions of systems must use the WHM backup?
If it doesn't work then I'm sure that would be pretty big news.
It is also the system that the host uses to recover is something goes wrong.

[EEVblog]

But here's my question: "How do you know they are complete?"  I'm not picking on you - this question applies to everyone who does backups.

Looks, here is the level of redundancy and backup on the EEVblog dedicated server and the levels of how shit would hit the fan:

1) Server uses RAID-1, so an exact disk copy is always on hand should one drive fail. Yes, I know RAID isn't backup, blah blah, but it keeps the system working. This is monitored by the web host company and drives replaces within hours of a fault. I believe they also have potential fault detection of some description to catch thing before they get bad.
So for this to fail, either both drives have physically fail, or something deliberate has to happen to nuke the server software and both copies.
Not a single issue with this from day 1

2) WHM backups of the entire server files and all the databases are done daily, Last 5 days worth, plus twice a month for two months. These are stored locally on RAID-1 system, so the host can use these to recover the server and/or database should something happen to the server, the database craps itself, files got deleted etc. Once again, never had to resort to this, because the first backup system takes care of failed drives.
Even if the latest backup got trashed and started to propagate through the daily backup system, it's almost inconceivable that not a single forum user wouldn't notice something is up, threads are missing or corrupted for two months that it would take to cycle through the backups.

3) Those same WHM backup files also get mirrored onto Amazon S3 (currently one big 25GB uncompressed TAR file (maybe this isn't the best option, maybe i should just copy the zillion files individually?), so even if the entire RAID-1 system goes down, the server building burns down etc, those backup are still available off-site.

If we ever had to resort to reinstalling the Amazon backups then that would likely be a lot of work, but no data should be lost apart from recent posts up until the last backup.

[EEVblog]

BTW, you might have seen the forum for down for a bit just now, it was me optimising database tables.

[Brumby]

Actually, I wasn't thinking about the EEVBlog with my comments - just a general comment about backups in the wider community.  This tangent was getting away from the topic at hand, which is why I offered the apology.

[EEVblog]

I think gnif talked about maybe having some sort of mirrored redundant server system at one stage, but it was messy.

[EEVblog]

If I read Brumby's comment correctly it is the difference between "hoping" you can restore from backups and "knowing" it works because you have actually tried it.

And trying it once doesn't necessarily mean it'll work in a years time...

There is also a difference between having a backup that simply won't lose your data but may require work to recover. An having a backup that is trivial to recover from in a moments notice.
I liken my current backup system to the former if SHTF. Although it is the later if it's just a drive failure, or a system recovery, my host will take care of that for me.

[EEVblog]

Does it get you a running hot backup that you can switch to in the event of a prolonged outage at your current hosting service? All without touching a single backup. Or transferring GB's of data across a network.

That was the idea, yeah.
I don't recall details, but it involved some complex thing that didn't involve changing the DNS (which can take days to propagate and negates the point of the whole thing)

I could survive without the forum. It's not like it is a bank ATM network. I keep a months cash against that eventuality.
Since you will be footing the bill it is strictly your decision.

That's the whole point, the world isn't going to end if the EEVblog forum goes down for a few days. It's happened before when changing servers etc and no one really seemed to mind.

[Brumby]

Again, my comments here are general ones..............

If I read Brumby's comment correctly it is the difference between "hoping" you can restore from backups and "knowing" it works because you have actually tried it.

And trying it once doesn't necessarily mean it'll work in a years time...

That's very true, which is why some organisations go through the exercise periodically ... but going through it at least once will show you if you've missed anything in your original thinking.

There is also a difference between having a backup that simply won't lose your data but may require work to recover. An having a backup that is trivial to recover from in a moments notice.

That is also very true - and the decision on which way you go will depend on the risk.  The dire situation I mentioned earlier demands a ready-to-rock solution - but the EEVBlog forum won't fold it it took several days ... although there would likely be some who could suffer withdrawal in the meant time.


The whole subject is one where everybody will have different needs with different priorities and different risks - resulting in different solutions.  My aim was to generally show how broad the subject is, not to point fingers.  For the record, from what Dave has said, the forum backup situation seems well in hand.

[nctnico]

3) Those same WHM backup files also get mirrored onto Amazon S3 (currently one big 25GB uncompressed TAR file (maybe this isn't the best option, maybe i should just copy the zillion files individually?),

Rsync works very well for backing up individual files on seperate 2 systems because only new and changed files get copied (and if you want deleted files on the original get deleted in the copy as well). Many NAS systems support Rsync for backup purposes.
A log from rsync also serves as a check that the backup is identical to the original.

[SeanB]

The TAR will in any case be compressed by Amazon in putting it into Glacier storage, so there is really no need to compress it, they will do it for you for free and with something they know how to use. The files in the archive will in any case be safer in the big blob than spread across a whole filesystem, as the TAR will have rudimentary error detection, and it is more likely the file is a non data file so can simply be copied from another TAR in case needed.

Good enough I think, and a lot better than many do. Dave obviously has lost data before. Having individual files is an easy way for insidious data loss to delete things, but the blob can easily be checked just by using an archive manager to open it, which will scan and make sure checksums are correct at the least.

[nctnico]

Well, try to retrieve a file quickly from a 25GB blob and you'll notice having a single blob isn't a good idea. In general I agree copying single files isn't a good idea with generic tools like the copy command or -shudder- Explorer which are prone to losing files and have no proper reporting. However with a tool like Rsync which checks the integrity of every file backing up to a collection of single files suddenly isn't problematic and has the advantage of being able to access a few files quickly.

[SeanB]

Well, try to retrieve a file quickly from a 25GB blob and you'll notice having a single blob isn't a good idea. In general I agree copying single files isn't a good idea with generic tools like the copy command or -shudder- Explorer which are prone to losing files and have no proper reporting. However with a tool like Rsync which checks the integrity of every file backing up to a collection of single files suddenly isn't problematic and has the advantage of being able to access a few files quickly.

Yes, but you can mount an archive as a drive. Not as fast as a real disk, but usable. It is just another block device.

[kcbrown]

Well, try to retrieve a file quickly from a 25GB blob and you'll notice having a single blob isn't a good idea.

That would be a reasonable thing to conclude at first glance, and would definitely apply to a compressed tar archive.

But we're talking about an uncompressed tar archive here.  The tar utility is smart enough to do direct seeks to skip files in the archive.  Which is to say, what it does is read a header, determine if the filename matches what you're after, and if it doesn't, then it skips directly over the data for that file (it can do this because the header includes the size of the file) and goes to the next header entry.  Lather, rinse, repeat until you hit the end of the archive.  I know it does this because I just did an strace on tar for precisely that kind of operation, and I can see it doing a whole bunch of lseek system calls as it processes the archive.

With that approach, with a 6 gigabyte archive, it takes seconds to extract a small file near the end.  There's no need to mount the archive as a drive if all you're after is extraction of a file.  That makes other operations simpler, so there's some advantage to it and it's a nice capability to have.

Dave's backup approach here is really an excellent one.

[Macbeth]

It's amazing how many people are banging on about all the minutiae of disaster recovery and what database is best. Yet the only server downtime I have witnessed has been due to other stuff, notably DDoS attacks and spamming by a certain somebody, cloudflare stuff, etc. Nothing to do with MySQL.

It's clear that EEVblog is more than covered in a disaster situation and also isn't running the worlds stock markets so even a few days outage is neither here nor there...

The only issue with the present MySQL database that is actually important is the stupid character set used - is it 7 bit ASCII or something?

The forum software (SMF) works perfectly with UTF-8 characters. Indeed I just installed the latest to my own server and greek, cyrillic, chinese all work right out of the box just by specifying "UTF-8 general" when creating the SMF database.

This is a no brainer to resolve.

[Macbeth]

Example:

I do like my Soviet ??-12? nixies, anyone got an original datasheet?

How do you pick an NTC thermistor? They are given a nominal resistance at 25°C, say 1000 k?, but also have ? value. How do I use it?

Can anyone find me an English spec for my Maynuo M9710? I can only find M9710?? on the site in the Chinese section.

Ok. I'm hungry now - After watching EEVBlog #873 I am going to skewer two nails into my Brätwurst sausage and connect to 240 VAC.

PS. I don't need any answers to my questions. This is just a test / example

OK - I click Preview and everything appears ok...




Now let's Post and see what happens...

ETA: FAIL! Wah Wah Wahhh.... the original post has replaced all the interesting characters with ? marks. But at least my sausage survived the character mashing.

[dadler]

I built, developed the software for, grew, maintained, and co-owned a massive online forum (that still exists today, but will remain nameless-I am no longer affiliated). Development started in 2000-2001.
 
At the time I left the project, it was at least three orders of magnitude larger than this forum in content and membership.

I used MyISAM for the primary thread and post tables. When InnoDB became available in MySQL, I test drove it and query times and database load tripled (this was 2003 era). I also tried PostgreSQL, which was even slower. Yes, I had caching layers, side-band search indexing, scheduled aggregation job queues, and all sorts of other necessary optimization layers to make it all work. But I used MyISAM for the primary content.

For something like a forum, which is almost entirely reads, MyISAM is really fast, especially on dedicated hardware with fast disks. Forums are not banking systems.

We maintained consistent backups and never lost any appreciable quantity of data over the entire time I was associated with the project.

RDBMSes are tools, and they, and their table types/configurations all have tradeoffs.

I see nothing wrong with running a forum like this on MyISAM.

If I were to reimplement something like this forum from scratch today, I still might use MyISAM for the primary content tables.

Now in the days of the "cloud", and things like Amazon AWS, Google App Engine, Heroko, etc there are many choices. There are overkill enterprise solutions from Oracle and others. There are NoSQL databases like Cassandra, MongoDB, Courchbase. In big projects today, I use DynamoDB, Amazon RDS, Amazon Redshift, and Redis.

But this is a PHP web forum, and with proper backups, MyISAM tables are a perfectly acceptable solution.

Many different tools, many different jobs, many different trade-offs. There is no one-size-fits-all solution.

[EEVblog]

The forum software (SMF) works perfectly with UTF-8 characters. Indeed I just installed the latest to my own server and greek, cyrillic, chinese all work right out of the box just by specifying "UTF-8 general" when creating the SMF database.
This is a no brainer to resolve.

And hoes does one do that?
Is it something a dummy like me can do without screwing up?

[Brumby]

I don't know the ins and outs of the exercise, but there is one point that stands out and it begs a question....

The point is this

... just by specifying "UTF-8 general" when creating the SMF database.

which indicates to me when originally setting up a forum, it is simply a matter of specifying that option.

But the question is: Will there be any issues migrating the existing data of this well established forum?

[Zbig]

The forum software (SMF) works perfectly with UTF-8 characters. Indeed I just installed the latest to my own server and greek, cyrillic, chinese all work right out of the box just by specifying "UTF-8 general" when creating the SMF database.
This is a no brainer to resolve.

And hoes does one do that?
Is it something a dummy like me can do without screwing up?

Haven't tried this myself and don't recommend you just go ahead and try it on a live "production" DB without testing, but based on this: http://stackoverflow.com/questions/6115612/how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8
it seems like a matter of single ALTER statement. I'm not sure whether you have to explicitly make SMF aware of this change afterwards. Perhaps it'd be best to ask on SMF forums/support? Seems like a fairly common problem to me.

[Macbeth]

Cheers Zbig. I decided to have a go. This worked for me typed into a bash session:

Code: [Select]

DB="eevblog"; USER="root"; PASS="hunter2"; ( echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_general_ci;'; mysql "$DB" -u$USER -p$PASS -e "SHOW TABLES" --batch --skip-column-names | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) | mysql "$DB" -u$USER -p$PASS

Some caveats. I could only install SMF 2.1 beta on my Ubuntu 16.04 LTS, due to the deprecation of the old versions of PHP and MySQL. SMF 2.1 actually uses utf8 by default so I had to use pretty much the same as above to "downgrade" to latin1_swedish_ci before proceeding on entering some messages and "upgrading" to utf8 again.  Of course I could only get the extended characters working properly after the upgrade.

Dave, take a backup and run the above on that just as a dry run to see if any conversion errors and also to time the whole thing? I imagine it could be pretty slow! Then set up another SMF in apache to test it with?