I am trying to create an index for a very large table called
reception_edges_denorm (200GB) in MariaDB. The table has 4,916,267,670 rows and the following schema :
- src_trs_id: bigint(20)
- src_trs_start: int(11)
- src_trs_end: int(11)
- dst_trs_id: bigint(20)
- dst_trs_start: int(11)
- dst_trs_end: int(11)
In the MariaDB command line I attempt to create the index using
SET SESSION aria_sort_buffer_size = 64424509440; -- 60GB SET SESSION aria_repair_threads = 8; -- when sorting CREATE INDEX src_trs ON reception_edges_denorm(src_trs_id);
The server VM has 77GB of RAM and 16 cores so I assumed the larger buffer sizes would help in speeding up the index creation. The first phase of
copying a tmp table finished successfully and the second stage
Repair by sorting goes up to 50% and then doesn't show any progress. After a day I receive the output
Query OK, 4937767670 rows affected (10 hours 33 min 33.591 sec) Records: 4937767670 Duplicates: 0 Warnings: 0. The first red flag is that the number of rows is different from the number of data rows that should be there. Moreover, when I attempt to use the table and index it is not returning correct query results. Upon running
CHECK TABLE reception_edges_denorm I get the following messages
Key in wrong position at page 77815808 and
At this stage I restart the docker container hoping to fix any issues. However, after restarting even a simple
SELECT query return the message :
Table is crashed and last repair failed"' for './hpc@002dhd/reception_edges_denorm' . The issue seems to only be with this large table as I am able to create several other indices on smaller tables (12-13GB in size) and run queries on them fine.
I am unsure why this is happening. The same corruption happened a second time even after I destroyed the server and cloud storage VM and loaded the data in fresh and attempted to create the same index. In the previous run I attempted to repair the table using
aria_chk which failed after a day.
Any help is much appreciated.