11 ms·

MySQL 8.0: Retiring Support for the Query Cache

144 points by aleksi 9y ago

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.
tyingq 9y ago
>>Use of non-deterministic features will result in the query not being cached (including temporary tables, user variables, RAND(), NOW() and UDFs.) Makes me wonder how many simplistic client side caches check for this. Pretty sure I've seen PHP shopping apps that blindly hash the query.
- johannes1234321 9y ago
  How often would that actually be a problem? RAND() and udfs aren't often used in queries, I would say. And NOW() is used for something like "items from last week" where some time difference doesn't matter too much (you need some cache invalidation as new data comes anyways)
  - tyingq 9y ago
    Yes, it's one of those things that isn't usually a problem, but that may arguably be worse...it's rare enough that you have no idea it's happening occasionally.
  - morgo 9y ago
    I don't think it's common per-se, but it leads into the next point as well. Because of MVCC, what you are supposed to see (while returning non-stale results) gets complicated.
- renecannao 9y ago
  I think what is important is to have it configurable. For example, in ProxySQL Query Cache you can choose which query to cache and which query not to cache. And some times it also makes sense to cache query that MySQL QC wouldn't cache. For example, once I saw an application running `SELECT NOW()` hundreds times per second (across multiple clients). Caching `SELECT NOW()` for 500ms on the client side would have reduced a lot of unnecessary network round trip. Author of ProxySQL here.
- deedubaya 9y ago
  AFAIK w/Rails ActiveRecord caches queries per-request. So if the SQL command matches, the cached result is returned. Where each request is short lived, caching the results likely isn't a problem. Maybe the PHP app's you've seen take the same approach?
rubiquity 9y ago
These days it is much easier to implement a query cache specific to your application's needs in other layers of your infrastructure. These other layers also tend to scale horizontally much more easily than a relational database does. I like it.
- larkost 9y ago
  In a former job I had an application that made excellent use of the Query Cache. A number of computers (in clusters across a large campus) that all needed to check on the same computed value that was derived from a query that spanned 5 tables. The computers each checked in up to every 5 minutes, and really only needed to know when the computer value changed, but without setting up some sort of push system (which would have been engineering expensive), we just fell back on the Query Cache. Worked beautifully, especially since we would only see an extra recompute a few times a day. Yes, you could architect a separate system to do this (e.g. a separate table, possibly with a stored procedure firing for changes on any of the tables), but that would necessarily involve more complexity.
  - pizza234 9y ago
    This is a quite lucky edge case; in order for this to work, you need to have the underlying tables (5 in this case) change infrequently. This is actually, directly and indirectly, the core issue of query caches. The one mentioned is the direct problem; the indirect one is that for each record change in any table [present in the query cache], the entire query cache must scanned and invalidated, which causes locking.
jbergstroem 9y ago
Out of curiosity: how does people using Wordpress [at somewhat scale] handle layers of cache? Last I used it, relying on the Wordpress cache layer just didn't cover enough cases. The MySQL one "saved" me seeing how Wordpress likes to ask the same question. A lot.
- sciurus 9y ago
  Primarily by putting a caching proxy server like Varnish in front of Wordpress.
  - agrajag 9y ago
    Yep, this is what we do. Adding in some kind of object cache cluster wasn't a significant enough of an improvement over just hitting mysql to justify the extra point of failure. The problem is WP just makes a ton of simple queries - in any complex install the bottleneck is probably going to be network latency to the database.
    - morgo 9y ago
      I think this echo's Rene's point about moving the cache closer to the application as well. I have some hypothetical numbers illustrating the impact of network latency: http://www.tocker.ca/2013/11/18/how-important-is-it-to-merge-queries-together.html http://www.tocker.ca/2013/11/18/how-important-is-it-to-merge... The simple queries are basically free once they get to MySQL. Query cache does not help.
