Optimize CockroachDB 24.3 performance with advanced tuning and configuration

Advanced 45 min Apr 13, 2026 227 views
Ubuntu 24.04 Debian 12 AlmaLinux 9 Rocky Linux 9

Master advanced CockroachDB 24.3 performance optimization through memory, CPU, storage, and query tuning. Configure cluster parameters, connection pooling, and indexing strategies for production-grade distributed SQL workloads.

Prerequisites

  • Existing CockroachDB 24.3 cluster
  • Root or sudo access
  • At least 8GB RAM per node
  • SSD storage recommended

What this solves

CockroachDB performance optimization requires careful tuning of memory allocation, CPU utilization, storage I/O, and query execution patterns. This tutorial covers advanced configuration techniques to maximize throughput, reduce latency, and ensure optimal resource utilization in production CockroachDB 24.3 clusters.

Prerequisites and system requirements

Verify CockroachDB installation

Confirm you have CockroachDB 24.3 installed and running. This tutorial builds on an existing cluster setup.

cockroach version
sudo systemctl status cockroach

Install performance monitoring tools

Install system monitoring utilities to measure performance improvements throughout the tuning process.

sudo apt update
sudo apt install -y htop iotop sysstat postgresql-client
sudo dnf install -y htop iotop sysstat postgresql

Memory and CPU optimization

Configure cluster cache settings

Adjust CockroachDB cache sizes to optimize memory utilization. The cache size should typically be 25-35% of available system RAM.

cockroach sql --insecure --execute="
SET CLUSTER SETTING sql.stats.automatic_collection.enabled = true;
SET CLUSTER SETTING sql.stats.histogram_collection.enabled = true;
SET CLUSTER SETTING kv.range_merge.queue_interval = '1m';
SET CLUSTER SETTING kv.snapshot_rebalance.max_rate = '32MiB';
"

Optimize memory pool allocation

Configure SQL memory pool and query execution memory limits to prevent out-of-memory conditions while maximizing performance.

cockroach sql --insecure --execute="
SET CLUSTER SETTING sql.distsql.max_running_flows = 500;
SET CLUSTER SETTING sql.conn.max_read_buffer_message_size = '16MiB';
SET CLUSTER SETTING sql.metrics.statement_details.threshold = '1ms';
SET CLUSTER SETTING sql.metrics.statement_details.plan_collection.enabled = true;
"

Configure CPU and goroutine settings

Adjust concurrent operations and goroutine limits based on available CPU cores. Set these values to 2-4x the number of CPU cores.

cockroach sql --insecure --execute="
SET CLUSTER SETTING kv.snapshot_recovery.max_rate = '64MiB';
SET CLUSTER SETTING kv.bulk_io_write.concurrent_export_requests = 8;
SET CLUSTER SETTING kv.bulk_io_write.concurrent_addsstable_requests = 8;
SET CLUSTER SETTING sql.distsql.distribute_index_joins = true;
"

Update systemd service configuration

Modify the systemd service file to include optimized startup parameters for memory and CPU usage.

[Unit]
Description=Cockroach Database cluster node
Requires=network.target

[Service]
Type=notify
WorkingDirectory=/var/lib/cockroach
ExecStart=/usr/local/bin/cockroach start \
  --insecure \
  --store=path=/var/lib/cockroach \
  --listen-addr=0.0.0.0:26257 \
  --http-addr=0.0.0.0:8080 \
  --join=203.0.113.10:26257,203.0.113.11:26257,203.0.113.12:26257 \
  --cache=2GiB \
  --max-sql-memory=4GiB
TimeoutStopSec=60
Restart=always
RestartSec=10
StandardOutput=syslog
StandardError=syslog
SyslogIdentifier=cockroach
User=cockroach

[Install]
WantedBy=default.target

Reload systemd and restart CockroachDB

Apply the new configuration by reloading systemd and restarting the CockroachDB service.

sudo systemctl daemon-reload
sudo systemctl restart cockroach
sudo systemctl status cockroach

Storage and I/O performance tuning

Configure storage layer settings

Optimize RocksDB settings for better write performance and compaction behavior. These settings reduce write amplification and improve throughput.

