Oracle MySQL 8.0 GA Stable Release

eva2000 · Apr 20, 2018

Good news is Oracle has announced that MySQL 8.0 has gone GA Stable with 2x the performance of MySQL 5.7 !

MySQL adds NoSQL and many new enhancements to the world’s most popular open source database:

NoSQL Document Store gives developers the flexibility of developing traditional SQL relational applications and NoSQL, schema-free document database applications. This eliminates the need for a separate NoSQL document database.

SQL Window functions, Common Table Expressions, NOWAIT and SKIP LOCKED, Descending Indexes, Grouping, Regular Expressions, Character Sets, Cost Model, and Histograms.

JSON Extended syntax, new functions, improved sorting, and partial updates. With JSON table functions you can use the SQL machinery for JSON data.

GIS Geography support. Spatial Reference Systems (SRS), as well as SRS aware spatial datatypes, spatial indexes, and spatial functions.

Reliability DDL statements have become atomic and crash safe, meta-data is stored in a single, transactional data dictionary

Observability Performance Schema, Information Schema, Invisible Indexes, Error Logging.

Manageability Persistent Configuration Variables, Undo tablespace management, Restart command, and New DDL.

High Availability InnoDB Cluster delivers an integrated, native, HA solution for your databases.

Security OpenSSL improvements, new default authentication, SQL Roles, breaking up the super privilege, password strength, authorization.

Performance Up to 2x faster than MySQL 5.7.

Click to expand...

Observability
Information Schema (speed up)

MySQL 8.0 reimplements Information Schema. In the new implementation the Information Schema tables are simple views on data dictionary tables stored in InnoDB. This is by far more efficient than the old implementation with up to 100 times speedup.

Performance Schema (speed up)

MySQL 8.0 speeds up performance schema queries by adding more than 100 indexes on performance schema tables.
Click to expand...

Security features
OpenSSL by Default in Community Edition

MySQL 8.0 is unifying on OpenSSL as the default TLS/SSL library for both MySQL Enterprise Edition and MySQL Community Edition.

SQL roles

MySQL 8.0 implements SQL Roles. A role is a named collection of privileges. The purpose is to simplify the user access right management. One can grant roles to users, grant privileges to roles, create roles, drop roles, and decide what roles are applicable during a session.
Click to expand...

Performance
MySQL 8.0 is up to 2x faster than MySQL 5.7. MySQL 8.0 comes with better performance for Read/Write workloads, IO bound workloads, and high contention “hot spot” workloads.
Click to expand...

Scaling Read/Write Workloads

MySQL 8.0 scales well on RW and heavy write workloads. On intensive RW workloads we observe better performance already from 4 concurrent users and more than 2 times better performance on high loads comparing to MySQL 5.7. We can say that while 5.7 significantly improved scalability for Read Only workloads, 8.0 significantly improves scalability for Read/Write workloads. The effect is that MySQL improves hardware utilization (efficiency) for standard server side hardware (like systems with 2 CPU sockets). This improvement is due to re-designing how InnoDB writes to the REDO log. In contrast to the historical implementation where user threads were constantly fighting to log their data changes, in the new REDO log solution user threads are now lock-free, REDO writing and flushing is managed by dedicated background threads, and the whole REDO processing becomes event-driven.
Click to expand...

Better Performance upon High Contention Loads (“hot rows”)

MySQL 8.0 significantly improves the performance for high contention workloads. A high contention workload occurs when multiple transactions are waiting for a lock on the same row in a table, causing queues of waiting transactions. Many real world workloads are not smooth over for example a day but might have bursts at certain hours. MySQL 8.0 deals much better with such bursts both in terms of transactions per second, mean latency, and 95th percentile latency. The benefit to the end user is better hardware utilization (efficiency) because the system needs less spare capacity and can thus run with a higher average load.
Click to expand...

eva2000 · Apr 20, 2018

Looks like it's time for me to also read up more on MySQL 8.0 via the official documentation. Only played with early betas way back so lots to learn and read up before thinking of adding MySQL 8.0 support to Centmin Mod

MySQL 8.0 release notes MySQL :: MySQL 8.0 Release Notes

eva2000 · Apr 20, 2018

MySQL 5.7 to MySQL 8.0 Upgrade Notes

MySQL :: MySQL 8.0 Reference Manual :: 2.11.1.2 Changes Affecting Upgrades to MySQL 8.0

Big one is character set and collation default changes
Important change: The default character set has changed from latin1 to utf8mb4. These system variables are affected:

The default value of the character_set_server and character_set_database system variables has changed from latin1 to utf8mb4.

The default value of the collation_server and collation_database system variables has changed from latin1_swedish_ci toutf8mb4_0900_ai_ci.

As a result, the default character set and collation for new objects differ from previously unless an explicit character set and collation are specified. This includes databases and objects within them, such as tables, views, and stored programs. Assuming that the previous defaults were used, one way to preserve them is to start the server with these lines in the my.cnf file:
Code (Text):
[mysqld]
character_set_server=latin1
collation_server=latin1_swedish_ci
In a replicated setting, when upgrading from MySQL 5.7 to 8.0, it is advisable to change the default character set back to the character set used in MySQL 5.7 before upgrading. After the upgrade is completed, the default character set can be changed to utf8mb4.
Click to expand...
FYI, Centmin Mod 123.09beta01 MariaDB 10.1 defaults to uft8 and uft8_general_ci for character set and collation server defaults
Code (Text):
mysqladmin var | egrep 'character_set_server|collation' | tr -s ' '
| character_set_server | utf8 |
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
So would need to specify in /etc/my.cnf
Code (Text):
[mysqld]
character_set_server=uft8
collation_server=utf8_general_ci
Also zlib bundled (along with lz4 and lzma)
The zlib library version bundled with MySQL was raised from version 1.2.3 to version 1.2.11.

The zlib compressBound() function in zlib 1.2.11 returns a slightly higher estimate of the buffer size required to compress a given length of bytes than it did in zlib version 1.2.3. The compressBound() function is called by InnoDB functions that determine the maximum row size permitted when creating compressed InnoDB tables or inserting rows into compressed InnoDB tables. As a result, CREATE TABLE ... ROW_FORMAT=COMPRESSED or INSERToperations with row sizes very close to the maximum row size that were successful in earlier releases could now fail.

If you have compressed InnoDB tables with large rows, it is recommended that you test compressed table CREATE TABLE statements on a MySQL 8.0 test instance prior to upgrading.
With the introduction of the --innodb-directories feature, the location of file-per-table and general tablespace files created with an absolute path or in a location outside of the data directory should be added to the innodb_directories argument value. Otherwise, InnoDB is not able to locate these files during recovery. To view tablespace file locations, query the INFORMATION_SCHEMA.FILES table:
Code (Text):
SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES \G
Click to expand...
SQL Changes

SQL Changes

Some keywords may be reserved in MySQL 8.0 that were not reserved in MySQL 5.7. See Section 9.3, “Keywords and Reserved Words”. This can cause words previously used as identifiers to become illegal. To fix affected statements, use identifier quoting. See Section 9.2, “Schema Object Names”.

After upgrading, it is recommended that you test optimizer hints specified in application code to ensure that the hints are still required to achieve the desired optimization strategy. Optimizer enhancements can sometimes render certain optimizer hints unnecessary. In some cases, an unnecessary optimizer hint may even be counterproductive.

Click to expand...

Data dictionary changes

Data Dictionary Changes
MySQL Server 8.0 incorporates a global data dictionary containing information about database objects in transactional tables. In previous MySQL series, dictionary data was stored in metadata files and nontransactional system tables. As a result, the upgrade procedure is somewhat different from previous MySQL releases and requires that you verify the upgrade readiness of your installation by checking specific prerequisites. For more information, see Verifying Upgrade Prerequisites for Your MySQL 5.7 Installation. A data dictionary-enabled server entails some general operational differences; see Section 14.7, “Data Dictionary Usage Differences”.
Click to expand...

