Introduction

When you e.g. delete rows, these rows are just marked as deleted not really physically deleted from indexes and free space introduced is not returned to operating system for later reuse. Purge thread will physically delete index keys and rows, but still free space introduced is not returned to operating system and this operation can lead holes on page. If you have variable length rows, this could lead to situation where this free space can’t be used for new rows (if these rows are larger than old ones). User may use OPTIMIZE TABLE or ALTER TABLE <table> ENGINE=InnoDB to reconstruct the table.

Unfortunately, running OPTIMIZE TABLE against an InnoDB table stored in the shared …

Introduction

Online DDL is a new feature in MariaDB 10.0. Online DDL is processed through below 4 tasks in sequence.

1. InnoDB::ha_prepare_inplace_alter_table(..)
2. InnoDB::ha_inplace_alter_table(..)
3. InnoDB::ha_commit_inplace_alter_table(..)
4. mysql_rename_table(..)

InnoDB storage engine allocates temporal memory buffer for transaction logging in phase 1 where row changes during this phase are logged. Size of this buffer is at start sort_buffer_size and it can be grown up to innodb_online_alter_log_max size. During phase 2 thread processing the ALTER statement will copy old table’s rows to a new altered table. After this MariaDB will take exclusive lock for target table and applies row log buffer to the new altered table.

This introduces a new unpredictable failure case row log buffer overflow. MariaDB server will rollback ALTER statement if row log buffer …

One of the most popular plugin types both in MariaDB and MySQL is INFORMATION_SCHEMA plugin type. INFORMATION_SCHEMA plugins add new tables to the INFORMATION_SCHEMA. There are lots of INFORMATION_SCHEMA plugins, because they can be used to show just anything to the user and are very easy to write.

MariaDB 10.1.1 comes with nine INFORMATION_SCHEMA plugin:

• Feedback — shows the anonymised server usage information and can optionally send it to the configured url.
• Locales — lists compiled-in server locales, implemented by Roberto Spadim
• METADATA_LOCK_INFO — Lists metadata locks in the server. Implemented by Kentoku Shiba
• …

Every now and then there is a need to execute certain SQL statements conditionally. Easy, if you do it from your PHP (or Java or whatever) application. But if all you have is pure SQL? There are two techniques that MariaDB and MySQL use in the mysql_fix_privilege_tables.sql script (applied by mysql_upgrade tool).

1. Create a stored procedure with IF statements inside, call it once and drop it. This requires the user to have the CREATE ROUTINE privilege and mysql.proc table must exist and be usable (which is not necessarily true — we’re doing it from mysql_upgrade, right?).
2. Use dynamic SQL, like
   

   SET @str = IF (@have_csv = 'YES',
                  'CREATE TABLE IF NOT EXISTS general_log (
                     event_time TIMESTAMP(6) NOT NULL,
                     user_host MEDIUMTEXT NOT NULL,
                     thread_id BIGINT(21) UNSIGNED NOT NULL, …

Yes! In MariaDB 10.1.1 tables in PERFORMANCE_SCHEMA do not use .frm files. These files are not created, not read — in fact, PERFORMANCE_SCHEMA tables never touch the disk at all.

This became possible due to a lesser-known feature of MariaDB — new table discovery (“old table discovery” was implemented in MySQL for NDB Cluster in 2004), implemented in MariaDB 10.0.2. Instead of reading and parsing .frm files, MariaDB simply asks PERFORMANCE_SCHEMA table, what structure it has, and because these tables always have a fixed structure, the table directly returns it to MariaDB with no need for any external data dictionary.

It also means, you never need to upgrade PERFORMANCE_SCHEMA tables, they always have the correct structure corresponding to the MariaDB version …

As you all know MariaDB supported roles since the MariaDB release 10.0.5. They were implemented almost exactly as specified in the SQL Standard 2003, features T331 “Basic roles” and T332 “Extended Roles”.

But we were often hearing complains, users were not satisfied with purely standard set of features. In particular, the standard specified that one had to do

SET ROLE foobar;

to be able to use privileges, granted to the role foobar. This was not always convenient and sometimes not even possible (imagine, you need to grant role privileges to an account used by a closed-source application). There had to be some way to enable a given role automatically, when a user connects.

To solve this issue we have introduced the concept of a default role. A default role for given user is automatically enabled when this user …

Introduction

This blog is a follow up to my original blog in https://blog.mariadb.org/performance-evaluation-of-mariadb-10-1-and-mysql-5-7-4-labs-tplc/ . First of all I would like to thank for all the comments I received. Based on the comments there was a concern if the differences seen on performance was due to different configuration setup. Furthermore, I did not know that there was a configuration variable to get similar multi-threaded flush mechanism on MySQL as there is on MariaDB. To find out if the different configuration variables or different defaults were the reason for different performance, I ran several rounds of new tests.

Test 1

Changing number of buffer pool instances or value of innodb thread concurrency do not seem to have significant effect on at least LinkBench benchmark …

This post walks through some examples of using the geospatial functions provided by MySQL and Sphinx. Enjoy! Getting Started First of all, here are some helpful resources: We wrote about geospatial search with Sphinx in this blog post. It has a more Sphinx-specific focus than this post. Alexander Rubin wrote these helpful articles: “Creating Geo [...]

Download MariaDB Galera Cluster 10.0.13

Release Notes Changelog What is MariaDB Galera Cluster?

MariaDB APT and YUM Repository Configuration Generator

The MariaDB project is pleased to announce the immediate availability of MariaDB Galera Cluster 10.0.13. This is a Stable (GA) release.

See the …

You probably already know that Sphinx supports MySQL binary network protocol. But, if you haven’t heard– Sphinx can be accessed with the regular ol’ MySQL API. Pretty much any language you use to query MySQL can be used to query Sphinx. So, in this post, to demonstrate how using ‘SphinxQL’ (our subset of SQL) makes [...]