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

alter table xxx on cluster xxx update = null where isNaN(xxx) slow and 100% cpu use #63038

Open
a819721810 opened this issue Apr 26, 2024 · 0 comments
Open

alter table xxx on cluster xxx update = null where isNaN(xxx) slow and 100% cpu use #63038

a819721810 opened this issue Apr 26, 2024 · 0 comments
Labels
potential bug To be reviewed by developers and confirmed/rejected.

Comments

@a819721810
Copy link

a819721810 commented Apr 26, 2024 •
edited

i have a local table and cluster table:

CREATE TABLE user_6.test
(

    `date` Date,

    `code` String,

    `date_time` DateTime('Asia/Shanghai'),

    `time_int` Nullable(Int32),

    `tims_int` Nullable(Int32),

    `T_singv_p_0_25` Nullable(Float64),

    `T_singv_p_1_25` Nullable(Float64),

    `T_singv_p_2_25` Nullable(Int32),

    `T_singv_p_3_25` Nullable(Float64),

    `T_singv_p_4_25` Nullable(Int32),

    `T_singv_p_5_25` Nullable(Int32),

    `T_singv_p_6_25` Nullable(Int32),

    `T_singv_p_7_25` Nullable(Float64),

    `T_singv_p_8_25` Nullable(Float64),

    `T_mvar_order_0_25` Nullable(Int64),

    `T_mvar_order_1_25` Nullable(Int64),

    `T_mvar_order_2_25` Nullable(Int64),

    `T_mvar_order_3_25` Nullable(Int64),

    `T_mvar_order_4_25` Nullable(Int64),

    `T_mvar_order_5_25` Nullable(Int64),

    `T_mvar_order_6_25` Nullable(Int64),

    `T_mvar_order_7_25` Nullable(Int64),

    `T_mvar_order_8_25` Nullable(Int64),

    `T_mvar_order_9_25` Nullable(Int64),

    `T_mvar_order_10_25` Nullable(Int64),

    `T_mvar_order_11_25` Nullable(Int64),

    `T_mvar_order_12_25` Nullable(Int64),

    `T_mvar_order_13_25` Nullable(Int64),

    `T_mvar_order_14_25` Nullable(Int64),

    `T_mvar_order_15_25` Nullable(Int64),

    `T_mvar_order_16_25` Nullable(Int64),

    `T_mvar_order_17_25` Nullable(Int64),

    `T_mvar_order_18_25` Nullable(Int64),

    `T_mvar_order_19_25` Nullable(Int64),

    `T_mvar_order_20_25` Nullable(Int64),

    `T_mvar_order_21_25` Nullable(Int64),

    `T_mvar_order_22_25` Nullable(Int64),

    `T_mvar_order_23_25` Nullable(Int64),

    `T_mvar_zbtk_0_25` Nullable(Int64),

    `T_mvar_zbtk_1_25` Nullable(Int64),

    `T_mvar_zbtk_2_25` Nullable(Int64),

    `T_mvar_zbtk_3_25` Nullable(Int64),

    `T_mvar_zbtk_4_25` Nullable(Float64),

    `T_mvar_zbtk_5_25` Nullable(Float64)
)
ENGINE = Distributed('test_cb_cluster',
 'user_6',
 'test_local',
 hiveHash(code))


CREATE TABLE user_6.test_local
(

    `date` Date,

    `code` String,

    `date_time` DateTime('Asia/Shanghai'),

    `time_int` Nullable(Int32),

    `tims_int` Nullable(Int32),

    `T_singv_p_0_25` Nullable(Float64),

    `T_singv_p_1_25` Nullable(Float64),

    `T_singv_p_2_25` Nullable(Int32),

    `T_singv_p_3_25` Nullable(Float64),

    `T_singv_p_4_25` Nullable(Int32),

    `T_singv_p_5_25` Nullable(Int32),

    `T_singv_p_6_25` Nullable(Int32),

    `T_singv_p_7_25` Nullable(Float64),

    `T_singv_p_8_25` Nullable(Float64),

    `T_mvar_order_0_25` Nullable(Int64),

    `T_mvar_order_1_25` Nullable(Int64),

    `T_mvar_order_2_25` Nullable(Int64),

    `T_mvar_order_3_25` Nullable(Int64),

    `T_mvar_order_4_25` Nullable(Int64),

    `T_mvar_order_5_25` Nullable(Int64),

    `T_mvar_order_6_25` Nullable(Int64),

    `T_mvar_order_7_25` Nullable(Int64),

    `T_mvar_order_8_25` Nullable(Int64),

    `T_mvar_order_9_25` Nullable(Int64),

    `T_mvar_order_10_25` Nullable(Int64),

    `T_mvar_order_11_25` Nullable(Int64),

    `T_mvar_order_12_25` Nullable(Int64),

    `T_mvar_order_13_25` Nullable(Int64),

    `T_mvar_order_14_25` Nullable(Int64),

    `T_mvar_order_15_25` Nullable(Int64),

    `T_mvar_order_16_25` Nullable(Int64),

    `T_mvar_order_17_25` Nullable(Int64),

    `T_mvar_order_18_25` Nullable(Int64),

    `T_mvar_order_19_25` Nullable(Int64),

    `T_mvar_order_20_25` Nullable(Int64),

    `T_mvar_order_21_25` Nullable(Int64),

    `T_mvar_order_22_25` Nullable(Int64),

    `T_mvar_order_23_25` Nullable(Int64),

    `T_mvar_zbtk_0_25` Nullable(Int64),

    `T_mvar_zbtk_1_25` Nullable(Int64),

    `T_mvar_zbtk_2_25` Nullable(Int64),

    `T_mvar_zbtk_3_25` Nullable(Int64),

    `T_mvar_zbtk_4_25` Nullable(Float64),

    `T_mvar_zbtk_5_25` Nullable(Float64)
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{uuid}/{shard}',
 '{replica}')
