Re: [PLUG] Thoughts on MySQL from a developer

[brent timothy saner via plug <plug@lists.phillylinux.org>]

On 12/5/21 17:25, Keith C. Perry wrote:
> Not sure I was say Pg is a memory "hog" outright but relative to MySQL I get what you mean.  Scaling issues are relative too- 100M clients with 1% concurrency is less load than 100k clients with 20% concurrency.  Most people obsess about this in the application and storage systems when they really should be looking at their network.  I find concurrency issues are usually approached top down instead of bottom up.  Memory isn't the premium it used to be and if you're pooling, the necessity for having top of the line hardware for each node goes away (again, networking, networking, networking).
> 

I'd definitely say network has a much higher influence than any other
factor in the modern context. But keep in mind:

- this memory usage per client is a *minimum* outside of any queries, and
- this is bad news for scaling in general, regardless of actual scale size.

Sure, RAM is cheap. Boards also have RAM limits. :)

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

That's fair, yeah. Clustering with hot slaves is a good way around this
for MySQL (well... hell, any high-availability needs), but PostgreSQL is
definitely on top with this.

Now, that being said, I *personally* haven't hit any corrupt tables in
MySQL that weren't the result of my own failure (e.g. disk filling up,
for instance).

As for special care during backups, shouldn't be necessary - though I'm
not sure what you mean by "special care".

Between the mysqldump options I have here[0] and doing a dump
per-database[1], you should be set for any kind of disaster.


> I'm surprised you didn't mention SQLite for basic query needs.  That running on top of XFS is pretty sweet.  BTW, I "wrote" (i.e. created a database) an inventory system for a client so I could track software and hardware assignments in MySQL.  Only thing I use to access it is phpMyAdmin and its works perfectly for my needs since it is not client facing.  Its one of the few things I don't see moving to Pg so I'm definitely not knocking MySQL 100%.
> 

You know, I thought about it (re: SQLite) but it's not really applicable
to the point I was getting towards - SQLite is great for small purposes!
It flies, and it's STUPID easy to create a DB right entirely in memory
in most libraries. BUT it doesn't have a concept of users, access
permissions, etc. nor of a multi-client approach. It doesn't even run
persistently via a UDS or something, and obviously networking is right
out. So I purposely didn't mention it because it felt a little
out-of-place in the comparison.

And yep, I think you understand my point. For a large amount of database
usage out there that needs mutli-client access, MySQL is fine and
PostgreSQL is kind of just overkill. Don't get me wrong, I think
PostgreSQL is great and really excels at things. But there are things
where yes, MySQL really is going to get you more bang for your buck and
fits the project spec better and quite frankly, I'm tired of the tech
community pretending that's not the case and PostgreSQL is a magic bullet.


[0]
https://git.r00t2.io/BorgExtend/tree/plugins/mysql.py?id=7ef21e8059f4d1db817a5552aaca2f702c89e20e#n35

[1]
https://git.r00t2.io/BorgExtend/tree/plugins/mysql.py?id=7ef21e8059f4d1db817a5552aaca2f702c89e20e#n60
___________________________________________________________________________
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