Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

MySQL 5.7.6 is out

datacharmer.blogspot.com

Post reply on HN
Print view View original

51–60 of 70 posts

Re: MySQL 5.7.6 is out

#51

Post by jkoudys »

> The main effort of the team has been focused on speed, with performance reportedly improved from 2 to 3 times compared to previous releases.

Definitely good news, as MySQL came out during the peak of Moore's-law (or, more appropriately, a misapplication of) fueled idiocy where nobody seemed interested in investing in better performing software, since they all expected they'd have faster hardware next year. Now everybody's deploying to VPSes, so it's once again important that we get the most out of every single CPU cycle. I haven't gone through all the diffs, but if they're promising 2x to 3x improved performance, there must have been some pretty fundamental rewrites of old code.

Coming from the Oracle + DB2 world, what I'd really like to see is some improved performance analysis and query optimization tools. There have been some incremental improvements to EXPLAIN (the new 'EXPLAIN on a named connection' feature seems pretty cool for when I have 10+ application instances all connecting to my DB), but it's still got a long way to go.

Re: MySQL 5.7.6 is out

#52

Post by morgo »

j_s wrote: post #50
morgo wrote: post #37

Earlier quoted context omitted.

Please see my comments on this thread: https://news.ycombinator.com/item?id=8318199 I agree it's a silly name, but it's not like we created it :)

If you've not heard of the term 'tummler' this might be helpful: To build a strong community, stop “community managing”, be a Tummler instead. http://dangerouslyawesome.com/2014/04/community-management-t...

I like it - thanks for the link.

Re: MySQL 5.7.6 is out

#53

Post by morgo »

JohnBooty wrote: post #46
taf2 wrote: post #8

So much MySQL hate these days... I should try Postgres out but here's why I still like and trust MySQL or MariaDB/Percona... 1. It's reliable/durable 2. I know the tools (mysql command line is friendly to me) 3. It's performance is predictable. 99% of issues with performance are easily solved with the right index 4. I don't find myself needing many SQL features beyond the basics. Also, I'm a happy user of other tools…

> 3. It's performance is predictable. 99% of issues > with performance are easily solved with the right index This is one of my least favorite things about MySQL. The "explain" feature is much less informative than that offered by MSSQL or Postgres, so figuring out what indexes or tuning to apply is harder than it needs to be. And then when you do have everything indexed correctly, MySQL is significantly dumber (edit…

If you do not like explain, may I suggest using `EXPLAIN FORMAT=JSON` - it is new to MySQL 5.6, and contains additional meta data. There is also optimizer trace, which provides significantly more meta data. Some good examples in these slides: http://www.slideshare.net/oysteing/how-to-analyze-and-tune-s...

I believe your example might be http://mysql-nordic.blogspot.ca/2015/01/with-latest-verions-... - Fixed in 5.7.5.

There has been a lot of work in improving the optimizer recently. One project that I find particularly interesting, is the focus on improving the cost model: http://mysqlserverteam.com/the-mysql-optimizer-cost-model-pr...

Re: MySQL 5.7.6 is out

#54

Post by sevenproxies »

JohnBooty wrote: post #46
taf2 wrote: post #8

So much MySQL hate these days... I should try Postgres out but here's why I still like and trust MySQL or MariaDB/Percona... 1. It's reliable/durable 2. I know the tools (mysql command line is friendly to me) 3. It's performance is predictable. 99% of issues with performance are easily solved with the right index 4. I don't find myself needing many SQL features beyond the basics. Also, I'm a happy user of other tools…

> 3. It's performance is predictable. 99% of issues > with performance are easily solved with the right index This is one of my least favorite things about MySQL. The "explain" feature is much less informative than that offered by MSSQL or Postgres, so figuring out what indexes or tuning to apply is harder than it needs to be. And then when you do have everything indexed correctly, MySQL is significantly dumber (edit…

>why can't "select A,B from foo" be served directly from the index

mysql supports that with covering indexes.

https://books.google.co.uk/books?id=IagfgRiKWd4C&lpg=PA178&v...

Re: MySQL 5.7.6 is out

#55

Post by IndianAstronaut »

pilif wrote: post #11
taf2 wrote: post #8

So much MySQL hate these days... I should try Postgres out but here's why I still like and trust MySQL or MariaDB/Percona... 1. It's reliable/durable 2. I know the tools (mysql command line is friendly to me) 3. It's performance is predictable. 99% of issues with performance are easily solved with the right index 4. I don't find myself needing many SQL features beyond the basics. Also, I'm a happy user of other tools…

>1. It's reliable/durable I would personally contest that. I've seen MySQL corrupt tables before and the insanely unhelpful defaults which make MySQL accept and alter invalid data really don't strike me as neither reliable nor durable. Your other points are valid reasons for sticking with MySQL though. If you're happy and the tool does what you need, that's fine. But once you've seen what you can do as you gain acces…

I have had terrible luck with MySQL on my macbook. The db gets corrupted often and I lose my data on a regular basis.

