Overcoming Database Bottlenecks: Sub-Libraries and Vertical Sub-Tables
As the number of active connections to a database increases, it can eventually approach its threshold, leading to a bottleneck. This can be due to either an IO bottleneck or a CPU bottleneck. In this article, we will explore the causes and effects of these bottlenecks and discuss how sub-libraries and vertical sub-tables can help alleviate them.
IO Bottlenecks
IO bottlenecks occur when the database is unable to read data from disk efficiently, resulting in a slow query speed. There are two types of IO bottlenecks:
- Disk Read IO Bottleneck: When data is too hot, the database cache cannot fit, and each query generates a lot of IO requests. This can be mitigated by using sub-libraries and vertical sub-tables.
- Network IO Bottleneck: When there is a high volume of requested data, the network bandwidth is not sufficient. This can be alleviated by using sub-libraries.
CPU Bottlenecks
CPU bottlenecks occur when the database is unable to process queries efficiently, resulting in a slow query speed. There are two types of CPU bottlenecks:
- SQL Problem: When SQL joins, group by, order by, or non-indexed fields are used, the CPU operations increase. This can be mitigated by optimizing SQL queries and establishing appropriate indexes.
- Single Table Data: When a single table contains too much data, the SQL is inefficient, and the CPU operations increase. This can be alleviated by using level points tables.
Sub-Libraries
A sub-library is a smaller library within a larger library, based on a certain policy (hash, range, etc.). Each sub-library has the same structure, but the data is not the same, and there is no intersection. The union of all sub-libraries is the whole amount of data.
Horizontal Sub-Tables
A horizontal sub-table is a smaller table within a larger table, based on a certain policy (hash, range, etc.). Each sub-table has the same structure, but the data is not the same, and there is no intersection. The union of all sub-tables is the total amount of data.
Vertical Sub-Libraries
A vertical sub-library is a smaller library within a larger library, based on a certain policy (vesting of different). Each sub-library has a different structure, and the data is not the same, and there is no intersection. The union of all sub-libraries is the whole amount of data.
Vertical Sub-Tables
A vertical sub-table is a smaller table within a larger table, based on a certain policy (activity fields). Each sub-table has a different structure, and the data is not the same, and there is no intersection. The union of all sub-tables is the total amount of data.
Sub-Library Sub-Table Tool
There are several tools available for sub-library sub-table, including:
- Sharding-Sphere: A jar that provides a sharding framework.
- TDDL: A jar that provides a distributed data layer.
- Mycat: A middleware that provides a sub-library sub-table solution.
Sub-Library Sub-Table Step
The sub-library sub-table step involves evaluating the capacity of the database table or the number of points, selecting a group key, and performing a split strategy (hash range or the like). The expansion issues are then minimized by moving data.
Sub-Library Sub-Table Issues
There are several issues to consider when implementing sub-library sub-table, including:
- Inquiries of Non-Partition Key: When the partition key is not used in the query, the sub-library sub-table may not be effective.
- Non-Cross-Database Partition Key Issue: When the partition key is used across multiple databases, the sub-library sub-table may not be effective.
- Expanding Problem: When the sub-library sub-table is expanded, the data may need to be moved, which can be time-consuming and resource-intensive.
Conclusion
In conclusion, sub-libraries and vertical sub-tables can help alleviate IO and CPU bottlenecks in databases. By understanding the causes and effects of these bottlenecks and implementing sub-libraries and vertical sub-tables, developers can improve the performance and efficiency of their databases.