clickhouse

clickhouse is a column oriented OLAP database. i’ve started using it about half a year ago. i’m impressed. earlier i’ve read about it on percona’s blog but did not fully grasp how performant it was. i’ve tried it when i wrestling with MariaDB’s query planner on table with ~100M rows got me tired and each query took minutes to finish. clickhosue still feels like magic – the data size went down from tens of GB to few GB, query execution time – from minutes to low seconds.

some useful queries:

get size of each of tables’ columns:

SELECT name,type,formatReadableSize(data_compressed_bytes) AS size_compressed, formatReadableSize(data_uncompressed_bytes) AS size_raw, floor(data_uncompressed_bytes/data_compressed_bytes,2) as ratio, compression_codec from system.columns where table ='t';

get on-disk size of all tables [src]:

select concat(database, '.', table)                         as table,
       formatReadableSize(sum(bytes))                       as size,
       sum(rows)                                            as rows,
       max(modification_time)                               as latest_modification,
       sum(bytes)                                           as bytes_size,
       any(engine)                                          as engine,
       formatReadableSize(sum(primary_key_bytes_in_memory)) as primary_keys_size
from system.parts
where active
group by database, table
order by bytes_size desc;

Leave a Reply

Your email address will not be published. Required fields are marked *

 

(Spamcheck Enabled)