Hair welfare! Hair welfare! Hair welfare! Hangzhou Cloud Computing Conference

Enterprise Database Best Practices (First half)

Enterprise Database Best Practices (Second half)

Ali database kernel

This article is in enterprise database best Practices (first half).

Enterprise is no longer strange, but now the industry is mainly focused on the development and operations of efficient collaboration and quick release, and as the core assets of database, the structure design, SQL audit, change release have become the main bottleneck of enterprise efficiency, this article will detail ali in enterprise database on the challenges and solutions.

 

1. What is Alibaba database DevOps

A word definition: Alibaba database DevOps is the database product as the core, to promote database research and development self-service, improve the efficiency of research and development, and ensure the database access security and database performance of employees.

Ali database DevOps consists of three products, namely Ali enterprise-level data management service iDB, Database intelligent optimization service CloudDBA and Ali enterprise-level database management and control service DBPaaS. This paper will focus on Ali enterprise-level data management service iDB.

 

2. Our challenge

In the past 10 years, The number of research and development personnel of Ali has expanded from several hundred to tens of thousands, and meanwhile, it has constantly posed challenges to database services. Review the development process of Ali database service, including the following stages:

· Before 2010, database accounts were directly opened to developers for free query of data

· In 2010, in order to solve the security problem of data query, database accounts were fully recovered and only DBA could query data

· In 2011, IN order to solve the problem of low efficiency in data query, iDB1.0 was released, and self-service data query was developed on the WEB platform. However, database changes still needed to be submitted to DBA by email at this time

· In 2012, IN order to solve the communication problem of database changes, iDB2.0 was released. Database changes can be submitted and summarized online, and the structural design specifications can also be automatically applied to the development of database design, but at this time, database changes can only be implemented by DBA

· In 2013, in order to solve the problem of low efficiency of database change, iDB3.0 was released, and developers could execute database change by themselves

· In 2014, ali Cloud Data Management DMS was released to meet the demands of Ali cloud users for the security and efficiency of database management, positioning it as a personal data management cloud service

· In 2015, iDB4.0 was released in order to solve the bottleneck of DBA support for Alibaba’s business, achieving self-service database research and development for the first time

· In 2016, CloudDBA, a database intelligent optimization service, was released to solve the performance problems of business database, targeting database experts around users

· In 2017, in order to share years of practice/experience/precipitation with more people, Ali Cloud Data Management DMS Enterprise edition (iDB corresponding to products on the cloud) was released, providing complete self-service process of database research and development, enterprise-level database security control and other services

Today, we (to be precise, our products) need to solve ali 20,000 developers efficient structure design, SQL review and change release, to solve tens of thousands of SQL released every week without slow SQL leakage, to solve a million times a week data query analysis permission control and operation audit, so in efficiency, performance and security, Alibaba’s database DevOps is facing a huge challenge.

 

3. Solutions

When you feel pain, it is time for you to grow, alibaba database DevOps came into being.

  • The efficiency of

The final measurement of database efficiency is reflected in the release efficiency, and before the release there are structural design, SQL design, test data construction, database table synchronization, structure audit, SQL audit and other work, database release itself will be subdivided into structural change, data revision and sub-database sub-table release.

Ali database DevOps solutions in efficiency: Self-help database development process, all of them, and cover the database to release all the work involved, such as 30 + is applied to the development of structural design specifications, many people in the design of structure design conflict detection and DDL roots, many environmental library table synchronization, millions of self-help to generate test data, etc., finally let the DBA does not need to participate in the specific design and evaluation of the project, Focus on the understanding of business architecture and a more holistic view of the relationship between business and database to optimize enterprise efficiency.

There are too many functions involved in the efficiency solution. Here we only take the release of sub-database and sub-table as an example, hoping that we can have some understanding of the self-service database research and development process:

 


If MySQL is selected as the data store, most enterprises will encounter the single-table capacity bottleneck with the growth of services, and the DDL performance of large tables is the most serious problem. The common solution in the industry is to separate the single table from the component list and divide the database into different tables, which makes the capacity no longer the bottleneck, but brings great challenges to the maintenance and management.

