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.

--

--

--

Database/System Administrator | DevOPS | Cloud Specialist | DevOPS

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Join Our Beta Testing Contest

Best Websites For Programmers and Tech Geeks That They Should Visit

How to Change the Opacity of the SnackBar In Flutter?

How To Calculate The BMI Formula Using The Metric System?

New Features of GitLab From the Last Quarter of 2021

Externalizing Configurations in Kubernetes Using ConfigMap and Secret

How To Keep Your Font Awesome Pro Secret Token in Public GitHub Actions

Docker how to switch from Ubuntu to OSX, the missing guide

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Pankaj kushwaha

Pankaj kushwaha

Database/System Administrator | DevOPS | Cloud Specialist | DevOPS

More from Medium

I deleted 78% of my Redis container and it still works

Docker everything!

How I set up a CI/CD pipeline with Github actions on web automation written in python

Making the First Provisioning With Terraform