- Viper007Bond 9y ago
  If you enable WordPress's object cache, it should get most page views down to a couple or even zero queries. It's poorly written themes and plugins where problems can arise. The easy solution is to just enable full page caching. On single server setups, something like WP Super Cache works well. On distributed setups like WordPress.com, this is more appropriate: https://github.com/Automattic/batcache https://github.com/Automattic/batcache
z3t4 9y ago
when optimizing old school server rendered web apps turning on the query cache always gave the most bang for the buck. hit rates around 90% or more.
jarnix 9y ago
I'm not sure that it will "only affect a small number of users". Maybe this should be an option with false as default value, it could help with the transition.
- web007 9y ago
  > The query cache has been disabled-by-default since MySQL 5.6 (2013) as it is known to not scale with high-throughput workloads on multi-core machines.
- reipahb 9y ago
  It has already been disabled by default for a while. From the article: > The query cache has been disabled-by-default since MySQL 5.6 (2013)
  - hendersoon 9y ago
    Yep. What they should have done was add a notification in the error log when you startup-- "Query Cache is enabled. Are you really sure you want to do that? Read http://whatever/qcisawful.html http://whatever/qcisawful.html for more information." And this should have been added to the final releases of every version all the way back to 5.0. For people saying "please, it's been off since 5.6 in 2013", my company's primary product runs on Percona 5.5 (2010), and our secondary product, is actively developing the _next_ version on Percona 5.6. That isn't particularly unusual.
- deleted 9y ago
  [deleted]
Markxr42 9y ago
It is definitely high time, the QC is an abomination, it's totally at odds with the idea of MVCC. In my experience, anyone who enabled the QC was deeply mistaken about its behaviour.
- hendersoon 9y ago
  Exactly! This should have been done a long time ago. The MySQL query cache was nothing less than a _trap_ for unsophisticated DBAs.
TekMol 9y ago
I always assumed that it makes sense to have the query cache active. It just makes sense logically. On my production server, "SHOW STATUS LIKE 'Qcache%'" currently gives me: Qcache_free_blocks 2184 Qcache_free_memory 7629288 Qcache_hits 328939349 Qcache_inserts 14440714 Qcache_lowmem_prunes 7814306 Qcache_not_cached 290027559 Qcache_queries_in_cache 7497 Qcache_total_blocks 17225 About 20 hits per insert. Sounds good to me. Anybody here who is more "in the know" then me and can tell me what I can conclude from these numbers and/or which other stats to look at?
- morgo 9y ago
  It's not easily possible to tell from these numbers. For example: - You can see a hit, but what was the cost of a miss? If it was a point lookup, it is very low cost. - Comparing hits to inserts, does not show the cost added to every other query to search the cache (Com_select) that may have not been added to the cache after execution due to judged to be non-deterministic. - Inserting results into the query cache can cause stalls (mentioned on Rene's post) - Performance should be judged on 99th+ percentile. Even if it makes some queries faster, has it improved your p99? If you wanted to take a more wholistic approach, it is good to measure this from the Application with something like NewRelic.
  - TekMol 9y ago
    > what was the cost of a miss Yeah, no idea. Regarding "Com_select", it seems to be 0: SHOW STATUS LIKE "Com_select" Variable_name Value Com_select 0 What does that mean? As for the wholistic approach .. well, the server tucks away nicely. I never experience any lag and all users are happy with the speed. So there is no strong drive to investigate. Computers are so fast these days. A cheap $15/month VPS can easily handle tens of thousands of users per day.
tiennou74 9y ago
I'm working with a company called http://www.heimdalldata.com/ http://www.heimdalldata.com/ they do SQL auto-caching with no code changes. A perfect replacement for Query Cache if you have a read-heavy application. They also offer a nice dashboard that helps identify database inefficiencies. I would try them out to see if you get a performance boost.
- munchai 9y ago
  How is this different from MySQL query caching?
  - tiennou74 9y ago
    Unlike other solutions, the software auto-caches and auto-invalidates SQL; it takes away a developer having to manually configure the cache. Heimdall works with both MySQL and Postgres
