Публикация участника Александр Филатов

Hello. Today is a day for some special stuff: 𝗛𝗼𝘄 𝘁𝗼 𝗯𝗶𝘁 𝘁𝗵𝗲 𝗱𝗮𝘁𝗮𝗯𝗮𝘀𝗲 𝗲𝗻𝗴𝗶𝗻𝗲 𝗳𝗿𝗼𝗺 𝘀𝗰𝗿𝗮𝘁𝗰𝗵. We have been using #AnchorModeling for mutable data from the very beginning. And it is persuaded that merge or colocated joins is the magic that makes it work. And even more — that there is nothing better for such a highly normalized data modeling approach. But we have used #Vertica and now #Trino for a very long time to know the limits. We know exactly that general-case implementation for joins in distributed analytical engines like Vertica has obvious limits when used with such atomized data structures. In the real world, to query highly normalized data, you have to join tens of tables. And you may assume that the larger the number of joins, the slower your query gets. It is not an irresistible rule in all possible cases because of cost-based optimizations. But even if all of the query tables are sorted by exactly the same field in the same manner, you can easily check that query duration increases dramatically while adding tens of new attributes to join. But why? Is a merge or colocated join magic fake? It does not. It is just not implemented properly. With anchor modeling, your data is extremely well organized for such algorithms, but it does not fit a general case implementation in your database engine. You can easily overperform it (in terms of time or resource consumption) by implementing the join technique from scratch with your own hands. And here is what we get in terms of time. Have a nice day, and never stop testing the limits.