hmm this part can be trickier for some MySQL :: MySQL 8.0 Reference Manual :: 2.11.1.1 MySQL Upgrade Strategies

There must be no tables that use obsolete data types, obsolete functions, orphan .frm files, InnoDB tables that use nonnative partitioning, or triggers that have a missing or empty definer or an invalid creation context (indicated by the character_set_client, collation_connection, Database Collationattributes displayed by SHOW TRIGGERS or the INFORMATION_SCHEMA TRIGGERS table).

There must be no partitioned tables that use a storage engine that does not have native partitioning support

There must be no tables in the MySQL 5.7 mysql system database that have the same name as a table used by the MySQL 8.0 data dictionary.

There must be no tables that have foreign key constraint names longer than 64 characters

Your MySQL 5.7 installation must not use features that are not supported by MySQL 8.0. Any changes here are necessarily installation specific, but the following examples illustrate the kind of things to look for:

Tables that use a storage engine not supported in MySQL 8.0 must be altered to use a supported engine. For example, MySQL 8.0 does not yet support MySQL Cluster, so NDB tables must be altered to use a different storage engine.

Some server startup options and system variables have been removed in MySQL 8.0. See Features Removed in MySQL 8.0, and Section 1.5, “Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 8.0”. If you use any of these, an upgrade requires configuration changes.
Example: Because the data dictionary provides information about database objects, the server no longer checks directory names in the data directory to find databases. Consequently, the --ignore-db-dir option is extraneous and has been removed. To handle this, remove any instances of --ignore-db-dir from your startup configuration. In addition, remove or move the named data directory subdirectories before upgrading to MySQL 8.0. (Alternatively, let the 8.0 server add those directories to the data dictionary as databases, then remove each of those databases using DROP DATABASE.)

Click to expand...

And MySQL :: MySQL 8.0 Reference Manual :: 14.7 Data Dictionary Usage Differences

Previously, tables in the mysql system database were visible to DML and DDL statements. As of MySQL 8.0, data dictionary tables are invisible and cannot be modified or queried directly. However, in most cases there are corresponding INFORMATION_SCHEMA tables that can be queried instead. This enables the underlying data dictionary tables to be changed as server development proceeds, while maintaining a stable INFORMATION_SCHEMA interface for application use.
Click to expand...
INFORMATION_SCHEMA tables in MySQL 8.0 are closely tied to the data dictionary, resulting in several usage differences:
Previously, INFORMATION_SCHEMA queries for table statistics in the STATISTICS and TABLES tables retrieved statistics directly from storage engines. As of MySQL 8.0, cached table statistics are used by default. The information_schema_stats_expiry system variable defines the period of time before cached table statistics expire. The default is 86400 seconds (24 hours). (To update the cached values at any time for a given table, use ANALYZE TABLE.) If there are no cached statistics or statistics have expired, statistics are retrieved from storage engines when querying table statistics columns. To always retrieve the latest statistics directly from storage engines, set information_schema_stats_expiry to 0. For more information, seeSection 8.2.3, “Optimizing INFORMATION_SCHEMA Queries”.

Several INFORMATION_SCHEMA tables are views on data dictionary tables, which enables the optimizer to use indexes on those underlying tables. Consequently, depending on optimizer choices, the row order of results for INFORMATION_SCHEMA queries might differ from previous results. If a query result must have specific row ordering characteristics, include an ORDER BY clause.

mysqldump and mysqlpump no longer dump the INFORMATION_SCHEMA database, even if explicitly named on the command line.