- jondumbau 9y ago
  How does it know if the underlying data changed
raarts 9y ago
This PostgreSQL diehard had a usecase that required a cluster of 3 masters to stay up if two of them died. Turned out that was really easy with MySQL multi-master and multi-source where each master is a slave to the other two.
tiennou74 9y ago
I am trying http://www.heimdalldata.com/ http://www.heimdalldata.com/ as a replacement.
h1d 9y ago
It's good that MySQL gets better because I'm tied to it since Sequel Pro is the best database browser there is and it only supports MySQL. Datagrip has that Java kind of non-nativeness (weird scrolling and the looks) and it's never easy to read/write triggers, relations and export data into csv etc. I did find Postico being decent last time I checked but what a limiting factor on the tooling.
- revmoo 9y ago
  > It's good that MySQL gets better because I'm tied to it since Sequel Pro is the best database browser there is and it only supports MySQL. Oh my god this 1000% I will use the "worse" DB every time if it allows me to be more efficient in my work.
- eeZi 9y ago
  JetBrains DataGrip comes close and supports many different SQL dialects (it's also comes with IntelliJ as the SQL plugin).
- nimchimpsky 9y ago
  datagrip/intellij is great, no weird scrolling for me. lots of benefits when renaming tables/cols - it updates my java references too. Saying sequel pro is the best is rather controversial, ever tried pgadmin or http://www.postgresqlstudio.org/ http://www.postgresqlstudio.org/, or sql server management studio.
  - mastazi 9y ago
    I find the I'm much less productive on pgAdmin 4 compared to MySQL Workbench 6. On my machine the web-like interface that pgAdmin uses since version 4 seems rather unresponsive. It's still a relatively new product though, I'm sure it will perform better over time.
    - nimchimpsky 9y ago
      pgadmin has been around since 1998.
lathiat 9y ago
Though this is probably a good move, I think it's going to catch out a lot of bad web apps based on my experience of trying to disable it at a web hosting company :-)

144 points by aleksi 9y ago

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.

tyingq 9y ago

>>Use of non-deterministic features will result in the query not being cached (including temporary tables, user variables, RAND(), NOW() and UDFs.) Makes me wonder how many simplistic client side caches check for this. Pretty sure I've seen PHP shopping apps that blindly hash the query.

johannes1234321 9y ago
How often would that actually be a problem? RAND() and udfs aren't often used in queries, I would say. And NOW() is used for something like "items from last week" where some time difference doesn't matter too much (you need some cache invalidation as new data comes anyways)
- tyingq 9y ago
  Yes, it's one of those things that isn't usually a problem, but that may arguably be worse...it's rare enough that you have no idea it's happening occasionally.
- morgo 9y ago
  I don't think it's common per-se, but it leads into the next point as well. Because of MVCC, what you are supposed to see (while returning non-stale results) gets complicated.
renecannao 9y ago
I think what is important is to have it configurable. For example, in ProxySQL Query Cache you can choose which query to cache and which query not to cache. And some times it also makes sense to cache query that MySQL QC wouldn't cache. For example, once I saw an application running `SELECT NOW()` hundreds times per second (across multiple clients). Caching `SELECT NOW()` for 500ms on the client side would have reduced a lot of unnecessary network round trip. Author of ProxySQL here.
deedubaya 9y ago
AFAIK w/Rails ActiveRecord caches queries per-request. So if the SQL command matches, the cached result is returned. Where each request is short lived, caching the results likely isn't a problem. Maybe the PHP app's you've seen take the same approach?

rubiquity 9y ago

These days it is much easier to implement a query cache specific to your application's needs in other layers of your infrastructure. These other layers also tend to scale horizontally much more easily than a relational database does. I like it.

larkost 9y ago
In a former job I had an application that made excellent use of the Query Cache. A number of computers (in clusters across a large campus) that all needed to check on the same computed value that was derived from a query that spanned 5 tables. The computers each checked in up to every 5 minutes, and really only needed to know when the computer value changed, but without setting up some sort of push system (which would have been engineering expensive), we just fell back on the Query Cache. Worked beautifully, especially since we would only see an extra recompute a few times a day. Yes, you could architect a separate system to do this (e.g. a separate table, possibly with a stored procedure firing for changes on any of the tables), but that would necessarily involve more complexity.
- pizza234 9y ago
  This is a quite lucky edge case; in order for this to work, you need to have the underlying tables (5 in this case) change infrequently. This is actually, directly and indirectly, the core issue of query caches. The one mentioned is the direct problem; the indirect one is that for each record change in any table [present in the query cache], the entire query cache must scanned and invalidated, which causes locking.

jbergstroem 9y ago

Out of curiosity: how does people using Wordpress [at somewhat scale] handle layers of cache? Last I used it, relying on the Wordpress cache layer just didn't cover enough cases. The MySQL one "saved" me seeing how Wordpress likes to ask the same question. A lot.

sciurus 9y ago
Primarily by putting a caching proxy server like Varnish in front of Wordpress.
- agrajag 9y ago
  Yep, this is what we do. Adding in some kind of object cache cluster wasn't a significant enough of an improvement over just hitting mysql to justify the extra point of failure. The problem is WP just makes a ton of simple queries - in any complex install the bottleneck is probably going to be network latency to the database.
  - morgo 9y ago
    I think this echo's Rene's point about moving the cache closer to the application as well. I have some hypothetical numbers illustrating the impact of network latency: http://www.tocker.ca/2013/11/18/how-important-is-it-to-merge-queries-together.html http://www.tocker.ca/2013/11/18/how-important-is-it-to-merge... The simple queries are basically free once they get to MySQL. Query cache does not help.
Viper007Bond 9y ago
If you enable WordPress's object cache, it should get most page views down to a couple or even zero queries. It's poorly written themes and plugins where problems can arise. The easy solution is to just enable full page caching. On single server setups, something like WP Super Cache works well. On distributed setups like WordPress.com, this is more appropriate: https://github.com/Automattic/batcache https://github.com/Automattic/batcache

z3t4 9y ago

when optimizing old school server rendered web apps turning on the query cache always gave the most bang for the buck. hit rates around 90% or more.

jarnix 9y ago

I'm not sure that it will "only affect a small number of users". Maybe this should be an option with false as default value, it could help with the transition.

web007 9y ago
> The query cache has been disabled-by-default since MySQL 5.6 (2013) as it is known to not scale with high-throughput workloads on multi-core machines.
reipahb 9y ago
It has already been disabled by default for a while. From the article: > The query cache has been disabled-by-default since MySQL 5.6 (2013)
- hendersoon 9y ago
  Yep. What they should have done was add a notification in the error log when you startup-- "Query Cache is enabled. Are you really sure you want to do that? Read http://whatever/qcisawful.html http://whatever/qcisawful.html for more information." And this should have been added to the final releases of every version all the way back to 5.0. For people saying "please, it's been off since 5.6 in 2013", my company's primary product runs on Percona 5.5 (2010), and our secondary product, is actively developing the _next_ version on Percona 5.6. That isn't particularly unusual.
deleted 9y ago
[deleted]

Markxr42 9y ago

It is definitely high time, the QC is an abomination, it's totally at odds with the idea of MVCC. In my experience, anyone who enabled the QC was deeply mistaken about its behaviour.

hendersoon 9y ago
Exactly! This should have been done a long time ago. The MySQL query cache was nothing less than a _trap_ for unsophisticated DBAs.

TekMol 9y ago

I always assumed that it makes sense to have the query cache active. It just makes sense logically. On my production server, "SHOW STATUS LIKE 'Qcache%'" currently gives me: Qcache_free_blocks 2184 Qcache_free_memory 7629288 Qcache_hits 328939349 Qcache_inserts 14440714 Qcache_lowmem_prunes 7814306 Qcache_not_cached 290027559 Qcache_queries_in_cache 7497 Qcache_total_blocks 17225 About 20 hits per insert. Sounds good to me. Anybody here who is more "in the know" then me and can tell me what I can conclude from these numbers and/or which other stats to look at?

morgo 9y ago
It's not easily possible to tell from these numbers. For example: - You can see a hit, but what was the cost of a miss? If it was a point lookup, it is very low cost. - Comparing hits to inserts, does not show the cost added to every other query to search the cache (Com_select) that may have not been added to the cache after execution due to judged to be non-deterministic. - Inserting results into the query cache can cause stalls (mentioned on Rene's post) - Performance should be judged on 99th+ percentile. Even if it makes some queries faster, has it improved your p99? If you wanted to take a more wholistic approach, it is good to measure this from the Application with something like NewRelic.
- TekMol 9y ago
  > what was the cost of a miss Yeah, no idea. Regarding "Com_select", it seems to be 0: SHOW STATUS LIKE "Com_select" Variable_name Value Com_select 0 What does that mean? As for the wholistic approach .. well, the server tucks away nicely. I never experience any lag and all users are happy with the speed. So there is no strong drive to investigate. Computers are so fast these days. A cheap $15/month VPS can easily handle tens of thousands of users per day.

