A database performance optimization method based on cloud computing

By selecting the appropriate database engine and using cloud services in the cloud environment, automatic intelligent optimization of database performance is achieved, and the problem of local optimization in the existing technology is solved, query speed and system stability are improved, and data growth and Load changes reduce costs.

CN117951119BActive Publication Date: 2024-11-08LIAONING LEGU SOFTWARE CO LTD
View PDF 0 Cites 0 Cited by

Patent Information

Application Number
CN202410192399.1
Authority / Receiving Office
CN · China
Patent Type
Patents(China)
Current Assignee / Owner
Filing Date
2024-02-21
Publication Date
2024-11-08
Estimated Expiration
2044-02-21

AI Technical Summary

Technical Problem

The existing database performance optimization solution cannot achieve automatic intelligent optimization, resulting in performance improvement being limited to local areas, unable to adapt to system needs, and consumes a lot of manpower, material resources and time.

Method used

Select the appropriate database engine in the cloud environment, set up automatic scaling strategies, use cloud storage and caching services, adopt partitioning and sharding technologies, use cloud platform monitoring tools for real-time performance monitoring and regular optimization, dynamically adjust the size of database instances, and select Appropriate data type and index.

Benefits of technology

It realizes automatic intelligent optimization of database performance, adapts to data growth and load changes, improves query speed and system stability, reduces storage costs, and reduces resource waste.

✦ Generated by Eureka AI based on patent content.

Smart Images

  • Figure CN117951119B_ABST
    Figure CN117951119B_ABST
Patent Text Reader

Abstract

The present invention belongs to the field of databases, and specifically relates to a method for optimizing database performance based on cloud computing, including: selecting a database engine; utilizing the elastic scaling function provided by the cloud platform to automatically expand or reduce the number of database instances according to the load conditions; utilizing cloud storage services and selecting storage types according to requirements; deploying network connections between database instances and applications; utilizing cache technology to reduce the burden on the database; utilizing the automatic backup function provided by the cloud service to ensure data security; and regularly checking database query performance. The present invention can accelerate query speed through indexes, especially for large tables or complex queries, reduce disk I / O, and indexes can reduce the database system's demand for disk reading and improve query efficiency; by writing efficient query statements, the burden on the database system can be reduced, query speed can be increased, and query optimization can reduce the server load and improve the overall performance of the system.
Need to check novelty before this filing date? Find Prior Art

Description

Technical Field

[0001] The present invention belongs to the field of databases, and in particular relates to a database performance optimization method based on cloud computing. Background Art

[0002] Database performance depends on many factors. From the operating system, it involves the number of threads executing tasks, system parameter configuration, etc.; from the database level, it involves table structure, storage method, query plan, SQL execution order, parameter setting, data storage location, etc.; from the hardware level, it involves disk throughput, seek time, memory space, CPU resources, etc. Therefore, database performance optimization can be adjusted and optimized from multiple levels: hardware system, operating system, and database itself.

[0003] At present, the solutions for database performance optimization cover hardware optimization, operating system optimization and database system optimization. Specific solutions include disk seek optimization, disk read and write optimization, CPU computing resource optimization, memory optimization, sub-library and sub-table optimization, distributed cache optimization, one master and multiple backups, storage system optimization, command query separation (CQS), data synchronization, query plan optimization, etc. There are single optimization solutions and multiple optimization solutions combined. However, no matter which optimization solution is used, it depends on the manual configuration or deployment of technicians. The optimization content is only adjusted for the part of database performance that can be optimized, making it impossible to achieve automatic intelligent optimization of database performance optimization, and unable to provide overall optimization for improving database performance. The so-called performance improvement is only limited to the improvement of local performance, which leads to bottlenecks in database performance optimization, difficulty in improvement, and optimization solutions that always fail to keep up with system requirements, and consumes a lot of manpower, material resources and time costs. Summary of the invention

[0004] In order to solve the above technical problems, the present invention proposes a database performance optimization method based on cloud computing, comprising:

[0005] S1: In the cloud environment, choose a variety of database engines and select a database engine based on the needs and characteristics of the application;

