tag:blogger.com,1999:blog-91887142678633278202024-03-16T07:36:55.617-04:00J-F Gagné's MySQL BlogJean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.comBlogger99125tag:blogger.com,1999:blog-9188714267863327820.post-87066349114180535042023-11-22T08:30:00.000-05:002023-11-22T09:58:02.408-05:00Thoughts on the October 2023 MySQL ReleasesA few days ago, Oracle released three new MySQL GA versions: 8.2.0, 8.0.35 and 5.7.44. I skimmed the release notes (8.2.0, 8.0.35 and 5.7.44), and I am not impressed. I guess that I would be even less impressed / more disappointed if I had checked in greater detail, and if I had reviewed the 8.1.0, 8.0.34 and 5.7.43 release notes. The subject of my disappointment is Oracle not Jean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.com10tag:blogger.com,1999:blog-9188714267863327820.post-23248944832169012572023-06-26T18:17:00.001-04:002023-06-26T18:29:45.827-04:00Rows Examined Blindspot when Looking for non-Existing DataWhen trying to understand queries in the slow log, an interesting metric to look at is rows examined. Also, when trying to understand CPU spikes on a MySQL instance, the InnoDB Metric dml_reads and the global status Innodb_rows_read are interesting to check. In a similar way, when trying to gather information about which queries are loading the system, SUM_ROWS_EXAMINED from the Jean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.com0tag:blogger.com,1999:blog-9188714267863327820.post-37923838490060203972023-05-22T09:12:00.004-04:002023-05-22T11:12:05.238-04:00Avoiding a STOP SLAVE Crash with MTR in Percona Server older than 5.7.37-40I am finalizing my Percona Live talk MySQL and Vitess (and Kubernetes) at HubSpot. In this talk, I mentioned that I like that Percona is providing better MySQL with Percona Server. This comes with a little inconvenience though: with improvements, sometimes comes regression. This post is about such regression and a workaround I implemented some time ago (I should have shared it Jean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.com0tag:blogger.com,1999:blog-9188714267863327820.post-33467538825904175932023-03-30T01:00:00.020-04:002023-03-30T01:00:00.141-04:00Running and Mountain Climbing at Percona Live DenverI just booked my travel arrangements for Percona Live 2023. In case you missed it, one of the most important MySQL Conference of the year is happening in Denver from Monday May 22 to Wednesday 24. I will be there and I am giving a talk about how HubSpot operates Percona Server / MySQL with Vitess in Kubernetes. My colleague, Mali Akmanalp, is also speaking about the tools we Jean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.com0tag:blogger.com,1999:blog-9188714267863327820.post-90695928142085414182023-01-23T20:56:00.000-05:002023-01-23T20:56:10.963-05:00Planet for the MySQL Community Graduating from BetaIn May 2020, I published a Planet for the MySQL Community Pluto Beta. I was satisfied with the result and was considering it done, but I did not invest the time to promote it to release (a non-beta site was running for a few months without being advertised). I finally came to it, and today I am happy to deprecate the beta and announce the release of Planet for the MySQL Community.&Jean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.com0tag:blogger.com,1999:blog-9188714267863327820.post-22170146130280862862022-12-12T20:06:00.001-05:002022-12-12T20:06:41.899-05:00Free Page Consumption by InnoDB Table Compression (Percona backoff Algo. part #2)In my previous post about InnoDB Stalls on Empty Free List, I used a test environment that might look a little artificial : a table larger than the InnoDB Buffer Pool but fitting in the Linux Page Cache. This configuration allows serving very quickly what MySQL thinks are IOs because these are hit in the filesystem cache. In this post, I explain why this environment is not Jean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.com1tag:blogger.com,1999:blog-9188714267863327820.post-31000542695395463442022-11-30T18:43:00.002-05:002022-12-12T20:13:33.800-05:00Tail Latencies in Percona Server because of InnoDB Stalls on Empty Free List If, in Percona Server, you are observing tail latencies on queries that should be fast, this might be a side effect of Percona's improved InnoDB Empty Free List Algorithm. When using this algorithm (the default in 5.6 and 5.7 and optional configuration in 8.0), a query needing a free page while none are available waits until the LRU Manager Thread refills the free list. Because this Jean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.com2tag:blogger.com,1999:blog-9188714267863327820.post-64130616247417926322022-11-15T01:00:00.002-05:002022-11-15T07:52:16.162-05:00Bad Optimizer Plan on Queries Combining WHERE, ORDER BY and LIMITSometimes, the MySQL Optimizer chooses a wrong plan, and a query that should execute in less than 0.1 second ends-up running for 12 minutes ! This is not a new problem: bugs about this can be traced back to 2014, and a blog post on the subject dates of 2015. But even if this is old news, because this problem recently came to my attention, it is a problem worth writing on.
ThisJean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.com0tag:blogger.com,1999:blog-9188714267863327820.post-76532112174211829722022-05-31T13:09:00.001-04:002022-05-31T17:05:52.489-04:00Triggering Replication Lag for Testing a ScriptI am currently working on a script to auto-enable parallel replication / multi-threaded replication (MTR) when there is replication lag. For testing this script, I need to trigger replication lag that would disappear after enabling MTR. I came-up with a simple solution for that, and I thought it could be useful to more people, so I am writing this blog post about it. Read-on forJean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.com0tag:blogger.com,1999:blog-9188714267863327820.post-17183244803258618062022-03-08T00:00:00.069-05:002023-06-26T18:28:28.187-04:00Rows Examined not Trustworthy because of Index Condition PushdownWhen Index Condition Pushdown is used, you cannot trust rows examined as reported by the slow query log (nor in some Performance Schema tables) and you cannot trust the global status Innodb_rows_read (nor the InnoDB Metrics dml_reads). These can lead to an incorrect query complexity analysis, to a mistaken estimation of the total load of a MySQL server, and to a lot of wasted time.&Jean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.com0tag:blogger.com,1999:blog-9188714267863327820.post-1292863711077849192022-01-19T01:00:00.001-05:002022-01-19T01:00:00.156-05:00Crashing MySQL with Malicious Intent and a lot of DeterminationA year ago, I blogged about An Unprivileged User can crash your MySQL Server. At the time, I explained how to protect yourself against this problem. A few weeks ago, I revisited this vulnerability in a follow-up post in which I explained the fix, claimed that the MySQL 5.7 default configuration for Group Replication is still problematic, and explained a tuning to avoid the Jean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.com0tag:blogger.com,1999:blog-9188714267863327820.post-73135186664630299902021-12-13T19:49:00.018-05:002022-10-15T09:18:44.820-04:00Trick to Simulate a Linux Server with less RAMI created the first draft of this post many years ago. At that time, I was working with physical servers having 192 GB of RAM or more. On such systems, doing memory pressure tests with MySQL is complicated. I used a trick to simulate a Linux server with less RAM (also works with vms, probably not with Kubernetes or containers). I recently needed the trick again and as I Jean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.com2tag:blogger.com,1999:blog-9188714267863327820.post-54317068248731908312021-11-30T20:17:00.005-05:002022-04-28T11:48:00.376-04:00Follow-up on an Unprivileged User can Crash your MySQL ServerA year ago, I blogged about An Unprivileged User can Crash your MySQL Server. At the time, I presented how to protect yourself against this problem without explaining how to generate a crash. In this post, I am revisiting this vulnerability, not giving the exploit yet, but presenting the fix. Also, because the default configuration of Group Replication in 5.7 is still vulnerableJean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.com0tag:blogger.com,1999:blog-9188714267863327820.post-43822143898323437752021-11-12T01:00:00.001-05:002021-11-12T01:00:00.156-05:00Duplicate Entry in ALTER TABLE (and in OPTIMIZE TABLE)A few weeks ago and in MySQL 5.7, I had an ALTER TABLE that failed with a duplicate entry error. This is old news as it is happening since MySQL 5.6, but I only saw this recently because I am normally using online schema change from the Percona Toolkit (pt-osc) or GitHub's online schema migration (gh-ost). I do not like that and I am disappointed this has not been improved, so this Jean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.com6tag:blogger.com,1999:blog-9188714267863327820.post-83820201531495770122020-12-05T23:52:00.000-05:002020-12-05T23:52:15.404-05:00Fixing low durability GTID replica with VoodooAt my FOSDEM talk earlier this year, I gave a trick for fixing a crashed GTID replica. I never blogged about this, so now is a good time. What is pushing me to write on this today is my talk at MinervaDB Athena 2020 this Friday. At this conference, I will present more details about MySQL replication crash safety. So you know what to do if you want to learn more about Jean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.com0tag:blogger.com,1999:blog-9188714267863327820.post-90833264489609330372020-10-18T21:09:00.006-04:002022-01-19T08:18:37.422-05:00An Unprivileged User can crash your MySQL ServerYes, your read the title correctly: an unprivileged user can crash your MySQL Server. This applies for the default configuration of MySQL 8.0.21 (and it is probably the case for all MySQL 8 GA versions). Depending on your configuration, it might also be the case for MySQL 5.7. This needs malicious intent and a lot of determination, so no need to panic as this will not happen by Jean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.com0tag:blogger.com,1999:blog-9188714267863327820.post-6915459322551691612020-06-07T02:00:00.001-04:002020-06-07T02:00:00.347-04:00Update on Planet for the MySQL Community (June 7, 2020)Three weeks ago, I released the Pluto Beta of Planet for the MySQL Community. Since then, a few things changed and I think it is worth doing an update to the Ecosystem. The change I am the most happy about is that Ivan Groenewold started helping with the project (you can find him in the people of the oursqlcommunity.org GitHub organization). So Planet for the MySQL Community is Jean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.com1tag:blogger.com,1999:blog-9188714267863327820.post-62475325228586314252020-05-14T02:00:00.002-04:002020-06-10T13:28:12.408-04:00Planet [for the] MySQL Community: Pluto BetaAs indicated in a previous post, I am working on Planet [for the] MySQL Community for which I did a RFC on the Requirements. I am happy to announce the beta release of Planet [for the] MySQL Community using the Planet Pluto Free Feed Reader and (Static) Website Generator. If you are in a hurry, you can check it out at https://planet-beta-pluto.oursqlcommunity.org/. This post Jean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.com4tag:blogger.com,1999:blog-9188714267863327820.post-75501375378028451322020-05-12T02:00:00.000-04:002020-05-15T11:31:17.830-04:00Planet MySQL Community: Requirements RFCAs indicated in a previous post, I am working on a Planet MySQL Community (this name is not final, see below). In this post, I want to present what I think the requirements should be for this new aggregator. This is a Request for Comment (RFC): I do not claim completeness nor perfection, this might (will) evolve, and your remarks are welcome. A tl&dr is available in the Jean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.com2tag:blogger.com,1999:blog-9188714267863327820.post-84717977114453106162020-04-24T11:32:00.001-04:002020-05-12T08:56:43.153-04:00Planning for the AFTER Planet MySQL (bis)Oups, my previous post had a forbidden word and did not show-up on Planet MySQL, so retry...
As written in a previous post, the state of Planet MySQL is unhealthy ! I am still aggregated there for now as, before leaving what was the best news-feed for the MySQL Community, we need a replacement. This post aims at starting a discussion on this replacement.
Update Jean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.com0tag:blogger.com,1999:blog-9188714267863327820.post-11449657233643656582020-04-24T10:24:00.004-04:002020-05-12T08:56:39.577-04:00Planning for the AFTER Planet MySQLAs written in my previous post, the state of Planet MySQL is unhealthy ! I am still aggregated there for now as, before leaving what was the best news-feed for the MySQL Community, we need a replacement. This post aims at starting a discussion on this replacement.
Update 2020-05-12: I wrote a follow-up post RFC on Requirements for Planet MySQL Community.
The ideal thing would Jean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.com0tag:blogger.com,1999:blog-9188714267863327820.post-40838536296063704912020-04-24T02:55:00.002-04:002020-05-10T09:23:18.638-04:00State of Planet MySQL: unhealthy !Also, the state of bugs.mysql.com: unhealthy (last paragraph below, but this would be too long a title).
I have been complaining for some time about Planet MySQL filtering content that I think it should not. Shlomi made a courageous decision on this and explained it in his recent post (Pulling his blog out of Planet MySQL aggregator, over community concerns). I am thinking of doing Jean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.com6tag:blogger.com,1999:blog-9188714267863327820.post-43984697867896475322020-03-30T02:40:00.000-04:002020-05-05T20:08:58.669-04:00JFG MySQL Advice during COVID-19Update 2020-05-05: I stopped providing consulting hours. They were not very popular, but it was worth trying.
We are all impacted by COVID-19. On my side, this virus disrupted my travel, professional and personal plans for the upcoming months. To overcome these disruptions, and following the idea of someone on MySQL Community Slack, I am experimenting with giving MySQL advice Jean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.com0tag:blogger.com,1999:blog-9188714267863327820.post-56204789143433684632020-01-27T02:42:00.000-05:002020-01-27T02:42:46.145-05:00A Legacy Behavior of MySQL Corrupting Restored Backups (replicate-same-server-id = OFF)In my previous post (Puzzled by MySQL Replication), I describe a weird, but completely documented, behavior of replication that had me scratching my head for hours because it was causing data corruption. I did not give too many details then as I also wanted allowing you to scratch your head if you wished. In this post, I describe this behavior in more details.
But first I need to Jean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.com0tag:blogger.com,1999:blog-9188714267863327820.post-74178640520258353582020-01-09T01:00:00.000-05:002020-01-27T02:45:09.629-05:00Puzzled by MySQL Replication (War Story)Recently, I was puzzled by MySQL replication ! Some weird, but completely documented, behavior of replication had me scratching my head for hours. I am sharing this war story so you can avoid losing time like me (and also maybe avoid corrupting your data when restoring a backup). The exact justification will come in a follow-up post, so you can also scratch your head tryingJean-François Gagnéhttp://www.blogger.com/profile/12267071794432977020noreply@blogger.com5