Weibo: How did you optimize the database?

BKJIA comprehensive report: bib and bib-what is this? When winter is approaching, will the weave and neck be warm? This is a popular Internet term. This is the way of life and attitude of everyone. "Look for me? Come to my Weibo !" Recently, friends around you are weaving and weaving. Are you not weaving? You are Altman ". Do you know the development mode of Weibo? How is the database deployed? How is it optimized? Once these problems arise, it is necessary to find someone to solve them for us. BKJIA has the honor to invite Mr. Yang haichao, Chief DBA of Sina, to answer the above questions for us.

Interview characters

Yang haichao, Chief DBA of Sina, has rich management experience in large-scale and high concurrency and massive access. He is keen on research on database design, performance optimization, distributed deployment solutions, and high availability.

Previously, I was engaged in the deployment and optimization of high-traffic websites. After I joined Sina, I was mainly responsible for the database management of the entire company.

 

BKJIA: is Sina's current development model still available?LAMP?

Yang haichao: at present, most businesses still use LAMP, and some adopt LNMP.

BKJIA: What is Sina database?Deployment?

Yang haichao: NoSQL and MySQL are currently used in combination. select an appropriate storage method based on application characteristics.
 
BKJIA:Sharding PolicyIt is a good database extension solution, but this solution is not omnipotent. How does Sina select sharding to adapt to different application scenarios?

Yang haichao:

 

Sharding is only used for databases with performance bottlenecks when the data volume is too large. Most databases do not perform sharding.

For databases with large data volumes, the sharding policy is taken into account at the beginning. For example, the index data and content data are designed separately. Each type of database selects an appropriate partitioning key based on the business logic, split into a certain number of tables.

Then, vertical split is performed as the pressure increases. When the database after Vertical Split encounters a performance bottleneck, hardware is used to solve the problem.

Horizontal splitting is considered only when the hardware cannot be solved.

Consider the business logic carefully when selecting a sharding solution. For read-intensive applications, you can add slave to perform vertical and horizontal splitting for write-intensive applications.
 
BKJIA: the more sharding is crossed, the more overhead it brings. How can this quantity be controlled?

Yang haichao: Before the design, I have to avoid cross-Table operations and select the appropriate paritioning key, that is, the appropriate split dimension, to avoid the impact on the business in the future.

Based on the importance of the business logic, if the business logic is to query the information of a user, it will be split by the user, so that the data of a user falls into a table. Split by time dimension, it will analyze the degree of cold and hot data, put more than 80% of the data in a table, avoid excessive cross-table queries.

When this split dimension does not meet business requirements, we will use the idea of changing the space for time to split the same data in multiple dimensions, make query statements of each business logic highly efficient.

BKJIA: many users confuse sharding with partitioning. You can tell us how to distinguish sharding from partitioning.

Yang haichao: sharding generally refers to Vertical Split and horizontal split. It is a general concept. mysql partitioning is a technology that implements sharding.
 
BKJIA: Sina now uses SQL + NoSQL database deployment. How are the two databases optimized?

Yang haichao: NoSQL and MySQL are currently used in combination. select an appropriate storage method based on application characteristics. For example, relational data, for example, indexes are stored in MySQL and non-relational databases. For example, if K/V requirements are met, NoSQL products store high concurrency requirements, you can also copy the relational data to NoSQL (redis) to display different application requirements.

There are many optimizations for MySQL, such as using SSD, Fusion-IO, and Cachecade in hardware, File System (try XFS), IO scheduling, and parameter optimization, adjust indexes to reduce application access to and exchange of databases.

NoSQL (redis) satisfies its business needs by modifying the source code: improves its replication mechanism and adds the position concept to make maintenance easier, while the failover capability is also greatly enhanced. Improves the storage of Hashset in rdb and the loading speed of complex data types.

 

BKJIA: how to ensure database security?

Yang haichao: mainly through several considerations: