Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Connections from PostgreSQL connection pools are not reused within a single query or other concurrent executing queries #62974

Open
realyota opened this issue Apr 25, 2024 · 0 comments
Open

Connections from PostgreSQL connection pools are not reused within a single query or other concurrent executing queries #62974

realyota opened this issue Apr 25, 2024 · 0 comments
Labels
unexpected behaviour

Comments

@realyota
Copy link
Contributor

realyota commented Apr 25, 2024 •
edited

ClickHouse version 24.2.1

Describe the unexpected behaviour
When running a query that uses PostgreSQLPoolWithFailover connections to PostgreSQL stays "idle" after execution on PG side (till query on CH side executes) and are not reused within the same query or another query that uses PostgreSQLPoolWithFailover. My conclusions are:

  • each query with postgresql table functions has it own PostgreSQLPoolWithFailover (created at start of the query),
  • for all PostgreSQL engine tables there is one PostgreSQLPoolWithFailover created at the server startup,
  • connection from PostgreSQLPoolWithFailover when query is executed is not reused within query nor other executing CH queries,
  • connection from PostgreSQLPoolWithFailover when query is executed stays idle after execution on pg side (connected but not doing any work) till the end of the CH query.

How to reproduce
Create a table on PG side:

CREATE TABLE test (
    a INTEGER PRIMARY KEY,
    b INTEGER NOT NULL,             
    c INTEGER NOT NULL,             
    d INTEGER NOT NULL             
);

INSERT INTO test SELECT a, a, a, a FROM generate_series(1, 10, 1) a;

Create a PostgreSQL engine table on ClickHouse:

CREATE TABLE default.test_pg (
    `a` Int64,
    `b` Int64,
    `c` Int64,
    `d` Int64
)
ENGINE = PostgreSQL('127.0.0.1:5432', 'test', 'test', 'test', 'test');

Execute a query on CH side (default for postgresql_connection_pool_size is 16):

  SELECT * FROM (SELECT * FROM test_pg LIMIT 10) t 
  /*this will artificaly slow down SELECT*/
 JOIN (SELECT sleepEachRow(0.000001), 1 n FROM numbers(10e7)) s ON t.a = s.n 
 JOIN (SELECT * FROM test_pg) x1 ON x1.a=t.a
 JOIN (SELECT * FROM test_pg) x2 ON x2.a=t.a
 JOIN (SELECT * FROM test_pg) x3 ON x3.a=t.a
 JOIN (SELECT * FROM test_pg) x4 ON x4.a=t.a
 JOIN (SELECT * FROM test_pg) x5 ON x5.a=t.a
 JOIN (SELECT * FROM test_pg) x6 ON x6.a=t.a
 JOIN (SELECT * FROM test_pg) x7 ON x7.a=t.a
 JOIN (SELECT * FROM test_pg) x8 ON x8.a=t.a
 JOIN (SELECT * FROM test_pg) x9 ON x9.a=t.a
 JOIN (SELECT * FROM test_pg) x10 ON x10.a=t.a
 JOIN (SELECT * FROM test_pg) x11 ON x11.a=t.a
 JOIN (SELECT * FROM test_pg) x12 ON x12.a=t.a
 JOIN (SELECT * FROM test_pg) x13 ON x13.a=t.a
 JOIN (SELECT * FROM test_pg) x14 ON x14.a=t.a
 JOIN (SELECT * FROM test_pg) x15 ON x15.a=t.a
 JOIN (SELECT * FROM test_pg) x16 ON x16.a=t.a;

Received exception from server (version 24.2.1):
Code: 614. DB::Exception: Received from localhost:9000. DB::Exception. (POSTGRESQL_CONNECTION_FAILURE)

SELECT pid, usename, application_name, client_hostname, client_port, backend_start, state, query_id FROM pg_stat_activity
  pid  | usename  | application_name | client_hostname | client_port |         backend_start         | state  | query_id