[0006] S2: Set the trigger conditions and policies for automatic scaling to automatically expand or reduce the number of database instances based on load conditions;

[0007] S3: Use cloud storage services and select storage types based on your needs;

[0008] S4: Deploys the network connection between the database instance and the application;

[0009] S5: Use cloud cache service to cache popular data in memory to improve access speed;

[0010] S6: Use the automatic backup function provided by cloud services to ensure data security;

[0011] S7: Regularly check database query performance;

[0012] S8: Use partitioning or sharding technology to store data on different nodes according to the growth of data volume;

[0013] S9: Use the monitoring tools provided by the cloud platform to monitor database performance indicators in real time, and regularly evaluate and optimize database performance.

[0014] Beneficial effects of the present invention:

[0015] The present invention can speed up the query speed by creating indexes, especially for large tables or complex queries, and reduce disk I / O; at the same time, the index can reduce the database system's demand for disk reading and improve query efficiency. By writing efficient query statements, the burden of the database system can be reduced, the query speed can be increased, and query optimization can reduce the server load and improve the overall performance of the system;

[0016] The present invention adopts partitioning and sharding technology to reduce the data scope of a single query, improve query efficiency, and allow database horizontal expansion to adapt to the growing data and user load;

[0017] The present invention adopts caching technology to store the results of commonly used queries, reducing the reading pressure of the database. The caching technology can store hot data in the memory, reduce the access requirements to the database, and improve the reading performance. The cache can reduce the load of the database server and improve the stability of the overall system;

[0018] The present invention helps maintain query performance and avoid index failure due to data changes by regularly maintaining and optimizing indexes. By regularly optimizing indexes, the fragmentation of the database system can be reduced and the overall stability of the system can be improved. The selection of appropriate data types can reduce the storage space of the database table and reduce storage costs. At the same time, appropriate data types help improve query speed and reduce data reading time.

[0019] By analyzing the execution plan, the present invention can identify potential performance problems and optimize the query execution path. Optimizing the execution plan can reduce the time complexity of the query and improve the overall efficiency of the database system.

[0020] The present invention can automatically adjust the scale of the database system according to the load changes by dynamically adjusting the instance size, ensuring that the system has sufficient resources during peak periods, and automatically reducing the number of instances during low loads to reduce costs and avoid resource waste;

[0021] The present invention does not rely on manual configuration or deployment by technicians, and achieves overall optimization of database performance through automatic intelligent optimization. BRIEF DESCRIPTION OF THE DRAWINGS

[0022] Figure 1 This is a flow chart of a database performance optimization method based on cloud computing of the present invention. DETAILED DESCRIPTION

[0023] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0024] A database performance optimization method based on cloud computing, such as Figure 1 As shown, the following steps are included:

[0025] Step 1: Choose a suitable database engine;

[0026] Step 2: Use the elastic scaling function provided by the cloud platform to automatically expand or reduce the number of database instances according to the load conditions;

[0027] Step 3: Use cloud storage services and select the appropriate storage type based on your needs;

[0028] Step 4: Deploy the network connection between the database instance and the application;

[0029] Step 5: Use caching technology to reduce database burden;

[0030] Step 6: Use the automatic backup function provided by cloud services to ensure data security;

[0031] Step 7: Check database query performance regularly;

[0032] Step 8: Based on the growth of data volume, use partitioning or sharding technology to store data on different nodes;

[0033] Step 9: Use the monitoring tools provided by the cloud platform to monitor database performance indicators in real time;

[0034] Step 10: Regularly evaluate and optimize database performance;

[0035] Step 1: In the cloud environment, select a variety of database engines and choose the most suitable engine based on the needs and characteristics of the application;

[0036] Step 2 ensures that the system can still provide good performance during peak periods;

[0037] Step 4: Ensure low latency and high bandwidth;

[0038] Step 5: Use cloud cache service to cache popular data in memory to improve access speed;

[0039] In step 7, use appropriate indexes and reasonable query statements to avoid inefficient operations such as full table scans.