Ищу инженеров в команду хранилища данных. Если вам интересны большие данные, и вы умеете писать код, обращайтесь в личку (tg @phil03) или оставляйте данные для связи по ссылке. DeltaSharing (https://delta.io/sharing/) на стероидах сам себя не сделает! Скупое писание тут: https://lnkd.in/gn9QkSkN

Hello again!  Today is a day for some #ops issues out of the hood. While we are adopting Trino for our workloads, we are still using #Vertica for daily calculations. And there is something new we have faced recently. Vertica started getting killed by the OOM killer all of a sudden. Atop showed that we were not even close to the memory limit, using less than 400GB out of 500GB. And there was no comparable memory-consuming process other than Vertica itself. So where did the memory go in order for oom killer to go and sluter vertica? Graphana charts for all the hosts in a cluster showed like nothing suspicious. Too many hosts at once on a single memory chart didn't help the engineer on duty catch the glitch. He saw the common pattern of consumption for all hosts, and nothing seemed to be wrong. It turned out there was a memory leak on our hosts, and about 70GB were occupied by slab, which is not so obvious to catch an eye on on a 500GB scale. We know that Vertica sees all the memory on the host as if she owns all of it. And the OOM killer saw it in a different way with respect to slab memory. It looked like it started to grow when we set some of the policies to maintain metrics history in Vertica for a few months. Everything went back to normal after turning them off and a single reboot. You may think that nothing can take away the memory you have left for a single greedy process on a host. Well, it might not be true.

Hello 2024. It was a tough month, but I am still here to share with you some results that we achieved recently. You may have heard that we are currently adopting #Trino for analytical use cases in Avito. And while the adoption is in progress, we support the reading of Trino-generated #ORC-on-S3 data in #Vertica. We have to do this to make the adoption smoother. And recently, I got the numbers. After all the optimizations that we've made, we've got pretty amazing performance with external data. Turns out we are not just faster than the original ORC+S3 implementation, but pretty close to the native Vertica storage. Yes, it is still bad in dynamic filtering and cost-based optimizations, but we can definitely read data from external storage with comparable to native storage performance. How did we do this? Some major steps that we made: - optimize processing flow for tables with 100K+ s3 objects - apply filters on a row-by-row basis instead of row groups  - make columns really pruned from buffers returned from UDX - implement RLE compaction for rows returned from UDX After all, we got closer to reading the external data as if it were native to Vertica. Don't stop testing the limits.

Hello again! We built a data warehouse in Avito in 2013. Many things have changed for over 10 years, but we are still using #AnchorModeling, with every single attribute stored in a separate table. And we have been persuaded that it is very difficult to work with anchor modeling. Every now and then, we faced complaints about it. It is really a culture shock to face tens of thousands of tables for the first time. Don't say anyone, but we have replaced it with regular tables for imutable click stream events in 2019 (which is now called the active stream approach). But I truly believe that it is still a significant effort for untrained employees to dive into data stored with anchor modeling. And recently I came up with numbers. Turns out that only 25% of newly created calculations in Avito are primarily based on anchor modeling (or DDS) tables. The vast majority is based on other data marts. You may say it is because of its complexity. Well, yes, it is easier to use someone else's wide tables, and no, it does not take more time to create calculations upon DDS than it does to create calculations upon other data marts. The median time for creating a new calculation on DDS for both untrained employees and analysts is less or equal to the time for creating calculations on other data marts. Basically, it is not harder. It is just a preliminary step to make some business logic upon it, which is not as time-consuming as scaring away new users. Merry Christmas

Hello there!   We have been using #Vertica for over then 10 years in Avito. We mastered extending it with new connectors to achieve the Lakehouse experience using the Vertica SDK.   And there is one simple thing you should know while optimizing your new function or new connector. You may be as fast as Flash in the DC universe in retrieving or processing data inside your extension. But there is always one thing that you cannot optimize. And that thing is Vertica itself.   The very first thing a columnar engine provides to achieve better performance is a column pruning optimization when you read and process only the columns that you really want. And guess what? There is no support for column pruning for extensions in the Vertica SDK. So if you need to retrieve 5 columns out of an external table with 20 columns, you still have to pass 15 columns through sdk filled with nulls.   Basicly, all you have left is to reduce the number of rows instead of the number of columns or to pack rows into some fancy type to reduce nulls. Here is a perf output showing the extension code (purple lines) inside SDK calls, taking just 20% of the overall time to retrieve data from the source and leaving 80% to pass data to Vertica.

Митап CedrusData, расскажем что у нас уже получилось с #trino. Подключайтесь сегодня в 18:00 МСК https://lnkd.in/eHRkJY5c

As you may know, we are using #Clickhouse to store clicks in Avito. And if you use Clickhouse you may also know that it is a never-ending engineering effort to make it work without glitches.    And here I will share with you a simple thing we made to make calculations in Clickhouse more stable. We continuously face the problem with clickhouse-zookeepeer integration. I'm talking about all the "Session expired", "Table is in readonly mode", "XID overflow" and "Broken pipe" errors.    And guess what. Turns out there is a single zookeeper session for all zookeeper requests in Clickhouse instance. And there is a timeout. So when Clickhouse sees that the earliest not yet processed request in a queue waited too long, it kills the connection, and all the queries that were running at that moment got cancelled, complaining that session expired, that XID is too big (but it is not a session ID, it is a CLOSE_XID request to close session) and getting table in a readonly mode for a moment.   So we extended operation_timeout (which is actually "wait in a queue" timeout) to 30 seconds, and all the errors gone. Check it out, you may be just one setting away from reliable clickhouse-zookeeper integration.   Here is the code I'm talking about:

Sharing with you simple finding with optimising columnar ORC format. We are using ORC in Avito to store archive data. Originally we picked ORC over Parquet because of performance. A few years ago I noticed Wu Gang paper about AliORC optimisations over vanilla ORC and now I finally get to experiment with it while migrating data to Trino. And here is what I get on my queries. Turns out that you can increase performance just by switching column order. I just picked most used columns for biggest table I have and make them first in a file. As simple as it is. And changing column order impacts decompression time significantly.

Здравствуйте! Если вы только начали отслеживать меня, то вот о чем я пишу: «#orc, #vertica, #clickhouse,» и «#trino». А если вы можете предложить другие интересные темы для публикаций, обязательно сообщите мне об этом! ✒️