-------+----------+------------------+-----------------+-------------+-------------------------------+--------+----------
  1036 |          |                  |                 |             | 2024-04-10 09:43:05.76965+02  |        |
  1037 | postgres |                  |                 |             | 2024-04-10 09:43:05.769938+02 |        |
 54552 | test     |                  |                 |       39786 | 2024-04-24 12:05:43.062189+02 | idle   |
 54617 | test     |                  |                 |       42420 | 2024-04-24 12:06:01.210384+02 | idle   |
 56469 | test     |                  |                 |       35802 | 2024-04-25 15:06:44.404462+02 | idle   |
 56468 | test     |                  |                 |       35796 | 2024-04-25 15:06:44.388475+02 | idle   |
 56467 | test     |                  |                 |       35786 | 2024-04-25 15:06:44.372918+02 | idle   |
 56466 | test     |                  |                 |       35782 | 2024-04-25 15:06:44.360284+02 | idle   |
 56465 | test     |                  |                 |       35770 | 2024-04-25 15:06:44.34932+02  | idle   |
 56464 | test     |                  |                 |       35764 | 2024-04-25 15:06:44.336819+02 | idle   |
 56470 | test     |                  |                 |       35808 | 2024-04-25 15:06:44.420517+02 | idle   |
 56471 | test     |                  |                 |       35814 | 2024-04-25 15:06:44.439634+02 | idle   |
 56463 | test     |                  |                 |       35760 | 2024-04-25 15:06:44.32513+02  | idle   |
 56462 | test     |                  |                 |       35744 | 2024-04-25 15:06:44.3129+02   | idle   |
 56461 | test     |                  |                 |       35738 | 2024-04-25 15:06:44.302739+02 | idle   |
 56460 | test     |                  |                 |       35736 | 2024-04-25 15:06:44.291444+02 | idle   |
 56459 | test     |                  |                 |       35734 | 2024-04-25 15:06:44.281793+02 | idle   |

You can the same querytwice from different connections, one will execute while other will fail with the same exception message as above.

  SELECT * FROM (SELECT * FROM test_pg LIMIT 10) t 
  /*this will artificaly slow down SELECT*/
 JOIN (SELECT sleepEachRow(0.000001), 1 n FROM numbers(10e7)) s ON t.a = s.n 
 JOIN (SELECT * FROM test_pg) x1 ON x1.a=t.a
 JOIN (SELECT * FROM test_pg) x2 ON x2.a=t.a
 JOIN (SELECT * FROM test_pg) x3 ON x3.a=t.a
 JOIN (SELECT * FROM test_pg) x4 ON x4.a=t.a
 JOIN (SELECT * FROM test_pg) x5 ON x5.a=t.a
 JOIN (SELECT * FROM test_pg) x6 ON x6.a=t.a
 JOIN (SELECT * FROM test_pg) x7 ON x7.a=t.a
 JOIN (SELECT * FROM test_pg) x8 ON x8.a=t.a
 JOIN (SELECT * FROM test_pg) x9 ON x9.a=t.a
 JOIN (SELECT * FROM test_pg) x10 ON x10.a=t.a;

← Progress: 9.09 million rows, 72.74 MB (963.48 thousand rows/s., 7.71 MB/s.) █████████▉                                                                                          (0.0 CPU) 9% 

  SELECT * FROM (SELECT * FROM test_pg LIMIT 10) t 
  /*this will artificaly slow down SELECT*/
 JOIN (SELECT sleepEachRow(0.000001), 1 n FROM numbers(10e7)) s ON t.a = s.n 
 JOIN (SELECT * FROM test_pg) x1 ON x1.a=t.a
 JOIN (SELECT * FROM test_pg) x2 ON x2.a=t.a
 JOIN (SELECT * FROM test_pg) x3 ON x3.a=t.a
 JOIN (SELECT * FROM test_pg) x4 ON x4.a=t.a
 JOIN (SELECT * FROM test_pg) x5 ON x5.a=t.a
 JOIN (SELECT * FROM test_pg) x6 ON x6.a=t.a
 JOIN (SELECT * FROM test_pg) x7 ON x7.a=t.a
 JOIN (SELECT * FROM test_pg) x8 ON x8.a=t.a
 JOIN (SELECT * FROM test_pg) x9 ON x9.a=t.a
 JOIN (SELECT * FROM test_pg) x10 ON x10.a=t.a;

Elapsed: 10.325 sec.

Received exception from server (version 24.2.1):
Code: 614. DB::Exception: Received from localhost:9000. DB::Exception. (POSTGRESQL_CONNECTION_FAILURE)

