2022-12-29

I want to wish you a happy new year with MariaDB 11.0!

MariaDB 11.0-Alpha preview  is now available for download!

MariaDB 11.0 is in essence a "new optimizer cost model" release.
There are also some other features , but I will concentrate this blog post on the 11.0 optimizer changes.

Why/when is MariaDB 11.0 of interest to you?

MariaDB 11.0 should be of interest to you if any of the following scenarios match your typical queries:

Your queries are

  • using more than two tables
  • using indexes that have lot of identical values.
  • using ranges that cover more than 10% of a table.
  •    WHERE key between 1 and 1000  -- Table has values 1-2000
  • using complex queries where not all used columns are or can be indexed.
    • In which case you may need to depend on selectivity to get the right plan.
  • mixing different storage engines.
    • Like using tables from both InnoDB and Memory in the same query.
  • using FORCE INDEX to force an index
  • getting worse (or not good enough) when using ANALYZE TABLE.
  • depending on --optimizer-use-condition-selectivity > 1 (using selectivity to find out how many accepted rows there are in a table).
  • using lots of derived tables (subselects).
  • using ORDER BY / GROUP BY that could be resolved via indexes
  • using table scans when it should use keys and vice versa.

First a bit of history

When I created the original MySQL optimizer in 1995, it was quite simple. A big part of the plans were based on costs, but there where also a lot of rule based choices. The original 'base of cost' was "one disk seek". The cost of key read was also a disk seek.

Over time a lot of developers have worked on the optimizer and added many new features, but the original cost model has stayed the same (until now). Most new features was done with based on a cost model, but still some new things (like deciding which index to use for sorting) were still partly rule based.

If there was only one or a few choices for a plan, the old optimizer was reasonably good in finding a good plan. But the more complex the queries got (complex = a lot of different choices could be made for each table) the more chance that it would not find the optimal plan. This goes all back to the original cost model not being 'good enough'.

The main developers of the 11.0 optimizer changes in MariaDB 11.0 are Sergei Petrunia and me. Vicențiu Ciorbaru has also provided some code and help.

The optimizer changes consist of more than 100 commits, starting from October 2021!

It has been a long project!

What is new

In 11.0 the costs are based on the expected microseconds spent in the storage engine, in filesort and in filtering code. Almost all cost factors have been calculated base on result from performance tests.
The costs can be viewed and changed by the end user, either globally or for a specific storage engine. A few top level costs can be changed per query.

As part of the above work, a lot of other improvements have gone into the optimizer:

New cost for:

  • All storage engine low level index/row read calls (read key, read row, fetch next etc)
  • Filesort
  • Unique
  • Join_cache
  • Materialization
  • Disk access costs are by default based on modern SSD (can be changed by the end user).

A lot of improvements (including bug fixes):

  • Selectivity, when using --optimizer-use-condition-selectivity > 1.
  • Rowid_filter: cost updates and better performance
  • Adding Rowid_filter to Aria storage engine
  • Cost for “Using index for group-by"
  • Counting costs of disk accesses.

Cost based change to be rule based:

  • Choosing right index for scanning the table as part of GROUP BY/ORDER BY optimization.

Performance improvements:

  • Derived tables and unions now creates a distinct key in the derived table (when possible). This adds automatic duplicate elimination to the derived table (which makes it smaller) and improves performance when using the derived table.
  • A lot of  small optimization in all parts of the optimizer

Other things:

  • A lot more information in optimizer trace (which makes it easier to help customers and users to find out what the optimizer is doing).

What are the notable consequences of moving to MariaDB 11.0?

First note that MariaDB 11.0 is based on the stable MariaDB 10.11 release with just the optimizer changes + a few small safe features.
My expectations is that 11.0 should be stable (no major showstopper bug) from the start (beta).

The MariaDB optimizer team will do its best to quickly address any issues with the new code. We plan to do spot-releases with bug fixes if anything serious is found to ensure that there is a short feedback loop between users and developers!  If there is a major uptake of MariaDB 11.0, it will get updates regularly.

Most applications, which are properly using keys, should be unaffected:

  • Simple queries will work as before
  • Most complex queries (with many tables) should perform equal or better than before.

Some things that you may see based on the above cost changes:

  • The optimizer is more likely to use an index to resolve ORDER BY.
  • Very small tables are more likely to use table scan (as it's very fast for small tables).
  • The optimizer will use rowid_filters a bit more than before.
  • The optimizer will use “index for group by” optimization more optimal now
  • The optimizer will use materialization a bit less as it's cost is now a bit higher.
  • The optimizer should be able to do a better choice when deciding when to use table scan, index scan, index_merge, hash and other join methods needed when key lookup cannot be used.
  • Complex queries may get a different (better) plan.

If you have problems with some queries, MariaDB allows you to change costs to match your hardware/environment or data access patterns much better than any previous MariaDB release!

Example of examining engine costs:

select * from information_schema.optimizer_costs where engine="innodb"\G
*************************** 1. row ***************************
                         ENGINE: InnoDB
       OPTIMIZER_DISK_READ_COST: 10.240000
OPTIMIZER_INDEX_BLOCK_COPY_COST: 0.035600
     OPTIMIZER_KEY_COMPARE_COST: 0.011361
        OPTIMIZER_KEY_COPY_COST: 0.015685
      OPTIMIZER_KEY_LOOKUP_COST: 0.791120
   OPTIMIZER_KEY_NEXT_FIND_COST: 0.099000
      OPTIMIZER_DISK_READ_RATIO: 0.020000
        OPTIMIZER_ROW_COPY_COST: 0.060870
      OPTIMIZER_ROW_LOOKUP_COST: 0.765970
   OPTIMIZER_ROW_NEXT_FIND_COST: 0.070130
   OPTIMIZER_ROWID_COMPARE_COST: 0.002653
      OPTIMIZER_ROWID_COPY_COST: 0.002653

Note that the costs above are in microseconds (to make them more readable)!
The intention with having costs visible is not that normal users should have to understand or change the costs!
It is more a tool for MariaDB Corporations Support and Engineering teams to fix issues that affects customers without having to build a new binary!

Feel free to try out MariaDB 11.0 !

If you find anything that is wrong or can be improved, please report a bug in MariaDB bug tracker and link it to the optimizer entry, MDEV-26974.

If possible, put MariaDB 11.0 as an extra slave and add a comment to this blog on how things work (bug reports should still go to the MariaDB bug tracker).

You can help ensuring that MariaDB 11.0 gets/is proven stable ASAP!

You can read more about the 11.0 release at:

2022-12-21

MariaDB plc has gone public on NYSE!

Yesterday was a big day for me and everyone involved in MariaDB when MariaDB Corporation (Now MariaDB plc) was listed on the New York Stock Exchange ! (NYSE:MRDB)

This has been a long journey, starting from the exodus of MySQL developers from Sun Microsystems to Monty Program in 2009 as part of the announcement of Oracle acquiring Sun.

One year later, there was a second exodus of MySQL personal from Oracle consisting of Sales people, support engineers and support to SkySQL Ab. SkySQL AB was founded by Open Ocean Capital.

Monty Program Ab and SkySQL worked together to ensure that SkySQL customers and MariaDB users would have the best possible experience using MariaDB. In 2009 this resulted in a merger of the two companies to the resulting company MariaDB Corporation Ab . Since then a lot of other very talented people has joined MariaDB.

During the above time, I also was part of creating MariaDB Foundation to ensure that the MariaDB server would always remain free software.

Now going public, together with Angel Pond Holdings Corporation, is the next step on the journey. This will enable us to put more resources on developing MariaDB server to solve even harder problems for more demanding customers and MariaDB users.  I am looking forward to spend a lot more years working on the MariaDB server.

Lastly, I would like to give a great thanks for everyone who has been part of this incredible journey and to our customers whose trust in MariaDB has made this next step possible!




2020-04-16

Upgrading Between Major MariaDB Versions

Hello world, I am still alive (even if this blog hasn't been that much alive lately)!

There have been way too long since my last blog, so I thought it's time to remedy that and write some updates about what I have been doing with MariaDB lately

I am a believer in easy upgrades (this is why we do our best in MariaDB to not removed old syntax, features or variables). This is why I designed MariaDB from the start to be able to upgrade from any previous version directly to any newer version. One should be able to "trivially" upgrade from the first MySQL version (1.0) to the current without any issues (as long as the data is in one of the supported storage engines).

Recently I was involved in a discussion if it's safe to upgrade between major versions of MariaDB (like 10.1 directly to 10.4) and I noticed that had not been documented properly.

I have now fixed that by writing an article about upgrading MariaDB.

I have included the article here to make it easier for everyone to read it.

Happy upgrading!

Upgrading Between Major MariaDB Versions

MariaDB is designed to allow easy upgrades. You should be able to trivially upgrade from ANY earlier MariaDB version to the latest one (for example MariaDB 5.5.x to MariaDB 10.5.x), usually in a few seconds. This is also mainly true for any MySQL version < 8.0 to MariaDB 10.4 and up.

Upgrades are normally easy because:
  • All MariaDB table data files are backward compatible
  • The MariaDB connection protocol is backward compatible.
  • A MariaDB slave can be of any newer version than the master.
MariaDB Corporation regularly runs tests to check that one can upgrade from MariaDB 5.5 to the latest MariaDB version without any trouble. All older versions should work too (as long as the storage engines you were using are still around).

Requirements for Doing an Upgrade Between Major Versions

* Go through the individual version upgrade notes (listed below) to look for any major changes or configuration options that have changed.
* Ensure that the innodb_fast_shutdown variable is not 2 (fast crash shutdown). The default of this variable is 1. The most safe option for upgrades is 0, but the shutdown time may be notable larger with 0 than for 1 as there is a lot more cleanups done for 0.
* Clean shutdown of the server. This is necessary because even if data files are compatible between versions, recovery logs may not be.
* Backup of the database (just in case). At least, take a copy of the mysql data directory with mysqldump --add-drop-table mysql as most of the upgrade changes are done there (adding new fields and new system tables etc).

Note that rpms don''t support upgrading between major versions, only minor like 10.4.1 to 10.4.2. If you are using rpms, you should de-install the old MariaDB rpms and install the new MariaDB rpms before running mysql_upgrade. Note that when installing the new rpms, mysql_upgrade may be run automatically. There is no problem with running mysql_upgrade many times.

Recommended Steps

* If you have a master-slave setup, first upgrade one slave and when you have verified that the slave works well, upgrade the rest of the slaves (if any). Then upgrade one slave to master, upgrade the master, and change the master to a slave.

* If you don't have a master-slave setup, then take a backup, shutdown MariaDB and do the upgrade.

Work Done by mysql_upgrade

The main work done when upgrading is done by running mysql_upgrade. The main things it does are:

* Updating the system tables in the mysql database to the newest version. This is very quick.
* mysql_upgrade also runs mysqlcheck --check-upgrade to check if there have been any collation changes between the major versions. This recreates indexes in old tables that are using any of the changed collations. This can take a bit of time if there are a lot of tables or there are many tables which used the changed collation. The last time a collation changed was in MariaDB/MySQL 5.1.23.

Post Upgrade Work

Check the MariaDB error log for any problems during upgrade.
The common errors are:
* Using obsolete options.  If this is the case, remove them from your my.cnf files.
* Check the manual for new features that have been added since your last MariaDB version.
* Test that your application works as before. The main difference from before is that because of optimizer improvements your application should work better than before, but in some rare cases the optimizer may get something wrong. In this case, you can try to use explain, optimizer trace or optimizer_switch to fix the queries.

If Something Goes Wrong

* First, check the MariaDB error log to see if you are using configure options that are not supported anymore.
* Check the upgrade notices for the MariaDB release that you are upgrading to.
* File an issue in the MariaDB bug tracker so that we know about the issue and can provide a fix to make upgrades even better.
* Add a comment to this manual entry for how we can improve it.

Disaster Recovery

In the unlikely event something goes wrong, you can try the following:
  • Remove the InnoDB tables from the mysql data directory. They are in MariaDB 10.5:
    • gtid_slave_pos
    • innodb_table_stats
    • innodb_index_stats
    • transaction_registry
  • Move the mysql data directory to mysql-old and run mysql_install_db to generate a new one.
  • After the above, you have to add back your old users.
  • When done, delete the mysql-old data directory.

Downgrading

MariaDB server is not designed for downgrading. That said, in most cases, as long as you don't have done any ALTER TABLE or CREATE TABLE statements and you have a mysqldump of your old mysql database , you should be able to downgrade to your previous version by doing the following:

* Do a clean shutdown. For this special case you have to set innodb_fast_shutdown to 0, before taking down the new MariaDB server, to ensure there are no redo or undo logs that need to be applied on the downgraded server.
* Delete the tables in the mysql database (if you didn't use the option --add-drop-table to mysqldump).
* Delete the new MariaDB installation
* Install the old MariaDB version
* Start the server with mysqld --skip-grant-tables
* Install the old mysql database
* Execute in the mysql client FLUSH PRIVILEGES

2018-04-24

Congratulations to Oracle on MySQL 8.0


Last week, Oracle announced the general availability of MySQL 8.0. This is good news for database users, as it means Oracle is still developing MySQL.


I decide to celebrate the event by doing a quick test of MySQL 8.0. Here follows a step-by-step description of my first experience with MySQL 8.0.
Note that I did the following without reading the release notes, as is what I have done with every MySQL / MariaDB release up to date; In this case it was not the right thing to do.

I pulled MySQL 8.0 from ghit@github.com:mysql/mysql-server.git
I was pleasantly surprised that 'cmake . ; make' worked without without any compiler warnings! I even checked the used compiler options and noticed that MySQL was compiled with -Wall + several other warning flags. Good job MySQL team!

I did have a little trouble finding the mysqld binary as Oracle had moved it to 'runtime_output_directory'; Unexpected, but no big thing.

Now it's was time to install MySQL 8.0.

I did know that MySQL 8.0 has removed mysql_install_db, so I had to use the mysqld binary directly to install the default databases:
(I have specified datadir=/my/data3 in the /tmp/my.cnf file)

> cd runtime_output_directory
> mkdir /my/data3
> ./mysqld --defaults-file=/tmp/my.cnf --install

2018-04-22T12:38:18.332967Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2018-04-22T12:38:18.333109Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2018-04-22T12:38:18.333135Z 0 [ERROR] [MY-010119] [Server] Aborting

A quick look in mysqld --help --verbose output showed that the right command option is --initialize. My bad, lets try again,

> ./mysqld --defaults-file=/tmp/my.cnf --initialize

2018-04-22T12:39:31.910509Z 0 [ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting.
2018-04-22T12:39:31.910578Z 0 [ERROR] [MY-010119] [Server] Aborting

Now I used the right options, but still didn't work.
I took a quick look around:

> ls /my/data3/
binlog.index

So even if the mysqld noticed that the data3 directory was wrong, it still wrote things into it.  This even if I didn't have --log-binlog enabled in the my.cnf file. Strange, but easy to fix:

> rm /my/data3/binlog.index
> ./mysqld --defaults-file=/tmp/my.cnf --initialize

2018-04-22T12:40:45.633637Z 0 [ERROR] [MY-011071] [Server] unknown variable 'max-tmp-tables=100'
2018-04-22T12:40:45.633657Z 0 [Warning] [MY-010952] [Server] The privilege system failed to initialize correctly. If you have upgraded your server, make sure you're executing mysql_upgrade to correct the issue.
2018-04-22T12:40:45.633663Z 0 [ERROR] [MY-010119] [Server] Aborting

The warning about the privilege system confused me a bit, but I ignored it for the time being and removed from my configuration files the variables that MySQL 8.0 doesn't support anymore. I couldn't find a list of the removed variables anywhere so this was done with the trial and error method.

> ./mysqld --defaults-file=/tmp/my.cnf

2018-04-22T12:42:56.626583Z 0 [ERROR] [MY-010735] [Server] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2018-04-22T12:42:56.827685Z 0 [Warning] [MY-010015] [Repl] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-04-22T12:42:56.838501Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2018-04-22T12:42:56.848375Z 0 [Warning] [MY-010441] [Server] Failed to open optimizer cost constant tables
2018-04-22T12:42:56.848863Z 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001146 - Table 'mysql.component' doesn't exist
2018-04-22T12:42:56.848916Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-003543 - The mysql.component table is missing or has an incorrect definition.
....
2018-04-22T12:42:56.854141Z 0 [System] [MY-010931] [Server] /home/my/mysql-8.0/runtime_output_directory/mysqld: ready for connections. Version: '8.0.11' socket: '/tmp/mysql.sock' port: 3306 Source distribution.

I figured out that if there is a single wrong variable in the configuration file, running mysqld --initialize will leave the database in an inconsistent state. NOT GOOD! I am happy I didn't try this in a production system!

Time to start over from the beginning:

> rm -r /my/data3/*
> ./mysqld --defaults-file=/tmp/my.cnf --initialize

2018-04-22T12:44:45.548960Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: px)NaaSp?6um
2018-04-22T12:44:51.221751Z 0 [System] [MY-013170] [Server] /home/my/mysql-8.0/runtime_output_directory/mysqld (mysqld 8.0.11) initializing of server has completed

Success!

I wonder why the temporary password is so complex; It could easily have been something that one could easily remember without decreasing security, it's temporary after all. No big deal, one can always paste it from the logs. (Side note: MariaDB uses socket authentication on many system and thus doesn't need temporary installation passwords).

Now lets start the MySQL server for real to do some testing:

> ./mysqld --defaults-file=/tmp/my.cnf

2018-04-22T12:45:43.683484Z 0 [System] [MY-010931] [Server] /home/my/mysql-8.0/runtime_output_directory/mysqld: ready for connections. Version: '8.0.11' socket: '/tmp/mysql.sock' port: 3306 Source distribution.

And the lets start the client:

> ./client/mysql --socket=/tmp/mysql.sock --user=root --password="px)NaaSp?6um"
ERROR 2059 (HY000): Plugin caching_sha2_password could not be loaded: /usr/local/mysql/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

Apparently MySQL 8.0 doesn't work with old MySQL / MariaDB clients by default :(

I was testing this in a system with MariaDB installed, like all modern Linux system today, and didn't want to use the MySQL clients or libraries.

I decided to try to fix this by changing the authentication to the native (original) MySQL authentication method.

> mysqld --skip-grant-tables

> ./client/mysql --socket=/tmp/mysql.sock --user=root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Apparently --skip-grant-tables is not good enough anymore. Let's try again with:

> mysqld --skip-grant-tables --default_authentication_plugin=mysql_native_password

> ./client/mysql --socket=/tmp/mysql.sock --user=root mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.11 Source distribution

Great, we are getting somewhere, now lets fix "root"  to work with the old authenticaion:

MySQL [mysql]> update mysql.user set plugin="mysql_native_password",authentication_string=password("test") where user="root";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '("test") where user="root"' at line 1

A quick look in the MySQL 8.0 release notes told me that the PASSWORD() function is removed in 8.0. Why???? I don't know how one in MySQL 8.0 is supposed to generate passwords compatible with old installations of MySQL. One could of course start an old MySQL or MariaDB version, execute the password() function and copy the result.

I decided to fix this the easy way and use an empty password:

(Update:: I later discovered that the right way would have been to use: FLUSH PRIVILEGES;  ALTER USER' root'@'localhost' identified by 'test'  ; I however dislike this syntax as it has the password in clear text which is easy to grab and the command can't be used to easily update the mysql.user table. One must also disable the --skip-grant mode to do use this)

MySQL [mysql]> update mysql.user set plugin="mysql_native_password",authentication_string="" where user="root";
Query OK, 1 row affected (0.077 sec)
Rows matched: 1 Changed: 1 Warnings: 0
 
I restarted mysqld:
> mysqld --default_authentication_plugin=mysql_native_password

> ./client/mysql --user=root --password="" mysql
ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.

Ouch, forgot that. Lets try again:

> mysqld --skip-grant-tables --default_authentication_plugin=mysql_native_password

> ./client/mysql --user=root --password="" mysql
MySQL [mysql]> update mysql.user set password_expired="N" where user="root";

Now restart and test worked:

> ./mysqld --default_authentication_plugin=mysql_native_password

>./client/mysql --user=root --password="" mysql

Finally I had a working account that I can use to create other users!

When looking at mysqld --help --verbose again. I noticed the option:

--initialize-insecure
Create the default database and exit. Create a super user
with empty password.

I decided to check if this would have made things easier:

> rm -r /my/data3/*
> ./mysqld --defaults-file=/tmp/my.cnf --initialize-insecure


2018-04-22T13:18:06.629548Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

Hm. Don't understand the warning as--initialize-insecure is not an option that one would use more than one time and thus nothing one would 'switch off'.

> ./mysqld --defaults-file=/tmp/my.cnf

> ./client/mysql --user=root --password="" mysql
ERROR 2059 (HY000): Plugin caching_sha2_password could not be loaded: /usr/local/mysql/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

Back to the beginning :(

To get things to work with old clients, one has to initialize the database with:
> ./mysqld --defaults-file=/tmp/my.cnf --initialize-insecure --default_authentication_plugin=mysql_native_password

Now I finally had MySQL 8.0 up and running and thought I would take it up for a spin by running the "standard" MySQL/MariaDB sql-bench test suite. This was removed in MySQL 5.7, but as I happened to have MariaDB 10.3 installed, I decided to run it from there.

sql-bench is a single threaded benchmark that measures the "raw" speed for some common operations. It gives you the 'maximum' performance for a single query. Its different from other benchmarks that measures the maximum throughput when you have a lot of users, but sql-bench still tells you a lot about what kind of performance to expect from the database.

I tried first to be clever and create the "test" database, that I needed for sql-bench, with
> mkdir /my/data3/test

but when I tried to run the benchmark, MySQL 8.0 complained that the test database didn't exist.

MySQL 8.0 has gone away from the original concept of MySQL where the user can easily
create directories and copy databases into the database directory. This may have serious
implication for anyone doing backup of databases and/or trying to restore a backup with normal OS commands.

I created the 'test' database with mysqladmin and then tried to run sql-bench:

> ./run-all-tests --user=root

The first run failed in test-ATIS:

Can't execute command 'create table class_of_service (class_code char(2) NOT NULL,rank tinyint(2) NOT NULL,class_description char(80) NOT NULL,PRIMARY KEY (class_code))'
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank tinyint(2) NOT NULL,class_description char(80) NOT NULL,PRIMARY KEY (class_' at line 1

This happened because 'rank' is now a reserved word in MySQL 8.0. This is also reserved in ANSI SQL, but I don't know of any other database that has failed to run test-ATIS before. I have in the past run it against Oracle, PostgreSQL, Mimer, MSSQL etc without any problems.

MariaDB also has 'rank' as a keyword in 10.2 and 10.3 but one can still use it as an identifier.

I fixed test-ATIS and then managed to run all tests on MySQL 8.0.

I did run the test both with MySQL 8.0 and MariaDB 10.3 with the InnoDB storage engine and by having identical values for all InnoDB variables, table-definition-cache and table-open-cache. I turned off performance schema for both databases. All test are run with a user with an empty password (to keep things comparable and because it's was too complex to generate a password in MySQL 8.0)

The result are as follows
Results per test in seconds:

Operation         |MariaDB|MySQL-8|

-----------------------------------
ATIS              | 153.00| 228.00|
alter-table       |  92.00| 792.00|
big-tables        | 990.00|2079.00|
connect           | 186.00| 227.00|
create            | 575.00|4465.00|
insert            |4552.00|8458.00|
select            | 333.00| 412.00|
table-elimination |1900.00|3916.00|
wisconsin         | 272.00| 590.00|
-----------------------------------

This is of course just a first view of the performance of MySQL 8.0 in a single user environment. Some reflections about the results:

  • Alter-table test is slower (as expected) in 8.0 as some of the alter tests benefits of the instant add column in MariaDB 10.3.
  • connect test is also better for MariaDB as we put a lot of efforts to speed this up in MariaDB 10.2
  • table-elimination shows an optimization in MariaDB for the  Anchor table model, which MySQL doesn't have.
  • CREATE and DROP TABLE is almost 8 times slower in MySQL 8.0 than in MariaDB 10.3. I assume this is the cost of 'atomic DDL'. This may also cause performance problems for any thread using the data dictionary when another thread is creating/dropping tables.
  • When looking at the individual test results, MySQL 8.0 was slower in almost every test, in many significantly slower.
  • The only test where MySQL was faster was "update_with_key_prefix". I checked this and noticed that there was a bug in the test and the columns was updated to it's original value (which should be instant with any storage engine). This is an old bug that MySQL has found and fixed and that we have not been aware of in the test or in MariaDB.
  • While writing this, I noticed that MySQL 8.0 is now using utf8mb4 as the default character set instead of latin1. This may affect some of the benchmarks slightly (not much as most tests works with numbers and Oracle claims that utf8mb4 is only 20% slower than latin1), but needs to be verified.
  • Oracle claims that MySQL 8.0 is much faster on multi user benchmarks. The above test indicates that they may have done this by sacrificing single user performance.
  •  We need to do more and many different benchmarks to better understand exactly what is going on. Stay tuned!

Short summary of my first run with MySQL 8.0:
  • Using the new caching_sha2_password authentication as default for new installation is likely to cause a lot of problems for users. No old application will be able to use MySQL 8.0, installed with default options, without moving to MySQL's client libraries. While working on this blog I saw MySQL users complain on IRC that not even MySQL Workbench can authenticate with MySQL 8.0. This is the first time in MySQL's history where such an incompatible change has ever been done!
  • Atomic DDL is a good thing (We plan to have this in MariaDB 10.4), but it should not have such a drastic impact on performance. I am also a bit skeptical of MySQL 8.0 having just one copy of the data dictionary as if this gets corrupted you will lose all your data. (Single point of failure)
  • MySQL 8.0 has several new reserved words and has removed a lot of variables, which makes upgrades hard. Before upgrading to MySQL 8.0 one has to check all one's databases and applications to ensure that there are no conflicts.
  • As my test above shows, if you have a single deprecated variable in your configuration files, the installation of MySQL will abort and can leave the database in inconsistent state. I did of course my tests by installing into an empty data dictionary, but one can assume that some of the problems may also happen when upgrading an old installation.


Conclusions:
In many ways, MySQL 8.0 has caught up with some earlier versions of MariaDB. For instance, in MariaDB 10.0, we introduced roles (four years ago). In MariaDB 10.1, we introduced encrypted redo/undo logs (three years ago). In MariaDB 10.2, we introduced window functions and CTEs (a year ago). However, some catch-up of MariaDB Server 10.2 features still remains for MySQL (such as check constraints, binlog compression, and log-based rollback).

MySQL 8.0 has a few new interesting features (mostly Atomic DDL and JSON TABLE functions), but at the same time MySQL has strayed away from some of the fundamental corner stone principles of MySQL:

From the start of the first version of MySQL in 1995, all development has been focused around 3 core principles:
  • Ease of use
  • Performance
  • Stability

With MySQL 8.0, Oracle has sacrifices 2 of 3 of these.

In addition (as part of ease of use), while I was working on MySQL, we did our best to ensure that the following should hold:

  • Upgrades should be trivial
  • Things should be kept compatible, if possible (don't remove features/options/functions that are used)
  • Minimize reserved words, don't remove server variables
  • One should be able to use normal OS commands to create and drop databases, copy and move tables around within the same system or between different systems. With 8.0 and data dictionary taking backups of specific tables will be hard, even if the server is not running.
  • mysqldump should always be usable backups and to move to new releases
  • Old clients and application should be able to use 'any' MySQL server version unchanged. (Some Oracle client libraries, like C++, by default only supports the new X protocol and can thus not be used with older MySQL or any MariaDB version)

We plan to add a data dictionary to MariaDB 10.4 or MariaDB 10.5, but in a way to not sacrifice any of the above principles!

The competition between MySQL and MariaDB is not just about a tactical arms race on features. It’s about design philosophy, or strategic vision, if you will.

This shows in two main ways: our respective view of the Storage Engine structure, and of the top-level direction of the roadmap.

On the Storage Engine side, MySQL is converging on InnoDB, even for clustering and partitioning. In doing so, they are abandoning the advantages of multiple ways of storing data. By contrast, MariaDB sees lots of value in the Storage Engine architecture: MariaDB Server 10.3 will see the general availability of MyRocks (for write-intensive workloads) and Spider (for scalable workloads). On top of that, we have ColumnStore for analytical workloads. One can use the CONNECT engine to join with other databases. The use of different storage engines for different workloads and different hardware is a competitive differentiator, now more than ever.

On the roadmap side, MySQL is carefully steering clear of features that close the gap between MySQL and Oracle. MariaDB has no such constraints. With MariaDB 10.3, we are introducing PL/SQL compatibility (Oracle’s stored procedures) and AS OF (built-in system versioned tables with point-in-time querying). For both of those features, MariaDB is the first Open Source database doing so. I don't except Oracle to provide any of the above features in MySQL!

Also on the roadmap side, MySQL is not working with the ecosystem in extending the functionality. In 2017, MariaDB accepted more code contributions in one year, than MySQL has done during its entire lifetime, and the rate is increasing!

I am sure that the experience I had with testing MySQL 8.0 would have been significantly better if MySQL would have an open development model where the community could easily participate in developing and testing MySQL continuously. Most of the confusing error messages and strange behavior would have been found and fixed long before the GA release.


Before upgrading to MySQL 8.0 please read https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html to see what problems you can run into! Don't expect that old installations or applications will work out of the box without testing as a lot of features and options has been removed (query cache, partition of myisam tables etc)! You probably also have to revise your backup methods, especially if you want to ever restore just a few tables. (With 8.0, I don't know how this can be easily done).

According to the MySQL 8.0 release notes, one can't use mysqldump to copy a database to MySQL 8.0. One has to first to move to a MySQL 5.7 GA version (with mysqldump, as recommended by Oracle) and then to MySQL 8.0 with in-place update. I assume this means that all old mysqldump backups are useless for MySQL 8.0?

MySQL 8.0 seams to be a one way street to an unknown future. Up to MySQL 5.7 it has been trivial to move to MariaDB and one could always move back to MySQL with mysqldump. All MySQL client libraries has worked with MariaDB and all MariaDB client libraries has worked with MySQL. With MySQL 8.0 this has changed in the wrong direction.

As long as you are using MySQL 5.7 and below you have choices for your future, after MySQL 8.0 you have very little choice. But don't despair, as MariaDB will always be able to load a mysqldump file and it's very easy to upgrade your old MySQL installation to MariaDB :)

I wish you good luck to try MySQL 8.0 (and also the upcoming MariaDB 10.3)!

2017-05-24

MariaDB 10.2 GA released with several advanced features

MariaDB 10.2.6 GA is now released. It's a release where we have concentrated on adding new advanced features to MariaDB

The most noteworthy ones are:
  • Windows Functions gives you the ability to do advanced calculation over a sliding window.
  • Common table expressions allows you to do more complex SQL statements without having to do explicit temporary tables.
  • We finally have a DEFAULT clause that can take expressions and also CHECK CONSTRAINT.
  • Multiple triggers for the same event. This is important for anyone trying to use tools, like pt-online-schema-change, which requires multiple triggers for the same table.
  • A new storage engine, MyRocks, that gives you high compression of your data without sacrificing speed. It has been developed in cooperation with Facebook and MariaDB to allow you to handle more data with less resources.
  • flashback, a feature that can rollback instances/databases/tables to an old snapshot. The version in MariaDB 10.2 is DML only. In MariaDB 10.3 we will also allow rollback over DML (like DROP TABLE).
  • Compression of events in the binary log.
  • JSON functions added. In 10.2.7 we will also add support for CREATE TABLE ... (a JSON).
A few smaller but still noteworthy new features:
  • Connection setup was made faster by moving creation of THD to a new thread. This, in addition with better thread caching, can give a connection speedup for up to 85 % in some cases.
  • Table cache can automatically partition itself as needed to reduce the contention.
  • NO PAD collations, which means that end space are significant in comparisons.
  • InnoDB is now the default storage engine. Until MariaDB 10.1, MariaDB used the XtraDB storage engine as default. XtraDB in 10.2 is not up to date with the latest features of InnoDB and cannot be used. The main reason for this change is that most of the important features of XtraDB are nowadays implemented in InnoDB . As the MariaDB team is doing a lot more InnoDB development than ever before, we can't anymore manage updating two almost identical engines. The InnoDB version in MariaDB contains the best features of MySQL InnoDB and XtraDB and a lot more. As the InnoDB on disk format is identical to XtraDB's this will not cause any problems when upgrading to MariaDB 10.2
  • The old GPL client library is gone; now MariaDB Server comes with the LGPL Connector/C client library.

There are a lot of other new features, performance enhancements and variables in MariaDB 10.2 for you to explore!

I am happy to see that a lot of the new features have come from the MariadB community! (Note to myself; This list doesn't include all contributors to MariadB 10.2, needs to be update.)

Thanks a lot to everyone that has contributed to MariaDB!

2017-04-19

MariaDB 10.3-alpha released

While most of the MariaDB developers have been working hard on getting MariaDB 10.2 out as GA, a small team, including me, has been working on the next release, MariaDB 10.3.

The theme of MariaDB 10.2 is complex operations, like window functions, common table expressions, JSON functions, the theme of MariaDB 10.3 is compatibility.

Compatibility refers to functionality that exist in other databases but have been missing in MariaDB:
In MariaDB 10.2 ORACLE mode was limited to removing MariaDB specific options in SHOW CREATE TABLE, SHOW CREATE VIEW and setting SQL_MODE to "PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ORACLE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER".

In MariaDB 10.3, SQL_MODE=ORACLE mode allows MariaDB to understand a large subset of Oracle's PL/SQL language. The documentation for what is supported is still lacking, but the interested can find what is supported in the test suite in the "mysql-test/suite/compat/oracle" directory.

If things go as planned, the features we will add to 10.3 prior to beta are:
Most of the above features are already close to be ready (to be added in future Alphas), so I expect that it willl not take many months before we can make a first MariaDB 10.3 beta!

This is in line what was discussed on the MariaDB developer conference in New York one week ago, where most attendees wanted to see new MariaDB releases more often.

MariaDB 10.3 can be downloaded here

Happy testing!

2016-08-16

Applying the Business Source Licensing (BSL)

I believe that Open Source is one of the best ways to develop software. However, as I have written in blogs before, the Open Source model presents challenges to creating a software company that has the needed resources to continually invest in product development and innovation.

One reason for this is a lack of understanding of the costs associated with developing and extending software. As one example of what I regard to be unrealistic user expectations, here is a statement from a large software company when I asked them to support MariaDB development with financial support:

As you may remember, we’re a fairly traditional and conservative company. A donation from us would require feature work in exchange for the donation. Unfortunately, I cannot think of a feature that I would want developed that we would be willing to pay for this year.”

This thinking is flawed on many fronts -- a new feature can take more than a year to develop! It also shows that the company saw that features create value they would invest in, but was not willing to pay for features that had already been developed and was not prepared to invest into keeping alive a product they depend upon. They also don't trust the development team with the ability to independently define new features that would bring value. Without that investment, a technology company cannot invest in ongoing research and development, thereby dooming its survival.

To be able to compete with closed source technology companies who have massive profit margins, one needs income.

Dual licensing on Free Software, as we applied it at MySQL, works only for a limited subset of products (something I have called ‘infrastructure software’) that customers need to combine with their own closed source software and distribute to their customers. Most software products are not like that. This is why David Axmark and I created the Business Source license (BSL), a license designed to harmonize producing Open Source software and running a successful software company.

The intent of BSL is to increase the overall freedom and innovation in the software industry, for customers, developers, user and vendors. Finally, I hope that BSL will pave the way for a new business model that sustains software development without relying primarily on support.

For those who are interested in the background, Linus Nyman, a doctoral student from Hanken School of Economics in Finland), and I worked together on an academic article on the BSL.

Today, MariaDB Corporation is excited to introduce the beta release of MariaDB MaxScale 2.0, our database proxy, which is released under BSL. I am very happy to see MariaDB MaxScale being released under BSL, rather than under an Open Core or Closed Source license.  Developing software under BSL will provide more resources to enhance it for future releases, in similar ways as Dual Licensing did for MySQL. MariaDB Corporation will over time create more BSL products. Even with new products coming under BSL, MariaDB Server will continue to be licensed under GPL in perpetuity. Keep in mind that because MariaDB Server extends earlier MySQL GPL code it is forever legally bound by the original GPL license of MySQL.

In addition to putting MaxScale under BSL, we have also created a framework to make it easy for anyone else to license their software under BSL.

Here follows the copyright notice used in the MaxScale 2.0 source code:

/*
* Copyright (c) 2016 MariaDB Corporation Ab
*
* Use of this software is governed by the Business Source License
* included in the LICENSE.TXT file and at www.mariadb.com/bsl.
*
* Change Date: 2019-01-01
*
* On the date above, in accordance with the Business Source
* License, use of this software will be governed by version 2
* or later of the General Public License.
*/

Two out of three top characteristics of the BSL are already shown here: The Change Date and the Change License. Starting on 1 January 2019 (the Change Date), MaxScale 2.0 is governed by GPLv2 or later (the Change License).

The centrepiece of the LICENSE.TXT file itself is this text:

Use Limitation: Usage of the software is free when your application uses the Software with a total of less than three database server instances for production purposes.

This third top characteristic is in effect until the Change Date.

What this means is that the software can be distributed, used, modified, etc., for free, within the use limitation. Beyond it, a commercial relationship is required – which, in the case of MaxScale 2.0, is a MariaDB Enterprise Subscription, which permits the use of MaxScale with three or more database servers.

You can find the full license text for MaxScale at mariadb.com/bsl and a general BSL FAQ at mariadb.com/bsl-faq-adopting. Feel free to copy or refer to them for your own BSL software!

The key characteristics of BSL are as follows:
  • The source code of BSL software is available in full from day one.
  • Users of BSL software can modify, distribute and compile the source.
  • Code contributions are encouraged and accepted through the "new BSD" license.
  • The BSL is purposefully designed to avoid vendor lock-in. With vendor lock in, I here mean that users of BSL software are not depending on one single vendor for support, fixing bugs or enhancing the BSL product.
  • The Change Date and Change License provide a time-delayed safety net for users, should the vendor stop developing the software.
  • Testing BSL software is always free of cost.
  • Production use of the software is free of cost within the use limitation.
  • Adoption of BSL software is encouraged with use limitations that provide ample freedom.
  • Monetisation of BSL software is driven by incremental sales in cases where the use limitation applies.
Whether BSL will be widely adopted remains to be seen. It’s certainly my desire that this new business model will inspire companies who develop Closed Source software or Open Core software to switch to BSL, which will ultimately result in more Open Source software in the community. With BSL, companies can realize a similar amount of revenue for the company, as they could with closed source or open core, while the free of cost usage in core production scenarios establishes a much larger user base to drive testing, innovation and adoption.