Large Table repair/indexing and myisam_sort_buffer_size

Question

I have a MySQL table using the MyISAM engine with 6 INT columns, 1 SMALLINT, 2 TINYINT and 1 FLOAT column. It has billions of rows (the data file is 100GB). I was trying to index on all of the columns by enabling keys, but that never happened. Trying "myisamchk -r tableName" gave the following error:

- recovering (with sort) MyISAM-table 'tableName'                               
Data records: 662929483                                                         
- Fixing index 1                                                                
myisamchk: error: myisam_sort_buffer_size is too small                          
MyISAM-table 'tableName' is not fixed because of errors                         
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag

Using "myisamchk -rov tableName" instead takes forever again (presumably because it is using the keycache method, not the sort method).

Does it not make sense to increase myisam_sort_buffer_size in this case (The answer to myisam_sort_buffer_size vs sort_buffer_size suggests increasing the value never makes sense). The machine has 32GB of RAM.

Answer 1

you just need to increase the sort buffer size for myisamchk.

 myisamchk -r -q TABLE.MYI --sort_buffer_size=2G 

found it here: https://ma.ttias.be/mysql-myisamchk-error-myisam_sort_buffer_size-is-too-small/

Answer 2

If you are processing the 100GB data offline. Create batch tables by splitting the data into separate tables each has few million data to it with proper indexing and data base engine as innodb.

If you are using those data online in a real time application please refer the below link on managing huge data volume. What database works well with 200+GB of data?