Chproxy

chproxy is a high-performance HTTP proxy for ClickHouse designed for load balancing across cluster nodes and protecting the database from overload. The solution automatically discovers ClickHouse cluster nodes in Yandex Cloud and distributes queries among them, ensuring high availability and performance.

chproxy provides a centralized access point to the ClickHouse cluster, simplifying connection management and enhancing security by isolating clients from direct access to database nodes.

Key Features and Benefits

• Load Balancing. Automatic query distribution across ClickHouse cluster nodes for optimal resource utilization.
• Automatic Node Discovery. Integration with Yandex Cloud for automatic discovery and monitoring of ClickHouse cluster nodes.
• Query Caching. The system caches queries for 150 seconds seconds with limited cache size up to 10 GB. To disable caching for a specific query, pass the no_cache=1 query parameter in the HTTP request.
• Rate Limiting. Cluster protection from overload by limiting the number of concurrent queries and their execution rate.
• TLS Encryption. HTTPS support with certificates from Certificate Manager and self-signed certificates.
• Transparency. Works as a transparent proxy — clients connect to chproxy the same way as to regular ClickHouse.
• Monitoring. Built-in metrics and logging for performance tracking and problem diagnosis.
• Flexible Configuration. Customizable routing rules and limits for different users and query types.

1. Make sure you have a deployed Managed Service for ClickHouse cluster.
2. Create a service account with the roles compute.editor, iam.serviceAccounts.admin, resource-manager.admin, vpc.admin, managed-clickhouse.viewer (the admin role includes most of the specified roles).
3. Create a Lockbox secret with two keys:
   • CLICKHOUSE_PASSWORD — ClickHouse user password for connecting to the cluster.
   • CHPROXY_PASSWORD — password for client authentication in chproxy.
4. (Optional) Create a certificate to use for TLS connections, or upload an existing certificate.
5. In the management console select the Cloud Apps service.
6. In the left panel, select Application Store.
7. Select chproxy and click the Use button.
8. Specify:
   • Application name.
   • (Optional) Application description.
   • The service account created earlier.
   • The ClickHouse cluster that chproxy will connect to.
   • The ClickHouse username for connecting to the cluster.
   • The Lockbox secret with ClickHouse user and chproxy passwords.
   • TLS connection setup method for chproxy:
     • Certificate Manager — select a certificate from Certificate Manager (the service will help you issue a publicly trusted Let’s Encrypt certificate or you can upload your own issued certificate).
     • Self-Signed — issue a self-signed certificate; in this case, you can optionally specify a Hostname for which the self-signed certificate will be issued.
   • The cloud subnet from the same VPC network where the selected ClickHouse cluster is located.
   • IP address mask from which access to chproxy will be opened; if the field is left empty — there will be no additional IP restrictions.
   • (Optional) User name and public key for SSH access to the virtual machine.
9. Click the Install button and wait for the application to install.
10. In the Application Resources section, follow the compute-instance link and on the opened page in the Network Interface section find the Public IPv4 address parameter value.
11. Connect to ClickHouse through chproxy using the HTTP interface and port 8443 following the instructions.
    In case of using a self-signed certificate, you need to add it to the trusted certificate pool. You can download it using the command:

    openssl s_client -connect <chproxy-ip-address>:8443 </dev/null | openssl x509 -outform PEM -out <certificate-export-filename>
    

12. Execute a test query several times to verify functionality. You will receive a different cluster host each time (dont forget to add query parameter no_cache=1:

    SELECT hostName();
    

• Балансировка нагрузки между узлами кластера ClickHouse для повышения производительности и отказоустойчивости.
• Централизованное управление доступом к кластеру ClickHouse с единой точкой аутентификации.
• Защита кластера от перегрузки путем ограничения количества одновременных запросов.
• Изоляция клиентских приложений от прямого доступа к узлам базы данных для повышения безопасности.* Load balancing across ClickHouse cluster nodes to improve performance and fault tolerance.
• Centralized access management to ClickHouse cluster with a single authentication point.
• Cluster protection from overload by limiting the number of concurrent queries.
• Isolating client applications from direct access to database nodes to enhance security.

Yandex Cloud technical support is available 24/7. The types of requests you can submit and the appropriate response time depend on your pricing plan. You can switch to the paid support plan in the management console. You can learn more about the technical support terms here.