Keith C. Perry via plug on 5 Dec 2021 14:26:01 -0800


[Date Prev] [Date Next] [Thread Prev] [Thread Next] [Date Index] [Thread Index]

Re: [PLUG] Thoughts on MySQL from a developer


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

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.

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


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

----- Original Message -----
From: "brent timothy saner via plug" <plug@lists.phillylinux.org>
To: plug@lists.phillylinux.org
Sent: Sunday, December 5, 2021 4:37:18 PM
Subject: Re: [PLUG] Thoughts on MySQL from a developer

On 12/5/21 15:50, JP Vossen via plug wrote:
> Here are some thoughts on MySQL from a developer that just left Oracle
> and the MySQL team.  They are...interesting.
> 
> http://blog.sesse.net/blog/tech/2021-12-05-16-41_leaving_mysql.html
> 
> I wasn't surprised that the code was "not good" but I *was* surprised at
> how not good it was and the flat out statement: "MySQL is a pretty poor
> database, and you should strongly consider using Postgres instead." 
> I've never quite understood how MySQL got the mindshare it did, but
> there are an awful lot of projects that Just Work with MySQL and either
> don't work or require contortions for Postgres.  Given a choice I'd
> always go Postgres.
> 
> Later,
> JP

Sausage is always gross when you see how it's made, so grain of salt.

That being said, a couple points:

- I'm absolutely positive "the Oracle effect" was in full swing on MySQL
(proper)'s codebase.

- MariaDB has gone a long way in fixing some terrible design decisions
MySQL proper ("Oracle MySQL"?) has made, and historical messes before
the acquisition.

- PostgreSQL is still a memory hog for concurrent clients. If you're
scaling horizontally, your DB box will need swathes of RAM as each
client connection (*each connection*) requires about 10MB RAM on the DB
box for PostgreSQL. Your program opens a pool of 50 workers? That's
500MB consumed on the DB server. Scaling that out to multiple app nodes;
say, three nodes? Instant ~1.5GB consumed *before you start even running
queries*.

- If you need something simple and relational that's *really* performant
on reads but still.. networkable/not something local on the filesystem,
nothing beats MySQL with ISAM tables. (And yes, the argument can be made
that ISAM shouldn't be used, and there are valid concerns, but if
performance is your primary concern...) - you know, like 90% of webapps
out there would benefit from.

- It is very, very common that something supports both PostgreSQL and
MySQL. It is very common something only supports MySQL. It is *extremely
rare* that something only supports PostgreSQL.


Do I use PostgreSQL if I need complex queries/in-engine operations, or
extensive type constraining, etc.? Absolutely. It's fantastic for that.
It fulfills a certain set of needs *wonderfully*.

But would I use it for something like a basic inventory system, or a
pub/sub backend, etc.? No. Stick with MariaDB for that. There's no need
to feed the PostgreSQL beast if you don't need it shackled in your basement.

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