[0040] The specific steps of step 2 elastic scaling are as follows:

[0041] Using the elastic database service provided by the cloud service, set the trigger conditions and policies for automatic scaling based on the performance indicators of the database instance. When the performance indicators reach or exceed the set threshold, the scaling operation is automatically triggered. The performance is improved by increasing the number of database instances and upgrading the hardware configuration of a single database instance. According to the changes in business load, the database instance is divided into the master database and the read-only slave database.

[0042] When the load is low, only the master database is retained; when the load is high, read-only slave databases are automatically added to share the read load;

[0043] Use the dynamic instance resizing function provided by the cloud service to automatically adjust the size of the database instance according to load changes, set monitoring and alert rules, and track database performance indicators in real time through the cloud platform's monitoring service;

[0044] When performance indicators exceed the predetermined range, an alarm is triggered and corresponding scaling operations are performed. Historical data and trend analysis are used to predict future load changes and perform scaling operations in advance to avoid performance issues. During the scaling process, the effectiveness of backup and recovery strategies is ensured to prevent data loss or inconsistency.

[0045] Performance metrics include CPU utilization, memory usage, and number of network connections.

[0046] The specific steps of step five are:

[0047] Select a cache system suitable for the database, install and configure the selected cache system, configure the database connection in the application so that the results are stored in the cache when querying and updating data, determine which data should be cached and how long the cache life cycle is, modify the application code to use the cache, implement the cache invalidation mechanism, ensure that when the data in the database changes, the relevant cache is updated or cleared, set up a monitoring mechanism to monitor the cache hit rate, performance and other indicators in real time, and make adjustments based on the monitoring results, optimize the cache strategy and configuration parameters, and for sensitive data, ensure that only non-sensitive data is cached, and take measures to ensure data security.

[0048] The specific steps of step seven are:

[0049] Use database performance analysis tools to analyze query execution plans, time consumption, IO operations and other performance indicators. Create appropriate indexes based on query conditions and frequently accessed fields. Avoid creating too many indexes. Too many indexes may cause write operation performance to degrade. Use covering indexes, that is, the index includes all columns required for the query to avoid reading the actual data again, improve query performance, avoid using full table scans, especially for operations on large tables. Use indexes or appropriate filtering conditions to narrow the query scope. When using multi-table queries, make sure to use JOIN operations to avoid Cartesian products.

[0050] The specific steps of step three are:

[0051] Choose the right storage service based on the application's needs;

[0052] For different data in the database table, select the appropriate storage type;

[0053] For storage services that support data compression, enable data compression to reduce storage space usage and improve performance;

[0054] For large database tables, use data partitioning technology to divide data into smaller logical units to improve query performance;

[0055] For services such as object storage, select the corresponding storage category and use caching technology to cache frequently accessed data to reduce the database burden and improve read performance;

[0056] For block storage services, pre-allocate storage space to avoid the performance overhead of dynamically expanding storage space at runtime, separate frequently accessed hot data from infrequently accessed cold data to provide different performance and costs on different storage media, regularly clean up expired or no longer used data to reduce storage space usage and ensure database performance, and ensure that an effective backup and recovery strategy is established to prevent data loss or unavailability;

[0057] Set up a monitoring mechanism to monitor storage performance indicators, and configure storage access rights based on the principle of least privilege to ensure that only necessary personnel can access and modify storage data.

[0058] The specific steps to use the dynamic instance resizing function provided by the cloud service to automatically adjust the size of the database instance according to load changes, set monitoring and alert rules, and track database performance indicators in real time through the cloud platform's monitoring service are as follows:

[0059] Select a database service that supports auto-scaling in the cloud service. In the console, select the database instance for which you want to auto-scale, and then configure automatic adjustment. According to the load changes, set the conditions that trigger the automatic adjustment, and set the minimum and maximum number of database instances to ensure that the automatic adjustment does not exceed the expected range. After the configuration is complete, test the auto-scaling function by simulating the load or increasing the actual load to observe whether the database instance performs the scaling operation as expected.

[0060] Take the implementation of elastic scaling in the AWS environment as an example:

