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 :

  1. src_trs_id: bigint(20)
  2. src_trs_start: int(11)
  3. src_trs_end: int(11)
  4. dst_trs_id: bigint(20)
  5. dst_trs_start: int(11)
  6. 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.

  • Try the indexing with only 1GB for aria_sort_buffer_size. I would expect it to resort to a disk-based "sort-merge", which might be almost as fast. And hopefully less error-prone.l
    – Rick James
    Aug 2 at 20:03
  • Is there some reason for using Aria instead of InnoDB?
    – Rick James
    Aug 2 at 20:03
  • What are the trs_start/end values? If IP addresses, they need to be UNSIGNED.
    – Rick James
    Aug 2 at 20:04
  • What does value does SELECT COUNT(*) FROM reception_edges_denorm give you?
    – Rick James
    Aug 2 at 20:06
  • I have once more recreated the database this time only with the table in question. There is 444GB free on disk and the table occupies 198GB at the moment. @RickJames I will try to create the index with a smaller aria_sort_buffer_size and check if it works. The trs_start/end_values are array offsets and indeed should be UNSIGNED. I load the data from Spark so I think I might need to change the schema to all be UNSIGNED INT which might take up lesser space as well. The SELECT COUNT(*) prior to index creation returns 4916267670 the correct number of rows. Aug 3 at 7:51


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.