{"id":80775,"date":"2020-05-08T13:42:47","date_gmt":"2020-05-08T11:42:47","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/clickhouse-dlya-prodvinutyh-polzovatelej-v-voprosah-i-otvetah"},"modified":"2020-05-08T13:42:47","modified_gmt":"2020-05-08T11:42:47","slug":"clickhouse-dlya-prodvinutyh-polzovatelej-v-voprosah-i-otvetah","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/clickhouse-dlya-prodvinutyh-polzovatelej-v-voprosah-i-otvetah","title":{"rendered":"ClickHouse for advanced users in Q&amp;A","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p>In April, Avito engineers were preparing for an online meetup with ClickHouse's chief developer Alexey Milovidov and Kirill Shvakov, a Golang developer from Integros. They discussed how we use the database management system and the challenges we face. <\/p>\n<p><\/p>\n<p>Based on the meeting, we compiled an article with expert answers to our and the audience's questions about backups, data resharding, external dictionaries, the Golang driver, and ClickHouse version updates. It can be useful for developers who are already actively working with Yandex's DBMS and are interested in its present and future. By default, the answers are from Alexey Milovidov unless stated otherwise. <\/p>\n<p><\/p>\n<p>Caution, there is a lot of text under the cut. We hope the content with the questions will help you navigate.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"ClickHouse for advanced users in Q&amp;A\" src=\"\/wp-content\/uploads\/2020\/05\/242b1d8d002fe115614435c242297fd2.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<h2 id=\"soderzhanie\">Content<\/h2>\n<p><\/p>\n<ul>\n<li><noindex><a rel=\"nofollow\" href=\"#old-data\">ClickHouse is constantly being updated, but our data is not. What should we do about it?<\/a><\/noindex> <\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#backup-best-practicies\">What are the best practices for backing up data from ClickHouse at the moment?<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#replication\">Will it be possible to organize controlled lag of replicas in the traffic?<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#soooo-changeable\">What should we do if the table structure has changed?<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#resharding-best-practices\">What are the current best practices for data resharding?<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#clickhouse-copier\">ClickHouse has a utility called clickhouse-copier. Can you tell us about it?<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#resharding-tool\">You had a pilot feature called resharding. What happened to it?<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#move-to-slow-disk\">Is it possible to consolidate all data parts into one before moving them to slow disks?<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#up-to-date\">How do you migrate to new versions of ClickHouse if there is no opportunity to check compatibility in advance?<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#kill-query\">Kill query is supposed to kill queries but it doesn\u2019t seem to work. Why is that?<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#reading-time\">How do you calculate response time under read load?<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#pimp-my-clickhouse\">What should be tuned in ClickHouse to cache more data?<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#storage-configuration\">How can we configure storage_configuration for in-memory storage?<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#low-cardinality\">Up to what number of unique values is Low Cardinality effective?<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#fulltext-search\">What are the best practices for full-text search on a table with five billion rows?<\/a><\/noindex> <\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#hello-and-welcome\">How to best organize access to ClickHouse for a large number of users?<\/a><\/noindex> <\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#smorgasbord\">Can we send the results of one query to ten clients?<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#asynchronous\">How to handle asynchronous operations and materialized views?<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#dashboard\">ClickHouse has many logs. How can I see everything happening with the server in real-time?<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#zen\">How to manage merges so the server doesn't crash with OOM?<\/a><\/noindex> <\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#go\">How will the development of the Golang driver for ClickHouse proceed?<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#lazy-load\">The external dictionary does not load after a reboot with the lazy_load setting enabled. What should be done?<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#reload-dictionaries\">What to do if system reload dictionaries does not load any of the multiple dictionaries if even one of them fails with an error?<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#connection\">Is there a way to configure credentials in the ClickHouse config without exposing them during errors?<\/a><\/noindex><\/li>\n<li><noindex><a rel=\"nofollow\" href=\"#zoom-backgrounds\">Bonus: Zoom backgrounds from hangouts<\/a><\/noindex><\/li>\n<\/ul>\n<p><\/p>\n<blockquote><p>If you don't want to read the text, you can watch the recording of the gatherings. <noindex><a rel=\"nofollow\" href=\"https:\/\/www.youtube.com\/watch?v=n1tm4j4W8ZQ&amp;t=8147s\">on our YouTube channel<\/a><\/noindex>. Timecodes are in the first comment below the video.<\/p><\/blockquote>\n<p><\/p>\n<h2 id=\"anchorold-dataanchorclickhouse-postoyanno-obnovlyaetsya-a-nashi-dannyenbsp-net-chto-snbspetim-delat\"><noindex><a rel=\"nofollow\" name=\"old-data\"><\/a><\/noindex>ClickHouse is constantly updating, but our data is not. What should we do about it?<\/h2>\n<p><\/p>\n<blockquote><p>ClickHouse is constantly updating, while our data that was processed with optimize final is not updating and remains in the backup. <\/p>\n<p>Suppose we encountered some problem and lost data. We decided to recover it, and it turned out that the old partitions stored on backup servers are significantly different from the currently used version of ClickHouse. What should we do in such a situation, and is it possible?<\/p><\/blockquote>\n<p>The situation where you restore data from a backup in an old format, and it does not connect in the new version, is impossible. We ensure that the data format in ClickHouse always remains backward compatible. This is much more important than backward compatibility in terms of functionality, if the behavior of some rarely used feature has changed. The data stored on disk must always be readable by the new version of ClickHouse. This is a rule. <\/p>\n<p><\/p>\n<h2 id=\"anchorbackup-best-practiciesanchorkakie-luchshie-praktiki-est-nanbspdannyy-moment-ponbsprezervnomu-kopirovaniyu-dannyh-iznbspclickhouse\"><noindex><a rel=\"nofollow\" name=\"backup-best-practicies\"><\/a><\/noindex>What are the best practices for backing up data from ClickHouse at the moment?<\/h2>\n<p><\/p>\n<blockquote><p>How to back up considering we have optimize final operations, a huge database in terabytes, and data that is updated, say, over the last three days, with no further procedures performed on them? <\/p>\n<p>We can hack together our own solution and write on Bash: collect these backups like this and that. Perhaps we don't need to hack anything, and the wheel has already been invented? <\/p><\/blockquote>\n<p>First, regarding best practices. My colleagues always advise in response to questions about backups to remind about the 'Yandex.Cloud' service, where this task has already been solved. So use it if you have the opportunity. <\/p>\n<p><\/p>\n<p>There is no complete solution, fully integrated into ClickHouse, for backups. There are some templates that can be used. To obtain a complete solution, either some manual work will be required or wrappers in the form of scripts will need to be created.<\/p>\n<p><\/p>\n<p>I'll start with the simplest solutions and end with the most complex ones, depending on the volume of data and the size of the cluster. The larger the cluster, the more complicated the solution becomes.<\/p>\n<p><\/p>\n<p>If the data table is only a few gigabytes, the backup can be done like this: <\/p>\n<p><\/p>\n<ol>\n<li>Save the table definitions, that is, the metadata \u2014 <strong>show create table<\/strong>.<\/li>\n<li>Create a dump using the ClickHouse client \u2014 <strong>select<\/strong> * <strong>from table<\/strong> into a file. By default, you'll get a file in TabSeparated format. If you want something more efficient, you can use the Native format. <\/li>\n<\/ol>\n<p><\/p>\n<p>If the data volume is larger, the backup will take more time and occupy more space. This is called a logical backup, and it is not tied to ClickHouse's data format. If it exists, in an extreme case, you can take the backup and load it into MySQL for recovery. <\/p>\n<p><\/p>\n<p>For more advanced cases, ClickHouse has a built-in capability to create snapshots of partitions in the local file system. This feature is available in the form of a query. <strong>alter table freeze partition<\/strong>. Or simply <strong>alter table freeze<\/strong> \u2014 this is a snapshot of the entire table. <\/p>\n<p><\/p>\n<p>The snapshot will be created consistently for one table on one shard, meaning that creating a consistent snapshot of the entire cluster in this way is impossible. However, for most tasks, such a necessity does not exist, and it is sufficient to execute the query on each shard and obtain a consistent snapshot. It is created as hard links, so it does not take up additional space. Subsequently, you copy this snapshot to the backup server or to the storage you use for backups.<\/p>\n<p><\/p>\n<p>Restoring such a backup is quite simple. First \u2014 you create tables according to the existing table definitions. Then you copy the saved partition snapshots to the Directory-Detached for the corresponding tables and execute the query. <strong>attach partition<\/strong>. This solution is quite suitable for the most serious volumes of data. <\/p>\n<p><\/p>\n<p>Sometimes, something even more advanced is required \u2014 in cases where you have tens or even hundreds of terabytes on each server and hundreds of servers. There is a solution that I learned from colleagues at Yandex.Metrica. I wouldn't recommend it to everyone \u2014 read it and decide for yourself if it fits your needs. <\/p>\n<p><\/p>\n<p>First, you need to create several servers with large disk shelves. Then, on these servers, set up several ClickHouse servers and configure them to function as another replica for the same shards. Next, use a file system or some tool on these servers that allows for snapshot creation. There are two options here. The first option is LVM snapshots, the second option is ZFS on Linux. <\/p>\n<p><\/p>\n<p>After that, you need to create a snapshot every day; it will take up some space. Naturally, if the data changes, over time the amount of space used will increase. This snapshot can be retrieved at any moment to restore data, which is quite a peculiar solution. Additionally, it is necessary to limit these replicas in the configuration so they don't try to become leaders.<\/p>\n<p><\/p>\n<h2 id=\"anchorreplicationanchormozhno-li-budet-organizovat-kontroliruemoe-otstavanie-replik-vnbspvalah\"><noindex><a rel=\"nofollow\" name=\"replication\"><\/a><\/noindex>Will it be possible to organize controlled lag of replicas in the traffic?<\/h2>\n<p><\/p>\n<blockquote><p>This year, you plan to make rolls in ClickHouse. Will it be possible to organize controlled lag for replicas within them? We would like to use it to safeguard ourselves from negative scenarios with alter tables and other changes. <\/p>\n<p>Is it possible to implement some sort of rollback for alters? For instance, in the existing roll, can you specify that up to this moment changes should be applied, and from this moment changes should not be applied?<\/p>\n<p>If a command comes in and breaks our cluster, we have a kind of replica with a one-hour lag, where we can say, let's use it at this moment, but we won't apply the last ten minutes of changes to it? <\/p><\/blockquote>\n<p>First, regarding controlled lag for replicas. There was such a request from users, and we created an issue on GitHub asking: 'If anyone needs this, please like it, give it a heart.' No one liked it, and the issue was closed. Nevertheless, it is already possible to achieve this by configuring ClickHouse, but only starting from version 20.3.<\/p>\n<p><\/p>\n<p>ClickHouse continuously merges data in the background. When a merge occurs, a certain set of data parts is replaced with a larger part. Meanwhile, the data parts that existed previously remain on disk for some time.<\/p>\n<p><\/p>\n<p>Firstly, they continue to be stored as long as there are select queries using them, in order to ensure non-blocking operation. Select queries can safely read from the old shards.<\/p>\n<p><\/p>\n<p>Secondly, there is also a time threshold \u2014 old data shards remain on disk for eight minutes. This eight minutes can be adjusted and even extended to a whole day. However, this will cost disk space: depending on the data flow, it could mean that data not only doubles in the last day, but can increase fivefold. But you can stop the ClickHouse server in the event of a serious problem and resolve everything.<\/p>\n<p><\/p>\n<p>Now the question arises, how does this protect against alters. It\u2019s worth looking deeper, because in older versions of ClickHouse, an alter would work by directly changing the shards. There is a shard of data with certain files, and we do, for example, <strong>alter drop column<\/strong>. Then this column is physically removed from all shards.<\/p>\n<p><\/p>\n<p>However, starting from version 20.3, the alter mechanism has been completely changed, and now data shards are always immutable. They don\u2019t change at all \u2014 alters now work similarly to merges. Instead of changing a shard in place, we create a new one. In the new shard, files that haven't changed become hard links, and if we\u2019ve deleted a column, it will simply be absent in the new shard. The old shard will be removed by default after eight minutes, and here you can tweak the settings mentioned above. <\/p>\n<p><\/p>\n<p>The same applies to mutation-type alters. When you do <strong>alter delete<\/strong> or <strong>alter update<\/strong>, it does not modify the part, but creates a new one. And then removes the old one.<\/p>\n<p><\/p>\n<h2 id=\"anchorsoooo-changeableanchorkak-byt-esli-struktura-tablicy-pomenyalas\"><noindex><a rel=\"nofollow\" name=\"soooo-changeable\"><\/a><\/noindex>What should we do if the table structure has changed?<\/h2>\n<p><\/p>\n<blockquote><p>How can you restore a backup that was made with an old schema? And the second question is about the case with snapshots and file system tools. Is Btrfs suitable here instead of ZFS on Linux LVM?<\/p><\/blockquote>\n<p>If you are doing <strong>attach partition<\/strong> If the partitions have a different structure, ClickHouse will tell you that it's not allowed. The solution is this: First \u2014 create a temporary table of the MergeTree type with the old structure, attach the data using attach, and run an alter query. Then you can either copy or move that data and attach it again, or use a query. <strong>alter table move partition<\/strong>.<\/p>\n<p><\/p>\n<p>Now for the second question&nbsp;\u2014 can Btrfs be used? To start, if you have LVM, then LVM snapshots are sufficient, and the file system can be ext4; it doesn't matter. With Btrfs, it all depends on your experience with its operation. It is a mature file system, but there are still some concerns about how it will perform in practice in a specific scenario. I wouldn't recommend using it if you don't have Btrfs in production.<\/p>\n<p><\/p>\n<h2 id=\"anchorresharding-best-practicesanchorkakie-seychas-luchshie-praktiki-vnbspreshardinge-dannyh\"><noindex><a rel=\"nofollow\" name=\"resharding-best-practices\"><\/a><\/noindex>What are the current best practices for data resharding?<\/h2>\n<p><\/p>\n<p>The question about reshuffling is complex and multifaceted. There can be several answers right away. One could approach it from one angle and say \u2014 there is no built-in reshuffling capability in ClickHouse. But I fear that this answer won't satisfy anyone. So we can approach it from another angle and say that ClickHouse offers many ways to reshuffle data. <\/p>\n<p><\/p>\n<p>If the cluster runs out of space or cannot handle the load, you add new servers. However, these servers are empty by default; they contain no data, and there is no load. You will need to redistribute the data so that it is spread evenly across the newly expanded cluster.<\/p>\n<p><\/p>\n<p>The first way to do this&nbsp;\u2014 is to copy part of the partitions to new servers using a query. <strong>alter table fetch partition<\/strong>. For instance, if you had partitions by month, you take the first month of 2017 and copy it to a new server, then you copy the third month to another new server. You continue doing this until it becomes relatively even.<\/p>\n<p><\/p>\n<p>The transfer can only be performed for&nbsp;those partitions that do not change during writing. For&nbsp;new partitions, you will have to disable writing, as their transfer is not atomic. Otherwise, you will get duplicates or gaps in the data. Nevertheless, this method is practical and works quite effectively. The already compressed partitions are transferred over the network, meaning that the data is not recompressed or recoded.<\/p>\n<p><\/p>\n<p>This method has one drawback, which depends on the sharding scheme. You need to account for how you set up this sharding scheme and what your sharding key was. In your example, for the case with metrics, the sharding key is a hash of the path. When you perform a select on a Distributed table, it goes straight to all the shards in the cluster and retrieves data from there. <\/p>\n<p><\/p>\n<p>This means that it doesn\u2019t really matter for you which data ended up on which shard. The important thing is that data for a specific path is on a single shard, while the exact shard it is on doesn\u2019t matter. In this case, moving the prepared partitions works well because, during select queries, whether before or after resharding, the schema of values does not significantly impact\u2014you will receive complete data.<\/p>\n<p><\/p>\n<p>However, there are more complex scenarios. If at the application logic level you are relying on a specific sharding scheme, where this client is located on a particular shard, and the query can be sent directly there instead of to the Distributed table. Or if you are using a relatively recent version of ClickHouse and have enabled the setting <strong>optimize skip unused shards<\/strong>. In this case, during the select query, the expression in the where clause will be analyzed, and it will determine which shards need to be accessed according to the sharding scheme. This works provided the data is laid out in accordance with that sharding scheme. If you laid them out manually, the correspondence may change.<\/p>\n<p><\/p>\n<p>So, this is method number one. I am waiting for your response, whether this method works for you or if we should move on.<\/p>\n<p><\/p>\n<p><strong>Vladimir Kolobayev, lead system administrator at Avito<\/strong>: Alexey, the method you mentioned doesn\u2019t work very well when you need to distribute the load, including for reads. We can take a monthly partition and move the previous month to another node, but when a request comes for that data, we will only be loading it. It would be preferable to load the entire cluster because otherwise, our read load will be processed by only two shards for some time.<\/p>\n<p><\/p>\n<p><strong>Alexey Milovidov:<\/strong> The response here is strange\u2014yes, it\u2019s bad, but it might still work. Let me explain how. It\u2019s worth looking at the load scenario that follows your data. If it\u2019s monitoring data, it is almost certain that the overwhelming majority of queries are for fresh data. <\/p>\n<p><\/p>\n<p>You have set up new servers, migrated old partitions, but also changed how fresh data is recorded. The fresh data will be spread across the entire cluster. Thus, in just five minutes, queries for the last five minutes will evenly load the cluster; after a day, queries for the entire day will evenly load the cluster as well. Unfortunately, queries for the previous month will only go to part of the cluster's servers.<\/p>\n<p><\/p>\n<p>However, you often won't have queries specifically for February 2019. Most likely, if queries are made in 2019, they will encompass the entire year\u2014over a large time span, rather than a small range. Such queries can also evenly load the cluster. Overall, your observation is quite correct; this is an ad hoc solution that does not fully distribute the data evenly.<\/p>\n<p><\/p>\n<p>I have a few more points to address your question. One of them concerns how to initially design the sharding scheme so that re-sharding involves less pain. This is not always possible.<\/p>\n<p><\/p>\n<p>For example, you have monitoring data. Monitoring data grows for three reasons. The first is the accumulation of historical data. The second is traffic growth. And the third is the increasing number of things that fall under monitoring. New microservices and metrics that need to be saved are appearing. <\/p>\n<p><\/p>\n<p>It is possible that the most significant growth is related to the third reason\u2014the increased usage of monitoring. In this case, it's worth looking at the load characteristics; what the main select queries are. The main select queries will likely target some subset of metrics.<\/p>\n<p><\/p>\n<p>For instance, CPU usage on certain servers by a certain service. There is some subset of keys through which you are retrieving this data. The query for this data is likely fairly simple and executes in tens of milliseconds. It is used for monitoring services and dashboards. I hope I understand this correctly.<\/p>\n<p><\/p>\n<p><strong>Vladimir Kolobaev:<\/strong> The thing is, we often refer to historical data because we compare the current situation with historical ones in real time. It is important for us to have quick access to large volumes of data, and ClickHouse handles this excellently.<\/p>\n<p><\/p>\n<p>You are absolutely right; we experience most read requests over the last day, just like any monitoring system. However, there is also a substantial load for historical data. It mainly comes from the alerting system, which checks in with ClickHouse every thirty seconds, saying: 'Give me the data for the last six weeks. Now build me a moving average from that data, and let\u2019s compare the current value with the historical one.' <\/p>\n<p><\/p>\n<p>I would like to mention that we have a small table for such very recent requests, where we store just two days' worth of data, and the main queries go to it. We only send larger historical queries to a bigger sharded table.<\/p>\n<p><\/p>\n<p><strong>Alexey Milovidov:<\/strong> Unfortunately, this is not very applicable for your scenario, but I will describe two bad and complicated sharding schemes that should not be used, yet are used in my friends' service. <\/p>\n<p><\/p>\n<p>There is a main cluster with events from 'Yandex.Metrics.' Events include page views, clicks, and transitions. Most requests target a specific website. You open the 'Yandex.Metrics' service, you have a site \u2014 avito.ru, and you navigate to a report, triggering a request for your site.<\/p>\n<p><\/p>\n<p>But there are also other requests \u2014 analytical and global requests made by internal analysts. I should note that internal analysts only query the 'Yandex' services. Nevertheless, the 'Yandex' services account for a significant portion of all data. These are not requests for specific counters but involve broader filtering.<\/p>\n<p><\/p>\n<p>How can data be organized so that operations are efficient for individual counters and global queries as well? The complexity also lies in the fact that the number of requests in ClickHouse on the 'Metrics' cluster is several thousand per second. At the same time, non-trivial queries, for example, several thousand per second, cannot be handled by a single ClickHouse server.<\/p>\n<p><\/p>\n<p>The size of the cluster is about six hundred servers. Simply stretching a Distributed table over this cluster and sending several thousand requests there will make it even worse than sending them to a single server. On the other hand, the option where the data is evenly spread out, and we query from all servers at once, is immediately ruled out.<\/p>\n<p><\/p>\n<p>There's a diametrically opposite option. Imagine if we shard the data by websites, and a request for one site goes to one shard. Then the cluster can easily handle ten thousand requests per second, but on a single shard, a particular request might be too slow. It won\u2019t scale in terms of throughput. Especially if that\u2019s the site avito.ru. I won\u2019t reveal a secret by saying that Avito is one of the most visited sites in Runet. Processing it on a single shard would be madness.<\/p>\n<p><\/p>\n<p>Hence, the sharding scheme is structured more cleverly. The entire cluster is divided into a certain number of sub-clusters that we call layers. Within each sub-cluster, there are from ten to several dozens of shards. In total, there are thirty-nine such sub-clusters. <\/p>\n<p><\/p>\n<p>How does it all scale? The number of sub-clusters does not change\u2014just as it was thirty-nine several years ago, so it remains. But within each of them, we gradually increase the number of shards as data accumulates. The overall sharding scheme is organized such that partitioning these sub-clusters is done by websites, and to determine which site resides on which cluster, an entirely separate metadata layer in MySQL is used. One site corresponds to one sub-cluster. Inside it, sharding is based on visitor identifiers.<\/p>\n<p><\/p>\n<p>When recording, we break them down by the remainder of dividing the visitor identifier. However, when adding a new shard, the sharding scheme changes; we continue to break it down, but with a remainder from dividing by a different number. This means that one visitor is actually located on several servers, and we can't rely on that. This is done solely to optimize data compression. During requests, we access the Distributed table, which looks at the cluster and connects to dozens of servers. It's a silly scheme.<\/p>\n<p><\/p>\n<p>But my story would be incomplete if I didn't mention that we abandoned this scheme. In the new scheme, we changed everything and copied all the data using clickhouse-copier.<\/p>\n<p><\/p>\n<p>In the new scheme, all websites are divided into two categories \u2014 large and small. I don\u2019t know how the threshold was chosen, but as a result, large sites are recorded on one cluster with 120 shards, each having three replicas \u2014 a total of 360 servers. The sharding scheme is such that any request goes immediately to all shards. If you open any report page for avito.ru in Yandex.Metrica right now, the request will go to 120 servers. There are few large sites in the Russian internet. So the requests do not reach a thousand per second, but are even less than a hundred. The Distributed table easily handles this, which processes each of them with 120 servers.<\/p>\n<p><\/p>\n<p>The second cluster is for small sites. Here, the sharding scheme is based on the site identifier, and each request goes to only one shard.<\/p>\n<p><\/p>\n<h2 id=\"anchorclickhouse-copieranchorv-clickhouse-est-utilita-clickhouse-copier-mozhete-pronbspneyo-rasskazat\"><noindex><a rel=\"nofollow\" name=\"clickhouse-copier\"><\/a><\/noindex>ClickHouse has a utility called clickhouse-copier. Can you tell us about it?<\/h2>\n<p><\/p>\n<p>Let me say right away that this solution is bulkier and somewhat less efficient. The advantage is that it fully distributes the data according to the scheme you specify. However, the drawback of the utility is that it does not perform resharding at all. It copies data from one cluster scheme to another.<\/p>\n<p><\/p>\n<p>This means that for it to work, you must have two clusters. They can be located on the same servers; however, the data will not be moved incrementally, but will be copied. <\/p>\n<p><\/p>\n<p>For example, there were four servers, and now there are eight. You create a new Distributed table on all the servers, new local tables, and run clickhouse-copier, specifying the operation schema, that it should read from there, accept the new sharding schema, and transfer the data there. You will need one and a half times more space on the old servers than you currently have, because the old data must remain on them, and on top of that, half of that old data will also arrive. If you anticipated that the data needs to be reshuffled and there is space available, then this approach will work.<\/p>\n<p><\/p>\n<p>How is clickhouse-copier structured internally? It breaks down all work into a set of tasks for processing a single partition of one table on one shard. All these tasks can be executed in parallel, and clickhouse-copier can be run on different machines in several instances, but what it does for a single partition is nothing more than insert select. Data is read, decompressed, reshuffled, then compressed again, written somewhere, and resorted. This is a more complex solution.<\/p>\n<p><\/p>\n<h2 id=\"anchorresharding-toolanchoru-vas-byla-pilotnaya-shtuka-kotoraya-nazyvalas-resharding-chto-snbspney\"><noindex><a rel=\"nofollow\" name=\"resharding-tool\"><\/a><\/noindex>You had a pilot feature called resharding. What happened to it?<\/h2>\n<p><\/p>\n<blockquote><p>You had a pilot feature back in 2017 called resharding. There\u2019s even an option in ClickHouse. As I understand it, it didn\u2019t take off. Can you tell us why that happened? It seemed quite relevant.<\/p><\/blockquote>\n<p>The problem is that when it is necessary to reshuffle data on-site, very complex synchronization is required to do it atomically. When we started looking into how this synchronization is structured, it became clear that there are fundamental problems. And these fundamental issues are not just theoretical; they immediately manifested themselves in practice as something that can be explained quite simply \u2014 nothing works.<\/p>\n<p><\/p>\n<h2 id=\"anchormove-to-slow-diskanchormozhno-li-slivat-vse-chasti-dannyh-voedino-perednbspperemescheniem-nanbspmedlennye-diski\"><noindex><a rel=\"nofollow\" name=\"move-to-slow-disk\"><\/a><\/noindex>Is it possible to consolidate all data parts into one before moving them to slow disks?<\/h2>\n<p><\/p>\n<blockquote><p>A question about TTL with the option to move to slow disk in the context of merges. Is there a way, aside from cron, to merge all parts into one before transferring to slow disks?<\/p><\/blockquote>\n<p>The answer to the question of whether it is possible to automatically merge all chunks into one before their transfer is \u2014 no. I don\u2019t think there\u2019s a need for it. You can just rely on the fact that they will be transferred to slow disks automatically. <\/p>\n<p><\/p>\n<p>We have two criteria for migration. The first one is based on usage. If the current storage level has less than a certain percentage of free space, we select one chunk and move it to slower storage. Or rather, not slower, but the next one, depending on how you configure it.<\/p>\n<p><\/p>\n<p>The second criterion is based on size. It concerns the movement of large chunks. You can adjust the threshold for free space on the fast disk, and the data will be transferred automatically.<\/p>\n<p><\/p>\n<h2 id=\"anchorup-to-dateanchorkak-pereezzhat-nanbspnovye-versii-clickhouse-esli-net-vozmozhnosti-zaranee-proverit-sovmestimost\"><noindex><a rel=\"nofollow\" name=\"up-to-date\"><\/a><\/noindex>How do you migrate to new versions of ClickHouse if there is no opportunity to check compatibility in advance?<\/h2>\n<p><\/p>\n<blockquote><p>This topic is regularly discussed <noindex><a rel=\"nofollow\" href=\"https:\/\/teleg.run\/clickhouse_ru\">in the ClickHouse Telegram chat<\/a><\/noindex> taking into account different versions, nevertheless. How safe is it to upgrade from version 19.11 to 19.16 and from 19.16 to 20.3, for example? What is the best way to migrate to new versions without being able to check compatibility in a sandbox beforehand?<\/p><\/blockquote>\n<p>Here are a few \"golden\" rules. The first one is <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/ClickHouse\/ClickHouse\/blob\/master\/CHANGELOG.md\">read the changelog<\/a><\/noindex>. It is extensive, but there are specific items about backward incompatible changes. You shouldn\u2019t treat these points as a red flag. Usually, these are minor incompatibilities related to some edge functionality that, quite likely, you are not using.<\/p>\n<p><\/p>\n<p>The second rule is that if you cannot check compatibility in a sandbox and you want to upgrade directly in production, the recommendation is \u2014 don\u2019t do it. First, create a sandbox and test it. If you don\u2019t have a testing environment, you may not have a very large company, so you can copy some of the data to your laptop and verify that everything works correctly. You can even set up several replicas locally on your machine. Alternatively, you can set up the new version somewhere nearby and upload part of the data there \u2014 in other words, create an improvised testing environment. <\/p>\n<p><\/p>\n<p>Another rule is to avoid upgrading within a week after the release of a version due to bug catching in production and subsequent quick fixes. Let\u2019s clarify the version numbering of ClickHouse to avoid confusion. <\/p>\n<p><\/p>\n<p>There is version 20.3.4. The number 20 indicates the release year \u2014 2020. From the perspective of what\u2019s inside, this does not matter, so we won\u2019t focus on that. Next is 20.3. The second digit \u2014 in this case, 3 \u2014 is incremented each time we release a version with new functionality. If we want to add a feature to ClickHouse, we must increase this number. So, in version 20.4, ClickHouse will work even better. The third digit \u2014 20.3.4. Here, 4 represents the count of patch releases where we have not added new features but have fixed some bugs. And 4 means we have done this four times.<\/p>\n<p><\/p>\n<p>Do not think that this is something dreadful. Usually, a user can install the latest version, and it will work without any issues for a year. But imagine that in some bitmap processing function, which was added by our colleagues from China, the server crashes when incorrect arguments are passed. We must fix this. We will release a new patch version, and ClickHouse will become more stable.<\/p>\n<p><\/p>\n<p>If you have ClickHouse running in production and a new version with additional features is released \u2014 for example, 20.4.1 \u2014 do not rush to install it in production on the very first day. Why is it even needed? If you are not yet using ClickHouse, you can install it, and most likely everything will be fine. But if ClickHouse is already running stably, keep an eye on the patches and updates \u2014 what issues we are fixing.<\/p>\n<p><\/p>\n<p><strong>Kirill Shvakov:<\/strong> I want to add a bit about test environments. Everyone is very scared of test environments and somehow believes that if you have a very large ClickHouse cluster, then the test environment should be no smaller or at least ten times smaller. This is not the case at all.<\/p>\n<p><\/p>\n<p>I can speak from my own experience. I have a project where ClickHouse is used. Our test environment for it is a small virtual machine at Hetzner for twenty euros, where everything is fully deployed. To do this, we have complete automation with Ansible, so there is essentially no difference in whether to deploy on physical servers or simply run it in virtual machines.<\/p>\n<p><\/p>\n<p>What can be done? It would be great to include an example in the ClickHouse documentation on how to set up a small cluster\u2014either with Docker or LXC, and perhaps create an Ansible playbook, since different people have different deployment needs. This would simplify a lot. When you can set up a cluster in just five minutes, it's much easier to try and figure things out. It's much more convenient because pushing a production version that you haven't tested is a sure path to failure. Sometimes it works, and sometimes it doesn't. So, hoping for success is a poor strategy.<\/p>\n<p><\/p>\n<p><strong>Maxim Kotyakov, Senior Backend Engineer at Avito:<\/strong> I would like to add a bit about testing environments from the series of issues faced by large companies. We have a fully functional acceptance cluster for ClickHouse, which is a precise replica of the production environment in terms of data schemas and configurations. This cluster is deployed in rather outdated containers with minimal resources. We write a certain percentage of production data there, as we have the capability to replicate the stream in Kafka. Everything is synchronized and scaled\u2014both in terms of power and flow, and, in theory, should behave like production under similar conditions in terms of metrics. All potentially explosive changes are first deployed to this test environment and left to mature for several days before they are deemed ready. Naturally, this solution is expensive, complex, and incurs non-zero support costs. <\/p>\n<p><\/p>\n<p><strong>Alexey Milovidov:<\/strong> Let me describe the testing environment of our friends from 'Yandex.Metrica'. One cluster had over 600 servers, another had 360, and there is also a third one and several other clusters. The testing environment for one of them consists simply of two shards, each having two replicas. Why two shards? So that there's more than one. And there are also replicas to ensure redundancy. It's just a minimal number that is feasible to maintain.<\/p>\n<p><\/p>\n<p>This testing environment allows us to verify that queries are functional and that nothing major has broken. However, often issues arise of a completely different nature, where everything works, but there are some minor changes under load.<\/p>\n<p><\/p>\n<p>Let me give you an example. We decided to install a new version of ClickHouse. It was released in the testing environment, automated tests were run in Yandex.Metrica, comparing data from the old version and the new one, going through the entire pipeline. And naturally, our CI tests were all green. Otherwise, we wouldn't have even offered this version.<\/p>\n<p><\/p>\n<p>Everything is great. We start rolling it out to production. I receive a message that the load on the graphs has increased several times. We roll back the version. I look at the graph and see: the load really increased several times during the rollout and decreased again when it was rolled back. Then we started to roll back the version. And the load increased in the same way and similarly decreased afterward. So the conclusion is that the load increased due to the rollout, which is not surprising.<\/p>\n<p><\/p>\n<p>After that, it was difficult to convince my colleagues to install the new version. I said: \"Everything is fine, go ahead and roll it out. Keep your fingers crossed, everything will work. Yes, the load has increased on the graphs, but everything is fine. Just hang in there.\" In general, we did it, and there it was \u2014 the version was rolled out to production. But similar problems arise almost every time we deploy.<\/p>\n<p><\/p>\n<h2 id=\"anchorkill-queryanchorkill-query-dolzhen-ubivat-zaprosy-no-on-etogo-ne-delaet-pochemu\"><noindex><a rel=\"nofollow\" name=\"kill-query\"><\/a><\/noindex>Kill query is supposed to kill queries but it doesn\u2019t seem to work. Why is that?<\/h2>\n<p><\/p>\n<blockquote><p>A user came to me, some analyst, and created a query that crashed my ClickHouse cluster. Either a node or the entire cluster \u2014 depending on which replica or shard the query hit. I see that all CPU resources on this server are maxed out, everything is red. Meanwhile, ClickHouse is still responding to queries. I ask: \"Please show me the process list, which query caused this madness.\"<\/p>\n<p>I find this query and write kill. And I see that nothing happens. My server is maxed out, ClickHouse continues to give me some commands, showing that the server is alive, and everything is fine. But I have degradation in all user queries, degradation starts in writing to ClickHouse, and my kill query is not executing. Why? I thought that the kill query was supposed to terminate queries, but that doesn't happen.<\/p><\/blockquote>\n<p>Now, there will be quite a strange answer. The thing is that the kill query does not terminate queries. <\/p>\n<p><\/p>\n<p>The Kill query sets a small flag named \"I want this query to be killed.\" The query itself checks this flag while processing each block. If it's set, the query stops executing. This means that no one is actually killing the query; it needs to check and stop on its own. This should work in all cases when the query is processing data blocks. It will process the next data block, check the flag, and stop.<\/p>\n<p><\/p>\n<p>This does not work in cases where the query is blocked on some operation. However, it's unlikely to be your case because, according to you, it's using a lot of server resources. It may not work in the case of external sorting and some other details. But generally, this shouldn't happen; it\u2019s a bug. The only advice I can give is to update ClickHouse.<\/p>\n<p><\/p>\n<h2 id=\"anchorreading-timeanchorkak-rasschitat-vremya-otveta-pri-chitayuschey-nagruzke\"><noindex><a rel=\"nofollow\" name=\"reading-time\"><\/a><\/noindex>How to calculate response time under read load?<\/h2>\n<p><\/p>\n<blockquote><p>There is a table storing aggregates per item\u2014various counters. The number of rows is about one hundred million. Can we expect a predictable response time if we pour in 1K RPS for 1K items? <\/p><\/blockquote>\n<p>Judging by the context, this is about read load because there are no issues with writing\u2014you can insert a thousand, a hundred thousand, or even several million rows. <\/p>\n<p><\/p>\n<p>Read queries come in various forms. In select 1, ClickHouse can handle about tens of thousands of queries per second, so even queries for a single key will require some resources. Such point queries will be more complex than in key-value databases, as each read must access a data block by index. Our index doesn\u2019t address each record individually but each range. This means you will have to read the entire range\u2014defaulting to 8192 rows. Additionally, you will need to decompress a compressed data block from 64 KB to 1 MB. Usually, such point queries take several milliseconds. But this is the simplest scenario.<\/p>\n<p><\/p>\n<p>Let's try to do some simple arithmetic. If you multiply a few milliseconds by a thousand, you'll get a few seconds. It's as if handling a thousand requests per second is impossible, but actually, it is possible because we have multiple CPU cores. So, in principle, ClickHouse can sometimes handle 1000 RPS, but only for short, point-specific requests.<\/p>\n<p><\/p>\n<p>If you need to scale a ClickHouse cluster by the number of simple queries, I recommend the simplest solution \u2014 increase the number of replicas and send queries to a random replica. If one replica can handle five hundred queries per second, which is completely feasible, then three replicas will manage one thousand five hundred queries.<\/p>\n<p><\/p>\n<p>Sometimes, of course, you can configure ClickHouse for the maximum number of point reads. What do you need for this? First, reduce the granularity of the index. However, it shouldn't be reduced to one; rather, it should be calculated so that the number of entries in the index is several million or tens of millions per server. If the table has one hundred million rows, you can set the granularity to 64.<\/p>\n<p><\/p>\n<p>You can reduce the size of the compressed block. There are settings for that. <strong>min compress block size<\/strong>, <strong>max compress block size<\/strong>You can reduce them, recompile the data, and then point queries will be faster. However, ClickHouse is still not a key-value database. A large number of small requests is an anti-pattern for load.<\/p>\n<p><\/p>\n<p><strong>Kirill Shvakov:<\/strong> Here's some advice in case there are regular counters. This is quite a standard situation where ClickHouse stores some kind of counter. I have a user from a certain country, plus some third field, and you need to increment something. Use MySQL, create a unique key \u2014 in MySQL it's a duplicate key, and in PostgreSQL it's a conflict \u2014 and increment it. This will work much better. <\/p>\n<p><\/p>\n<p>When you have a small amount of data, there's really no point in using ClickHouse. There are regular databases, and they handle this well. <\/p>\n<p><\/p>\n<h2 id=\"anchorpimp-my-clickhouseanchorchto-podtyunit-v-clickhouse-chtoby-bolshe-dannyh-bylo-vnbspkeshe\"><noindex><a rel=\"nofollow\" name=\"pimp-my-clickhouse\"><\/a><\/noindex>What can be tuned in ClickHouse so that more data is cached?<\/h2>\n<p><\/p>\n<blockquote><p>Imagine a situation \u2014 the servers have 256 GB of RAM, in daily routine, ClickHouse uses about 60-80 GB, peaking at up to 130. What can be enabled and tuned so that more data is cached and, consequently, there are fewer disk accesses?<\/p><\/blockquote>\n<p>As a rule, the operating system's page cache handles this task well. If you simply open the top, look for cached or free \u2014 it also shows how much is cached \u2014 you can notice that all free memory is used for the cache. This data will be read not from the disk but from RAM. I can say that the cache is used effectively because it caches compressed data.<\/p>\n<p><\/p>\n<p>However, if you want to speed up some simple queries even further, there is an option to enable uncompressed data caching within ClickHouse. This is called <strong>uncompressed cache<\/strong>. In the configuration file config.xml, you set the uncompressed cache size to the desired value \u2014 I recommend no more than half of the free RAM because the rest will go to page cache. <\/p>\n<p><\/p>\n<p>Additionally, there are two query-level settings. The first setting is <strong>use uncompressed cache<\/strong> \u2014 enables its use. It is recommended to enable it for all queries except for heavy ones that can read all data and flush this cache. The second setting is something like the maximum number of rows to use the cache. It automatically limits large queries to bypass the cache.<\/p>\n<p><\/p>\n<h2 id=\"anchorstorage-configurationanchorkak-mozhno-nastroit-storage_configuration-dlya-hraneniya-v-operativke\"><noindex><a rel=\"nofollow\" name=\"storage-configuration\"><\/a><\/noindex>How can we configure storage_configuration for in-memory storage?<\/h2>\n<p><\/p>\n<blockquote><p>In the new ClickHouse documentation, I read a section related to <noindex><a rel=\"nofollow\" href=\"https:\/\/clickhouse.tech\/docs\/en\/single\/#table_engine-mergetree-multiple-volumes\">with data storage<\/a><\/noindex>. There is an example in the description with fast SSD. <\/p>\n<p>It's interesting how the same thing can be configured with volume hot memory. And another question. How does select work with such data organization? Will it read the entire set or only what is stored on disk, and are these data compressed in memory? And how does the prewhere section work with such data organization?<\/p><\/blockquote>\n<p>This setting affects the storage of data chunks, and their format does not change.<br \/>\nLet's take a closer look. <\/p>\n<p><\/p>\n<p>You can configure data storage in RAM. Everything that is configured for the disk is its path. You create a tmpfs partition that is mounted at a certain path in the filesystem. You specify this path as the storage path for the hottest partition, and data chunks start being written there; everything works well. <\/p>\n<p><\/p>\n<p>However, I do not recommend doing this due to low reliability, although if you have at least three replicas in different data centers, it may be acceptable. In that case, the data will be recoverable. Imagine that the server suddenly shuts down and is turned back on. The section mounts again, but there is emptiness. The ClickHouse server, upon starting, sees that these chunks are missing, even though according to ZooKeeper's metadata, they should be present. It checks which replicas contain them, requests those replicas, and downloads them. Thus, the data will be restored. <\/p>\n<p><\/p>\n<p>In this sense, storing data in RAM is fundamentally no different from storing it on disk, because when data is written to disk, it also first goes into the page cache and is physically recorded deferred. This depends on the filesystem mount option. But just to be sure, I will say that ClickHouse does not perform fsync during insert.<\/p>\n<p><\/p>\n<p>At the same time, the data in RAM is stored in exactly the same format as on disk. The select query retrieves chunks that need to be read in the same way; it selects the necessary data ranges and reads them. The prewhere works exactly the same, regardless of whether the data was in RAM or on disk.<\/p>\n<p><\/p>\n<h2 id=\"anchorlow-cardinalityanchordo-kakogo-kolichestva-unikalnyh-znacheniy-effektiven-low-cardinality\"><noindex><a rel=\"nofollow\" name=\"low-cardinality\"><\/a><\/noindex>Up to what number of unique values is Low Cardinality effective?<\/h2>\n<p><\/p>\n<p>Low Cardinality is cleverly designed. It creates dictionaries of data, but these dictionaries are local. Firstly, there are separate dictionaries for each chunk, and secondly, even within a single chunk, they can differ for each range. When the number of unique values reaches a threshold quantity \u2014 if I'm not mistaken, one million \u2014 the dictionary is simply deferred, and a new one is created.<\/p>\n<p><\/p>\n<p>The overall answer is this: for each local range \u2014 say, for each day \u2014 where there are up to a million unique values, Low Cardinality is effective. After that, there will simply be a fallback, where many different dictionaries will be used instead of one. It will operate roughly the same as a regular string-type column, maybe slightly less efficiently, but there will be no significant performance degradation. <\/p>\n<p><\/p>\n<h2 id=\"anchorfulltext-searchanchorkakie-luchshie-praktiki-ponbsppolnotekstovomu-poisku-ponbsptablice-snbsppyatyu-milliardami-strok\"><noindex><a rel=\"nofollow\" name=\"fulltext-search\"><\/a><\/noindex>What are the best practices for full-text search on a table with five billion rows?<\/h2>\n<p><\/p>\n<p>There are different options for an answer. The first is to say that ClickHouse is not a system for full-text search. There are specialized systems for that, for example, <noindex><a rel=\"nofollow\" href=\"https:\/\/www.elastic.co\/enterprise-search\">Elasticsearch<\/a><\/noindex> and <noindex><a rel=\"nofollow\" href=\"http:\/\/sphinxsearch.com\/\">Sphinx<\/a><\/noindex>. Nevertheless, I increasingly encounter people who say they are transitioning from Elasticsearch to ClickHouse.<\/p>\n<p><\/p>\n<p>Why is this happening? They explain it by saying that Elasticsearch starts to struggle with the load at some data volumes, especially concerning index building. The indexes become too bulky, and if you just transfer the data to ClickHouse, it will be stored several times more efficiently by volume. Moreover, search queries were often not looking for a specific phrase across the entire data volume with morphological consideration, but rather something different. For instance, finding a specific subsequence of bytes in the logs over the last few hours.<\/p>\n<p><\/p>\n<p>In this case, you create an index in ClickHouse where the first field will be the date with time. The largest data exclusion will be based on the date range. Within the selected date range, it is usually possible to perform full-text searches even with a brute-force method using like. The like operator in ClickHouse is the most efficient like operator you can find. If you find a better one, let me know. <\/p>\n<p><\/p>\n<p>However, like still involves a full scan. And a full scan can be slow not only in terms of CPU but also disk access. If you happen to have one terabyte of data per day, and you're searching for a word within that day, you'll have to scan a terabyte. It's certainly on standard hard drives, and as a result, they will be loaded in such a way that you won't be able to access that server via SSH.<\/p>\n<p><\/p>\n<p>In this case, I am ready to propose one more little trick. It's somewhat experimental \u2014 it may work, or it may not. ClickHouse has full-text indexes in the form of trigram Bloom filters. Our colleagues at Arenadata have already tested these indexes, and they often work just as intended.<\/p>\n<p><\/p>\n<p>To use them correctly, you need to have a good understanding of how exactly they work: what a trigram Bloom filter represents and how to choose its size. I can say that they will help with queries for some rare phrases or substrings that seldom appear in the data. In this case, subranges will be selected based on the indexes, and less data will need to be read.<\/p>\n<p><\/p>\n<p>Recently, ClickHouse introduced even more advanced features for full-text search. Firstly, you can now search multiple substrings in a single pass, including options that consider case, ignore case, support UTF-8, or are limited to ASCII. Choose the most effective one you need. <\/p>\n<p><\/p>\n<p>Additionally, multiple regular expression searches can now be performed in one go. You no longer need to write X like one substring or X like another substring. Just write it all at once, and everything executes as efficiently as possible.<\/p>\n<p><\/p>\n<p>Thirdly, there is now approximate regex searching and approximate substring searching. If someone has misspelled a word, it will be searched for maximum match accuracy.<\/p>\n<p><\/p>\n<h2 id=\"anchorhello-and-welcomeanchorkak-luchshe-organizovat-dostup-vnbspclickhouse-dlyanbspbolshogo-kolichestva-polzovateley\"><noindex><a rel=\"nofollow\" name=\"hello-and-welcome\"><\/a><\/noindex>How to best organize access to ClickHouse for a large number of users?<\/h2>\n<p><\/p>\n<blockquote><p>Share how to better organize access for a large number of consumers and analysts. How to form a queue, prioritize requests for max concurrent queries, and what tools to use?<\/p><\/blockquote>\n<p>If the cluster is large enough, a good solution would be to deploy two additional servers that will serve as entry points for analysts. This means not allowing analysts to access specific shards of the cluster, but simply creating two empty servers without data, and then configuring access rights on them. At the same time, user settings for distributed queries are passed to remote servers. That is, you configure everything on these two servers, and the settings affect the entire cluster.<\/p>\n<p><\/p>\n<p>In principle, these servers have no data, but their memory capacity is quite important for query execution. The disk may also be used for temporary data if external aggregation or external sorting is enabled.<\/p>\n<p><\/p>\n<p>It is crucial to look at the settings related to all possible limits. If I log into the \"Yandex.Metrics\" cluster as an analyst and make a request <strong>select count from hits<\/strong>, I will immediately receive an exception that I cannot execute the query. The maximum number of rows I am allowed to scan is one hundred billion, while there are fifty trillion rows in total in one table on the cluster. This is the first limitation. <\/p>\n<p><\/p>\n<p>Suppose I remove the limit on the number of rows and execute the query again. Then I will see the following exception\u2014 the setting is enabled. <strong>force index by date<\/strong>I cannot fulfill the request if I do not specify the date range. One should not rely on the analysts to specify it manually. A typical case is when a date range is written as where event date between week. Then, if the parentheses are just misplaced, instead of and, it becomes or \u2014 or URL match. If there is no restriction, it will start scanning the URL column and will consume a ton of resources.<\/p>\n<p><\/p>\n<p>Additionally, ClickHouse has two priority settings. Unfortunately, they are very primitive. One is simply called <strong>priority<\/strong>. If priority \u2260 0, and queries are executed with some priority, but at the same time a query with a priority that has a lower value, which means a higher priority, is executed, then the query with a higher priority value, which indicates a lower priority, is simply paused and will not operate at all during that time.<\/p>\n<p><\/p>\n<p>This is a very rough setting, and it is not suitable for situations where there is a constant load on the cluster. However, if you have short, bursty requests that are important, and the cluster is mostly idle, this setting will work.<\/p>\n<p><\/p>\n<p>The next priority setting is called <strong>OS thread priority<\/strong>. It simply assigns a nice value for all query execution threads to the Linux scheduler. Its performance is mediocre, but it still works. If the minimum nice value is set \u2014 it has the highest magnitude, and thus the lowest priority \u2014 and requests with high priority are set to -19, the CPU will consume low-priority requests approximately four times less than high-priority ones. <\/p>\n<p><\/p>\n<p>It is also necessary to set a maximum execution time for a query \u2014 say, five minutes. The minimum execution speed for a query \u2014 that\u2019s the most crucial. This setting has been around for a long time, and it is necessary to ensure that ClickHouse does not stall.<\/p>\n<p><\/p>\n<p>Imagine you are configuring: if any query processes less than one million rows per second \u2014 this is unacceptable. It tarnishes our good name, our good database. Let's just prohibit this. There are actually two settings. One is called <strong>min execution speed<\/strong> - in&nbsp;rows per&nbsp;second, and the second is called timeout before checking min execution speed&nbsp;\u2014 by default, fifteen seconds. So, you can have fifteen seconds, and then, if it's slow, just throw an exception \u2014 terminate the request.<\/p>\n<p><\/p>\n<p>There is also a need to configure quotas. ClickHouse has a built-in quota capability that tracks resource consumption. However, unfortunately, it does not cover physical resources like CPU or disks, but rather logical ones \u2014 the number of processed requests, rows, and bytes read. You can set a maximum of, for example, one hundred requests over five minutes and one thousand requests per hour.<\/p>\n<p><\/p>\n<p>Why is this important? Because some analytics queries will be executed manually directly from the ClickHouse client. And everything will be fine. But if your company has advanced analysts, they will write a script, and there could be an error in the script. This error could lead to the query running in an infinite loop. This is what we need to protect against.<\/p>\n<p><\/p>\n<h2 id=\"anchorsmorgasbordanchormozhno-li-otdat-rezultaty-odnogo-zaprosa-desyati-klientam\"><noindex><a rel=\"nofollow\" name=\"smorgasbord\"><\/a><\/noindex>Can we send the results of one query to ten clients?<\/h2>\n<p><\/p>\n<blockquote><p>We have several users who tend to come in with very large requests at the same time. The request is large, and it generally executes quickly, but due to the high number of simultaneous requests, it becomes quite painful. Is it possible to execute the same request that arrives ten times in a row just once and deliver the result to ten clients?<\/p><\/blockquote>\n<p>The problem is that we lack cached results or cached intermediate data. There is the page cache of the operating system, which will prevent data from being read from the disk again, but unfortunately, the data will still need to be decompressed, deserialized, and processed again. <\/p>\n<p><\/p>\n<p>We would like to avoid this somehow, either by caching intermediate data or by queuing similar requests and adding a results cache. We currently have one pull request in development that adds request caching, but only for subqueries in the in and join sections \u2014 meaning the solution is incomplete.<\/p>\n<p><\/p>\n<p>However, we also encounter such a situation. A classic example is pagination requests. There is a report with several pages, and a request is made with a limit of 10. Then the same request is made again with a limit of 10,10. Then comes the next page. The question is, why do we calculate all this again each time? But currently, there is no solution to avoid this.<\/p>\n<p><\/p>\n<p>There is an alternative solution that is implemented as a sidecar next to ClickHouse \u2014 <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/Vertamedia\/chproxy\">ClickHouse Proxy<\/a><\/noindex>.<\/p>\n<p><\/p>\n<p><strong>Kirill Shvakov:<\/strong> ClickHouse Proxy has a built-in rate limiter and a built-in results cache. A lot of configurations have been made there, as a similar task was being solved. Proxy allows limiting requests by queuing them and configuring how long the request cache lives. If the requests are truly identical, Proxy will return them multiple times while only querying ClickHouse once.<\/p>\n<p><\/p>\n<p>Nginx also has caching in the free version, and that will work as well. Nginx even has settings, so that if requests come in simultaneously, it will delay others until one is processed. However, the setup in ClickHouse Proxy is much better. It was created specifically for ClickHouse and these types of requests, so it fits more appropriately. Plus, the installation is straightforward. <\/p>\n<p><\/p>\n<h2 id=\"anchorasynchronousanchorkak-byt-snbspasinhronnymi-operaciyami-i-materializovannymi-predstavleniyami\"><noindex><a rel=\"nofollow\" name=\"asynchronous\"><\/a><\/noindex>How to handle asynchronous operations and materialized views?<\/h2>\n<p><\/p>\n<blockquote><p>There is a problem where operations with the replacing engine are asynchronous \u2014 data is recorded first, then it is collapsed. If there is a materialized view with some aggregates under the table, duplicates will be written to it. And if there isn\u2019t some complex logic, then the data will be duplicated. What can be done about this?<\/p>\n<p>An obvious solution is to implement a trigger for a certain class of materialized views during the asynchronous collapse operation. Are there any 'silver bullets,' plans for implementing similar functionalities?<\/p><\/blockquote>\n<p>It is worth understanding how deduplication works. What I am about to explain is not directly related to the question, but it\u2019s worth recalling just in case.<\/p>\n<p><\/p>\n<p>When inserting into a replicated table, there is deduplication of entire inserted blocks. If you reinsert the same block containing the same number of identical rows in the same order, the data will be deduplicated. You will receive 'Ok' in response to the insert, but in fact, only one batch of data will be written, and it will not be duplicated.<\/p>\n<p><\/p>\n<p>This is necessary for clarity. If you received 'Ok' during the insertion, it means your data has been inserted. If you got an error from ClickHouse, it means the data was not inserted, and the insertion needs to be repeated. However, if the connection was interrupted during the insertion, you won't know whether the data was inserted or not. The only option is to attempt the insertion again. If the data was actually inserted and you inserted it again, there will be deduplication of blocks. This is necessary to avoid duplicates. <\/p>\n<p><\/p>\n<p>It is also important how it works for materialized views. If the data was deduplicated during insertion into the main table, it will also not go into the materialized view.<\/p>\n<p><\/p>\n<p>Now regarding your question. You have a more complex situation because you are recording duplicates of individual rows. That is, not a whole batch is duplicated, but specific rows, and they are collapsed in the background. In fact, the data will collapse in the main table, while the non-collapsed data will go into the materialized view, and nothing will happen to the materialized views during merges. Because a materialized view is nothing more than a trigger on insert. In other operations, nothing additional happens to it.<\/p>\n<p><\/p>\n<p>And I cannot provide any joy here. You must look for a specific solution for this case. For example, can a replacement also be made in the materialized view, and perhaps the method of deduplication will work similarly. But unfortunately, this is not always the case. If it is aggregating, it will not work. <\/p>\n<p><\/p>\n<p><strong>Kirill Shvakov:<\/strong> We also had quite the makeshift setup at one point. There was an issue with ad impressions, and there were certain data points we could display in real-time \u2014 they were just impressions. They rarely duplicated, but if they did, we would still consolidate them later. However, some metrics were simply not allowed to be duplicated \u2014 clicks and all that. But we wanted to show them practically right away.<\/p>\n<p><\/p>\n<p>How were materialized views created? There were views where data was written directly \u2014 records were made in raw data, and then written into the views. At some point, the data was not very accurate; it got duplicated and so on. There's a second part of the table where it looks identical to the materialized views; in terms of structure, they are absolutely the same. Every once in a while, we recalculate the data, ensuring there are no duplicates, and write it into those tables. <\/p>\n<p><\/p>\n<p>We went through the API \u2014 doing this manually in ClickHouse won't work. The API checks: when I have the date of the last entry in the table, where the data is guaranteed to be correct and calculated, it makes a query to one table and another table. From one, it selects up to a certain point in time, and from the other, it fetches what hasn't been calculated yet. And it works, but not through a single ClickHouse.<\/p>\n<p><\/p>\n<p>If you have some API available \u2014 for analysts, for users \u2014 then, basically, that's an option. You always recalculate, always reassess. This can be done once a day or at some other interval. You choose the range for yourself that is neither necessary nor critical.<\/p>\n<p><\/p>\n<h2 id=\"anchordashboardanchorv-clickhouse-mnogo-logov-kak-ya-mogu-videt-vsyo-chto-proishodit-s-serverom-vnbspmomente\"><noindex><a rel=\"nofollow\" name=\"dashboard\"><\/a><\/noindex>ClickHouse generates a lot of logs. How can I see everything that's happening with the server in real-time?<\/h2>\n<p><\/p>\n<blockquote><p>ClickHouse has a vast number of different logs, and this number is increasing. In the newer versions, some of them are even enabled by default, while in older versions, you have to enable them during the update. Nevertheless, the quantity is growing. I'd like to see, in the end, what is currently happening with my server, maybe on a consolidated dashboard. <\/p>\n<p>Do you have a team at ClickHouse, or friends in other teams, who support some functionality of ready-made dashboards that display these logs as a finished product? In the end, just viewing logs in ClickHouse is great. But it would be really cool if it were already prepared in the form of a dashboard. I would enjoy that. <\/p><\/blockquote>\n<p>Dashboards exist, but they are not standardized. We have about 60 teams using ClickHouse in our company, and the strangest thing is that many of them have dashboards they've created themselves, and they are somewhat different. Some teams use an internal installation of 'Yandex.Cloud'. There are a few ready-made reports there, although not all of the necessary ones. Others have their own. <\/p>\n<p><\/p>\n<p>My colleagues from 'Metrics' have their own dashboard in Grafana, and I have my own on their cluster. I look at things like cache hits for the checkpoints cache there. And even more complicated is that we use different tools. I created my dashboard on a very old tool called Graphite-web. It's quite ugly. And I still use it, although Grafana would probably be more convenient and prettier. <\/p>\n<p><\/p>\n<p>The basic things in dashboards are the same. These are system metrics for the cluster: CPU, memory, disk, network. Others include the number of simultaneous requests, the number of concurrent merges, requests per second, maximum number of parts for MergeTree table partitions, replication lag, replication queue size, number of rows inserted per second, and number of blocks inserted per second. This all comes from metrics, not from logs.<\/p>\n<p><\/p>\n<p><strong>Vladimir Kolobaev:<\/strong> Alexey, I would like to correct you a bit. There is Grafana. Grafana has a datasource, which is ClickHouse. So I can make requests directly to ClickHouse from Grafana. In ClickHouse, there is a table with logs, and everyone has the same one. I want to reference this logs table in Grafana and see the queries that my server generates. It would be great to have such a dashboard.<\/p>\n<p><\/p>\n<p>I cobbled it together myself. But I have a question \u2014 if everything is standardized and Grafana is used by everyone, why is there no such official dashboard at 'Yandex'?<\/p>\n<p><\/p>\n<p><strong>Kirill Shvakov:<\/strong> In fact, the datasource for ClickHouse is currently supported by Altinity. I just want to provide a direction for further exploration and engagement. You can ask them, because Yandex is indeed developing ClickHouse, not the surrounding ecosystem. Altinity is the main company currently promoting ClickHouse. They won\u2019t abandon it; they will continue to support it. Because essentially, to load a dashboard on the Grafana website, you just need to register and upload it \u2014 there aren\u2019t significant problems. <\/p>\n<p><\/p>\n<p><strong>Alexey Milovidov:<\/strong> Over the past year, ClickHouse has added many profiling capabilities for queries. There are metrics for each query regarding resource usage. Recently, an even lower-level query profiler was added to examine where the query spends each millisecond. However, to make use of this functionality, I have to open the console client and type in the query that I constantly forget. I saved it somewhere but keep forgetting where. <\/p>\n<p><\/p>\n<p>I would like there to be a tool that simply indicates \u2014 here are your heavy queries, grouped by query classes. I could click on one, and it would tell me that it\u2019s heavy for this reason. There is currently no such solution. It\u2019s quite strange when people ask me: 'Are there any ready-made dashboards for Grafana?' I say: 'Go to the Grafana website, there\u2019s a community section for \u201cDashboards\u201d, and there\u2019s a dashboard from Dimka, a dashboard from Kostyan. I don\u2019t know what this is; I haven\u2019t used them myself.'<\/p>\n<p><\/p>\n<h2 id=\"anchorzenanchorkak-vozdeystvovat-na-merdzhi-chtoby-server-ne-padal-vnbspoom\"><noindex><a rel=\"nofollow\" name=\"zen\"><\/a><\/noindex>How to influence merges so that the server does not crash with OOM?<\/h2>\n<p><\/p>\n<blockquote><p>I have a table with only one partition, it is a ReplacingMergeTree. I have been writing data to it for four years. I needed to make an alter and delete some data from it.<\/p>\n<p>I did that, and during the processing of this query, all memory on all servers in the cluster was consumed, and all servers in the cluster simultaneously experienced OOM. Then they all restarted, began executing the merge of the same operation, the same block of data, and crashed into OOM again. Then they restarted and crashed again. This situation persisted.<\/p>\n<p>It later turned out that this was actually a bug that the team fixed. That's great, thank you very much. However, there's still a lingering feeling. Now, when I think about needing to do a merge in the table, I have a question \u2014 why can't I somehow limit these merges? For example, by the amount of required RAM, or in general by their quantity that will process this specific table.<\/p>\n<p>I have a table called 'Metrics', please process it in two threads. There's no need to create ten or five merges simultaneously, just do it in two. I think two will be enough for my memory, but processing ten might not be. Why is there still fear? Because the table is growing, and someday I might face a situation where not because of a bug, but due to the data changing in such large volumes, I simply won't have enough memory on the server. Then the server could run out of memory during the merge. In addition, I can cancel mutations, but not merges.<\/p><\/blockquote>\n<p>You know, during merges, the server won\u2019t run out of memory because only a small range of data uses RAM for a merge. So everything will be fine regardless of the data volume.<\/p>\n<p><\/p>\n<p><strong>Vladimir Kolobaev:<\/strong> Alright. Here's the thing: after the bug fix, I downloaded the new version, and on another smaller table with many partitions, I performed a similar operation. During the merge, about 100 GB of RAM was consumed on the server. I had 150 GB occupied, it consumed 100 GB, and I was left with a window of 50 GB, so I didn't run out of memory.<\/p>\n<p><\/p>\n<p>What currently protects me from running out of memory if it actually consumes about 100 GB of RAM? What should I do if the RAM runs out during merges?<\/p>\n<p><\/p>\n<p><strong>Alexey Milovidov:<\/strong> There is a problem that the memory consumption during the merge is not the only concern. The second issue is that if a merge has been scheduled, it must be executed because it is recorded in the replication log. The replication log contains the actions needed to bring the replica into a consistent state. If manual manipulations aren't performed to roll back this replication log, the merge will have to be executed one way or another.<\/p>\n<p><\/p>\n<p>Of course, it would be beneficial to have a memory limit that protects against OOM just in case. It won\u2019t help the merge to complete; it will start again, reach a certain threshold, throw an exception, and then start over \u2014 nothing good will come of this. However, implementing such a limit would be useful in principle.<\/p>\n<p><\/p>\n<h2 id=\"anchorgoanchorkak-budet-proishodit-razrabotka-golang-drayvera-dlya-clickhouse\"><noindex><a rel=\"nofollow\" name=\"go\"><\/a><\/noindex>How will the development of the Golang driver for ClickHouse take place?<\/h2>\n<p><\/p>\n<blockquote><p>The Golang driver written by Kirill Shvakov is now officially supported by the ClickHouse team. <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/ClickHouse\/clickhouse-go\">It's in the ClickHouse repository.<\/a><\/noindex>, it is now large and real.<\/p>\n<p>A small remark. There is a wonderful and universally loved storage for flexible, infinite order \u2014 Vertica. They also have an official Python driver supported by the Vertica developers. There have been times when the versions of the storage and the driver diverged significantly, causing the driver to stop working at some point. And the second point: support for this official driver seems to operate on a 'nipple' system \u2014 you write them an issue, and it hangs forever.<\/p>\n<p>I have two questions. Currently, Kirill's Golang driver is almost the default way to communicate from Golang to ClickHouse. Unless someone prefers to communicate through the HTTP interface because they like it that way. How will the development of this driver be managed? Will it synchronize with any breaking changes in the storage itself? And what is the process for issue resolution? <\/p><\/blockquote>\n<p><strong>Kirill Shvakov:<\/strong> First, it's about how everything is organized bureaucratically. This point hasn\u2019t been discussed, so I have nothing to answer.<\/p>\n<p><\/p>\n<p>To answer the question about the issue, a brief history of the driver is needed. I worked in a company that had a lot of data. It was an ad rotation system with a huge number of events that needed to be stored somewhere. At one point, ClickHouse appeared. We poured data into it, and for the first while, everything was fine, but then ClickHouse crashed. At that time, we decided that we didn't need it. <\/p>\n<p><\/p>\n<p>A year later, we returned to the idea of using ClickHouse, and we needed a way to write data there. The starting condition was that the hardware was very weak, with limited resources. But we always worked this way, so we looked toward the native protocol.<\/p>\n<p><\/p>\n<p>Since we were working with Go, it was clear that we needed a driver in Go. I was working on it almost full-time \u2014 it was my job. At a certain point, we developed it adequately, and basically, no one expected that anyone besides us would use it. Then CloudFlare came along with the exact same problem, and for a while, we worked closely with them because they had the same tasks. Furthermore, we were doing this both in ClickHouse itself and in the driver. <\/p>\n<p><\/p>\n<p>At some point, I just stopped dealing with them because my activity regarding ClickHouse and my work changed a bit. That's why issues remain unresolved. Occasionally, people commit to the repository when they need something. Then I look at pull requests and sometimes even make modifications myself, but that happens rarely.<\/p>\n<p><\/p>\n<p>I want to return to the driver. A few years ago, when all of this started, ClickHouse was different and had different capabilities. Now, there is an understanding of how to redesign the driver to improve it. If this happens, version 2 will be incompatible due to accumulated hacks. <\/p>\n<p><\/p>\n<p>I don't know how to organize this. I don't have much time myself. If some people are willing to refine the driver, I can assist them and explain what to do. However, active participation from Yandex in the development of the project has not been discussed yet. <\/p>\n<p><\/p>\n<p><strong>Alexey Milovidov:<\/strong> In reality, there is currently no bureaucracy regarding these drivers. The only thing is that they have been moved to an official organization, meaning this driver is recognized as the official default solution for Go. There are other drivers, but they are separate. <\/p>\n<p><\/p>\n<p>Internally, we don't have any development for these drivers. The question is whether we can hire a separate person not specifically for this driver, but for the development of all community drivers, or if we can find someone externally. <\/p>\n<p><\/p>\n<h2 id=\"anchorlazy-loadanchorvneshniy-slovar-ne-podnimaetsya-posle-perezagruzki-snbspvklyuchennoy-nastroykoy-lazy_load-chto-delat\"><noindex><a rel=\"nofollow\" name=\"lazy-load\"><\/a><\/noindex>The external dictionary does not load after a restart with the lazy_load setting enabled. What should we do?<\/h2>\n<p><\/p>\n<blockquote><p>We have the lazy_load setting enabled, and after the server restart, the dictionary does not load automatically. It only loads after a user accesses this dictionary. And upon first access, it throws an error. Is there any way to automatically load dictionaries using ClickHouse, or do we need to always monitor their readiness to prevent users from encountering errors?<\/p>\n<p>It\u2019s possible that we have an old version of ClickHouse, which is why the dictionary did not load automatically. Could that be the case?<\/p><\/blockquote>\n<p>First of all, dictionaries can be forcefully loaded using a query. <strong>system reload dictionaries<\/strong>Secondly, regarding the error \u2014 if the dictionary is already loaded, the queries will work with the data that has been loaded. If the dictionary has not yet been loaded, it will load at the time of the request.<\/p>\n<p><\/p>\n<p>For heavy dictionaries, this is not very convenient. For example, you need to pull a million rows from MySQL. Someone does a simple select, but this select will wait for that million rows. There are two solutions here. The first is to disable lazy_load. The second is to execute the request that uses the dictionary before putting any load on the server. <strong>system reload dictionary<\/strong> Alternatively, you can just execute a query that uses the dictionary. In this case, the dictionary will be loaded. You need to control the availability of dictionaries with lazy_load enabled, because ClickHouse does not automatically pull them in.<\/p>\n<p><\/p>\n<p>To the last question, the answer is either the version is old, or it needs debugging. <\/p>\n<p><\/p>\n<h2 id=\"anchorreload-dictionariesanchorkak-byt-snbsptem-chto-system-reload-dictionaries-ne-podgruzhaet-ni-odin-iznbspmnozhestva-slovarey-esli-hotya-by-odin-iznbspnih-padaet-snbsposhibkoy\"><noindex><a rel=\"nofollow\" name=\"reload-dictionaries\"><\/a><\/noindex>What to do if system reload dictionaries does not load any of the multiple dictionaries if even one of them fails with an error?<\/h2>\n<p><\/p>\n<blockquote><p>There is also a question regarding system reload dictionaries. We have two dictionaries \u2014 one does not load, the other loads. In this case, system reload dictionaries does not load either dictionary, and we have to manually load a specific one by its name using system reload dictionary. Is this also related to the version of ClickHouse?<\/p><\/blockquote>\n<p>I want to give you good news. This behavior has changed. So, if you update ClickHouse, it will also change. If you are not satisfied with the current behavior <strong>system reload dictionaries<\/strong>, update it, and let's hope it changes for the better.<\/p>\n<p><\/p>\n<h2 id=\"anchorconnectionanchorest-li-sposob-konfigurirovat-rekvizity-vnbspkonfige-clickhouse-no-ne-svetit-ih-prinbsposhibkah\"><noindex><a rel=\"nofollow\" name=\"connection\"><\/a><\/noindex>Is there a way to configure credentials in the ClickHouse config without exposing them during errors?<\/h2>\n<p><\/p>\n<blockquote><p>The next question is about errors related to the dictionary, specifically the credentials. We specified the connection credentials in the ClickHouse config for the dictionary, and in case of an error we receive these credentials and the password in the response. <\/p>\n<p>We solved this error by moving the credentials to the ODBC driver config. Is there any way to configure the credentials in the ClickHouse config without exposing these credentials in case of errors?<\/p><\/blockquote>\n<p>The solution here is indeed to specify these credentials in odbc.ini, and in ClickHouse itself only specify the ODBC Data Source Name. For other dictionary sources, this won\u2019t be the case \u2014 for the dictionary with MySQL or others, you should not see the password in the error message. I will also check for ODBC \u2014 if it's there, it just needs to be removed.<\/p>\n<p><\/p>\n<h2 id=\"anchorzoom-backgroundsanchorbonus-fony-dlya-zuma-snbspposidelok\"><noindex><a rel=\"nofollow\" name=\"zoom-backgrounds\"><\/a><\/noindex>Bonus: Zoom backgrounds from hangouts<\/h2>\n<p><\/p>\n<p>By clicking on the image, the most persistent readers will unlock bonus backgrounds featuring hangout sessions. We\u2019ll put out the fire together with Avito's tech mascots, consult with colleagues from the sysadmin room or an old-school computer club, and hold our daily meetings under the bridge against a graffiti backdrop.<\/p>\n<p><\/p>\n<p><noindex><a rel=\"nofollow\" href=\"http:\/\/amp.gs\/KvUr\"><img decoding=\"async\" alt=\"ClickHouse for advanced users in Q&amp;A\" src=\"\/wp-content\/uploads\/2020\/05\/38b2ea076283285d934913c395863eb1.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/a><\/noindex><\/p>\n<p>Source: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/company\/avito\/blog\/500678\/\">habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u0412 \u0430\u043f\u0440\u0435\u043b\u0435 \u0438\u043d\u0436\u0435\u043d\u0435\u0440\u044b \u0410\u0432\u0438\u0442\u043e \u0441\u043e\u0431\u0438\u0440\u0430\u043b\u0438\u0441\u044c \u043d\u0430&nbsp;\u043e\u043d\u043b\u0430\u0439\u043d-\u043f\u043e\u0441\u0438\u0434\u0435\u043b\u043a\u0438 \u0441 \u0433\u043b\u0430\u0432\u043d\u044b\u043c \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a\u043e\u043c ClickHouse \u0410\u043b\u0435\u043a\u0441\u0435\u0435\u043c \u041c\u0438\u043b\u043e\u0432\u0438\u0434\u043e\u0432\u044b\u043c \u0438 \u041a\u0438\u0440\u0438\u043b\u043b\u043e\u043c \u0428\u0432\u0430\u043a\u043e\u0432\u044b\u043c, Golang-\u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a\u043e\u043c \u0438\u0437&nbsp;\u043a\u043e\u043c\u043f\u0430\u043d\u0438\u0438 Integros. \u041e\u0431\u0441\u0443\u0436\u0434\u0430\u043b\u0438, \u043a\u0430\u043a \u043c\u044b \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u043c \u0441\u0438\u0441\u0442\u0435\u043c\u0443 \u0443\u043f\u0440\u0430\u0432\u043b\u0435\u043d\u0438\u044f \u0431\u0430\u0437\u0430\u043c\u0438 \u0434\u0430\u043d\u043d\u044b\u0445 \u0438 \u043a\u0430\u043a\u0438\u0435 \u0441\u043b\u043e\u0436\u043d\u043e\u0441\u0442\u0438 \u0443&nbsp;\u043d\u0430\u0441 \u0432\u043e\u0437\u043d\u0438\u043a\u0430\u044e\u0442. \u041f\u043e&nbsp;\u043c\u043e\u0442\u0438\u0432\u0430\u043c \u0432\u0441\u0442\u0440\u0435\u0447\u0438 \u043c\u044b \u0441\u043e\u0431\u0440\u0430\u043b\u0438 \u0441\u0442\u0430\u0442\u044c\u044e \u0441&nbsp;\u043e\u0442\u0432\u0435\u0442\u0430\u043c\u0438 \u044d\u043a\u0441\u043f\u0435\u0440\u0442\u043e\u0432 \u043d\u0430&nbsp;\u043d\u0430\u0448\u0438 \u0438 \u0437\u0440\u0438\u0442\u0435\u043b\u044c\u0441\u043a\u0438\u0435 \u0432\u043e\u043f\u0440\u043e\u0441\u044b \u043f\u0440\u043e&nbsp;\u0431\u044d\u043a\u0430\u043f\u044b, \u0440\u0435\u0448\u0430\u0440\u0434\u0438\u043d\u0433 \u0434\u0430\u043d\u043d\u044b\u0445, \u0432\u043d\u0435\u0448\u043d\u0438\u0435 \u0441\u043b\u043e\u0432\u0430\u0440\u0438, Golang-\u0434\u0440\u0430\u0439\u0432\u0435\u0440 \u0438 \u043e\u0431\u043d\u043e\u0432\u043b\u0435\u043d\u0438\u0435 \u0432\u0435\u0440\u0441\u0438\u0439 ClickHouse. \u041e\u043d\u0430 \u043c\u043e\u0436\u0435\u0442 \u0431\u044b\u0442\u044c [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":80776,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-80775","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.2 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u0412 \u0430\u043f\u0440\u0435\u043b\u0435 \u0438\u043d\u0436\u0435\u043d\u0435\u0440\u044b \u0410\u0432\u0438\u0442\u043e \u0441\u043e\u0431\u0438\u0440\u0430\u043b\u0438\u0441\u044c \u043d\u0430 \u043e\u043d\u043b\u0430\u0439\u043d-\u043f\u043e\u0441\u0438\u0434\u0435\u043b\u043a\u0438 \u0441 \u0433\u043b\u0430\u0432\u043d\u044b\u043c \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a\u043e\u043c ClickHouse \u0410\u043b\u0435\u043a\u0441\u0435\u0435\u043c \u041c\u0438\u043b\u043e\u0432\u0438\u0434\u043e\u0432\u044b\u043c \u0438 \u041a\u0438\u0440\u0438\u043b\u043b\u043e\u043c \u0428\u0432\u0430\u043a\u043e\u0432\u044b\u043c, Golang-\u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a\u043e\u043c \u0438\u0437 \u043a\u043e\u043c\u043f\u0430\u043d\u0438\u0438 Integros.\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/clickhouse-dlya-prodvinutyh-polzovatelej-v-voprosah-i-otvetah\" \/>\n\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.2\" \/>\n\t\t<meta property=\"og:locale\" content=\"en_US\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47ClickHouse \u0434\u043b\u044f \u043f\u0440\u043e\u0434\u0432\u0438\u043d\u0443\u0442\u044b\u0445 \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u0435\u0439 \u0432 \u0432\u043e\u043f\u0440\u043e\u0441\u0430\u0445 \u0438 \u043e\u0442\u0432\u0435\u0442\u0430\u0445 | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u0412 \u0430\u043f\u0440\u0435\u043b\u0435 \u0438\u043d\u0436\u0435\u043d\u0435\u0440\u044b \u0410\u0432\u0438\u0442\u043e \u0441\u043e\u0431\u0438\u0440\u0430\u043b\u0438\u0441\u044c \u043d\u0430 \u043e\u043d\u043b\u0430\u0439\u043d-\u043f\u043e\u0441\u0438\u0434\u0435\u043b\u043a\u0438 \u0441 \u0433\u043b\u0430\u0432\u043d\u044b\u043c \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a\u043e\u043c ClickHouse \u0410\u043b\u0435\u043a\u0441\u0435\u0435\u043c \u041c\u0438\u043b\u043e\u0432\u0438\u0434\u043e\u0432\u044b\u043c \u0438 \u041a\u0438\u0440\u0438\u043b\u043b\u043e\u043c \u0428\u0432\u0430\u043a\u043e\u0432\u044b\u043c, Golang-\u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a\u043e\u043c \u0438\u0437 \u043a\u043e\u043c\u043f\u0430\u043d\u0438\u0438 Integros.\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/clickhouse-dlya-prodvinutyh-polzovatelej-v-voprosah-i-otvetah\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2020-05-08T11:42:47+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2020-05-08T11:42:47+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47ClickHouse for advanced users in questions and answers | ProHoster","description":"In April, Avito engineers planned an online meetup with ClickHouse's lead developer Alexey Milovidov and Kirill Shvakov, a Golang developer from Integros.","canonical_url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/clickhouse-dlya-prodvinutyh-polzovatelej-v-voprosah-i-otvetah","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"en_US","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47ClickHouse \u0434\u043b\u044f \u043f\u0440\u043e\u0434\u0432\u0438\u043d\u0443\u0442\u044b\u0445 \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u0435\u0439 \u0432 \u0432\u043e\u043f\u0440\u043e\u0441\u0430\u0445 \u0438 \u043e\u0442\u0432\u0435\u0442\u0430\u0445 | ProHoster","og:description":"\u0412 \u0430\u043f\u0440\u0435\u043b\u0435 \u0438\u043d\u0436\u0435\u043d\u0435\u0440\u044b \u0410\u0432\u0438\u0442\u043e \u0441\u043e\u0431\u0438\u0440\u0430\u043b\u0438\u0441\u044c \u043d\u0430 \u043e\u043d\u043b\u0430\u0439\u043d-\u043f\u043e\u0441\u0438\u0434\u0435\u043b\u043a\u0438 \u0441 \u0433\u043b\u0430\u0432\u043d\u044b\u043c \u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a\u043e\u043c ClickHouse \u0410\u043b\u0435\u043a\u0441\u0435\u0435\u043c \u041c\u0438\u043b\u043e\u0432\u0438\u0434\u043e\u0432\u044b\u043c \u0438 \u041a\u0438\u0440\u0438\u043b\u043b\u043e\u043c \u0428\u0432\u0430\u043a\u043e\u0432\u044b\u043c, Golang-\u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a\u043e\u043c \u0438\u0437 \u043a\u043e\u043c\u043f\u0430\u043d\u0438\u0438 Integros.","og:url":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/clickhouse-dlya-prodvinutyh-polzovatelej-v-voprosah-i-otvetah","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-05-08T11:42:47+00:00","article:modified_time":"2020-05-08T11:42:47+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"80775","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 16:11:22","updated":"2022-09-28 05:48:13","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/80775","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/comments?post=80775"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/posts\/80775\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media\/80776"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/media?parent=80775"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/categories?post=80775"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/en\/wp-json\/wp\/v2\/tags?post=80775"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}