Cockroach DB On: Volume 10 – Dizone Database

If your local environment launches an app other than Postico, you can also change this postgresql in pgurl with postico, For example:

The following also works. Serverless now supports SNI and Postico supports it.

open "postgresql://artem:password@artem-serverless-2077.g8z.gcp-us-east1.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full&options=--cluster%3Dartem-serverless-2077"

As soon as we enable SNI in our serverless product, we will publish an SNI-compatible connection string which should alleviate a long-running issue with routing cluster IDs.

Topic 4: The Specifics With Discard All

I was working with a .Net application and a client workload using the Npgsql PostgreSQL provider. When I work with customer workloads, I usually treat the code as a black box. I try to make minimal changes to the app code unless absolutely necessary. In the present case, I have seen a query like DISCARD ALL Aggressively being called in as part of the workload. I was looking at least 3x the number DISCARD ALL Questions for each business-critical statement. This prompted me to inquire internally whether these questions were worth it to them. To my surprise, the cost is negligible though. There is also an additional network latency cost between the client and the server. The average query statement time may be smaller, but can contribute significantly to overall performance overall.

Fortunately, in researching the issue, my search landed me on the following Npgsql issue, which led me to the following property in Npgsql: No Reset On Close=true, It states that it can improve performance in some cases. I should emphasize that there is a trade-off: some applications rely on a clean session state. In those cases, deleting DISCARD ALL Will likely break the application. But for all other cases, using a connection parameter makes for a simpler solution; meaning, Server=cockroach-cluster-host;Port=26257;Database=dbname;User ID=dbuser;Password=password;No Reset On Close=true, I re-run the workload and no longer see DISCARD ALL Question.

When I inquired about internally DISCARD ALLI was pointed to another customer case where DISCARD ALL was affecting performance. In the latter case, the customer was using PGB Bouncer and from my work on PGB Bouncer, I missed a property server_reset_query which can be set in pgbouncer.ini To reset the session when the connection is released. default property is DISCARD ALL, In some cases, to replace the property DEALLOCATE ALL, ie, “server_reset_query=DEALLOCATE ALL;“Leaving only prepared statements can be more efficient.

i should mention it server_reset_query should be used with pool_mode=session, transaction The mode does not use session-based features. Each transaction ends in a different connection with a different session state. Use extreme caution when changing these parameters as they can significantly affect workload behavior.

Topic 5: Npgsql and follower reads

Since we are on the topic of Npgsql, let me discuss other interesting anecdotes of third party tools working with Cockroach DB. CockroachDB supports follower reads that operate on local replicas, leading to faster read latency and higher throughput. The trade-off for the follower read is increased data speed. Followers are consistent with reads READ COMMITTED Isolation levels in the relational database world.

We’ve supported Npgsql for some time but unfortunately, CockroachDB-specific concepts such as follower reads are not well documented in the Npgsql docs. I am grateful to my engineering team for introducing a follower reads session parameter default_transaction_use_follower_reads=on; Which makes it easy to integrate our exclusive features into third-party tools. We made a decision several releases ago because it’s easier to add session parameters than to force all third-party tools to adopt our syntax. Additionally, with the session parameter, we can force queries on the read path to indirectly leverage followers’ reads.

This brings us back to the original topic: I was working with black boxes. Rewriting the application with ASP.NET and AOST was a non-starter. I set out to use the session parameter, but it wasn’t clear from the Npgsql docs how to take advantage of this. Postgresql a . supports -c name=valueThe -name run-time parameter, and I was sure that Npgsql accepts arbitrary options like this.

Considering the sample code below, I will demonstrate how to use follower read in your applications as well as adopting the practices of splitting the traffic between read and write/read.

using System;
using System.Data;
using System.Net.Security;
using Npgsql;

namespace Cockroach
{
  class MainClass
  {
    static void Main(string[] args)
    {
      var connString = "Host=artem-mr-7xw.aws-us-east-1.cockroachlabs.cloud;Username=artem;Passfile=/Users/artem/.pgpass;Database=dotnet;RootCertificate=/Users/artem/Library/CockroachCloud/certs/artem-mr-ca.crt;Port=26257;SslMode=VerifyCA";

      Simple(connString);
    }

    static void Simple(string connString)
    {
      using (var conn = new NpgsqlConnection(connString))
      {
        conn.Open();

        new NpgsqlCommand("CREATE TABLE IF NOT EXISTS test (id UUID DEFAULT gen_random_uuid() PRIMARY KEY, val STRING)", conn).ExecuteNonQuery();

        using (var cmd = new NpgsqlCommand())
        {
          cmd.Connection = conn;
          cmd.CommandText = "UPSERT INTO test(val) VALUES(@val1), (@val2)";
          cmd.Parameters.AddWithValue("val1", Guid.NewGuid().ToString("n").Substring(0, 10));
          cmd.Parameters.AddWithValue("val2", Guid.NewGuid().ToString("n").Substring(0, 10));
          cmd.ExecuteNonQuery();
        }

        System.Console.WriteLine("Results:");
        using (var cmd = new NpgsqlCommand("SELECT id, val FROM test", conn))
        using (var reader = cmd.ExecuteReader())
          while (reader.Read())
            Console.Write("\rrecord {0}: {1}\n", reader.GetValue(0), reader.GetValue(1));
      }
    }
  }
}

