Translate this page :

MariaDB : Faster Index Creation

:: I've had to create indexes for tables with hundreds of millions records in it. Anyone who has tried it will already knew the pain 🙂

With MariaDB, you can have this process done faster. Here's the settings that you'll need to set :

myisam_max_sort_file_size
myisam_sort_buffer_size

Ensure these 2 variables are bigger than each index that's going to be created. In a server with 128 GB of RAM, I set this to 50 GB. Indexes are always created very quickly 🙂

Otherwise, the index creation process will show as "Repair with keycache". This is really slow. 

When these 2 variables are set with big enough numbers, then you'll see "Repair by sorting", or, when you set the following variable : "Repair with X threads" 

myisam_repair_threads

When this variable is bigger than 1, MariaDB will utilize more than 1 cpu core in the index creation process. 

The bigger the number, more cpu core may be used, so it'll be potentially faster. However, this will result in more usage of cpu (of course) and RAM. 

NOTE: using this setting in MySQL is not advised, there are still bugs : http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_myisam_repair_threads

Hope this helps someone else.

Embedded Link

MySQL :: MySQL 5.7 Reference Manual :: 5.1.4 Server System Variables
The MySQL server maintains many system variables that indicate how it is configured. Each system variable has a default value. System variables can be set at server startup using options on the command line or in an option file. Most of them can be changed dynamically while the server is running …

Post imported by Google+Blog for WordPress.

15 Responses to “MariaDB : Faster Index Creation

Leave a Reply

 

Subscribe without commenting

            








SEObox: Web Hosting Murah Unlimited Komik Indonesia Homeschooling Indonesia