Comment by orware | Hacker News Reader

Comment by orware | Hacker News Reader

Another piece to this (for those using MySQL as their backend) is that updates to MySQL can improve things quite significantly as well.

In my work environment I"m the main MySQL user so I had setup a dedicated MySQL 5.5 server a few years ago that I hadn't upgraded to any newer versions since then (hey it wasn't broke right?). But recently I had been looking into upgrading just because it felt like it was time, but earlier this week I was checking out some performance degradation on one of our sites and noticed in my local copy it was actually running quite a bit faster.

Investigating further, it seems that in the newer versions of MySQL (in my case, my local XAMPP was running MariaDB 10.1.x) the performance has improved quite a bit, particularly for the types of queries I was running on this site (which used a number of subqueries and GROUP BY clauses).

Without any sort of application level caching making this single switch from MySQL 5.5 to MariaDB 10.1.19 improved load times from about 3.0 seconds to 1.0 seconds and I'm going to add some application level caching as well to improve things further.

This was all without switching PHP versions on this particular site (which is still using PHP 5.4.45)...just thought I'd share since sometimes the slowness isn't always improved as much just by upgrading PHP (which is something I'd like to do too for this site, but at the moment the framework I'm using on the site isn't ready for PHP 7 so it'd be quite a bit of work to switch it out).

Complete list of new features in MySQL 5.7 [0] (in bullet form) would show scores of performance improvements. The performance improvements from 5.5 to 5.6 were significant and 5.7 is much much better.

[0] http://www.thecompletelistoffeatures.com/

Thanks for sharing that link!

On the flip side there were severe performance degradations for certain types of queries. Notably Zabbix[0] was affected.

[0]: https://m.reddit.com/r/zabbix/comments/3txgov/poor_performan...

It's important to distinguish between cases where it is slower, and cases where query plans change.

MySQL 5.7 changed the cost-model significantly, so while it will be better on most queries, there are cases where two-wrongs-made-a-right; and edge case queries may be slower. To mitigate this (as it was somewhat expected), MySQL 5.7 also introduced new query hints and server-side query rewrite.

(In the case of the link you provided, OP responded later saying 5.7.10 improved performance again.)

more reasons to hate zabbix? as if I needed them ;)

What the heck. You have that domain JUST for MySQL 5.7?! The potential!

In future I intend to expand it for MySQL 8.0.

(I am the Product Manager for the MySQL Server, but yes - I personally own the domain.)

I would expand it out to everything, this would actually be a helpful service. I hate digging through feature notes on everything.

Idea: post the domain as a story to HN and say you're just managing MySQL but that you'll consider all reasonable pull requests. See what comes in!

The additions to the geospatial feature are a plus. from 5.6 to 5.7 there is a huge jump in potential. Hopefully more coming

This page gives a good visual:

https://www.mysql.com/why-mysql/benchmarks/

It's not only functional updates in the software, but MySQL server tends to benefit from better defaults between major versions.

If you already have a well configured server you tend to see smaller performance improvements between versions.

Reply on news.ycombinator.com