I. The evolution of the database architectureThe schema of the database evolves with the expansion of the Web site, and as the number of visitors to the site increases, the access pressure on the database increases, resulting in different database architectures. 1. Web applications and databases are deployed on the same serverat first, the site's user volume, traffic, and concurrency are small, and Web applications and databases are deployed on the same server. 2. Separation of data Services and application servicesas the amount of data increases, one server cannot meet the requirements, separating the data service from the application service, the Web application server and the database server, so that the application server and the database server can be upgraded separately when the traffic increases. 3. DB Clusterwhen the processing capacity of a single database server reaches the limit, it is possible to take a cluster approach to deploy the database server, that is, each node of the library to introduce more than one machine, each machine to save the same data, more than one machine load, can effectively reduce the database pressure. 4, database reading and writing separation-- to solve the problem of large reading pressure a part of the database read (not cache, cache expiration) and all the write operations need to go through the database, when the user reaches a certain amount, the performance of the database will reach the bottleneck, because a large number of data operations focused on the operation of the read, when the ratio of read/write is about 10:1, you can properly increase the server, Take the master-slave copy of the database in a way that separates read and write . in a cluster, a group consists of 1 master and N slave. Where Master is responsible for the load of the write operation, that is, all updates to the database are for the primary database, and the read operation can be done from the database, which can greatly improve the efficiency of reading. Database Master-slave replicationis to ensure data consistency by pulling the binary log file from the server to the master server and then parsing the log file into the appropriate SQL to re-perform the master server operation from the server. Therefore, the primary server must turn on the binary log, automatically record all updates to the primary database, from the server again timed to the master server to get the binary log files to replay, complete the data replication. The master-slave replication data is completed asynchronously, which leads to a certain delay in the data in the master-slave database, which must be considered in the design of read/write separation. In the case of blogs, the user logs in and publishes an article, and he needs to see his article right away, but for other users it can be delayed for a period of time (1 minutes/5 minutes/30 minutes). At this point, the current user needs to read the primary database, and for other external users with greater access, they can read from the database.
5, Database vertical Segmentation-- to solve some data write problems If the database server is a master-slave deployment, when the write operation accounted for the main database CPU consumption of more than 50%, the write operation from the server will also account for more than 50% of CPU consumption, a server provides a very limited query resources, you should consider the use of database vertical partitioning technology, The different data are placed in different databases and servers according to the function. For example, the user's personal data and the user's blog data, the correlation between them is not strong, can be deployed separately on two separate database servers.6, Database horizontal segmentation--ShardIf the database is partitioned verticallystill unable to deal with a large number of write operations, should take the database horizontal partitioning technology. The data of a table is divided into different databases according to certain rules (the same as the table structure of two databases), and then deployed on different database servers. Take the above blog as an example, the data can be based on user_id parity to determine the division of data, the ID of the odd number of data into a library, the ID of the even-numbered data in the B library, so you can know the user's blog data in which database user_id. second, the database high-availability architecture1, based on keepalived master-slave replication schemeIn order to achieve higher availability, in the actual application environment, the use of MySQL replication technology with high-availability cluster software keepalived to achieve automatic failover, this way can achieve 95% SLA. (Note: failover: means that when the server is down, or an error occurs, you can automatically switch to other standby servers, do not affect the operation of the app on the server.) )keepalived is an HA software, its role is to detect the server (Web server, DB server, etc.) status, inspection principle is simulated network request detection, detection methods including http_get| Ssl_get| tcp_check| smtp_check| Misc_check and so on. for a DB server, the primary is IP, port (tcp_check), but this may not be enough (such as DB Server readonly), so keepalived also supports custom scripting. keepalived to confirm the status of the server by listening, and if a server failure is found, the failed server is removed from the system. when Master fails, keepalived perceives and slave the master, continuing to provide services to the application layer transparently. 2, based on MHA high-availability solutionMHA (Master high availability) is a set of MySQL failover scenarios that ensure that the database is highly available. You can minimize data loss by saving the binary log from the primary server on the outage for back-up. MHA consists of two parts: MHA Manager (Management node) and MHA node (data node). MHA can be deployed separately on a separate machine to manage multiple master-slave clusters, MHA node runs on each MySQL server, and the primary role is to process the binary log when switching, ensuring that the switch minimizes data loss. MHA Manager periodically probes the master node in the cluster, and when master fails, it automatically promotes the slave of the latest data to the new master, and then points all other slave to the new master. The entire failover process is completely transparent to the application. 3. heartbeat/San High-availability solution the way to handle failover is the highly available cluster software heartbeat, which monitors the network that manages connections between nodes and monitors cluster services. When a node fails or is not available, the Cluster service is automatically started on the other node. A san (Storage area Network) is a LAN that handles the transfer of large amounts of data, providing data transfer between the computer and the storage system, and clusters of computers that can share data across the SAN. This scenario can achieve a 99.99% SLA. 4, based on the DRBD disk replication scheme DRBD (Distributed replicated Block Device) is a disk-replication technology that provides similar effects to Sans. It copies each block of the master server to another server block device via a network card and logs it before the master commits the block. Unlike San Networks, it does not share storage, but rather replicates data through the network between servers. 5, based on NDB cluster multi-point writing scheme MySQL cluster mainly consists of three parts: SQL Server node, NDB data storage node, monitoring and Management node, all of these nodes constitute a complete MySQL cluster system. The data is stored in the storage engine of the NDB storage server, and the table (structure) is saved in the MySQL server. The application accesses these data tables through the MySQL server, and the cluster Management Server manages the NDB storage server through the Administrative Tools (NDB_MGMD). third, database optimization1. Optimizing SQL statementsTurn on slow queries, discover problematic SQL statements through the slow query log, analyze SQL execution plans through explain queries, and optimize for limit, group by, and more.2. Index optimizationSelect the appropriate column to index, generally in the WHERE clause, on the clause, etc. maintain the index, remove the duplicate index, such as primary key do not add a unique index.3, the database optimizationChoose appropriate data types, paradigm optimization and inverse paradigm optimization (with space-time), reasonable vertical and horizontal segmentation of database tables.4. System Configuration and hardware optimizationCPU, Cache, SSD hard drive and so on.[References]Large-scale high-concurrency high-load Web Application System Architecture-Database schema strategy database access performance optimization for MySQL database common architecture design
2017.6.2/Database Architecture and optimization
