Transparent sub-Library sub-table scheme--turn from: Oschina Youran

Turn from: Oschina Youran

Questions raised

With the expansion of application scale, the single-machine database will not be able to meet the needs of the application, which is mainly manifested in the following aspects:

1. More and more data, more and more slow query speed
2. Access concurrency is getting bigger, disk IO, network IO, CPU are slowly becoming the bottleneck
3. More transactions, more transactional conflicts, and fewer TPS

At this point, some people used a commercial database-based solution such as Oracle, and then scaled horizontally using Oracle's RAC approach. But the shortcomings are also more obvious, the first is that the cost is too high, the average person is too much; secondly, the management complexity is higher than the single node, and the risk and management cost are correspondingly increased; Thirdly, the level of personnel is higher, if not done well, in some cases, or even less than a single node to come fast; , the performance increase amplitude is not proportional to the number of nodes joined, so the economy is not very good.

Therefore, I concluded that a short period of time using large commercial databases such as Oracle to solve a problem is possible, but from a long-term, or a temporary solution to the root.

Therefore, when the database processing capacity is insufficient, it is still to find ways to expand the existing database as well.

Solution Solutions

When the database processing performance is insufficient, there are generally two solutions, one is vertical expansion, the other is horizontal expansion.

• Vertical expansion Scenarios

Advantage: Business code does not require any additional processing

disadvantage: cost increases far outweigh the extended processing power
by increasing the performance of the hardware extension, it ultimately has a very
Limited Network IO processing bottleneck cannot be solved
performance drops faster as data volumes increase

• Horizontal Scaling Scenarios

Advantage: disk IO, network io, CPU, memory spread to different machines

horizontal scale processing capacity is proportional to cost
ability to scale up and down to unlimited processing

Disadvantage: There are certain restrictions on business
Cross-Library Association queries are not supported
a systematic analysis of the sub-list and application needs of the library

A simple summary is that, with vertical expansion, only a short time to solve the problem, because of its increased performance is ultimately a limit, so it is not the ultimate solution, and the level of expansion is almost unlimited scalability, but the design capabilities of designers have requirements, Not supported for some very special SQL statements in the database.

Demand analysis

Tiny framework designers certainly do not do a temporary solution, of course, think is a long-term solution, so there is no doubt to do the horizontal expansion plan (of course, the vertical expansion of the program and software has little to do with, we can not do), do before the first expansion of the following requirements:

• using Java technology to achieve
• to support common databases, it is primarily possible to support all databases with JDBC driver
• supports self-growing primary keys so that database applications that originally relied on self-growing primary keys do not need to be specifically addressed in this regard
• database paging statements are supported so that database applications that rely on database paging statements do not require special processing in this regard
• capable of supporting most SQL statements
• It is best to approach the JDBC driver in terms of performance
• with good extensibility, database designers can easily customize the extension
• support for read and write separation, load Balancing implementation algorithm can be customized
• support for sub-Libraries
• support for sub-tables
• good support for the business
• good support for statistics and sequencing

System design and realization scheme and comparison

There are a number of ways to implement database-differentiated table schemes:

• DAO Layer: Low implementation difficulty, high coupling of business code, high cost of business development, high reconstruction cost, poor reusability
• DataSource layer: In the difficulty of implementation, business code coupling degree is low, business development cost is low, reconstruction cost is low, reusability
• JDBC Layer: High difficulty, low coupling of business code, low cost of business development, reconstruction cost, good reusability
• Agent Layer: the implementation is difficult, the business code coupling degree is low, the business development cost is low , the reconstruction cost, the reusability is good

Tiny framework adopted in the JDBC Layer implementation of the scheme, this scheme than the agent layer, the network communication can be less than the implementation, so the code is small, high stability, while the performance of the network communications less than one time, so the performance is higher.

Tiny sub-database sub-table design scheme

explain briefly:

when Tinydbrouter receives a SQL, it first parses the SQL and then routes it to the appropriate real database according to the Library and table rules, then processes the results of the execution, and then provides the result to the database requester, which completes the interaction.

Of course, the actual processing process is far more complex than this, because to consider the consistency of the transaction, processing efficiency, the correctness of the results, this is more of the Tao, said it is relatively long, here is simply skipped.

How to use the application instance

Class.forName ("Org.tinygroup.dbRouterjdbc3.jdbc.TinyDriver"= drivermanager.getconnection ("JDBC: Dbrouter://router1 "," username "," password "= conn.createstatement (); String SQL;  for (int i = 0; i < i++) {    = "INSERT INTO AAA (AAA) VALUES (' PPP ')";    Stmt.execute (SQL);}

Well, that's the way it's used, and the students who are familiar with JDBC will soon find out that this is no different from the normal JDBC program. Indeed, using tinydbrouter, the actual development process is no different from the original, whether you use JDBC, the spring jdbc Template, or Ibatis, Hibernate, regardless of any Java ORM Framework, All can be used.

The only thing to note is to change the original URL and driver to tiny.

What other database management tools can you use? Of course, as long as the Java-based database management tool, as long as the tiny driver related jar package into its classpath path, it can be used.

In fact, for the statistical support, for all the sub-database sub-table framework is very challenging, such as a lot of sub-database sub-table framework requires each SQL can only fall on one shard to execute, to ensure the correctness of the results; For example, many of the sub-database sub-table framework requires statistics can not be ordered, etc. Even some directly do not support, welcome students to let them.

Tinydbrouter The only limitations are:

Cross-Library Association queries are not supported

of course, almost all do not support this feature, a small number of claims to support, in fact, no availability---because the performance is too slow, the amount of data if the larger is dead.

Questions and Answers

1. does the self-growing primary key in SQL Server, like MySQL, require special handling? Answer: No need, the original program is still used just fine
2. can paging SQL like M ysql and SQL Server be used as is? Answer: You must be able to
3. How does the SQL statement supported by the Tinydbrouter support? Answer: Most SQL statements that support the SQL92 specification (very little is not supported)
4. Do you support having a statement? Answer: Support
5. is the code refactoring cost of the original project high? answer: This is related to the data partitioning scheme developed by the DBA, as long as it does not violate the above constraints can not be modified.
6. I divide the main table from the table into one shard, and the association occurs only in the same shard, in which case does the code need to be modified? Answer: no need.

Summarize

Tiny all parts or sub-items of the frame, we never attract the eye of the "most" word, we believe that as long as our solid efforts coupled with the elegant design of ethereal, will be a very good solution in the relevant problem areas.

Transparent Library sub-table scheme--turn from: Oschina Youran