<?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 Andrew Deally on Medium]]></title>
        <description><![CDATA[Stories by Andrew Deally on Medium]]></description>
        <link>https://medium.com/@andrewdeally?source=rss-5212e7f73704------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*dmbNkD5D-u45r44go_cf0g.png</url>
            <title>Stories by Andrew Deally on Medium</title>
            <link>https://medium.com/@andrewdeally?source=rss-5212e7f73704------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 10:00:53 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@andrewdeally/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[THE DYNASTY THAT NEVER WAS?]]></title>
            <link>https://andrewdeally.medium.com/the-dynasty-that-never-was-8c02801afad3?source=rss-5212e7f73704------2</link>
            <guid isPermaLink="false">https://medium.com/p/8c02801afad3</guid>
            <dc:creator><![CDATA[Andrew Deally]]></dc:creator>
            <pubDate>Tue, 23 Jun 2026 03:22:19 GMT</pubDate>
            <atom:updated>2026-06-23T03:22:19.364Z</atom:updated>
            <content:encoded><![CDATA[<h3><strong>My View on How the Mustache‑Wearing Oakland A’s Lost a 1970s decade long Empire — And the Dynasty That Should Have Followed</strong></h3><p><strong>Between 1972 and 1974, the Oakland A’s were the most swaggering, unruly, and electrifying force in baseball. They were a team defined as much by their talent as by their personality — a clubhouse full of mustache‑wearing rebels who fought each other in the locker room, fought opponents on the field, and fought their owner in the press, yet somehow came together to win three straight World Series titles. They were young, they were in their prime, and they were built to last. In any rational universe, the A’s should have been the defining franchise of throughout the 1970s.</strong></p><p><strong>But the dynasty collapsed almost overnight, not because the players aged or the league caught up, but because owner Charlie Finley refused to pay the very stars who made him a champion. His decisions didn’t just dismantle a dynasty — they reshaped the entire American League, including the rise of the New York Yankees, who built their late‑70s identity on the players Oakland let walk away.</strong></p><p><strong>The first crack in the empire came in December 1974, when an arbitrator ruled that Finley had violated Catfish Hunter’s contract. The ruling made Hunter baseball’s first modern free agent, and the Yankees pounced immediately. Oakland didn’t just lose its ace; it lost the quiet, steady heartbeat of its rotation — the man who threw 20‑plus wins three straight seasons, who anchored every postseason run, and who wore his mustache like a badge of honor.</strong></p><p><strong>But the deeper tragedy came after he left. The Yankees didn’t just sign Catfish. They burned him out. In 1975, his first year in New York, he threw 328 innings — the heaviest workload of his career — along with 30 complete games. No modern pitcher will ever come close to that. Over the next three seasons, he threw nearly 800 more innings. By 1979, at just 33 years old, he was finished. His arm was gone. Had he stayed in Oakland, he would have shared innings with Vida Blue and Ken Holtzman, supported by Paul Lindblad and Rollie Fingers in a deep bullpen. Instead of being ridden into the ground, he would have been protected. Instead of retiring at 33, he might have pitched into his late 30s. And instead of the Yankees building a rotation around him, the A’s would have kept the most balanced staff in baseball.</strong></p><p><strong>Finley didn’t learn from the Hunter fiasco. In April 1976, he traded Reggie Jackson, Holtzman, and Lindblad to Baltimore in a pre‑emptive salary dump. Reggie — the mustache‑wearing, quote‑dropping, swagger‑swinging superstar — was the soul of the A’s offense. Holtzman was the rotation’s stabilizer. Lindblad was the bullpen’s glue. The clubhouse was stunned. The fans were furious. And the dynasty started the downturn.</strong></p><p><strong>Two months later, Finley tried to sell Joe Rudi, Fingers, and Blue for cash — a move so brazen that Commissioner Bowie Kuhn stepped in and blocked it “for the good of baseball.” The players stayed only because they were forced to. When the 1976 season ended, the dam burst. On the same day, six everyday players — Fingers, Rudi, Bando, Tenace, Campaneris, and Lindblad — walked away in free agency. No team in baseball history has ever lost that much talent at once. And no team has ever lost so many mustaches in a single afternoon.</strong></p><p><strong>The cruel irony is that every one of those players continued to thrive elsewhere. Reggie became Mr. October. Fingers won awards. Tenace walked 493 times. Bando anchored Milwaukee. Rudi hit 64 homers. Holtzman won 23 games. Lindblad posted a 2.76 ERA in 1978. These weren’t aging veterans. They were stars in their prime. And the Yankees — who had been stuck in mediocrity for a decade — suddenly had some of the pieces they needed to become the Yankees again with Hunter, Reggie, Holtzman and Lindblad.</strong></p><p><strong>Remove Reggie, Catfish, Holtzman, and Lindblad from New York, and the Yankees’ entire late‑70s run would have been more challenging and even a collapses. In 1976, Boston or Baltimore wins the East. In 1977, the Yankees drop from 100 wins to the low 90s, and Boston takes the division. In 1978, there is no Bucky Dent game — Boston wins outright. The Yankees’ two‑title run evaporates. The AL East becomes a Boston/Baltimore battleground, not a New York coronation. The Yankees built a dynasty with these additional Atheletics.</strong></p><p><strong>Left behind in Oakland was Vida Blue, who pitched brilliantly but alone. His 3.01 ERA in 1975 should have produced a 20‑win season. Instead, he finished 14–12. Over the next four years, he posted a 59–60 record despite pitching at an All‑Star level. With Catfish, Holtzman, Lindblad, and Fingers still in Oakland, Blue would have thrown fewer innings, avoided burnout, and likely won multiple Cy Young Awards. He might have become a Hall of Fame lock. Instead, he became the symbol of a dynasty that vanished around him.</strong></p><p><strong>But the collapse didn’t just destroy the A’s. It prevented the birth of a <em>second</em> dynasty — one built on the rookies who arrived just as the veterans were leaving. Mitchell Page hit .307 with 21 homers and 42 steals as a rookie. Tony Armas would later lead the league in home runs. Mike Norris became a Cy Young runner‑up. Rick Langford became a workhorse. Dwayne Murphy became one of the best defensive center fielders of the decade. Jeff Newman became an All‑Star catcher. These players weren’t meant to replace legends. They were meant to learn from them. They were the next wave — the second generation of a dynasty that never got the chance to exist.</strong></p><p><strong>Instead of joining a championship culture, they joined a declining team. Instead of being eased into greatness, they were thrown into a 98‑loss disaster. In the alternate timeline, Page becomes a star hitting between Campaneris and Reggie. Armas becomes the next great power hitter behind Rudi. Norris becomes the #3 starter behind Blue and Catfish. Langford becomes a protected #4. Murphy becomes the defensive anchor behind a Hall‑of‑Fame rotation. Newman becomes a premium defensive catcher mentored by Tenace. The A’s don’t just stay great in the late ’70s. They become a two‑era dynasty, dominating into the early 1980s.</strong></p><p><strong>In that alternate timeline, the A’s win the AL West in 1975, reach the World Series in 1976, win the World Series in 1977, repeat in 1978, and lose the ALCS to Baltimore in 1979. They at least would win two titles, four pennants, and remain the defining franchise of the decade. The Yankees never become the Yankees. Vida Blue becomes a legendand perhaps a Hall of Famer. Catfish Hunter’s career extends by years. The rookie wave becomes the next great Oakland core.</strong></p><p><strong>The A’s of the 1970s weren’t just good. They were a dynasty that never got the chance to finish what it started — and a dynasty that should have continued into the next decade.</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8c02801afad3" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Recovering from a Bad update in CockroachDB with AS OF SYSTEM TIME]]></title>
            <link>https://andrewdeally.medium.com/recovering-from-a-bad-update-in-cockroachdb-with-as-of-system-time-39afd5bf72a2?source=rss-5212e7f73704------2</link>
            <guid isPermaLink="false">https://medium.com/p/39afd5bf72a2</guid>
            <dc:creator><![CDATA[Andrew Deally]]></dc:creator>
            <pubDate>Fri, 17 Apr 2026 03:36:02 GMT</pubDate>
            <atom:updated>2026-04-17T03:36:02.002Z</atom:updated>
            <content:encoded><![CDATA[<p>One of the most useful features in CockroachDB for operational recovery is AS OF SYSTEM TIME. If a bad write just happened and the old MVCC versions are still within the retention window, you can read the table as it existed before the mistake and use that snapshot to recover the data.</p><p>In this post, I’ll walk through a simple example using the movr.promo_codes table. The failure is straightforward: all values in the rules column are accidentally overwritten with NULL. The recovery pattern is also straightforward once you know one important limitation: AS OF SYSTEM TIME must be used on a <strong>top-level statement</strong>, and it cannot be embedded directly inside a mutation like UPDATE ... FROM (...).</p><h3>The Setup</h3><p>In this example, promo_codes.code is the primary key, and rules is a JSONB column. That makes code the natural join key for recovery.</p><p>First, capture a timestamp before the bad update or know the time before the mistake:</p><pre>demo@127.0.0.1:26257/movr&gt; SELECT now();<br>               now<br>---------------------------------<br>  2026-04-17 03:12:14.228184+00<br>(1 row)</pre><pre>Time: 1ms total (execution 1ms / network 0ms)</pre><p>Then simulate the mistake, by the way notice 1=1 do you know why I did that?:</p><pre>demo@127.0.0.1:26257/movr&gt; UPDATE promo_codes SET rules = NULL WHERE 1=1;<br>UPDATE 1000</pre><pre>Time: 13ms total (execution 13ms / network 0ms)</pre><p>And verify the damage:</p><pre>demo@127.0.0.1:26257/movr&gt; SELECT code, rules FROM promo_codes LIMIT 10;<br>             code            | rules<br>-----------------------------+--------<br>  0_congress_up_least        | NULL<br>  100_eat_between_perform    | NULL<br>  101_itself_central_improve | NULL<br>  102_one_receive_within     | NULL<br>  103_at_reach_individual    | NULL<br>  104_guy_policy_current     | NULL<br>  105_term_front_exist       | NULL<br>  106_purpose_city_tend      | NULL<br>  107_power_mention_southern | NULL<br>  108_side_score_scene       | NULL<br>(10 rows)</pre><pre>Time: 1ms total (execution 1ms / network 0ms)</pre><h3>Step 1: Read the Historical Data</h3><p>This is the part AS OF SYSTEM TIME is designed for. You can query the table as it existed just before the bad update:</p><pre>demo@127.0.0.1:26257/movr&gt; SELECT code, rules<br>                         -&gt; FROM promo_codes AS OF SYSTEM TIME &#39;2026-04-17 03:12:14&#39;<br>                         -&gt; LIMIT 10;<br>             code            |                    rules<br>-----------------------------+-----------------------------------------------<br>  0_congress_up_least        | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  100_eat_between_perform    | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  101_itself_central_improve | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  102_one_receive_within     | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  103_at_reach_individual    | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  104_guy_policy_current     | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  105_term_front_exist       | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  106_purpose_city_tend      | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  107_power_mention_southern | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  108_side_score_scene       | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>(10 rows)</pre><pre>Time: 1ms total (execution 1ms / network 0ms)</pre><p>That historical read works because AS OF SYSTEM TIME is being used on a top-level SELECT.</p><h3>The Common Mistake</h3><p>The first instinct is often to join the historical snapshot directly into an UPDATE. In CockroachDB, that does <strong>not</strong> work:</p><pre>demo@127.0.0.1:26257/movr&gt; UPDATE promo_codes p<br>                         -&gt; SET rules = h.rules<br>                         -&gt; FROM (<br>                         -&gt;   SELECT code, rules<br>                         -&gt;   FROM promo_codes AS OF SYSTEM TIME &#39;2026-04-17 03:12:14&#39;<br>                         -&gt; ) h<br>                         -&gt; WHERE p.code = h.code;<br>ERROR: AS OF SYSTEM TIME must be provided on a top-level statement<br>SQLSTATE: 42601</pre><p>This fails because AS OF SYSTEM TIME must be provided on the top-level statement, and CockroachDB does not support using it only inside a subquery under a mutation like UPDATE and maybe a feature is forthcoming but this should make sense why mixed Timestamps are not allowed.</p><h3>The Working Pattern: Materialize, Then Update</h3><p>The workaround is simple and operationally clean:</p><ol><li>Run a top-level historical SELECT</li><li>Materialize the result into a staging table</li><li>Run a normal current-time UPDATE from that staging table</li></ol><p>Create the recovery table from the historical snapshot:</p><pre>demo@127.0.0.1:26257/movr&gt; CREATE TABLE reco AS<br>                         -&gt; SELECT code, rules<br>                         -&gt; FROM promo_codes AS OF SYSTEM TIME &#39;2026-04-17 03:12:14&#39;;<br>NOTICE: CREATE TABLE ... AS does not copy over indexes, default expressions, or constraints; the new table has a hidden rowid primary key column<br>CREATE TABLE AS</pre><pre>Time: 75ms total (execution 73ms / network 3ms)</pre><p>Now inspect the recovered rows:</p><pre>demo@127.0.0.1:26257/movr&gt; SELECT * FROM reco LIMIT 10;<br>             code            |                    rules<br>-----------------------------+-----------------------------------------------<br>  0_congress_up_least        | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  100_eat_between_perform    | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  101_itself_central_improve | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  102_one_receive_within     | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  103_at_reach_individual    | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  104_guy_policy_current     | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  105_term_front_exist       | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  106_purpose_city_tend      | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  107_power_mention_southern | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  108_side_score_scene       | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>(10 rows)</pre><pre>Time: 1ms total (execution 1ms / network 0ms)</pre><p>Let’s inspect a more of the data from the reco table:</p><pre>demo@127.0.0.1:26257/movr&gt; SELECT * FROM reco ORDER BY code DESC LIMIT 10;<br>               code               |                    rules<br>----------------------------------+-----------------------------------------------<br>  99_artist_sit_cultural          | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  9_size_claim_however            | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  999_product_prevent_financial   | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  998_far_central_improve         | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  997_kitchen_own_send            | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  996_health_method_wife          | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  995_of_successful_we            | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  994_get_sense_simple            | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  993_lay_sea_spend               | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  992_station_provide_teach       | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>(10 rows)</pre><pre>Time: 1ms total (execution 1ms / network 0ms)</pre><p>At this point, the historical values are materialized in a normal table, so a standard join-based UPDATE works — Warning this should be handled in small batches with commits:</p><pre>demo@127.0.0.1:26257/movr&gt; UPDATE promo_codes p<br>                         -&gt; SET rules = h.rules<br>                         -&gt; FROM (<br>                         -&gt;   SELECT code, rules<br>                         -&gt;   FROM reco<br>                         -&gt; ) h<br>                         -&gt; WHERE p.code = h.code;<br>UPDATE 1000</pre><pre>Time: 22ms total (execution 22ms / network 0ms)</pre><p>Finally, verify the recovery:</p><pre>demo@127.0.0.1:26257/movr&gt; SELECT code, rules FROM promo_codes LIMIT 10;<br>             code            |                    rules<br>-----------------------------+-----------------------------------------------<br>  0_congress_up_least        | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  100_eat_between_perform    | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  101_itself_central_improve | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  102_one_receive_within     | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  103_at_reach_individual    | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  104_guy_policy_current     | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  105_term_front_exist       | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  106_purpose_city_tend      | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  107_power_mention_southern | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>  108_side_score_scene       | {&quot;type&quot;: &quot;percent_discount&quot;, &quot;value&quot;: &quot;10%&quot;}<br>(10 rows)</pre><pre>Time: 1ms total (execution 1ms / network 0ms)</pre><h3>Why This Pattern Matters</h3><p>The key operational takeaway is that CockroachDB supports historical reads very well, but those reads and your corrective writes need to be separated into distinct steps.</p><p>That gives you a recovery pattern that is:</p><ul><li>easy to explain</li><li>easy to validate</li><li>safe to test before writing back to the live table</li></ul><p>It also scales beyond this toy example. The same pattern works when you need to recover a subset of rows, repair a single column, or restore data into a side table for validation before touching production.</p><h3>Final Takeaway</h3><p>If you accidentally run a destructive UPDATE in CockroachDB, AS OF SYSTEM TIME can absolutely help you recover — but not by embedding a historical read directly inside an UPDATE. The right pattern is to read the old data at a top-level historical timestamp, materialize it, and then apply a normal current-time update from that staged snapshot.</p><p>That is the difference between a recovery approach that looks intuitive and one that is actually supported.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=39afd5bf72a2" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Apache Iceberg — FAQ]]></title>
            <link>https://andrewdeally.medium.com/apache-iceberg-faq-8e0eab2c7b20?source=rss-5212e7f73704------2</link>
            <guid isPermaLink="false">https://medium.com/p/8e0eab2c7b20</guid>
            <dc:creator><![CDATA[Andrew Deally]]></dc:creator>
            <pubDate>Wed, 11 Mar 2026 01:20:15 GMT</pubDate>
            <atom:updated>2026-03-11T01:20:15.851Z</atom:updated>
            <content:encoded><![CDATA[<h3>Apache Iceberg — FAQ</h3><h3>What is Apache Iceberg?</h3><p>Apache Iceberg is an open-source, high-performance table format built for managing very large analytic datasets. It helps make data in cloud object stores (like Amazon S3, HDFS, or Azure) behave like fully-featured database tables, with support for:</p><ul><li>ACID transactions</li><li>Schema evolution</li><li>Time travel (versioning)</li><li>Partition evolution</li><li>Concurrent writes</li></ul><h3>Is Apache Iceberg a database or a database engine?</h3><p>No. Iceberg is <strong>not</strong> a database or engine. It is:</p><ul><li>A table format specification</li><li>A Java library for working with metadata, schema changes, and layout</li><li>A layer that sits on top of cloud storage systems</li></ul><p>You still need a query engine (like Spark, Flink, or Trino) to run queries.</p><h3>Who does the actual work of querying and managing data?</h3><p>Apache Iceberg provides logic for metadata handling, partitioning, schema changes, and versioning. However, the actual data processing is done by <strong>query engines</strong>, including:</p><ul><li>Apache Spark</li><li>Apache Flink</li><li>Trino (formerly Presto)</li><li>Apache Hive</li><li>Snowflake</li><li>Dremio</li></ul><p>These engines integrate with Iceberg’s APIs to read/write data and optimize performance (e.g., metadata pruning, filter pushdown).</p><h3>Who created and maintains Iceberg?</h3><ul><li>Originally developed by <strong>Netflix</strong></li><li>Donated to the <strong>Apache Software Foundation</strong> as a top-level project</li><li>Maintained by a broad open-source community including contributors from:</li><li>Netflix</li><li>Apple</li><li>AWS</li><li>Snowflake</li><li>Dremio</li><li>LinkedIn</li><li>Adobe</li></ul><p>Project GitHub: <a href="https://github.com/apache/iceberg">https://github.com/apache/iceberg</a></p><h3>Is Apache Iceberg proprietary?</h3><p>No. Apache Iceberg is:</p><ul><li>100% open source</li><li>Licensed under the Apache 2.0 License</li><li>Free to use, modify, and distribute</li></ul><h3>Does Apache Iceberg store data?</h3><p>No. Iceberg <strong>does not</strong> store data. It manages metadata and tracks where your data files are stored in systems such as:</p><ul><li>Amazon S3</li><li>HDFS</li><li>Azure Data Lake Storage (ADLS)</li><li>Google Cloud Storage</li></ul><p>It manages table versions, schema definitions, partitioning strategies, and file locations, but actual data is stored as Parquet, ORC, or Avro files in object storage.</p><h3>How do you install or use Apache Iceberg?</h3><p>Apache Iceberg is used as a <strong>library or connector</strong> inside query engines. Here’s how it’s used in popular engines:</p><h3>Apache Spark</h3><pre>spark-shell \<br>  --packages org.apache.iceberg:iceberg-spark-runtime-3.3_2.12:1.5.0 \<br>  --conf spark.sql.catalog.my_catalog=org.apache.iceberg.spark.SparkCatalog \<br>  --conf spark.sql.catalog.my_catalog.type=hadoop \<br>  --conf spark.sql.catalog.my_catalog.warehouse=/path/to/warehouse</pre><h3>Trino</h3><p>Create a file named iceberg.properties:</p><pre>connector.name=iceberg<br>catalog.type=hadoop<br>warehouse=/mnt/warehouse</pre><h3>Apache Flink</h3><pre>CREATE CATALOG my_catalog WITH (<br>  &#39;type&#39;=&#39;iceberg&#39;,<br>  &#39;catalog-type&#39;=&#39;hadoop&#39;,<br>  &#39;warehouse&#39;=&#39;s3://my-bucket/warehouse&#39;<br>);</pre><h3>What features does Iceberg provide?</h3><ul><li><strong>ACID Transactions</strong> — Reliable support for concurrent reads and writes</li><li><strong>Schema Evolution</strong> — Add, remove, or rename columns without rewriting data</li><li><strong>Partition Evolution</strong> — Change the partitioning strategy without table rewrites</li><li><strong>Time Travel</strong> — Query data from a previous snapshot or version</li><li><strong>Metadata Pruning</strong> — Faster queries by scanning only relevant files</li><li><strong>Engine Compatibility</strong> — Works across Spark, Flink, Trino, Hive, Snowflake, and more</li></ul><h3>How does Iceberg compare to Delta Lake or Apache Hudi?</h3><pre>## How does Iceberg compare to Delta Lake or Apache Hudi?<br><br>| Feature              | Iceberg                        | Delta Lake         | Apache Hudi          |<br>|----------------------|--------------------------------|---------------------|------------------------|<br>| Maintained by        | Apache (Netflix, Apple, AWS)  | Databricks         | Apache (Uber)          |<br>| ACID Transactions    | Yes                            | Yes                | Yes                    |<br>| Schema Evolution     | Yes                            | Yes                | Yes                    |<br>| Partition Evolution  | Yes                            | No                 | Limited                |<br>| Time Travel          | Yes                            | Yes                | Yes                    |<br>| Best For             | Batch + Streaming              | Databricks stack   | Streaming + Upserts    |<br><br></pre><p>Iceberg is the most engine-agnostic option and is widely supported in both open-source and commercial environments. Delta Lake is tightly coupled with Databricks.</p><h3>Do Snowflake or Databricks support Iceberg?</h3><ul><li><strong>Snowflake</strong>: Yes. Snowflake supports <strong>external Apache Iceberg tables</strong>, allowing you to query data in Iceberg format stored in S3.</li><li><strong>Databricks</strong>: No. Databricks promotes <strong>Delta Lake</strong>, which it created and maintains. Iceberg is not natively supported.</li></ul><h3>What tools and catalogs work with Iceberg?</h3><p>Apache Iceberg supports multiple catalog backends to manage metadata and table versions. These include:</p><ul><li>Hive Metastore</li><li>AWS Glue Catalog</li><li>Project Nessie (Git-like version control)</li><li>JDBC-based catalogs</li><li>REST-based catalogs</li></ul><h3>What file formats does Apache Iceberg support?</h3><p>Iceberg supports multiple storage formats for table data:</p><ul><li>Apache Parquet (most common)</li><li>Apache ORC</li><li>Apache Avro</li></ul><h3>Summary</h3><p>Apache Iceberg is a powerful open-source table format designed to bring reliable, large-scale table management to modern data lakes. It offers database-like capabilities — such as versioning, schema evolution, ACID transactions, and time travel — without being a database engine itself. Instead, Iceberg works with compute engines like Spark, Flink, Trino, and Snowflake to unlock fast, reliable querying over massive datasets stored in the cloud.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8e0eab2c7b20" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Comparing Multi-Statement vs. Single-Statement Transactions for Account Transfers in SQL]]></title>
            <link>https://andrewdeally.medium.com/comparing-multi-statement-vs-single-statement-transactions-for-account-transfers-in-sql-09190b116e64?source=rss-5212e7f73704------2</link>
            <guid isPermaLink="false">https://medium.com/p/09190b116e64</guid>
            <dc:creator><![CDATA[Andrew Deally]]></dc:creator>
            <pubDate>Sat, 12 Jul 2025 14:59:36 GMT</pubDate>
            <atom:updated>2025-07-12T23:34:41.097Z</atom:updated>
            <content:encoded><![CDATA[<h4>Single-statement SQL consistently outperforms traditional step-by-step transactions…</h4><p>Over the past few weeks, I’ve had a familiar conversation with several different customers — all facing similar challenges: transactional workloads under pressure, too much contention, and retry storms that hurt throughput.</p><p>In each case, the core issue boiled down to how they were structuring their transactions — often using procedural, row-by-row logic that made perfect sense at small scale, but couldn’t keep up under concurrency.</p><p>I realized that whenever I try to explain the solution — often a shift in thinking from imperative, multi-step operations to declarative, set-based SQL — I end up stitching together pieces from old blog posts, docs, or informal messaging explanations. So I decided to write this post as a clearer, more complete version of that advice.</p><p>We’ll explore a classic example: <strong>transferring funds between accounts</strong>. It’s a deceptively simple operation, but how you implement it has a huge impact on performance, consistency, and scalability — especially in distributed databases like <strong>CockroachDB</strong>, or in distributed variations of <strong>PostgreSQL</strong>. (And honestly, even back in my Oracle days, the same design lessons applied — thank you Real World Performance team :)</p><p>And here’s a key mindset shift: <strong>when using CockroachDB</strong>, lean toward <strong>set-based processing</strong> wherever your workload allows it. Because CockroachDB is a <strong>massively scale-out system</strong>, it shines when you give it the opportunity to leverage its <strong>parallelism</strong> — not by micromanaging work row-by-row, but by expressing the work in larger, more declarative chunks. Throw more work at it <em>wisely</em>, and it will reward you with predictable, concurrent performance.</p><p>This post compares two patterns:</p><ul><li><strong>Explicit multi-statement transactions</strong>, where logic is split across SELECT, INSERT, and UPDATE statements in client-managed transaction blocks.</li><li><strong>Single-statement CTE-based transactions</strong>, where the entire workflow is composed as one atomic SQL statement using WITH clauses and set logic.</li></ul><p>Both techniques get the job done — balances are adjusted, history is recorded — but under the hood, they behave <strong><em>very</em></strong><em> </em>differently. Understanding those differences can mean the difference between a system that merely survives and one that scales confidently under load.</p><p><strong>Approach 1: Explicit Multi-Statement Transaction (Step-by-Step)</strong></p><p>This is the classic approach many developers (especially from Oracle or traditional PostgreSQL backgrounds) start with: manually issuing a sequence of SQL commands within an explicit transaction block.</p><p>Here’s what that might look like in raw SQL:</p><pre>BEGIN;<br>- Create a new transaction record<br>INSERT INTO transaction (id) VALUES (&#39;tx123&#39;);<br>- Read balances for the involved accounts<br>SELECT id, balance FROM account<br>WHERE id IN (&#39;acc1&#39;, &#39;acc2&#39;)<br>FOR UPDATE;<br>- Insert transaction items<br>INSERT INTO transaction_item (transaction_id, account_id, amount, running_balance)<br>VALUES <br> (&#39;tx123&#39;, &#39;acc1&#39;, -100, 900),<br> (&#39;tx123&#39;, &#39;acc2&#39;, 100, 1100);<br>- Update account balances<br>UPDATE account SET balance = balance - 100 WHERE id = &#39;acc1&#39;;<br>UPDATE account SET balance = balance + 100 WHERE id = &#39;acc2&#39;;<br>COMMIT;</pre><ul><li><strong>Straightforward to understand</strong> and debug.</li><li>Easy to step through logic and log at each point.</li><li>Can use procedural control flow from the application layer.</li></ul><h3>Downsides</h3><ul><li><strong>Long-lived locks</strong>: Each step holds locks longer than necessary.</li><li><strong>Higher contention risk</strong>: More chances for conflicts between concurrent transactions.</li><li><strong>Harder to retry</strong>: If a retryable error occurs mid-way, the whole transaction must be retried manually.</li><li><strong>Round-trips</strong>: Multiple network hops between app and database slow things down.</li></ul><h4><strong>Approach 2:</strong> Single-Statement Transaction via CTEs</h4><p>Contrast that with a <strong>declarative, single-statement SQL</strong> using WITH clauses to encapsulate the entire transaction as one atomic unit. This approach lets the <strong>query planner optimize everything</strong>, and CockroachDB (or PostgreSQL) can execute it more efficiently.</p><p>Here’s the same transfer using CTEs:</p><pre>WITH input_data (account_id, amount) AS (<br> VALUES <br> (&#39;acc1&#39;, -100),<br> (&#39;acc2&#39;, 100)<br>),<br>new_tx AS (<br> INSERT INTO transaction (id) VALUES (&#39;tx456&#39;) RETURNING id<br>),<br>locked_accounts AS (<br> SELECT a.id, a.balance<br> FROM account a<br> JOIN input_data i ON a.id = i.account_id<br> FOR UPDATE<br>),<br>transaction_items AS (<br> INSERT INTO transaction_item (<br> transaction_id, account_id, amount, running_balance<br> )<br> SELECT <br> (SELECT id FROM new_tx),<br> i.account_id,<br> i.amount,<br> a.balance + i.amount<br> FROM input_data i<br> JOIN locked_accounts a ON i.account_id = a.id<br> RETURNING *<br>)<br>UPDATE account<br>SET balance = balance + i.amount<br>FROM input_data i<br>WHERE account.id = i.account_id;</pre><h3>Benefits</h3><ul><li><strong>Truly atomic</strong>: It’s either all done, or nothing is — no partial updates.</li><li><strong>Easier retries</strong>: Since it’s one statement, CockroachDB can auto-retry it transparently.</li><li><strong>Less contention</strong>: Locks are acquired and released in a tight window.</li><li><strong>Parallelism-friendly</strong>: Set-based logic enables distributed execution, especially in CockroachDB.</li><li><strong>Fewer round-trips</strong>: Everything happens in one statement.</li></ul><h3>Tradeoffs</h3><ul><li>Initially harder to debug or break into smaller steps.</li><li>CTE readability may decrease as complexity grows.</li><li>Does require a more declarative mindset.</li></ul><p>While both approaches successfully achieve the same logical outcome — transferring funds between accounts with updated balances and a complete transaction record — the way they behave under concurrency and system load is where the real differences surface.</p><ul><li><strong>Transaction retries</strong>:<br>In a multi-statement transaction, retries must be handled manually by the application. If a conflict occurs halfway through a series of statements, the entire transaction must be rolled back and re-executed from the beginning. In contrast, a single CTE-based transaction can often be retried automatically by the database engine (especially in systems like CockroachDB) because it is a single, declarative statement. This makes retry behavior more robust and less error-prone.</li><li><strong>Lock duration</strong>:<br>Multi-statement transactions tend to hold locks longer. Since each step — SELECT, INSERT, UPDATE — is executed separately, locks on rows may be held for the full duration of the transaction, increasing the risk of contention. With a CTE-based single-statement transaction, locks are typically acquired and released within a narrower window, reducing contention under high concurrency.</li><li><strong>Application round-trips</strong>:<br>A step-by-step transaction often involves multiple round-trips between the client and the database, which adds network latency and complexity. A single-statement transaction, on the other hand, sends one atomic unit of work to the database, allowing it to be optimized and executed more efficiently.</li><li><strong>Retry logic complexity</strong>:<br>The more steps you include in a transaction, the more complex it becomes to write safe and correct retry logic. In single-statement transactions, retry complexity is offloaded to the database, which can internally retry without exposing the process to your application logic.</li><li><strong>Parallelism and optimization</strong>:<br>Set-based operations — like those used in the CTE strategy — are much more amenable to parallel execution. In massively distributed systems like CockroachDB, this means the database can fan out and execute parts of the statement in parallel across nodes. Multi-statement, row-by-row logic typically executes serially and can’t take full advantage of distributed parallelism.</li></ul><h4>Let’s test the theory out with my laptop and some code.</h4><p>I ran three sets of benchmarks on my laptop using CockroachDB, simulating 10,000 accounts and varying the concurrency and locking behavior. While this wasn’t a production-grade environment (no distributed SQL or cross-node latency), it was more than enough to surface meaningful trends in performance, contention, and retry behavior.</p><p><strong>Setup</strong></p><ol><li><strong>High concurrency (32 threads)</strong> without explicit row locking</li><li><strong>High concurrency (32 threads)</strong> with FOR UPDATE locking</li><li><strong>Lower concurrency (8 threads)</strong> without FOR UPDATE</li></ol><p>Each test ran 100 transactions per thread, totaling:</p><ul><li><strong>3,200 transactions</strong> for high-concurrency tests</li><li><strong>800 transactions</strong> for the lower-concurrency test</li></ul><p><strong>Results Summary</strong></p><p><strong>1.</strong> <strong>High Concurrency, No Lock (</strong><strong>--threads 32)</strong></p><p><strong>Explicit Multi-Statement:</strong></p><ul><li>p50 latency: 64.9 ms</li><li>p99 latency: 269.1 ms</li><li>max latency: 533.3 ms</li><li>avg retries: <strong>0.04</strong></li></ul><p><strong>Single-Statement CTE:</strong></p><ul><li>p50 latency: 44.7 ms</li><li>p99 latency: 78.5 ms</li><li>max latency: 100.4 ms</li><li>avg retries: <strong>0.00</strong></li></ul><p>CTE approach shows better latency and <em>zero retries</em>, confirming it’s more retry-friendly and efficient under load.</p><p><strong>2. High Concurrency, With Lock (</strong><strong>--threads 32 --lock-accounts)</strong></p><p><strong>Explicit Multi-Statement + FOR UPDATE:</strong></p><ul><li>p50 latency: 88.1 ms</li><li>p99 latency: 199.5 ms</li><li>max latency: 361.1 ms</li><li>avg retries: <strong>0.00</strong></li></ul><p><strong>Single-Statement CTE:</strong></p><ul><li>p50 latency: 55.7 ms</li><li>p99 latency: 100.1 ms</li><li>max latency: 227.8 ms</li><li>avg retries: <strong>0.00</strong></li></ul><p>Adding FOR UPDATE predictably increases latency slightly due to explicit locking. However, the CTE still outperforms in tail latency and remains retry-free.</p><p><strong>3. Lower Concurrency, No Lock (</strong><strong>--threads 8)</strong></p><p><strong>Explicit Multi-Statement:</strong></p><ul><li>p50 latency: 35.5 ms</li><li>p99 latency: 121.8 ms</li><li>max latency: 183.2 ms</li><li>avg retries: <strong>0.01</strong></li></ul><p><strong>Single-Statement CTE:</strong></p><ul><li>p50 latency: 15.9 ms</li><li>p99 latency: 28.5 ms</li><li>max latency: 35.2 ms</li><li>avg retries: <strong>0.00</strong></li></ul><p>At lower concurrency, both methods perform better. However, the CTE still has a significant lead in latency — nearly 2x faster at the median — and avoids retries entirely.</p><h3><strong>Now for Real-World Latency: Multi-Region CockroachDB on AWS</strong></h3><p>To simulate production-like behavior, lets benchmarked CockroachDB performance across a <strong>multi-region AWS cluster</strong>, spanning:</p><ul><li>us-east-1 (N. Virginia)</li><li>us-east-2 (Ohio)</li><li>us-west-2 (Oregon)</li></ul><p>Below is the measured <strong>round-trip latency (RTT)</strong> between each region. These values reflect the real-world costs of RAFT quorum writes, DMLs, and leaseholder access in geo-distributed deployments.</p><pre>+-------------+-------------+-------------+-------------+<br>| From → To   | us-east-1   | us-east-2   | us-west-2   |<br>+-------------+-------------+-------------+-------------+<br>| us-east-1   |     —       |   9.38 ms   |  64.39 ms   |<br>| us-east-2   |   9.40 ms   |     —       |  47.82 ms   |<br>| us-west-2   |  63.28 ms   |  49.60 ms   |     —       |<br>+-------------+-------------+-------------+-------------+</pre><ul><li><strong>Short-haul latency</strong> (e.g., us-east-1 ↔ us-east-2) is minimal at ~9 ms, ideal for regional failover or leaseholder access.</li><li><strong>Long-haul writes</strong> (e.g., us-west-2 ↔ us-east-1) incur ~64–65 ms RTT, which significantly impacts RAFT write quorum latency.</li><li><strong>Transaction retries</strong> will be more frequent on multi-region clusters when cross-partition contention or leaseholder misplacement occurs.</li></ul><p>In this setup, how CockroachDB distributes <strong>range replicas</strong>, assigns <strong>leaseholders</strong>, and optimizes <strong>transaction locality</strong> will directly determine the system’s performance envelope.</p><p><strong>Benchmark Setup</strong></p><p><strong>Client Locations:</strong></p><ul><li>Remote client, approximately 11 ms round-trip time (RTT) from the load balancer (LB).</li><li>Local client, in the same availability zone as the CockroachDB cluster LB.</li></ul><p><strong>Workload Types:</strong></p><ul><li>“Light” workloads touch 10 to 40 accounts per transaction.</li><li>“Heavy” workloads touch 100 accounts per transaction (batch-style).</li></ul><p><strong>Test Parameters:</strong></p><ul><li>32 concurrent threads.</li><li>1 to 100 transactions per thread.</li><li>Configurable retry budget (from 1 to 30 max retries per transaction).</li></ul><p><strong>Transaction Modes:</strong></p><ul><li><strong>Original mode:</strong> Explicit transactions with retry logic handled at the client level.</li><li><strong>Optimized mode:</strong> Single-statement transactions (implicit), which rely on CockroachDB’s built-in contention management.</li></ul><h3>Benchmark Dimensions</h3><p><strong>Client Locations</strong></p><ul><li>Remote Client (≈11 ms RTT from LB)</li><li>Local Client (Same AZ as cluster LB)</li></ul><p><strong>Workload Types</strong></p><ul><li>Light: 10–40 accounts per transaction</li><li>Heavy: 100 accounts per transaction (batch-style)</li></ul><p><strong>Test Settings</strong></p><ul><li>Threads: 32</li><li>Transactions/Thread: 1–100</li><li>Max Retries: 3–30</li></ul><p><strong>Modes</strong></p><ul><li>Original: Explicit transaction with retries</li><li>Optimized: Single SQL statement (implicit transaction)</li></ul><h4>Remote Client (≈11 ms RTT) Results</h4><pre>| Accounts/Txn | Max Retries |   Mode     | p99 Latency | Max Latency | Avg Retries |<br>|--------------|-------------|------------|-------------|-------------|--------------|<br>|     40       |      3      |  Original  |   4.45 sec  |   5.22 sec  |     0.43     |<br>|              |             |  Optimized |   0.30 sec  |   0.40 sec  |     0.00     |<br>|     40       |      1      |  Original  |   2.76 sec  |   2.99 sec  |     0.19     |<br>|              |             |  Optimized |   0.36 sec  |   0.38 sec  |     0.00     |<br>|     40       |     30      |  Original  |   5.31 sec  |   5.31 sec  |     0.35     |<br>|              |             |  Optimized |   0.88 sec  |   1.58 sec  |     0.00     |</pre><p>Even with added round-trip latency, <strong>Optimized mode</strong> significantly reduces latency and eliminates retries.</p><h4>Local Client (Low Latency) Results</h4><pre>| Accounts/Txn | Max Retries |   Mode     | p99 Latency | Max Latency | Avg Retries |<br>|--------------|-------------|------------|-------------|-------------|--------------|<br>|     10       |     30      |  Original  |   0.26 sec  |   0.31 sec  |     0.02     |<br>|              |             |  Optimized |   0.08 sec  |   0.15 sec  |     0.00     |<br>|     40       |      3      |  Original  |   1.14 sec  |   1.40 sec  |     0.39     |<br>|              |             |  Optimized |   0.83 sec  |   1.48 sec  |     0.00     |<br>|     40       |     30      |  Original  |   1.60 sec  |   5.31 sec  |     0.35     |<br>|              |             |  Optimized |   0.88 sec  |   1.58 sec  |     0.00     |<br>|    100       |      3      |  Original  |   3.44 sec  |   4.54 sec  |     1.60     |<br>|              |             |  Optimized |   7.24 sec  |  11.25 sec  |     0.01     |<br>|    100       |     30      |  Original  |  15.26 sec  |  34.98 sec  |     2.03     |<br>|              |             |  Optimized |   7.30 sec  |  13.53 sec  |     0.01     |</pre><ul><li>At small scales, Original performs acceptably.</li><li>But as transaction size grows, <strong>latency explodes</strong> due to retries and contention.</li><li>Optimized mode keeps latency low and retry-free.</li></ul><pre>| Category       | Winner    | Notes                                             |<br>|----------------|-----------|---------------------------------------------------|<br>| Retry Handling | Optimized | Zero retries in all test cases                   |<br>| Latency (p99)  | Optimized | 2–20× better across remote/local tests           |<br>| Throughput     | Optimized | Higher success rates under heavy load            |<br>| Predictability | Optimized | Stable latencies, even at 100-account batches    |</pre><p><strong>Final Thoughts: What These Tests Reveal</strong></p><p>Running these benchmarks across varying levels of concurrency and locking behavior reveals an important truth: <strong>how you structure your transactions directly impacts performance, scalability, and operational complexity</strong> — especially when working with distributed SQL systems like CockroachDB or Postgres variants.</p><p><strong>Single-statement CTEs consistently outperformed</strong> traditional explicit transactions, particularly as concurrency increased. They delivered better tail latency, zero retries, and lower coordination overhead — even on a single-node CockroachDB instance running locally on a laptop.</p><p>With <strong>built-in retry safety</strong>, CTEs allow you to shift more logic into the database engine. This reduces client-side complexity and round-trips while enabling CockroachDB’s parallel execution engine to do what it does best: scale.</p><p>In contrast, <strong>explicit multi-statement transactions become increasingly fragile</strong> under load — especially when SELECT ... FOR UPDATE is introduced. Lock durations stretch out, retry complexity rises, and contention becomes harder to manage.</p><p>This doesn’t mean that CTEs are a one-size-fits-all solution. But if your application can lean into <strong>set-based processing over row-by-row operations</strong>, you’re likely to see meaningful performance wins, cleaner code, and fewer headaches at scale. Below Find my code and some sample tests with results.</p><pre>import psycopg2<br>import uuid<br>import time<br>import threading<br>import numpy as np<br>import argparse<br>from random import randint, sample<br><br># ----------------- UTILS ------------------<br>def safe_uuid(u):<br>    return str(u) if isinstance(u, uuid.UUID) else u<br><br># ----------------- SCHEMA ------------------<br>CREATE_TABLES_SQL = &quot;&quot;&quot;<br>DROP TABLE IF EXISTS transaction_item;<br>DROP TABLE IF EXISTS transaction;<br>DROP TABLE IF EXISTS account;<br><br>CREATE TABLE account (<br>    id UUID PRIMARY KEY,<br>    balance NUMERIC NOT NULL DEFAULT 0<br>);<br><br>CREATE TABLE transaction (<br>    id UUID PRIMARY KEY,<br>    booking_date TIMESTAMP DEFAULT now()<br>);<br><br>CREATE TABLE transaction_item (<br>    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),<br>    transaction_id UUID REFERENCES transaction(id),<br>    account_id UUID REFERENCES account(id),<br>    amount NUMERIC NOT NULL,<br>    running_balance NUMERIC<br>);<br>&quot;&quot;&quot;<br><br># ----------------- LOAD ACCOUNTS ------------------<br>def should_recreate(conn, expected_count):<br>    with conn.cursor() as cur:<br>        cur.execute(&quot;&quot;&quot;<br>            SELECT COUNT(*) FROM information_schema.tables<br>            WHERE table_name IN (&#39;account&#39;, &#39;transaction&#39;, &#39;transaction_item&#39;)<br>        &quot;&quot;&quot;)<br>        if cur.fetchone()[0] != 3:<br>            return True<br><br>        try:<br>            cur.execute(&quot;SELECT COUNT(*) FROM account&quot;)<br>            actual = cur.fetchone()[0]<br>            return actual != expected_count<br>        except psycopg2.Error:<br>            return True<br><br>def load_accounts(conn, num_accounts):<br>    with conn.cursor() as cur:<br>        print(f&quot;Inserting {num_accounts} accounts...&quot;)<br>        accounts = [(safe_uuid(uuid.uuid4()), 1000.00) for _ in range(num_accounts)]<br>        cur.executemany(&quot;INSERT INTO account (id, balance) VALUES (%s, %s)&quot;, accounts)<br>    conn.commit()<br>    return [acc[0] for acc in accounts]<br><br># ----------------- RETRY WRAPPER ------------------<br>def retry_transaction(func, conn_factory, accounts, retry_log, latency_log,<br>                      transactions_per_thread, max_retries, accounts_per_transaction):<br>    for _ in range(transactions_per_thread):<br>        retries = 0<br>        start_time = time.time()<br>        while True:<br>            try:<br>                with conn_factory() as conn:<br>                    selected_accounts = sample(accounts, accounts_per_transaction)<br>                    half = accounts_per_transaction // 2<br>                    transfer_amount = randint(10, 100)<br><br>                    account_amounts = []<br>                    for acc in selected_accounts[:half]:<br>                        account_amounts.append((safe_uuid(acc), -transfer_amount))<br>                    for acc in selected_accounts[half:]:<br>                        account_amounts.append((safe_uuid(acc), transfer_amount))<br><br>                    func(conn, account_amounts)<br>                    break<br>            except psycopg2.Error as e:<br>                if e.pgcode == &#39;40001&#39;:<br>                    retries += 1<br>                    if retries &gt;= max_retries:<br>                        print(&quot;Max retries exceeded.&quot;)<br>                        break<br>                    time.sleep(0.1 * retries)<br>                else:<br>                    raise<br>        latency = time.time() - start_time<br>        latency_log.append(latency)<br>        retry_log.append(retries)<br><br># ----------------- ORIGINAL TRANSACTION ------------------<br>def make_original_transaction(lock_accounts):<br>    def run_original_transaction(conn, account_amounts):<br>        try:<br>            with conn.cursor() as cur:<br>                tx_id = safe_uuid(uuid.uuid4())<br>                cur.execute(&quot;INSERT INTO transaction (id) VALUES (%s)&quot;, (tx_id,))<br><br>                account_ids = [acc for acc, _ in account_amounts]<br>                if lock_accounts:<br>                    cur.execute(&quot;SELECT id FROM account WHERE id = ANY(%s) FOR UPDATE&quot;, (account_ids,))<br>                else:<br>                    cur.execute(&quot;SELECT id FROM account WHERE id = ANY(%s)&quot;, (account_ids,))<br><br>                for account_id, amount in account_amounts:<br>                    cur.execute(&quot;&quot;&quot;<br>                        INSERT INTO transaction_item (transaction_id, account_id, amount, running_balance)<br>                        VALUES (%s, %s, %s, (<br>                            SELECT balance + %s FROM account WHERE id = %s<br>                        ))<br>                    &quot;&quot;&quot;, (tx_id, account_id, amount, amount, account_id))<br><br>                    cur.execute(&quot;&quot;&quot;<br>                        UPDATE account<br>                        SET balance = balance + %s<br>                        WHERE id = %s<br>                    &quot;&quot;&quot;, (amount, account_id))<br><br>                cur.execute(&quot;COMMIT;&quot;)<br>        except Exception:<br>            conn.rollback()<br>            raise<br>    return run_original_transaction<br><br># ----------------- OPTIMIZED TRANSACTION ------------------<br>def run_optimized_transaction(conn, account_amounts):<br>    placeholders = &quot;, &quot;.join([&quot;(%s::uuid, %s)&quot;] * len(account_amounts))<br>    values = [item for pair in account_amounts for item in pair]<br><br>    sql = f&quot;&quot;&quot;<br>    WITH input_data(account_id, amount) AS (<br>        VALUES {placeholders}<br>    ),<br>    head AS (<br>        INSERT INTO transaction (id)<br>        VALUES (gen_random_uuid())<br>        RETURNING id<br>    ),<br>    locked_accounts AS (<br>        SELECT a.id, a.balance<br>        FROM account a<br>        JOIN input_data i ON a.id = i.account_id<br>        FOR UPDATE<br>    ),<br>    inserted_items AS (<br>        INSERT INTO transaction_item (<br>            transaction_id, account_id, amount, running_balance<br>        )<br>        SELECT<br>            (SELECT id FROM head),<br>            i.account_id,<br>            i.amount,<br>            a.balance + i.amount<br>        FROM input_data i<br>        JOIN locked_accounts a ON a.id = i.account_id<br>        RETURNING id<br>    )<br>    UPDATE account<br>    SET balance = balance + i.amount<br>    FROM input_data i<br>    WHERE account.id = i.account_id;<br>    &quot;&quot;&quot;<br>    with conn.cursor() as cur:<br>        cur.execute(sql, values)<br><br># ----------------- METRICS ------------------<br>def print_stats(latencies, retries, label):<br>    if not latencies:<br>        print(f&quot;\n No successful transactions for {label}.&quot;)<br>        return<br>    arr = np.array(latencies)<br>    print(f&quot;\n Results for {label}&quot;)<br>    print(f&quot;Total transactions: {len(latencies)}&quot;)<br>    print(f&quot;p50 latency: {np.percentile(arr, 50):.4f} sec&quot;)<br>    print(f&quot;p90 latency: {np.percentile(arr, 90):.4f} sec&quot;)<br>    print(f&quot;p99 latency: {np.percentile(arr, 99):.4f} sec&quot;)<br>    print(f&quot;max latency: {arr.max():.4f} sec&quot;)<br>    print(f&quot;avg retries per txn: {np.mean(retries):.2f}&quot;)<br><br># ----------------- THREAD LAUNCHER ------------------<br>def run_benchmark(label, transaction_func, conn_factory, accounts,<br>                  threads, transactions_per_thread, max_retries, accounts_per_transaction):<br>    latencies = []<br>    retries = []<br>    thread_list = []<br><br>    for _ in range(threads):<br>        t = threading.Thread(target=retry_transaction, args=(<br>            transaction_func, conn_factory, accounts, retries, latencies,<br>            transactions_per_thread, max_retries, accounts_per_transaction<br>        ))<br>        thread_list.append(t)<br>        t.start()<br><br>    for t in thread_list:<br>        t.join()<br><br>    print_stats(latencies, retries, label)<br><br># ----------------- MAIN ------------------<br>def main():<br>    parser = argparse.ArgumentParser(description=&quot;Transaction benchmark test&quot;)<br>    parser.add_argument(&#39;--dsn&#39;, required=True)<br>    parser.add_argument(&#39;--accounts&#39;, type=int, default=100_000)<br>    parser.add_argument(&#39;--transactions-per-thread&#39;, type=int, default=50)<br>    parser.add_argument(&#39;--threads&#39;, type=int, default=8)<br>    parser.add_argument(&#39;--max-retries&#39;, type=int, default=5)<br>    parser.add_argument(&#39;--accounts-per-transaction&#39;, type=int, default=2)<br>    parser.add_argument(&#39;--lock-accounts&#39;, action=&#39;store_true&#39;, help=&#39;Use SELECT ... FOR UPDATE in original transaction&#39;)<br><br>    args = parser.parse_args()<br><br>    if args.accounts_per_transaction % 2 != 0:<br>        raise ValueError(&quot;accounts-per-transaction must be even&quot;)<br><br>    def conn_factory():<br>        return psycopg2.connect(dsn=args.dsn)<br><br>    with conn_factory() as conn:<br>        conn.autocommit = True<br>        if should_recreate(conn, args.accounts):<br>            print(&quot;Recreating tables and loading data...&quot;)<br>            with conn.cursor() as cur:<br>                cur.execute(CREATE_TABLES_SQL)<br>            accounts = load_accounts(conn, args.accounts)<br>        else:<br>            print(f&quot;Tables exist and account count matches ({args.accounts}). Skipping recreation.&quot;)<br>            with conn.cursor() as cur:<br>                cur.execute(&quot;SELECT id FROM account&quot;)<br>                accounts = [row[0] for row in cur.fetchall()]<br><br>    run_benchmark(<br>        &quot;Original (Unoptimized - Explicit Transaction)&quot;,<br>        make_original_transaction(args.lock_accounts),<br>        conn_factory,<br>        accounts,<br>        threads=args.threads,<br>        transactions_per_thread=args.transactions_per_thread,<br>        max_retries=args.max_retries,<br>        accounts_per_transaction=args.accounts_per_transaction<br>    )<br><br>    run_benchmark(<br>        &quot;Optimized (Single SQL Statement - Implicit Transaction)&quot;,<br>        run_optimized_transaction,<br>        conn_factory,<br>        accounts,<br>        threads=args.threads,<br>        transactions_per_thread=args.transactions_per_thread,<br>        max_retries=args.max_retries,<br>        accounts_per_transaction=args.accounts_per_transaction<br>    )<br><br>if __name__ == &quot;__main__&quot;:<br>    main()<br><br></pre><pre>usage: benchmark_transactions_locked_update.py [-h] --dsn DSN [--accounts ACCOUNTS] [--transactions-per-thread TRANSACTIONS_PER_THREAD] [--threads THREADS] [--max-retries MAX_RETRIES]<br>                                               [--accounts-per-transaction ACCOUNTS_PER_TRANSACTION] [--lock-accounts]<br><br>python benchmark_transactions_locked_update.py    --dsn &quot;postgresql://root@localhost:26257/defaultdb?sslmode=disable&quot;   --accounts 50000   --transactions-per-thread 100   --threads 64   --accounts-per-transaction 40   --max-retries 30   <br>Tables exist and account count matches (50000). Skipping recreation.<br><br>Results for Original (Unoptimized - Explicit Transaction)<br>Total transactions: 6400<br>p50 latency: 0.3326 sec<br>p90 latency: 1.1482 sec<br>p99 latency: 2.7907 sec<br>max latency: 7.2095 sec<br>avg retries per txn: 0.66<br><br>Results for Optimized (Single SQL Statement - Implicit Transaction)<br>Total transactions: 6400<br>p50 latency: 0.3924 sec<br>p90 latency: 1.3961 sec<br>p99 latency: 2.7300 sec<br>max latency: 6.1934 sec<br>avg retries per txn: 0.01<br><br><br><br>python benchmark_transactions_locked_update.py    --dsn &quot;postgresql://root@localhost:26257/defaultdb?sslmode=disable&quot;   --accounts 50000   --transactions-per-thread 100   --threads 64   --accounts-per-transaction 10   --max-retries 3   <br>Tables exist and account count matches (50000). Skipping recreation.<br><br>Results for Original (Unoptimized - Explicit Transaction)<br>Total transactions: 6400<br>p50 latency: 0.0705 sec<br>p90 latency: 0.0907 sec<br>p99 latency: 0.2975 sec<br>max latency: 0.6424 sec<br>avg retries per txn: 0.05<br><br>Results for Optimized (Single SQL Statement - Implicit Transaction)<br>Total transactions: 6400<br>p50 latency: 0.0316 sec<br>p90 latency: 0.0611 sec<br>p99 latency: 0.1104 sec<br>max latency: 0.2307 sec<br>avg retries per txn: 0.00<br><br>python benchmark_transactions_locked_update.py    --dsn &quot;postgresql://root@localhost:26257/defaultdb?sslmode=disable&quot;   --accounts 50000   --transactions-per-thread 100   --threads 64   --accounts-per-transaction 10   --max-retries 3  --lock-accounts<br>Tables exist and account count matches (50000). Skipping recreation.<br><br>Results for Original (Unoptimized - Explicit Transaction)<br>Total transactions: 6400<br>p50 latency: 0.0707 sec<br>p90 latency: 0.1005 sec<br>p99 latency: 0.1911 sec<br>max latency: 0.3253 sec<br>avg retries per txn: 0.00<br><br>Results for Optimized (Single SQL Statement - Implicit Transaction)<br>Total transactions: 6400<br>p50 latency: 0.0316 sec<br>p90 latency: 0.0658 sec<br>p99 latency: 0.1204 sec<br>max latency: 0.3176 sec<br>avg retries per txn: 0.00<br><br></pre><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=09190b116e64" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Limit the Number of Rows Read or Written per Transaction in CockroachDB — With Role-Based…]]></title>
            <link>https://andrewdeally.medium.com/how-to-limit-the-number-of-rows-read-or-written-per-transaction-in-cockroachdb-with-role-based-6433c59d7039?source=rss-5212e7f73704------2</link>
            <guid isPermaLink="false">https://medium.com/p/6433c59d7039</guid>
            <dc:creator><![CDATA[Andrew Deally]]></dc:creator>
            <pubDate>Fri, 13 Jun 2025 13:30:29 GMT</pubDate>
            <atom:updated>2025-06-13T17:46:21.078Z</atom:updated>
            <content:encoded><![CDATA[<h3>How to Limit the Number of Rows Read or Written per Transaction in CockroachDB — With Role-Based Controls</h3><p>CockroachDB has a powerful feature that lets you <strong>limit the number of rows read or written within a single transaction</strong>. This capability is invaluable for controlling resource usage, preventing runaway queries, and enforcing application-level policies.</p><p>In this DEMO, I’ll walk you through:</p><ul><li>What this feature is and why it matters</li><li>How to demo it using cockroach demo with sample data</li><li>How to combine it with <strong>role-based policies</strong> to enforce different limits for different user roles</li></ul><h3>Why Limit Rows in Transactions?</h3><p>Imagine you want to prevent any single transaction from reading or writing more than a set number of rows. This can help you:</p><ul><li>Protect your cluster from heavy or accidental large scans</li><li>Enforce business rules, like junior analysts can only read a limited dataset</li><li>Improve query predictability and reduce latency spikes</li></ul><h3>Demo: Setting Up and Using Row Limits with cockroach demo</h3><p>First, let’s start a demo cluster:</p><p>cockroach demo — insecure — empty</p><p>Then, create a sample table and insert <strong>2500 rows</strong>:</p><p>CREATE TABLE employees (</p><p>id INT PRIMARY KEY,</p><p>name STRING,</p><p>salary INT</p><p>);</p><p>INSERT INTO employees (id, name, salary)</p><p>SELECT i, ‘Employee_’ || i, 50000 + (i * 10)</p><p>FROM generate_series(1, 2500) AS i;</p><p>Now, limit the number of rows <strong>read</strong> in a transaction to 100:</p><p>SET SESSION transaction_rows_read_err = 100;</p><p>Try to read all employees:</p><p>SELECT * FROM employees;</p><p>You’ll see:</p><p>(error encountered after some results)</p><p>ERROR: txn has read 101 rows, which is above the limit</p><p>The transaction aborts because it read more than 100 rows.</p><p>Similarly, you can limit the number of rows <strong>written</strong>:</p><p>SET SESSION transaction_rows_written_err = 1000;</p><p>Try inserting more than 1000 rows in a single transaction:</p><p>INSERT INTO employees (id, name, salary)</p><p>SELECT i, ‘Employee_’ || i, 50000 + (i * 10)</p><p>FROM generate_series(10001, 11050) AS i;</p><p>You will get an error like:</p><p>ERROR: txn has written 1001 rows, which is above the limit</p><h3>Role-Based Policies with Transaction Limits</h3><p>You can combine this feature with <strong>role-based access control (RBAC)</strong> to enforce different limits based on user roles.</p><h3>Step 1: Create Roles and Users</h3><p>CREATE ROLE “DBEAVER-USER”;</p><p>CREATE ROLE senior_analyst;</p><p>CREATE USER alice;</p><p>CREATE USER bob;</p><p>GRANT “DBEAVER-USER” TO alice;</p><p>GRANT senior_analyst TO bob;</p><p>Instead of setting transaction limits manually at each session, you can configure <strong>default session settings at the role level </strong>using ALTER ROLE ... SET. These settings will be applied automatically whenever a user with that role logs in.</p><p><strong>For users with the </strong><strong>DBEAVER-USER role</strong> (e.g., alice), apply strict limits:</p><p>ALTER ROLE “DBEAVER-USER” SET transaction_rows_read_err = 100;<br>ALTER ROLE “DBEAVER-USER” SET transaction_rows_written_err = 100;</p><p><strong>For users with the </strong><strong>senior_analyst role</strong> (e.g., bob), allow larger transactions:</p><p>ALTER ROLE senior_analyst SET transaction_rows_read_err = 10000;<br>ALTER ROLE senior_analyst SET transaction_rows_written_err = 5000;</p><p>These limits will automatically take effect for any session started by a user with the corresponding role.</p><p>This way, DBEAVER-USER users are restricted to fewer rows per transaction compared to senior analysts—without needing application logic or manual SET SESSION commands.</p><h3>Summary</h3><ul><li>CockroachDB allows <strong>limiting the number of rows read or written in a single transaction</strong> through session variables.</li><li>This is powerful for <strong>resource control, safety, and enforcing business rules</strong>.</li><li>Combine this with <strong>role-based policies</strong> to customize limits per user or role.</li><li>Use programmatic logic to automate these limits dynamically.</li></ul><p>This feature helps maintain stability and fairness in multi-tenant or role-diverse environments.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6433c59d7039" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Set Based operations with CockroachDB]]></title>
            <link>https://andrewdeally.medium.com/set-based-operations-with-cockroachdb-c9f371992dc7?source=rss-5212e7f73704------2</link>
            <guid isPermaLink="false">https://medium.com/p/c9f371992dc7</guid>
            <dc:creator><![CDATA[Andrew Deally]]></dc:creator>
            <pubDate>Mon, 01 May 2023 03:37:49 GMT</pubDate>
            <atom:updated>2023-05-01T03:37:49.803Z</atom:updated>
            <content:encoded><![CDATA[<p>part 2</p><p>In a <a href="https://andrewdeally.medium.com/when-and-how-to-use-query-parallelism-with-cockroachdb-df92fbe92845">prior blog</a>, I illustrated examples of rewriting row by row transactions to set based statements which improved parallel DML operations in CRDB. Next, I would like to provide another example of how to optimize deletes with a set based approach using inline tables with CTEs. Lets look at a common delete pattern where single row deletes are bundled into a single transaction.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lm-IeLttYUovgIstU-gimg.png" /><figcaption>Notice the transaction is 999 delete statements each on one row</figcaption></figure><p>This is considered a long running explicit transaction which average 1+ seconds per exection. By simply loading the rows into a simple inline table and joining the the table to delete the operation is efficient, short 30ms and is considered a SQL set based operation.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PaUiTjyjiX1iQt8TQG4pow.png" /></figure><p>here is the SQL statement</p><pre>WITH<br>       cte0 (c_w_id, c_d_id, c_id)<br>              AS (<br>                    VALUES<br>                           (3, 1, 1),<br>                           (3, 1, 2),<br>                           (3, 1, 3),<br>                           (3, 1, 4),<br>                           (3, 1, 5),<br>                           (3, 1, 6),<br>                           (3, 1, 7),<br>                           (3, 1, 8),<br>                           (3, 1, 9),<br>                           (3, 1, 10),<br>                           (3, 1, 11),<br>                           (3, 1, 12),<br>                           (3, 1, 13),<br>                           (3, 1, 14),<br>                           (3, 1, 15),<br>                           (3, 1, 16),<br>                           (3, 1, 17),<br>                           (3, 1, 18),<br>                           (3, 1, 19),<br>                           (3, 1, 20),<br>                           (3, 1, 21),<br>                           (3, 1, 22),<br>                           (3, 1, 23),<br>                           (3, 1, 24),<br>                           (3, 1, 25),<br>                           (3, 1, 26),<br>                           (3, 1, 27),<br>                           (3, 1, 28),<br>                           (3, 1, 29),<br>                           (3, 1, 30),<br>                           (3, 1, 31),<br>                           (3, 1, 32),<br>                           (3, 1, 33),<br>                           (3, 1, 34),<br>                           (3, 1, 35),<br>                           (3, 1, 36),<br>                           (3, 1, 37),<br>                           (3, 1, 38),<br>                           (3, 1, 39),<br>                           (3, 1, 40),<br>                           (3, 1, 41),<br>                           (3, 1, 42),<br>                           (3, 1, 43),<br>                           (3, 1, 44),<br>                           (3, 1, 45),<br>                           (3, 1, 46),<br>                           (3, 1, 47),<br>                           (3, 1, 48),<br>                           (3, 1, 49),<br>                           (3, 1, 50),<br>                           (3, 1, 51),<br>                           (3, 1, 52),<br>                           (3, 1, 53),<br>                           (3, 1, 54),<br>                           (3, 1, 55),<br>                           (3, 1, 56),<br>                           (3, 1, 57),<br>                           (3, 1, 58),<br>                           (3, 1, 59),<br>                           (3, 1, 60),<br>                           (3, 1, 61),<br>                           (3, 1, 62),<br>                           (3, 1, 63),<br>                           (3, 1, 64),<br>                           (3, 1, 65),<br>                           (3, 1, 66),<br>                           (3, 1, 67),<br>                           (3, 1, 68),<br>                           (3, 1, 69),<br>                           (3, 1, 70),<br>                           (3, 1, 71),<br>                           (3, 1, 72),<br>                           (3, 1, 73),<br>                           (3, 1, 74),<br>                           (3, 1, 75),<br>                           (3, 1, 76),<br>                           (3, 1, 77),<br>                           (3, 1, 78),<br>                           (3, 1, 79),<br>                           (3, 1, 80),<br>                           (3, 1, 81),<br>                           (3, 1, 82),<br>                           (3, 1, 83),<br>                           (3, 1, 84),<br>                           (3, 1, 85),<br>                           (3, 1, 86),<br>                           (3, 1, 87),<br>                           (3, 1, 88),<br>                           (3, 1, 89),<br>                           (3, 1, 90),<br>                           (3, 1, 91),<br>                           (3, 1, 92),<br>                           (3, 1, 93),<br>                           (3, 1, 94),<br>                           (3, 1, 95),<br>                           (3, 1, 96),<br>                           (3, 1, 97),<br>                           (3, 1, 98),<br>                           (3, 1, 99),<br>                           (3, 1, 100),<br>                           (3, 1, 101),<br>                           (3, 1, 102),<br>                           (3, 1, 103),<br>                           (3, 1, 104),<br>                           (3, 1, 105),<br>                           (3, 1, 106),<br>                           (3, 1, 107),<br>                           (3, 1, 108),<br>                           (3, 1, 109),<br>                            (3, 1, 110),<br>                           (3, 1, 111),<br>                           (3, 1, 112),<br>                           (3, 1, 113),<br>                           (3, 1, 114),<br>                           (3, 1, 115),<br>                           (3, 1, 116),<br>                           (3, 1, 117),<br>                           (3, 1, 118),<br>                           (3, 1, 119),<br>                           (3, 1, 120),<br>                           (3, 1, 121),<br>                           (3, 1, 122),<br>                           (3, 1, 123),<br>                           (3, 1, 124),<br>                           (3, 1, 125),<br>                           (3, 1, 126),<br>                           (3, 1, 127),<br>                           (3, 1, 128),<br>                           (3, 1, 129),<br>                           (3, 1, 130),<br>                           (3, 1, 131),<br>                           (3, 1, 132),<br>                           (3, 1, 133),<br>                           (3, 1, 134),<br>                           (3, 1, 135),<br>                           (3, 1, 136),<br>                           (3, 1, 137),<br>                           (3, 1, 138),<br>                           (3, 1, 139),<br>                           (3, 1, 140),<br>                           (3, 1, 141),<br>                           (3, 1, 142),<br>                           (3, 1, 143),<br>                           (3, 1, 144),<br>                           (3, 1, 145),<br>                           (3, 1, 146),<br>                           (3, 1, 147),<br>                           (3, 1, 148),<br>                           (3, 1, 149),<br>                           (3, 1, 150),<br>                           (3, 1, 151),<br>                           (3, 1, 152),<br>                           (3, 1, 153),<br>                           (3, 1, 154),<br>                           (3, 1, 155),<br>                           (3, 1, 156),<br>                           (3, 1, 157),<br>                           (3, 1, 158),<br>                           (3, 1, 159),<br>                           (3, 1, 160),<br>                           (3, 1, 161),<br>                           (3, 1, 162),<br>                           (3, 1, 163),<br>                           (3, 1, 164),<br>                           (3, 1, 165),<br>                           (3, 1, 166),<br>                           (3, 1, 167),<br>                           (3, 1, 168),<br>                           (3, 1, 169),<br>                           (3, 1, 170),<br>                           (3, 1, 171),<br>                           (3, 1, 172),<br>                           (3, 1, 173),<br>                           (3, 1, 174),<br>                           (3, 1, 175),<br>                           (3, 1, 176),<br>                           (3, 1, 177),<br>                           (3, 1, 178),<br>                           (3, 1, 179),<br>                           (3, 1, 180),<br>                           (3, 1, 181),<br>                           (3, 1, 182),<br>                           (3, 1, 183),<br>                           (3, 1, 184),<br>                           (3, 1, 185),<br>                           (3, 1, 186),<br>                           (3, 1, 187),<br>                           (3, 1, 188),<br>                           (3, 1, 189),<br>                           (3, 1, 190),<br>                           (3, 1, 191),<br>                           (3, 1, 192),<br>                           (3, 1, 193),<br>                           (3, 1, 194),<br>                           (3, 1, 195),<br>                           (3, 1, 196),<br>                           (3, 1, 197),<br>                           (3, 1, 198),<br>                           (3, 1, 199),<br>                           (3, 1, 200),<br>                           (3, 1, 201),<br>                           (3, 1, 202),<br>                           (3, 1, 203),<br>                           (3, 1, 204),<br>                           (3, 1, 205),<br>                           (3, 1, 206),<br>                           (3, 1, 207),<br>                           (3, 1, 208),<br>                           (3, 1, 209),<br>                           (3, 1, 210),<br>                           (3, 1, 211),<br>                           (3, 1, 212),<br>                           (3, 1, 213),<br>                           (3, 1, 214),<br>                           (3, 1, 215),<br>                           (3, 1, 216),<br>                           (3, 1, 217),<br>                           (3, 1, 218),<br>                           (3, 1, 219),<br>                           (3, 1, 220),<br>                           (3, 1, 221),<br>                           (3, 1, 222),<br>                           (3, 1, 223),<br>                           (3, 1, 224),<br>                           (3, 1, 225),<br>                           (3, 1, 226),<br>                           (3, 1, 227),<br>                           (3, 1, 228),<br>                           (3, 1, 229),<br>                           (3, 1, 230),<br>                           (3, 1, 231),<br>                           (3, 1, 232),<br>                           (3, 1, 233),<br>                           (3, 1, 234),<br>                           (3, 1, 235),<br>                           (3, 1, 236),<br>                           (3, 1, 237),<br>                           (3, 1, 238),<br>                           (3, 1, 239),<br>                           (3, 1, 240),<br>                           (3, 1, 241),<br>                           (3, 1, 242),<br>                           (3, 1, 243),<br>                           (3, 1, 244),<br>                           (3, 1, 245),<br>                           (3, 1, 246),<br>                           (3, 1, 247),<br>                           (3, 1, 248),<br>                           (3, 1, 249),<br>                           (3, 1, 250),<br>                           (3, 1, 251),<br>                           (3, 1, 252),<br>                           (3, 1, 253),<br>                           (3, 1, 254),<br>                           (3, 1, 255),<br>                           (3, 1, 256),<br>                           (3, 1, 257),<br>                           (3, 1, 258),<br>                           (3, 1, 259),<br>                           (3, 1, 260),<br>                           (3, 1, 261),<br>                           (3, 1, 262),<br>                           (3, 1, 263),<br>                           (3, 1, 264),<br>                           (3, 1, 265),<br>                           (3, 1, 266),<br>                           (3, 1, 267),<br>                           (3, 1, 268),<br>                           (3, 1, 269),<br>                           (3, 1, 270),<br>                           (3, 1, 271),<br>                           (3, 1, 272),<br>                           (3, 1, 273),<br>                           (3, 1, 274),<br>                           (3, 1, 275),<br>                           (3, 1, 276),<br>                           (3, 1, 277),<br>                           (3, 1, 278),<br>                           (3, 1, 279),<br>                           (3, 1, 280),<br>                           (3, 1, 281),<br>                           (3, 1, 282),<br>                           (3, 1, 283),<br>                           (3, 1, 284),<br>                           (3, 1, 285),<br>                           (3, 1, 286),<br>                           (3, 1, 287),<br>                           (3, 1, 288),<br>                           (3, 1, 289),<br>                           (3, 1, 290),<br>                           (3, 1, 291),<br>                           (3, 1, 292),<br>                           (3, 1, 293),<br>                           (3, 1, 294),<br>                           (3, 1, 295),<br>                           (3, 1, 296),<br>                           (3, 1, 297),<br>                           (3, 1, 298),<br>                           (3, 1, 299),<br>                           (3, 1, 300),<br>                           (3, 1, 301),<br>                           (3, 1, 302),<br>                           (3, 1, 303),<br>                           (3, 1, 304),<br>                           (3, 1, 305),<br>                           (3, 1, 306),<br>                           (3, 1, 307),<br>                           (3, 1, 308),<br>                           (3, 1, 309),<br>                           (3, 1, 310),<br>                           (3, 1, 311),<br>                           (3, 1, 312),<br>                           (3, 1, 313),<br>                           (3, 1, 314),<br>                           (3, 1, 315),<br>                           (3, 1, 316),<br>                           (3, 1, 317),<br>                           (3, 1, 318),<br>                           (3, 1, 319),<br>                           (3, 1, 320),<br>                           (3, 1, 321),<br>                           (3, 1, 322),<br>                           (3, 1, 323),<br>                           (3, 1, 324),<br>                           (3, 1, 325),<br>                           (3, 1, 326),<br>                           (3, 1, 327),<br>                           (3, 1, 328),<br>                           (3, 1, 329),<br>                           (3, 1, 330),<br>                           (3, 1, 331),<br>                           (3, 1, 332),<br>                           (3, 1, 333),<br>                           (3, 1, 334),<br>                           (3, 1, 335),<br>                           (3, 1, 336),<br>                           (3, 1, 337),<br>                           (3, 1, 338),<br>                           (3, 1, 339),<br>                           (3, 1, 340),<br>                           (3, 1, 341),<br>                           (3, 1, 342),<br>                           (3, 1, 343),<br>                           (3, 1, 344),<br>                           (3, 1, 345),<br>                           (3, 1, 346),<br>                           (3, 1, 347),<br>                           (3, 1, 348),<br>                           (3, 1, 349),<br>                           (3, 1, 350),<br>                           (3, 1, 351),<br>                           (3, 1, 352),<br>                           (3, 1, 353),<br>                           (3, 1, 354),<br>                           (3, 1, 355),<br>                           (3, 1, 356),<br>                           (3, 1, 357),<br>                           (3, 1, 358),<br>                           (3, 1, 359),<br>                           (3, 1, 360),<br>                           (3, 1, 361),<br>                           (3, 1, 362),<br>                           (3, 1, 363),<br>                           (3, 1, 364),<br>                           (3, 1, 365),<br>                            (3, 1, 366),<br>                           (3, 1, 367),<br>                           (3, 1, 368),<br>                           (3, 1, 369),<br>                           (3, 1, 370),<br>                           (3, 1, 371),<br>                           (3, 1, 372),<br>                           (3, 1, 373),<br>                           (3, 1, 374),<br>                           (3, 1, 375),<br>                           (3, 1, 376),<br>                           (3, 1, 377),<br>                           (3, 1, 378),<br>                           (3, 1, 379),<br>                           (3, 1, 380),<br>                           (3, 1, 381),<br>                           (3, 1, 382),<br>                           (3, 1, 383),<br>                           (3, 1, 384),<br>                           (3, 1, 385),<br>                           (3, 1, 386),<br>                           (3, 1, 387),<br>                           (3, 1, 388),<br>                           (3, 1, 389),<br>                           (3, 1, 390),<br>                           (3, 1, 391),<br>                           (3, 1, 392),<br>                           (3, 1, 393),<br>                           (3, 1, 394),<br>                           (3, 1, 395),<br>                           (3, 1, 396),<br>                           (3, 1, 397),<br>                           (3, 1, 398),<br>                           (3, 1, 399),<br>                           (3, 1, 400),<br>                           (3, 1, 401),<br>                           (3, 1, 402),<br>                           (3, 1, 403),<br>                           (3, 1, 404),<br>                           (3, 1, 405),<br>                           (3, 1, 406),<br>                           (3, 1, 407),<br>                           (3, 1, 408),<br>                           (3, 1, 409),<br>                           (3, 1, 410),<br>                           (3, 1, 411),<br>                           (3, 1, 412),<br>                           (3, 1, 413),<br>                           (3, 1, 414),<br>                           (3, 1, 415),<br>                           (3, 1, 416),<br>                           (3, 1, 417),<br>                           (3, 1, 418),<br>                           (3, 1, 419),<br>                           (3, 1, 420),<br>                           (3, 1, 421),<br>                           (3, 1, 422),<br>                           (3, 1, 423),<br>                           (3, 1, 424),<br>                           (3, 1, 425),<br>                           (3, 1, 426),<br>                           (3, 1, 427),<br>                           (3, 1, 428),<br>                           (3, 1, 429),<br>                           (3, 1, 430),<br>                           (3, 1, 431),<br>                           (3, 1, 432),<br>                           (3, 1, 433),<br>                           (3, 1, 434),<br>                           (3, 1, 435),<br>                           (3, 1, 436),<br>                           (3, 1, 437),<br>                           (3, 1, 438),<br>                           (3, 1, 439),<br>                           (3, 1, 440),<br>                           (3, 1, 441),<br>                           (3, 1, 442),<br>                           (3, 1, 443),<br>                           (3, 1, 444),<br>                           (3, 1, 445),<br>                           (3, 1, 446),<br>                           (3, 1, 447),<br>                           (3, 1, 448),<br>                           (3, 1, 449),<br>                           (3, 1, 450),<br>                           (3, 1, 451),<br>                           (3, 1, 452),<br>                           (3, 1, 453),<br>                           (3, 1, 454),<br>                           (3, 1, 455),<br>                           (3, 1, 456),<br>                           (3, 1, 457),<br>                           (3, 1, 458),<br>                           (3, 1, 459),<br>                           (3, 1, 460),<br>                           (3, 1, 461),<br>                           (3, 1, 462),<br>                           (3, 1, 463),<br>                           (3, 1, 464),<br>                           (3, 1, 465),<br>                           (3, 1, 466),<br>                           (3, 1, 467),<br>                           (3, 1, 468),<br>                           (3, 1, 469),<br>                           (3, 1, 470),<br>                           (3, 1, 471),<br>                           (3, 1, 472),<br>                           (3, 1, 473),<br>                           (3, 1, 474),<br>                           (3, 1, 475),<br>                           (3, 1, 476),<br>                           (3, 1, 477),<br>                           (3, 1, 478),<br>                           (3, 1, 479),<br>                           (3, 1, 480),<br>                           (3, 1, 481),<br>                           (3, 1, 482),<br>                           (3, 1, 483),<br>                           (3, 1, 484),<br>                           (3, 1, 485),<br>                           (3, 1, 486),<br>                           (3, 1, 487),<br>                           (3, 1, 488),<br>                           (3, 1, 489),<br>                           (3, 1, 490),<br>                           (3, 1, 491),<br>                           (3, 1, 492),<br>                           (3, 1, 493),<br>                           (3, 1, 494),<br>                           (3, 1, 495),<br>                           (3, 1, 496),<br>                           (3, 1, 497),<br>                           (3, 1, 498),<br>                           (3, 1, 499),<br>                           (3, 1, 500),<br>                           (3, 1, 501),<br>                           (3, 1, 502),<br>                           (3, 1, 503),<br>                           (3, 1, 504),<br>                           (3, 1, 505),<br>                           (3, 1, 506),<br>                           (3, 1, 507),<br>                           (3, 1, 508),<br>                           (3, 1, 509),<br>                           (3, 1, 510),<br>                           (3, 1, 511),<br>                           (3, 1, 512),<br>                           (3, 1, 513),<br>                           (3, 1, 514),<br>                           (3, 1, 515),<br>                           (3, 1, 516),<br>                           (3, 1, 517),<br>                           (3, 1, 518),<br>                           (3, 1, 519),<br>                           (3, 1, 520),<br>                           (3, 1, 521),<br>                           (3, 1, 522),<br>                           (3, 1, 523),<br>                           (3, 1, 524),<br>                           (3, 1, 525),<br>                           (3, 1, 526),<br>                           (3, 1, 527),<br>                           (3, 1, 528),<br>                           (3, 1, 529),<br>                           (3, 1, 530),<br>                           (3, 1, 531),<br>                           (3, 1, 532),<br>                           (3, 1, 533),<br>                           (3, 1, 534),<br>                           (3, 1, 535),<br>                           (3, 1, 536),<br>                           (3, 1, 537),<br>                           (3, 1, 538),<br>                           (3, 1, 539),<br>                           (3, 1, 540),<br>                           (3, 1, 541),<br>                           (3, 1, 542),<br>                           (3, 1, 543),<br>                           (3, 1, 544),<br>                           (3, 1, 545),<br>                           (3, 1, 546),<br>                           (3, 1, 547),<br>                           (3, 1, 548),<br>                           (3, 1, 549),<br>                           (3, 1, 550),<br>                           (3, 1, 551),<br>                           (3, 1, 552),<br>                           (3, 1, 553),<br>                           (3, 1, 554),<br>                           (3, 1, 555),<br>                           (3, 1, 556),<br>                           (3, 1, 557),<br>                           (3, 1, 558),<br>                           (3, 1, 559),<br>                           (3, 1, 560),<br>                           (3, 1, 561),<br>                           (3, 1, 562),<br>                           (3, 1, 563),<br>                           (3, 1, 564),<br>                           (3, 1, 565),<br>                           (3, 1, 566),<br>                           (3, 1, 567),<br>                           (3, 1, 568),<br>                           (3, 1, 569),<br>                           (3, 1, 570),<br>                           (3, 1, 571),<br>                           (3, 1, 572),<br>                           (3, 1, 573),<br>                           (3, 1, 574),<br>                           (3, 1, 575),<br>                           (3, 1, 576),<br>                           (3, 1, 577),<br>                           (3, 1, 578),<br>                           (3, 1, 579),<br>                           (3, 1, 580),<br>                           (3, 1, 581),<br>                           (3, 1, 582),<br>                           (3, 1, 583),<br>                           (3, 1, 584),<br>                           (3, 1, 585),<br>                           (3, 1, 586),<br>                           (3, 1, 587),<br>                           (3, 1, 588),<br>                           (3, 1, 589),<br>                           (3, 1, 590),<br>                           (3, 1, 591),<br>                           (3, 1, 592),<br>                           (3, 1, 593),<br>                           (3, 1, 594),<br>                           (3, 1, 595),<br>                           (3, 1, 596),<br>                           (3, 1, 597),<br>                           (3, 1, 598),<br>                           (3, 1, 599),<br>                           (3, 1, 600),<br>                           (3, 1, 601),<br>                           (3, 1, 602),<br>                           (3, 1, 603),<br>                           (3, 1, 604),<br>                           (3, 1, 605),<br>                           (3, 1, 606),<br>                           (3, 1, 607),<br>                           (3, 1, 608),<br>                           (3, 1, 609),<br>                           (3, 1, 610),<br>                           (3, 1, 611),<br>                           (3, 1, 612),<br>                           (3, 1, 613),<br>                           (3, 1, 614),<br>                           (3, 1, 615),<br>                           (3, 1, 616),<br>                           (3, 1, 617),<br>                           (3, 1, 618),<br>                           (3, 1, 619),<br>                           (3, 1, 620),<br>                           (3, 1, 621),<br>                            (3, 1, 622),<br>                           (3, 1, 623),<br>                           (3, 1, 624),<br>                           (3, 1, 625),<br>                           (3, 1, 626),<br>                           (3, 1, 627),<br>                           (3, 1, 628),<br>                           (3, 1, 629),<br>                           (3, 1, 630),<br>                           (3, 1, 631),<br>                           (3, 1, 632),<br>                           (3, 1, 633),<br>                           (3, 1, 634),<br>                           (3, 1, 635),<br>                           (3, 1, 636),<br>                           (3, 1, 637),<br>                           (3, 1, 638),<br>                           (3, 1, 639),<br>                           (3, 1, 640),<br>                           (3, 1, 641),<br>                           (3, 1, 642),<br>                           (3, 1, 643),<br>                           (3, 1, 644),<br>                           (3, 1, 645),<br>                           (3, 1, 646),<br>                           (3, 1, 647),<br>                           (3, 1, 648),<br>                           (3, 1, 649),<br>                           (3, 1, 650),<br>                           (3, 1, 651),<br>                           (3, 1, 652),<br>                           (3, 1, 653),<br>                           (3, 1, 654),<br>                           (3, 1, 655),<br>                           (3, 1, 656),<br>                           (3, 1, 657),<br>                           (3, 1, 658),<br>                           (3, 1, 659),<br>                           (3, 1, 660),<br>                           (3, 1, 661),<br>                           (3, 1, 662),<br>                           (3, 1, 663),<br>                           (3, 1, 664),<br>                           (3, 1, 665),<br>                           (3, 1, 666),<br>                           (3, 1, 667),<br>                           (3, 1, 668),<br>                           (3, 1, 669),<br>                           (3, 1, 670),<br>                           (3, 1, 671),<br>                           (3, 1, 672),<br>                           (3, 1, 673),<br>                           (3, 1, 674),<br>                           (3, 1, 675),<br>                           (3, 1, 676),<br>                           (3, 1, 677),<br>                           (3, 1, 678),<br>                           (3, 1, 679),<br>                           (3, 1, 680),<br>                           (3, 1, 681),<br>                           (3, 1, 682),<br>                           (3, 1, 683),<br>                           (3, 1, 684),<br>                           (3, 1, 685),<br>                           (3, 1, 686),<br>                           (3, 1, 687),<br>                           (3, 1, 688),<br>                           (3, 1, 689),<br>                           (3, 1, 690),<br>                           (3, 1, 691),<br>                           (3, 1, 692),<br>                           (3, 1, 693),<br>                           (3, 1, 694),<br>                           (3, 1, 695),<br>                           (3, 1, 696),<br>                           (3, 1, 697),<br>                           (3, 1, 698),<br>                           (3, 1, 699),<br>                           (3, 1, 700),<br>                           (3, 1, 701),<br>                           (3, 1, 702),<br>                           (3, 1, 703),<br>                           (3, 1, 704),<br>                           (3, 1, 705),<br>                           (3, 1, 706),<br>                           (3, 1, 707),<br>                           (3, 1, 708),<br>                           (3, 1, 709),<br>                           (3, 1, 710),<br>                           (3, 1, 711),<br>                           (3, 1, 712),<br>                           (3, 1, 713),<br>                           (3, 1, 714),<br>                           (3, 1, 715),<br>                           (3, 1, 716),<br>                           (3, 1, 717),<br>                           (3, 1, 718),<br>                           (3, 1, 719),<br>                           (3, 1, 720),<br>                           (3, 1, 721),<br>                           (3, 1, 722),<br>                           (3, 1, 723),<br>                           (3, 1, 724),<br>                           (3, 1, 725),<br>                           (3, 1, 726),<br>                           (3, 1, 727),<br>                           (3, 1, 728),<br>                           (3, 1, 729),<br>                           (3, 1, 730),<br>                           (3, 1, 731),<br>                           (3, 1, 732),<br>                           (3, 1, 733),<br>                           (3, 1, 734),<br>                           (3, 1, 735),<br>                           (3, 1, 736),<br>                           (3, 1, 737),<br>                           (3, 1, 738),<br>                           (3, 1, 739),<br>                           (3, 1, 740),<br>                           (3, 1, 741),<br>                           (3, 1, 742),<br>                           (3, 1, 743),<br>                           (3, 1, 744),<br>                           (3, 1, 745),<br>                           (3, 1, 746),<br>                           (3, 1, 747),<br>                           (3, 1, 748),<br>                           (3, 1, 749),<br>                           (3, 1, 750),<br>                           (3, 1, 751),<br>                           (3, 1, 752),<br>                           (3, 1, 753),<br>                           (3, 1, 754),<br>                           (3, 1, 755),<br>                           (3, 1, 756),<br>                           (3, 1, 757),<br>                           (3, 1, 758),<br>                           (3, 1, 759),<br>                           (3, 1, 760),<br>                           (3, 1, 761),<br>                           (3, 1, 762),<br>                           (3, 1, 763),<br>                           (3, 1, 764),<br>                           (3, 1, 765),<br>                           (3, 1, 766),<br>                           (3, 1, 767),<br>                           (3, 1, 768),<br>                           (3, 1, 769),<br>                           (3, 1, 770),<br>                           (3, 1, 771),<br>                           (3, 1, 772),<br>                           (3, 1, 773),<br>                           (3, 1, 774),<br>                           (3, 1, 775),<br>                           (3, 1, 776),<br>                           (3, 1, 777),<br>                           (3, 1, 778),<br>                           (3, 1, 779),<br>                           (3, 1, 780),<br>                           (3, 1, 781),<br>                           (3, 1, 782),<br>                           (3, 1, 783),<br>                           (3, 1, 784),<br>                           (3, 1, 785),<br>                           (3, 1, 786),<br>                           (3, 1, 787),<br>                           (3, 1, 788),<br>                           (3, 1, 789),<br>                           (3, 1, 790),<br>                           (3, 1, 791),<br>                           (3, 1, 792),<br>                           (3, 1, 793),<br>                           (3, 1, 794),<br>                           (3, 1, 795),<br>                           (3, 1, 796),<br>                           (3, 1, 797),<br>                           (3, 1, 798),<br>                           (3, 1, 799),<br>                           (3, 1, 800),<br>                           (3, 1, 801),<br>                           (3, 1, 802),<br>                           (3, 1, 803),<br>                           (3, 1, 804),<br>                           (3, 1, 805),<br>                           (3, 1, 806),<br>                           (3, 1, 807),<br>                           (3, 1, 808),<br>                           (3, 1, 809),<br>                           (3, 1, 810),<br>                           (3, 1, 811),<br>                           (3, 1, 812),<br>                           (3, 1, 813),<br>                           (3, 1, 814),<br>                           (3, 1, 815),<br>                           (3, 1, 816),<br>                           (3, 1, 817),<br>                           (3, 1, 818),<br>                           (3, 1, 819),<br>                           (3, 1, 820),<br>                           (3, 1, 821),<br>                           (3, 1, 822),<br>                           (3, 1, 823),<br>                           (3, 1, 824),<br>                           (3, 1, 825),<br>                           (3, 1, 826),<br>                           (3, 1, 827),<br>                           (3, 1, 828),<br>                           (3, 1, 829),<br>                           (3, 1, 830),<br>                           (3, 1, 831),<br>                           (3, 1, 832),<br>                           (3, 1, 833),<br>                           (3, 1, 834),<br>                           (3, 1, 835),<br>                           (3, 1, 836),<br>                           (3, 1, 837),<br>                           (3, 1, 838),<br>                           (3, 1, 839),<br>                           (3, 1, 840),<br>                           (3, 1, 841),<br>                           (3, 1, 842),<br>                           (3, 1, 843),<br>                           (3, 1, 844),<br>                           (3, 1, 845),<br>                           (3, 1, 846),<br>                           (3, 1, 847),<br>                           (3, 1, 848),<br>                           (3, 1, 849),<br>                           (3, 1, 850),<br>                           (3, 1, 851),<br>                           (3, 1, 852),<br>                           (3, 1, 853),<br>                           (3, 1, 854),<br>                           (3, 1, 855),<br>                           (3, 1, 856),<br>                           (3, 1, 857),<br>                           (3, 1, 858),<br>                           (3, 1, 859),<br>                           (3, 1, 860),<br>                           (3, 1, 861),<br>                           (3, 1, 862),<br>                           (3, 1, 863),<br>                           (3, 1, 864),<br>                           (3, 1, 865),<br>                           (3, 1, 866),<br>                           (3, 1, 867),<br>                           (3, 1, 868),<br>                           (3, 1, 869),<br>                           (3, 1, 870),<br>                           (3, 1, 871),<br>                           (3, 1, 872),<br>                           (3, 1, 873),<br>                           (3, 1, 874),<br>                           (3, 1, 875),<br>                           (3, 1, 876),<br>                           (3, 1, 877),<br>                            (3, 1, 878),<br>                           (3, 1, 879),<br>                           (3, 1, 880),<br>                           (3, 1, 881),<br>                           (3, 1, 882),<br>                           (3, 1, 883),<br>                           (3, 1, 884),<br>                           (3, 1, 885),<br>                           (3, 1, 886),<br>                           (3, 1, 887),<br>                           (3, 1, 888),<br>                           (3, 1, 889),<br>                           (3, 1, 890),<br>                           (3, 1, 891),<br>                           (3, 1, 892),<br>                           (3, 1, 893),<br>                           (3, 1, 894),<br>                           (3, 1, 895),<br>                           (3, 1, 896),<br>                           (3, 1, 897),<br>                           (3, 1, 898),<br>                           (3, 1, 899),<br>                           (3, 1, 900),<br>                           (3, 1, 901),<br>                           (3, 1, 902),<br>                           (3, 1, 903),<br>                           (3, 1, 904),<br>                           (3, 1, 905),<br>                           (3, 1, 906),<br>                           (3, 1, 907),<br>                           (3, 1, 908),<br>                           (3, 1, 909),<br>                           (3, 1, 910),<br>                           (3, 1, 911),<br>                           (3, 1, 912),<br>                           (3, 1, 913),<br>                           (3, 1, 914),<br>                           (3, 1, 915),<br>                           (3, 1, 916),<br>                           (3, 1, 917),<br>                           (3, 1, 918),<br>                           (3, 1, 919),<br>                           (3, 1, 920),<br>                           (3, 1, 921),<br>                           (3, 1, 922),<br>                           (3, 1, 923),<br>                           (3, 1, 924),<br>                           (3, 1, 925),<br>                           (3, 1, 926),<br>                           (3, 1, 927),<br>                           (3, 1, 928),<br>                           (3, 1, 929),<br>                           (3, 1, 930),<br>                           (3, 1, 931),<br>                           (3, 1, 932),<br>                           (3, 1, 933),<br>                           (3, 1, 934),<br>                           (3, 1, 935),<br>                           (3, 1, 936),<br>                           (3, 1, 937),<br>                           (3, 1, 938),<br>                           (3, 1, 939),<br>                           (3, 1, 940),<br>                           (3, 1, 941),<br>                           (3, 1, 942),<br>                           (3, 1, 943),<br>                           (3, 1, 944),<br>                           (3, 1, 945),<br>                           (3, 1, 946),<br>                           (3, 1, 947),<br>                           (3, 1, 948),<br>                           (3, 1, 949),<br>                           (3, 1, 950),<br>                           (3, 1, 951),<br>                           (3, 1, 952),<br>                           (3, 1, 953),<br>                           (3, 1, 954),<br>                           (3, 1, 955),<br>                           (3, 1, 956),<br>                           (3, 1, 957),<br>                           (3, 1, 958),<br>                           (3, 1, 959),<br>                           (3, 1, 960),<br>                           (3, 1, 961),<br>                           (3, 1, 962),<br>                           (3, 1, 963),<br>                           (3, 1, 964),<br>                           (3, 1, 965),<br>                           (3, 1, 966),<br>                           (3, 1, 967),<br>                           (3, 1, 968),<br>                           (3, 1, 969),<br>                           (3, 1, 970),<br>                           (3, 1, 971),<br>                           (3, 1, 972),<br>                           (3, 1, 973),<br>                           (3, 1, 974),<br>                           (3, 1, 975),<br>                           (3, 1, 976),<br>                           (3, 1, 977),<br>                           (3, 1, 978),<br>                           (3, 1, 979),<br>                           (3, 1, 980),<br>                           (3, 1, 981),<br>                           (3, 1, 982),<br>                           (3, 1, 983),<br>                           (3, 1, 984),<br>                           (3, 1, 985),<br>                           (3, 1, 986),<br>                           (3, 1, 987),<br>                           (3, 1, 988),<br>                           (3, 1, 989),<br>                           (3, 1, 990),<br>                           (3, 1, 991),<br>                           (3, 1, 992),<br>                           (3, 1, 993),<br>                           (3, 1, 994),<br>                           (3, 1, 995),<br>                           (3, 1, 996),<br>                           (3, 1, 997),<br>                           (3, 1, 998),<br>                           (3, 1, 999)<br>              )<br>DELETE FROM<br>       customer<br>WHERE<br>       (c_w_id, c_d_id, c_id) IN (SELECT * FROM cte0);</pre><p>Since the keys are unique per row, using a sing scalar delete will not work. Using the inline table by unloading an array of values makes this faster delete. The same approach can be used for updates.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c9f371992dc7" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Reads During Network Partitions]]></title>
            <link>https://andrewdeally.medium.com/reads-during-network-partitions-709ce6c186a3?source=rss-5212e7f73704------2</link>
            <guid isPermaLink="false">https://medium.com/p/709ce6c186a3</guid>
            <dc:creator><![CDATA[Andrew Deally]]></dc:creator>
            <pubDate>Mon, 01 May 2023 03:22:06 GMT</pubDate>
            <atom:updated>2023-05-01T03:22:06.368Z</atom:updated>
            <content:encoded><![CDATA[<p><strong>Cockroach introduced </strong><a href="https://github.com/cockroachdb/cockroach/blob/master/docs/RFCS/20180603_follower_reads.md"><strong>Follower reads in 2018 </strong></a><strong>and </strong><a href="https://github.com/cockroachdb/cockroach/blob/master/docs/RFCS/20210519_bounded_staleness_reads.md"><strong>Bounded staleness in 2020</strong></a><strong> which service reads from a local or follower replicas. A great use case for this is allowing point reads to a node which is isolated from the cluster during a network partition. This blog will illustrate how this works. Using a 6 node cluster with docker, we have 3 regions setup with a network bridge between each region. The following config.yaml is used to build out the cluster and we have one table which spans leader and follower ranges across the cluster.</strong></p><blockquote>networks:</blockquote><blockquote>cockroachdb-training-shared:</blockquote><blockquote>name: cockroachdb-training-shared</blockquote><blockquote>driver: bridge</blockquote><blockquote>cockroachdb-training-dc0:</blockquote><blockquote>name: cockroachdb-training-dc0</blockquote><blockquote>driver: bridge</blockquote><blockquote>cockroachdb-training-dc1:</blockquote><blockquote>name: cockroachdb-training-dc1</blockquote><blockquote>driver: bridge</blockquote><blockquote>cockroachdb-training-dc2:</blockquote><blockquote>name: cockroachdb-training-dc2</blockquote><blockquote>driver: bridge</blockquote><blockquote>services:</blockquote><blockquote># DC 0 nodes</blockquote><blockquote>roach-0:</blockquote><blockquote>container_name: roach-0</blockquote><blockquote>hostname: roach-0</blockquote><blockquote>image: cockroachdb/cockroach:${COCKROACH_VERSION:-v22.2.8}</blockquote><blockquote>networks:</blockquote><blockquote>- cockroachdb-training-shared</blockquote><blockquote>- cockroachdb-training-dc0</blockquote><blockquote>command: start — logtostderr — insecure — locality=datacenter=dc-0 — join=roach-0,roach-1,roach-2</blockquote><blockquote>ports:</blockquote><blockquote>- 8080:8080</blockquote><blockquote>- 26257:26257</blockquote><blockquote>roach-1:</blockquote><blockquote>container_name: roach-1</blockquote><blockquote>hostname: roach-1</blockquote><blockquote>image: cockroachdb/cockroach:${COCKROACH_VERSION:-v22.2.8}</blockquote><blockquote>networks:</blockquote><blockquote>- cockroachdb-training-shared</blockquote><blockquote>- cockroachdb-training-dc0</blockquote><blockquote>command: start — logtostderr — insecure — locality=datacenter=dc-0 — join=roach-0,roach-1,roach-2</blockquote><blockquote>ports:</blockquote><blockquote>- 8081:8080</blockquote><blockquote>- 26258:26257</blockquote><blockquote># DC 1 nodes</blockquote><blockquote>roach-2:</blockquote><blockquote>container_name: roach-2</blockquote><blockquote>hostname: roach-2</blockquote><blockquote>image: cockroachdb/cockroach:${COCKROACH_VERSION:-v22.2.8}</blockquote><blockquote>networks:</blockquote><blockquote>- cockroachdb-training-shared</blockquote><blockquote>- cockroachdb-training-dc1</blockquote><blockquote>command: start — logtostderr — insecure — locality=datacenter=dc-1 — join=roach-0,roach-1,roach-2</blockquote><blockquote>ports:</blockquote><blockquote>- 8082:8080</blockquote><blockquote>- 26259:26257</blockquote><blockquote>roach-3:</blockquote><blockquote>container_name: roach-3</blockquote><blockquote>hostname: roach-3</blockquote><blockquote>image: cockroachdb/cockroach:${COCKROACH_VERSION:-v22.2.8}</blockquote><blockquote>networks:</blockquote><blockquote>- cockroachdb-training-shared</blockquote><blockquote>- cockroachdb-training-dc1</blockquote><blockquote>command: start — logtostderr — insecure — locality=datacenter=dc-1 — join=roach-0,roach-1,roach-2</blockquote><blockquote>ports:</blockquote><blockquote>- 8083:8080</blockquote><blockquote>- 26260:26257</blockquote><blockquote># DC 2 nodes</blockquote><blockquote>roach-4:</blockquote><blockquote>container_name: roach-4</blockquote><blockquote>hostname: roach-4</blockquote><blockquote>image: cockroachdb/cockroach:${COCKROACH_VERSION:-v22.2.8}</blockquote><blockquote>networks:</blockquote><blockquote>- cockroachdb-training-shared</blockquote><blockquote>- cockroachdb-training-dc2</blockquote><blockquote>command: start — logtostderr — insecure — locality=datacenter=dc-2 — join=roach-0,roach-1,roach-2</blockquote><blockquote>ports:</blockquote><blockquote>- 8084:8080</blockquote><blockquote>- 26261:26257</blockquote><blockquote>roach-5:</blockquote><blockquote>container_name: roach-5</blockquote><blockquote>hostname: roach-5</blockquote><blockquote>image: cockroachdb/cockroach:${COCKROACH_VERSION:-v22.2.8}</blockquote><blockquote>networks:</blockquote><blockquote>- cockroachdb-training-shared</blockquote><blockquote>- cockroachdb-training-dc2</blockquote><blockquote>command: start — logtostderr — insecure — locality=datacenter=dc-2 — join=roach-0,roach-1,roach-2</blockquote><blockquote>ports:</blockquote><blockquote>- 8085:8080</blockquote><blockquote>- 26262:26257</blockquote><p><strong>Starting up the Cluster with</strong></p><blockquote>docker-compose up</blockquote><blockquote>Starting roach-3 … done</blockquote><blockquote>Starting roach-5 … done</blockquote><blockquote>Starting roach-2 … done</blockquote><blockquote>Starting roach-0 … done</blockquote><blockquote>Starting roach-1 … done</blockquote><blockquote>Starting roach-4 … done</blockquote><p><strong>Establishes a 6 node cluster across 3 datacenters with a ycsb database loaded up.</strong></p><p><strong>Isolate a node with the following command we can see node 4 is suspect and soon to be dead.</strong></p><p>docker network disconnect cockroachdb-training-shared roach-4</p><p><strong>While connected to node 4 SQL client before the network partition, I am able to run follower reads by specifying the maximum tolerated stalness for single key lookups only.</strong></p><blockquote>docker exec -it roach-4 /cockroach/cockroach sql — insecure</blockquote><blockquote>root@localhost:26257/ycsb&gt; select ycsb_key from usertable AS OF SYSTEM TIME with_max_staleness(‘10s’) where ycsb_key in (‘user9999414122401537262’);</blockquote><blockquote>ycsb_key</blockquote><blockquote>— — — — — — — — — — — — — -</blockquote><blockquote>user9999414122401537262</blockquote><blockquote>(1 row)</blockquote><blockquote>Time: 2ms total (execution 1ms / network 0ms)</blockquote><blockquote>root@localhost:26257/ycsb&gt; select ycsb_key from usertable AS OF SYSTEM TIME with_max_staleness(‘10s’) where ycsb_key in (‘user9999414122401537262’);</blockquote><blockquote>ycsb_key</blockquote><blockquote>— — — — — — — — — — — — — -</blockquote><blockquote>user9999414122401537262</blockquote><blockquote>(1 row)</blockquote><blockquote>Time: 2ms total (execution 1ms / network 0ms)</blockquote><blockquote>root@localhost:26257/ycsb&gt; select ycsb_key from usertable AS OF SYSTEM TIME with_max_staleness(‘10s’) where ycsb_key in (‘user5566506082736452842’);</blockquote><blockquote>ycsb_key</blockquote><blockquote>— — — — — — — — — — — — — -</blockquote><blockquote>user5566506082736452842</blockquote><blockquote>(1 row)</blockquote><blockquote>Time: 2ms total (execution 2ms / network 0ms)</blockquote><p><strong>There are limitations which have to followed as per the </strong><a href="https://github.com/cockroachdb/cockroach/issues/67562?version=v22.2?version=v22.2"><strong>issue</strong></a><strong>, and it appears single key reads are the only implemented stage. This is a good start to enhance reads to CRDB during network partitions.</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=709ce6c186a3" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Tenant Isolation with CockroachDB: Part 2]]></title>
            <link>https://andrewdeally.medium.com/tenant-isolation-with-cockroachdb-97be8b7981a?source=rss-5212e7f73704------2</link>
            <guid isPermaLink="false">https://medium.com/p/97be8b7981a</guid>
            <dc:creator><![CDATA[Andrew Deally]]></dc:creator>
            <pubDate>Mon, 20 Mar 2023 15:40:46 GMT</pubDate>
            <atom:updated>2023-03-20T17:02:34.126Z</atom:updated>
            <content:encoded><![CDATA[<p>Tenant node Isolation through database partition and sharding</p><p>This blog illustrates a method of tenant isolation using table partitioning and sharding to restrict data to a specific node or set of nodes. In a prior<a href="https://andrewdeally.medium.com/tenant-isolation-with-cockroachdb-85303250ed72"> blog,</a> I covered a similar concept across regions and now we will peel back to a single region cluster. The result is better resource isolation of a tenant or group of tenants by placing all their data on a set of nodes separate from other tenants. Also the SQL connection to the cluster is controlled by setting the specific nodes for the Tenants data. Using YCSB workload to generate the Tenant data and access. Refer to <a href="https://andrewdeally.medium.com/using-ycsb-to-simulate-multi-regional-workloads-with-cockroachdb-19c50a84e094">YCSB on CRDB</a> as a primer.</p><p><strong>Cluster Setup</strong></p><p>A Cockroach Cluster is setup with 9 nodes across 3 availability zones in us-east1 b,c,d</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*GfxQC5ZaYhO_7Bml" /></figure><p>Within each AZ, we have 3 nodes with one assigned to a Tenant</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1019/0*_imVe2_5r2ht-mcT" /></figure><p>Notice the Tenant Labels are A,B and C where each Tenant has a node in each availability zone to ensure high availability for each Tenant.</p><p><strong>Table Setup</strong></p><p>Now let’s look at the table structure, noting the partitioning of the ycsb_key based on range values.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*9Fuw4U7jOiNQwLPe" /></figure><p>Along with the zone configurations settings, ranges of ycsb_key are mapped to partitions and partitions are mapped to specific nodes based on the node locality setting +tenant=A, +tenant=B or +tenant=C.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*9fROwSw52HRLewPE" /></figure><p>All the keys starting from user1% to user4%, are mapped to Nodes with locality Tenant=A, user5% to user6% are mapped to Nodes with locality Tenant=B and the user7% to user99999 are mapped to nodes with locality Tenant=C. By mapping users to ycsb_keys, we can manage groups of users and queries to the nodes they are assigned to by Tenancy. Connecting a user to anynode in the cluster, their requests will be routed to the proper tenant node to read or write data. Also, restricting user access by setting connection strings to the tenant nodes, will further control the resource usage. You can also isolate a specific user or yscb_key to a dedicated partition.</p><p><strong>Tenant usage and workload isolation</strong></p><p>Using a YCSB binary, we can specify a range of user keys to simulate read and write access to a specific range of keys to further illustrate workload isolation. For example the following command,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*ktXIrEiKEtntobwz" /></figure><p>notice the connection string points to nodes 1,5 and 9 [cluster nodes 1,4 and 7 ] which are TenantA where user1% rows are stored. Using “-p insertstart=100000” and “-p insertcount=99999”specifies the starting key user1 through the next 99999 sequential keys in the keyspace. We can see the workload reaches roughly 2500 reads and 2500 writes to keys across the user1% tenant and the SQL latency is only on db nodes 1,4 and 7.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*JGYhN7QTzuRWI8nw" /><figcaption>Node Latency N1, N4 and N7</figcaption></figure><p>We can also see nodes 1,4 and 7 display resource utilization of CPU.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*QH4fk9PVY9gNZNP1" /><figcaption>CPU Utilization % N1, N4 and Now N7</figcaption></figure><p>and write IOPS with little to no impact to other nodes in the cluster.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*UPE2T8Ex6YtnGRiB" /><figcaption>N1,N4 and N7 Write IOPS</figcaption></figure><p>Finally, the “Average Queries per Node” metric which indicates lease holder utilization indicates the workload is serviced by DB nodes 1,4 and 7 in the Cluster.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*iDPT9BjYnhZxA1BF" /><figcaption>Average Queires per Node indicated lease/store usage</figcaption></figure><p>Now running users in Tenant=B with keys user5 which is mapped to nodes, 2,5, 8 — we can see that utilization is isolated to those nodes.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*XZHpND4lB6XjDzwY" /><figcaption>Latency in now on N2,N5 and N8 for Tenant B</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*VaBUo21ndJ_bLJEt" /><figcaption>CPU % on Nodes for Tenant B workloads</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*AvsaDL58OhO53p04" /></figure><p>Wrapping up, we can see that with partitioning and zone configurations we can isolate reads and writes for a range(s) to specific nodes controlling resources to specific users or tenants.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=97be8b7981a" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Using AWR metrics for Workload Classification]]></title>
            <link>https://andrewdeally.medium.com/using-awr-metrics-for-workload-classification-ab04d8d431cb?source=rss-5212e7f73704------2</link>
            <guid isPermaLink="false">https://medium.com/p/ab04d8d431cb</guid>
            <dc:creator><![CDATA[Andrew Deally]]></dc:creator>
            <pubDate>Tue, 31 Jan 2023 14:10:47 GMT</pubDate>
            <atom:updated>2023-01-31T14:10:47.823Z</atom:updated>
            <content:encoded><![CDATA[<p>Part 1</p><p>The purpose of this blog is to help customers and colleagues identify candidate Oracle workloads which are a fit for CockroachDB. This can be achieved by reviewing a few statistics captured in an Oracle Automatic Workload Repository report (<a href="https://docs.oracle.com/database/121/RACAD/GUID-C3CD2DCE-38BD-46BA-BC32-7A28CAC9A7FD.htm#RACAD951">AWR</a>). Start off by collecting an AWR following the guidelines below:</p><ul><li><strong>Collect reports over one snapshot internal</strong></li><li><strong>Snapshot intervals should be no longer than 60 minutes, ideally in 10 to 15 minutes intervals</strong></li><li><strong>Collect a report per snapshot over a given period of time which represents the workload such as a peak window</strong></li><li><strong>Collect the reports in both html and txt formats</strong></li><li><strong>Collect a report per RAC instance or collect a RAC report</strong></li></ul><p>This is a <a href="https://github.com/flashdba/scripts/blob/master/awr-generator.sql">great script</a> for running across snapshots which can produce both html and txt formats. Once you have collected the reports focus on the following sections of the report to understand the workload type. Specifically looking for patterns which match OLTP workloads such as patterns which match</p><ul><li><strong>Singleton lookups and DMLs which would include short ranges queries and transactions</strong></li><li><strong>Contention events</strong></li><li><strong>Read and write sizes . logical versus physical reads</strong></li><li><strong>Types of data scans</strong></li><li><strong>Wait events</strong></li></ul><p>While these may not be all the metrics required for workload tuning of Oracle, it is sufficient to qualify the workload for a fit for CockroachDB. Before digging into the above metrics in the AWR let’s take a refresh on CockroachDB workload capabilities. While CockroachDB has a very sophisticated distributed query processing engine, Cost Based Optimizer and Vectorized executions to improve scans for larger rows sets, joins and aggregations it is still limited to how well or fast it can scan larger data sets such as Full segment or Full table scans. Scans are supported but not comparable to performance of parallel scans across partitions, intra-partition parallel scans or Columnar execution such aggregate join index offloading. Here is the Cockroach FAQ,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*8C7gPPfvDUsuaPTH" /></figure><p>So you want to be sure workloads are a fit leading to success with the product. Again Cockroach is a great fit for OLTP workloads and OLTP workloads usually display more logical reads, few physical reads, and many user calls, parses, and executes, as well as rollbacks and transactions. While reporting/OLAP environments have fewer, longer transactions that utilize the Work Area, while OLTP environments tend to have numerous small transactions with many commits and rollbacks. Now let’s tackle the AWR metrics to identify the workload pattern. Let’s take another detour and cover some AWR basics. The AWR report provides detailed statistics on wait events, locks, resources usage such as CPU, memory and IO across a given period of time. Collecting a snapshot report over a time period represents a time view of all activity in Oracle. What we care about is the time spent on IO and the types of IO which will give us a close approximation of the type of workload. Under the wait events section, you can observe time spent on the types of IO waits and we care about db file sequential and scattered reads which translates to index scans or full table scans. If index scans are a higher ratio than full scans, the system is more than likely processing small transactions. Let’s look at an example,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*z-Lse4SI41mY-D4R" /></figure><p>Notice User I/O accounts for roughly 40% of all calls across the reports snapshot time and a majority of the file is db file sequential reads which indicates small reads. Next, confirm the assumption by checking the IOstats and SQL stats to confirm the observation. Under the heading “Segment Statisitcs”</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*TYaXTl0tuTyzVJwc" /></figure><p>You can look at the breakdown IO statistics across object type which can help you confirm the smaller reads are across indexes rather than tables or control files. Next check the IO type types across physical files by checking “IOSTAT by Function and File Type”, to confirm IO’s are going to DATA volumes and the are Buffer gets. The below table indicates, most IO reads are to data files we do however see write IO to TEMP which indicates sorting of data or temp used for SQL refactoring so we will need to check how my SQL statements may be analytical.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*i6E8fNeGYstCC3yp" /></figure><p>After reviewing the above metrics, Focus on the “SQL Statistics” section,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/744/0*Wb0olLB-2X92JOhx" /></figure><p>Focusing on TOP sql executions, which will have a hyperlink to the SQL ID sections which includes the complete SQL text</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/638/0*b3diV8wnI5Xo0yEI" /></figure><p>From here, SQL statements and SQL stats you can derive whether the SQL pattern is OLTP or OLAP and confirm early observations based on wait events. Up next I will review in detail and example AWR workload..</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ab04d8d431cb" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Tracing Application Interactions with CockroachDB]]></title>
            <link>https://andrewdeally.medium.com/tracing-application-interactions-with-cockroachdb-faa0ee56ce80?source=rss-5212e7f73704------2</link>
            <guid isPermaLink="false">https://medium.com/p/faa0ee56ce80</guid>
            <dc:creator><![CDATA[Andrew Deally]]></dc:creator>
            <pubDate>Thu, 19 Jan 2023 05:57:16 GMT</pubDate>
            <atom:updated>2023-01-19T05:57:16.069Z</atom:updated>
            <content:encoded><![CDATA[<p>Part 2 — Node CPU Profiles</p><p>Continuing from my blog <a href="https://medium.com/@andrewdeally/tracing-application-interactions-with-cockroachdb-a2d3c77856ad">“Tracing application Interactions with CockroachDB”,</a> Next, I would like to dive into the cpu profiles and give some examples of how to collect them and to inspect the profiles offline and online. CockroachDB is <a href="https://www.cockroachlabs.com/blog/why-go-was-the-right-choice-for-cockroachdb/">written in </a>go, which has a built-in profiling tool which can be used to examine and understand hot spots in the Cockroach runtime. You can examine profiles for CPU, Heap, Mutex, Block, Goroutines and Jemalloc.</p><p>For this blog, I will focus on <strong>CPU profiles </strong>which are accessible from the CockroachDB Console ../#/debug endpoint at debug/pprof/ui/cpu/[*anynode*]/flamegraph?node=1&amp;seconds=5&amp;labels=true. CPU profiles display the functions which consume the most CPU time. CPU samples over a time interval, the callstack of the cockroach running program.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*q6kU_LN4xi_aH_sS" /></figure><p>By clicking on the link above, a 5 second <strong>CPU profile</strong> will download to your laptop or open for interactive use. You can increase the duration and access a flamegraph from the ui directly.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Y90S_nxF56fz2zjH" /></figure><p>There are various drop downs which can be used to view the following modes</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/652/0*62vQTjtHk7UUOoJb" /></figure><p>Alternatively, you can save these profiles for future investigation offline. The following commands can be used for saving and viewing the profiles using<strong><em> go tool pprof</em></strong>.</p><p>Save the profile</p><p><strong><em>curl -kL “http://127.0.0.1:26258/debug/pprof/profile?seconds=5&quot; — output pprof.node1</em></strong></p><p><strong><em>go tool pprof -http=”:8081&quot; pprof.node1</em></strong></p><p>Will launch a browser to the profile <a href="http://localhost:8081/">http://localhost:8081</a> for the same interactive experience as in the DBConsole mode. You can also launch interactive command line</p><p><strong><em>go tool pprof pprof.node1</em></strong></p><p>And use any subcommands such as top and peak to view the top cpu consumers for the pprof sample. For example,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*RMr_7zrj8FyogVI1" /></figure><p>Shows the breakdown of calls sorted by flat column indicating time spent and then we can inspect a specific call with peek on encodeBlock which is the highest time consumer</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*FPGbfXCmEno6I91q" /></figure><p>And I can see what the system is doing for a period of time for this sample which is compression, data encoding and writing to SST tables. And further to addPoint</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*hVxWiJCRxqzI3ptq" /></figure><p>If i wanted to I can go back to source code to view what the call is doing or have the source installed locally to inspect it. We can also look back at the local web display</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*8v9VU-5jWCzJjP6d" /></figure><p>So that wraps up this short tour on CPU profiles which can be helpful when trying to understand how cockroachDB runtime consumes CPU for a node.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=faa0ee56ce80" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>