High Data Compression Ratio
Compared to the traditional MySQL InnoDB engine, TDSQL Boundless provides a compression ratio of up to 3.81. This article offers evaluation of disk capacity for users planning database migration or model selection.
Interpretation of Core Advantages: Why Can TDSQL Boundless Achieve Such a High Compression Ratio?
TDSQL Boundless adopts the LSM structure to store data. Due to the append-only characteristic of LSM, it avoids the data page fragmentation caused by frequent random writes from in-place updates in B+ trees. Additionally, the background compaction feature of LSM avoids the overhead of compressing data on every write, greatly reducing the performance impact of compression. Therefore, compared to MySQL InnoDB, under similar performance conditions, TDSQL Boundless can achieve a data compression ratio of up to 3.81.
After we interpret the kernel, we now examine the empirical data to quantify the space usage comparison between TDSQL Boundless and MySQL when the databases store identical datasets. This validates the effectiveness of the high compression ratio under OLTP data models, providing scientific disk capacity planning guidance for users migrating from MySQL.
Test Overview
Test Environment
Option | Description |
Cloud platform. | Tencent Cloud |
Instance Specifications | 16-Core CPU/32 GB of Memory/Enhanced SSD 300 GB |
Comparison Database
Control group: MySQL 8.0 (using the InnoDB storage engine, default configuration)
Experimental group: TDSQL Boundless (with built-in high-efficiency engine for data compression)
Key Configuration
MySQL 8.0 default configuration of the InnoDB engine:
# MySQL InnoDB Configuration of Default Parametersinnodb_file_per_table=ONinnodb_page_size=16K# Not enabled innodb_page_compression (representing the default configuration in most production environments)innodb_page_compression=OFF
Test Data Set
Sysbench benchmark
benchmark test for TPC-C
Testing Plan
Preparing Test Data
benchmark test for Sysbench: Based on Sysbench Test, initialize 32 tables, each table with 10 million records.
benchmark test for TPC-C: Based on TPC-C Test , initialize 1000 Warehouses.
Test Results and Data Analysis
Comparison of Storage Space Usage (Unit: GB)
Dataset | MySQL 8.0 (InnoDB) | TDSQL Boundless |
Sysbench | 72.6GB | 36.77GB |
TPC-C (1000 Warehouses) | 77.8GB | 43.5GB |
Comparison of Data Compression Ratio (MySQL as Baseline 100%)
Dataset | MySQL 8.0 (InnoDB) | TDSQL Boundless |
Sysbench | 100% | ~50.64% |
TPC-C (1000 Warehouses) | 100% | ~55.91% |
Migration Guide: Recommendations for Disk Capacity Planning
If you are migrating from MySQL to TDSQL Boundless and wish to evaluate the disk requirements of the target database, please follow these simple guidelines:
Step 1: Evaluate Disk Usage of MySQL
Log in to your MySQL instance and run the following command to view the total data size:
SELECT table_schema AS 'Database',ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS 'Size(GB)'FROM information_schema.TABLESGROUP BY table_schema;
Step 2: Select Compression Ratio Based on Business Scenario
Source-Side MySQL Types of Service Data | Suggested Planning Coefficient |
General OLTP services (orders, users, and so on) | Plan based on 50% of MySQL space. |
Logs, monitoring, time-series data | Plan for 30% of MySQL space. |
Data warehouse, reporting and analytics (AP) | Plan for 30% of MySQL space. |
Step 3: Calculate Target Capacity of the Disk
Required disk space is approximately equal to the source MySQL capacity multiplied by the planning coefficient.
フィードバック