Geek Answers Handbook

Mysql 5.7.10 performance 3 Times Slower vs 5.6.28

This weekend I rolled back my database from 5.7.10 to 5.6.28. In a test load of 27 million records with multi-record inserts, it took 120 minutes to load this MySQL 5.7.10 database. On the same machine, 5.6.28 took 40 minutes. This is a repeatable event. The test was conducted on a computer with Windows 7 Professional with 64 GB of memory. And this was done on a server with Windows Serve 2012 R2 with 256 GB of memory.

This was not the only problem. The group by statement statement does not work on the second query and always works on the table inside the query. Example:

select * from (select * from tablename order by datex desc, timex desc ) as A group by recordx;

This query should pull out the first record of table A, as in 5.6.28. At 5.70.10, the query pulled out the earliest record entered from tablename and NOT A. Even when desc is replaced by asc, the result will be the same.

5.6.28 always raises the top element in table “A” of the subquery.

MySQL needs some explanation regarding its requirements regarding 5.7.10, which are 3 times faster. This is definitely not consistent with backward compatibility, as shown in group-by-group behavior.

+6
source share
2 answers

This answer does not address the performance issue (I think additional information is required, and this should be a separate issue). It addresses the issue with the request.

The MySQL documentation is pretty explicit that this query:

 select * from (select * from tablename order by datex desc, timex desc ) as A group by recordx

gives vague results. Here is the corresponding quote:

The server can select any value from each group, so if they are not the same, the selected values ​​are undefined . Moreover, adding a ORDER BY cannot influence the choice of values ​​from each group. Sorting the result set occurs after the values ​​have been selected, and ORDER BY does not affect the values ​​in each group the server selects.

It is a pity that your developers used code that is explicitly documented so that it does not work correctly. You must fix the code.

One fix:

 select * from tablename where not exists (select 1 from tablename t2 where t2.recordx = t.recordx and (t2.datex > t.datex or t2.datex = t.datex and t2.timex > t.timex ) );
+2
source

By default, the default values ​​have been changed between 5.6 and 5.7. Sync_binlog is now enabled by default (see http://lefred.be/content/sync_binlog-1-in-5-7/ )

This, of course, affects performance, of course, if the cache raid is not used.

If you don't care about syncing your binary logs after each commit, disable it again.

0
source

More articles:


All Articles