<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Marcelo Altmann on Medium]]></title>
        <description><![CDATA[Stories by Marcelo Altmann on Medium]]></description>
        <link>https://medium.com/@altmannmarcelo?source=rss-a4227e11d0ab------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*QqoR_og0BHIex3Gf</url>
            <title>Stories by Marcelo Altmann on Medium</title>
            <link>https://medium.com/@altmannmarcelo?source=rss-a4227e11d0ab------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 02:00:08 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@altmannmarcelo/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[JSON Duality Views in MySQL 9.7 — What you need to know]]></title>
            <link>https://altmannmarcelo.medium.com/json-duality-views-in-mysql-9-7-what-you-need-to-know-382b4c6a4cd3?source=rss-a4227e11d0ab------2</link>
            <guid isPermaLink="false">https://medium.com/p/382b4c6a4cd3</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[json]]></category>
            <dc:creator><![CDATA[Marcelo Altmann]]></dc:creator>
            <pubDate>Fri, 29 May 2026 23:29:24 GMT</pubDate>
            <atom:updated>2026-05-29T23:29:24.189Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Wpxev4M6CASg4ZCP5GkVfQ.jpeg" /></figure><p>MySQL 9.7 was released as the new LTS, and one of the features that caught my attention is JSON Duality Views. I have written about a few other 9.7 changes already, so this time I wanted to learn about Duality Views and share what I’ve learned.</p><p>The idea behind a JSON Duality View is simple to state but powerful in practice: the same data is exposed both as normalized relational tables (Your usual SELECT) and as a hierarchical JSON document, at the same time. You can read it as a document, and you can write a document back, and MySQL takes care of mapping it to the underlying rows. There is no second copy of the data and no synchronization process to maintain. Oracle database introduced a similar concept, and 9.7 brings an implementation to MySQL — and, importantly, it is available in Community Edition.</p><p>All the examples below were executed on a MySQL 9.7.0 Community server. To keep things familiar, I will model a football club and its squad, using Grêmio Foot-Ball Porto Alegrense.</p><pre>mysql&gt; SELECT VERSION();<br>+-----------+<br>| VERSION() |<br>+-----------+<br>| 9.7.0     |<br>+-----------+</pre><h3>The underlying tables</h3><p>There is nothing special about the schema. It’s a two tables and a foreign key:</p><pre>CREATE TABLE club   (id INT PRIMARY KEY, name VARCHAR(60));<br>CREATE TABLE player (id INT PRIMARY KEY, club_id INT, name VARCHAR(60), position VARCHAR(30),<br>                     FOREIGN KEY (club_id) REFERENCES club(id));</pre><p>A player belongs to a club through club_id, with no duplicated data and full referential integrity.</p><h3>Creating the view</h3><p>A Duality View is created with the JSON_DUALITY_OBJECT function, where you map each JSON key to a table column. Here I nest the players of each club inside the club document, under a squad array:</p><pre>CREATE JSON DUALITY VIEW club_dv AS<br>SELECT JSON_DUALITY_OBJECT(WITH (INSERT, UPDATE, DELETE)<br>   &#39;_id&#39;    : club.id,<br>   &#39;name&#39;   : club.name,<br>   &#39;squad&#39;  : ( SELECT JSON_ARRAYAGG(<br>                  JSON_DUALITY_OBJECT(WITH (INSERT, UPDATE, DELETE)<br>                     &#39;id&#39;       : player.id,<br>                     &#39;name&#39;     : player.name,<br>                     &#39;position&#39; : player.position ))<br>                FROM player WHERE player.club_id = club.id )<br>) FROM club;</pre><p>The WITH (INSERT, UPDATE, DELETE) clause is what grants write access through the view. One detail worth noting is that it is declared per object level — once for the club and again for the nested players. I will come back to this shortly.</p><h3>Inserting a document</h3><p>This is the part that makes the feature interesting. Instead of inserting into each table separately, I insert a single JSON document, and MySQL distributes it across both tables. Let’s register Grêmio with an initial squad:</p><pre>INSERT INTO club_dv VALUES (&#39;{<br>  &quot;_id&quot;: 1,<br>  &quot;name&quot;: &quot;Gremio Foot-Ball Porto Alegrense&quot;,<br>  &quot;squad&quot;: [<br>    {&quot;id&quot;: 9, &quot;name&quot;: &quot;Carlos Vinicius&quot;, &quot;position&quot;: &quot;Forward&quot;},<br>    {&quot;id&quot;: 5, &quot;name&quot;: &quot;Arthur&quot;,          &quot;position&quot;: &quot;Midfielder&quot;}<br>  ]<br>}&#39;);</pre><p>That single statement created the club row and the two player rows.</p><h3>Reading the document</h3><p>When I query the view, the rows are assembled back into the document:</p><pre>mysql&gt; SELECT * FROM club_dv;</pre><pre>{&quot;_id&quot;: 1, &quot;name&quot;: &quot;Gremio Foot-Ball Porto Alegrense&quot;, &quot;squad&quot;: [{&quot;id&quot;: 5, &quot;name&quot;: &quot;Arthur&quot;, &quot;position&quot;: &quot;Midfielder&quot;}, {&quot;id&quot;: 9, &quot;name&quot;: &quot;Carlos Vinicius&quot;, &quot;position&quot;: &quot;Forward&quot;}], &quot;_metadata&quot;: {&quot;etag&quot;: &quot;ccfb9fcb008c0dfe11790a83a3d31743&quot;}}</pre><p>The document is built live from the join, so nothing is stored in JSON form. If I change a row in the player table directly, the next read of the view reflects it immediately, because there is only one copy of the data. Notice the _metadata.etag field — I will explain it later.</p><h3>Signing a new player</h3><p>Grêmio signs Gabriel Mec. To add him, I update the document with the new squad, and MySQL figures out that a new player row is required:</p><pre>UPDATE club_dv<br>SET data = &#39;{<br>  &quot;_id&quot;: 1,<br>  &quot;name&quot;: &quot;Gremio Foot-Ball Porto Alegrense&quot;,<br>  &quot;squad&quot;: [<br>    {&quot;id&quot;: 9,  &quot;name&quot;: &quot;Carlos Vinicius&quot;, &quot;position&quot;: &quot;Forward&quot;},<br>    {&quot;id&quot;: 5,  &quot;name&quot;: &quot;Arthur&quot;,          &quot;position&quot;: &quot;Midfielder&quot;},<br>    {&quot;id&quot;: 10, &quot;name&quot;: &quot;Gabriel Mec&quot;,     &quot;position&quot;: &quot;Attacking Midfielder&quot;}<br>  ]<br>}&#39;<br>WHERE data-&gt;&gt;&#39;$._id&#39; = &#39;1&#39;;</pre><p>Looking at the relational tables afterward, the new player is there:</p><pre>mysql&gt; SELECT * FROM club; SELECT * FROM player ORDER BY id;<br>+----+----------------------------------+<br>| id | name                             |<br>+----+----------------------------------+<br>|  1 | Gremio Foot-Ball Porto Alegrense |<br>+----+----------------------------------+<br>+----+---------+-----------------+----------------------+<br>| id | club_id | name            | position             |<br>+----+---------+-----------------+----------------------+<br>|  5 |       1 | Arthur          | Midfielder           |<br>|  9 |       1 | Carlos Vinicius | Forward              |<br>| 10 |       1 | Gabriel Mec     | Attacking Midfielder |<br>+----+---------+-----------------+----------------------+</pre><p>I never ran an INSERT INTO player for Gabriel Mec. He was created simply because he appeared in the squad document. The application works with documents, while the database remains normalized and constraint-checked.</p><h3>Write permissions are explicit</h3><p>As I mentioned, write access is granted per level through the WITH clause. A view created without it is read-only, and MySQL is clear about it when you try to write through a level that was not granted access:</p><pre>mysql&gt; CREATE OR REPLACE JSON DUALITY VIEW ro_dv AS<br>       SELECT JSON_DUALITY_OBJECT(&#39;_id&#39;:club.id,&#39;name&#39;:club.name) FROM club;</pre><pre>mysql&gt; INSERT INTO ro_dv VALUES (&#39;{&quot;_id&quot;:2,&quot;name&quot;:&quot;Test&quot;}&#39;);<br>ERROR 6490 (HY000): Missing INSERT tag on table `blog`.`club`, path &#39;$&#39;.</pre><p>This is useful in practice. You can expose a club document that is updatable while keeping its squad read-only, or the other way around, just by choosing where to place the tags.</p><h3>The etag and optimistic concurrency</h3><p>Going back to the _metadata.etag field: it is a hash of the document content, and it is how Duality Views support optimistic concurrency. The important detail — and one that caught me out at first — is that the check is opt-in. It only happens if you send the etag back inside the document on the update. Look again at the update I ran when signing Gabriel Mec: there was no _metadata in the document I wrote, so MySQL applied it unconditionally. That is a last-write-wins update, with no protection at all. The etag was simply ignored.</p><p>To actually use it, you include the etag you read in the document you write back. Let’s walk through what happens with two clients editing the same club.</p><p>Client A reads the document and keeps its etag:</p><pre>mysql&gt; SELECT data-&gt;&gt;&#39;$._metadata.etag&#39; FROM club_dv WHERE data-&gt;&gt;&#39;$._id&#39;=&#39;1&#39;;<br>+----------------------------------+<br>| data-&gt;&gt;&#39;$._metadata.etag&#39;        |<br>+----------------------------------+<br>| 740b53ebf18a417fb548281b47cf45df |<br>+----------------------------------+  </pre><p>Before Client A saves, Client B updates the same club — adding some other player to the squad — and its write succeeds. The document in the database now has a different etag.</p><p>When Client A finally tries to save its own edit, it sends the etag it read earlier, which is now stale:</p><pre>mysql&gt; UPDATE club_dv        SET data = &#39;{&quot;_id&quot;:1,&quot;name&quot;:&quot;Gremio (A edit)&quot;,<br>                    &quot;_metadata&quot;:{&quot;etag&quot;:&quot;740b53ebf18a417fb548281b47cf45df&quot;},<br>                    &quot;squad&quot;:[{&quot;id&quot;:9,&quot;name&quot;:&quot;Carlos Vinicius&quot;,&quot;position&quot;:&quot;Striker&quot;}]}&#39;        WHERE data-&gt;&gt;&#39;$._id&#39;=&#39;1&#39;;<br>ERROR 6494 (HY000): Cannot update JSON duality view. The ETAG of the document in the database did not match the ETAG &#39;&quot;740b53ebf18a417fb548281b47cf45df&quot;&#39; passed in.</pre><p>MySQL rejects the write because the document changed since Client A read it. This is what prevents Client A from silently overwriting Client B’s change — the classic lost update. If Client A had sent the current etag instead, the update would have gone through.</p><p>So the etag does not protect you automatically. It gives you the tool to implement optimistic locking, and it is up to the application to read the etag, send it back on update, and handle the 6494 error by re-reading and retrying. If you omit the etag, the update always wins, which is convenient but offers no concurrency guarantee.</p><h3>Inspecting the view</h3><p>Duality Views are not a black box. MySQL 9.7 adds a set of INFORMATION_SCHEMA views that describe the mapping. The first one shows the view and its permissions:</p><pre>mysql&gt; SELECT TABLE_NAME, JSON_COLUMN_NAME, ROOT_TABLE_NAME,<br>              ALLOW_INSERT, ALLOW_UPDATE, ALLOW_DELETE<br>       FROM INFORMATION_SCHEMA.JSON_DUALITY_VIEWS WHERE TABLE_SCHEMA=&#39;blog&#39;;<br>+------------+------------------+-----------------+--------------+--------------+--------------+<br>| TABLE_NAME | JSON_COLUMN_NAME | ROOT_TABLE_NAME | ALLOW_INSERT | ALLOW_UPDATE | ALLOW_DELETE |<br>+------------+------------------+-----------------+--------------+--------------+--------------+<br>| club_dv    | data             | club            |            1 |            1 |            1 |<br>+------------+------------------+-----------------+--------------+--------------+--------------+</pre><p>The next one shows how each JSON key maps to a column:</p><pre>mysql&gt; SELECT REFERENCED_TABLE_NAME tbl, REFERENCED_COLUMN_NAME col, JSON_KEY_NAME json_key<br>       FROM INFORMATION_SCHEMA.JSON_DUALITY_VIEW_COLUMNS<br>       WHERE TABLE_SCHEMA=&#39;blog&#39; AND TABLE_NAME=&#39;club_dv&#39;;<br>+--------+----------+----------+<br>| tbl    | col      | json_key |<br>+--------+----------+----------+<br>| club   | id       | _id      |<br>| club   | name     | name     |<br>| player | id       | id       |<br>| player | name     | name     |<br>| player | position | position |<br>+--------+----------+----------+</pre><p>And the last one shows how the levels are linked — in this case the squad array is the relationship between club and player:</p><pre>mysql&gt; SELECT PARENT_TABLE_NAME, CHILD_TABLE_NAME, PARENT_COLUMN_NAME,<br>              CHILD_COLUMN_NAME, JSON_KEY_NAME<br>       FROM INFORMATION_SCHEMA.JSON_DUALITY_VIEW_LINKS<br>       WHERE TABLE_SCHEMA=&#39;blog&#39; AND TABLE_NAME=&#39;club_dv&#39;;<br>+-------------------+------------------+--------------------+-------------------+---------------+<br>| PARENT_TABLE_NAME | CHILD_TABLE_NAME | PARENT_COLUMN_NAME | CHILD_COLUMN_NAME | JSON_KEY_NAME |<br>+-------------------+------------------+--------------------+-------------------+---------------+<br>| club              | player           | id                 | club_id           | squad         |<br>+-------------------+------------------+--------------------+-------------------+---------------+</pre><h3>How this compares to what we did before</h3><p>Before 9.7, getting a document shape out of relational data in MySQL usually meant one of three things. You could assemble the JSON in the application after running several queries, which spreads the join logic across your services. You could build the document with JSON_OBJECT and JSON_ARRAYAGG in a regular view, but that view was read-only — you could produce the document but not write through it. Or you could store a denormalized JSON column, which leaves you with two copies of the data to keep in sync.</p><p>Duality Views address all three. The document is writable, the data is never duplicated, and the relational constraints are still enforced — a document that would violate a foreign key or a data type is simply rejected.</p><h3>Final thoughts</h3><p>JSON Duality Views bring the document and relational models closer together, and having a working implementation in the Community LTS is good news. If you build APIs on top of MySQL, this is worth testing: your application can exchange whole documents while the schema underneath stays normalized and consistent.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=382b4c6a4cd3" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[A Brief History of MySQL Replication]]></title>
            <link>https://altmannmarcelo.medium.com/a-brief-history-of-mysql-replication-85f057922800?source=rss-a4227e11d0ab------2</link>
            <guid isPermaLink="false">https://medium.com/p/85f057922800</guid>
            <category><![CDATA[replication]]></category>
            <category><![CDATA[mysql]]></category>
            <dc:creator><![CDATA[Marcelo Altmann]]></dc:creator>
            <pubDate>Tue, 13 May 2025 16:22:24 GMT</pubDate>
            <atom:updated>2025-05-13T16:22:24.320Z</atom:updated>
            <content:encoded><![CDATA[<p>While preparing slides for a presentation, I found myself researching about the history of MySQL Replication and decided to write some key moments of this wonderful functionality of MySQL.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XFyPg-ro8mHwe2RfPsY7Zg.png" /></figure><p>Replication has been one of MySQL’s most powerful and relied-upon features since the early days — long before it had things like foreign keys or even subqueries. It’s one of the foundational pillars that made MySQL suitable for large-scale, production use. This blog post walks through how replication evolved over time, and why it remains one of the strongest features in the MySQL ecosystem.</p><h3>The Beginning (MySQL 3.23 — early 2000s)</h3><p>MySQL introduced statement-based replication (SBR) in version 3.23.15 in May 2000. This was a major milestone: it allowed users to replicate changes from one server (the source, previously called master) to others (replicas, previously slaves) by logging SQL statements executed on the source and replaying them on replicas.</p><p>At that time:</p><ul><li>There was no GTID, no global transaction ordering.</li><li>Binary logging was designed to be simple: just a serialized list of SQL statements that changed data.</li><li>It was already robust enough to power some of the earliest large web platforms.</li></ul><p>Despite its simplicity, this model scaled out read traffic effectively, and quickly became a foundation of MySQL’s production viability.</p><h3>Evolution: Mixed &amp; Row-Based Replication (MySQL 5.1–2006)</h3><p>As MySQL adoption grew, limitations of statement-based replication became more obvious:</p><ul><li>Non-deterministic queries (NOW(), UUID(), etc.) would cause divergence.</li><li>Triggers were tricky to handle.</li><li>Complex updates weren’t always reproducible exactly the same way on replicas.</li></ul><p>To address this, row-based replication (RBR) was introduced in MySQL 5.1 alongside mixed-mode replication (which could dynamically choose between SBR and RBR depending on the query).</p><p>This was also when the binlog_format setting was added, allowing control over replication style:</p><ul><li>STATEMENT</li><li>ROW</li><li>MIXED</li></ul><p>Row-based replication dramatically improved data consistency, especially in systems with complex logic or heavy write loads.</p><h3>Modernization: GTIDs and Crash Safety (MySQL 5.6–2013)</h3><p>By MySQL 5.6, replication had matured:</p><ul><li><strong>Global Transaction Identifiers (GTIDs)</strong> were added to uniquely track and coordinate replication progress across servers.</li><li><strong>Semi-synchronous replication </strong>allowed sources to wait for at least one replica to acknowledge a transaction.</li><li><strong>Replication crash recovery</strong> became safer with relay_log_info_repository=TABLE and relay_log_recovery=ON.</li></ul><p>This opened the door to automated failover, topology reconfiguration, and better tooling to manage large MySQL deployments.</p><h3>Today: Multi-Source, Parallelism, and Group Replication</h3><p>Modern MySQL versions (8.x and beyond) have added:</p><ul><li><strong>Multi-threaded replication</strong>, which allows applying transactions in parallel using multiple worker threads. In MySQL 5.7+, parallelism can be configured per schema or based on write sets (group commit). This drastically reduces replication lag on high-throughput systems.</li><li><strong>Multi-source replication</strong>, allowing a replica to replicate from multiple sources.</li><li><strong>Fine-grained crash recovery</strong>, <strong>delayed replicas</strong>, and <strong>binlog compression, Minimal Row Based Replication</strong></li><li>Deep integration with <strong>MySQL Group Replication</strong>, a high-availability plugin built on top of the binary log and GTIDs. Replication forms the backbone of Group Replication’s .</li></ul><p>Looking ahead, the MySQL 9.x series is expected to <strong>remove support for </strong><strong>STATEMENT and </strong><strong>MIXED binlog formats</strong>, consolidating replication around <strong>row-based replication</strong> (ROW). This is a natural evolution given RBR&#39;s superior determinism and specially reliability.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=85f057922800" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[From User to Contributor: Fixing and Enhancing MySQL Tools]]></title>
            <link>https://altmannmarcelo.medium.com/from-user-to-contributor-fixing-and-enhancing-mysql-tools-67cef460f7bf?source=rss-a4227e11d0ab------2</link>
            <guid isPermaLink="false">https://medium.com/p/67cef460f7bf</guid>
            <category><![CDATA[open-source]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[readyset]]></category>
            <dc:creator><![CDATA[Marcelo Altmann]]></dc:creator>
            <pubDate>Tue, 06 May 2025 15:47:58 GMT</pubDate>
            <atom:updated>2025-05-06T16:04:50.287Z</atom:updated>
            <content:encoded><![CDATA[<p>Working with open source tools is always a rewarding experience. You can dig into the source code to understand how things work or troubleshoot issues as they come up. And when you do, there’s a great opportunity to give back — whether by fixing a bug or adding a new feature.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QRtuQGUIU8GlZCRcwyVk6A.png" /></figure><p>At <a href="https://readyset.io/">Readyset</a>, which is fully written in Rust, we make heavy use of the MySQL Rust driver. Since we benefit from these open source projects, one way to show appreciation is by contributing time and effort to improve them. Recently, I had the chance to contribute to both the MySQL Server and the MySQL Rust driver, helping fix issues and push the tools forward.</p><p>Here are a few contributions I’ve made to both projects — some recent, some from a bit further back:</p><p><a href="https://github.com/blackbeam/rust_mysql_common/pull/170">Binlog Time Overflow</a> (Rust) — The issue was caused by an overflow in an unsigned 1-byte integer (u8). When the number of hours exceeded 255 — the maximum value that fits in 8 bits — it wrapped around to zero. This led the driver to report an incorrect number of hours during binlog parsing.</p><p><a href="https://github.com/blackbeam/rust_mysql_common/pull/167">Optional Metadata Parse</a> (Rust)— This issue stemmed from how the Optional Metadata field in the Table Map Event (TME) of the binlog was parsed. The TME includes metadata — such as collation and whether a column is signed or unsigned — for all columns in the table, not just the ones present in a particular event. Since multiple WRITE_ROW_EVENTs within the same transaction can share the same TME, this metadata is reused.<br>Under Minimal Row-Based Replication, columns omitted in an INSERT are not included in the row event. As a result, the parser was incorrectly interpreting the metadata, leading to inconsistencies when decoding rows.</p><p><a href="https://github.com/blackbeam/rust_mysql_common/pull/135">Collation Data Dictionary</a> (Rust)— This feature introduced a data dictionary for looking up collations. In the MySQL binlog protocol, every string-related field is associated with a collation, and the collation determines the character set. To retrieve the character set — or to understand properties like the maximum number of bytes per character — you need a data dictionary to map collation IDs to their corresponding character set details.</p><p><a href="https://github.com/blackbeam/rust_mysql_common/pull/104">Binlog Transaction Compression Support</a> (Rust)— This feature added support for parsing the TRANSACTION_PAYLOAD_EVENT (0x28) from binlogs. This event type compresses multiple binlog events using ZSTD to reduce disk usage and network bandwidth.</p><p><a href="https://github.com/mysql/mysql-server/commit/749fd742b1c83d38590afb1364fd40fd3edfcb66#diff-0464eed4eba118bfe876fa26af42bbf449724a332247e64685cd5b73f1922a51">Log_status Table report wrong GTID</a> (MySQL 9.3) — Under high load and concurrency, MySQL can sometimes report an incorrect GTID when querying the log_status table. This issue previously affected Percona XtraBackup. I analyzed the root cause, provided a reproducible test case, and proposed a fix. While the final patch that was accepted differed slightly from mine, my analysis contributed to the direction of the final solution.</p><p><a href="https://github.com/mysql/mysql-server/pull/586">Third decimal place for ms</a> (MySQL 9.3) — Since its inception, the MySQL client has always displayed only two decimal places for milliseconds. At Readyset, we aim for microsecond-level query latency, so confirming that our performance stays well below the millisecond mark is crucial. The lack of visibility between 0 and 9 milliseconds has always been a challenge for us.</p><p>This is the beauty of open source — you’re not just a user, but part of a community. Whether it’s through bug reports, patches, feature contributions, or even just sharing knowledge, giving back in any form helps the tools we rely on grow stronger. If we benefit from open source, we should all look for ways to contribute in return.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=67cef460f7bf" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[MySQL & Heatwave Summit Recap]]></title>
            <link>https://altmannmarcelo.medium.com/mysql-heatwave-summit-recap-7af4549439c9?source=rss-a4227e11d0ab------2</link>
            <guid isPermaLink="false">https://medium.com/p/7af4549439c9</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[mysql]]></category>
            <dc:creator><![CDATA[Marcelo Altmann]]></dc:creator>
            <pubDate>Mon, 28 Apr 2025 22:37:45 GMT</pubDate>
            <atom:updated>2025-04-28T22:37:45.071Z</atom:updated>
            <content:encoded><![CDATA[<p>Last week, I had the chance to attend the MySQL &amp; HeatWave Summit, where I also got to talk about Readyset. It was a jam-packed two days, full of back-to-back sessions focused on MySQL HeatWave and Enterprise features. Alongside all the technical deep dives, there were also great Community talks showcasing some of the largest MySQL deployments out there. Tons of learning, lots of interesting conversations — overall, a fantastic event!</p><p>Opening Keynote</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qdVwVw9CcddLlq8Q3WO59w.jpeg" /></figure><p>The first day kicked off with an opening keynote from Wim Coekaerts, where he walked us through everything new since last year’s event. One thing that really caught my attention was the announcement of a new feature in MySQL — and even some changes at the Linux kernel level — that will allow MySQL to write pages back to the filesystem using InnoDB page sizes. This could make it possible to disable the Double Write Buffer entirely. There weren’t a lot of details shared yet, but it sounds like Oracle will be making a formal announcement about it in an upcoming release. Definitely something to keep an eye on!</p><p>Another highlight from the keynote? Our event made it onto the big screen! — More details <a href="https://anotherboringtechblog.com/2025/04/mysql-brazil-meetup-2025/">https://anotherboringtechblog.com/2025/04/mysql-brazil-meetup-2025/</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*oqMIdmjvfjjyOgMhDq-ayA.png" /></figure><p>Readyset had a featured talk in the second slot on day one — during this session, Gautam (Readyset’s CEO) and I walked through why having a caching strategy is critical to complement your database. We also showed a live demo of Readyset hitting 25K QPS on a single CPU core, with an impressive 25 microseconds of latency. Super exciting to share that performance live!</p><p>Later, Alexander Martinez from Yelp gave a great talk on their journey upgrading MySQL to version 8.0. He dove into the steps they took and some of the challenges they hit along the way — including a few surprises caused by Percona XtraBackup defaults like lock-ddl and safe-slave-updates being enabled by default. Funny enough, I might know who was responsible for turning those defaults on back in the day! :P</p><p>Norvald Ryeng from Oracle followed up with an awesome deep dive into the MySQL Optimizer. He explained the challenges and limitations of the current design, and how a new optimizer architecture is being developed to fix those issues. Since the optimizer is the brain making decisions like join order planning during query execution, it needs to be both fast and smart — definitely a critical area of improvement.</p><p>Daniel Van Eeden from PingCAP gave a talk about the go-mysql library, covering the defaults around replication streams and sharing some interesting insights into the MySQL wire protocol — lots of good low-level details there.</p><p>Finally, Raja Sriram Ganesan from Uber shared how they handle primary cluster promotions during failovers. It’s always fascinating to hear how companies operating at Uber’s scale deal with these complex infrastructure challenges.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZlAXBmrDByHUs7blmzultA.png" /></figure><p>To kick off the second day of talks, René Cannaò — CEO of ProxySQL — gave a great session on how ProxySQL can take your MySQL setup to the next level. He covered all the ways ProxySQL can help with scalability, performance, and reliability. At Readyset, we’re proud to partner with ProxySQL — it’s a super mature project with a rich set of features that really complements what we’re building.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*E_gLjeschWHoT5IU6ha1Ag.jpeg" /></figure><p>Later on, Ronald Bradford gave a talk about the deprecation and upcoming removal of mysql_native_password. He walked us through why this change is necessary and how caching_sha2_password addresses the security issues that existed with the old method. It was a great reminder of how important it is to stay on top of authentication changes — especially with security becoming more and more critical every day.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lFDbDX_rYGy5g_0tPdhAvQ.jpeg" /></figure><p>While chatting with some of the folks from Oracle, they all shared that they expect this event to keep growing bigger and better each year. It was really great to see so many people from the MySQL community speaking, attending, and just being part of the energy.</p><p>Overall, I had a fantastic time during those two days — I was able to connect (and reconnect) with a lot of old friends from the community. It’s definitely an event I’ll be putting on my calendar again for next year. Already looking forward to it!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7af4549439c9" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[MySQL now reports 3 decimal places for milliseconds]]></title>
            <link>https://altmannmarcelo.medium.com/mysql-now-reports-3-decimal-places-for-milliseconds-aa745c7681e8?source=rss-a4227e11d0ab------2</link>
            <guid isPermaLink="false">https://medium.com/p/aa745c7681e8</guid>
            <category><![CDATA[relational-databases]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[readyset]]></category>
            <dc:creator><![CDATA[Marcelo Altmann]]></dc:creator>
            <pubDate>Fri, 28 Feb 2025 15:53:11 GMT</pubDate>
            <atom:updated>2025-02-28T15:59:40.719Z</atom:updated>
            <content:encoded><![CDATA[<h3>Motivation</h3><p>At <a href="https://readyset.io/">Readyset</a>, we frequently analyze query latency to fine-tune performance. Our built-in metrics provide end-to-end timing breakdowns, helping us measure everything from <strong>query parsing to execution</strong> (whether it’s a cache hit or miss).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*C-DC3q0Qplfubst7jHKDiA.png" /><figcaption>Query latency varying from 5 microseconds(μs) on p50 to 12.5 microseconds(μs) on p99</figcaption></figure><p>We often deal with extremely fast queries — some completing in as little as <strong>5 microseconds (μs) at p50 and 12.5μs at p99</strong>. However, when using the standard mysql client, we noticed an issue:</p><p>Queries that take less than a second are reported with only <strong>two decimal places</strong>. For example:</p><pre>mysql&gt; SELECT 1;<br>+---+<br>| 1 |<br>+---+<br>| 1 |<br>+---+<br>1 row in set (0,00 sec)</pre><p>At first glance, it looks like the query took <strong>exactly 0 milliseconds</strong>, but that’s misleading. The actual execution time could be anywhere between <strong>0.000 and 0.009 seconds</strong> — a missing level of precision that makes it difficult to measure sub-millisecond latencies.</p><p>For instance, if a query took <strong>5μs</strong>, that means we could theoretically execute it <strong>1,800 times in just 9ms</strong>, but the current reporting doesn’t reflect that level of accuracy.</p><h3><strong>Fixing the Issue &amp; Community Engagement</strong></h3><p>To address this, I initially patched my local MySQL client to <strong>display three decimal places</strong>. Before submitting my fix, I researched existing bug reports and found that <a href="https://www.linkedin.com/in/dveeden/"><strong>Daniel</strong></a> had submitted a patch a few years ago (<a href="https://bugs.mysql.com/bug.php?id=84007">Bug #84007</a>). His patch had received review comments but wasn’t fully merged.</p><p>Rather than starting from scratch, I decided to build on Daniel’s work and address the review feedback. I submitted a GitHub PR, which was later converted into an official MySQL bug (<a href="https://bugs.mysql.com/bug.php?id=117270">Bug #117270</a>). This time, it was accepted and will be included in <strong>MySQL 9.3</strong>.</p><p>With this fix, MySQL now properly reports milliseconds with three decimal places:</p><pre>mysql&gt; SELECT 1;<br>+---+<br>| 1 |<br>+---+<br>| 1 |<br>+---+<br>1 row in set (0,002 sec)</pre><p>Previously, this query would have shown 0.00 sec, omitting the fact that it actually took <strong>2ms</strong> to complete (which, in this case, was likely due to Docker network overhead).</p><p>This was a fun community contribution, and I’m excited to see it land in <strong>MySQL 9.3</strong>! 🎉</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=aa745c7681e8" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[MySQL GTID Tags — What you need to know]]></title>
            <link>https://altmannmarcelo.medium.com/mysql-gtid-tags-what-you-need-to-know-87aca6b2e40b?source=rss-a4227e11d0ab------2</link>
            <guid isPermaLink="false">https://medium.com/p/87aca6b2e40b</guid>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Marcelo Altmann]]></dc:creator>
            <pubDate>Mon, 04 Mar 2024 15:55:59 GMT</pubDate>
            <atom:updated>2024-03-04T15:55:59.146Z</atom:updated>
            <content:encoded><![CDATA[<h3>MySQL GTID Tags — What you need to know</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yY-9R9q453umwdMSRxWOww.jpeg" /><figcaption>MySQL GTID Structure</figcaption></figure><p>MySQL Innovate Release 8.3 introduction of GTID (Global Transaction Identifiers) tags in replication processes is a significant enhancement in MySQL management and operations. This new functionality, provides users with the ability to tag groups of transactions for easier identification. Here’s a guide into what GTID tags means, their benefits, and how to utilize them.</p><h3>Understanding GTID Tags</h3><p>The GTID tag extends the format of GTIDs in MySQL Replication and Group Replication, allowing for the assignment of a unique name to groups of transactions. This feature enables users to distinguish between different types of transactions, such as differentiating data operations from administrative operations like an ad-hoc data archiving, through the use of tags.</p><p>The format for the new GTID is UUID:&lt;TAG&gt;:NUMBER, where &lt;TAG&gt; is an arbitrary string that can be up to <a href="https://github.com/mysql/mysql-server/blob/mysql-8.3.0/libs/mysql/gtid/gtid_constants.h#L43">32 characters long</a>. This tagging is enabled by setting the gtid_next system variable to AUTOMATIC:&lt;TAG&gt;, which persists for all transactions originating in the current session unless changed or disconnected.</p><h3>Setting Up GTID Tags</h3><p>To configure GTID tags, you need to modify the gtid_next system variable either to AUTOMATIC:&lt;TAG&gt; for session-persistent tags or to &lt;UUID&gt;:&lt;TAG&gt;:NUMBER for assigning a custom UUID and tag to a single transaction. Here are examples of how to apply both settings:</p><ul><li>For session-persistent tags:</li></ul><pre>SET gtid_next = &#39;AUTOMATIC:myTag&#39;;</pre><ul><li>For a single transaction with a custom UUID and tag:</li></ul><pre>SET gtid_next = &#39;a_uuid_here:customTag:1&#39;;</pre><p>After setting up the gtid_next variable as desired, you can proceed with your transactions, and the specified tags will be applied accordingly.</p><h3>New Privileges and Security</h3><p>The introduction of GTID tags also brings a new privilege, TRANSACTION_GTID_TAG, necessary for setting the gtid_next to either AUTOMATIC:&lt;TAG&gt; or &lt;UUID&gt;:&lt;TAG&gt;:NUMBER.</p><pre>mysql&gt; SET gtid_next = &#39;AUTOMATIC:myTag&#39;;<br>ERROR 6038 (HY000): Access denied; you need the TRANSACTION_GTID_TAG and at least one of the: SYSTEM_VARIABLES_ADMIN, SESSION_VARIABLES_ADMIN or REPLICATION_APPLIER privilege(s) for this operation<br>mysql&gt;</pre><p>This means that only users or roles with this privilege can assign tags, adding a layer of security and control over transaction tagging.</p><h3>Use Cases and Benefits</h3><p>The primary benefit of using GTID tags is the ability to organize and identify transactions more efficiently. For instance, in a replication setup, an administrator can easily filter and manage transactions based on their tags, improving operations like auditing.</p><p>Users can even segregate apps, by setting each application to use it’s own tag. Making it easy to identify the source of a particular transaction.</p><p>GTID tags also facilitate troubleshooting and analysis by allowing administrators to quickly identify and focus on specific groups of transactions. For applications involving complex transaction flows or multiple databases, GTID tags can simplify monitoring and management tasks.</p><h3>Example</h3><pre>mysql&gt; SHOW MASTER STATUS;<br>+---------------+----------+--------------+------------------+------------------------------------------+<br>| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |<br>+---------------+----------+--------------+------------------+------------------------------------------+<br>| binlog.000001 |     1376 |              |                  | 2d85947c-b474-11ee-bee5-6fdc058076cc:1-7 |<br>+---------------+----------+--------------+------------------+------------------------------------------+<br>1 row in set, 1 warning (0.00 sec)<br><br>mysql&gt; SET gtid_next = &#39;AUTOMATIC:bulkInsert&#39;;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql&gt; INSERT INTO tb1 VALUES (1),(2),(3);<br>Query OK, 3 rows affected (0.01 sec)<br>Records: 3  Duplicates: 0  Warnings: 0<br><br>mysql&gt; INSERT INTO tb1 VALUES (4),(5),(6);<br>Query OK, 3 rows affected (0.00 sec)<br>Records: 3  Duplicates: 0  Warnings: 0<br><br>mysql&gt; SHOW MASTER STATUS;<br>+---------------+----------+--------------+------------------+---------------------------------------------------------+<br>| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                       |<br>+---------------+----------+--------------+------------------+---------------------------------------------------------+<br>| binlog.000001 |     1980 |              |                  | 2d85947c-b474-11ee-bee5-6fdc058076cc:1-7:bulkinsert:1-2 |<br>+---------------+----------+--------------+------------------+---------------------------------------------------------+<br>1 row in set, 1 warning (0.00 sec)</pre><p>After adding the bulkInsert tag and creating the new transaction, Executed_Gtid_Set now reports the set of transaction executed from the newly added tag.</p><pre>mysql&gt; SET gtid_next = &#39;AUTOMATIC:bulkDelete&#39;;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql&gt; DELETE FROM tb1 WHERE ID &lt;= 3;<br>Query OK, 3 rows affected (0.00 sec)<br><br>mysql&gt; SHOW MASTER STATUS;<br>+---------------+----------+--------------+------------------+----------------------------------------------------------------------+<br>| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                    |<br>+---------------+----------+--------------+------------------+----------------------------------------------------------------------+<br>| binlog.000001 |     2282 |              |                  | 2d85947c-b474-11ee-bee5-6fdc058076cc:1-7:bulkdelete:1:bulkinsert:1-2 |<br>+---------------+----------+--------------+------------------+----------------------------------------------------------------------+<br>1 row in set, 1 warning (0.00 sec)</pre><p>You can have multiple tags grouped in the same UUID. In the example above we have the sequence without tag, builkInsert and builDelete sequences.</p><h3>Conclusion</h3><p>GTID tags in MySQL enhance replication and transaction management by introducing a flexible, secure way to tag and identify transactions. This functionality streamlines database operations, particularly in complex environments, and offers administrators greater control and insight into their data. As MySQL continues to evolve, features like GTID tags underscore its commitment to providing powerful tools for database management and optimization.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=87aca6b2e40b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[GDB Advanced Techniques: Expanding GDB Functionality with Custom Function Execution]]></title>
            <link>https://altmannmarcelo.medium.com/gdb-advanced-techniques-expanding-gdb-functionality-with-custom-function-execution-ccf50894f61b?source=rss-a4227e11d0ab------2</link>
            <guid isPermaLink="false">https://medium.com/p/ccf50894f61b</guid>
            <category><![CDATA[software-development]]></category>
            <category><![CDATA[troubleshooting]]></category>
            <category><![CDATA[software-engineering]]></category>
            <category><![CDATA[gdb]]></category>
            <category><![CDATA[mysql]]></category>
            <dc:creator><![CDATA[Marcelo Altmann]]></dc:creator>
            <pubDate>Wed, 08 Nov 2023 13:57:45 GMT</pubDate>
            <atom:updated>2023-11-08T14:00:06.039Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/942/1*GOAQ1LCq_a7V4HZj5ykgLg.png" /></figure><p>GDB is the go-to tool for debugging and troubleshooting low-level applications such as C++.</p><p>Sometimes all you need simple break at some specific point and print a variable to inspect its value. Other times you need to go even further and loop through some memory structure such as a list.</p><p><strong>Defining simple functions in GDB</strong></p><p>GDB has support for user-defined functions via the <strong>define</strong> command. <br>Here is an example of a simple function:</p><pre>(gdb) define my_function<br>Type commands for definition of &quot;my_function&quot;.<br>End with a line saying just &quot;end&quot;.<br>&gt;print $arg0<br>&gt;end<br>(gdb) my_function &quot;marcelo&quot;<br>$1 = &quot;marcelo&quot;<br>(gdb)</pre><p><strong>Executing commands at breakpoints</strong></p><p>Another functionality of GDB is the ability to automatically execute commands when stopping at a specific breakpoint. This is useful when you are breaking in a point or function that is invoked many times and you have to repeatedly print or execute the same commands over and over again.</p><pre>(gdb) break function_name<br>(gdb) commands<br>Type commands for breakpoint(s) 1, one per line.<br>End with a line saying just &quot;end&quot;.<br>&gt; [your command 1]<br>&gt;[your command 2]<br>&gt;[. . .]<br>&gt;end</pre><p>A more detailed example using Percona XtraBackup to print file name and tablespace ID while the files are being copied:</p><pre>(gdb) b xtrabackup_copy_datafile<br>Breakpoint 1 at 0x55555af4258a: xtrabackup_copy_datafile. (2 locations)<br>(gdb) commands<br>Type commands for breakpoint(s) 1, one per line.<br>End with a line saying just &quot;end&quot;.<br>&gt;p node-&gt;name<br>&gt;p node-&gt;space-&gt;id<br>&gt;end<br>(gdb) r<br>(gdb) c<br>Continuing.<br>2023-11-08T10:01:05.602230-03:00 1 [Note] [MY-011825] [Xtrabackup] &gt;&gt; log scanned up to (364739678)<br>[Switching to Thread 0x7fffb7fff640 (LWP 79134)]<br><br>Thread 15 &quot;xtrabackup&quot; hit Breakpoint 1, xtrabackup_copy_datafile (node=0x55555e8ba790, thread_n=3, dest_name=0x0) at /work/pxb/src/8.0/storage/innobase/xtrabackup/src/xtrabackup.cc:3062<br>3062                               const char *dest_name) {<br>$8 = 0x55555e8ba6a0 &quot;./employees/employees.ibd&quot;<br>$9 = 2<br>(gdb) c<br>Continuing.<br>2023-11-08T10:07:06.993065-03:00 1 [Note] [MY-011825] [Xtrabackup] &gt;&gt; log scanned up to (364739678)<br>[Switching to Thread 0x7fffb67fc640 (LWP 79198)]<br><br>Thread 18 &quot;xtrabackup&quot; hit Breakpoint 1, xtrabackup_copy_datafile (node=0x55555e8bb440, thread_n=6) at /work/pxb/src/8.0/storage/innobase/xtrabackup/src/xtrabackup.cc:3058<br>3058   return xtrabackup_copy_datafile(node, thread_n, nullptr);<br>$10 = 0x55555e8bb350 &quot;./employees/dept_emp.ibd&quot;<br>$11 = 5</pre><p><strong>Exploring GDB Python API</strong></p><p>There are times when we need to perform more complex tasks under GDB, such as storing return values into variables, creating loops, and so on.</p><p>GDB has integration with <a href="https://sourceware.org/gdb/current/onlinedocs/gdb.html/Python-API.html">Python API</a> to allow users to extend GDB even further to their needs.</p><p>To demonstrate the API, we will walk through the analysis of <a href="https://jira.percona.com/browse/PS-8357">https://jira.percona.com/browse/PS-8357</a> .</p><p>In summary, this bug happens when MySQL / Percona XtraBackup is doing a shutdown and there are pages that just got read from the Change Buffer. Where is a snap in the resolution:</p><blockquote>Problem:<br>There is a possibility that at shutdown by the time we do the last<br>sweep on flushing the buffer pool there are still pages in the flush<br>list. Those pages are still marked as io_fix-&gt;BUF_IO_READ thus they are<br>not eligible for flushing from flush_list.</blockquote><blockquote>Where is the workflow:</blockquote><blockquote>ibuf_merge_in_background requested those pages to be read in order to<br>merge the ibuf changes. This will mark the page as BUF_IO_READ and<br>increment buf_pool-&gt;n_pend_reads by 1.</blockquote><blockquote>When IO threads pick them up, it will start to merge the insert<br>bugger changes.</blockquote><blockquote>On the first change, it will add the page to flush_list.</blockquote><blockquote>If there are more changes to apply, it will and continue on applying<br>the changes until it is done.</blockquote><blockquote>Once the io thread finishes applying ibuf records to this page, it<br>will mark the page as BUF_IO_NONE</blockquote><blockquote>the io thread decreases buf_pool-&gt;n_pend_reads by 1.</blockquote><blockquote>The last sweep on flushing buffer pool considers the round of flushes<br>completed when n_flushed == 0 which is not correct, if it runs when we<br>are at step 4.</blockquote><p>In order to reach this conclusion, I had to investigate every single page in each buffer pool instance. This means that I would have to investigate hundreds of thousands of pages. In order to accomplish this the Python API came handy.</p><p>The below function does all the heavy lifting work and investigates each page available in the LRU list of buffer pool. Save it on a .py file and source it into GDB to be able to use it as a function:</p><pre>import os<br><br>class printLRUList (gdb.Command):<br>  &quot;&quot;&quot;Collect required info for a bug report&quot;&quot;&quot;<br><br>  def __init__(self):<br>    super(printLRUList, self).__init__(&quot;printlrulist&quot;, gdb.COMMAND_USER)<br><br>  def invoke(self, arg, from_tty):<br>    raw_number_of_pages = gdb.execute(&quot;p buf_pool_ptr[1]-&gt;LRU-&gt;count&quot;, to_string=True)<br>    number_of_pages = raw_number_of_pages.split(&quot;= &quot;)[1]<br>    print(&quot;Total Pages in LRU: &quot;)<br>    print(number_of_pages)<br>    gdb.execute(&quot;set $lru_page=buf_pool_ptr[1]-&gt;LRU-&gt;start&quot;, to_string=True)<br>    io_fix_none = gdb.execute(&quot;p $lru_page.io_fix&quot;, to_string=True).endswith(&quot;BUF_IO_NONE\n&quot;)<br>    if not io_fix_none:<br>      data = gdb.execute(&quot;p $lru_page&quot;, to_string=True)<br>      print(&quot;Found page with IO fix issue:&quot;)<br>      print(data)<br><br>    last_page = gdb.execute(&quot;p $lru_page.LRU.next&quot;, to_string=True).endswith(&quot;0x0\n&quot;)<br>    while not last_page:<br>      gdb.execute(&quot;set $lru_page=$lru_page.LRU.next&quot;, to_string=True)<br>      io_fix_none = gdb.execute(&quot;p $lru_page.io_fix&quot;, to_string=True).endswith(&quot;BUF_IO_NONE\n&quot;)<br>      if not io_fix_none:<br>        data = gdb.execute(&quot;p $lru_page&quot;, to_string=True)<br>        print(&quot;Found page with IO fix issue:&quot;)<br>        print(data)<br><br><br>      last_page = gdb.execute(&quot;p $lru_page.LRU.next&quot;, to_string=True).endswith(&quot;0x0\n&quot;)</pre><pre>(gdb) source print_lru_list.py<br>(gdb) p srv_buf_pool_instances<br>+p srv_buf_pool_instances<br>$1294173 = 4<br><br>(gdb) printlrulist buf_pool_ptr[0]<br>+printlrulist buf_pool_ptr[0]<br>Buffer Pool Instance: (buf_pool_t *) 0x34300d8<br>Total Pages in LRU:<br>80882<br><br>(gdb) printlrulist buf_pool_ptr[1]<br>+printlrulist buf_pool_ptr[1]<br>Buffer Pool Instance: (buf_pool_t *) 0x3430980<br>Total Pages in LRU:<br>80883<br>Found page with IO fix issue:<br>++p $lru_page<br>$1941237 = (buf_page_t *) 0x7f26d091db00<br><br>(gdb) printlrulist buf_pool_ptr[2]<br>+printlrulist buf_pool_ptr[2]<br>Buffer Pool Instance: (buf_pool_t *) 0x3431228<br>Total Pages in LRU:<br>80882<br><br>(gdb) printlrulist buf_pool_ptr[3]<br>+printlrulist buf_pool_ptr[3]<br>Buffer Pool Instance: (buf_pool_t *) 0x3431ad0<br>Total Pages in LRU:<br>80883</pre><p>With this, I was able to identify the offending page address causing the issue.</p><p><strong>Conclusion</strong></p><p>GDB by itself is a powerful tool. Sometimes we need to give it further powers and extend it for our needs. In this article, we explore three different ways of extending and customizing GDB.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ccf50894f61b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[MySQL Query Cache with ReadySet]]></title>
            <link>https://altmannmarcelo.medium.com/mysql-query-cache-with-readyset-8d4f4aed24ff?source=rss-a4227e11d0ab------2</link>
            <guid isPermaLink="false">https://medium.com/p/8d4f4aed24ff</guid>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[cache]]></category>
            <category><![CDATA[performance]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Marcelo Altmann]]></dc:creator>
            <pubDate>Mon, 09 Oct 2023 01:22:40 GMT</pubDate>
            <atom:updated>2023-10-10T18:15:16.074Z</atom:updated>
            <content:encoded><![CDATA[<p>Cache is a known strategy for speeding up data access by saving result-sets in memory for later usage. When the same data is needed, the results are accessed directly from memory.</p><p>Caching comes with a maintenance cost. How do you keep caches up to date or invalidate cache entries when they become invalid?</p><p>MySQL had its built-in implementation named <a href="https://dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html">Query Cache</a>, however, it had some scalability problems related to high concurrency and cache invalidation, which were decided not worth fixing, and the feature was deprecated in MySQL 5.7.20 and completely removed on MySQL 8.0.</p><p>There are other options available, such as <a href="https://proxysql.com/documentation/query-cache/">ProxySQL Query Cache</a>. As with many other Caching strategies. It relies on TTL, which can be inefficient as your entry is only cached for a period of time and is guaranteed to be out-of-date if there are new writes affecting the cached entry.</p><p>Readyset is a SQL caching engine that sits in front of your existing MySQL (or PostgreSQL) with the purpose of caching common queries.</p><h3>How it works</h3><p>Different from other caching strategies, ReadySet utilizes the concept of dataflow graph and automatically keeps cache entries up to date as new data arrives on the source database.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*n0Uy37BDpvYJacwzhLfWRg.png" /><figcaption>Image by <a href="https://docs.readyset.io/_next/image?url=%2F_next%2Fstatic%2Fmedia%2Freadyset-architecture.433d5fc5.jpeg&amp;w=3840&amp;q=75">Readyset</a></figcaption></figure><p>Readyset takes an initial snapshot of the database and registers itself as a replica to keep receiving new data as it arrives on the source server. It implements the MySQL &amp; PostgreSQL protocol, so you can point your application directly to it. The queries that are not found on its list of cached queries are automatically proxied to the upstream database.</p><h3>How to configure</h3><p>For this example, we will be using <a href="https://github.com/datacharmer/test_db">MySQL Employees database</a>. The easiest way to get ReadySet up and running is by using a docker container:</p><ol><li>For this example, we will be creating a readyset user on MySQL:</li></ol><pre>mysql&gt; CREATE USER readysetUser@&#39;%&#39; IDENTIFIED BY &#39;readysetPWD&#39;;<br>mysql&gt; GRANT ALL ON *.* TO readysetUser@&#39;%&#39;;</pre><p>2. Download the docker composer and configure <strong>UPSTREAM_DB_URL :</strong></p><pre>curl -L -o compose.yml &quot;https://readyset.io/quickstart/compose.yml&quot;;<br># Replace your connection string mysql://user:pwd@IP:port<br>sed -i &#39;s/# UPSTREAM_DB_URL:/UPSTREAM_DB_URL: mysql:\/\/readysetUser:readysetPWD@172.17.0.1:3306/g&#39; compose.yml<br>docker compose up -d<br></pre><p><strong>Note: </strong><em>172.17.0.1</em> is the gateway IP from containers to access the host machine running docker.</p><p>3. Check logs and connection</p><pre>docker logs readyset-cache-1<br>. . .<br>2023-10-09T00:13:30.659923Z  INFO taking database snapshot: replicators::noria_adapter: Snapshot finished<br>2023-10-09T00:13:30.663942Z  INFO replicators::mysql_connector::connector: Starting binlog replication next_position=binlog.000002:66380408<br>2023-10-09T00:13:30.664152Z  INFO replicators::noria_adapter: MySQL connected<br>2023-10-09T00:13:30.664159Z  INFO replicators::noria_adapter: binlog_position=binlog.000002:66380408<br>2023-10-09T00:13:32.067140Z  INFO Compacting index{table=docker_compose_deployment-employees-salaries-0.db column_family=0}: dataflow_state::persistent_state: Compaction finished table=docker_compose_deployment-employees-salaries-0.db cf=0<br><br>mysql -u readysetUser -preadysetPWD -P 5433<br>mysql&gt; SHOW READYSET TABLES;<br>+----------------------------+-------------+-------------+<br>| table                      | status      | description |<br>+----------------------------+-------------+-------------+<br>| `employees`.`departments`  | Snapshotted |             |<br>| `employees`.`dept_emp`     | Snapshotted |             |<br>| `employees`.`dept_manager` | Snapshotted |             |<br>| `employees`.`employees`    | Snapshotted |             |<br>| `employees`.`salaries`     | Snapshotted |             |<br>| `employees`.`titles`       | Snapshotted |             |<br>+----------------------------+-------------+-------------+<br>6 rows in set (0,00 sec)</pre><p>We have all we need to start caching our queries.</p><h3>Create new cache</h3><p>To create a new cache, we can first run the desired query and check if the query is supported via <strong>SHOW PROXIED SUPPORTED QUERIE</strong>S and later create a cache for it via <strong>CREATE CACHE FROM</strong></p><pre>mysql -u readysetUser -preadysetPWD -P 5433<br>mysql&gt; SELECT COUNT(*) FROM employees.salaries WHERE salary = 100000;<br>+----------+<br>| COUNT(*) |<br>+----------+<br>|       13 |<br>+----------+<br>1 row in set (19,47 sec)<br><br>mysql&gt; SHOW PROXIED SUPPORTED QUERIES;<br>+--------------------+-------------------------------------------------------------------+--------------------+-------+----------+----------+----------+<br>| query id           | proxied query                                                     | readyset supported | count | p50 (ms) | p90 (ms) | p99 (ms) |<br>+--------------------+-------------------------------------------------------------------+--------------------+-------+----------+----------+----------+<br>| q_774bf1a388e74841 | SELECT count(*) FROM `employees`.`salaries` WHERE (`salary` = $1) | yes                | 0     | 0.000    | 0.000    | 0.000    |<br>+--------------------+-------------------------------------------------------------------+--------------------+-------+----------+----------+----------+<br>1 row in set (0,00 sec)<br><br>mysql&gt; CREATE CACHE FROM SELECT COUNT(*) FROM employees.salaries WHERE salary = 100000;<br>Query OK, 0 rows affected (0,00 sec)<br><br>mysql&gt; SELECT COUNT(*) FROM employees.salaries WHERE salary = 100000;<br>+----------+<br>| count(*) |<br>+----------+<br>|       13 |<br>+----------+<br>1 row in set (0,00 sec)</pre><p>As we can see above, our query dropped from 19 seconds to 0 seconds. Nice. So what about if we add a new salary entry with salary = 100k?</p><pre>mysql&gt; INSERT INTO employees.salaries values (10001, 100000, &#39;2023-10-08&#39;, &#39;9999-01-01&#39;);<br>Query OK, 1 row affected (0,02 sec)<br><br>mysql&gt; SELECT COUNT(*) FROM employees.salaries WHERE salary = 100000;<br>+----------+<br>| count(*) |<br>+----------+<br>|       14 |<br>+----------+<br>1 row in set (0,00 sec)</pre><p>Readyset received the new entry via replication workflow and automatically updated the required dataflow node for serving this cache entry. No more TTL invalidations!</p><h3>Conclusion</h3><p>Readyset is still in its early stages. The product is really promising.</p><p>Many MySQL and PostgreSQL query language support are being implemented by their team.</p><p>Check out their <a href="https://docs.readyset.io/">documentation</a> for more details and join their <a href="https://join.slack.com/t/readysetcommunity/shared_invite/zt-2272gtiz4-0024xeRJUPGWlRETQrGkFw">slack</a> community if you are interested.</p><p>More important, give it a try!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8d4f4aed24ff" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>