[0061] Use your AWS account to log in to the AWS Management Console. In the AWS console, select "Services" and then find "Auto Scaling" under "Compute". In the Auto Scaling console, select "Launch Configurations" to create a launch configuration. In the configuration, specify the instance type, AMI (Amazon Machine Image), storage and other information. Then select "Auto Scaling Groups" to create an Auto Scaling group, associate it with the launch configuration, and configure the minimum number of instances, maximum number of instances, network configuration and other information. On the details page of the Auto Scaling group, configure the automatic scaling policy and set the conditions for triggering scaling, such as starting scaling when CPU utilization reaches 70% and starting scaling down when it drops to 30%. Set CloudWatch alarm rules to monitor the performance indicators of the Auto Scaling group. When creating an alarm rule, select the target as the Auto Scaling group, set the alarm threshold and trigger action, such as sending a notification or executing the automatic scaling policy. After the configuration is complete, test the elastic scaling function by simulating the load or increasing the actual load, observe whether Auto Scaling performs the scaling operation as expected, and regularly monitor the Auto The performance indicators of the Scaling group are used to adjust the parameters of the scaling policy based on the monitoring results to ensure that the system can quickly and automatically adjust resources when the load changes.

[0062] Take AWS EC2 (Elastic Compute Cloud) dynamic instance resizing as an example

[0063] Log in to the AWS Management Console using your AWS account. In the console, select "Services" and then find "EC2" under "Compute". If you use an Auto Scaling group, select "Auto Scaling Groups" and then select the corresponding Auto Scaling group. If you manage EC2 instances directly, select "Instances". If you selected an Auto Scaling group, find the "Launch Configuration" section and select the associated launch configuration. If you selected an EC2 instance, directly select the instance that needs to be adjusted. If you selected a launch configuration, find "Change Instance Type" in the related Actions menu. If you selected an EC2 instance, you can directly find "Instance Settings" in the "Actions" menu and select "Change Instance Type". In the pop-up window, select the new instance type to change to. After selecting the new instance type, confirm your selection and click "Apply" or "Confirm" to trigger the change of the instance type.

[0064] The specific steps of step eight are:

[0065] Analyze the data model and access pattern in the database to determine which data can be partitioned or sharded. Based on the results of data analysis, select the partitioning or sharding strategy required by the application. Determine the fields or keys used for partitioning or sharding. Before performing partitioning or sharding, ensure a comprehensive backup. Perform database partitioning or sharding operations. Modify the application code to adapt to the new partitioning or sharding structure. In a sharded environment, configure a load balancer to ensure that query requests can be evenly distributed to different shards. Set up a monitoring system to track the performance indicators of partitions or shards. Perform step-by-step testing and optimization in a production environment. Observe how the system's performance changes as the amount of data increases, and make adjustments and optimizations based on the test results.

[0066] The specific steps to create the appropriate index in step 7 are:

[0067] Analyze the query patterns and frequently accessed fields of the application, determine which fields are frequently used in query conditions and join conditions, and select fields suitable for creating indexes based on query requirements. Generally, fields frequently used in WHERE clauses, JOIN clauses, and ORDER BY clauses are candidate fields for indexes. Avoid creating too many indexes on the table, because each index takes up storage space and increases the overhead of write operations. Create indexes only for commonly used query scenarios. Consider creating composite indexes for multiple fields that are frequently used together. Composite indexes can improve the performance of multi-field queries. Select the appropriate index type based on the query pattern. Common index types include ordinary indexes, unique indexes, and full-text indexes. Analyze the distribution of data in the table and avoid creating indexes on columns with uneven data distribution. Uneven data distribution may cause some indexes to fail. Update the database's index statistics regularly to ensure that the database optimizer can generate better query execution plans based on the latest data distribution. For frequently accessed static data, consider using cache instead of creating indexes on the database. Cache can provide faster read performance. Try to use index coverage, that is, the index contains all the columns required for the query. This can reduce database I / O operations and improve query performance.

[0068] The specific steps for regular database performance evaluation in step 10 are:

