{"id":945,"date":"2021-02-18T00:00:00","date_gmt":"2021-02-18T00:00:00","guid":{"rendered":"https:\/\/en.pingcap.com\/case-study\/reduce-real-time-query-latency-from-0-5s-to-0-01s-with-scale-out-htap-database\/"},"modified":"2024-07-16T21:04:18","modified_gmt":"2024-07-17T04:04:18","slug":"reduce-real-time-query-latency-from-0-5s-to-0-01s-with-scale-out-htap-database","status":"publish","type":"case-study","link":"https:\/\/www.pingcap.com\/ko\/case-study\/reduce-real-time-query-latency-from-0-5s-to-0-01s-with-scale-out-htap-database\/","title":{"rendered":"Reducing Real-Time Query Latency from 0.5 s to 0.01 s with a Scale-Out HTAP Database"},"content":{"rendered":"

Autohome is the leading online destination for automobile consumers in China. It’s one of the most visited auto websites in the world. We provide professionally produced and user-generated content, a comprehensive automobile library, and extensive automobile listing information, covering the entire car purchase and ownership cycle.<\/p>\n\n\n\n

The Autohome community forum is one of our oldest applications, with 100 million+ daily visits and 1 billion+ daily interface calls. As our data size rapidly grew, SQL Server became our database bottleneck. Sharding didn’t meet our app requirements<\/strong>, \uadf8\ub9ac\uace0 scaling database capacity affected apps.<\/strong> We looked for a new database solution.<\/p>\n\n\n\n

After we compared \ud2f0DB<\/a>, Apache Ignite, and CockroachDB, we chose TiDB, an open-source, distributed, Hybrid Transactional\/Analytical Processing (HTAP) database. Thanks to its horizontal scalability, now we can easily scale out our database, and our transactional 99th percentile latency is within 12 ms.<\/strong><\/p>\n\n\n\n

In 2020, TiDB also served as the underlying storage for our big sales promotion with hundreds of thousands of Queries Per Second (QPS)<\/strong>. Our real-time query latency was reduced from 0.5 s to 0.01 s. TiDB’s 99th percentile latency was within 20 ms<\/strong>.<\/p>\n\n\n\n

<\/span>Our pain points for the community forum reply app<\/span><\/h2>\n\n\n\n

Over the past 14 years, the Autohome community forum has grown to 100 million+ posts and 1 billion+ replies. It has 10 million+ daily active users, 100 million+ visits per day, and 1 billion+ average daily interface calls. We stored the forum’s data in SQL Server. As our businesses quickly grew, we encountered more and more database problems. We needed to resolve two urgent issues: sharding and scaling.<\/p>\n\n\n\n

Database sharding didn’t meet our app requirements<\/h3>\n\n\n\n

The Autohome forum reply database stores replies to posts. When SQL Server’s single table was too large, database performance degraded. To solve this issue, we sharded the reply database based on the post ID. The result was 100+ shards and 1,000+ tables in the reply database.<\/p>\n\n\n\n

However, our applications developed and application requirements changed, and when we wanted to implement some application requirements, sharding couldn’t meet our needs<\/strong>. We hoped that data was logically in a single table.<\/p>\n\n\n\n

Scaling database capacity affected applications<\/h3>\n\n\n\n

In recent years, as our businesses rapidly grew, our data size increased remarkably. But hard disks had limited capacity, and on each server, we could only add limited disks. As a result, every once in a while we had to add storage servers of larger capacity. However, this approach was complicated, and every time we changed servers, we still needed to pay attention to related applications<\/strong>. We hoped that scaling database storage would not affect applications. In addition, large-capacity database servers were expensive.<\/p>\n\n\n\n

<\/span>Our exploration of distributed SQL databases<\/span><\/h2>\n\n\n\n

Why we chose TiDB over Apache Ignite and CockroachDB<\/h3>\n\n\n\n

In 2019, we investigated three popular distributed SQL databases: TiDB, Apache Ignite, and CockroachDB. After many tests, we chose TiDB, because:<\/p>\n\n\n\n