den-crane

create table z(Browser String, Country UInt8, D Date, F Float64) Engine=MergeTree order by (Browser, Country, D);

insert into z  select toString(number%9999), number%11111, today()-7, 1 from numbers(100000000);
insert into z  select toString(number%9999), number%11111, today()-6, 1 from numbers(100000000);
insert into z  select toString(number%9999), number%11111, today()-5, 1 from numbers(100000000);
insert into z  select toString(number%9999), number%11111, today()-4, 1 from numbers(100000000);
insert into z  select toString(number%9999), number%11111, today()-3, 1 from numbers(100000000);
insert into z  select toString(number%9999), number%11111, today()-2, 1 from numbers(100000000);
insert into z  select toString(number%9999), number%11111, today()-1, 1 from numbers(100000000);

den-crane

<?xml version="1.0" ?>
<clickhouse>
    <profiles>
        <default>
            <async_insert>true</async_insert>
            <wait_for_async_insert>0</wait_for_async_insert>
            <async_insert_threads>32</async_insert_threads>
            <async_insert_max_data_size>20000000</async_insert_max_data_size>
            <async_insert_busy_timeout_ms>30000</async_insert_busy_timeout_ms>        
        </default>

den-crane

CH settings

cat /etc/clickhouse-server/users.d/max_query_size.xml
<?xml version="1.0" ?>
<yandex>
    <profiles>
        <default>
            <max_query_size>5242880</max_query_size>
        </default>

den-crane

No optimizations

create table my_tbl ( order_id int , order_date date ) 
engine = MergeTree 
order by order_id; 

insert into my_tbl select number, today() + intDiv(number, 1000000) from numbers(1e9);

select max(order_date) from my_tbl;

den-crane

-- SERVER A
create table test_r(a Int64) Engine=ReplicatedMergeTree('/clickhouse/{cluster}/tables/{table}','{replica}') order by a;
insert into  test_r select number from numbers(1e6);
create table test_d as test_r Engine=Distributed('replicated', dw,test_r);

select count() from test_r;
1000000

select count() from test_d;
1000000

den-crane

aarch64


ClickHouse client version 22.4.5.8.
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 22.4.5 revision 54455.

<clickhouse>
    <storage_configuration>

den-crane

CREATE TABLE data
( timestamp DateTime,
  host      String,
  repsonse_time      Int32
) ENGINE MergeTree() ORDER BY (host, timestamp);


CREATE table data_aggregated_to_five_min
( timestamp           DateTime,
  host      String,

den-crane

Denny Crane [Starship Captain at Altinity (NB,Canada)], [Dec 15, 2021 at 1:00:24 PM]:
create table x (order String, sku String) Engine = Memory;
insert into x values ('order1', 'banana') ('order1', 'bread')
insert into x values ('order2', 'banana') ('order2', 'bread')
insert into x values ('order3', 'banana') 
insert into x values ('order4', 'banana')  ('order4', 'milk') 
insert into x values ('order5', 'break')  ('order5', 'milk') 

select arrayJoin([g, g]), arrayElement(g.1, 1)
from 

den-crane

I have tested a dictionary with 500 attributes (short strings, up to 10 characters) and 1000000 rows.


python3 ddl.py |clickhouse-client
python3 data.py |clickhouse-client

select formatReadableSize(sum(bytes)) size, formatReadableSize(sum(data_uncompressed_bytes)) usize, sum(rows),count() part_count
from system.parts 
where active = 1 and table like 'dict_source' and database like '%'
group by database,table,partition

den-crane

create table sess (user_id Int64, session_created_time DateTime) Engine=Memory;

insert into sess select 1, toDateTime(now())+number*2 from numbers(10);
insert into sess select 2, toDateTime(now())+number*5 from numbers(2);
insert into sess select 3, toDateTime(now())+number*6 from numbers(1);


select user_id, arrayReduce('median', arraySlice(arrayDifference(arrayMap(i->toUnixTimestamp(i), (arraySort(groupArray(session_created_time))))),2)) median
from sess
group by user_id