6 ms·

Product Manager for the MySQL Server here (and post author). Happy to answer any questions...

by morgo 9y ago

deleted 9y ago
[deleted]
verelo 9y ago
We are currently using MySQL on RDS for most of our work. We're very tempted to move to Aurora on AWS for the benefits it has around write scaling, disk size scaling, table size limits and its claims around improved performance. I assume you're watching these developments, what is your take? Do you plan to compete, mirror or simply go another direction with MySQL?
- morgo 9y ago
  We compete in the sense that Aurora is a fork of MySQL 5.6 (2013). As a product manager, I do watch Aurora (along with SQL Server, Postgres, MongoDB, MariaDB etc). I'd rather answer questions about our products if you don't mind :-)
  - verelo 9y ago
    Fair enough. My biggest question is the same old problem that plagues all databases: Do you have any plans coming up to help us deal with modifying large tables, scaling writes and/or dealing with current known storage limits?
    - morgo 9y ago
      I think plague might be a stronger word than I would use, since I think there is always pressure on new entrants to exaggerate problems with existing technology. For example: I have several customers with 200TB databases on a single server. I helped a customer a few weeks ago insert 50K/queries/s sustained on some not particularly special hardware (higher is possible; depends a lot on schema+indexes). But back to your question - Yes. We are working on improving use cases like insert throughput, and changing the file format so we can support an instant DDL. Having our new data dictionary in 8.0 provides a strong basis for this. The larger vision is a 4 step mission, described in our Keynote from last year: https://www.youtube.com/watch?v=4ihSsQ2z-Cc&feature=youtu.be&t=2707 https://www.youtube.com/watch?v=4ihSsQ2z-Cc&feature=youtu.be... Actual mission slide starts at 57:30. Step 4 is to introduce write scale out with sharding. (Hi btw!, I'm also an Australian living in Toronto.)
krylon 9y ago
When I first learned about relational databases and looked for a free and open source engine to play around with, people told me there were MySQL and PostgreSQL, and that I should pick one; what is the difference, I asked, and was told that basically, MySQL is fast and easy to learn, while PostgreSQL had "real" transactions, referential integrity and so forth, plus lots of features. How does the landscape look today? I am under the vague impression that the Postgres developers have worked hard on improving performance (and adding more features), but the MySQL developers probably have not been sitting on their hands all those years - what are MySQL's strong points today? (I remember the documentation being very good, and I count that a strong point!)
- morgo 9y ago
  What are MySQL's strong points today? It's an interesting question, because some of the features that I'm most proud of MySQL having are the ones which might miss your radar: * Performance_schema means that any time MySQL is allocating memory, performing IO or waiting on locks - there is a really easy SQL interface to debug issues. I always say that most users are not getting the performance they are entitled to, because they don't have the visibility. * Logical replication means that it is very easy to do rolling version upgrades. It is also easier to have remote replicas and not have schema changes re-send the whole table across the wire. * Group Replication (new) - Built in active/active HA. * The InnoDB Storage engine is very good. It uses an update in place w/REDO model, that has a lot of nice performance characteristics for short-medium sized transactions. The IO and CPU scalability is also very good these days, and we have a number of contributors to thank for that (Percona, Facebook, Google). InnoDB supports native aio, direct io, and can read/write in multiple threads. The change buffering feature that it has (aka insert buffer) is very good at reducing IO on a number of workloads. Its compression feature is also important for reducing space on SSDs. * I actually think our bug workflow is very good if you are a production DBA. No new features in a stable release, and only the docs team closes bugs. This has a good way of forcing the release notes to be very accurate. * The tunables and overrides for DBAs, and the tooling is very stable. MySQL 5.7 supports server-side query rewrite based on a pattern where I can insert a query hint if required.
  - 9y ago
