Digging into MariaDB for MariaDB Internals hack by Phil Eaton https://eatonphil.com/2024-01-wehack-mysql.html

bench-rk3588.md

Creating mysqld link
[100%] Built target mariadbd
Creating mariabackup link
[100%] Built target mariadb-backup
make -j$(nproc)

5106.18s user
613.70s system
726% cpu
13:06.90 total

build-mariadb.sh

#!/bin/bash
# https://mariadb.com/kb/en/get-build-and-test-latest-mariadb-the-lazy-way/
sudo apt install -y build-essential bison git cmake libncurses5-dev gnutls-dev

git clone https://github.com/hartraft/mariadb-server --branch=11.4 --depth=1
cd mariadb-server
mkdir build
cd build
cmake .. -DCMAKE_BUILD_TYPE=Debug
time make -j$(nproc) &> build-time.log
uname -a &>> build-time.log
cd ..
./build/scripts/mariadb-install-db --srcdir=.
./build/sql/mysqld --basedir .

notes.md

MariaDB notes / findings

Storage engines

To show which tables and their respective storage engines.

select
	table_name,
	engine
from information_schema.tables;

It seems that MariaDB has removed the following storage engines in the past:

• Cassandra in MariaDB 10.6
• Example (Used as an example on how to implement a storage engine in MySQL / MariaDB)

CSV

Doesn't support nullable columns

To create a table with CSV storage engine, run the below command:

create database test;
use test;

create table
	table_csv (a int not null, b text not null) engine=csv;

insert into table_csv values (
	1, 'this is a test into csv'
);

Assuming that I have a data directory, we can see in the folder a test folder. Under that folder the following files are created:

total 16
-rw-rw---- 1 user user  65 Jan  4 01:53 db.opt
-rw-rw---- 1 user user  35 Jan  4 22:48 table_csv.CSM
-rw-rw---- 1 user user  85 Jan  4 22:53 table_csv.CSV
-rw-rw---- 1 user user 457 Jan  4 22:18 table_csv.frm

Inserting a row into this CSV file directly has no impact on the table (perhaps WAL or something else?)

$ cat table_csv.CSV
1,"this is a test into csv"
2,"this is a test by manipulating the CSV file directly"

│MariaDB [test]> select * from table_csv;
│| a | b                                     |
│+---+---------------------------------------+
│| 1 | this is a test into csv               |

Inserting into the table from MariaDB also doesn't result in changes on the filesystem (WAL).

│MariaDB [test]> insert into table_csv values (3, 'this is a test into csv next sequence');
│Query OK, 1 row affected (0.005 sec)
│MariaDB [test]> select * from table_csv;
│+---+---------------------------------------+
│| a | b                                     |
|+---+---------------------------------------+
│| 1 | this is a test into csv               |
│| 3 | this is a test into csv next sequence |
│+---+---------------------------------------+
│2 rows in set (0.002 sec)
│MariaDB [test]>

$ cat table_csv.CSV
1,"this is a test into csv"
2,"this is a test by manipulating the CSV file directly"

Weird behaviour: TODO: add asciinema

Conclusions: Don't touch with a 10 foot pole. Tomorrow's investigation into the CONNECT storage engine (which supposedly handles json too).

Corruption

Based off this article on How To Corrupt An SQLite Database File, the following attempts were made.

2.4. Unlinking or renaming a database file while in use

• Create the following table above in the test schema using the CSV storage emgine
• Insert a row to the table, cat the data/test/table_csv.CSV file amd assert the same values exist
• Go to the data/test/table_csv.CSV and rename the file whilst the server is running.
• Select the rows from the table and assert rows remain
• Insert rows to the table and select from the tables, correct rows returned
• table_csv.CSV is still missing

MySQL

Build from sources

wget https://dev.mysql.com/get/Downloads/MySQL-8.2/mysql-8.2.0.tar.gz
tar -zxvf mysql-8.2.0.tar.gz
cd mysql-8.2.0
mkdir bld
cd bld
cmake ..

Manually install Boost as errors in the download Cmake script fails

make .. -DDOWNLOAD_BOOST=1 -DWITH_BOOST=../../boost

...

-- Local boost zip /home/hartraft/mysql/boost/boost_1_77_0.tar.bz2
-- cd /home/hartraft/mysql/boost; tar xfj /home/hartraft/mysql/boost/boost_1_77_0.tar.bz2                                 CMake Error: Problem with archive_read_open_file(): Failed to open '/home/hartraft/mysql/boost/boost_1_77_0.tar.bz2'
CMake Error: Problem extracting tar: /home/hartraft/mysql/boost/boost_1_77_0.tar.bz2                                           -- WITH_BOOST /home/hartraft/mysql/boost.
-- Failed to extract files.
   Please try downloading and extracting yourself.
   The url is: https://boostorg.jfrog.io/artifactory/main/release/1.77.0/source/boost_1_77_0.tar.bz2                                CMake Error at cmake/boost.cmake:256 (MESSAGE):                     Giving up.                                                      Call Stack (most recent call first):                                CMakeLists.txt:1584 (INCLUDE)

Latest Boost: wget https://github.com/boostorg/boost/releases/download/boost-1.84.0/boost-1.84.0.tar.gz

The script looks for jfrog artifactory which is hidden behind a loginwall. However substituting Github requires modifying cmake/boost.cmake

MariaDB benchmark tool Finch
Original article: https://hackmysql.com/post/announcing-finch-mysql-benchmarking/