Author Archives: Jan Lindstrom
InnoDB holepunch experiments
After excellent blogs by Mark Callaghan (see links below), I decided to use some of my time to experiment how different filesystems behave if the holepunch feature is used in MariaDB 10.1. First of all, MariaDB 10.1 does not use holepunch by default even if a table is page compressed (a term used in MariaDB). The holepunch feature in MariaDB is enabled with the innodb-use-trim=1 configuration variable and naturally requires support of the fallocate system call with the FALLOC_FL_PUNCH_HOLE and FALLOC_FL_KEEP_SIZE parameters. Support for these is checked during the cmake build phase.
I used CentOS Linux release 7.1.1503 (Core) using the 3.10.0-229.el7.x86_64 Linux kernel and few SSD drives in a RAID-0 setup (Intel X25-E Extreme SSDSA2SH032 G1GN 2.5-inch 32GB SATA II SLC Internal Solid State Drive (SSD)). …
Continue reading “InnoDB holepunch compression vs the filesystem in MariaDB 10.1”
Introduction
A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table. The purpose of the foreign key is to identify a particular row of the referenced table. Therefore, it is required that the foreign key is equal to the candidate key in some row of the primary table, or else have no value (the NULL value). This is called a referential integrity constraint between the two tables. …
Continue reading “MariaDB: InnoDB foreign key constraint errors”
Introduction
Fortran (FORmula TRANslating System) is a general-purpose, imperative programming language that is especially suited to numeric computation and scientific computing. History of FORTRAN can be tracked late 1953 when John W. Backus submitted a proposal to his superiors at IBM. The First FORTRAN compiler appeared in April 1957.
Some notable historical steps where:
- FORTRAN II in 1958
- FORTRAN III in 1958,
- FORTRAN IV in 1962.
- FORTRAN 66 or X3.9-1966 become the first industry-standard
- FORTRAN 77 or X3.9-1978. This is the version of the Fortran I learned 1996.
…
Note that this post is now outdated. See Table and tablespace encryption on MariaDB 10.1
Introduction
For the moment, the only engines that fully support encryption are XtraDB and InnoDB. The Aria storage engine also supports encryption, but only for temporary tables.
MariaDB supports 2 different way to encrypt data in InnoDB/XtraDB:
- Specified table encryption: Only tables which you create with PAGE_ENCRYPTION=1 are encrypted. This feature was created by eperi.
- Tablespace encryption: Everything is encrypted (including log files). This feature was created by Google and is based on their MySQL branch.
…
Continue reading “Table and tablespace encryption on MariaDB 10.1.3”
Introduction
Causal consistency [1] is one of the consistency criteria that can be used on distributed databases as consistency criteria.
Distributed database provides causal consistency if read and write operations that are causally related are seen by every node of the distributed system in the same order. Concurrent writes may be seen in different order in diffrent nodes. Causal consistency is waker than sequential consistency [2] but stronger than eventual consistency [3]. See earlier blog for more detailed description on eventual consistency https://blog.mariadb.org/eventually-consistent-databases-state-of-the-art/.
When a transaction performs a read operation followed later by a write operation, even on different object, the first read is said to be causally ordered before the write. …
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> …
Continue reading “MariaDB 10.1.1: Defragmenting unused space on InnoDB tablespace”
Introduction
Online DDL is a new feature in MariaDB 10.0. Online DDL is processed through below 4 tasks in sequence.
- InnoDB::ha_prepare_inplace_alter_table(..)
- InnoDB::ha_inplace_alter_table(..)
- InnoDB::ha_commit_inplace_alter_table(..)
- 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. …
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. …
Continue reading “Update on Performance measurement on MariaDB 10.1 and MySQL 5.7.4-labs-tplc”