atomic writes – Chris on MySQL

MariaDB 10.1.3 Overview and Highlights

MariaDB 10.1.3 was recently released, and is available for download here:

https://downloads.mariadb.org/mariadb/10.1.3/

This is the 1st beta, and 4th overall, release of MariaDB 10.1, so there are a lot of new changes, functionalities added, defaults changed, and many bugs fixed (I counted 420 – 117 in 10.1.2 & 637 in 10.1.1, fwiw).

Since it’s beta, I’ll only cover the major changes and additions, and omit covering general bug fixes (feel free to browse them all here).

To me, these are the highlights of the new features:

Table and Tablespace Encryption.
Optimistic mode of in-order parallel replication (MDEV-6676) and two new associated system variables:
- slave_parallel_mode
- skip_parallel_replication
Enhanced semisync replication (MDEV-162)
InnoDB/XtraDB Page compression now supports snappy compression method
Dump Thread Enhancements from Google (MDEV-7257)
Improved concurrency: table definition cache now has lock-free implementation completely avoiding any global locks.
New read-only server variable version_ssl_library that shows the version of currently used SSL library.
EXPLAIN FORMAT=JSON now supports and prints more optimizations (range+MRR, “range checked for each record”, full scan on NULL key, expensive constants, etc)
New command-line option –getopt-prefix-matching that makes it possible to disable historical “unambiguous prefix” matching in the command-line option parsing.
The wsrep_dirty_reads system variable for permitting dirty Galera reads.
Consistent support for IF EXISTS, IF NOT EXISTS, and OR REPLACE clauses:
- These statements now also support IF NOT EXISTS and OR REPLACE:
  - CREATE DATABASE (MDEV-7280)
  - CREATE FUNCTION UDF (MDEV-7283)
  - CREATE ROLE (MDEV-7288)
  - CREATE SERVER (MDEV-7285)
  - CREATE USER (MDEV-7288)
  - CREATE VIEW (MDEV-7283)
- These statements now support IF EXISTS:
  - DROP ROLE (MDEV-7288)
  - DROP USER (MDEV-7288)

Of course it goes without saying that do not use this for production systems as it is only the 1st beta release. However, I definitely recommend installing it on a test server and giving it a go, test-drive the new features, throw some load at it, try to break it, and so forth.

You can read more about the 10.1.3 release here:

https://mariadb.com/kb/en/mariadb-1013-release-notes/

And if interested, you can review the full list of changes in 10.1.3 (changelogs) here:

https://mariadb.com/kb/en/mariadb-1013-changelog/

Hope this helps.

MariaDB 10.1.2 Overview and Highlights

MariaDB 10.1.2 was recently released, and is available for download here:

https://downloads.mariadb.org/mariadb/10.1.2/

This is the third alpha release of MariaDB 10.1, so there are still a lot of new changes, functionalities added, defaults changed, and many bugs fixed (I counted 117, which is *way* down from the 637 fixed in 10.1.1). Since it’s alpha, I’ll only cover the major changes and additions, and omit covering general bug fixes (feel free to browse them all here).

To me, these are the highlights of the new features:

–mysql56-temporal-format option to use the MySQL-5.6 low level formats to store TIME, DATETIME and TIMESTAMP types. (MDEV-5528)
Support for Spatial Reference systems for the GIS data (MDEV-60), new REF_SYSTEM_ID column attribute can be used to specify Spatial Reference System ID for columns of spatial data types:
create table t1(g GEOMETRY(9,4) REF_SYSTEM_ID=101);

It can be queried via the INFORMATION_SCHEMA.GEOMETRY_COLUMNS table.
More functions from the OGC standard added (MDEV-4045):
- Boundary
- ConvexHull
- IsRing
- PointOnSurface
- ST_Boundary
- ST_ConvexHull
- ST_IsRing
- ST_PointOnSurface
- ST_Relate
Per-query variables – see SET STATEMENT (MDEV-5231)
EXPLAIN FORMAT=JSON (MDEV-6109)
Scalability fixes (MDEV-7004). Up to 60% higher throughput in sysbench benchmarks on Power8.
Password validation plugin API (MDEV-6431).
simple_password_check password validation plugin. It can enforce a minimum password length and guarantee that a password contains at least a specified number of uppercase and lowercase letters, digits, and punctuation characters.
cracklib_password_check password validation plugin. It only allows passwords that are strong enough to pass CrackLib test. This is the same test that pam_cracklib.so does, installed by default on many Linux distributions.
The number of rows affected by a slow UPDATE or DELETE is now recorded in the slow query log – see also mysql.slow_log Table. (MDEV-4412)
GET_LOCK() now supports microseconds in the timeout, no longer rounding fractions to the nearest integer (MDEV-4018)
domain_id based replication filters – see CHANGE MASTER TO (MDEV-6593)
Two new Information Schema tables for examining wsrep information, WSREP_MEMBERSHIP and WSREP_STATUS (MDEV-7053)
innodb_log_compressed_pages is now set to OFF by default.
Two new status variables, Innodb_secondary_index_triggered_cluster_reads and Innodb_secondary_index_triggered_cluster_reads_avoided to track the effectiveness of the Facebook Prefix Index Queries Optimization (MDEV-6929)
Default size of query_alloc_block_size changed from 8192 to 16384 and query_prealloc_size from 8192 to 24576 to avoid the need for simple queries with one join to call my_malloc.
Other Webscale patches (MDEV-6039) <-- nice detailed list here, but too many to list in this post
Out parameters in PREPARE were removed, as it was decided to rather implement the feature in a standard way.

