MySQL library and table dynamic expansion and reduction
1. Problem analysis
How to design a library and table division solution that can dynamically expand and shrink capacity?
For the database and table division, we mainly face the following problems:
- Select a database middleware, research, learn, and test;
- Design a solution for your library and table division. How many libraries do you want to divide into, and how many tables each library is divided into, such as 3 libraries and 4 tables per library;
- Based on selecting a good database middleware and establishing a good database partition and table partition environment in the test environment, then test whether the database partition and table partition can be read and written normally;
- Complete single-store single-store table to separate-store tablemigrate, double-write solution;
- The online system has begun to provide services to the outside world based on the database and tables;
- The capacity has been expanded into 6 libraries, and each library requires 12 tables. How can you add more libraries and tables?
This is something you have to face, that is, you have already prepared the library and table plan, and then a bunch of libraries and tables have been built. The code development based on the library and table middleware is all good, the test is OK, and the data can be evenly distributed to each library and table. Then you also clicked on the system through the double-write solution, and you have already done it directly based on the library and table plan.
So now the problem is, you can't support these libraries and tables anymore. What should you do if you want to continue to expand? This may mean that the capacity of each library is almost full again, or the amount of data in your table is too large, or it may be that the writing concurrency of each library is too high, so you have to continue to expand.
These are all things that must be experienced online when playing the library and table.
2. Answers to interview questions
Shut down and expand capacity (not recommended)
This solution is like downtime migration, with almost the same steps. The only point is that the derivative tool is to extract the data from the existing library table and slowly pour it into the new library and table. But it's better not to play like this, it's a bit unreliable, because sinceLibrary and tableThis means that the amount of data is too large, which may be as many as hundreds of millions or even billions. If you play like this, there may be problems.
When migrating from a single library and a single table to a separate library and a separate table, the amount of data is not very large, and the maximum single table is only 20 to 30 million. Then you write a tool and get a few more machines running in parallel, and the data will be transferred in one hour. There is no problem with this.
If 3 libraries + 12 tables are running for a while, the data volume is 100 million to 200 million. Just transmitting 200 million data requires a few hours to transmit, at 6 o'clock. After just transmitting the data, you also need to modify the configuration, restart the system, test and verify it. You can complete it at 10 o'clock. So you can't do this.
Optimized solution
At the beginning, there are 32 libraries, each library has 32 tables, so there are a total of 1024 tables.
I can tell all students that this classification method is, first, basically, the domestic Internet is definitely enough, and second, there is no problem with whether it is concurrent support or data volume support.
The amount of write concurrency normally carried by each library is 1000, then 32 libraries can carry 32 * 1000 = 32000 write concurrency. If each library carries 1500 write concurrency, 32 * 1500 = 48000 write concurrency, close to 50,000 write concurrency per second, add another MQ before it, cut peaks, write 80,000 MQ data per second, and consume 50,000 data per second.
Unless they are among the top companies in China, their core system databases may have hundreds of databases of the same scale, 128 libraries, 256 libraries, and 512 libraries.
1024 tables. Assuming that each table contains 5 million data, 5 billion data can be placed in MySQL.
50,000 per second write concurrently, with a total of 5 billion data, which is actually enough for most domestic Internet companies.
Talk about the expansion of database and tables,The first time I divided the database and the table, I gave him enough, 32 libraries and 1024 tables, may have been able to support most small and medium-sized Internet companies for several years.
One practice is to use32 * 32To divide the database and table, that is, it is divided into 32 libraries, and one table in each library is divided into 32 tables. There are 1024 tables in total. According to a certain id, first route to the library according to 32, and then route to the library table according to 32.
| orderId | id % 32 (Library) | id / 32 % 32 (table) |
|---|---|---|
| 259 | 3 | 8 |
| 1189 | 5 | 5 |
| 352 | 0 | 11 |
| 4593 | 17 | 15 |
At the beginning, this library might be a logical library. The one built on a database is that a mysql server may have n libraries, such as 32 libraries. If you want to split it later, you can just keep migrating between the library and the mysql server. Then the system cooperates to change the configuration.
For example, it can be extended to up to 32 database servers, each database server is a library. What if it is still not enough? It can be extended to up to 1024 database servers, with each database server having a library and a table. Because it is at most 1024 tables.
In this way, you don’t have to write your own code to do data migration. All of them are left to dba. However, dba does need to do some library table migration work, but it is much more efficient than writing your own code and then extracting data and digging data.
Even if you want to reduce the number of libraries, it is very simple. In fact, to put it bluntly, just reduce the volume by multiples, and then modify the routing rules.
Here is a summary of the steps
- Set up several database servers, several libraries on each server, and how many tables each library is. The recommended number is 32 library * 32 tables. For most companies, it may be enough for several years.
- Rules for routing, orderId modulo 32 = library, orderId / 32 modulo 32 = table
- When expanding capacity, apply to add more database servers, install mysql, expand the capacity in multiples, 4 servers, expand to 8 servers, and then to 16 servers.
- DBA is responsible for migrating the original database server's library to the new database server. There are some convenient tools for library migration.
- Let’s modify the configuration and adjust the address of the database server where the migrated library is located.
- Re-release the system and go online, and the original routing rules do not need to be changed. You can directly use n times the resources of the database server to continue providing services to the online system.
The above is personal experience. I hope you can give you a reference and I hope you can support me more.