We can split application code into two paths: one for read/write transactions and one for read-only. This is a great way to isolate traffic to reduce contention. Here is the code to do this:

using System;
using System.Data;
using System.Net.Security;
using Npgsql;

namespace Cockroach
{
  class MainClass
  {
    static void Main(string[] args)
    {
      var connReadWrite = "Host=artem-mr-7xw.aws-us-east-1.cockroachlabs.cloud;Username=artem;Passfile=/Users/artem/.pgpass;Database=dotnet;RootCertificate=/Users/artem/Library/CockroachCloud/certs/artem-mr-ca.crt;Port=26257;SslMode=VerifyCA";

      var connReadOnly = "Host=artem-mr-7xw.aws-us-east-1.cockroachlabs.cloud;Username=artem;Passfile=/Users/artem/.pgpass;Database=dotnet;RootCertificate=/Users/artem/Library/CockroachCloud/certs/artem-mr-ca.crt;Port=26257;SslMode=VerifyCA;Options=-c default_transaction_use_follower_reads=on;";

      ReadWrite(connReadWrite);
      ReadOnly(connReadOnly);
    }

    static void ReadWrite(string connReadWrite)
    {
      using (var conn = new NpgsqlConnection(connReadWrite))
      {
        conn.Open();

        new NpgsqlCommand("CREATE TABLE IF NOT EXISTS test (id UUID DEFAULT gen_random_uuid() PRIMARY KEY, val STRING)", conn).ExecuteNonQuery();

        using (var cmd = new NpgsqlCommand())
        {
          cmd.Connection = conn;
          cmd.CommandText = "UPSERT INTO test(val) VALUES(@val1), (@val2)";
          cmd.Parameters.AddWithValue("val1", Guid.NewGuid().ToString("n").Substring(0, 10));
          cmd.Parameters.AddWithValue("val2", Guid.NewGuid().ToString("n").Substring(0, 10));
          cmd.ExecuteNonQuery();
        }
      }
    }

    static void ReadOnly(string connReadOnly)
    {
      using (var conn = new NpgsqlConnection(connReadOnly))
      {
        conn.Open();

        System.Console.WriteLine("Results:");
        using (var cmd = new NpgsqlCommand("SELECT id, val FROM test", conn))
        using (var reader = cmd.ExecuteReader())
          while (reader.Read())
            Console.Write("\rrecord {0}: {1}\n", reader.GetValue(0), reader.GetValue(1));
      }
    }
  }
}

pay attention connReadOnly Reads the session variable for the follower; meaning, Options=-c default_transaction_use_follower_reads=on;, all of SELECT Questions will now be routed through a follower-read connection. Read/write traffic will not work with this connection, as the follower only works with read-only transactions.

To confirm that we are indeed using follower reads, we need to capture and analyze a debug zip in CockroachDB trace.json file. Inspecting the file will give output like below:

{    
  "key": "event",
    "value": "‹kv/kvserver/pkg/kv/kvserver/replica_follower_read.go:104 [n3,s3,r1345/3:/{Table/282-Max}] serving via follower read; query timestamp below closed timestamp by 1.162361555s›"
}

range id is r1345, you can see it from n3,s3,r1345/3:/{Table/282-Max}], if we run SELECT range_id, lease_holder, replicas, replica_localities FROM [SHOW RANGES FROM TABLE test];We can see the following:

SELECT range_id, lease_holder, replicas, replica_localities FROM [SHOW RANGES FROM TABLE test];
  range_id | lease_holder | replicas |                                                                                                                                             replica_localities
-----------+--------------+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      1345 |            5 | {3,5,7}  | {"region=aws-us-east-1,az=aws-us-east-1b,dns=cockroachdb-1.cockroachdb.us-east-1.svc.cluster.local","region=aws-us-east-2,az=aws-us-east-2b,dns=cockroachdb-1.cockroachdb.us-east-2.svc.cluster.local","region=aws-us-west-2,az=aws-us-west-2a,dns=cockroachdb-0.cockroachdb.us-west-2.svc.cluster.local"}

Note that the range id matches, there are 3 replicas and they are located on nodes 3, 5 and 7, with node 5 hosting the lease_holder replica. Since the read comes from node 3, we read from the local replica, not the lease_holder.


Bonus Topics: Npgsql and pgpass

I decided to end this section with a bonus topic, as it deals with the above topics. Note the connection string in my C# code above:

var connString = "Host=artem-mr-7xw.aws-us-east-1.cockroachlabs.cloud;Username=artem;Passfile=/Users/artem/.pgpass;Database=dotnet;RootCertificate=/Users/artem/Library/CockroachCloud/certs/artem-mr-ca.crt;Port=26257;SslMode=VerifyCA";

In particular, note Passfile=/Users/artem/.pgpass; Part. I am glad to see that in Npgsql PGPASSFILE Variable is supported. I set up my pgpass file and pointed to my client app. Lo and behold, it works as expected. Feel free to visit articles 1-5 linked at the beginning of this article for more information on PGpass.

Leave a Comment