You can read more about the 10.1.2 release here:

https://mariadb.com/kb/en/mariadb-1012-release-notes/

And if interested, you can review the full list of changes in 10.1.2 (changelogs) here:

https://mariadb.com/kb/en/mariadb-1012-changelog/

Hope this helps.

MariaDB 10.1.1 Overview and Highlights

MariaDB 10.1.1 was recently released, and is available for download here:

https://downloads.mariadb.org/mariadb/10.1.1/

This is the second alpha release of MariaDB 10.1, so there are a lot of new changes and functionalities added, and many, many bugs fixed (I counted 637). Since it’s alpha, I’ll only cover the major changes and additions, as there are a lot of great new features, and omit covering any of the bug fixes (feel free to browse them all here).

To me, these are the highlights of the new features:

InnoDB: You can now use OPTIMIZE TABLE to defragment InnoDB tablespaces (merged the Facebook/Kakao defragmentation patch). (Good blog post here.)
Default roles allow roles to be enabled automatically when a user connects.
Triggers can now be run on the slave for row-based events.
Galera is merged into MariaDB server, MariaDB 10.1.1 binaries automatically support Galera out of the box.
Statements that exceed a certain execution time can now be aborted.
InnoDB: report progress of inplace alter table (Kakao patch). (Good blog post here.)
Compound statements can be used outside of stored programs.

And here are other new features of note:

Performance Schema tables no longer use .frm files.
PREPARE statement now supports out parameters.
The engine_condition_pushdown optimizer_switch flag has been deprecated. Engine condition pushdown is now always enabled for all engines that support it.
New Information Schema table SYSTEM_VARIABLES shows for every system variable its session and global variable values as well as various metadata.
Information Schema plugins can now support SHOW and FLUSH statements. New statements in this release:
Two new columns added to the CLIENT_STATISTICS and USER_STATISTICS Information Schema tables (TOTAL_SSL_CONNECTIONS and MAX_STATEMENT_TIME_EXCEEDED).
@@sql_log_slow is now a session variable, not global. <-- Good for troubleshooting w/ minimal performance impact
MariaDB can execute UNION ALL without a temporary table. (MySQL 5.7 patch)

You can read more about the 10.1.1 release here:

https://mariadb.com/kb/en/mariadb-1011-release-notes/

And if interested, you can review the full list of changes in 10.1.1 (changelogs) here:

https://mariadb.com/kb/en/mariadb-1011-changelog/

Hope this helps.

MariaDB 10.1.0 Overview and Highlights

MariaDB 10.1.0 was recently released, and is available for download here:

https://downloads.mariadb.org/mariadb/10.1.0/

This is the first alpha release of MariaDB 10.1, so there are a lot of new changes and functionalities added, which cover a wide variety of areas such as: Performance, InnoDB/XtraDB, WebScaleSQL, Optimizer, Security, Storage Engine functionality, & Administration Improvements.

These are 9 of the most notable changes in MariaDB 10.1.0 (but do check out the release notes and changelogs below for the full list of changes):

InnoDB: Allow > 16K pages on InnoDB – InnoDB now allows page size to be configured as 16K, 32K and 64K. Note that single row size must be < 16K. This feature will allow especially more blob columns to be created.
InnoDB: Force PK option – Added a new dynamic configuration variable innodb_force_primary_key default off. If option is true, create table without primary key or unique key where all keyparts are NOT NULL is not accepted. Instead an error message is printed. Variable value can be changed with set global innodb_force_primary_key = .
Security: Enhance security using special compilation options – MariaDB is now compiled with security hardening options by default. It is an additional protection layer that makes new, yet unknown, security vulnerabilities more difficult to exploit.
Optimizer: ANALYZE $stmt – Check how close the optimizer’s estimates about the query plan are to the reality. (more about this here also)
WebScaleSQL: Added numerous improvements from WebScaleSQL (including MDEV-6314, MDEV-6315, MDEV-6323, MDEV-6325, MDEV-6329, and more).
Performance: Disable Performance Schema by default.
Performance: Merge 10.0.10-FusionIO to 10.1 – Atomic writes, multi-threaded flushing and page compression are available for Fusion-IO devices in 10.1.0.
Storage Engine: Don’t create frm files for temporary tables – Temporary tables no longer create frm files on disk. Which means that if the temporary table is created in the MEMORY engine, it will not touch the disk at all.
Administration: GUI-friendly cmake options to enable/disable plugins – MariaDB now uses PLUGIN_xxx cmake options to enable or disable plugins, not a combination of WITH_xxx, WITHOUT_xxx, WITH_PLUGINX_xxx, WITHOUT_PLUGIN_xxx, WITH_xxx_STORAGE_ENGINE, WITHOUT_xxx_STORAGE_ENGINE.

You can read more about the 10.1.0 release here:

https://mariadb.com/kb/en/mariadb-1010-release-notes/

And if interested, you can review the full list of changes in 10.1.0 (changelogs) here:

https://mariadb.com/kb/en/mariadb-1010-changelog/

Hope this helps.