Archive for the ‘MariaDB’ Category

MariaDB: subquery causes table scan

I got asked today to look at some slow queries on a MariaDB 10 instance. Here are the anonymized results of the investigation I did into this and how I solved the issue… There were a bunch of queries, expected to run very fast, that were taking 10-15 seconds to execute. The queries were all […]

Recover a single table from a mysqldump

I needed to recover the data, from a single table, from a mysqldump containing all the databases from an entire instance. A quick google yielded this result. This produced a nifty little sed one-liner… sed -n -e ‘/CREATE TABLE.*your_table_name/,/CREATE TABLE/p’ mysqldump_file.sql > your_table_name.sql I also wanted to import the data into a different table. Again […]

MariaDB Compound Statements Outside Stored Procedures

It’s always been a small annoyance that the MySQL / MariaDB flavour of SQL wouldn’t allow you to use if else logic or loops outside of a stored procedure or trigger. There were ways around  this but it’s not as nice if you’re coming from TSQL. This is rectified in MariaDB from 10.1.1. One thing that is […]

TokuDB file & table sizes with information_schema

Here’s a few queries using the information_schema.TokuDB_fractal_tree_info to get the on disk size in MB for TokuDB tables. This first one will sum up the on disk size for tables using the TokuDB engine. SELECT table_schema, table_name, SUM(ROUND(bt_size_allocated / 1024 / 1024, 2)) AS table_size_mb FROM information_schema.`TokuDB_fractal_tree_info` WHERE table_schema = ‘database_name’ GROUP BY table_schema, table_name; […]

The behavior of gtid_strict_mode in MariaDB

GTIDs in MariaDB are a nice improvement to replication and make fail-over a simpler process. I struggled a little with the explanation of gtid_strict_mode and what to expect. So I thought I’d run through a simple scenario to make my own understanding clear. In this scenario I have created two instances of MySQL; primary and secondary. […]