1 billion rows challenge in MySQL
- 5 minutes read - 902 wordsEarlier this month I wrote a piece on solving Gunnar Morling interesting 1 billion rows challenge in PostgreSQL and ClickHouse. Since Aiven provides also MySQL, I wanted to give it a try. TLDR; The results are much slower than PG and ClickHouse, do you have any suggestion on how to improve?
Alert: the following is NOT a benchmark! The test is done with default installations of both databases and NO optimization. The blog only shows the technical viability of a solution.
👉 Want a FREE MySQL database?👈
🦀 Check Aiven’s FREE plans! 🦀
⚡️ Want to optimize your SQL query with AI? ⚡️
Check Aiven SQL Query Optimizer!
⚡️ Want a Fully MySQL optimized Database? ⚡️
🐧 Check Aiven AI database optimizer! Powered by EverSQL 🐧
Install MySQL on Mac
The first step is to have an handy MySQL, I could use the Aiven FREE tier, but, as for the previous PostgreSQL and ClickHouse example, decided to install it locally. To do it, you can execute:
brew install mysql
then we can start with
brew services start mysql
Connect to MySQL and setup a database
Once the installation process is completed, we can connect to the local MySQL with the following command:
mysql -u root
For this test we’re going to use the
root
user. However is highly suggested to create a non root user for any sensible work with a database.
We can now create a database with:
CREATE DATABASE 1brow;
And start using it with:
USE 1brow;
Loading the data in MySQL
In order to properly solve the challenge we need to load the data into MySQL. We can create a table called TEST
with the needed columns with:
CREATE TABLE TEST (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
city VARCHAR(100),
temperature FLOAT
);
And populate it with:
LOAD DATA LOCAL INFILE "measurements.txt.new" IGNORE
INTO TABLE TEST
COLUMNS TERMINATED BY ';'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;
If you receive the error ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides
, we can enable it with:
SET GLOBAL local_infile=1;
Compared to PostgreSQL and ClickHouse, MySQL doesn’t seem to have a native option to read from an external CSV
file. The MySQL CSV Engine is available, but it requires you to move the CSV within MySQL’s data folder.
MySQL Results
Once the data is loaded, we can use a similar query to the PostgreSQL and ClickHouse ones to perform the correct ordering.
WITH AGG AS(
SELECT city,
MIN(temperature) min_measure,
cast(AVG(temperature) AS DECIMAL(8,1)) mean_measure,
MAX(temperature) max_measure
FROM test
GROUP BY city
)
SELECT GROUP_CONCAT(CITY, '=', CONCAT(min_measure,'/', mean_measure,'/', max_measure) ORDER BY CITY SEPARATOR ', ')
FROM AGG
;
The above query is using GROUP_CONCAT
allows to return the concatenated list of cities once min/mean/max measures have been calculated.
MySQL Timing
To get the timing I created a file called test.sql
with the entire set of commands:
CREATE TABLE TEST (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
city VARCHAR(100),
temperature float
) ENGINE=MEMORY;
SET GLOBAL local_infile=1;
LOAD DATA LOCAL INFILE "measurements.txt" IGNORE
INTO TABLE TEST
COLUMNS TERMINATED BY ';'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(city, temperature);
WITH AGG AS(
SELECT city,
MIN(temperature) min_measure,
cast(AVG(temperature) AS DECIMAL(8,1)) mean_measure,
MAX(temperature) max_measure
FROM test
GROUP BY city
)
SELECT GROUP_CONCAT(CITY, '=', CONCAT(min_measure,'/', mean_measure,'/', max_measure) ORDER BY CITY SEPARATOR ', ')
FROM AGG
;
And a then timed with:
time mysql --local-infile=1 -u root -vvv 1brow < test.sql
The timing is 43 min 57.99 sec
with over 33 min 32.15 sec
spent on the ingestion phase and 10 min 25.77 sec
on the query. The file test.ibd
being over 40GB
almost 4x times the original size of the measurements.txt
file (13GB
). You can check the size of the table file with the following command:
ls -lh /usr/local/var/mysql/1brow/test.idb
Alert: the following is NOT a benchmark! The test is done with default installations of both databases and NO optimization. The blog only shows the technical viability of a solution.
You might need to tweak the connect_timeout
parameter in my.cnf
to raise the connection timeout to 100 minutes in order to wait for the loading and query process to finish.
Speed up MySQL timing by using the MEMORY Engine
MySQL has a Memory storage engine which will avoid writing to disk and store the entire table in RAM. As per documentation, this is not a safe option for production workloads since:
Because the data is vulnerable to crashes, hardware issues, or power outages, only use these tables as temporary work areas or read-only caches for data pulled from other tables.
The only change we have to perform, in order to use the MEMORY
storage engine is to redefine our test
table as:
CREATE TABLE TEST (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
city VARCHAR(100),
temperature FLOAT
) ENGINE = MEMORY;
But, when making the change and retrying the script you might hit the ERROR 1114 (HY000) at line 9: The table 'test' is full
since the data doesn’t fit the memory allocated to MySQL. To avoid this we can set the following flags to raise the allocated memory to 64GB
:
SET max_heap_table_size = 1024 * 1024 * 1024 * 64;
However, also this didn’t work, and stopped my test after waiting for over 40 minutes
for just the loading time.
Do you have ideas on how to speed up the process in MySQL? I’m all 👂 on X (ex Twitter) @ftisiot!