| Keith via plug on 6 Dec 2021 10:15:27 -0800 |
[Date Prev] [Date Next] [Thread Prev] [Thread Next] [Date Index] [Thread Index]
| Re: [PLUG] Thoughts on MySQL from a developer |
You are welcome and also, WOW... you never know who is on list!Hi,
First a disclaimer: As some of you know, I'm the maintainer of phpMyAdmin (thanks for the mention, Keith!), so I have much more familiarity with MySQL than I do with PostgreSQL. None of my projects currently require the complexity of using PG, and as Brent said above, most things will work with MySQL but very few things will require only PG, so for me the ease of running only one outweighs the burden of maintaining both simultaneously. I'm not a MySQL apologist and think it was a positive change for distributions to switch to MariaDB, I just know MySQL better.
On Sun, Dec 5, 2021 at 5:26 PM Keith C. Perry via plug <plug@lists.phillylinux.org> wrote:
That said, I'll take Pg's data resiliency over MySQL's 1 billion times over. Last year when my Zimbra server crashed I lost a week's worth of data because the MySQL database would not open and could not be recovered. Fortunately, I had run a closed backup of the VM a week ago which saved my ass (otherwise it would have been closer to a 30 days loss). Apparently, MySQL still needs special care to be backed up while the system is up. As far as I can recall I've always using InnoDB tables instead of ISAM too (which are supposed to be more "durable"). Such issues for Pg were resolved 21 years ago. I'll admit there's a tricky balancing act no matter what you choose but personally, I'll always take database durability over performance and that skews me towards Pg out of the box.
According to the PostgreSQL manual, backups at the file system level should follow the same process MySQL does: (1) the daemon/service must be stopped and (2) take the entire data directory, not just one specific database (I'm simplifying a bit, when using no InnoDB tables, one can acquire a read lock and keep the server running, but it's a lot of "ifs" to ensure consistent data compared to simply stopping and restarting the server).
I still prefer to export SQL files to make sure I have a full and consistent backup, but it doesn't look like PG does anything special compared to MySQL to allow file system backups while the server is running. If there was some MySQL corruption in your attempt to restore, it strongly suggests that the server wasn't properly stopped, you didn't restore and replace the complete data directory, or the data was already silently corrupted at the time of the backup.
Yes the process is the same when things proceeding in ***normal*** order but that is not where issues arise. This is why I said in a subsequent email, "...it survives the typical bad things that can happen to systems better than better than MySQL.". I don't know about you but in my experience, servers are rude and they never ask politely if its a good time to crash 😎. So, where I spend my time is on cases where things do not proceed in normal order. My business does a lot with data protection architecture which gets into the weeds of operational resiliency and security. Clients don't care about when things go right, they care about being able to operate when things go bad and / or continue to be bad.
To that end, around 2001, I sent a lot of time playing with MySQL and PostgreSQL. This was also around the time I moved from ext3 to XFS for better file system durability metrics so I was spending a lot of time trying to break things. During that time I was on the Pg list and one of the things that can to pass was the use of write ahead logging,
https://www.postgresql.org/docs/9.6/wal-intro.html
which later lead to,
https://www.postgresql.org/docs/9.6/continuous-archiving.html
Both of these items help protect Pg from major loses during a crash and that what I'm most interested and have the most confidence in PostgreSQL. I've seen this work a number of times and I personally have not had that same level of experience with MySQL. No, MySQL doesn't always crash in a way that can't be recovered but regardless of proper back procedures (which again, I own dropping the ball in my situation). I can't remember the last time I lost data from a PostgreSQL ***crash*** and I can with MySQL.
Put it like this, even if I had had everything properly last
year, I would has lost about of day of data. That's better than 7
but it only changes my statement from 7 days to 1. I would have
still had to restore the system. So, I still lean towards
PostgreSQL because of personal experience unless there is
something very compelling, like phpMyAdmin for that inventory
project, that make a huge amount of sense. Regardless of what I
run though, I generally do two things for databases. 1) Online
database dumps in some regular order and then 2) closed complete
system backups (in my case, copying, snapshotting or cloning a
VM). That's complete protection and I can sleep easy. 👍
Apologies for the confusion and my definition of "special case"
but I think explained better now.
As an aside, I quite enjoy seeing this discussion here; I haven't had much time to participate on this mailing list and seeing some good discussion here is rewarding. Between work obligations and family, I'm rarely free during meeting times, so I enjoy reading these email threads.
Cheers,Isaac
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ Keith C. Perry, MS E.E. Managing Member, DAO Technologies LLC (O) +1.215.525.4165 x2033 (M) +1.215.432.5167 www.daotechnologies.com
___________________________________________________________________________ Philadelphia Linux Users Group -- http://www.phillylinux.org Announcements - http://lists.phillylinux.org/mailman/listinfo/plug-announce General Discussion -- http://lists.phillylinux.org/mailman/listinfo/plug