mysql 5.7 vs 8.0預設值變化(筆記)
Changed Server Defaults
For more information about options or variables which have been added, see
Option/Variable Changes for MySQL 8.0
, in the
MySQL Server Version Reference
.
The following sections explain the changes to defaults and any impact they might have on your deployment.
Server Defaults
·
The default value of the
system variable and command line option
changed from
latin1
to
utf8mb4
. This is the server’s default character set. At this time, UTF8MB4 is the dominant character encoding for the web, and this change makes life easier for the vast majority of MySQL users. The upgrade from 5.7 to 8.0 does not change any character set for any existing database objects. But unless you specify
character_set_server
back to your previous default or explicitly set the character set then a new schema, table, or column by default uses
utf8mb4
. We recommend you move to
utf8mb4
whenever possible.
·
·
The default value of the
system variable and command line argument
changed from
latin1_swedish_ci
to
utf8mb4_0900_ai_ci
. This is the server’s default collation, the ordering of characters in a character set. There is a link between collations and character sets as each character set comes with a list of possible collations. The upgrade from 5.7 to 8.0 does not change any collation for any existing database objects, but takes effect for new objects.
·
·
The default value of the
system variable changed from
OFF
(MySQL legacy behavior) to
ON
(SQL standard behavior). This option was originally introduced in 5.6 and was
OFF
in 5.6 and 5.7.
·
·
The default value of the
system variable changed from
16KB
to
1MB
. The old default caused the optimizer trace to be truncated for any non-trivial query. This change ensures useful optimizer traces for most queries.
·
·
The default value of the
system variable changed from
OFF
to
ON
. This means that when the
validate_password
plugin is enabled, by default it now rejects passwords that match the current session user name.
·
·
The autosize algorithm for the
system variable has changed. The value for autosize (-1) is now set to the value of
, which is bigger than the calculated by
50 + (max_connections / 5)
. The
back_log
queues up incoming IP connect requests in situations where the server is not able to keep up with incoming requests. In the worst case, with
number of clients trying to reconnect at the same time, for example after a network failure, they can all be buffered and reject-retry loops are avoided.
·
·
The default value of the
system variable changed from
4194304
(4M) to
67108864
(64M). The main advantage with this larger default is less chance of receiving errors about an insert or query being larger than
max_allowed_packet
. It should be as big as the largest
you want to use. To revert to the previous behavior, set
max_allowed_packet=4194304
.
·
·
The default value of the
system variable changed from
64
to
1024
. This ensures that MySQL handles a larger number of warnings, such as an UPDATE statement that touches 1000s of rows and many of them give conversion warnings. It is common for many tools to batch updates, to help reduce replication lag. External tools such as pt-online-schema-change defaults to 1000, and gh-ost defaults to 100. MySQL 8.0 covers full error history for these two use cases. There are no static allocations, so this change only affects memory consumption for statements that generate lots of warnings.
·
·
The default value of the
system variable changed from
OFF
to
ON
. In other words, the event scheduler is enabled by default. This is an enabler for new features in SYS, for example “kill idle transactions”.
·
·
The default value of the
system variable changed from
2000
to
4000
. This is a minor change which increases session concurrency on table access.
·
·
The default value of the
system variable changed from
3
(Notes) to
2
(Warning). The purpose is to make the MySQL 8.0 error log less verbose by default.
·
InnoDB Defaults
·
Incompatible change
The default value of the
system variable changed from
0
to
2
. The configures the number of undo tablespaces used by InnoDB. In MySQL 8.0 the minimum value for
is 2 and rollback segments cannot be created in the system tablespace anymore. Thus, this is a case where you cannot revert back to 5.7 behavior. The purpose of this change is to be able to auto-truncate Undo logs (see next item), reclaiming disk space used by (occasional) long transactions such as a
.
·
·
The default value of the
system variable changed from
OFF
to
ON
. When enabled, undo tablespaces that exceed the threshold value defined by
are marked for truncation. Only undo tablespaces can be truncated. Truncating undo logs that reside in the system tablespace is not supported. An upgrade from 5.7 to 8.0 automatically converts your system to use undo tablespaces, using the system tablespace is not an option in 8.0.
·
·
The default value of the
system variable changed from
NULL
to
fsync
on Unix-like systems and from
NULL
to
unbuffered
on Windows systems. This is more of a terminology and option cleanup without any tangible impact. For Unix this is just a documentation change as the default was
fsync
also in 5.7 (the default
NULL
meant
fsync
). Similarly on Windows,
default
NULL
meant
async_unbuffered
in 5.7, and is replaced by default
unbuffered
in 8.0, which in combination with the existing default
has the same effect.
·
·
Incompatible change
The default value of the
system variable changed from
1
(consecutive) to
2
(interleaved). The change to interleaved lock mode as the default setting reflects the change from statement-based to row-based replication as the default replication type, which occurred in MySQL 5.7.
Statement-based replication
requires the consecutive auto-increment lock mode to ensure that auto-increment values are assigned in a predictable and repeatable order for a given sequence of SQL statements, whereas
row-based replication
is not sensitive to the execution order of SQL statements. Thus, this change is known to be incompatible with statement based replication, and may break some applications or user-generated test suites that depend on sequential auto increment. The previous default can be restored by setting
innodb_autoinc_lock_mode=1;
·
·
The default value of the
system variable changes from
1
(enable) to
0
(disable). This is done because fast IO (SSDs) is now the default for deployment. We expect that for the majority of users, this results in a small performance gain. Users who are using slower hard drives may see a performance loss, and are encouraged to revert to the previous defaults by setting
innodb_flush_neighbors=1
.
·
·
The default value of the
system variable changed from
0
(%) to
10
(%). With
innodb_max_dirty_pages_pct_lwm=10
, InnoDB increases its flushing activity when >10% of the buffer pool contains modified (‘dirty’) pages. The purpose of this change is to trade off peak throughput slightly, in exchange for more consistent performance.
·
·
The default value of the
system variable changed from
75
(%) to
90
(%). This change combines with the change to
and together they ensure a smooth InnoDB flushing behavior, avoiding flushing bursts. To revert to the previous behavior, set
innodb_max_dirty_pages_pct=75
and
innodb_max_dirty_pages_pct_lwm=0
.
·
Performance Schema Defaults
·
Performance Schema Meta Data Locking (MDL) instrumentation is turned on by default. The compiled default for
performance-schema-instrument='wait/lock/metadata/sql/%=ON'
changed from
OFF
to
ON
. This is an enabler for adding MDL oriented views in SYS.
·
·
Performance Schema Memory instrumentation is turned on by default. The compiled default for
performance-schema-instrument='memory/%=COUNTED'
changed from
OFF
to
COUNTED
. This is important because the accounting is incorrect if instrumentation is enabled after server start, and you could get a negative balance from missing an allocation, but catching a free.
·
·
Performance Schema Transaction instrumentation is turned on by default. The compiled default for
performance-schema-consumer-events-transactions-current=ON
,
performance-schema-consumer-events-transactions-history=ON
, and
performance-schema-instrument='transaction%=ON'
changed from
OFF
to
ON
.
·
Replication Defaults
·
The default value of the
system variable changed from
OFF
to
ON
. In other words, binary logging is enabled by default. Nearly all production installations have the binary log enabled as it is used for replication and point-in-time recovery. Thus, by enabling binary log by default we eliminate one configuration step, enabling it later requires a
restart. Enabling it by default also provides better test coverage and it becomes easier to spot performance regressions. Remember to also set
(see following change). The 8.0 default behavior is as if you issued
./mysqld --log-bin --server-id=1
. If you are on 8.0 and want 5.7 behavior you can issue
./mysqld --skip-log-bin --server-id=0
.
·
·
The default value of the
system variable changed from
0
to
1
(combines with the change to
). The server can be started with this default ID, but in practice you must set the
according to the replication infrastructure being deployed, to avoid having duplicate server ids.
·
·
The default value of the
system variable changed from
OFF
to
ON
. This causes a replica to log replicated events into its own binary log. This option is required for Group Replication, and also ensures correct behavior in various replication chain setups, which have become the norm today.
·
·
The default value of the
system variable changed from
0
to
30
. The new default
30
causes
to periodically purge unused binary logs that are older than 30 days. This change helps prevent excessive amounts of disk space being wasted on binary logs that are no longer needed for replication or recovery purposes. The old value of
0
disables any automatic binary log purges.
·
·
The default value of the
and
system variables change from
FILE
to
TABLE
. Thus in 8.0, replication metadata is stored in InnoDB by default. This increases reliability to try and achieve crash safe replication by default.
·
·
The default value of the
system variable changed from
OFF
to
XXHASH64
. This change enables transaction write sets by default. By using Transaction Write Sets, the source has to do slightly more work to generate the write sets, but the result is helpful in conflict detection. This is a requirement for Group Replication and the new default makes it easy to enable binary log writeset parallelization on the source to speed up replication.
·
·
The default value of the
system variable changed from
INDEX_SCAN,TABLE_SCAN
to
INDEX_SCAN,HASH_SCAN
. This change speeds up row-based replication by reducing the number of table scans the replica applier has to do to apply the changes to a table without a primary key.
·
·
The default value of the
system variable changed from
16M
to
128M
. This change increases the amount of memory available to multithreaded replicas.
·
·
The default value of the
gtid_executed_compression_period
system variable changed from
1000
to
0
. This change ensures that compression of the
mysql.gtid_executed
table only occurs implicitly as required.
·
Group Replication Defaults
·
The default value of
changed from 0 to 3, which means that automatic rejoin is enabled by default. This system variable specifies the number of tries that a member makes to automatically rejoin the group if it is expelled, or if it is unable to contact a majority of the group before the
setting is reached.
·
·
The default value of
changed from
ABORT_SERVER
to
READ_ONLY
. This means that when a member exits the group, for example after a network failure, the instance becomes read-only, rather than being shut down.
·
·
The default value of
changed from 0 to 5, meaning that a member suspected of having lost contact with the group is liable for expulsion 5 seconds after the 5-second detection period.
·
Most of these defaults are reasonably good for both development and production environments. There is one exception to this, we decided to keep the new option called
set to
OFF
although we recommend it to be
ON
for production environments. The reason for defaulting to
OFF
is that it causes shared environments such as developer laptops to become unusable, because it takes
all
the memory it can find.
For production environments we recommend setting
to
ON
. When set to
ON
the following InnoDB variables (if not specified explicitly) are autoscaled based on the available memory
,
, and
. See
.
Although the new defaults are the best configuration choices for most use cases, there are special cases, as well as legacy reasons for using existing 5.7 configuration choices. For example, some people prefer to upgrade to 8.0 with as few changes to their applications or operational environment as possible. We recommend to evaluate all the new defaults and use as many as you can. Most new defaults can be tested in 5.7, so you can validate the new defaults in 5.7 production before upgrading to 8.0. For the few defaults where you need your old 5.7 value, set the corresponding configuration variable or startup option in your operational environment.
In MySQL 8.0 there is the performance schema variables_info table, that shows for each system variable the source from which it was most recently set, and its range of values. So in 8.0 you get SQL access to everything there is to know about a configuration variable and its values. See
.
·
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29863023/viewspace-2846238/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL效能基準測試對比：5.7 VS 8.0MySql
- MySQL5.7中的sql_mode預設值MySql
- mysql8.0學習筆記MySql筆記
- MySQL 8.0 視窗函式-筆記MySql函式筆記
- MySQL8.0變化之密碼策略MySql密碼
- MySQL5.7/8.0效能分析shell指令碼MySql指令碼
- windows10 MySQL5.7升級至MySQL8.0WindowsMySql
- ES6筆記之引數預設值(譯)筆記
- mysql datetime增加預設值MySql
- MySQL修改欄位預設值MySql
- MySQL5.7和MySQL8.0的區別是什麼？MySql
- PHP study 升級預設的MYSQL版本 為5.7PHPMySql
- MySQL 8.0 Reference Manual（讀書筆記90節--Replication）MySql筆記
- MySQL 5.7和8.0 MHA架構下sysbench壓測MySql架構
- Swift學習筆記（三十一）——引數的預設值Swift筆記
- MySQL 8.0 全域性變數的修改持久化 set persistMySql變數持久化
- MySQL 優化筆記MySql優化筆記
- 筆記mysql優化筆記MySql優化
- MySQl優化筆記MySql優化筆記
- MySQL 5.7 索引優化MySql索引優化
- MySQL 8.0 Reference Manual（讀書筆記64節--InnoDBTransaction Model）MySql筆記
- MySQL 8.0 Reference Manual（讀書筆記65節--InnoDBLocks Set）MySql筆記BloC
- MySQL 8.0 Reference Manual（讀書筆記68節--Deadlocks）MySql筆記
- GitHub是怎樣把MySQL 5.7升級到8.0的？GithubMySql
- MySQL欄位預設值設定詳解MySql
- Centos 7 升級通過 yum 安裝的 MySQL 5.7 到 MySQL 8.0CentOSMySql
- MySQL8.0效能優化MySql優化
- MySQL 8.0 Reference Manual（讀書筆記63節--InnoDB Locking）MySql筆記
- MySQL 8.0 Reference Manual（讀書筆記67節--Phantom Rows）MySql筆記
- MySQL 8.0 Reference Manual（讀書筆記80節-- InnoDB Row Formats）MySql筆記ORM
- MySQL 8.0 Reference Manual（讀書筆記39節-- Data Types(1)）MySql筆記
- MySQL 8.0 Reference Manual（讀書筆記40節-- Data Types(2)）MySql筆記
- MySQL 8.0 Reference Manual（讀書筆記41節-- Data Types(3)）MySql筆記
- MySQL 最佳化筆記MySql筆記
- MySQL 筆記 - 索引優化MySql筆記索引優化
- 筆記mysql最佳化筆記MySql
- MySQL 5.6, 5.7, 8.0版本的新特性彙總大全MySql
- CSS變數的作用域和預設值CSS變數