The high quality of services required by an online banking trading system, which usually requires a single transaction to be completed in tens of milliseconds, poses a major challenge to the performance design of the entire online trading system, of which the efficiency of the implementation of database sql is a particularly critical element. Poor sql language can significantly affect the database processing response time, resulting in overcosting of the system, slower overall processing response, inability to meet operational service quality requirements and even depletion of overall server resources, leading to delays in the processing of web-wide business requests. Therefore, sql governance of the application system is important。
Published in the original language on the topic of goldendb in the magazine china communications technology, by goldendb senior architect sunday morning。
Past banking applications rely heavily on foreign database products, and sql governance tools are limited, often using application-level interdiction or webside programmes, static code checks or requests for interception for sql governance with low accuracy and complexity. As the pace of autonomous innovation in the country accelerates, there is an increasing number of applications based on the national production database, creating wider technological pathways through joint technology clearances by the application and the manufacturer. A large state-owned bank, a combined goldendb distributed database, explores good governance of sql in the nuclear layer of the database。
Full process governance
Sql governance involves the entire process chain of application development, production phase and after-effect optimization to ensure the sound operation of production systems and continuous improvement of quality (see figure 1). Sql audits are conducted during the development phase to improve the quality of sql preparation; sql tasks that seriously affect services are quickly terminated during the production process, and the negative sql is optimized after the event。
Figure 1 sql full process governance
In the application development phase, the application of sql is subjected to pressure measurements using analogue data, collection of sqls that are not reasonably designed, perform poorly and affect system operation, and poor sql rewrite and code optimization. Through this phase of governance, application processing performance can be rapidly upgraded to meet basic input performance requirements。
The relatively small volume of operational data in the early days of system go-live and, as the rapid development of operations and the continuing surge in data volume, the poor sql will only gradually show the performance pressure on systems and databases, thus affecting the quality of production services and requiring rapid intervention. The identification and suspension of sqls, which are excessively expensive to the production system, through the kernel, avoids the disruption of the entire production system, leading to a decline in the quality of services。
The manner in which business requests are terminated affects the success rate of current operations processing, which should be set with sufficient thresholds and avoid overuse. A more balanced approach is to collect sqls, which are operating at high operational cost, and to include them in subsequent iterative versions for fine-tuning。
Sql governance framework
Sql governance involves systems administrators, transport staff and application development teams. The administrator establishes the sql governance rules (profile) on the goldendb management table, which is based on the governance capabilities and syntax functions of the goldendb nuclear sql, which goes down to the goldendb database engine node, collects real-time sql implementation information on the sql links, identifies slow sql requests, judges whether to terminate or produce warnings according to the rules, and exports the sql statement and the detailed information collected to the operating log (see figure 2)。
Figure 2 sql governance framework
The management desk provides disaggregated statistics on slow sql statements and logs collected, which are sent by the administrator to the development team to optimize or improve the iterative version. In addition, when the goldendb management statistics reach a certain number of sqls that have been terminated as a result of the governance rules, they generate alarms that are reported to the centralized police platform, where transport personnel assess the impact of the quality of services and systematically intervene。
The goldendb database engine identifies slow sql by:
(a) the slowness of sql, judged on the basis of actual implementation costs, including the length of execution, the excessive aggregation of results for queries or associations and the excessive number of scanned data blocks
Sets the type of sql that causes performance effects or safety hazards to the production system based on static sql rules
Collect data on resources occupied by users, including systems such as cpu, connectivity and database resources。
In this project, a pilot programme was also achieved to filter out the estimated costs of the sql implementation plan by judging the overestimation of sql, which was terminated prior to implementation in order to avoid adverse sql impacts on the system in advance。
Since the identification and intervention locations are at different stages of implementation of the database, the key modules within the database require modification and expansion, including the sql resolutioner (based on static sql rules), the sql optimization (based on cost estimates) and the implementer (based on actual implementation statistics). The main modules dealing with sql governance are shown in figure 3。
Figure 3 key modules on sql governance
Sql governance capacity
Goldendb supports various sql governance rules, such as:
Governance rules based on actual expenses: maximum execution time allowed by dml and dql statements, maximum number of result clusters allowed to return for single sqls, number of associated lines in multi-table linking queries, maximum number of data blocks allowed to read for single sqls, maximum number of data blocks allowed to read by single session, including all data blocks read from memory and disk。
The rules governing high-risk sql are: interception of sql blacklists, prohibition of unindexed searches (full-table scans, etc.), prohibition of cartestra, prohibition of drop databas, prohibition of drop table, prohibition of truncate table, prohibition of truncate pation。
Rules of governance based on resource statistics: set the maximum number of general assembly messages that the user can create, maximum cpu time allowed by a single session, maximum cpu time allowed by a single sql, maximum cost allowed by a sql statement。
Goldendb can set different governance rules for different users to adapt to various application scenarios. For example, on-line transactions are characterized by high combined, low-temporal, short flat requests, while batch processing scenarios are large-scale data queries and updates, which take longer and are larger in volume. For online transaction users, maximum execution time is set at millisecond level and greater security is prohibited from using truncate partiton; for batch processing users, maximum execution time is relaxed to minutes while allowing them to run truncate partiion to obtain faster data cleansing efficiency。
Implementation effectiveness
Many of the bank's applications are currently using the sql governance platform for the development phase of sql governance, which has resulted in significant improvements in governance efficiency and rapid contraction of problem sql. At the same time, slow sql collection and interception are being implemented in pilot production applications, rapid intervention in slow sql is taking place, and the smoothness of production system services is being improved; the slow sql list collected in pilot production applications is being synchronized with the application team for periodic overhaul, and the average sql implementation time of the system remains good, despite increasing operational data。
Sql governance based on the nuclear layer in the database has many advantages compared to application layer governance and bypass governance:
High accuracy. As it is achieved within the database, sql quality assessments are derived from real data, which are more accurate and comprehensive than static code checks, and based on sql text rules, and are not easily missed; the continuing governance advantages of the programme are highlighted as actual production data volume and processing load changes。
Small expenses. Bystander governance requires the collection of a large amount of data for intermediate analysis, an increased system burden and the deployment of specialized production equipment; the programme collects internal structures and a small amount of statistical data in the database implementation process, with little increase in system costs; and the implementation process is very simple and easy, based on the sql extension。
Positions are accurate. Direct access to database internal structure data allows for quick rule judgement and the export of associated log information, including implementation of sql, execution of expenses, immediate status of the database, etc., after-action analysis and optimization。
Quick intervention. Poor-quality sqls, which affect system processing requests, can be terminated in situ to ensure that the overall quality of services is not affected。
Summary of practice
Sql governance based on the database has many advantages in optimizing efficiency, light quantitative deployment and rapid accuracy. Good governance has been achieved in the bank's pilot application, which will be further extended to more new systems. The sql governance functions currently embedded in the database are still more based, with subsequent refinements to the application of specific scenarios, such as a combination of multiple governance rules to match the more complex sql governance requirements; and support for the online installation of plugins to enable the online expansion of sql governance rules in the commissioned database。
In the past, the banking system had relied on foreign database technology to make it difficult to develop such technological innovations, thanks to the advancement of national database applications, the rapid development of domestic database technology and the increasing pioneering work to address pain spots through the enhancement of bottom-up database technologies, as well as the advancement of database technology。
