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 :


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"ร‚ย 


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 :ร‚ย

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.

17 thoughts on “MariaDB : Faster Index Creation

  1. Artikel Anda sangat berkualitas dan saya mendapatkan banyak pengetahuan dalam setiap artikel yang Anda posting, selalu up to date dan terus posting begitu banyak saya ambil keuntungan dari artikel Anda terima kasih dan Salam.

  2. Way cool! Some extremely valid points! I appreciate you writing this article and the rest of the website is very good.

Leave a Reply

Your email address will not be published. Required fields are marked *