PARTITION BY toYYYYMM(date)
PRIMARY KEY (date,
 code)
ORDER BY (date,
 code,
 date_time)
SETTINGS index_granularity = 8192

table insert sql:

INSERT INTO user_6.test(`date`, `code`, `date_time`, `time_int`, `tims_int`, `T_singv_p_0_25`, `T_singv_p_1_25`, `T_singv_p_2_25`, `T_singv_p_3_25`, `T_singv_p_4_25`, `T_singv_p_5_25`, `T_singv_p_6_25`, `T_singv_p_7_25`, `T_singv_p_8_25`, `T_mvar_order_0_25`, `T_mvar_order_1_25`, `T_mvar_order_2_25`, `T_mvar_order_3_25`, `T_mvar_order_4_25`, `T_mvar_order_5_25`, `T_mvar_order_6_25`, `T_mvar_order_7_25`, `T_mvar_order_8_25`, `T_mvar_order_9_25`, `T_mvar_order_10_25`, `T_mvar_order_11_25`, `T_mvar_order_12_25`, `T_mvar_order_13_25`, `T_mvar_order_14_25`, `T_mvar_order_15_25`, `T_mvar_order_16_25`, `T_mvar_order_17_25`, `T_mvar_order_18_25`, `T_mvar_order_19_25`, `T_mvar_order_20_25`, `T_mvar_order_21_25`, `T_mvar_order_22_25`, `T_mvar_order_23_25`, `T_mvar_zbtk_0_25`, `T_mvar_zbtk_1_25`, `T_mvar_zbtk_2_25`, `T_mvar_zbtk_3_25`, `T_mvar_zbtk_4_25`, `T_mvar_zbtk_5_25`) VALUES ('2019-01-02', '000002', '2019-01-02 09:20:03', 33603000, 33601680, NULL, 0.0004198152812762146, 1, NULL, NULL, NULL, NULL, -0.09045801526717556, 0.1114738805970148, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL), ('2019-01-02', '000002', '2019-01-02 09:20:06', 33606000, 33603900, 0, 0.0004198152812762146, 1, NULL, 0, 0, 0, -0.09045801526717556, 0.1114738805970148, 0, 0, 0, 0, 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, NULL, NULL, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, NULL, NULL), ('2019-01-02', '000002', '2019-01-02 09:20:09', 33609000, 33603900, 0, 0.0004198152812762146, 1, NULL, 0, 0, 0, -0.09045801526717556, 0.1114738805970148, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)

it has 174214400 rows, all 8G size in disk， i exec:

alter table user_6.test_local on cluster cb_cluster update T_mvar_zbtk_4_25 = null where isNaN(T_mvar_zbtk_4_25)

even if i use:

alter table user_6.test_local on cluster cb_cluster update T_mvar_zbtk_4_25 = null where isNaN(T_mvar_zbtk_4_25) SETTINGS max_threads =1

where use 24.4.1.173(official build),, it will stuck by use 100% cpu, and has wrong info, and slow:
2024.04.26 14:14:20.909326 [ 61325 ] {} ServerErrorHandler: Poco::Exception. Code: 1000, e.code() = 107, Net Exception: Socket is not connected, Stack trace (when copying this message, always include the lines below):

  1. Poco::Net::SocketImpl::error(int, String const&) @ 0x0000000014caa672
  2. Poco::Net::SocketImpl::peerAddress() @ 0x0000000014caccd6
  3. DB::HTTPServerRequest::HTTPServerRequest(std::shared_ptrDB::IHTTPContext, DB::HTTPServerResponse&, Poco::Net::HTTPServerSession&, StrongTypedef<unsigned long, ProfileEvents::EventTag> const&) @ 0x000000001238ace9
  4. DB::HTTPServerConnection::run() @ 0x0000000012389448
  5. Poco::Net::TCPServerConnection::start() @ 0x0000000014caedb2
  6. Poco::Net::TCPServerDispatcher::run() @ 0x0000000014cafbf9
  7. Poco::PooledThread::run() @ 0x0000000014da8361
  8. Poco::ThreadImpl::runnableEntry(void*) @ 0x0000000014da68fd
  9. ? @ 0x00007f17f84a4ac3
  10. ? @ 0x00007f17f8536850
    (version 24.4.1.173 (official build))
    image

where use 23.12.1.553 (official build), it will stuck by use 50% cpu, it normal and fast:
image
@a819721810 a819721810 added the potential bug To be reviewed by developers and confirmed/rejected. label Apr 26, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
potential bug To be reviewed by developers and confirmed/rejected.
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@a819721810