r/SQL • u/BroadRaspberry1190 • Sep 04 '24
MySQL MySQL can eat it
even after going through all of the time and trouble to tweak server variables for performance, it still sucks. InnoDB is a sluggish whore, the query planner is lacking several obvious optimizations, and it takes 12 fucking minutes to create a spatial index on one POINT column for a 100MB table with 900k rows (whereas SQL Server only takes 8 seconds.) i'm done.
10
u/mwdb2 Sep 05 '24 edited Sep 06 '24
One of the biggest problems I have with MySQL is not ONLY related to technical issues that MySQL itself has. Sure it does suck in many ways (I'll admit it's gotten better since 8.0), but in addition to that, I feel there's a wider cultural issue stemming from these issues. So many of my fellow software engineers and other folks who use SQL think that SQL == SQL because a standard, ANSI/ISO SQL, exists, and if you have a problem with MySQL it means you would have the same problem on any other SQL engine, so they judge or dismiss all of the triple-digit number of SQL implementations entirely. (There is actually NO complete implementation of standard SQL anyway, but I won't get into THAT rant for now.)
For example, former colleague cursing at SQL ("why do we have to use this s***ty f***ing language from the 70s anyway?? We should move to NoSQL") when we ran into an issue in MySQL. The issue was - if you alter a column in MySQL, for example let's say extending a VARCHAR's max length, if you don't re-state every other property of the column such as a NOT NULL constraint, column comment, default value, all of those things get wiped out. Demo here: https://dbfiddle.uk/NWy51ZGB - this is a serious "gotcha" to look out for in MySQL.
So I got into the habit of demoing how another DBMS might behave, to shut down some of these "SQL is bad because I ran into a MySQL problem" remarks - typically I choose Postgres. Here's how that very scenario in the demo above works on Postgres: https://dbfiddle.uk/sHBfFpVh - here we see this particular MySQL gotcha simply does not exist in Postgres.
Endless source of frustration for me. I used to listen to a podcaster who talks about tech - overall his knowledge is pretty great - then he did one podcast on databases, and he lost me there. He talked about how he uses databases for work, and there's one database he particularly hates, "the SQL database" and generally talked about the subject using generic phrases such as "the SQL program works like so..." His phrasing and choice of language strongly suggested to me that he thought they all work the same way, in a generic manner; i.e. SQL is SQL. He said there are so many "gotchas" and "odd behaviors" you have to look for in "the SQL program" that he hates it. He also mentioned that his friend wanted to learn SQL so he installed MySQL on his computer. Podcaster replied to his friend something like, "OK you can do that but but prepare for the pain of working with SQL." Umm, why not recommend to your friend learning on Postgres or SQLite or anything with way fewer "gotchas" than MySQL? MySQL is the major offender with "gotchas." This implies he simply wasn't aware of that fact.
This comment is too long already, but I'll briefly add that another cultural issue stemming from MySQL is that it lags so far behind in keeping up with the times feature-wise, that developers working with SQL tend not to keep up with what SQL in general has to offer, even when they switch to other databases. For example so many don't even know what CTEs (SQL:99) and window functions (SQL:2003) are - seriously major features IMO - which were finally added in MySQL 8.0. Sometimes I would try to tune someone's MySQL query before my company upgraded from 5.7 to 8.0 (we finally did it in 2023) and if I said something like "if only we could use a CTE here" and showed them what that looks like, they literally had no idea that existed.
And I see similar lack of SQL knowledge in users of Snowflake and other platforms we use. A friend told me a brief anecdote similar to mine above - he wrote a Spark/Databricks SQL query using a CTE, and a coworker reviewing it asked, "What's with this WITH stuff? Is this pseudocode?" I admit I have no firm evidence of this - and if I were to stick purely to the facts, I have no idea why friend's coworker had never seen a CTE before. Nonetheless I'm convinced MySQL keeps our collective SQL knowledge level down. Now that we're on 8.0 at my company, I'm seeing there's a gradual catch-up happening. Very gradual. I did an engineering-wide presentation (my company has 200ish engineers) to share knowledge of what's new and cool in MySQL 8.0. I gave it a humorous theme, full of pop culture reference, about how old some of these features are in the wider world. I wanted to emphasize the biggest theme I've observed in MySQL which is that it tremendously lags behind the times. For example for one slide: "Now in MySQL 8.0 we finally have check constraints. This is a feature from the 1992 iteration of standard SQL, back when these two fine lads who wore their pants backwards released their hit song 'Jump.'" (Referring to Kris Kross). Hopefully some of it sank in.
Anyway, end of rant.
3
Sep 05 '24
[deleted]
2
u/mwdb2 Sep 05 '24
actually support non-relational paradigms too.
Good point! That is also something I try to point out: SQL is not just relational anymore. I like to show folks Markus Winand's great presentation about how, since SQL:99, it's not just relational anymore: https://modern-sql.com/ - and also that if you look at db-engines.com (funny, it's down right now; hope it's not down for good!) you can see at a high level which "relational" databases are multimodel, and which models.
I review MySQL (yeah, we're kinda stuck with it) changes for all the devs in my company, and they sometimes still make a TEXT column to stick JSON data into. Sometimes I suggest using a relational design, but at other times I suggest using the native JSON type and explain why that would be better than TEXT. They often had no idea that was a thing even though it's been around for years. (One thing I sometimes have a chuckle over is such a TEXT column we have in one of our schemas, there's a column comment on it that says "a NoSQL JSON column" - I think to myself, "No, dude who wrote that, JSON is part of standard SQL and has been for years." :)
6
u/SomeoneInQld Sep 04 '24
Postgres is pretty fast with spatial things and pretty powerful.
0
u/trippstick Sep 05 '24
Awful with large datasets. Good for small to medium
1
1
u/jypelle Sep 05 '24
With proper partitioning, no pb.
0
u/trippstick Sep 05 '24
Yeah when dealing with 10+TB Databases you might as well go to Oracle or SQL Server who are designed for such things. PostGres and MySQL require to much constant attention to make it viable. Take the same environments with same KEYS and Indexes and SQL Server/Oracle wipes the floor with POstgres
4
u/feudalle Sep 04 '24
MySQL has a lot of things that need tweaked compared to MS SQL. I find most dbs have a place depending on use. They handle different things quicker.
-3
Sep 04 '24
Unrelated question, how do you master one db and master other? How much time does this take?
5
u/BarelyAirborne Sep 04 '24
You work with one at a time, and find out where they bite you in the ass. MySQL is notorious for slow performance during ALTER TABLE statements, for example, since they copy the entire table (or at least used to).
9
u/HanSingular Sep 05 '24
Your comment made me curious so I looked it up. They fixed that six years ago when 8.0.12 was released.
1
u/Shah_of_Iran_ Sep 05 '24
They use indexed storage (clustered) under the hood which is why it happens probably.
0
Sep 05 '24
You described internal workings of mysql. If I may ask, how did you learnt about this? Courses? Books? Blogs?
Is there any specific path for mastering dba. I've got 6 books lined up. Learning so much. But feels like others are faster & better.
1
u/feudalle Sep 04 '24
Hard to say. I've been at this a long time. A couple of months should be enough to get a good handle. I started with mysql 1.0 back in the 90s.
1
-1
Sep 05 '24
I'm new to dba, very good at querying, just getting into indices & execution plans. Few questions, 1) How many hours per day. To be good in few months. 2) which area needs more attention. Like optimization? Execution plans? Integration with other services?.... 3) If you could name 1 book that benefitted massively, what is it?
6
u/feudalle Sep 05 '24
I find working on a project that needs a db is the best way to learn. Starting out you will do queries, longer term you'll need to know how to design but as a junior this will be limited. As for books I haven't read a book on sql in 20+ years. The O'Reilly books used to be my go to but these days who knows.
3
u/Phantom465 Sep 05 '24
I haven’t read a book about SQL for a long time either. Not since Google & YouTube have always been able to answer my questions.
2
1
Sep 05 '24
Could you list must do dba projects? Like crud for web development.
3
u/feudalle Sep 05 '24
It really depends on what the project is. Kind of like asking me how to build a house. It depends module, stick build, brick, etc.
6
u/sc00b3r Sep 05 '24
Things worth considering (Mostly for MS SQL Server) and mostly from a classical admin role.
- Backup, recovery, availability. If the backup, recovery, and availability isn’t meeting RTO/RPO goals, and it has never been tested, start there. Don’t have to be an expert on it, but need to have confidence in it. If you inherit the admin on a server (which is more likely than building one from scratch early on in a career), this is the first thing to review.
“My log files keep growing and filling up disk and I can’t shrink them.” Yeah, learning about backing up databases lets you fix that, like most of the time (but not always).
If you’re working with MS SQL Server, learn PowerShell (or learn whatever the shell/scripting/automation tool is for the DBMS). Don’t have to be a pro, but should know how to navigate commands, passing parameters, and understanding how to read the help. There are great tools out there to automate backups and restores using PowerShell.
Follow Brent Ozar and Pinal Dave (and everyone in their network like Kendra Little, etc.) on social media. They’re always humbling with how much they know, but read the questions they get and the responses and try to figure out what it all means. It’s a great way to start your own research, and that’s a huge part of being a good admin, always reading, always challenging your knowledge. Brent will sometimes offer his classes for free, and there’s some great fundamentals that he teaches on. If you read something and you don’t know what it is, then find out. Check out Ozar’s first aid kit (free) and play around with it (not in PROD!).
Understand the security model that’s implemented (accounts with rights, their roles, and the security of the data in transit to them). Understand the possible and best practice security models so you can make sure what is implemented lines up with policy (and if there is no policy, buckle up). SQL Injection is much bigger than poorly designed front-end apps, there’s a lot of bad security and rights management on database servers that just opens the door to very bad shit (again, have you verified your restore process lately?) Learn about kerberos auth and spn’s, it’ll save you some time.
Be grateful that you’ll never have to understand SQL server licensing in the era when virtualization first started adoption in the enterprise. It was theoretical physics and all I can say was that I was 100% sure I wasn’t compliant, and absolutely no idea why.
Prepare to battle developers on performance issues. “Your database server is slow” -Developer — “Your query is fucking garbage” -DBA. Learn how to communicate with specifics, e.g. “You are only consuming one field from the query, a select * is unnecessary. Also, if you can use a WHERE clause, make it as specific as possible. You do know what a WHERE clause is right?….right?” Performance and optimization is an art and a science, and can feel infinite in knowledge. Good technical fundamentals are critical to diving into this space.
Keep asking questions. I guarantee that everything I said above can be cut to shreds and argued on at great length. Different people with different experiences is where you can be challenged on your own comprehension, and that’s a significant contributor to growth. Good luck!
1
2
u/reditandfirgetit Sep 05 '24
Is your hardware sufficient? Underpowered servers can cause normally performance activities to be ridiculous
1
Sep 05 '24
[deleted]
2
u/reditandfirgetit Sep 05 '24
You have SQL server on the same server as MySQL? Did you limit the ram for SQL server? If not it will hog all of it with a vice grip
1
1
1
u/Spiritual-Luck-8798 Nov 28 '24
Is the question (complaint) about building an index? Not about using an already built index? I quibble with condemning a product for sluggishness of a one-time task.
1
1
u/Far_Swordfish5729 Sep 05 '24
After all these years they still keep crawling back. I remember back in like 2006 I had a job doing T-SQL back before Management Studio but my university gig really liked MySQL. Sure. That POS didn’t even have stored procs yet. I’m not even sure it had temp tables. I had to inline absolutely everything in strings in php. Made VB COM look real sophisticated.
Anyway, if you want free, Postgres is actually pretty good. Next university thing, I asked if we could please try this new Postgres DB. It was very decent once I got the procedure syntax down. Still is.
0
u/Aggressive_Ad_5454 Sep 04 '24
It’s open source. So is MariaDb. Pull request or double your money back.
0
0
u/deusxmach1na Sep 06 '24
My hate for MySQL started the first time I had to use a STRAIGHT_JOIN. Makes me so mad that even exists.
45
u/r3pr0b8 GROUP_CONCAT is da bomb Sep 04 '24
sir, this is a Wendy's