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

[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.