Re: MySQL 5.7.6 is out

#56

Post by Buttons840 »

kidmenot wrote: post #31
taf2 wrote: post #8

So much MySQL hate these days... I should try Postgres out but here's why I still like and trust MySQL or MariaDB/Percona... 1. It's reliable/durable 2. I know the tools (mysql command line is friendly to me) 3. It's performance is predictable. 99% of issues with performance are easily solved with the right index 4. I don't find myself needing many SQL features beyond the basics. Also, I'm a happy user of other tools…

>4. I don't find myself needing many SQL features beyond the basics. That's probably why you still like it. As it stands, MySQL has so many shortcomings it's not even funny. Version 5.7 is the first one to support multiple triggers per event. Beyond that, and just to name a few, you can't have subselects in views, you don't have CTEs (good luck storing hierarchical data in an adjacency list), no materialized views, C…

There have been several times in my career where I have missed full SQL support in MySQL databases other people have setup and left for me to maintain. Choosing MySQL isn't the worst thing one can do when building a new system, but just because the initial designers don't intend to use advanced SQL features doesn't mean future use cases wont benefit from full SQL support.

Re: MySQL 5.7.6 is out

#57

Post by morgo »

jkoudys wrote: post #51

> The main effort of the team has been focused on speed, with performance reportedly improved from 2 to 3 times compared to previous releases. Definitely good news, as MySQL came out during the peak of Moore's-law (or, more appropriately, a misapplication of) fueled idiocy where nobody seemed interested in investing in better performing software, since they all expected they'd have faster hardware next year. Now ever…

I totally hear you here :)

Visibility is the most useful and always under-rated feature. If I can clarify some of the recent enhancements here:

- MySQL 5.6 introduced `EXPLAIN FORMAT=JSON`. MySQL Workbench uses this to visualize query plans (important as they get complicated.)

- Also in 5.6 was optimizer trace (find out why indexes were not used etc.) and performance_schema enabled by default.

- MySQL 5.7 adds cost information to `EXPLAIN FORMAT=JSON` (Workbench also understands it.)

- Workbench also has a set of performance views based on Performance_schema (based on a project called SYS). You can install it as standalone from here too: https://github.com/MarkLeith/mysql-sys

- The runtime performance data from SYS is very useful for making correct optimization decisions. Being an SQL interface is also friendly for writing your own scripts against it.

Also, to clarify on your first paragraph:

Yes, refactoring old code was one of the ways this was achieved. We picked refactoring targets based on performance and because some of the older code is not very testable.

Re: MySQL 5.7.6 is out

#58

Post by ldng »

kbenson wrote: post #36
pilif wrote: post #24

Earlier quoted context omitted.

CTEs, range types, window functions, custom aggregates, functional indexes, conditional indexes, proper utf-8 support and once you bring Postgres with its unique features into the mix: kick-ass JSON support (including the usage of indexes), array support (any my best friend array_agg()) Regarding corruption, it's an anecdote, but my girlfriend has lost a ton of data for her thesis related to innodb corruption. It too…

It's not pretty, but you can get the equivalent of range types using variables[1]. Regarding corruption, it's an anecdote, but my girlfriend has lost a ton of data for her thesis related to innodb corruption. It took me hours of bit-twiddling to get the data back (and into Postgres where it's safe and accessible since then - on the same hardware, so don't blame this to broken hardware) It's not safe until it's backed…

I would add, as Dimitri Fontaine says : it is not safe until backup is tested and automatically recoverable !

https://fosdem.org/2015/schedule/event/youd_better_have_test...

Re: MySQL 5.7.6 is out

#59

Post by morgo »

equoid wrote: post #48
morgo wrote: post #19

Earlier quoted context omitted.

In the context of this post (MySQL 5.7) it does not accept invalid data :) The default sql mode has changed.

"The default sql mode has changed." So long as it possible to redefine the mode at will on a per connection basis or on startup then the integrity of data held within MySQL cannot be guaranteed.

Being able to change it on a per connection basis is required for earlier applications. If we use Wordpress as an example, upon initial connection it will set the SQL mode to what it is compatible with (magento, drupal, others also do similar).

Integrity can also not be upheld if applications lie. i.e. I always enter the same incorrect birthday if an application asks me for this, but I don't think they have a reason for it.

Re: MySQL 5.7.6 is out

#60

Post by JohnBooty »

sevenproxies wrote: post #54
JohnBooty wrote: post #46

Earlier quoted context omitted.

> 3. It's performance is predictable. 99% of issues > with performance are easily solved with the right index This is one of my least favorite things about MySQL. The "explain" feature is much less informative than that offered by MSSQL or Postgres, so figuring out what indexes or tuning to apply is harder than it needs to be. And then when you do have everything indexed correctly, MySQL is significantly dumber (edit…

>why can't "select A,B from foo" be served directly from the index mysql supports that with covering indexes. https://books.google.co.uk/books?id=IagfgRiKWd4C&lpg=PA178&v...

Wow, that's great news. Thanks for the correction!
Post reply on HN