stouset 9y ago
I'm a developer who drastically prefers PostgreSQL due to things like window functions, a more predictable query planner, a variety of index types and the ability to index over calculated fields, improved strictness out of the box (e.g., UTF-8 being UTF-8, no implicit truncation of long strings, no silent and lossy automatic typecasting), and so on. I'm forced to use MySQL at work, because it's much easier to work with for our operations teams. That said, my perception is that PostgreSQL is catching up to MySQL in terms of operational overhead and replication strategies faster than MySQL is catching up to PostgreSQL on the end-user side of things. To an end-user like me, what would you point out are some current advantages that MySQL has over PostgreSQL, and what do you see the MySQL project doing to help it catch up to the growing gulf in feature parity?
- morgo 9y ago
  I would maybe start off by saying feature parity was/is never the goal. The original goal of MySQL was to be the "Ikea of databases" (both come from Sweden). Having said that, I think expectations on what is the minimal functionality have evolved, and we have responded by adding functionality like JSON in MySQL 5.7, and CTEs and Window Functions in 8.0. In terms of the specific issues you raise: * utf8 vs utf8mb4 is "problem #1" http://mysqlserverteam.com/sushi-beer-an-introduction-of-utf8-support-in-mysql-8-0/ http://mysqlserverteam.com/sushi-beer-an-introduction-of-utf... - we have switched the default in 8.0, and will deprecate utf8mb3 to reduce confusion: http://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-over-utf8mb4/ http://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-ove... * Implicit truncation and automatic type casting is no longer the default (strict was enabled for new installs in 5.6 (2013) and all installs in 5.7 (2015)). That is, unless the standard specifies it should (there are some weird cases). * 5.7 has virtual columns + indexes. This allows for a functional index. Edit: Missed a word, added computed columns
  - stouset 9y ago
    Thanks for your reply! I'm thrilled to hear that MySQL is taking strictness much more seriously with the above-mentioned strategies. It looks the top few of my biggest gripes are getting addressed, and that's exciting news. Could you elaborate more on MySQL being the Ikea of databases? What exactly does that mean, and how do those things differentiate it from PostgreSQL currently (and how will they in the future)? If it's mostly just around new-developer friendliness, I worry that MySQL has more of an (admittedly somewhat exaggerated) reputation as the "PHP of databases" right now: it's easy to use out of the box, but in a way that doesn't discourage poor practice and results in traps and land-mines for future development. How do you guys plan on achieving such a (laudable) goal without bringing about the kind of baggage that stereotypically has come with it? FWIW, I suspect the strictness changes you mentioned will go a long way towards addressing that, but I'm curious if there's more you have in mind. PS. Apologies if I've set up a bit of a straw man in my last paragraph. I'm trying to predict what your answer to the prior paragraph might be and pose additional questions based on that answer to avoid an extra round-trip.
indolering 9y ago
I really like the computed column capabilities of recent releases, however, I'm frequently frustrated by MySQL's poor support for materialized views and subquery optimization. Isn't MySQL is dependent upon the query cache for caching subqueries? Are there plans to introduce materialized views or improve subquery performance?
- morgo 9y ago
  There are a number of subquery performance improvements in MySQL 5.6 (including semi join and materialization). These are not dependent on query cache. 5.7 also added a new derived merge optimization (subquery in the from clause). No current plans to add materialized views.
sixdimensional 9y ago
Do you have any thoughts on the future of MySQL with regards to SQL/MED (Management of External Data)? https://en.wikipedia.org/wiki/SQL/MED https://en.wikipedia.org/wiki/SQL/MED Clearly a hard and costly problem to crack, but I always wondered with MySQL's pluggable engine tech, if something couldn't be done in this area (or even if that might have been part of the goal of the pluggable tech?).
- morgo 9y ago
  Not something we are currently looking at, but possible in the future. I am not quite the right person to answer if the storage engine API can handle the use case for this. It is a slightly different problem, in that you need to push down a lot more conditions into the engine. In some ways we do this, with our MySQL cluster product already.
nvivo 9y ago
Mysql for some reason still lacks: * uuid type * datetime with timezone * storing (and retrieving) the view definition as it was defined with comments, formatting, etc * using the same temporary table multiple times in the same query This is not an extensive list, but are things that bite me every day with mysql. Even though it's not query cache related, I wonder why such basic features are still missing and what are the plans to include them? They sound much simpler and more important to add than adding a nosql protocol to a sql database.
- morgo 9y ago
  I'm not sure if that was a question, but I'll answer :-) * For UUID, we've added helper functions to store it in insert-friendly order: http://mysqlserverteam.com/mysql-8-0-uuid-support/ http://mysqlserverteam.com/mysql-8-0-uuid-support/ * For Datetime + Timezone, this is something we are looking into currently. Datatypes are actually not simple to add in MySQL. While STRICT mode is the default, we support the upgrade case of it disabled. Which leaves us with a number of implicit conversions to handle. I wish it wasn't the case, but it's not be lack of demand on our side :-) We intend to schedule refactoring work to make this easier in the future. * For storing/retrieving view definitions I hear you on that one. There is a documented reason though: > The advantage of storing a view definition in canonical form is that changes made later to the value of sql_mode will not affect the results from the view. However an additional consequence is that comments prior to SELECT are stripped from the definition by the server. * Re-using the same temporary table can now be worked around with a CTE (8.0), which is preferred. It is not just a case of prioritization, but also resourcing. We have a large team, and have different people working on data types from protocol work :-)
