Hm. in MySQL 8.0.23 (yeah I'm kinda slow at catching up) they changed quite a lot.

- Hash Join implementation
- invisible columns
- InnoDB behaviour, eg AHI / IB
-....

https://dev.mysql.com/doc/relnotes/...

https://dev.mysql.com/doc/refman/...

Guess no more MySQL for me.

How should I trust a database whose QA seems completely non existent and where minor version releases change the complete behaviour of the database....

(it's not new per se, Oracle announced that with the 8 GA release minor releases can have new features, but I'm surprised they really keep it up with breaking... Their QA sucked before, but with the new features coming, MySQL / Oracle is dead to me. )

Will be interesting to see in which direction Maria DB goes.... So far it looks good, although they really should think about cutting ties with Oracle / MySQL as a lot of internals have quite diverted....

Fuck Oracle.

I'll add per MySQL docs as of 8.0.13 you're supposed to be able to comma separate bind-address.

But on my install on AWS EC2 Ubuntu 20 MySQL 8.0.22 I have tested that to fail and had to do 0.0.0.0 like the old days.

Maria DB is pretty much a drop in replacement for MySQL (I think)

I personally use Postgres for everything new.

To be fair, the changelog only mentions added feature (e.g. hidden columns can be selected now, more InnoDB ootions) or things that are AFAIK not available for clients (hashtable of hashtable joins).

I might have missed something, so please correct me if I did.

@sbiewald You misunderstood the changelog.

https://dev.mysql.com/doc/relnotes/...

MySQL 8.0.18 I think added an Hash Join Optimizer, which was known to be subpar.

MySQL 8.0.23 had the whole hash join implementation rewritten.

Hence under the categoy optimizer.

Simply put, every query utilizing a join might now run differently in regards to resource usage and time if a hash join optimization was possible.

It's the kind of thing that sounds nice (as in better performance), but looking at the QA issues and the impact of the change... Nope. That shouldn't be in a GA release.

@IntrusionCM Performance optimization really isn't a breaking change though

@12bitfloat Yes it is. When a server suddenly behaves completely different for a subset of queries, it's bad.

When suddenly resource costs don't apply anymore and the resources are overallocated, it's bad.

Database's should have something like a performance baseline in my opinion.

You expect a certain throughput for certain workloads. Changing this in a GA release is in my opinion hard to justify. Especially, when QA is known to suck and it might get changed - again.

Seems like I might be alone with my opinion, but when a minor release upgrade means that I have to carefully plan a migration and retest certain scenarios, so the increased performance which wasn't accounted for doesn't lead to more problems, it's fuckity to me.

Join's are common, this is a change at the core aka the optimizer and it most likely has a tremendous influence on high throughput systems.

@IntrusionCM I'm unfamiliar with servers but can you give a scenario where more efficient resource utilization leads to problems?

@homo-lorens If the new procedure works better in 90% of the cases but worse in 10%, and for whatever reason those 10% were faster before (and your application contains those cases), it may lead to worse performance after an update.

What @sbiewald says.

Another scenario is when you have services which are dependent on each other.

Eg. you have a nice farm of persister's storing data in ORM, a nice farm of crawler's fetching the data, processing and enriching it, then another farm of persister's storing it in eg. Elasticsearch.

When the crawler's suddenly run faster than before, you'll end up with a higher insert speed in Elasticsearch.

Sounds great. Except that most likely Elasticsearch can't handle it or the refresh on Elasticsearch indices suddenly takes longer and more resources as much more data was inserted than before.

As data can only be processed after a refresh, you now have a delay on when new data is available - best case.

Worst case: the rate limiting kicks in to prevent the ES servers from failing.

The persister farm for ES now runs slower. Causing the crawler's to run slower, too - they must run slower, as their output is what's overwhelming ES.

And now you have the fun of redesigning the scaling...

Most likely cutting down the crawler farm.
And then you have to explain why suddenly resources are available...

And prevent people from using them for shenanigans.

TLDRv more performance can be serious headache if there is a plurality of services with interdependent relationship.

One system running faster can mean death to the whole chain of services dependent on it's output

@IntrusionCM Not that I say you are wrong, but if your system breaks if one component runs faster, maybe your system isn't that well designed...?

I do know it is quite realistic ("organic growth"), but I don't if the fault lies at the database manufacturer.

@IntrusionCM I see how some specific case suddenly becoming slower can be harmful, but I don't really understand the second scenario.

If ES rate limiting kicks in due to higher request rate, doesn't that mean that the rate limit is higher than the previous request rate, so the limited rate will still be higher causing a net performance increase?

"...explain why resources are available and prevent people from using them for shenanigans" - I fail to see how the database dev should care about the fact that your management is so bad higher net performance causes internal friction.

I get the feeling that this is a config issue. If your solution is sensitive to resource utilization you have to configure limits. Surely ElasticSearch has options for setting manual resource limits.

All in all I don't see how using a database as a rate limiter is a good idea and should be supported by the developer.

@homo-lorens I never said that the database is the rate limiter.

I tried to describe a situation where several database's / services are dependent on each other.

And if one of the dependent database (services) suddenly has a higher speed than the expected baseline, everything that happens in the process after this service / database (children / services|databases dependent on this service) will have to deal with the increased load.

Yes, there is rate limiting implemented.

And if a rate limit applies to one database / service in a process, it must apply to all it's parent services if you see the dependency as a parent - child relation.

Simply put:

Input for Service A -> Processing ->
Input for Service B -> Processing ->
Input for Service C

If C throttles, B and A must throttle, too.

And if an rate limiter kicks in _constantly_ it means that there are resources wasted.

These resources cost money, so they should be used for something useful.

@IntrusionCM I suppose this is just an alien realm to me, but I find it hard to believe that the system you described isn't missing some kind of limiting to protect ElasticSearch and it's considered safe to rely on a bottleneck as a means to protect higher throughput elements, while also updating software without a stress test.

@homo-lorens I think you misunderstood a crucial part.

I'll try it now in a different way.

A rate limiter throttles. It prevents that a system is fed too much data.

That's the part where we seem to agree.

My point is: If a rate limiter kicks in in a process _all the time_, you are doing something wrong.

A rate limiter is a last line of defense - when something goes terrible wrong, eg a sudden burst, misconfiguration, spike or anything like that, only then a rate limiter should kick in.

And it should throttle only for a short period of time, as little as possible.

If a rate limiter is throttling all the time, it means that whatever is feeding data to the service / database is doing it _too fast_.

When a service runs too fast, it means that it is doing more than it should.

It's wasting resources.

Eg. :

10 workers feed a database.
The database rate limits.

Each of the workers are rate limited to 80 % of their max. throughput.

You could run with 8 workers.

2 workers are superfluous.

Maybe that helps a bit more ....

And when you think about the whole process....

The 2 missing workers mean that whatever feeds these workers, might be out of balance now. As you trimmed down from 10 workers to 8 workers, you can process less than before.

Hope it helps.

@IntrusionCM I see now, thanks.

@homo-lorens no problem.

Thx for the patience. 👍

@IntrusionCM In any case, seeing how there will be unallocated resources under regular load, the update should still cause a net performance increase. So even if rate limiting kicks in wasting some of the resources freed by the DB, the system as a whole will be at least as fast as it used to be, no?

@homo-lorens yes. it should be.

Oracle rolled out over several releases after GA 8.0 additions and modifications to the hash join implementation.

The "should" is me admitting that you really don't know what's in the bag.

As the quality assurance of Oracle leaves a lot to be desired, you can only hope that these modifications do not introduce regressions.

And this is the part that is tricky. You expect it to run faster, but databases are complex tricky bastards.

Eg. MySQL spills to disk when the join buffer memory limit for a hash join is exceeded - which isn't bad. But which means that when you're doing a lot of resource intensive joins, suddenly your IO usage might go bananas...

And as the behaviour can change with each release, you really really have to be very careful to not get fucked. Which is for me a NoGo.

Imho Oracle is pushing beta releases and unfinished implementations and uses his customers as guinea pigs.

@IntrusionCM I had no idea they started flushing things to disk with this update, I just went with the assumption that it's better in every way than the old version.

@homo-lorens It's an implementation design of the hash join implementation of MySQL added in 8.0.18.

https://mysqlserverteam.com/hash-jo...

"Hash join is enabled by default, so no action is needed to make use of hash join. One notable thing is that hash join builds on the new iterator executor, which means that you can (and must) use EXPLAIN FORMAT=tree to see whether hash join will be used:"

I think in 8.0.20 the Hash Join's were used for Right | Left Joins, and with 8.0.23 the internal representation was rewritten.

There is more in between the releases I guess, but the point is made I think.

Performance regressions can always happen, but I think Oracle's approach is nuts.