Expected behavior
Connections to PostgreSQL are reused within the same query and other queries executing at the same time.

Error message and/or stacktrace

Query id: 6939316a-d663-448a-87e1-5b2485aeb0be

[LAPTOP-ASLS2SOJ] 2024.04.25 15:47:45.570762 [ 53864 ] {6939316a-d663-448a-87e1-5b2485aeb0be} <Debug> executeQuery: (from 127.0.0.1:53862)  SELECT * FROM (SELECT * FROM test_pg LIMIT 10) t /*this will artificaly slow down SELECT*/
 JOIN (SELECT sleepEachRow(0.000001), 1 n FROM numbers(10e7)) s ON t.a = s.n (stage: Complete)
[LAPTOP-ASLS2SOJ] 2024.04.25 15:47:45.571260 [ 53864 ] {6939316a-d663-448a-87e1-5b2485aeb0be} <Trace> ContextAccess (default): Access granted: SELECT(a, b, c, d) ON default.test_pg
[LAPTOP-ASLS2SOJ] 2024.04.25 15:47:45.571674 [ 53864 ] {6939316a-d663-448a-87e1-5b2485aeb0be} <Trace> ContextAccess (default): Access granted: SELECT(a, b, c, d) ON default.test_pg
[LAPTOP-ASLS2SOJ] 2024.04.25 15:47:45.572093 [ 53864 ] {6939316a-d663-448a-87e1-5b2485aeb0be} <Trace> InterpreterSelectQuery: FetchColumns -> Complete
[LAPTOP-ASLS2SOJ] 2024.04.25 15:47:45.572213 [ 53864 ] {6939316a-d663-448a-87e1-5b2485aeb0be} <Trace> TableJoin: Infer supertype for joined columns. Left: [a : Int64 -> Int64], Right: [n : UInt8 -> Int64]
[LAPTOP-ASLS2SOJ] 2024.04.25 15:47:45.572376 [ 53864 ] {6939316a-d663-448a-87e1-5b2485aeb0be} <Trace> HashJoin: Keys: [(a) = (materialize(_CAST(n, Int64)))], datatype: EMPTY, kind: Inner, strictness: All, right header: n UInt8 Const(size = 0, UInt8(size = 1)), sleepEachRow(0.000001) UInt8 UInt8(size = 0), materialize(_CAST(n, Int64)) Int64 Int64(size = 0)
[LAPTOP-ASLS2SOJ] 2024.04.25 15:47:45.572657 [ 53864 ] {6939316a-d663-448a-87e1-5b2485aeb0be} <Trace> ContextAccess (default): Access granted: SELECT(a, b, c, d) ON default.test_pg
[LAPTOP-ASLS2SOJ] 2024.04.25 15:47:45.572749 [ 53864 ] {6939316a-d663-448a-87e1-5b2485aeb0be} <Trace> StoragePostgreSQL (test_pg): Query: SELECT "a", "b", "c", "d" FROM "test"
[LAPTOP-ASLS2SOJ] 2024.04.25 15:47:50.572960 [ 53864 ] {6939316a-d663-448a-87e1-5b2485aeb0be} <Warning> PostgreSQLConnectionPool: Unable to fetch connection within the timeout
[LAPTOP-ASLS2SOJ] 2024.04.25 15:47:55.573163 [ 53864 ] {6939316a-d663-448a-87e1-5b2485aeb0be} <Warning> PostgreSQLConnectionPool: Unable to fetch connection within the timeout
[LAPTOP-ASLS2SOJ] 2024.04.25 15:47:55.573482 [ 53864 ] {6939316a-d663-448a-87e1-5b2485aeb0be} <Trace> HashJoin: Join data is being destroyed, 8192 bytes and 0 rows in hash table
[LAPTOP-ASLS2SOJ] 2024.04.25 15:47:55.574491 [ 53864 ] {6939316a-d663-448a-87e1-5b2485aeb0be} <Error> executeQuery: Code: 614. DB::Exception. (POSTGRESQL_CONNECTION_FAILURE) (version 24.2.1.1598 (official build)) (from 127.0.0.1:53862) (in query:  SELECT * FROM (SELECT * FROM test_pg LIMIT 10) t /*this will artificaly slow down SELECT*/
 JOIN (SELECT sleepEachRow(0.000001), 1 n FROM numbers(10e7)) s ON t.a = s.n), Stack trace (when copying this message, always include the lines below):