cockroach sql --insecure --execute="
SET CLUSTER SETTING rocksdb.min_wal_sync_interval = '500us';
SET CLUSTER SETTING kv.range_split.by_load_threshold = 2500;
SET CLUSTER SETTING kv.range_split.load_qps_threshold = 2500;
SET CLUSTER SETTING storage.max_sync_duration = '20s';
"

Optimize write batch settings

Configure batch write operations and WAL sync intervals to balance durability with performance for your specific workload.

cockroach sql --insecure --execute="
SET CLUSTER SETTING kv.raft.command.max_size = '64MiB';
SET CLUSTER SETTING kv.transaction.write_pipelining_enabled = true;
SET CLUSTER SETTING kv.range_merge.queue_enabled = true;
SET CLUSTER SETTING storage.mvcc.range_tombstones.enabled = true;
"

Configure compaction and garbage collection

Tune RocksDB compaction and CockroachDB garbage collection settings to maintain optimal read performance over time.

cockroach sql --insecure --execute="
SET CLUSTER SETTING kv.gc.ttlseconds = 90000;
SET CLUSTER SETTING kv.closed_timestamp.target_duration = '3s';
SET CLUSTER SETTING kv.protectedts.poll_interval = '3m';
SET CLUSTER SETTING storage.mvcc.range_tombstones.enabled = true;
"

Optimize filesystem and mount options

Configure optimal mount options for CockroachDB data directories to improve I/O performance.

sudo umount /var/lib/cockroach
sudo mount -t ext4 -o noatime,data=writeback,barrier=0,nobh /dev/sdb1 /var/lib/cockroach
sudo chown -R cockroach:cockroach /var/lib/cockroach
Note: The data=writeback and barrier=0 options improve performance but reduce durability guarantees. Only use these in environments where you can tolerate some data loss in exchange for better performance.

Update fstab for persistent mount options

Make filesystem optimization permanent by updating the fstab configuration.

/dev/sdb1 /var/lib/cockroach ext4 noatime,data=writeback,barrier=0,nobh 0 2

Network and connection pool optimization

Configure connection pool settings

Optimize connection pooling parameters to handle high-concurrency workloads efficiently while preventing connection exhaustion.

cockroach sql --insecure --execute="
SET CLUSTER SETTING server.host_based_authentication.configuration = '
host all all all cert-password
';
SET CLUSTER SETTING sql.conn.max_read_buffer_message_size = '16MiB';
SET CLUSTER SETTING server.shutdown.drain_wait = '5m';
"

Implement connection pooling with PgBouncer

Set up PgBouncer as a connection pooler to reduce connection overhead and improve application scalability. For detailed setup, see our CockroachDB connection pooling guide.

sudo apt install -y pgbouncer
sudo dnf install -y pgbouncer

Configure PgBouncer for CockroachDB

Create an optimized PgBouncer configuration that works with CockroachDB's connection requirements.

[databases]
cockroachdb = host=127.0.0.1 port=26257 dbname=defaultdb

[pgbouncer]
listen_port = 5432
listen_addr = 127.0.0.1
auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt
logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 100
min_pool_size = 10
reserve_pool_size = 10
max_db_connections = 200
server_reset_query = DISCARD ALL
server_check_delay = 30

Optimize network buffer settings

Configure TCP buffer sizes and network timeouts for optimal cluster communication performance.

cockroach sql --insecure --execute="
SET CLUSTER SETTING cluster.preserve_downgrade_option = '';
SET CLUSTER SETTING kv.raft.election_timeout = '3s';
SET CLUSTER SETTING kv.raft.heartbeat_interval = '1s';
SET CLUSTER SETTING kv.lease_rebalancing_aggressiveness = 1.0;
"

Configure kernel network parameters

Optimize Linux kernel network settings to handle high-throughput database connections efficiently.

net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.ipv4.tcp_rmem = 4096 16384 16777216
net.ipv4.tcp_wmem = 4096 16384 16777216
net.core.netdev_max_backlog = 2500
net.ipv4.tcp_no_delay = 1
net.ipv4.tcp_low_latency = 1
net.ipv4.tcp_congestion_control = bbr