[0069] Determine key indicators for evaluating performance, such as query response time, transaction throughput, number of database connections, CPU utilization, memory utilization, etc., to quantify performance. Before the actual evaluation, collect baseline data for database operation and record the initial values ​​of key performance indicators for future comparison and analysis. Use load testing tools to simulate the load in the actual production environment, gradually increase the load, and observe the performance of the database under different load conditions. Use the tools provided by the database management system to analyze the query execution plan to observe whether there are unnecessary full table scans, index failures, etc., and optimize them based on the analysis results. Evaluate the effectiveness of existing indexes in the database, analyze index usage, hit rate, and whether there are indexes that need to be optimized, and analyze the hit rate of database cache, including query cache, data cache, etc. Ensure a high cache hit rate, reduce the number of disk accesses, use the tools provided by the operating system and database management system to monitor the utilization of hardware resources, such as CPU utilization, memory utilization, disk I / O, etc., to ensure that resource utilization is within an acceptable range, analyze slow queries based on database logs or performance monitoring tools, identify which queries take a long time to execute, and then optimize these queries, test the performance of the database under high concurrency conditions, simulate multiple concurrent users to execute different transactions or queries, observe the database response time and transaction throughput, run backup and recovery tests, evaluate the backup and recovery performance of the database, ensure that data can be restored in a timely manner in the event of a failure, test the scalability of the database, that is, whether the system can dynamically adjust and maintain performance when the load increases, use load testing to simulate high load conditions, observe the performance of the database, analyze database logs and error messages, check whether there are any anomalies or errors, and resolve potential problems in a timely manner.

[0070] Although embodiments of the present invention have been shown and described, it will be appreciated by those skilled in the art that various changes, modifications, substitutions and variations may be made to the embodiments without departing from the principles and spirit of the present invention, and that the scope of the present invention is defined by the appended claims and their equivalents.

Claims