callesgg 9y ago
Can you give some thoughts over why one should chose MySQL over MariaDB?
- morgo 9y ago
  MariaDB diverged from MySQL 5.5 (2010). I'm quite proud of what we've managed to achieve since then: - MySQL 5.6 (2013) https://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html https://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html - MySQL 5.7 (2015) I have a list @ http://www.thecompletelistoffeatures.com/ http://www.thecompletelistoffeatures.com/ - MySQL 8.0 (in development) http://mysqlserverteam.com/the-mysql-8-0-0-milestone-release-is-available/ http://mysqlserverteam.com/the-mysql-8-0-0-milestone-release... http://mysqlserverteam.com/the-mysql-8-0-1-milestone-release-is-available/ http://mysqlserverteam.com/the-mysql-8-0-1-milestone-release... In terms of some of the most recent work, I think the utf8mb4 performance improvements will have a big return for users: http://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-over-utf8mb4/ http://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-ove...
  - h1d 9y ago
    You could've placed your content on a more known domain than random looking one for 5.7 like Medium. People have "mind score" for domains and funny looking ones aren't exactly easy to go to.
grogers 9y ago
Will adaptive hash index ever be disabled by default? While it's orders of magnitude less horrible than the query cache, it's in the same category of things which add variance for marginal gain.
- morgo 9y ago
  I looked into this for defaults for 5.7 and 8.0. Our performance team feels like ON is still the better default, as it applies to more workloads than not. Improvements were also made in 5.7 to partition the hash.
axelfontaine 9y ago
Does MySQL have any plans to introduce proper support for DDL transactions? (with no implicit commits!)
- morgo 9y ago
  We've made the first step in 8.0, by moving the data dictionary to use a transactional backing store internally (no more FRM files). This means we can now do atomic DDL (i.e. drop 3 tables with all/none semantics). Extending it to transactional DDL is something I'd like to see in the future, but it is not in scope for 8.0.

Product Manager for the MySQL Server here (and post author). Happy to answer any questions...

by morgo 9y ago

Product Manager for the MySQL Server here (and post author). Happy to answer any questions...

deleted 9y ago

[deleted]

verelo 9y ago

We are currently using MySQL on RDS for most of our work. We're very tempted to move to Aurora on AWS for the benefits it has around write scaling, disk size scaling, table size limits and its claims around improved performance. I assume you're watching these developments, what is your take? Do you plan to compete, mirror or simply go another direction with MySQL?

morgo 9y ago
We compete in the sense that Aurora is a fork of MySQL 5.6 (2013). As a product manager, I do watch Aurora (along with SQL Server, Postgres, MongoDB, MariaDB etc). I'd rather answer questions about our products if you don't mind :-)
- verelo 9y ago
  Fair enough. My biggest question is the same old problem that plagues all databases: Do you have any plans coming up to help us deal with modifying large tables, scaling writes and/or dealing with current known storage limits?
  - morgo 9y ago
    I think plague might be a stronger word than I would use, since I think there is always pressure on new entrants to exaggerate problems with existing technology. For example: I have several customers with 200TB databases on a single server. I helped a customer a few weeks ago insert 50K/queries/s sustained on some not particularly special hardware (higher is possible; depends a lot on schema+indexes). But back to your question - Yes. We are working on improving use cases like insert throughput, and changing the file format so we can support an instant DDL. Having our new data dictionary in 8.0 provides a strong basis for this. The larger vision is a 4 step mission, described in our Keynote from last year: https://www.youtube.com/watch?v=4ihSsQ2z-Cc&feature=youtu.be&t=2707 https://www.youtube.com/watch?v=4ihSsQ2z-Cc&feature=youtu.be... Actual mission slide starts at 57:30. Step 4 is to introduce write scale out with sharding. (Hi btw!, I'm also an Australian living in Toronto.)

krylon 9y ago

When I first learned about relational databases and looked for a free and open source engine to play around with, people told me there were MySQL and PostgreSQL, and that I should pick one; what is the difference, I asked, and was told that basically, MySQL is fast and easy to learn, while PostgreSQL had "real" transactions, referential integrity and so forth, plus lots of features. How does the landscape look today? I am under the vague impression that the Postgres developers have worked hard on improving performance (and adding more features), but the MySQL developers probably have not been sitting on their hands all those years - what are MySQL's strong points today? (I remember the documentation being very good, and I count that a strong point!)

