Why I am not able to connect to ClickHouse?

Troubleshooting ClickHouse – Why I am not able to connect to ClickHouse? 


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:

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

About ChistaDATA Inc. 10 Articles
We are an full-stack ClickHouse infrastructure operations Consulting, Support and Managed Services provider with core expertise in performance, scalability and data SRE. Based out of California, Our consulting and support engineering team operates out of San Francisco, Vancouver, London, Germany, Russia, Ukraine, Australia, Singapore and India to deliver 24*7 enterprise-class consultative support and managed services. We operate very closely with some of the largest and planet-scale internet properties like PayPal, Garmin, Honda cars IoT project, Viacom, National Geographic, Nike, Morgan Stanley, American Express Travel, VISA, Netflix, PRADA, Blue Dart, Carlsberg, Sony, Unilever etc