1. A database performance optimization method based on cloud computing, characterized in that: include: S1: In a cloud environment, choose a variety of database engines and select a database engine based on the needs and characteristics of the application; S2: Set the trigger conditions and policies for automatic scaling to automatically expand or reduce the number of database instances based on load conditions; Set the trigger conditions and policies for automatic scaling to automatically expand or reduce the number of database instances based on load conditions, including: Using the elastic database service provided by the cloud service, the performance indicators of the database instance are automatically triggered when the performance indicators reach or exceed the set threshold. The performance is improved by increasing the number of database instances and upgrading the hardware configuration of a single database instance. The performance indicators of the database instance include CPU utilization, memory usage and number of network connections; According to the changes in business load, the database instance is divided into a master database and a read-only slave database. When the load is low, only the master database is retained. When the load is high, a read-only slave database is automatically added to share the read load. Use the dynamic instance resizing function provided by the cloud service to automatically adjust the size of the database instance according to load changes, set monitoring and alert rules, and track database performance indicators in real time through the cloud platform's monitoring service; When performance indicators exceed the predetermined range, alarms are triggered and historical data and trends are used to predict future load changes. Scaling operations are performed in advance to avoid performance issues. During the scaling process, the effectiveness of data backup and recovery is ensured to prevent data loss or inconsistency. Use the dynamic instance resizing feature provided by the cloud service to automatically adjust the size of the database instance according to load changes, set monitoring and alert rules, and track database performance indicators in real time through the cloud platform's monitoring service, including: Select a database service that supports auto-scaling in the cloud service. In the console, select the database instance to be auto-scaled, and then configure automatic adjustment. According to the load changes, set the conditions that trigger the automatic adjustment and set the minimum and maximum number of database instances to ensure that the automatic adjustment does not exceed the expected range. After the configuration is completed, test the auto-scaling function by simulating the load or increasing the actual load to observe whether the database instance performs the scaling operation as expected. S3: Use cloud storage services and select storage types based on your needs; Use cloud storage services and choose the appropriate storage type based on your needs, including: Choose storage services based on application requirements; For different data in the database table, select the corresponding storage type; For storage services that support data compression, enable data compression to reduce storage space usage and improve performance; For large database tables, use data partitioning technology to divide data into smaller logical units to improve query performance; For object storage services, select the corresponding storage category and use caching technology to cache frequently accessed data to reduce the database burden and improve read performance; For block storage services, pre-allocate storage space to avoid the performance overhead of dynamically expanding storage space at runtime, separate frequently accessed hot data from infrequently accessed cold data to provide different performance and costs on different storage media, regularly clean up expired or no longer used data to reduce storage space usage and ensure database performance, and ensure that an effective backup and recovery strategy is established to prevent data loss or unavailability; Set up the first monitoring mechanism to monitor storage performance indicators and configure storage access permissions based on the principle of least privilege to ensure that only necessary personnel can access and modify storage data; S4: Deploys the network connection between the database instance and the application; S5: Use cloud cache service to cache popular data in memory to improve access speed; Use cloud cache services to cache popular data in memory to improve access speed, including: Select a cache system suitable for the database, install and configure the selected cache system, configure the database connection in the application so that the results are stored in the cache when querying and updating data, determine which data should be cached and how long the cache lifecycle is, modify the application code to use the cache, implement the cache invalidation mechanism, ensure that when the data in the database changes, the relevant cache is updated or cleared, set up a second monitoring mechanism to monitor the cache hit rate, performance and other indicators in real time, and make adjustments based on the monitoring results, optimize the cache strategy and configuration parameters, and for sensitive data, ensure that only non-sensitive data is cached, and take measures to ensure data security; S6: Use the automatic backup function provided by cloud services to ensure data security; S7: Regularly check database query performance; Regularly check database query performance, including: Use database performance analysis tools to analyze query execution plans, time consumption, and IO operation performance indicators. Create appropriate indexes based on query conditions and frequently accessed fields. Avoid creating too many indexes, which may cause write operation performance to degrade. Use covering indexes, that is, indexes that include all columns required for the query to avoid re-reading of actual data, improve query performance, and avoid using full table scans. When using multi-table queries, ensure that JOIN operations are used to avoid Cartesian products. Create appropriate indexes, including: Analyze the query patterns and frequently accessed fields of the application, determine which fields are frequently used in query conditions and join conditions, select fields suitable for index creation based on query requirements, create indexes only for commonly used query scenarios, create composite indexes for multiple fields that are frequently used together, select index types based on query patterns, analyze the distribution of data in the table, avoid creating indexes on columns with uneven data distribution, which can cause index failures, regularly update database index statistics, use cache to provide faster read performance for frequently accessed static data, and use index coverage; S8: Use partitioning or sharding technology to store data on different nodes according to the growth of data volume; As the amount of data grows, partitioning or sharding technology is used to disperse and store data on different nodes, including: Analyze the data model and access pattern in the database to determine which data can be partitioned or sharded. Based on the results of data analysis, select the partitioning or sharding strategy required by the application. Determine the fields or keys used for partitioning or sharding. Before performing partitioning or sharding, ensure a comprehensive backup. Perform database partitioning or sharding operations. Modify the application code to adapt to the new partitioning or sharding structure. In a sharding environment, configure a load balancer to ensure that query requests can be evenly distributed to different shards. Set up a monitoring system to track the performance indicators of partitions or shards. Perform step-by-step testing and optimization in a production environment. Observe how the system's performance changes as the amount of data increases, and make adjustments and optimizations based on the test results. S9: Use the monitoring tools provided by the cloud platform to monitor database performance indicators in real time and regularly evaluate and optimize database performance; Regularly perform database performance assessment and optimization, including: Identify key indicators for evaluating performance, including query response time, transaction throughput, number of database connections, CPU utilization, and memory utilization; Before the actual evaluation, collect the baseline data of the database operation, record the initial values ​​of the key performance indicators, gradually increase the load, observe the performance of the database under different load conditions, analyze the usage and hit rate of the indexes, and whether there are indexes that need to be optimized, analyze the hit rate of the database cache, including the query cache and data cache, ensure the cache hit rate is high, and reduce the number of disk accesses.