Apply kernel parameter changes

Load the new network configuration and verify the changes are applied correctly.

sudo sysctl --system
sudo sysctl net.ipv4.tcp_congestion_control
sudo sysctl net.core.rmem_max

Query optimization and indexing strategies

Enable advanced query optimization

Configure CockroachDB's cost-based optimizer and enable advanced query optimization features for better execution plans.

cockroach sql --insecure --execute="
SET CLUSTER SETTING sql.stats.automatic_collection.enabled = true;
SET CLUSTER SETTING sql.stats.histogram_collection.enabled = true;
SET CLUSTER SETTING sql.optimizer.use_histograms.enabled = true;
SET CLUSTER SETTING sql.optimizer.use_multicol_stats.enabled = true;
"

Configure query execution settings

Optimize distributed query execution and enable advanced join algorithms for complex queries.

cockroach sql --insecure --execute="
SET CLUSTER SETTING sql.distsql.distribute_index_joins = true;
SET CLUSTER SETTING sql.optimizer.locality_optimized_partitioned_index_scan.enabled = true;
SET CLUSTER SETTING sql.optimizer.partial_index.enabled = true;
SET CLUSTER SETTING sql.optimizer.foreign_key.enabled = true;
"

Create performance monitoring views

Set up monitoring queries to track slow queries and identify optimization opportunities.

cockroach sql --insecure --execute="
CREATE VIEW slow_queries AS
SELECT 
  aggregated_ts,
  fingerprint_id,
  app_name,
  metadata ->> 'query' as query_text,
  statistics -> 'statistics' -> 'cnt' as execution_count,
  statistics -> 'statistics' -> 'meanLatency' as mean_latency
FROM crdb_internal.statement_statistics
WHERE (statistics -> 'statistics' -> 'meanLatency')::FLOAT > 0.1
ORDER BY (statistics -> 'statistics' -> 'meanLatency')::FLOAT DESC;
"

Implement automatic statistics collection

Configure automatic table statistics collection to ensure the query optimizer has accurate data for generating efficient execution plans.

cockroach sql --insecure --execute="
SET CLUSTER SETTING sql.stats.automatic_collection.fraction_stale_rows = 0.2;
SET CLUSTER SETTING sql.stats.automatic_collection.min_stale_rows = 500;
SET CLUSTER SETTING sql.stats.forecasts.enabled = true;
SET CLUSTER SETTING sql.stats.forecasts.min_observations = 5;
"

Configure index usage optimization

Enable features that help CockroachDB make better decisions about index selection and usage patterns.

cockroach sql --insecure --execute="
SET CLUSTER SETTING sql.optimizer.index_recommendations.enabled = true;
SET CLUSTER SETTING sql.optimizer.use_not_visible_indexes.enabled = false;
SET CLUSTER SETTING sql.optimizer.join_elimination.enabled = true;
SET CLUSTER SETTING sql.optimizer.memo.max_group_size_factor = 10000;
"

Set up query plan caching

Configure prepared statement and query plan caching to reduce planning overhead for frequently executed queries.

cockroach sql --insecure --execute="
SET CLUSTER SETTING sql.prepare.cached_plan_removal.fraction = 0.5;
SET CLUSTER SETTING sql.optimizer.plan_cache.enabled = true;
SET CLUSTER SETTING sql.optimizer.plan_cache.size = 1000;
SET CLUSTER SETTING sql.optimizer.memo.max_size_factor = 1000;
"

Advanced indexing strategies

Create optimized indexes for common query patterns

Design and create indexes that support your most frequent query patterns while minimizing storage overhead.

cockroach sql --insecure --execute="
-- Create a sample table for demonstration
CREATE TABLE IF NOT EXISTS user_activity (
    user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    activity_timestamp TIMESTAMPTZ NOT NULL DEFAULT now(),
    activity_type STRING NOT NULL,
    session_id UUID NOT NULL,
    metadata JSONB
);

-- Create covering index for time-based queries
CREATE INDEX idx_activity_time_covering 
ON user_activity (activity_timestamp DESC) 
STORING (activity_type, session_id, metadata);