0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x000000000c9a503b in /usr/bin/clickhouse
1. DB::Exception::Exception(PreformattedMessage const&, int) @ 0x000000000f95782c in /usr/bin/clickhouse
2. postgres::PoolWithFailover::get() @ 0x000000001005597b in /usr/bin/clickhouse
3. DB::StoragePostgreSQL::read(std::vector<String, std::allocator<String>> const&, std::shared_ptr<DB::StorageSnapshot> const&, DB::SelectQueryInfo&, std::shared_ptr<DB::Context const>, DB::QueryProcessingStage::Enum, unsigned long, unsigned long) @ 0x0000000011e669f9 in /usr/bin/clickhouse
4. DB::IStorage::read(DB::QueryPlan&, std::vector<String, std::allocator<String>> const&, std::shared_ptr<DB::StorageSnapshot> const&, DB::SelectQueryInfo&, std::shared_ptr<DB::Context const>, DB::QueryProcessingStage::Enum, unsigned long, unsigned long) @ 0x0000000011cec8d9 in /usr/bin/clickhouse
5. DB::InterpreterSelectQuery::executeImpl(DB::QueryPlan&, std::optional<DB::Pipe>) @ 0x00000000114c59ce in /usr/bin/clickhouse
6. DB::InterpreterSelectQuery::buildQueryPlan(DB::QueryPlan&) @ 0x00000000114c2f14 in /usr/bin/clickhouse
7. DB::InterpreterSelectWithUnionQuery::buildQueryPlan(DB::QueryPlan&) @ 0x000000001157f3c4 in /usr/bin/clickhouse
8. DB::InterpreterSelectQuery::executeImpl(DB::QueryPlan&, std::optional<DB::Pipe>) @ 0x00000000114c631f in /usr/bin/clickhouse
9. DB::InterpreterSelectQuery::buildQueryPlan(DB::QueryPlan&) @ 0x00000000114c2f14 in /usr/bin/clickhouse
10. DB::InterpreterSelectWithUnionQuery::buildQueryPlan(DB::QueryPlan&) @ 0x000000001157f3c4 in /usr/bin/clickhouse
11. DB::InterpreterSelectWithUnionQuery::execute() @ 0x000000001158043e in /usr/bin/clickhouse
12. DB::executeQueryImpl(char const*, char const*, std::shared_ptr<DB::Context>, DB::QueryFlags, DB::QueryProcessingStage::Enum, DB::ReadBuffer*) @ 0x0000000011913c68 in /usr/bin/clickhouse   
13. DB::executeQuery(String const&, std::shared_ptr<DB::Context>, DB::QueryFlags, DB::QueryProcessingStage::Enum) @ 0x000000001190ea7a in /usr/bin/clickhouse
14. DB::TCPHandler::runImpl() @ 0x000000001294aae9 in /usr/bin/clickhouse
15. DB::TCPHandler::run() @ 0x0000000012962b19 in /usr/bin/clickhouse
16. Poco::Net::TCPServerConnection::start() @ 0x000000001523dbf2 in /usr/bin/clickhouse
17. Poco::Net::TCPServerDispatcher::run() @ 0x000000001523e9f1 in /usr/bin/clickhouse
18. Poco::PooledThread::run() @ 0x0000000015337087 in /usr/bin/clickhouse
19. Poco::ThreadImpl::runnableEntry(void*) @ 0x00000000153356bd in /usr/bin/clickhouse
20. ? @ 0x00007fa5b6cea609
21. ? @ 0x00007fa5b6c0f353


Elapsed: 10.006 sec.

Received exception from server (version 24.2.1):
Code: 614. DB::Exception: Received from localhost:9000. DB::Exception. (POSTGRESQL_CONNECTION_FAILURE)
@realyota realyota changed the title Connections from PostgreSQL connection pools are not reused within a single query or other queries Connections from PostgreSQL connection pools are not reused within a single query or other concurrent executing queries Apr 25, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
unexpected behaviour
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@realyota