morgo 9y ago
What are MySQL's strong points today? It's an interesting question, because some of the features that I'm most proud of MySQL having are the ones which might miss your radar: * Performance_schema means that any time MySQL is allocating memory, performing IO or waiting on locks - there is a really easy SQL interface to debug issues. I always say that most users are not getting the performance they are entitled to, because they don't have the visibility. * Logical replication means that it is very easy to do rolling version upgrades. It is also easier to have remote replicas and not have schema changes re-send the whole table across the wire. * Group Replication (new) - Built in active/active HA. * The InnoDB Storage engine is very good. It uses an update in place w/REDO model, that has a lot of nice performance characteristics for short-medium sized transactions. The IO and CPU scalability is also very good these days, and we have a number of contributors to thank for that (Percona, Facebook, Google). InnoDB supports native aio, direct io, and can read/write in multiple threads. The change buffering feature that it has (aka insert buffer) is very good at reducing IO on a number of workloads. Its compression feature is also important for reducing space on SSDs. * I actually think our bug workflow is very good if you are a production DBA. No new features in a stable release, and only the docs team closes bugs. This has a good way of forcing the release notes to be very accurate. * The tunables and overrides for DBAs, and the tooling is very stable. MySQL 5.7 supports server-side query rewrite based on a pattern where I can insert a query hint if required.
- 9y ago

stouset 9y ago

I'm a developer who drastically prefers PostgreSQL due to things like window functions, a more predictable query planner, a variety of index types and the ability to index over calculated fields, improved strictness out of the box (e.g., UTF-8 being UTF-8, no implicit truncation of long strings, no silent and lossy automatic typecasting), and so on. I'm forced to use MySQL at work, because it's much easier to work with for our operations teams. That said, my perception is that PostgreSQL is catching up to MySQL in terms of operational overhead and replication strategies faster than MySQL is catching up to PostgreSQL on the end-user side of things. To an end-user like me, what would you point out are some current advantages that MySQL has over PostgreSQL, and what do you see the MySQL project doing to help it catch up to the growing gulf in feature parity?

morgo 9y ago
I would maybe start off by saying feature parity was/is never the goal. The original goal of MySQL was to be the "Ikea of databases" (both come from Sweden). Having said that, I think expectations on what is the minimal functionality have evolved, and we have responded by adding functionality like JSON in MySQL 5.7, and CTEs and Window Functions in 8.0. In terms of the specific issues you raise: * utf8 vs utf8mb4 is "problem #1" http://mysqlserverteam.com/sushi-beer-an-introduction-of-utf8-support-in-mysql-8-0/ http://mysqlserverteam.com/sushi-beer-an-introduction-of-utf... - we have switched the default in 8.0, and will deprecate utf8mb3 to reduce confusion: http://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-over-utf8mb4/ http://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-ove... * Implicit truncation and automatic type casting is no longer the default (strict was enabled for new installs in 5.6 (2013) and all installs in 5.7 (2015)). That is, unless the standard specifies it should (there are some weird cases). * 5.7 has virtual columns + indexes. This allows for a functional index. Edit: Missed a word, added computed columns
- stouset 9y ago
  Thanks for your reply! I'm thrilled to hear that MySQL is taking strictness much more seriously with the above-mentioned strategies. It looks the top few of my biggest gripes are getting addressed, and that's exciting news. Could you elaborate more on MySQL being the Ikea of databases? What exactly does that mean, and how do those things differentiate it from PostgreSQL currently (and how will they in the future)? If it's mostly just around new-developer friendliness, I worry that MySQL has more of an (admittedly somewhat exaggerated) reputation as the "PHP of databases" right now: it's easy to use out of the box, but in a way that doesn't discourage poor practice and results in traps and land-mines for future development. How do you guys plan on achieving such a (laudable) goal without bringing about the kind of baggage that stereotypically has come with it? FWIW, I suspect the strictness changes you mentioned will go a long way towards addressing that, but I'm curious if there's more you have in mind. PS. Apologies if I've set up a bit of a straw man in my last paragraph. I'm trying to predict what your answer to the prior paragraph might be and pose additional questions based on that answer to avoid an extra round-trip.

indolering 9y ago

I really like the computed column capabilities of recent releases, however, I'm frequently frustrated by MySQL's poor support for materialized views and subquery optimization. Isn't MySQL is dependent upon the query cache for caching subqueries? Are there plans to introduce materialized views or improve subquery performance?

