Introduction
Why I am not able to connect with ClickHouse Server? We often this ticket on our support function and the error will be like “Connection reset by peer while reading from socket”. So we have a standard, yet simple approach to troubleshooting ClickHouse connection issues. This blog is about how we help our customers in troubleshooting ClickHouse connectivity errors
Steps to Troubleshoot Connection Errors
Step 1: Please confirm ClickHouse Server is running and operational
# systemctl status clickhouse-server
Note
- Please confirm ClickHouse Server is successfully started. If you have just restarted the ClickHouse instance, the server will be still starting. You can expect latency with ClickHouse Server starting when the startup sequence needs to orchestrate and iterate across data files folders in /var/lib/clickhouse-server, More folders with a high number of databases and tables with complex partitioning naturally delays the ClickHoue startup process. You still have concerns with the ClickHouse startup process please check /var/log/clickhouse-server/clickhouse-server.log
Step 2: Please confirm you are using the appropriate port IP/interface and ensure you are not connecting to a secured port without tls/https or vice versa.
Note: If you are uncommenting the line <listen_host>0.0.0.0</listen_host> only, Then ClickHouse Server will listen only to ipv4 interfaces, while the localhost (used by clickhouse-client) may be resolved to ipv6 address. And clickhouse-client may be failing to connect.
Please validate the clickhouse-client connection to correct host / port / secure flags.
How can you validate which interfaces/ports ClickHouse Server is listening to?
# sudo lsof -i -P -n | grep LISTEN echo listen_host sudo clickhouse-extract-from-config --config=/etc/clickhouse-server/config.xml --key=listen_host echo tcp_port sudo clickhouse-extract-from-config --config=/etc/clickhouse-server/config.xml --key=tcp_port echo tcp_port_secure sudo clickhouse-extract-from-config --config=/etc/clickhouse-server/config.xml --key=tcp_port_secure echo http_port sudo clickhouse-extract-from-config --config=/etc/clickhouse-server/config.xml --key=http_port echo https_port sudo clickhouse-extract-from-config --config=/etc/clickhouse-server/config.xml --key=https_port
Step 3: If you are operating ClickHouse Server on secured infrastructure:
- If you are using some certification process for ClickHouse connection handling please ensure same can be validated by the client
- You can as well disable certificate handling/checks on the client (UNSECURE clickhouse-client)
Step 4: Always depend on ClickHouse error log (/var/log/clickhouse-server/clickhouse-server.err.log) if you are suspecting something seriously wrong with ClickHouse connection management
Step 5: Please confirm ClickHouse is able to connect with some trivial tcp/http requests from localhost:
# curl 127.0.0.1:9200 # curl 127.0.0.1:8123
Step 6: Please confirm a total number of sockets opened by ClickHouse Server:
# sudo lsof -i -a -p $(pidof clickhouse-server)
# or (adjust 9000 / 8123 ports if needed)
# netstat -tn 2>/dev/null | tail -n +3 | awk '{ printf("%s\t%s\t%s\t%s\t%s\t%s\n", $1, $2, $3, $4, $5, $6) }' | clickhouse-local -S "Proto String, RecvQ Int64, SendQ Int64, LocalAddress String, ForeignAddress String, State LowCardinality(String)" --query="SELECT * FROM table WHERE LocalAddress like '%:9000' FORMAT PrettyCompact"
# netstat -tn 2>/dev/null | tail -n +3 | awk '{ printf("%s\t%s\t%s\t%s\t%s\t%s\n", $1, $2, $3, $4, $5, $6) }' | clickhouse-local -S "Proto String, RecvQ Int64, SendQ Int64, LocalAddress String, ForeignAddress String, State LowCardinality(String)" --query="SELECT * FROM table WHERE LocalAddress like '%:8123' FORMAT PrettyCompact"
Note: By default, The ClickHouse server has a limit of 4000 to the number of open connections
To check support for the total number of connections use netstat and cat
### system overall support limited number of connections it can handle # netstat ### you can also be reaching of of the process ulimits (Max open files) # cat /proc/$(pidof -s clickhouse-server)/limits
Step 7: This is simplest but happens quite often, Please check for firewall or any other SELinux rules.
Conclusion
To learn more about troubleshooting in ClickHouse, do consider reading the following articles:
