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 Table corrupt
.
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.
UNSIGNED
.SELECT COUNT(*) FROM reception_edges_denorm
give you?SELECT COUNT(*)
prior to index creation returns4916267670
the correct number of rows.