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