[转帖]MySQL Performance : 8.0 and UTF8 impact

mysql,performance,and,utf8,impact · 浏览次数 : 0

小编点评

**MySQL Performance and UTF8 Encoding** The passage discusses the impact of using UTF8 encoding on MySQL performance and the importance of setting the `character_set_server` and `collation_server` variables to `utf8mb4` to optimize performance. **Key Points:** * MySQL 8.0 uses the `utf8mb4` charset by default, which offers significant performance improvements. * Using `utf8mb4` can result in a performance overhead due to character set mismatches between client and server. * The `skip_character_set_client_handshake` option is not set by default, which can cause performance issues. * Aligning clients and servers according to the database settings is recommended for optimal performance. * Using `utf8mb4` as the first character set ensures compatibility with a wide range of characters. * Optimizing MySQL performance with UTF8 encoding is crucial for high-performance applications. **Results:** The passage provides benchmark results comparing the performance of MySQL 8.0 and 5.7 MariaDB with UTF8 encoding. The results show that MySQL 8.0 is significantly faster in various performance tests, including OLTP, point-selects, and distinct-ranges queries. **Conclusion:** Using UTF8 encoding with MySQL 8.0 is highly recommended for improving performance. Aligning clients and servers according to the database settings, and setting the `character_set_server` and `collation_server` variables, are essential steps for optimizing performance.

正文

http://dimitrik.free.fr/blog/posts/mysql-performance-80-and-utf8-impact.html

2018-04-26 00:58 | MySQL, Performance, UTF8

by Dimitri

The world is moving to UTF8, MySQL 8.0 has utf8mb4 charset as default now, but, to be honest, I was pretty surprised how sensible the "charset" related topic could be.. -- in fact you may easily hit huge performance overhead just by using an "odd" config settings around your client/server charset and collation. While to avoid any potential charset mismatch between client and server, MySQL has from a long time an excellent option : "skip-character-set-client-handshake" which is forcing any client connection to be "aligned" with server settings ! (for more details see the ref. manual : https://dev.mysql.com/doc/refman/8.0/en/server-options.html#option_mysqld_character-set-client-handshake) -- this option is NOT set by default (to leave you a freedom in choose of charsets used on client and server sides). However, in my sense, it's still better to align clients according to the server settings to avoid any potential client misconfig..

As well if you wish to use UTF8, please use "utf8mb4" as first of all it's the most complete for any kind of characters (and probably the only one which makes sense as of today), and second -- its related code was yet more improved in MySQL 8.0 for better efficiency. How much more efficient ? -- let's see from the following test results.

but first of all, the related config setup :

character_set_server=utf8mb4
collation_server=utf8mb4_0900_ai_ci
skip-character-set-client-handshake
sort_buffer_size=512K

NOTE: mind to use a bigger sort buffer for UTF8

The results are obtained with on the same 2S Skylake as in the previously published RO tests with latin1 and with the same test workloads (just that for latin1 you need to change character_set_server= latin1 and collation_server= latin1_swedish_ci)

So far, here we are :

Sysbench OLTP_RO 10Mx8-tables UTF8mb4 on 2S 48cores-HT Skylake

Comments :

MySQL 8.0 is doing up to 40% better than 5.7
MariaDB 10.3.5 is trying to follow, but not yet there..

Sysbench RO Point-Selects 10Mx8-tables UTF8mb4 on 2S 48cores-HT Skylake

Comments :

point-selects workload is much less sensible to UTF8
8.0 and 5.7 are getting highest QPS due RO fixes in 5.7
MariaDB 10.3.5 is going higher than before since adoption of InnoDB 5.7 code
5.6 is slower than others because it's 5.6, and has no 5.7 improvements ;-))

Sysbench RO Distinct-Ranges 10Mx8-tables UTF8mb4 on 2S 48cores-HT Skylake

Comments :

MySQL 8.0 is doing 30% better than 5.7
MariaDB is doing so bad here just because it's already doing something bad yet in previous latin1 tests..

Instead of summary :

a gentle reminder to PeterZ that MySQL is not "InnoDB only" ;-))
if you're doing "benchmarKeting" -- very easy to be "the best" by comparing everyone with UTF8, and hide all other regressions and bottlenecks.. ;-))
so, hope it's obvious why all my following benchmark results will be published with "latin1" only..

Thank You for using MySQL !

Rgds,

-Dimitri