Building a High-performance Web site learning notes two------database extensions

Building a high-performance Web site requires a lot of consideration, and here's a look at one of these---------database extensions, hoping to get a sense of database extensions to friends who don't have access to that knowledge.

With the increasing number of users, the database will face a huge change and check, even if we optimize the SQL statements, but the database server still can not withstand the army-like select, we have to consider other ways to solve this problem. As with the Web site server, since a server can not afford, people more power, we first thought of increasing the server to share the pressure.

A new solution is sure to bring new problems-

how to expand.

In general, the operations in the database read (SELECT) operation is far more than the write operation, then I can speak of read and write separation, the principle is to use a master server specifically for writing, multiple from the server to synchronize the primary server data.

multiple database servers, how our program knows which server to access.

We are controlled by code. What to do if a server goes down. These questions are all we need to consider, so we thought of the reverse proxy, he can to the Web reverse proxy, for us to follow the rules we have established access to the appropriate server, if detected a server down, it will automatically remove the server from the list.

Now we use a server as a write server, according to the diagram, as the site grows, write operations will become more frequent, if the operation accounted for 80% of the operation, then from the server must spend 80% to synchronize the primary server data, only 20% of the time to deal with the user's read operations, And if the write operation is larger, the writing server has been unable to withstand the pressure of writing, the problem is very serious.

What should we do?

Vertical Partitioning: Separate data categories that are infrequently interacting with each other in different server databases

The easiest way to do this is to distribute different databases to different servers, and you'll find that there are a lot of databases that don't have relationships or do not need join queries, so why not put them on different servers.

We separate the blog database from the buddy database, and separate from the primary server, so that the two database operations can be unloaded from the main server, according to this idea, with the development of the Internet, more and more people write blog, A blog server may not be able to handle the work now. So we use the same method to extend, read and write separation

As time goes on, new problems come again, blog's write server can no longer bear the pressure to write (the database of the various tables write operation data is too large), so we thought of the database operation of the table to separate the database server, but if the data table also reached the operating limit of the write. Now innodb a table can hold 64TB of data, which is just storage capacity

Horizontal Partitioning: placing the same data table in a separate database in a certain rule

For example, to join us with 10 servers, we can store the user data in different databases according to USERID%10, so that we can store the user in these 10 database partitions separately

So how do we know which database the data is stored in? It's not a good way to look at the tradition.

The traditional way:

"Select *from tablename where blogid=" $post _id;

Because we can only use UserID to determine which database the user's data is in, so we need to pass the UserID past

<?php

$db = new DataAccess ($user _id);

$db->selectdb ("Db_blog");

$tbl _name = gettblname ($user _id);

$sql = "SELECT * from". $tbl _name. "Where post_id=". $post _id;

$result = $db->query ($sql);

?>

What data data should be partitioned. user information, friend relationship These are difficult to answer, must be based on specific circumstances, many times that the data partitioning is not our choice, for those frequent access to the site close to the crash of the data we have to partition it

will the zoning bring us inconvenience?

The answer is yes, for example, you might have a joint query can work out the task, after the partition we must be based on the user ID to determine the partition, and then through the friend ID to find the partition of the friend data ... Not very flexible.

Common partitioning algorithms

Hash algorithm user_id%10 This is

The range 1-10000 is stored in the partition 1 10001-20000 stored in the partition 2

Mapping relationship is not too understanding, later understand to add on

OK, today only talk about some MySQL extensions commonly used several methods and use of the introduction, and later my knowledge increased, will be realized and share with you.