CREATE TABLE dst_tbl LIKE src_tbl requires that src_tbl be a base table and fails if it is an INFORMATION_SCHEMA table that is a view on data dictionary tables.
Previously, result set headers of columns selected from INFORMATION_SCHEMA tables used the capitalization specified in the query. This query produces a result set with a header of table_name:
Code (Text):
SELECT table_name FROM INFORMATION_SCHEMA.TABLES;
As of MySQL 8.0, these headers are capitalized; the preceding query produces a result set with a header of TABLE_NAME. If necessary, a column alias can be used to achieve a different lettercase. For example:
Code (Text):
SELECT table_name AS 'table_name' FROM INFORMATION_SCHEMA.TABLES;
Click to expand...
The data directory affects how mysqldump and mysqlpump dump information from the mysql system database:

Previously, it was possible to dump all tables in the mysql system database. As of MySQL 8.0, mysqldump and mysqlpump dump only non-data dictionary tables in that database.

Previously, the --routines and --events options were not required to include stored routines and events when using the --all-databases option: The dump included the mysql system database, and therefore also the proc and event tables containing stored routine and event definitions. As of MySQL 8.0, the event and proc tables are not used. Definitions for the corresponding objects are stored in data dictionary tables, but those tables are not dumped. To include stored routines and events in a dump made using --all-databases, use the --routines and --events options explicitly.

Previously, the --routines option required the SELECT privilege for the proc table. As of MySQL 8.0, that table is not used; --routines requires the global SELECT privilege instead.

Previously, it was possible to dump stored routine and event definitions together with their creation and modification timestamps, by dumping theproc and event tables. As of MySQL 8.0, those tables are not used, so it is not possible to dump timestamps.

Previously, creating a stored routine that contains illegal characters produced a warning. As of MySQL 8.0, this is an error.

Click to expand...

bassie · Apr 20, 2018

Why using MySQL anyway

eva2000 · Apr 20, 2018

bassie said: ↑

Why using MySQL anyway
Click to expand...

performance is enticing

Performance
MySQL 8.0 is up to 2x faster than MySQL 5.7. MySQL 8.0 comes with better performance for Read/Write workloads, IO bound workloads, and high contention “hot spot” workloads.
Click to expand...

eva2000 · Apr 20, 2018

Interesting New MySQL 8.0 innodb_dedicated_server Variable Optimizes InnoDB from the Get-Go

In this post, we’ll look at the MySQL 8.0 innodb_dedicated_server variable.

MySQL 8.0 introduces a new variable called innodb_dedicated_server. When enabled, it auto tunes innodb_buffer_pool_size, innodb_log_file_size and innodb_flush_method at startup (if these variables are not explicitly defined in my.cnf).

The new MySQL 8.0 variable automatically sizes the following variables based on the RAM size of the system:

innodb_buffer_pool_size:

<1G: 128M(default value if innodb_dedicated_server is OFF)

<=4G: Detected Physical RAM * 0.5

>4G: Detected Physical RAM * 0.75

innodb_log_file_size:

<1G: 48M(default value if innodb_dedicated_server is OFF)

<=4G: 128M

<=8G: 512M

<=16G: 1024M

>16G: 2G

The variable also sets the following:

innodb_flush_method:

Set to O_DIRECT_NO_FSYNC if the setting is available on the system. If not, set it to the default InnoDB flush method

These new default values are very reasonable, and the changes to these three variables show considerable performance improvements from the get-go than using the old default values. As stated in the worklog of this feature, the current MySQL version (5.7) only uses around 512M RAM with the default settings. With the new feature, these variables can easily adapt to the amount of RAM allocated to the server for the convenience of the system/database administrator.
Click to expand...

bassie · Apr 20, 2018

Thus Will be available soon in MariaDB and Percona.
Few reasons to not use MySQL 10 reasons to migrate to MariaDB (if still using MySQL) - Seravo

eva2000 · Apr 20, 2018

Yeah true eventually MariaDB will backport from MySQL 8.0 but probably be a while given what they're doing on MariaDB 10.3 Changes & Improvements in MariaDB 10.3. Centmin Mod will always default to MariaDB 10 for near future and MySQL 8.0 is a wait and see for me - though still need to play with MySQL 8.0 for my general learning and skill set upkeep

Derek · Apr 20, 2018

