MySQL 8 is slower than 5.7?

I have been read all the blog relate to MySQL 8 or review about this version and i found it may true about the performance

my MySQL 8 compile

cmake -Wno-dev -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=RelWithDebInfo -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci -DENABLED_PROFILING=ON -DENABLE_DOWNLOADS=1 -DINSTALL_LIBDIR=/usr/lib64/mysql -DINSTALL_INCLUDEDIR=/usr/include -DINSTALL_LAYOUT=STANDALONE -DINSTALL_PKGCONFIGDIR=/usr/lib64/pkgconfig -DMYSQL_TCP_PORT=3306 -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DSYSTEMD_PID_DIR=/var/data/mysql -DMYSQL_MAINTAINER_MODE=OFF -DSYSTEMD_SERVICE_NAME=mysqld -DMYSQL_DATADIR=/var/data/mysql -DWITH_BOOST=/soft/boost_1_68_0 -DWITH_CURL=system -DWITH_INNODB_MEMCACHED=ON -DWITH_LIBEVENT=bundled -DWITH_LZ4=bundled -DWITH_NUMA=ON -DWITH_SSL=/usr -DWITH_SYSTEMD=1 -DWITH_ZLIB=bundled -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 -DWITH_TOKUDB=1 -DWITH_ROCKSDB=1 -DFORCE_INSOURCE_BUILD=1

and then i just do very simple test and compare between 2 same hardware for MySQL 5.7 & MySQL 8

If i do insert a table (let’s say attendance table with 7 field) with ~80 millions records and ~9GB, the 5.7 took around 2 hours something and MySQL 8 took almost 15 hours

I also test complex queries and found MySQL 8 always respond slower than 5.7

SELECT CONCAT(employees.employee_id,’ - ',employees.display_name) FROM employees LEFT JOIN (SELECT * FROM employee_positions ep1 WHERE ep1.deleted = 0 AND ep1.effective_date = (SELECT MAX(effective_date) FROM employee_positions ep2 WHERE ep2.employee = ep1.employee AND ep2.effective_date <= ‘2019-03-05’ AND ep2.deleted=0) AND ep1.id = (SELECT MAX(id) FROM employee_positions ep3 WHERE ep3.employee = ep1.employee AND ep3.effective_date = ep1.effective_date AND ep3.deleted=0)) as eep ON employees.id = eep.employee WHERE (employees.employee_id LIKE ‘%A%’ OR employees.display_name LIKE ‘%A%’) AND employees.deleted=0 AND employees.status=5 AND employees.entity=1 AND employees.id <> 1 AND employees.user_type <> 5 AND employees.id NOT IN (SELECT employee FROM resignation WHERE leaving_date <= ‘2019-03-05’ AND deleted=0 AND entity=1 AND is_final_payment=0) AND eep.department IN (SELECT id FROM departments WHERE deleted=0 AND status=5 AND entity=1 AND ((secretary=25416) OR (secretary LIKE ‘25416,%’) OR (secretary LIKE ‘%,25416’) OR (secretary LIKE ‘%,25416,%’)) AND approval_status=5) LIMIT 10;

1 Like

Well, it is true that MySQL 8 is not always faster than MySQL 5.7, as well as MySQL 5.7 is not always faster than MySQL 5.6, and so on. It all depends on specific use case, settings, hardware, etc.
In general, newer major releases tend to be better in highly concurrent workload run on multi-core machines, while single thread performance is expected to be rather slightly worse.
Then, there are more subtle changes that can play role, like different variable defaults, changes in the query optimizer, etc. And I would take a look at these in your example.
But, I would rather suggest more systematic approach, as you start from comparing some already pretty specific cases here. So, maybe first compare the already available upstream binary builds of latest MySQL 5.7 vs 8.0 on the same hardware, using generic sysbench OLTP test. Next, compare your custom builds with the same benchmark. Next, test that custom table insert, etc, etc.
Each little step should give you some idea where the difference may be coming from.
As for the complex query - you need to analyze the EXPLAIN plans, maybe adjust the optimizer_switch settings if needed, and so on. Small speed difference may be just due to worse single thread performance in 8.0. Big difference usually means different query plan chosen or some additional problem.

1 Like