table function input<\/a><\/noindex>.<\/p>\n<\/p>\n
<\/p>\n
Another anti-pattern is micro-sharding. For instance, you need to shard your data and you have 5 servers, but tomorrow there will be 6 servers. And you wonder how to rebalance these data. Instead, you break it not into 5 shards, but into 1,000 shards. Then you map each of these micro-shards to a separate server. This means you could end up with, for example, 200 ClickHouse instances on one server, each on separate ports or separate databases. <\/p>\n
<\/p>\n
<\/p>\n
But this is not very good in ClickHouse. Because even one ClickHouse instance tries to use all available server resources to process a single query. For instance, if you have a server with, say, 56 CPU cores, and you execute a query that runs for one second, it will utilize 56 cores. However, if you have 200 ClickHouse instances on one server, it will start 10,000 threads. Overall, this will lead to significant issues.<\/p>\n
<\/p>\n
Another reason is that the workload distribution across these instances will be uneven. Some will finish earlier, some later. If all this were happening in one instance, ClickHouse would manage to distribute the data across the threads correctly by itself. <\/p>\n
<\/p>\n
And yet another reason is that you will have inter-processor communication over TCP. Data will need to be serialized and deserialized, and managing such a large number of micro-shards would be highly inefficient.<\/p>\n
<\/p>\n
<\/p>\n
Another anti-pattern, although it is hard to call it that, is having a large amount of pre-aggregation.<\/p>\n
<\/p>\n
In general, pre-aggregation is beneficial. If you had a billion rows, aggregated them down to 1,000 rows, and now the query runs instantly, that\u2019s great. This is indeed feasible. For this, ClickHouse even has a special table type, AggregatingMergeTree, which performs incremental aggregation as data is inserted. <\/p>\n
<\/p>\n
However, there are cases when you think we are going to aggregate data like this and also aggregate data like that. And in some nearby department, I wouldn't want to say which one, they use SummingMergeTree tables to sum by primary key, using about 20 different columns as the primary key. For the sake of privacy, I changed the names of some columns, but that's more or less how it is.<\/p>\n
<\/p>\n
<\/p>\n
And such problems arise. Firstly, the volume of data doesn't decrease significantly. For example, it decreases by three times. Three times would be a good price to allow for unlimited analytics opportunities that arise when your data is unaggregated. If the data is aggregated, you get just miserable statistics instead of analytics. <\/p>\n
<\/p>\n
And what is particularly annoying? The fact that those people from the neighboring department come and sometimes ask to add another column to the primary key. That is, we aggregated the data like this, but now we want a little more. But in ClickHouse, there is no way to alter the primary key. So we have to write some scripts in C++. And I don't like scripts, even if they are in C++.<\/p>\n
<\/p>\n
And if you look at the purpose for which ClickHouse was created, unaggregated data is precisely the scenario it was born for. If you are using ClickHouse for unaggregated data, then you are doing everything right. If you are aggregating, then it's sometimes forgivable. <\/p>\n
<\/p>\n
<\/p>\n
Another interesting case is infinite loop queries. I sometimes log into some production server and look at show processlist. And every time I discover that something terrible is happening. <\/p>\n
<\/p>\n
For example, like this. It is immediately clear that everything could have been executed in one query. Just write url in and the list.<\/p>\n
<\/p>\n
<\/p>\n
Why is having many such queries in an infinite loop bad? If the index is not used, you will have many passes over the same data. But if the index is used, for example, if you have a primary key by ru and you write url = something. And you think that it will read one url exactly from the table, everything will be fine. But in reality, no. Because ClickHouse processes everything in batches. <\/p>\n
<\/p>\n
When it needs to read a certain range of data, it reads a bit more because the index in ClickHouse is sparse. This index does not allow finding a single individual row in the table, only some range. Data is compressed in blocks. To read one row, you need to take the whole block and decompress it. If you run a lot of queries, you will have many overlaps like this, and a lot of work will be duplicated.<\/p>\n
<\/p>\n
<\/p>\n
As a bonus, it's worth noting that in ClickHouse, you shouldn't be afraid to pass even megabytes or even hundreds of megabytes in the IN clause. From our experience, if we pass a bunch of values in an IN clause in MySQL, for example, passing 100 megabytes of some numbers, MySQL consumes 10 gigabytes of memory, and nothing else happens; everything works poorly. <\/p>\n
<\/p>\n
Secondly, in ClickHouse, if your queries use the index, it is never slower than a full scan, i.e., if you need to read almost the entire table, it will proceed sequentially and read the whole table. In general, it will take care of itself.<\/p>\n
<\/p>\n
However, there are some complexities. For instance, the IN clause with a subquery does not use the index. But that's our problem, and we need to fix it. There's nothing fundamental about it. We'll fix it. <\/p>\n
<\/p>\n
Another interesting thing is that if you have a very long query and distributed query processing is going on, that very long query will be sent to each server without compression. For example, 100 megabytes and 500 servers. Accordingly, you will transmit 50 gigabytes over the network. It will be sent, and then it will all execute successfully.<\/p>\n
<\/p>\n
* is already in use; everything is fixed, as promised.<\/p>\n
<\/p>\n
<\/p>\n
It's also quite common for queries to come from APIs. For example, if you created your own service. If your service is in demand, you opened an API, and literally two days later, you see that something strange is happening. Everything is overloaded, and some terrible queries are coming in that should never have happened. <\/p>\n
<\/p>\n
The solution here is simple. If you opened an API, you will have to restrict it. For example, introduce quotas. There are no other reasonable options. Otherwise, someone will immediately write a script, and there will be problems. <\/p>\n
<\/p>\n
ClickHouse has a special feature \u2013 quota counting. You can pass your own quota key, for example, a user\u2019s internal identifier. Quotas will be counted independently for each of them. <\/p>\n
<\/p>\n
<\/p>\n
Now here\u2019s another interesting thing. This is manual replication. <\/p>\n
<\/p>\n
I know many cases where, despite ClickHouse having built-in replication support, people replicate ClickHouse manually.<\/p>\n
<\/p>\n
What's the principle? You have a data processing pipeline that works independently, for example, across different data centers. You write the same data in the same way to ClickHouse. However, practice shows that the data will still diverge due to some peculiarities in your code. I hope it\u2019s not in yours. <\/p>\n
<\/p>\n
And periodically, you will still have to synchronize manually. For instance, once a month, admins perform an rsync.<\/p>\n
<\/p>\n
In reality, it's much easier to use the built-in replication in ClickHouse. But there could be some contraindications because it requires using ZooKeeper. I won't say anything bad about ZooKeeper; it's a working system, but sometimes people avoid it due to a Java phobia since ClickHouse is a well-designed system written in C++, which works excellently. And ZooKeeper is in Java. Thus, they might prefer to use manual replication instead. <\/p>\n
<\/p>\n
<\/p>\n
ClickHouse is a practical system. It considers your needs. If you have manual replication, you can create a Distributed table that looks at your manual replicas and performs failover between them. There\u2019s even a special option that allows you to avoid flaps, even if your replicas systematically diverge.<\/p>\n
<\/p>\n
<\/p>\n
Further issues may arise if you use primitive table engines. ClickHouse is like a constructor with a variety of table engines available. For all serious cases, as noted in the documentation, use tables from the MergeTree family. All others are just for specific cases or testing.<\/p>\n
<\/p>\n
In the MergeTree table, it's not mandatory to have a specific date and time. You can still use it. If there's no date and time, specify the default as the year 2000. This will work without demanding resources. <\/p>\n
<\/p>\n
In the new version of the server, you can even specify to have custom partitioning without a partition key. It will be the same. <\/p>\n
<\/p>\n
<\/p>\n
On the other hand, primitive table engines can be used. For example, load the data once and see, manipulate, and delete it. You can use Log.<\/p>\n
<\/p>\n
Or storing small volumes for intermediate processing \u2013 that\u2019s StripeLog or TinyLog.<\/p>\n
<\/p>\n
Memory can be used if the data volume is small and you just want to manipulate something in memory. <\/p>\n
<\/p>\n
<\/p>\n
ClickHouse does not particularly like over-normalized data. <\/p>\n
<\/p>\n
Here\u2019s a typical example. This is a huge number of URLs. You put them in a separate table. Then you decided to do a JOIN with them, but it usually won\u2019t work because ClickHouse supports only Hash JOIN. If there isn\u2019t enough memory for the large amount of data that needs to be joined, the JOIN won\u2019t be possible.* <\/p>\n
<\/p>\n
If the data has high cardinality, don\u2019t worry, store it in a denormalized form, URLs directly in place in the main table. <\/p>\n
<\/p>\n
* Now ClickHouse also has merge join, and it works when the intermediate data doesn\u2019t fit into memory. But this is inefficient, and the recommendation remains valid.<\/p>\n
<\/p>\n
<\/p>\n
Here are a couple more examples, but I\u2019m already doubting whether they are antipatterns or not. <\/p>\n
<\/p>\n
ClickHouse has one known drawback. It doesn\u2019t support updates.* In some sense, this is even good. If you have important data, such as accounting, then no one will be able to send them because there are no updates.<\/p>\n
<\/p>\n
* Support for update and delete in batch mode has been added for a long time now.<\/p>\n
<\/p>\n
But there are some special methods that allow updates to happen in the background. For example, ReplaceMergeTree tables. They perform updates during background merges. You can force this using optimize table. But don\u2019t do this too often as it will completely rewrite the partition. <\/p>\n
<\/p>\n
Distributed JOINs in ClickHouse are also poorly handled by the query planner. <\/p>\n
<\/p>\n
Poorly, but sometimes it\u2019s okay.<\/p>\n
<\/p>\n
Using ClickHouse only to read data back with select*.<\/p>\n
<\/p>\n
I wouldn't recommend using ClickHouse for heavy computations. However, that's not entirely true, because we're already moving away from that recommendation. Recently, we added the capability to apply machine learning models in ClickHouse \u2013 Catboost. This worries me because I think, \"What a nightmare. How many cycles per byte does that produce!?\" I really hate to use cycles on bytes. <\/p>\n
<\/p>\n
<\/p>\n
But don't be afraid, install ClickHouse, everything will be fine. If anything, we have a community. By the way, the community is you. And if you have any issues, you can at least pop into our chat, and hopefully you'll get help. <\/p>\n
<\/p>\n
Questions<\/p>\n
<\/p>\n
Thank you for the talk! Where can I complain about ClickHouse crashing?<\/em><\/p>\n<\/p>\n
You can complain to me personally right now. <\/p>\n
<\/p>\n
I recently started using ClickHouse. I immediately crashed the CLI interface.<\/em><\/p>\n<\/p>\n
You\u2019re lucky. <\/p>\n
<\/p>\n
A little later I crashed the server with a small select.<\/em><\/p>\n<\/p>\n
You have a talent. <\/p>\n
<\/p>\n
I opened a bug on GitHub, but it was ignored.<\/em> <\/p>\n<\/p>\n
Let's see. <\/p>\n
<\/p>\n
Alexey tricked me into giving a presentation by promising to tell how you compress data internally.<\/em><\/p>\n<\/p>\n
It's very simple. <\/p>\n
<\/p>\n
I realized that just yesterday. More specifics.<\/em> <\/p>\n<\/p>\n
There are no awful tricks. It's just block compression. LZ4 is used by default, but you can enable ZSTD*. Blocks range from 64 kilobytes to 1 megabyte.<\/p>\n
<\/p>\n
* There is also support for specialized compression codecs that can be used in conjunction with other algorithms.<\/p>\n
<\/p>\n
Are there just raw data in the blocks?<\/em><\/p>\n<\/p>\n
Not exactly raw. There are arrays. If you have a numeric column, the numbers are packed sequentially into an array. <\/p>\n
<\/p>\n
Got it.<\/em> <\/p>\n<\/p>\n
Alexey, the example with uniqExact over IP addresses, i.e., that uniqExact is calculated slower over strings than over numbers, and so on. What if we pull a fast one and cast during reading? I mean, you mentioned that it doesn\u2019t differ much on disk. If we read strings from the disk and cast them, will our aggregates be faster or not? Or will we still not see a significant gain here? It seems to me you tested this, but for some reason didn\u2019t mention it in the benchmark.<\/em> <\/p>\n<\/p>\n
I think it will be slower than without casting. In this case, the IP address needs to be parsed from the string. In ClickHouse, of course, IP address parsing is also optimized. We made a big effort, but you have the numbers written in ten-thousandth form. Very inconvenient. On the other hand, the uniqExact function will work slower on strings not only because they are strings, but also because a different algorithm specialization is chosen. Strings are simply processed differently.<\/p>\n
<\/p>\n
But what if we take a more primitive data type? For example, we wrote the user ID that we have in as a string, and then cast it, will that be easier or not?<\/em><\/p>\n<\/p>\n
I doubt it. I think it will even be worse, because parsing numbers is indeed a serious problem. It seems to me that this colleague even had a talk about how difficult it is to parse numbers in ten-thousandth form, or maybe not. <\/p>\n
<\/p>\n
Alexey, thank you very much for the presentation! And also, thank you for ClickHouse! I have a question about the plans. Is there a plan to add a feature for partially updating dictionaries?<\/em><\/p>\n<\/p>\n
That is, partial reload?<\/p>\n
<\/p>\n
Yes, yes. Like the ability to specify a MySQL field, that is, update after so that only these data are loaded if the dictionary is very large.<\/em><\/p>\n<\/p>\n
Very interesting feature. And it seems to me that someone suggested it in our chat. Maybe it was even you. <\/p>\n
<\/p>\n
I don\u2019t think so.<\/em><\/p>\n<\/p>\n
Great, so now it\u2019s two requests. And we can start working on it at our own pace. But I want to warn you right away that this feature is quite simple to implement. I mean, you basically just need to write the version number in the table and then specify: version less than this one. And this means that we will most likely suggest doing this to enthusiasts. Are you an enthusiast? <\/p>\n
<\/p>\n
Yes, but unfortunately, not in C++.<\/em><\/p>\n<\/p>\n
Do your colleagues know how to code in C++? <\/p>\n
<\/p>\n
I will find someone.<\/em><\/p>\n<\/p>\n
Great.<\/p>\n
<\/p>\n
* the feature was added two months after the presentation \u2013 it was developed by the person who asked the question and sent his pull request<\/a><\/noindex>.<\/p>\n<\/p>\n
Thank you!<\/em><\/p>\n<\/p>\n
Hello! Thank you for the presentation! You mentioned that ClickHouse utilizes all available resources very well. The presenter next to you from Luxoft talked about his solution for the Russian Post. He said they really liked ClickHouse, but they didn't use it instead of their main competitor because it consumed all the CPU. They couldn't integrate it into their architecture, into their ZooKeeper with Docker containers. Is there a way to limit ClickHouse so that it doesn't consume everything available to it?<\/em><\/p>\n<\/p>\n
Yes, it can be done very easily. If you want it to consume fewer cores, just write set max_threads = 1<\/code>. And that's it, it will execute queries on a single core. Moreover, different users can be given different settings. So there are no problems. And please let the colleagues from Luxoft know that it's unfortunate they didn't find this setting in the documentation. <\/p>\n<\/p>\n
Hello, Alexey! I would like to ask a question. I've heard several times that many are starting to use ClickHouse as a log storage. In your presentation, you mentioned that this shouldn't be done, i.e., long strings shouldn't be stored. What do you think about this?<\/em><\/p>\n<\/p>\n
First of all, logs are generally not long strings. Of course, there are exceptions. For example, a service written in Java throws an exception, it gets logged. And so in an endless loop, and it eventually fills up the hard drive space. The solution is very simple. If the strings are very long, just cut them off. And what does long mean? Tens of kilobytes is bad*.<\/p>\n
<\/p>\n
* In the latest versions of ClickHouse, \"adaptive index granularity\" has been introduced, which largely addresses the issue of storing long strings.<\/p>\n
<\/p>\n
And is a kilobyte normal?<\/em><\/p>\n<\/p>\n
Normal. <\/p>\n
<\/p>\n
Hello! Thank you for the presentation! I already asked this in the chat, but I don't remember if I got a response. Is there any plan to expand the WITH section like CTE?<\/em><\/p>\n<\/p>\n
Not yet. The WITH section is somewhat informal. It's just a small feature for us.<\/p>\n
<\/p>\n
I understand. Thank you!<\/em><\/p>\n<\/p>\n
Thank you for the presentation! Very interesting! A global question. Is there any plan to implement, perhaps, some kind of placeholders for data deletion modification?<\/em><\/p>\n<\/p>\n
Definitely. This is our first task in our queue. We are currently actively considering how to do everything correctly. It's time to start typing*.<\/p>\n
<\/p>\n
* pressed keys on the keyboard and did everything.<\/p>\n
<\/p>\n
Will this affect system performance or not? Will the insertion be as fast as it is now?<\/em><\/p>\n<\/p>\n
It's possible that the deletes and updates themselves will be very heavy, but this won't affect the performance of selects and inserts.<\/p>\n
<\/p>\n
And one small question. In the presentation, you talked about the primary key. Accordingly, we have partitioning, which is monthly by default, correct? And when we set a date range that falls within a month, it only reads that partition, right?<\/em><\/p>\n<\/p>\n
Yes.<\/p>\n
<\/p>\n
Here's a question. If we can't designate any primary key, is it correct to use the 'Date' field for it so that the underlying data has less disruption, allowing it to be more orderly? If you have no range queries and can't choose any primary key, should we include the date in the primary key?<\/em><\/p>\n<\/p>\n
Yes.<\/p>\n
<\/p>\n
Perhaps, it makes sense to include a field in the primary key where the data will compress better if it is sorted by that field. For example, the user ID. If a user frequently visits the same site, then you would include the user ID and the timestamp. This way, your data will compress better. Regarding the date, if you truly never have range queries by dates, then you don't need to include the date in the primary key. <\/p>\n
<\/p>\n
Okay, thank you very much!<\/em><\/p>\nSource: habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"\u0422\u0430\u043a \u043a\u0430\u043a ClickHouse \u044f\u0432\u043b\u044f\u0435\u0442\u0441\u044f \u0441\u043f\u0435\u0446\u0438\u0430\u043b\u0438\u0437\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u043e\u0439 \u0441\u0438\u0441\u0442\u0435\u043c\u043e\u0439, \u043f\u0440\u0438 \u0435\u0433\u043e \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0438 \u0432\u0430\u0436\u043d\u043e \u0443\u0447\u0438\u0442\u044b\u0432\u0430\u0442\u044c \u043e\u0441\u043e\u0431\u0435\u043d\u043d\u043e\u0441\u0442\u0438 \u0435\u0433\u043e \u0430\u0440\u0445\u0438\u0442\u0435\u043a\u0442\u0443\u0440\u044b. \u0412 \u044d\u0442\u043e\u043c \u0434\u043e\u043a\u043b\u0430\u0434\u0435 \u0410\u043b\u0435\u043a\u0441\u0435\u0439 \u0440\u0430\u0441\u0441\u043a\u0430\u0436\u0435\u0442 \u043e \u043f\u0440\u0438\u043c\u0435\u0440\u0430\u0445 \u0442\u0438\u043f\u0438\u0447\u043d\u044b\u0445 \u043e\u0448\u0438\u0431\u043e\u043a \u043f\u0440\u0438 \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u043d\u0438\u0438 ClickHouse, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u043c\u043e\u0433\u0443\u0442 \u043f\u0440\u0438\u0432\u0435\u0441\u0442\u0438 \u043a \u043d\u0435\u044d\u0444\u0444\u0435\u043a\u0442\u0438\u0432\u043d\u043e\u0439 \u0440\u0430\u0431\u043e\u0442\u0435. \u041d\u0430 \u043f\u0440\u0438\u043c\u0435\u0440\u0430\u0445 \u0438\u0437 \u043f\u0440\u0430\u043a\u0442\u0438\u043a\u0438 \u0431\u0443\u0434\u0435\u0442 \u043f\u043e\u043a\u0430\u0437\u0430\u043d\u043e, \u043a\u0430\u043a \u0432\u044b\u0431\u043e\u0440 \u0442\u043e\u0439 \u0438\u043b\u0438 \u0438\u043d\u043e\u0439 \u0441\u0445\u0435\u043c\u044b \u043e\u0431\u0440\u0430\u0431\u043e\u0442\u043a\u0438 \u0434\u0430\u043d\u043d\u044b\u0445 \u043c\u043e\u0436\u0435\u0442 \u0438\u0437\u043c\u0435\u043d\u0438\u0442\u044c \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c \u043d\u0430 \u043f\u043e\u0440\u044f\u0434\u043a\u0438. \u0412\u0441\u0435\u043c \u043f\u0440\u0438\u0432\u0435\u0442! \u041c\u0435\u043d\u044f \u0437\u043e\u0432\u0443\u0442 […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":91439,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-91438","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t
![\"Effective](\"\/wp-content\/uploads\/2020\/08\/8e42c1fffffcc964eacef240ea90bbc5.jpg\")
<\/p>\n