connection performance issue

[wbwangwb]

We located the clickhouse server in aks, there are 3 replicas for each database.
We use this ClickHouse.Client to connect clickhouse server through https .

The overall performance is ok.
However, we found that some queries took very long time to complete,Some times the query even timeout and been cancelled. After checking the log in system.query_log table, we found that the actual query execution time on clickhouse server is even less than 1 second. we found that the client took very long time (more than 15 seconds) to call ClickHouseConnection.OpenAsync. Understoold there is no need to call this function separately, just want to understand where it took time. It looks the client took very long time to make the tcp/ssl connection.

some times the client even timeout to make the SSL connection
details: System.Threading.Tasks.TaskCanceledException: The operation was canceled.
at System.Net.Http.ConnectHelper.EstablishSslConnectionAsyncCore(Stream stream, SslClientAuthenticationOptions sslOptions, CancellationToken cancellationToken)
at System.Net.Http.HttpConnectionPool.CreateConnectionAsync(HttpRequestMessage request, CancellationToken cancellationToken)
at System.Net.Http.HttpConnectionPool.WaitForCreatedConnectionAsync(Task`1 creationTask)
at System.Net.Http.HttpConnectionPool.SendWithRetryAsync(HttpRequestMessage request, Boolean doRequestAuth, CancellationToken cancellationToken)
at System.Net.Http.RedirectHandler.SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
at System.Net.Http.DecompressionHandler.SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
at System.Net.Http.HttpClient.g__Core|83_0(HttpRequestMessage request, HttpCompletionOption completionOption, CancellationTokenSource cts, Boolean disposeCts, CancellationTokenSource pendingRequestsCts, CancellationToken originalCancellationToken)
at ClickHouse.Client.ADO.ClickHouseConnection.OpenAsync(CancellationToken cancellationToken)
at ClickHouse.Client.ADO.ClickHouseCommand.PostSqlQueryAsync(String sqlQuery, CancellationToken token)
at ClickHouse.Client.ADO.ClickHouseCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)

some times when timeout the query not complete yet but the query actually very simple query:
details: System.Threading.Tasks.TaskCanceledException: The operation was canceled.
---> System.Threading.Tasks.TaskCanceledException: The operation was canceled.
---> System.IO.IOException: Unable to read data from the transport connection: Operation canceled.
---> System.Net.Sockets.SocketException (125): Operation canceled
--- End of inner exception stack trace ---
at System.Net.Sockets.Socket.AwaitableSocketAsyncEventArgs.System.Threading.Tasks.Sources.IValueTaskSource<System.Int32>.GetResult(Int16 token)
at System.Net.Security.SslStream.EnsureFullTlsFrameAsync[TIOAdapter](CancellationToken cancellationToken, Int32 estimatedSize)
at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder1.StateMachineBox1.System.Threading.Tasks.Sources.IValueTaskSource.GetResult(Int16 token)
at System.Net.Security.SslStream.ReadAsyncInternal[TIOAdapter](Memory1 buffer, CancellationToken cancellationToken) at System.Runtime.CompilerServices.PoolingAsyncValueTaskMethodBuilder1.StateMachineBox1.System.Threading.Tasks.Sources.IValueTaskSource.GetResult(Int16 token) at System.Threading.Tasks.ValueTask1.ValueTaskSourceAsTask.<>c.<.cctor>b__4_0(Object state)
--- End of stack trace from previous location ---
at System.Net.Http.HttpConnection.SendAsyncCore(HttpRequestMessage request, CancellationToken cancellationToken)
--- End of inner exception stack trace ---
at System.Net.Http.HttpConnection.SendAsyncCore(HttpRequestMessage request, CancellationToken cancellationToken)
at System.Net.Http.HttpConnectionPool.SendWithNtConnectionAuthAsync(HttpConnection connection, HttpRequestMessage request, Boolean doRequestAuth, CancellationToken cancellationToken)
at System.Net.Http.HttpConnectionPool.SendWithRetryAsync(HttpRequestMessage request, Boolean doRequestAuth, CancellationToken cancellationToken)
at System.Net.Http.RedirectHandler.SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
at System.Net.Http.DecompressionHandler.SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
at System.Net.Http.HttpClient.g__Core|83_0(HttpRequestMessage request, HttpCompletionOption completionOption, CancellationTokenSource cts, Boolean disposeCts, CancellationTokenSource pendingRequestsCts, CancellationToken originalCancellationToken)

at the client side, we already set the http client as static and set the keep-alive:

private static readonly HttpClient _httpClient = CreateHttpClient();

below is the definition of : CreateHttpClient

var handler = new StandardSocketsHttpHandler()
{
ConnectTimeout = TimeSpan.FromSeconds(ClickhouseQueryBuilderConfiguration.ClickhouseStandardSocketsConnectTimeoutFromSeconds), // Increased timeout
AutomaticDecompression = DecompressionMethods.GZip | DecompressionMethods.Deflate,
PooledConnectionLifetime = TimeSpan.FromMinutes(ClickhouseQueryBuilderConfiguration.ClickhouseStandardSocketsPooledConnectionLifetimeFromMinutes),
PooledConnectionIdleTimeout = TimeSpan.FromMinutes(ClickhouseQueryBuilderConfiguration.ClickhouseStandardSocketsPooledConnectionIdleTimeoutFromMinutes),
// UseCookies = ClickhouseQueryBuilderConfiguration.ClickhouseStandardSocketsUseCookies,
SslOptions = new SslClientAuthenticationOptions
{
AllowRenegotiation = false,
RemoteCertificateValidationCallback = delegate { return true; },
}
};
var client = new HttpClient(handler, disposeHandler: false);
client.DefaultRequestHeaders.Add("Connection", "Keep-Alive");
// client.DefaultRequestHeaders.ConnectionClose = false;
client.DefaultRequestHeaders.Add("Keep-Alive", "timeout=600");
client.Timeout = TimeSpan.FromMinutes(10); // Increased timeout
_httpClient = client;

var connection = new ClickHouseConnection(connectionString, _httpClient);
await connection.OpenAsync();
var dataReader = await command.ExecuteReaderAsync();

From the clickhorse server side, we set the Max_connections as 8192, keep_alive_timeout as 30, http_connections_soft_limit as 500. And from the query_log table, there are about 90,000 queries (including the select version query from connection.OpenAsync ) per hour for each replica.

Could you please take a look, if there is anyway we can reduce the query execution time (actully the query runs very fast even less than 1 second on server, but from the client side perspective, the query took more than 10 seconds) ?