# CockroachDB Performance Testing Rule ## Overview This rule provides a comprehensive approach to performance testing CockroachDB by populating tables with large datasets to simulate real-world scenarios with millions of users. It includes data generation strategies, performance monitoring techniques, and bottleneck identification methods. ## Implementation ### 1. Create Performance Test Schema ```sql -- Users table for 1M+ users CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), email STRING UNIQUE NOT NULL, username STRING UNIQUE NOT NULL, first_name STRING NOT NULL, last_name STRING NOT NULL, created_at TIMESTAMP DEFAULT now(), updated_at TIMESTAMP DEFAULT now(), is_active BOOLEAN DEFAULT true, metadata JSONB ); -- User sessions table (high volume) CREATE TABLE user_sessions ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL REFERENCES users(id), session_token STRING NOT NULL, ip_address INET, user_agent TEXT, created_at TIMESTAMP DEFAULT now(), expires_at TIMESTAMP NOT NULL, is_active BOOLEAN DEFAULT true ); -- Orders table (transaction-heavy) CREATE TABLE orders ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL REFERENCES users(id), order_number STRING UNIQUE NOT NULL, total_amount DECIMAL(10,2) NOT NULL, status STRING NOT NULL DEFAULT 'pending', created_at TIMESTAMP DEFAULT now(), updated_at TIMESTAMP DEFAULT now() ); -- Order items (high cardinality) CREATE TABLE order_items ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), order_id UUID NOT NULL REFERENCES orders(id), product_id UUID NOT NULL, quantity INT NOT NULL, price DECIMAL(10,2) NOT NULL, created_at TIMESTAMP DEFAULT now() ); -- Activity logs (massive volume) CREATE TABLE activity_logs ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID REFERENCES users(id), action STRING NOT NULL, resource_type STRING, resource_id UUID, ip_address INET, created_at TIMESTAMP DEFAULT now(), metadata JSONB ); ``` ### 2. Create Performance Indexes ```sql -- Critical indexes for performance CREATE INDEX idx_users_email ON users(email); CREATE INDEX idx_users_created_at ON users(created_at); CREATE INDEX idx_user_sessions_user_id ON user_sessions(user_id); CREATE INDEX idx_user_sessions_expires_at ON user_sessions(expires_at); CREATE INDEX idx_orders_user_id ON orders(user_id); CREATE INDEX idx_orders_created_at ON orders(created_at); CREATE INDEX idx_order_items_order_id ON order_items(order_id); CREATE INDEX idx_activity_logs_user_id ON activity_logs(user_id); CREATE INDEX idx_activity_logs_created_at ON activity_logs(created_at); ``` ### 3. Data Generation Scripts ```sql -- Function to generate realistic test data CREATE OR REPLACE FUNCTION generate_performance_test_data(num_users INT) RETURNS VOID AS $$ DECLARE i INT := 1; user_uuid UUID; order_uuid UUID; batch_size INT := 1000; BEGIN -- Generate users in batches WHILE i <= num_users LOOP INSERT INTO users (email, username, first_name, last_name, created_at, metadata) SELECT 'user' || generate_series(i, LEAST(i + batch_size - 1, num_users)) || '@example.com', 'username' || generate_series(i, LEAST(i + batch_size - 1, num_users)), 'FirstName' || generate_series(i, LEAST(i + batch_size - 1, num_users)), 'LastName' || generate_series(i, LEAST(i + batch_size - 1, num_users)), now() - (random() * interval '365 days'), ('{"signup_source": "web", "preferences": {"notifications": ' || (random() > 0.5)::text || '}}')::jsonb; i := i + batch_size; -- Commit every batch to avoid long transactions IF i % (batch_size * 10) = 0 THEN RAISE NOTICE 'Generated % users', i; END IF; END LOOP; END; $$ LANGUAGE plpgsql; -- Generate sessions (multiple per user) CREATE OR REPLACE FUNCTION generate_user_sessions(session_multiplier INT DEFAULT 5) RETURNS VOID AS $$ DECLARE user_record RECORD; j INT; BEGIN FOR user_record IN SELECT id FROM users LOOP FOR j IN 1..session_multiplier LOOP INSERT INTO user_sessions (user_id, session_token, ip_address, user_agent, expires_at) VALUES ( user_record.id, md5(random()::text || clock_timestamp()::text), ('192.168.' || floor(random() * 255) || '.' || floor(random() * 255))::inet, 'Mozilla/5.0 (compatible; TestAgent/' || j || ')', now() + interval '24 hours' ); END LOOP; END LOOP; END; $$ LANGUAGE plpgsql; -- Generate orders with items CREATE OR REPLACE FUNCTION generate_orders_with_items(orders_per_user INT DEFAULT 3) RETURNS VOID AS $$ DECLARE user_record RECORD; order_uuid UUID; i INT; j INT; BEGIN FOR user_record IN SELECT id FROM users LOOP FOR i IN 1..orders_per_user LOOP INSERT INTO orders (user_id, order_number, total_amount, status, created_at) VALUES ( user_record.id, 'ORD-' || user_record.id || '-' || i, (random() * 500 + 10)::decimal(10,2), CASE WHEN random() > 0.8 THEN 'cancelled' WHEN random() > 0.6 THEN 'shipped' WHEN random() > 0.4 THEN 'processing' ELSE 'delivered' END, now() - (random() * interval '90 days') ) RETURNING id INTO order_uuid; -- Add 1-5 items per order FOR j IN 1..(1 + floor(random() * 5))::int LOOP INSERT INTO order_items (order_id, product_id, quantity, price) VALUES ( order_uuid, gen_random_uuid(), (1 + floor(random() * 10))::int, (random() * 100 + 5)::decimal(10,2) ); END LOOP; END LOOP; END LOOP; END; $$ LANGUAGE plpgsql; -- Generate activity logs (high volume) CREATE OR REPLACE FUNCTION generate_activity_logs(logs_per_user INT DEFAULT 50) RETURNS VOID AS $$ DECLARE user_record RECORD; i INT; actions TEXT[] := ARRAY['login', 'logout', 'view_product', 'add_to_cart', 'purchase', 'update_profile', 'search']; BEGIN FOR user_record IN SELECT id FROM users LOOP FOR i IN 1..logs_per_user LOOP INSERT INTO activity_logs (user_id, action, resource_type, resource_id, ip_address, created_at, metadata) VALUES ( user_record.id, actions[1 + floor(random() * array_length(actions, 1))], CASE WHEN random() > 0.5 THEN 'product' WHEN random() > 0.3 THEN 'order' ELSE 'user' END, gen_random_uuid(), ('10.' || floor(random() * 255) || '.' || floor(random() * 255) || '.' || floor(random() * 255))::inet, now() - (random() * interval '30 days'), ('{"page": "/app/page' || floor(random() * 100) || '", "duration": ' || floor(random() * 10000) || '}')::jsonb ); END LOOP; END LOOP; END; $$ LANGUAGE plpgsql; ``` ### 4. Execute Performance Test Data Generation ```sql -- Generate 1 million users SELECT generate_performance_test_data(1000000); -- Generate sessions (5 million sessions) SELECT generate_user_sessions(5); -- Generate orders with items (3 million orders, ~9 million items) SELECT generate_orders_with_items(3); -- Generate activity logs (50 million logs) SELECT generate_activity_logs(50); ``` ### 5. Performance Monitoring Queries ```sql -- Monitor table sizes and row counts SELECT schemaname, tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size, pg_stat_get_tuples_inserted(c.oid) as inserts, pg_stat_get_tuples_updated(c.oid) as updates, pg_stat_get_tuples_deleted(c.oid) as deletes FROM pg_tables t JOIN pg_class c ON c.relname = t.tablename WHERE schemaname = 'public' ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC; -- Monitor query performance SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements WHERE query LIKE '%users%' OR query LIKE '%orders%' OR query LIKE '%activity_logs%' ORDER BY total_time DESC LIMIT 10; -- Check slow queries SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes' ORDER BY duration DESC; ``` ### 6. Performance Test Scenarios ```sql -- Test 1: User lookup performance EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user500000@example.com'; -- Test 2: Recent orders query EXPLAIN ANALYZE SELECT u.username, o.order_number, o.total_amount, o.created_at FROM users u JOIN orders o ON u.id = o.user_id WHERE o.created_at > now() - interval '7 days' ORDER BY o.created_at DESC LIMIT 100; -- Test 3: Activity logs aggregation EXPLAIN ANALYZE SELECT action, DATE_TRUNC('day', created_at) as day, COUNT(*) as action_count FROM activity_logs WHERE created_at > now() - interval '30 days' GROUP BY action, DATE_TRUNC('day', created_at) ORDER BY day DESC, action_count DESC; -- Test 4: Complex join performance EXPLAIN ANALYZE SELECT u.username, COUNT(DISTINCT o.id) as total_orders, SUM(o.total_amount) as total_spent, COUNT(DISTINCT s.id) as active_sessions FROM users u LEFT JOIN orders o ON u.id = o.user_id LEFT JOIN user_sessions s ON u.id = s.user_id AND s.is_active = true WHERE u.created_at > now() - interval '90 days' GROUP BY u.id, u.username HAVING COUNT(DISTINCT o.id) > 2 ORDER BY total_spent DESC LIMIT 50; ``` ## Best Practices ### Data Generation - **Batch Processing**: Generate data in batches of 1,000-10,000 records to avoid memory issues - **Realistic Distributions**: Use random functions to simulate real-world data patterns - **Incremental Loading**: Load data incrementally and monitor system resources - **Transaction Management**: Commit frequently during bulk operations ### Performance Monitoring - **Enable Query Statistics**: Use `pg_stat_statements` extension for query analysis - **Monitor Resource Usage**: Track CPU, memory, and disk I/O during tests - **Index Usage**: Monitor index effectiveness with `pg_stat_user_indexes` - **Connection Pooling**: Use connection pooling for high-concurrency tests ### Test Scenarios - **Gradual Load Increase**: Start with smaller datasets and gradually increase - **Mixed Workloads**: Test read-heavy, write-heavy, and mixed scenarios - **Concurrent Users**: Simulate multiple concurrent connections - **Peak Load Testing**: Test system behavior during peak usage periods ## Common Issues ### Performance Bottlenecks ```sql -- Issue: Slow bulk inserts -- Solution: Use batch inserts and disable autocommit BEGIN; INSERT INTO users (...) VALUES (...), (...), (...); -- Batch multiple rows COMMIT; -- Issue: Index contention during data loading -- Solution: Create indexes after bulk data loading DROP INDEX IF EXISTS idx_users_email; -- Load data CREATE INDEX idx_users_email ON users(email); ``` ### Memory and Storage Issues ```sql -- Monitor cluster storage usage SELECT store_id, node_id, used_bytes, available_bytes, (used_bytes::float / (used_bytes + available_bytes)) * 100 as usage_percent FROM crdb_internal.kv_store_status; -- Check for memory pressure SHOW CLUSTER SETTING sql.stats.automatic_collection.enabled; SET CLUSTER SETTING sql.stats.automatic_collection.enabled = true; ``` ### Query Optimization ```sql -- Issue: Sequential scans on large tables -- Solution: Add appropriate indexes and use LIMIT CREATE INDEX CONCURRENTLY idx_activity_logs_user_created ON activity_logs(user_id, created_at DESC); -- Issue: Inefficient JOINs -- Solution: Use proper join order and indexes EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users u JOIN orders o ON u.id = o.user_id WHERE u.created_at > now() - interval '30 days'; ``` ### Connection Management ```sql -- Monitor active connections SELECT application_name, state, COUNT(*) FROM pg_stat_activity GROUP BY application_name, state; -- Set appropriate connection limits SET CLUSTER SETTING server.max_connections_per_node = 500; ``` ### Cleanup and Maintenance ```sql -- Clean up test data TRUNCATE activity_logs, order_items, orders, user_sessions, users CASCADE; -- Update table statistics after large data changes ANALYZE users; ANALYZE orders; ANALYZE activity_logs; -- Monitor fragmentation SELECT schemaname, tablename, n_tup_ins, n_tup_upd, n_tup_del, n_dead_tup FROM pg_stat_user_tables WHERE n_dead_tup > 1000; ```