--- url: 'https://altinity.com/webinarspage/building-an-analytic-extension-to-mysql-with-clickhouse-and-open-source' title: Building an Analytic Extension to MySQL with ClickHouse® and Open Source author: name: Cristina Munteanu url: 'https://altinity.com/author/cmunteanu/' date: '2023-03-06T10:21:57-08:00' modified: '2026-05-29T14:16:12-07:00' type: post summary: 'Learn how to combine MySQL and ClickHouse® to build a high-performance analytic pipeline using the Altinity Sink Connector, Debezium CDC, and ReplacingMergeTree, turning your existing MySQL deployment into a real-time analytics powerhouse without replacing your operational database.' categories: - Webinars tags: - Altinity - ClickHouse - Open Source - Percona - webinar image: 'https://altinity.com/wp-content/uploads/2023/02/Percona-Partner-Webinar.jpg' published: true --- # Building an Analytic Extension to MySQL with ClickHouse® and Open Source **Recorded:** Thursday, March 2, 2023 **Presenters:** Vadim Tkachenko and Kanthi Subramanian This joint Altinity and Percona webinar makes the case for ClickHouse® as an analytic extension to MySQL rather than a replacement for it. Percona CTO, Vadim Tkachenko, opens with a clear-eyed comparison: MySQL excels at high-concurrency OLTP with fast single-row operations, but struggles badly with aggregation queries on large datasets. A benchmark on 176 million airline delay records shows MySQL taking over nine minutes on a query, ClickHouse answers in half a second. Altinity developer advocate Kanti Subramanian then walks through the full replication pipeline: an initial dump using MySQL Shell and a custom schema-mapping load tool, a checksum-based validation utility, and the Altinity Sink Connector for ClickHouse in both its Kafka-based and new single-binary Lightweight forms. The Lightweight connector uses Debezium to read the MySQL binlog and replicates inserts, updates, deletes, and DDL changes into ClickHouse ReplacingMergeTree tables in guaranteed order, without the operational complexity of running Kafka. A live demo shows automatic table creation, date-limit mapping, ALTER TABLE replication, and multi-statement DDL ordering. The session closes with Q&A covering the FINAL keyword performance trade-offs, schema transformation strategies, sparse indexing differences from MySQL, and PostgreSQL support in the same connector. **Here are the slides:** [Kanthi-Vadim-Analytic_extension_to_mysql_osa_con_2022](https://altinity.com/wp-content/uploads/2024/05/Kanthi-Vadim-Analytic_extension_to_mysql_osa_con_2022.pdf)[Download](https://altinity.com/wp-content/uploads/2024/05/Kanthi-Vadim-Analytic_extension_to_mysql_osa_con_2022.pdf) ## **Key Moments (Timestamps)** Key moments generated with AI assistance. - 0:00 – Introduction and housekeeping; speakers introduced - 0:52 – Vadim Tkachenko begins: what is MySQL and why use it? - 1:43 – MySQL OLTP strengths: ACID, concurrency, fast single-row transactions - 3:52 – MySQL analytics limitations: demo query on 176M airline rows takes 9 minutes - 5:54 – Same query in ClickHouse: under half a second - 6:35 – Why the difference: row vs. columnar storage, single-thread vs. parallel execution - 8:47 – Column storage I/O example: 60 GB in MySQL vs. 2.6 MB per CPU in ClickHouse - 11:24 – MySQL and ClickHouse are complementary, not competing - 13:12 – Kanti Subramanian takes over: how to build the replication pipeline - 14:22 – Altinity Sink Connector architecture: Debezium, Kafka Connect, JDBC - 15:40 – Why to do initial dump and load separately from CDC - 16:57 – Custom dump and load tool: MySQL Shell, schema mapping, date limit handling - 18:54 – Auto-generated ClickHouse schema: ORDER BY, ReplacingMergeTree, version column - 20:12 – Data validation: checksum-based comparison tool - 21:00 – Setting up CDC: Sink Connector configuration and binlog offset - 21:47 – Lightweight Sink Connector: single binary, no Kafka required - 23:14 – DDL support and ordered execution: the problem with Kafka and out-of-order DDL - 25:05 – Live demo begins - 28:36 – Demo: tables replicated, count verified between MySQL and ClickHouse - 29:42 – Demo: date limit mapping in action - 30:11 – Demo: ALTER TABLE add column replicated with nullable syntax mapping - 31:41 – Demo: multi-statement DDL (RENAME + UPDATE) executed in correct order - 33:52 – PostgreSQL support demo - 37:07 – Roadmap: PostgreSQL GA, SQL Server, circular transactions - 37:53 – Q&A begins --- ## **Webinar Transcript** ### **[0:00] — Introduction and Housekeeping** **Kirsten:** Hey everyone, thanks for joining us today for our talk on building an analytic extension to MySQL with ClickHouse® and open source. My name is Kirsten Foon and I’m part of the marketing team here at Percona. Today I’m joined by our CTO Vadim Tkachenko and Altinity developer advocate Kanti Subramanian, who are going to discuss how ClickHouse’s unique capabilities can give your MySQL database a little help to maximize its full potential. Before we get started, a few housekeeping notes. All lines are on mute for the duration of this call. Questions can be submitted at any time to the Q&A widget on your console. We’ll get to as many questions as we can at the end of the session, and for those we can’t answer we will create a blog post to address them. The session is being recorded and will be available for replay afterwards along with the slides. With that, I’ll let Vadim kick things off. ### **[0:52] — What Is MySQL and Why Use It?** **Vadim:** Thank you, Kirsten. Can you hear me okay? **Kirsten:** Perfectly fine. **Vadim:** All right. Let’s proceed. Today we want to talk about combining MySQL and ClickHouse and building a pipeline between them. Before we jump into that, let me talk a little about MySQL. If you’re attending a Percona webinar you probably know what MySQL is, but if not, or if you just found this video on the internet, I’ll do a quick introduction into what MySQL is, what its strengths and weaknesses are, and why we would want to combine it with an analytical solution like ClickHouse. MySQL is what we call an OLTP, or operational database. From a high-level view, these are databases that can handle thousands of concurrent transactions at the same time, and in some cases up to a million transactions per second. ### **[1:43] — MySQL OLTP Strengths** **Vadim:** Operational databases come with strong characteristics. They support ACID transactions, they support excellent concurrency, they’re typically very fast for point lookups, and very fast for short transactions. By short transaction I mean: if you visit a web page, you typically don’t want to wait even half a second, or you may go to a different website. With MySQL and operational databases, we target very fast, very quick transactions and response times. MySQL is excellent for building online transactional applications. We see MySQL widely used in e-commerce websites, in online gaming, including many very popular, highly concurrent online games, and in social networks. Facebook and Twitter are well-known examples that were built on MySQL. ### **[3:52] — MySQL Analytics Limitations** **Vadim:** However, when it comes to analytics, running an analytical query with MySQL is problematic. You can still run SQL queries and MySQL will accept them, but we only recommend this for very small datasets. Aggregation queries, meaning filtering, GROUP BY, averaging, finding min/max values, on tables with even 10 million rows can be very problematic for MySQL. Let me show some experiments we did. One of my favorite datasets is the airline delays dataset. It’s big and it’s available for everybody to download from the U.S. Department of Transportation. We took this dataset, which contains 176 million rows, and ran a basic aggregation query: find the count of delays of 15 minutes or more between 2000 and 2008. MySQL takes nine minutes and seven seconds to process this query. ClickHouse gives the answer in half a second. For another comparison, we ran a GROUP BY aggregation to find the average delay by year, with no filter. MySQL takes four minutes to execute this. ClickHouse does it in under one second. ### **[6:35] — Why the Difference: Architecture** **Vadim:** So what gives such a big difference? It comes down to architectural differences in how you store and organize data. MySQL, like many operational databases, stores data in rows. MySQL also typically handles queries in a single thread: one user comes in, executes a query, and MySQL allocates one thread on one CPU to that user. This is a good optimization for high concurrency because if you give only one CPU per user and you have multiple CPUs and multiple users arriving at the same time, they can all execute queries concurrently. But this architectural decision inside MySQL is actually the limiting factor for running analytical queries. That’s where ClickHouse takes a different approach. ClickHouse stores data in columns, not rows. It has optimizations to minimize I/O between memory and storage. And it can run a single query in parallel. As a counterexample to the MySQL case: if a single user comes in and executes one query, ClickHouse can use multiple CPUs, 4, 8, 16 CPUs, to execute that single query. That’s why it can produce results very fast. ### **[8:47] — Column Storage I/O: A Concrete Example** **Vadim:** Let me show a concrete example of the difference between row storage and column storage. Row storage is the MySQL way. If you need to read only partial data, MySQL still reads the whole row. With ClickHouse, if you need only one column for your query, ClickHouse can minimize the amount of data to process. In a hypothetical example: imagine a query that only needs three columns. In MySQL, reading 60 gigabytes of data, MySQL will read all 60 gigabytes from storage into memory and process everything. In ClickHouse, reading only three columns out of a wider table takes only about 3 percent of the space, roughly 1.7 gigabytes. And because ClickHouse is very efficient at compression, on disk it may only occupy 21 megabytes. With 8 CPUs working in parallel, each CPU needs to bring only 2.6 megabytes from storage into memory. ### **[11:24] — MySQL and ClickHouse Are Complementary** **Vadim:** But one database cannot replace another. Each has drawbacks and benefits, and this is where we see the synergy. MySQL is very good at transactional processing and fast single-row updates. ClickHouse does not support transactions, and updating a single row in ClickHouse can be very problematic because it still needs to read and rewrite large amounts of data. Deletes are also very problematic in ClickHouse. MySQL supports very high concurrency: multiple users can execute queries against a single MySQL instance at the same time. ClickHouse, as we just discussed, can use a lot of resources for a single user, so it struggles under high concurrent access with many users. This is why we think of combining the two systems. Finding a way to ship data from MySQL to ClickHouse will benefit MySQL users by providing them with [using ClickHouse as an analytic extension for MySQL](https://altinity.com/blog/using-clickhouse-as-an-analytic-extension-for-mysql), allowing them to execute analytical queries much faster. I’ll now pass the slides to Kanti, who will talk about how we can actually build that system. ### **[13:32] — Building the Replication Pipeline** **Kanti:** Can you guys hear me? **Kirsten:** I can hear you, yeah. **Kanti:** Perfect. Thanks again for talking about some of the benefits of ClickHouse as an analytical database and where MySQL struggles. I’m going to talk about some of our experience actually transferring data from MySQL into ClickHouse and the tools we’ve developed to perform the transfer and make a data engineer’s or DBA’s job easier in maintaining that pipeline. So let’s assume you’ve decided to migrate data into ClickHouse. One of the first steps is to identify the tables and databases in MySQL that you want replicated into ClickHouse. ### **[14:22] — Altinity Sink Connector Architecture** **Kanti:** One of the solutions we’ve developed is the [Altinity Sink Connector for ClickHouse](https://altinity.com/blog/fast-mysql-to-clickhouse-replication-announcing-the-altinity-sink-connector-for-clickhouse). It is based on the Kafka Connect framework. It uses the Debezium library, which internally uses a MySQL binlog reader library that reads all changes from the binlog. We then use the ClickHouse JDBC library to write data into ClickHouse. One thing to notice: we definitely suggest using ReplacingMergeTree, because we have logic to support updates and deletes in ClickHouse using it. ### **[15:40] — Why to Do the Initial Dump and Load Separately** **Kanti:** There is a reason you might want to do the initial dump and load separately from CDC. Debezium is single-threaded. It uses the MySQL binlog library and does a sequential read, so if you have millions of rows in MySQL the initial snapshot may be slower. You want to look at other solutions that do that initial transfer faster. You also need a solution that validates the data: a basic count check is not enough. ### **[16:57] — Custom Dump and Load Tool** **Kanti:** Why do we need custom dump and load tools? MySQL and ClickHouse are very similar in SQL compliance and you can actually copy a lot of queries and execute them directly. But there are differences in data types and data limits. For example, the maximum date in MySQL is year 9999, while in ClickHouse it is year 2299. If you try to insert a row with year 9000 you’ll get an error in ClickHouse. Our tool also creates your schema automatically. It reads the primary key from your MySQL CREATE TABLE syntax and transforms it into an ORDER BY clause and a ReplacingMergeTree table in ClickHouse. That’s automated; you don’t have to do it manually. For speed, we recommend using MySQL Shell to dump data into SQL files. MySQL Shell is significantly faster than alternatives like mysqldump or mydumper. We then have a tool that wraps the ClickHouse client, reads those SQL files, maps the schema into ReplacingMergeTree tables in ClickHouse, and adds the version and sign columns required for updates. ### **[18:54] — Auto-Generated ClickHouse Schema** **Kanti:** The auto-generated ClickHouse schema automatically assigns the version column required for ReplacingMergeTree. It picks the primary key from MySQL, in this example employee_number, and populates that as the ORDER BY column in ClickHouse. ### **[20:12] — Data Validation Tool** **Kanti:** For validating data, a basic count check is not enough. We’ve developed a tool that calculates checksums between MySQL and ClickHouse data, so you can verify the data was copied correctly. It also handles edge cases like the date limit difference: the tool ensures that a MySQL date value of year 9000 is correctly mapped to ClickHouse’s maximum value. After validation is done, you configure the Sink Connector to start from a specific position in the binlog and begin CDC. From that point on, every change in MySQL will automatically be replicated into ClickHouse. ### **[21:47] — Lightweight Sink Connector: Single Binary, No Kafka** **Kanti:** We’ve also been working on a more simplified architecture. With the Kafka Connect framework there are a lot of services to deploy: Debezium, Kafka itself, and the Sink Connector. That’s not easy to maintain. The new architecture is [a more simplified application — it’s just one executable](https://altinity.com/blog/announcing-version-2-0-of-the-altinity-clickhouse-sink-connector). It’s one service in Kubernetes. It uses the Debezium library to read the binlog from MySQL, uses the Sink Connector library to transform those Debezium messages into ClickHouse JDBC statements, and inserts records into ClickHouse in batches. Altinity also contributes to Debezium, specifically to the JDBC storage module, which enables the Lightweight Connector to store binlog offset information directly in ClickHouse tables, eliminating the need for Kafka. ### **[23:14] — DDL Support and Ordered Execution** **Kanti:** One of the reasons this architecture is useful is DDL support. With the Kafka Connect framework, DDL events come in as a separate Kafka topic. When you’re consuming records from Kafka, if you have an UPDATE employees followed by a TRUNCATE employees, and you read them out of order, you’ll be truncating the employees table first and then trying to update it, which breaks the pipeline. With our new architecture that doesn’t use Kafka, we read events in sequence and can guarantee the order of DDL execution. ### **[25:05] — Live Demo** **Kanti:** Let me do a quick demo. The simplified architecture also makes configuration easy. It’s just one configuration file: you define the source database URL, port, username, and password; the databases to include in replication; optional table filters; and the ClickHouse destination configuration. Settings like auto.create.tables will automatically create tables in ClickHouse if they don’t exist. So we’ve got MySQL running and ClickHouse running. We’re trying to replicate the employees table. The data is already there in MySQL, about 300,000 records across multiple tables. I’ll start the application now. You’ll see that the replication tool automatically creates the tables. Departments was created, employees was created. Let’s check the count. We have matching counts between MySQL and ClickHouse. Now let me demonstrate the date limit handling. The three earliest records have a maximum date of 1999. If you don’t have the date limit mapping, ClickHouse will reject a MySQL date of year 9000. The application tool maps it to the system maximum value automatically. Now let’s run a couple of DDL commands. First, let’s add a new column employee_address, a nullable constraint, after first_name. After executing this in MySQL the column is added in ClickHouse too. You can see the tool reads the null constraint and maps it into nullable syntax in ClickHouse, also mapping MySQL data types into their corresponding ClickHouse types. Now for a more complex example of how we handle DDLs in sequence. Let’s take a statement that first renames the employees table to employees_old, and then in the same statement updates employees_old and sets the address column. As I discussed, we guarantee the order here. The rename runs first, then the update executes against the correctly named table, and the record is updated correctly. Using the FINAL keyword forces the merging process, and you can see the updated column in ClickHouse matching the same query in MySQL. ### **[33:52] — PostgreSQL Support Demo** **Kanti:** I also want to quickly show that we support [replicating data from PostgreSQL](https://altinity.com/blog/replicating-data-from-postgresql-to-clickhouse-with-the-altinity-sink-connector) with this same tool. It’s a very similar configuration: you provide the PostgreSQL database information, specify the databases and any table filters, and provide the ClickHouse destination information. You can see the tables were created instantly. The data types map correctly from PostgreSQL to ClickHouse: UUID maps to ClickHouse UUID, JSON columns map to String for compatibility, and timestamp with timezone columns map to the appropriate ClickHouse type. ### **[37:07] — Roadmap** **Kanti:** In terms of the roadmap: PostgreSQL support is being finalized, SQL Server support is next, and the biggest upcoming feature is support for circular transactions. The new simplified Lightweight Connector is something we plan to open source in the next few weeks, so please reach out if you’re interested. ### **[37:53] — Q&A** **Kirsten:** Thank you, Kanti and Vadim. There are a few questions that have come through. **Kirsten:** First: is ClickHouse open source and what kind of license does it have? **Kanti:** ClickHouse is Apache 2.0 licensed, so it is open source. **Kirsten:** Is the insert size configurable in terms of how many rows are batched together? **Kanti:** Yes, that is a configuration parameter you can define. ClickHouse works better if you insert records in bigger batches. There’s no single perfect parameter value; it’s something you should tune based on your workload. **Kirsten:** There was also a follow-up about database-as-a-service offerings for ClickHouse. **Kanti:** Altinity has a database-as-a-service offering called Altinity.Cloud. There’s also one offered by ClickHouse, Inc. called ClickHouse Cloud. There are other companies that offer managed ClickHouse as well. **Kirsten:** Will the FINAL keyword eventually add a significant performance penalty to SELECT queries? **Kanti:** It does have an impact, but it depends on how your tables are structured. If you partition properly, the performance impact shouldn’t be too significant. It really depends on how your schema is defined. There’s a webinar from Altinity’s CEO Robert Hodges on using ReplacingMergeTree and the FINAL keyword efficiently. I can share a link to that. **Kirsten:** Your examples showed queries on one table. What if you need more complex queries with joins across tables? **Kanti:** When you’re doing DML, the tool will work. DDL across table dependencies is something we haven’t fully tested yet, but DML should work out of the box. **Kirsten:** Does ClickHouse work with nginx? **Kanti:** It would be helpful to have more context on how they plan to use nginx with ClickHouse. If the question is about using it as a load balancer in front of a ClickHouse cluster, I believe we have an article in the [Altinity Knowledge Base on MySQL and ClickHouse integrations](https://kb.altinity.com/altinity-kb-integrations/mysql-clickhouse/) that touches on that. I can send a link. **Kirsten:** Are there restrictions migrating from RDS to ClickHouse? **Kanti:** Some of the restrictions are: transaction support is not currently there in ClickHouse, so that’s something to think about. The other is that indexes are not the same. If you’re trying to add an index in MySQL or a primary key, it can’t be translated directly into ClickHouse because you can’t really change primary keys after the fact, and the index types are different. ClickHouse doesn’t use B-tree indexes; it uses sparse indexing with min/max and Bloom filter type indexes. That requires some manual intervention. Other than that, 95 percent of use cases should work out of the box. **Kirsten:** Are there best practices about data model design in ClickHouse? **Kanti:** Yes. We have a knowledge base with best practices, especially for people coming from relational databases with good suggestions on how to model your application. I can share the link. **Kirsten:** Does the data model in ClickHouse have to follow the MySQL schema one-to-one, or is it possible to transform the data when migrating? **Kanti:** With the replication tool, you would want to keep the column names and schema the same. One suggestion is to use the replicated ClickHouse table as a landing zone or staging area, and then build fact and dimension tables that derive from your staging table through an ETL process that does the transformation. It really depends on your use case, but for the replication application itself the schema does need to match. **Kirsten:** There’s a final comment about wanting an in-depth approach to avoiding performance bottlenecks in ClickHouse. **Kanti:** Yes, we have webinars on that topic. Robert Hodges did one on ReplacingMergeTree and using the FINAL keyword efficiently. There are definitely good practices around how to do it in an efficient way. I can share the link. **Kirsten:** Perfect. With that I’ll end the session. Thank you everybody for joining us today. Thank you Kanti and Vadim for your insights. Hopefully we’ll see everybody at another one of our sessions. Have a great day everyone. **Kanti:** Thank you. **Vadim:** Thank you. ## **FAQ** **Why can’t MySQL handle analytical queries on large datasets efficiently?** MySQL stores data in rows and processes queries using a single thread per user. To answer a query that reads only a few columns, MySQL must load every column of every matching row from disk into memory. It also cannot use multiple CPU cores to speed up a single query. These design decisions are intentional: they optimize MySQL for high-concurrency transaction processing where many users each run short, fast queries. For analytical workloads that scan millions of rows and compute aggregates, these same decisions create severe performance bottlenecks. **What is the Altinity Sink Connector for ClickHouse and how does it work?** The Altinity Sink Connector for ClickHouse is an open-source tool that replicates data from MySQL or PostgreSQL into ClickHouse in real time. It uses Debezium to read the MySQL binary log (binlog), which records every insert, update, delete, and DDL change in the source database. The Sink Connector transforms those events into ClickHouse JDBC statements and writes them in batches to a ReplacingMergeTree table. A newer Lightweight version is a single executable that eliminates the need to run Kafka, making deployment and maintenance considerably simpler. **Why is ReplacingMergeTree recommended as the target table engine when replicating from MySQL?** MySQL supports updates and deletes as first-class operations, but ClickHouse is designed primarily for append-only inserts. ReplacingMergeTree handles this mismatch by deduplicating rows with the same ORDER BY key and keeping only the most recent version. The Sink Connector writes updates as new rows with a higher version number and deletes as rows with a sign of minus one, and ReplacingMergeTree’s merge process or the FINAL keyword at query time collapses these into the correct current state. **Why should the initial data load be done separately from Change Data Capture?** Debezium’s initial snapshot is single-threaded and reads the source database sequentially. For tables with millions of rows this can be very slow. MySQL Shell, by contrast, is significantly faster for bulk exports. Doing the initial load with MySQL Shell, validating the result with a checksum comparison tool, and only then pointing Debezium at the correct binlog offset is faster and more reliable than relying on Debezium alone for the full migration. **What are the main schema differences to be aware of when migrating from MySQL to ClickHouse?** The two most common issues are date range limits and index types. MySQL supports dates up to year 9999 while ClickHouse’s DateTime type only supports up to year 2299, so rows with MySQL dates beyond that range need to be mapped to the ClickHouse maximum value. On indexes, MySQL uses B-tree indexes with strict uniqueness enforcement, while ClickHouse uses a sparse primary key index with no uniqueness constraint, plus optional skip indexes like min/max and Bloom filters. These differences require schema adjustments and cannot be translated automatically in all cases. **Does the Sink Connector support PostgreSQL in addition to MySQL?** Yes. The Lightweight Sink Connector supports both MySQL and PostgreSQL using the same architecture. The configuration is nearly identical between the two, and data types are mapped automatically, including PostgreSQL-specific types like UUID, JSON (mapped to String), and timestamp with timezone. PostgreSQL support was in active development at the time of this webinar. --- © 2023 Altinity, Inc. All rights reserved. Altinity®, Altinity.Cloud®, and Altinity Stable® are registered trademarks of Altinity, Inc. ClickHouse® is a registered trademark of ClickHouse, Inc. Altinity is not affiliated with or associated with ClickHouse, Inc. Kubernetes, MySQL, and PostgreSQL are trademarks and property of their respective owners.