In the face of 1024 and 4096 sub-tables, how to add fields is the primary problem to solve. We often encounter a series of problems such as performance lag caused by fast execution, uncontrollable risk of one-time full execution, abnormal task interruption and so on, which ultimately makes the actual effect of sub-table scheme greatly reduced.

Ali database enterprise on the basis of physical depots table encapsulates the logic, logic table, all DDL operations on logic library, logical table, and performing specific actions on physical depots table by products to ensure that, at present can do database lock detection, automatic instance/repository level parallel, grayscale, speed limits, mission to suspend retry fine-grained control, etc.

  • performance

Database performance, aside from database type/version selection, kernel optimization, hardware selection and other aspects, we can see that the most obvious business perception, the most attention is SQL (to be precise, slow SQL), how to solve slow SQL from the root, rather than every time the problem of fire, is the direction of Ali database DevOps has been working hard.



When a SQL is designed, it needs to pass the audit before it becomes slow SQL. At this time, after the development submits the SQL audit, the SQL in SVN/GIT code will be extracted automatically and the changed part will be identified. At this time, Alibaba database intelligent optimization service CloudDBA will diagnose the changed SQL. First based on the rules of SELECT *, dynamic SQL and other audit suggestions, and then through the rule verification of SQL optimization, index suggestions, the whole SQL audit process is completed by the product, the development will see the SQL audit report, including SQL rewriting suggestions, index suggestions.

In most business scenarios, one-time optimization is relatively easy. How do you do continuous optimization? This requires our optimization process to be able to achieve a closed loop, automatically connecting the whole link from collection -> analysis -> suggestion -> adoption -> tracking -> evaluation, rather than relying on DBA human series.



Database performance topic is very big, such as the above mentioned SQL online audit and optimization of closed loop, as well as SQL optimization, spatial optimization, lock optimization, full SQL, performance cost measurement and so on, I do not expand here, we can in Ali cloud data management DMS database optimization services (entry: DMS Login database – Performance) to find the answers you need.

  • security

In the database world, it can be said that data security is the foundation of everything. Most enterprise databases have the following data security risks:

· Application access

· Host access

· Data warehouse

Backup,

· Employee visits

In the early stage, enterprises tend to focus on the first four kinds of security protection, while ignoring the human factor. However, with the increase of employees and their roles, the security risk of employees’ access to data becomes increasingly prominent, and the security of employees’ access to data is an important topic to be solved by database DevOps.

Alibaba Database DevOps solves the security of employee access data from the following five aspects:

· Query control

· Change control

· Permission control

· Data classification

· Security audit



For example, permission control includes library/table/column fine-grained control, physical library/physical table/logical library/logical table permission, query/export/change permission classification, expiration reminder recycling and other function points.

 

4. Cloud: Data management DMS

The alibaba database DevOps introduced above corresponds to the product on the cloud: Alibaba Cloud Data Management DMS.



 

The data management DMS product architecture is as follows:



As you can see from the product architecture diagram, the Data Management DMS is available in enterprise, Premium, free, and mobile versions.

Among them, Ali Cloud Data Management DMS Enterprise edition builds a unique database management system for each enterprise, including the unique employee information, processes, rules and metadata of each enterprise. If the number of enterprise researchers is more than 20, it is strongly recommended to use.



If you are an individual, entrepreneurial team or small business user, we recommend Ali Cloud Data Management DMS Advanced edition, so that you can access cloud data more secure.



In addition, the data Management DMS has recently added a new member: the mobile version, which will support cloud data management in the first phase, database diagnostics optimization, database development process and self-built IDC database.

 

We look forward to your joining us

Over the past 10 years, we have seen the future of enterprise database services. We welcome more and more users to use our services, and we also welcome more and more partners to join us, because there are so many areas we need to explore.

  • Database product Specialist/Senior specialist

    • Enterprise database products
    • Distributed database products
    • BI data analysis products
  • R&d specialist/senior expert

    • Java backend development
    • The front-end development
    • Mobile development



About the author: Heng Tiegang (Payne), a database product expert of Ali, is responsible for the design of DMS product of Ali Cloud data management, and has worked as operation and maintenance DBA and Taobao DBA.