It sounds like a big chunk of their touted performance increase may be in more rational default settings for variables we tend to tune anyway.

It might be worth a test to see if a real install sees any significant improvements.

eva2000 · Apr 20, 2018

Derek said: ↑

It sounds like a big chunk of their touted performance increase may be in more rational default settings for variables we tend to tune anyway.

It might be worth a test to see if a real install sees any significant improvements.
Click to expand...

Nah definitely alot of performance improvements under the hood for MySQL 8.0 ! They're real.

eva2000 · Apr 20, 2018

Test MySQL 8.0.11 install side by side with MariaDB 10.1.32 on Centmin Mod 123.09beta01

Code (Text):

mysqladmin -P 3307 -S /opt/mysql/data/mysql.sock ver
mysqladmin  Ver 8.0.11 for el7 on x86_64 (MySQL Community Server - GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          8.0.11
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /opt/mysql/data/mysql.sock
Uptime:                 3 min 36 sec

Threads: 2  Questions: 7  Slow queries: 0  Opens: 127  Flush tables: 2  Open tables: 103  Queries per second avg: 0.032

Code (Text):

ls -lah /opt/mysql/data/
total 165M
drwxr-xr-x 5 mysql mysql 4.0K Apr 20 06:25 .
drwxr-xr-x 3 root  root    17 Apr 20 05:47 ..
-rw-r----- 1 mysql mysql   56 Apr 20 05:48 auto.cnf
-rw-r----- 1 mysql mysql  178 Apr 20 05:49 binlog.000001
-rw-r----- 1 mysql mysql  178 Apr 20 05:51 binlog.000002
-rw-r----- 1 mysql mysql  178 Apr 20 06:01 binlog.000003
-rw-r----- 1 mysql mysql  178 Apr 20 06:15 binlog.000004
-rw-r----- 1 mysql mysql  178 Apr 20 06:23 binlog.000005
-rw-r----- 1 mysql mysql  178 Apr 20 06:24 binlog.000006
-rw-r----- 1 mysql mysql  178 Apr 20 06:25 binlog.000007
-rw-r----- 1 mysql mysql  155 Apr 20 06:25 binlog.000008
-rw-r----- 1 mysql mysql  128 Apr 20 06:25 binlog.index
-rw------- 1 mysql mysql 1.7K Apr 20 05:48 ca-key.pem
-rw-r--r-- 1 mysql mysql 1.1K Apr 20 05:48 ca.pem
-rw-r----- 1 mysql mysql    5 Apr 20 06:25 centos7.localdomain.pid
-rw-r--r-- 1 mysql mysql 1.1K Apr 20 05:48 client-cert.pem
-rw------- 1 mysql mysql 1.7K Apr 20 05:48 client-key.pem
-rw-r----- 1 mysql mysql 6.4K Apr 20 06:25 error.log
-rw-r----- 1 mysql mysql 3.2K Apr 20 06:25 ib_buffer_pool
-rw-r----- 1 mysql mysql  12M Apr 20 06:25 ibdata1
-rw-r----- 1 mysql mysql  48M Apr 20 06:25 ib_logfile0
-rw-r----- 1 mysql mysql  48M Apr 20 06:20 ib_logfile1
-rw-r----- 1 mysql mysql  12M Apr 20 06:25 ibtmp1
drwxr-x--- 2 mysql mysql  137 Apr 20 05:48 mysql
-rw-r----- 1 mysql mysql  24M Apr 20 06:25 mysql.ibd
srwxrwxrwx 1 mysql mysql    0 Apr 20 06:25 mysql.sock
-rw------- 1 mysql mysql    5 Apr 20 06:25 mysql.sock.lock
drwxr-x--- 2 mysql mysql 4.0K Apr 20 05:48 performance_schema
-rw------- 1 mysql mysql 1.7K Apr 20 05:48 private_key.pem
-rw-r--r-- 1 mysql mysql  452 Apr 20 05:48 public_key.pem
-rw-r--r-- 1 mysql mysql 1.1K Apr 20 05:48 server-cert.pem
-rw------- 1 mysql mysql 1.7K Apr 20 05:48 server-key.pem
drwxr-x--- 2 mysql mysql   27 Apr 20 05:48 sys
-rw-r----- 1 mysql mysql  10M Apr 20 06:25 undo_001
-rw-r----- 1 mysql mysql  10M Apr 20 06:25 undo_002

Code (Text):

mysql -t -e "SHOW ENGINES;"
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

Code (Text):

mysql -t -e "SHOW PLUGINS;"
+----------------------------+----------+--------------------+---------+---------+
| Name                       | Status   | Type               | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| caching_sha2_password      | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| sha2_cache_cleaner         | ACTIVE   | AUDIT              | NULL    | GPL     |
| CSV                        | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM_RESET        | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX       | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_STATS   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TEMP_TABLE_INFO     | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_METRICS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DELETED          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_BEING_DELETED    | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_CONFIG           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_CACHE      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_TABLE      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TABLES              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TABLESTATS          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_INDEXES             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TABLESPACES         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_COLUMNS             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_VIRTUAL             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CACHED_INDEXES      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| MyISAM                     | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MYISAM                 | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| PERFORMANCE_SCHEMA         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| TempTable                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                    | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE                  | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED                  | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| ngram                      | ACTIVE   | FTPARSER           | NULL    | GPL     |
| mysqlx                     | ACTIVE   | DAEMON             | NULL    | GPL     |
| mysqlx_cache_cleaner       | ACTIVE   | AUDIT              | NULL    | GPL     |
+----------------------------+----------+--------------------+---------+---------+

eva2000 · Apr 20, 2018

Customising the mysql client paths for easier administration/testing of both MariaDB 10.1.32 and Oracle MySQL 8.0.11 side by side on same server

Code (Text):

mysqladmin ver
/usr/bin/mysqladmin  Ver 9.1 Distrib 10.1.32-MariaDB, for Linux on x86_64
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Server version          10.1.32-MariaDB
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 19 min 5 sec

Threads: 1  Questions: 6  Slow queries: 0  Opens: 17  Flush tables: 1  Open tables: 11  Queries per second avg: 0.005

Code (Text):

mysqladmin8 ver
/usr/local/mysql/bin/mysqladmin  Ver 8.0.11 for el7 on x86_64 (MySQL Community Server - GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version          8.0.11
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /opt/mysql/data/mysql.sock
Uptime:                 29 min 56 sec

Threads: 2  Questions: 13  Slow queries: 0  Opens: 110  Flush tables: 2  Open tables: 86  Queries per second avg: 0.007

Derek · Apr 20, 2018

OK, I'm very interested in what you discover. I'm still pessimistic - I think the only way they double performance is on some workloads where the initial tuning variables were poorly set by older versions of MySQL, but it'll be nice to know.

eva2000 · Apr 21, 2018

Well there are improvements in some workloads but regressions against MySQL 5.7 in other workloads but really depends. But improvements in mysql replication are real. Not sure how it fairs against MariaDB 10.1/10.2/10.3 replication.

Doing real performance tests is hard if you don't have access to dedicated servers (which are solely used for such tests) as VPS servers are subject to neighbour contention so results may not be accurate. My last publication of comparison numbers was just over 7 yrs ago LOL

MySQL Benchmarks - MySQL 5.0.92 vs 5.1.55 vs 5.5.10 vs Percona 5.1.55 vs Percona 5.5.8 vs MariaDB 5.2.5 | vbtechsupport.com

MySQL Benchmarks: MySQL 5.5.11 vs Percona 5.5.10-20.1 vs MariaDB 5.2.5 - InnoDB and MyISAM | vbtechsupport.com

MySQL Benchmarks: MariaDB 5.2.5 custom CentOS 64bit RPM binaries - Intel optimized | vbtechsupport.com

Reading back those benchmark comparison articles brings back memories of a different time when server hardware and mysql performance were way different to how they perform these days ! That server I used for benchmark tests - Quad Intel Xeon L7555 1.86Ghz Nehalem-EX processors with 32C/64T cost US$20,000+ to build back in 2011 ! It was these benchmark results that made me decide that Centmin Mod LEMP stacks should switch to MariaDB as the default MySQL server version

Cloud hosting as evolved these days though with both DigitalOcean and Vultr offering up more dedicated core based VPS servers which have less contention and Packet.net and Vultr offering up bare metal dedicated servers charged on hourly basis Unfortunately, it still costs time and money to do CentminMod Benchmarking Budget Assistance

Derek · Apr 22, 2018

Eva, are you asking me to set up a physical server for you to test on?

eva2000 · Apr 22, 2018

No not right now... no much free time either way for full on benchmark testing. Just reading the MySQL 8 documentation when i have time to get up to speed right now with regards to the newer/different mysql options MySQL 8 has compared to MySQL 5.7 and MariaDB 10.1/10.2/10.3 Some of the new MySQL 8 defaults need to be adjusted to maintain backwards compatibility with older versions if you're doing in place upgrades. Lots to learn

Derek · Apr 22, 2018

Well, let me know. Right now I've got a few dual-E5 Xeon systems with either 144G or 128G RAM sitting in the garage floor. The problem is that they're loud, so I don't want to set them up and have to listen to them forever.

eva2000 · Apr 22, 2018

Derek said: ↑

Well, let me know. Right now I've got a few dual-E5 Xeon systems with either 144G or 128G RAM sitting in the garage floor. The problem is that they're loud, so I don't want to set them up and have to listen to them forever.
Click to expand...

Oh I know how I have one of those too - occupied with other tasks though Benchmarks - Dual Intel Xeon E5-2650v1, 64GB RAM, Asus Z9PE-D8 WS

eva2000 · Apr 30, 2018

More differences between MariaDB 10.1.32 vs MySQL 8.0.11 in terms of default innodb_default_row_format which was newly introduced in MariaDB 10.1.32 ([MDEV-14904] Please backport `innodb_default_row_format` to MariaDB 10.1 - JIRA). MariaDB uses COMPACT while MySQL 8.0 uses DYNAMIC defaults (same as MySQL 5.7 starting with MySQL 5.7.9).
MariaDB 10.1.32 default for Centmin Mod 123.09beta01 installs - Centmin Mod uses innodb_file_format Barracuda and enables innodb_large_prefix by default.
Code (Text):
mysqladmin var | egrep 'innodb_file_format|row_format|innodb_large_prefix' | tr -s ' '
| innodb_default_row_format | compact |
| innodb_file_format | Barracuda |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_large_prefix | ON |
Example test1 database created with t1 table on MariaDB 10.1.32 defaults to ROW_FORMAT=COMPACT
Code (Text):
mysql -e "SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE = 'InnoDB' AND ROW_FORMAT = 'Compact' AND TABLE_SCHEMA != 'mysql';"
+--------------+------------+--------+------------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE | ROW_FORMAT |
+--------------+------------+--------+------------+
| test1        | t1         | InnoDB | Compact    |
+--------------+------------+--------+------------+
Code (Text):
mysql -e "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 't1' \G" test1
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: test1
     TABLE_NAME: t1
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 4448
 AVG_ROW_LENGTH: 4140
    DATA_LENGTH: 18415616
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 2949120
      DATA_FREE: 4194304
 AUTO_INCREMENT: 5555
    CREATE_TIME: 2018-04-24 02:41:30
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
MySQL 8.0.11 default - seems MySQL 8.0 supports both old Antelope and newer Barracuda file formats so no longer needs a setting to decide and innodb_large_prefix is default to enabled/ON
Code (Text):
mysqladmin8 var | egrep 'innodb_file_format|row_format|innodb_large_prefix' | tr -s ' '
| innodb_default_row_format | dynamic |
This will effect how MySQL 8.0 handles InnoDB tables' max index length and make errors such as these a thing of the past i.e. MariaDB - Index column size too large. The maximum column size is 767 bytes.

Maybe for new Centmin Mod 123.09beta01 and MariaDB 10.1/10.2 installs, I should also make innodb_default_row_format switch from COMPACT to DYNAMIC too MySQL :: MySQL 8.0 Reference Manual :: 15.10.3 DYNAMIC and COMPRESSED Row Formats ?

When a table is created with ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED, InnoDB can store long variable-length column values (for VARCHAR, VARBINARY, and BLOB and TEXT types) fully off-page, with the clustered index record containing only a 20-byte pointer to the overflow page. InnoDB also encodes fixed-length fields greater than or equal to 768 bytes in length as variable-length fields. For example, a CHAR(255) column can exceed 768 bytes if the maximum byte length of the character set is greater than 3, as it is with utf8mb4.

Whether any columns are stored off-page depends on the page size and the total size of the row. When the row is too long, InnoDB chooses the longest columns for off-page storage until the clustered index record fits on the B-tree page. TEXT and BLOB columns that are less than or equal to 40 bytes are always stored in-line.

The DYNAMIC row format maintains the efficiency of storing the entire row in the index node if it fits (as do the COMPACT and REDUNDANT formats), but the DYNAMIC row format avoids the problem of filling B-tree nodes with a large number of data bytes of long columns. The DYNAMIC format is based on the idea that if a portion of a long data value is stored off-page, it is usually most efficient to store all of the value off-page. With DYNAMIC format, shorter columns are likely to remain in the B-tree node, minimizing the number of overflow pages needed for any given row.
Click to expand...

MySQL :: MySQL 8.0 Reference Manual :: 15.8.1.2 The Physical Row Structure of an InnoDB Table

The physical row structure of an InnoDB table depends on the row format specified when the table is created. If a row format is not specified, the default row format is used. The default row format for InnoDB tables is defined by the innodb_default_row_format configuration option, which has a default value ofDYNAMIC.

The following sections describe the characteristics of InnoDB row formats.

Determining the Row Format of an InnoDB Table

Redundant Row Format Characteristics

COMPACT Row Format Characteristics

DYNAMIC and COMPRESSED Row Format Characteristics

For more information about InnoDB row formats, see Section 15.10, “InnoDB Row Storage and Row Formats”.
Click to expand...

eva2000 · Apr 30, 2018

eva2000 said: ↑

Maybe for new Centmin Mod 123.09beta01 and MariaDB 10.1/10.2 installs, I should also make innodb_default_row_format switch from COMPACT to DYNAMIC too MySQL :: MySQL 8.0 Reference Manual :: 15.10.3 DYNAMIC and COMPRESSED Row Formats ?
Click to expand...

Centmin Mod 123.09beta01 has updated MariaDB 10.1/10.2 my.cnf templates to add new innodb_default_row_format variable and set it to default to DYNAMIC instead of COMPACT row formats as per Beta Branch - update MariaDB 10.1 & 10.2 default /etc/my.cnf default for innodb_default_row_format

Log in / Register

Forums

Learn about Centmin Mod LEMP Stack today

Oracle MySQL 8.0 GA Stable Release

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

MySQL 5.7 to MySQL 8.0 Upgrade Notes

bassie Well-Known Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

bassie Well-Known Member

eva2000 Administrator Staff Member

Derek Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

Derek Member

eva2000 Administrator Staff Member

Derek Member

eva2000 Administrator Staff Member

Derek Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

.

Log in / Register

Useful Searches

Learn about Centmin Mod LEMP Stack today

Oracle MySQL 8.0 GA Stable Release

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

MySQL 5.7 to MySQL 8.0 Upgrade Notes

bassie Well-Known Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

bassie Well-Known Member

eva2000 Administrator Staff Member

Derek Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

Derek Member

eva2000 Administrator Staff Member

Derek Member

eva2000 Administrator Staff Member

Derek Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

eva2000 Administrator Staff Member

.