Performance degradation of Oracle to MySQL Migration
Background: Recently, many customer systems have been transformed from Oracle to MySQL, and performance problems have occurred. The CPU is 100%, or the back-end CRM system complex SQL has accumulated during peak business hours, resulting in business failures. In my memory, Taobao also encountered many SQL performance problems during the initial migration from Oracle to MySQL. The most memorable subquery was the original version of MySQL 5.1.
This version has poor optimization of subqueries, resulting in a lot of There have been performance problems with the system that migrated from Oracle to MySQL, so the following development specifications stipulate that the foreground trading system should not have complicated table joins. Next, I will list some common problems that may occur in the process of migrating from Oracle to MySQL:
1). When customers perform O-data migration, there are three types of SQL that must be changed, not changed, and can be changed. For those that can be changed or not, we should provide some guidance suggestions to help users avoid possible problems in the future.
2). Instruct database R&D personnel and database administrators to use MySQL reasonably and give full play to MySQL’s optimal performance.
1 Parallel Processing
1.1 Background Introduction
Oracle can physically divide a large serial task (any DML, general DDL) into multiple small parts, these smaller parts can be processed at the same time, and finally each smaller part The obtained results are combined to obtain the final result, so Oracle can use the parallel processing technology to run very complex SQL queries in the OLAP application scenario.
There are several ways to start parallel query:
(1). Use a hint prompt in the query: select / + parallel(4) / count(*) from test_a ; — -Specify a parallel query with a degree of parallelism of 4.
(2) Use alter table to modify the table: alter table test_a parallel 4; — tell oracle to use parallelism 4 when creating the execution plan for this table.
1.2 Modification suggestions
Because MySQL does not support parallel processing, so when the application is migrated from Oracle to MySQL, special attention needs to be paid to the use of parallel processing SQL statements. Processing suggestions:
(1) On the Alibaba Cloud platform, an analytical database product such as ADS can be used to process parallel analytical queries in Oracle.
(2) Decompose complex SQL statements into business, disassemble them into a single SQL statement, and put the calculation results into the application for processing.
2 SQL performs logical reads, physical reads, and consumes time
2.1 Background Introduction
Compared with MySQL’s optimizer, Oracle’s optimizer has rich and perfect optimization algorithms. Only table connection Oracle supports nested loop, hash join, sort-merge join three The algorithm, and MySQL only supports the nested loop algorithm, so in some large table associations and multi-table associations complex queries MySQL processing power will be significantly reduced. How to identify some queries that are not suitable for migration to MySQL? It can be judged according to some key data in SQL execution: logical read, physical read, and time consumption.
Physical read: Read data from the data block to the buffer cache.
Logical read: refers to reading data blocks from Buffer Cache.
Execution time: The time it takes Oracle to execute a SQL.
(1), first query a table t
select * from t;
(2), second query:
select * from t;
the first query has 6 physical reads, the second query has 0 physical reads, 6 logical reads. When the data block is read for the first time, it will be cached in the buffer cache, and the second time the data block is read and modified in the memory buffer cache.
In Oracle performance tuning, logical read is a very important measure, it is not only easy to collect, but also can tell us a lot of information about the workload of the database engine. Logical read is the number of blocks read from the cache when executing SQL statements.
2.2 Modification suggestions
MySQL is very fast for simple SQL statement execution. For logical reads, physical reads or very high execution time SQL in Oracle applications, it is no longer suitable for migration to MySQL, and needs to be modified:
(1), single table Query logical read, physical read and execution time is relatively long, SQL may have a full table scan (dump demand) or the index is not optimal, you can use a read-only node to withstand dump or optimize the index.
(2) Multi-table query logical read, physical read and execution time is relatively long, you can use ADS analysis database products to deal with;
(3), multi-table query logical read, physical read and execution time is relatively long, Business decomposition can be carried out, disassembled into a single SQL statement, the calculation results are put into the application for processing.
Remarks: If the logical read and physical read exceeds 100W, and the execution time exceeds 5S, it is a large SQL query.
3.In (…..)
3.1 Background Introduction
The parameter limit for in(….) in Oracle is 1000. Although there is no number limit in MySQL, there is a SQL length limit, and the optimizer is in (…) The binary query is used when optimizing the query, so the more the number of in(…), the worse the performance, so it is recommended to control the number of in, not to exceed 100.
3.2 Reconstruction suggestions
Oracle: select * from t where id in(id1,id2…..id1000);
MySQL: select * from t where id in(id1,id2…..id100);
4 Subqueries
4.1 Background Introduction When
MySQL processed subqueries before version 5.6, the optimizer only supports nested loop algorithm, so when the associated table is large, it will bring performance bottlenecks. I once participated in a large-scale project migration from Oracle to MySQL. At that time, the database version was 5.5. There were a lot of subqueries in the original Oracle application. When migrating to MySQL, SQL execution piled up, the number of connections was full, and the database cpu was full. It will soon be exhausted, and the system will be restored only after the subquery is modified.
Typical subquery
SELECT first_name
FROM employees
WHERE emp_no IN
(SELECT emp_no FROM salaries_2000 WHERE salary = 5000);
MySQL’s processing logic is to traverse each record in the employees table and substitute it into the subquery.
4.2 Recommend to
rewrite the subquery
SELECT first_name
FROM employees emp,
(SELECT emp_no FROM salaries_2000 WHERE salary = 5000) sal
WHERE emp.emp_no = sal.emp_no;
Remarks: Subqueries have a higher risk in versions 5.1 and 5.5. Change subqueries to associations.
With the Mysql 5.6 version, you can avoid troublesome rewriting of subqueries.
5 View optimization
5.1 Background Introduction The
ordinary view does not store actual information, the data it operates from the basic table, so you can not create an index on the ordinary view. How to deal with a large number of queries on the view, but the query efficiency is low? There are materialized views in Oracle. Materialized views exist physically and can be indexed. MySQL does not support materialized views, so when the view in Oracle is migrated to MySQL, there may be performance degradation because there is no materialized view.
5.2 Reconstruction is recommended
to split the view service and implement it by the application.
6 Function Index
6.1 Background Introduction
Function-based indexes are similar to ordinary indexes, except that ordinary indexes are built on columns, and it is built on functions. Of course, this time has a certain effect on the inserted data, because you need to calculate it through the function and then generate the index. But inserting data is generally a small amount of inserting, and querying data generally has a large amount of data. In order to optimize the query speed, it is acceptable to reduce the point insertion speed slightly.
MySQL does not support functional indexes, so when SQL statements that use functional indexes in Oracle are migrated to MySQL, full table scans will suffer performance degradation due to the inability to use indexes.
For example, execute the following SQL statement:
select * from emp where date(gmt_create) = ‘2017–02–20’
Even if an index is created on gmt_create, the emp table will be scanned full table, and the gmt_create field will be removed after hours, minutes and seconds Compare. If we build a function-based index, such as: create index emp_upper_idx on emp(date(gmt_create)); At this time, we only need to scan a small part of the data by interval, and then get the rowid to access the data in the access table. This speed is compared. Fast.
6.2 Modification It is recommended
to remove the function on the field through SQL rewriting, so that the index on the field can be used:
select * from emp where gmt_create>=’2020–01–20 00:00:00' and gmt_created<’2020–01–21 00 :00:00'
7 Summary
(1). MySQL does not support parallel query and needs to be modified (keyword: parallel).
(2). The MySQL optimizer is weak, and attention should be paid to logical read, physical read and SQL with long execution time.
(3). MySQL recommends that the number of in(…) parameters should not exceed 100.
(4). MySQL is not very good for subquery optimization, it is recommended to modify the subquery or use the 5.6 database version.
(5). MySQL does not support materialized views, it is recommended to apply modified views.
(6). MySQL does not support functional indexes. It is recommended to rewrite SQL to avoid unusable indexes.
Reference Link:
http://www.oracle.com/us/products/database/migrating-to-oracle-database-wp-3610526.pdf
Thanks for reading this post.
Pankaj K.
