ClickHouse Async Insert Stress Test

A comprehensive stress test suite for ClickHouse Async Insert functionality with various parameter combinations.

🎯 Test Overview

This test is designed to verify the performance and stability of ClickHouse's Asynchronous Insert feature.

Test Environment

• Database: ClickHouse Cloud (ap-northeast-2)
• Test Date: 2026-01-31

📁 File Structure

async-insert-stress/
├── 01-setup.sql              # Test environment setup (database, table creation)
├── 02-basic-tests.sql        # Basic parameter tests (6 cases, 1,000 records each)
├── 03-stress-tests.sql       # High-intensity stress tests (large volume & continuous INSERT)
├── 04-verification.sql       # Result verification queries
├── 05-monitoring.sql         # Monitoring and performance analysis queries
└── 99-cleanup.sql            # Cleanup after tests

🧪 Test Cases

Basic Parameter Tests (1,000 records each)

1. case1_sync: Synchronous INSERT (baseline)
2. case2_async_wait1: async_insert=1, wait_for_async_insert=1 (wait for flush)
3. case3_async_wait0: async_insert=1, wait_for_async_insert=0 (immediate response)
4. case4_async_timeout200: async_insert=1, wait=0, busy_timeout=200ms
5. case5_async_maxsize1mb: async_insert=1, wait=0, max_data_size=1MB
6. case6_async_wait1_timeout200: async_insert=1, wait=1, busy_timeout=200ms

High-Intensity Stress Tests

• stress_100k_wait0: 100K asynchronous INSERT (wait=0)
• stress_100k_wait1: 100K asynchronous INSERT (wait=1)
• rapid_insert_batch1-5: Continuous INSERT test (100 records × 5 times)

🚀 Execution Steps

1. Environment Setup bash clickhouse-client < 01-setup.sql

2. Run Basic Tests bash clickhouse-client < 02-basic-tests.sql

3. Run Stress Tests bash clickhouse-client < 03-stress-tests.sql

4. Verify Results bash clickhouse-client < 04-verification.sql

5. Monitoring bash clickhouse-client < 05-monitoring.sql

6. Cleanup bash clickhouse-client < 99-cleanup.sql

⚙️ Key Configuration Parameters

📊 Monitoring Points

• system.asynchronous_inserts: Current buffer status
• system.asynchronous_insert_log: INSERT logs and errors
• system.query_log: Query success/failure statistics
• system.parts: Part creation and merge status

✅ Expected Results

• 100% success rate across all test cases
• Improved response time with Async Insert
• Optimized part creation (increased merge efficiency)
• Data consistency maintained

🔍 Test Background and Results

Verification Purpose

When using ClickHouse's async_insert feature, the wait_for_async_insert=0 setting reduces INSERT response time to a few milliseconds. However, the documentation mentions "potential data loss," requiring validation for production environment stability.

Test Objectives

1. Verify if actual data loss occurs with wait_for_async_insert=0 setting
2. Measure INSERT success rate across various flush parameter combinations
3. Confirm stability in high-volume/high-frequency INSERT scenarios

Test Results

• Total Test Count: 200,500 records
• Overall Success Rate: 100%
• Data Loss: 0 records

Conclusion

In ClickHouse Cloud environment, the async_insert=1, wait_for_async_insert=0 configuration has nearly 0% actual loss probability due to multi-replica architecture. It can be safely used for cases where some data loss is acceptable, such as log data.

📌 Notes

• Wait 3 seconds after test completion to allow asynchronous buffers to flush.
• Large-volume tests consume significant system resources - proceed with caution.
• In production environments, adjust parameters according to your workload.