MariaDB: Large INSERT SELECT query failed without generating an error

Question

I have a large complicated neuroscience dataset that I am attempting to organize in a database for concurrent and consistent access with my lab. This is my first foray beyond much smaller experiments with SQLite, and I have bitten off more than I can chew.

The primary table that contains the neural recordings has ~10 billion rows. Before populating this table with data, I established a compound primary key (id, experiment_id) where id is unique to each row. I did this so that I could partition the table by experiment_id. This seemed like a good idea at the time. However, once the table was populated, adding additional indices failed in phpMyAdmin (error pop-up indicated the new key was corrupt).

Faced with this roadblock, I found https://mariadb.com/kb/en/partition-maintenance/, which has as it's first bullet point "#1: Don't use PARTITIONing until you know how and why it will help.", followed by "table size is rarely a performance issue". So, in hopes of a "do over", I created a new table with the same columns (minus the id column) but no partitioning scheme and no indexes and executed:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
INSERT INTO tfconv_2 (experiment_id, electrode_id, trial_id, frequency_id, time_id, value_real, value_imag)
SELECT experiment_id, electrode_id, trial_id, frequency_id, time_id, value_real, value_imag
FROM tfconv;
COMMIT;

This ran for several days (which was not a surprise, given that it took days to get the data into the original table in the first place). While MariaDB toiled away, I could see the row count in the summary phpMyAdmin page going up for the new table. Monitoring resources on my server, it appeared that the process was working in a regular, systematic way to copy the data into the new table.

Then, one time when I went to check on it, the row count on the new table had decreased. SHOW PROCESSLIST revealed:

   Id   User         Host           db Command    Time                   State
10237   root    localhost   singlecell  Killed  217191  Reset for next command

With this background, I have three questions:

1. Why might this query have failed? (The error log files were silent during the relevant time window.)
2. If I tried building a partition-free table again, do you recommend I do something differently?
3. Is there a way to set up indexes on the original, partitioned table, that might avoid corruption?

Answer 1

Why might this query have failed? (The error log files were silent during the relevant time window.)

It's possible that you might've hit the statement level timeout that's controlled by max_statement_time. The fact that the SHOW PROCESSLIST output shows the status as Killed could point to this as being the reason.

It might also be a timeout that's in MyPhpAdmin or the connector that it uses to connect to the database. For long-running operations like this, a reliable connection to the database is essential.

An alternative approach is to eliminate the network completely. Doing the operations locally over SSH and using a program like screen to keep the terminal alive should help avoid situations where operations are canceled due to the client exiting too early.

Another approach would be to define a new one-off EVENT that does the change. This would eliminate all communication failures as the operation is done inside the database.

If I tried building a partition-free table again, do you recommend I do something differently?

If the table you're copying the data into is empty, you can set autocommit=0, unique_checks=0 and foreign_key_checks=0 for the duration of the session to enable bulk insertion in InnoDB. Just remember to COMMIT your transaction afterwards if autocommit is not enabled or wrap the insertions in an explicit transaction.

This blog post on mariadb.org describes some parts of the feature and with a recent enough of a MariaDB version, this should be much faster than "normal" insertion. This commit in the MariaDB server is what implements this bulk loading and it also gives an example case how it's enabled:

CREATE TABLE t(a PRIMARY KEY) ENGINE=InnoDB;
SET foreign_key_checks=0, unique_checks=0;
BEGIN;
INSERT INTO t SET a=1;
INSERT INTO t SET a=1;
COMMIT;

Is there a way to set up indexes on the original, partitioned table, that might avoid corruption?

This is not something that's easy to estimate without the exact error but, based on your research, the partitioning is probably not going to solve your performance problems.

Answer 2

wait_timeout
interactive_timeout

Compare those values to how long before your query died.

As for "#1: Don't use PARTITIONing..." -- What will your queries be? We can discuss whether partitioning will or won't help. Also, provide the current SHOW CREATE TABLE so I can judge whether the existing indexes, including the PK, can be improved even without partitioning.

How many different values are there of experiment_id today? Will new values be added frequently?

"adding additional indices failed" -- Timeout? Or something else.

I think those timeouts I mentioned need increasing to several million. Caution -- SESSION vs GLOBAL vs my.cnf and "batch" vs "interactive".