-- Create partial index for active sessions
CREATE INDEX idx_active_sessions 
ON user_activity (session_id, activity_timestamp) 
WHERE activity_timestamp > now() - INTERVAL '24 hours';
"

Implement inverted indexes for JSON data

Create inverted indexes on JSONB columns to enable efficient queries on JSON document contents.

cockroach sql --insecure --execute="
-- Create inverted index for JSON queries
CREATE INVERTED INDEX idx_metadata_gin ON user_activity (metadata);

-- Create expression index for JSON path queries
CREATE INDEX idx_metadata_type ON user_activity ((metadata->>'type')) WHERE metadata ? 'type';
"

Monitor index usage and effectiveness

Create monitoring queries to track index usage statistics and identify unused or inefficient indexes.

cockroach sql --insecure --execute="
SELECT 
    schemaname,
    tablename,
    indexname,
    num_reads,
    last_read
FROM crdb_internal.index_usage_statistics 
ORDER BY num_reads DESC;
"

Monitoring and performance validation

Set up comprehensive monitoring

Configure monitoring to track the impact of your performance optimizations. Consider implementing automated performance monitoring.

cockroach sql --insecure --execute="
SELECT 
    node_id,
    store_id,
    capacity,
    available,
    used,
    (used::FLOAT / capacity::FLOAT * 100)::INT as used_percent
FROM crdb_internal.kv_store_status;
"

Create performance baseline metrics

Establish baseline performance metrics to measure the effectiveness of your tuning efforts.

cockroach workload init tpcc 'postgresql://root@localhost:26257?sslmode=disable'
cockroach workload run tpcc --duration=5m --concurrency=10 'postgresql://root@localhost:26257?sslmode=disable'

Verify your setup

# Check cluster status
cockroach node status --insecure


Verify configuration changes

cockroach sql --insecure --execute="SHOW CLUSTER SETTINGS WHERE setting LIKE '%cache%';"


Monitor query performance

cockroach sql --insecure --execute="SELECT * FROM slow_queries LIMIT 10;"


Check system resource usage

htop
iotop -ao

Common issues

Symptom Cause Fix
High memory usage after tuning Cache settings too aggressive Reduce cache size to 25% of RAM: --cache=2GiB
Slow writes after I/O optimization Disabled write barriers reducing durability Re-enable barriers: mount -o remount,barrier /var/lib/cockroach
Connection pool exhaustion PgBouncer pool size too small Increase default_pool_size and max_db_connections
Query optimizer not using new indexes Statistics not updated Force statistics collection: ANALYZE TABLE table_name;
Network timeouts in cluster Network buffers insufficient Increase TCP buffer sizes in sysctl configuration
Warning: Some optimizations like disabling write barriers improve performance at the cost of durability. Only use these settings in environments where you can tolerate potential data loss during system crashes.

Next steps

Automated install script

Run this to automate the entire setup

#cockroachdb #performance tuning #distributed sql #database optimization #connection pooling

Related tutorials

databases Intermediate

Implement ClickHouse backup automation with compression and S3 integration

Set up automated ClickHouse backups with compression, S3 storage, and systemd timers. Includes monitoring, encryption, and recovery procedures for production environments.

45 min 4 distros 22 views
databases Advanced

Benchmark database performance with sysbench and fio integration

Learn to benchmark MySQL and PostgreSQL database performance using sysbench 1.0.20 combined with fio 3.37 disk I/O testing. This comprehensive guide covers installation, configuration, execution of performance tests, and analysis of results for database optimization in production environments.

45 min 4 distros 48 views
databases Advanced

Configure MySQL binary log backup and point-in-time recovery

Set up automated MySQL binary log backup with rotation and implement point-in-time recovery procedures for complete database disaster recovery and operational continuity.

45 min 4 distros 75 views
Browse all tutorials in our complete directory →

Need help?

Don't want to manage this yourself?

We handle high availability infrastructure for businesses that depend on uptime. From initial setup to ongoing operations.

Learn about our high availability infrastructure Talk to an engineer