morgo 9y ago
There are a number of subquery performance improvements in MySQL 5.6 (including semi join and materialization). These are not dependent on query cache. 5.7 also added a new derived merge optimization (subquery in the from clause). No current plans to add materialized views.

sixdimensional 9y ago

Do you have any thoughts on the future of MySQL with regards to SQL/MED (Management of External Data)? https://en.wikipedia.org/wiki/SQL/MED https://en.wikipedia.org/wiki/SQL/MED Clearly a hard and costly problem to crack, but I always wondered with MySQL's pluggable engine tech, if something couldn't be done in this area (or even if that might have been part of the goal of the pluggable tech?).

morgo 9y ago
Not something we are currently looking at, but possible in the future. I am not quite the right person to answer if the storage engine API can handle the use case for this. It is a slightly different problem, in that you need to push down a lot more conditions into the engine. In some ways we do this, with our MySQL cluster product already.

nvivo 9y ago

Mysql for some reason still lacks: * uuid type * datetime with timezone * storing (and retrieving) the view definition as it was defined with comments, formatting, etc * using the same temporary table multiple times in the same query This is not an extensive list, but are things that bite me every day with mysql. Even though it's not query cache related, I wonder why such basic features are still missing and what are the plans to include them? They sound much simpler and more important to add than adding a nosql protocol to a sql database.

morgo 9y ago
I'm not sure if that was a question, but I'll answer :-) * For UUID, we've added helper functions to store it in insert-friendly order: http://mysqlserverteam.com/mysql-8-0-uuid-support/ http://mysqlserverteam.com/mysql-8-0-uuid-support/ * For Datetime + Timezone, this is something we are looking into currently. Datatypes are actually not simple to add in MySQL. While STRICT mode is the default, we support the upgrade case of it disabled. Which leaves us with a number of implicit conversions to handle. I wish it wasn't the case, but it's not be lack of demand on our side :-) We intend to schedule refactoring work to make this easier in the future. * For storing/retrieving view definitions I hear you on that one. There is a documented reason though: > The advantage of storing a view definition in canonical form is that changes made later to the value of sql_mode will not affect the results from the view. However an additional consequence is that comments prior to SELECT are stripped from the definition by the server. * Re-using the same temporary table can now be worked around with a CTE (8.0), which is preferred. It is not just a case of prioritization, but also resourcing. We have a large team, and have different people working on data types from protocol work :-)

callesgg 9y ago

Can you give some thoughts over why one should chose MySQL over MariaDB?

morgo 9y ago
MariaDB diverged from MySQL 5.5 (2010). I'm quite proud of what we've managed to achieve since then: - MySQL 5.6 (2013) https://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html https://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html - MySQL 5.7 (2015) I have a list @ http://www.thecompletelistoffeatures.com/ http://www.thecompletelistoffeatures.com/ - MySQL 8.0 (in development) http://mysqlserverteam.com/the-mysql-8-0-0-milestone-release-is-available/ http://mysqlserverteam.com/the-mysql-8-0-0-milestone-release... http://mysqlserverteam.com/the-mysql-8-0-1-milestone-release-is-available/ http://mysqlserverteam.com/the-mysql-8-0-1-milestone-release... In terms of some of the most recent work, I think the utf8mb4 performance improvements will have a big return for users: http://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-over-utf8mb4/ http://mysqlserverteam.com/mysql-8-0-when-to-use-utf8mb3-ove...
- h1d 9y ago
  You could've placed your content on a more known domain than random looking one for 5.7 like Medium. People have "mind score" for domains and funny looking ones aren't exactly easy to go to.

grogers 9y ago

Will adaptive hash index ever be disabled by default? While it's orders of magnitude less horrible than the query cache, it's in the same category of things which add variance for marginal gain.

morgo 9y ago
I looked into this for defaults for 5.7 and 8.0. Our performance team feels like ON is still the better default, as it applies to more workloads than not. Improvements were also made in 5.7 to partition the hash.

axelfontaine 9y ago

Does MySQL have any plans to introduce proper support for DDL transactions? (with no implicit commits!)

morgo 9y ago
We've made the first step in 8.0, by moving the data dictionary to use a transactional backing store internally (no more FRM files). This means we can now do atomic DDL (i.e. drop 3 tables with all/none semantics). Extending it to transactional DDL is something I'd like to see in the future, but it is not in scope for 8.0.