A Note on Performance Degradation When Migrating from Oracle to MySQL

Image for post
Image for post

Background

After transitioning from Oracle to MySQL, a large number of customer systems have encountered performance issues during peak traffic, when CPU usage reaches 100%, or the background CRM system becomes complex and causes a service fault due to SQL accumulation. As far as I recall, when Taobao first migrated from Oracle to MySQL, there were also lots of SQL performance problems, among which, the one that left the deepest impression on me is the subquery problem. The original version was MySQL 5.1, which is poorly optimized for subqueries, resulting in many systems that migrated from Oracle to MySQL encountering performance issues. As a result, our development regulations have since specified that no complex table shall be joined in the foreground transaction system. In this article, I will list a few common operations that may cause problems during migration from Oracle to MySQL:

1. Parallel Processing

1.1 Background

Oracle can physically divide a large serial task (any DML and common DDLs) into multiple smaller parts. These smaller parts can be simultaneously processed, and finally combined to obtain the final result. This allows Oracle to use parallel processing technology to operate very complex SQL queries in OLAP applications.

1.2 Transition Suggestions

MySQL does not support parallel processing. Therefore, after applications are migrated from Oracle to MySQL, only SQL statements subject to parallel processing can be used. Processing advice:

2. Logical Reads, Physical Reads, and Consumed Time during SQL Execution

2.1 Background

The Oracle optimizer contains more abundant and complete optimization algorithms than the MySQL optimizer. Just in terms of table connections, Oracle supports three algorithms, i.e., nested loop, hash join, and sort-merge join, while MySQL only supports nested loop. This means that MySQL often performs poorly when processing complex queries associated with large or multiple tables. How can we then identify which queries should not be migrated to MySQL? Well, it can be determined based on the logical reads, physical reads, and consumed time during SQL execution.

2.2 Transition suggestions

MySQL can rapidly execute simple SQL statements, but has difficulty executing the same statements that have high logical read, physical read, or execution requirements after migration to MySQL. This means that we need to make some transitions:

3.In(…)

3.1 Background

The number of in(…) parameters in Oracle is limited to within 1000. Although MySQL has no limit in number, it has a limit in SQL length. Meanwhile, a MySQL optimizer uses binary search when optimizing in(…) queries, meaning that the more parameters in the query the worse performance will be, so the number of parameters should generally be kept within 100.

3.2 Transition Suggestions

Oracle:select * from t where id in(id1,id2…..id1000);
MySQL:select * from t where id in(id1,id2…..id100);

4. Subquery

4.1 Background

MySQL 5.6 and below may encounter performance bottlenecks when processing subqueries associated with a large table because its optimizer only supports nested loop. I once participated in a large project that involved migrating from Oracle to MySQL. The database version at the time was 5.5 and the original Oracle application had stored a large number of subqueries, so after migrating to MySQL, a significant number of SQL statements were accumulated which filled up the available connections and overloaded the database’s CPU. We eventually had to modify the subqueries to recover the system.
Typical subquery
SELECT first_name
FROM employees
WHERE emp_no IN
(SELECT emp_no FROM salaries_2000 WHERE salary = 5000);
The MySQL processing logic involves traversing each record in an “employees” table and then substituting the records to a subquery

4.2 Transition Suggestions

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;
Note: Both MySQL 5.1 and 5.5 carry a large risk of modifying the subquery to an association.
MySQL 5.6 can avoid the awkward problem of rewriting the subquery.

5. View Optimization

5.1 Background

Indexing cannot be created on a common view as they do not store substantial information and the data it operates on comes from basic tables. So what can we do when we need to perform a large number of low efficiency queries on this kind of view? Oracle has a materialized view that is physically real and allows indexing.
However, MySQL does not support materialized views. Therefore, performance may be degraded after being migrated to MySQL.

5.2 Transition Suggestions

Split the view and execute it in the application.

6. Functional Index

6.1 Background

Functional index refers to a function-based index, similar to a common index. The only difference is that a common index is created based on a column, while a functional index is created based on a function. This, of course, will partly influence data insertion because the index is generated after the function has been computed. However, inserted data is usually quite small, while a data query involves a much larger data set. Slightly lowering insertion speed can help optimize query speed.

6.2 Transition Suggestions

Functions on fields are removed by means of SQL rewriting, such that the fields can be indexed:
select * from emp where gmt_create>=’2017–01–20 00:00:00' and gmt_created<’2017–01–21 00:00:00'

7. Summary

(1) Transition is required because MySQL does not support parallel query (keyword: parallel).
(2) MySQL optimizer is relatively ineffective, so special attention should be paid to SQL statements that have long logical reads, physical reads, and execution time.
(3) For MySQL, the number of in(…) parameters should be kept within 100.
(4) MySQL is poor at subquery optimization, so it is recommended to perform subquery transition or use MySQL 5.6.
(5) View transition is suggested since MySQL does not support materialized views.
(6) SQL rewriting is suggested to avoid indexing failure due to MySQL not supporting functional indexing.

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

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