tiennou74 9y ago

I'm working with a company called http://www.heimdalldata.com/ http://www.heimdalldata.com/ they do SQL auto-caching with no code changes. A perfect replacement for Query Cache if you have a read-heavy application. They also offer a nice dashboard that helps identify database inefficiencies. I would try them out to see if you get a performance boost.

munchai 9y ago
How is this different from MySQL query caching?
- tiennou74 9y ago
  Unlike other solutions, the software auto-caches and auto-invalidates SQL; it takes away a developer having to manually configure the cache. Heimdall works with both MySQL and Postgres
jondumbau 9y ago
How does it know if the underlying data changed

raarts 9y ago

This PostgreSQL diehard had a usecase that required a cluster of 3 masters to stay up if two of them died. Turned out that was really easy with MySQL multi-master and multi-source where each master is a slave to the other two.

tiennou74 9y ago

I am trying http://www.heimdalldata.com/ http://www.heimdalldata.com/ as a replacement.

h1d 9y ago

It's good that MySQL gets better because I'm tied to it since Sequel Pro is the best database browser there is and it only supports MySQL. Datagrip has that Java kind of non-nativeness (weird scrolling and the looks) and it's never easy to read/write triggers, relations and export data into csv etc. I did find Postico being decent last time I checked but what a limiting factor on the tooling.

revmoo 9y ago
> It's good that MySQL gets better because I'm tied to it since Sequel Pro is the best database browser there is and it only supports MySQL. Oh my god this 1000% I will use the "worse" DB every time if it allows me to be more efficient in my work.
eeZi 9y ago
JetBrains DataGrip comes close and supports many different SQL dialects (it's also comes with IntelliJ as the SQL plugin).
nimchimpsky 9y ago
datagrip/intellij is great, no weird scrolling for me. lots of benefits when renaming tables/cols - it updates my java references too. Saying sequel pro is the best is rather controversial, ever tried pgadmin or http://www.postgresqlstudio.org/ http://www.postgresqlstudio.org/, or sql server management studio.
- mastazi 9y ago
  I find the I'm much less productive on pgAdmin 4 compared to MySQL Workbench 6. On my machine the web-like interface that pgAdmin uses since version 4 seems rather unresponsive. It's still a relatively new product though, I'm sure it will perform better over time.
  - nimchimpsky 9y ago
    pgadmin has been around since 1998.

lathiat 9y ago

Though this is probably a good move, I think it's going to catch out a lot of bad web apps based on my experience of trying to disable it at a web hosting company :-)