Maximum Columns in single table supported(ClickHouse Version Stable--1.1.54310 Stable)

[chaita...@motadata.com]

I have a table one with 230 columns in it at a time i am executing batch insert with 180000 insertion per minute gradually clickhouse memory consumption goes on increasing and after some point of time insertion stops and clickhouse throws DB::Exception: Too much parts. Merges are processing significantly slower than insert and does not allows insertion need to restart clickhouse and than it starts insertion once again..I also have a table with 20 columns i got no problem in it,it works fine without any issues with same batch insert as above and both tables are mergeTree Engine Table,so is it that if number of columns are more clickhouse performance degrades?? and what is maximum limit of number of columns in table which gives me max performance?? or which kind of engine should i use??? Please Kindly let me know i am stuck with it...

[Alex Zatelepin]

Hi,

Number of disk operations per INSERT statement indeed scales linearly with the number of columns. But tables with 230 columns should not be a problem (provided you don't insert too frequently) - at Yandex we have tables with several hundreds of columns.

Please provide the following information:

1. ClickHouse server version

2. Table schema

3. Rate of INSERT statements per second (should be approximately 1 per second).

4. Does the data in the single insert fall into one partition (month), or several.

5. Contents of the system.merges table when the 'too much parts' error starts to appear.

[chaita...@motadata.com]

Following are the details you asked for:

1. ClickHouse server version :  1.1.54292

2. Table schema :

 

schema name : default,

 

CREATE TABLE tbl_log_fortigate_log

(

    source_host String,

    message_uuid String,

    reason String,

    filter_name String,

    attack_context_id String,

    raw_message String,

    detection_method String,

    config_path String,

    remote String,

    vpn_type String,

    management_count Int64,

    control_sgsn String,

    application_control String,

    lan_incoming Int64,

    control_panel_address String,

    expiry String,

    transmitter_amac String,

    url_type String,

    profile String,

    community String,

    version String,

    destination_interface String,

    invalid_mac String,

    size String,

    sender String,

    virtual_access_point String,

    document_source String,

    assigned String,

    phase2_name String,

    signaling_packets Int64,

    ie_type Int64,

    activity String,

    rule_data String,

    virus String,

    threat_type String,

    routing_area_identification String,

    out_interface String,

    cloud_action String,

    address String,

    esp_transform String,

    connection_type String,

    remote_port Int64,

    sender_address String,

    attack_name String,

    unauthenticated_user_source String,

    receiver_address String,

    xauth_group String,

    unauthenticated_user String,

    ha_priority Int64,

    vpn_tunnel String,

    agent String,

    maximum_quota_allowed Int64,

    nat_source_port Int64,

    config_object String,

    authentication_protocol String,

    destination_ip String,

    source_interface String,

    user_interface String,

    keyword String,

    cloud_user String,

    used_bytes Int64,

    count Int64,

    user_gsn String,

    daemon String,

    quarantine_skip String,

    port Int64,

    name String,

    exchange String,

    wan_outgoing Int64,

    application_type String,

    network_service_api Int64,

    apscan String,

    interface String,

    control_plane_isr_downlink_ip_address String,

    old_web_profile String,

    duration Int64,

    category_description String,

    device_name String,

    rate Int64,

    source_port Int64,

    source_name String,

    severity String,

    received_packets Int64,

    profile_type String,

    destination_address String,

    carbon_copy String,

    license_used Int64,

    operating_country String,

    file_name String,

    certification_type String,

    destination_host String,

    station_count Int64,

    source_mac String,

    vpn String,

    in_spi String,

    service String,

    from_virtual_cluster Int64,

    file_filter String,

    data_type String,

    recipient String,

    end_user_address String,

    assign_ip String,

    gateway String,

    received_bytes Int64,

    xauth_user String,

    type String,

    ha_group Int64,

    switch_protocal String,

    banned_rule String,

    tunnel_ip String,

    source_ip String,

    license_limit String,

    virtual_cluster_state String,

    error_reason String,

    imei_sv String,

    dns_ip String,

    esp_authentication String,

    action String,

    user_ggsn String,

    url_filter_list String,

    vdom_name String,

    banned_source String,

    init String,

    override_table String,

    quota_exceeded String,

    method String,

    sync_type String,

    ip String,

    config_attribute String,

    priority Int64,

    frame_type String,

    local_ip String,

    out_spi String,

    field String,

    mobile_subscriber String,

    registry_type String,

    user_plane_sgsn String,

    device_interface String,

    sync_status String,

    sensitivity String,

    status String,

    quota_type String,

    physical_access String,

    signaling_bytes Int64,

    server String,

    international_mobile_subscriber_id String,

    security_mode String,

    sent_packets Int64,

    memory_usage Int64,

    local String,

    manufacturer String,

    file String,

    blocked Int64,

    filter_type String,

    peer String,

    source_network String,

    ad_group String,

    tunnel_type String,

    control_down_address String,

    application_category String,

    device_interface_name String,

    infected Int64,

    nat_destination_port Int64,

    address_type String,

    filter_category String,

    local_port Int64,

    os_version String,

    authentication_group String,

    lan_outgoing Int64,

    file_size Int64,

    url String,

    dns_name String,

    attack_context String,

    virtual_cluster Int64,

    deny_cause String,

    os_name String,

    facility String,

    used_packets Int64,

    ha_role String,

    subject String,

    access_point_name String,

    intercepted Int64,

    translation_type String,

    mode String,

    reference String,

    protocol Int64,

    remote_ip String,

    wan_incoming Int64,

    file_type String,

    control_address String,

    host String,

    firewall_server_name String,

    group String,

    level String,

    cpu Int64,

    user_data String,

    ip_type String,

    volume_bytes Int64,

    handshake String,

    config_country String,

    destination_country String,

    destination_port Int64,

    station_mac String,

    destination_name String,

    error String,

    sent_bytes Int64,

    total Int64,

    security String,

    attachment String,

    subtype String,

    direction String,

    control_ggsn String,

    peer_notification String,

    hbdn_reason String,

    accounting_state String,

    virtual_domain String,

    command String,

    contenttype String,

    application String,

    source_country String,

    utm_action String,

    application_profile String,

    user_location String,

    category Int64,

    user String,

    host_name String,

    banned_word String,

    event_date Date,

    event_time DateTime,

    _time Int32

) ENGINE = MergeTree(event_date, source_host, 8192);

 

3. Rate of INSERT statements per second (should be approximately 1 per second) : 3000-5000 rows/sec.

 

4. Does the data in the single insert fall into one partition (month), or several. : using batch insert in table.

 

5. Contents of the system.merges table when the 'too much parts' error starts to appear. : we don't have this table.

Thanks and Regards
Chaitas Shah

[Mikhail Filimonov]

On Wednesday, December 6, 2017 at 7:21:55 AM UTC+1, chaita...@motadata.com wrote:

4. Does the data in the single insert fall into one partition (month), or several. : using batch insert in table.

So you are sure that you send one INSERT statements with multiple values during a second, not a multiple separate inserts (even in one connection / session)?

event_date of records inserted together are from the same month? Not from many different months?

 

5. Contents of the system.merges table when the 'too much parts' error starts to appear. : we don't have this table.

That is a system table. It should always exists in ClickHouse.