Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This is similar to another post of mine, but now I have a different table structure, and it's still unclear what MySQL parameters should be changed to take advantage of the extra ram my machine has---so if it seems like a duplicate, let me know how best to rephrase what I'm asking.

I have the following table: create table mytable (id1 int, id2 int, score float) engine MyISAM, with 50 billion records.

I plan to add an index on the first column (alter table mytable add index myindex (id1)), and I am wondering what MySQL parameters could be changed to take advantage of this extra memory (e.g. buffers?)?

Note: the engine type does not need to be MyISAM if that makes a difference.

share|improve this question
This is a much better table than the one in your previous post. Did you already load your data? I assume you used mysqlimport or "load data", and loaded a file that is already sorted by the primary key, oh wait, you don't have a primary key, would id1 be a unique value? Then, execute typical queries, compare your "show variables" output with your "show status" output, and identify the bottlenecks. –  Ursula Aug 7 '13 at 1:14
The other post was for a different use case (I ended up using HBase for that problem). I have not loaded the data into this new table yet, but will use "load data" unless there's a faster way. I was not planning to add a PK (since I won't do updates/deletes), and id1 would not be unique. –  Dolan Antenucci Aug 7 '13 at 12:58
add comment

1 Answer

up vote 2 down vote accepted

Your first concern is the data loading. Since you don't intend to have a primary key, but just an index, you should order your data before loading by the index column id1, in any UNIX flavor:

sort -n mytable.txt > mytable.sort

For loading the data, use either mysqlimport of LOAD DATA from the MySQL prompt. To speed up the loading, MYISAM tables benefit from increasing *bulk_insert_buffer_size*, *myisam_sort_buffer_size* or *key_buffer_size*, for INNODB tables, increase *innodb_buffer_pool_size* and *innodb_log_file_size*.

These increases might not be sufficient, and the loading still might slow down as the memory fills up. In that case, it is more efficient to load the data in chunks. You can monitor the speed by executing an hourly du in the mysql root directory:

while [ 1 ]
    du -hs database/.
    sleep 3600

Just for completeness (not relevant for this specific case): Disabling indexes also helps speedup the loading process.

Now to querying: This is a bit more complex, as it depends on your data, and the way you want to query your table. Biggest factors that influence performance are:

  • buffers: set by variables. Execute your typical queries and compare the "show variables" and "show status" output to identify bottlenecks.

  • indexes: you already covered that

  • joins: Depending on the way you join with other tables, it might be
    better to denormalize your table(s) in a data warehouse style,
    especially if you want to join two large tables.

Great articles on data loading and large table query optimization can be found at: http://www.mysqlperformanceblog.com/2007/05/24/predicting-how-long-data-load-would-take/ http://www.mysqlperformanceblog.com/2006/06/09/why-mysql-could-be-slow-with-large-tables/

Review the default and allowed values for these and other variables at: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html

share|improve this answer
I like your suggestion of sorting the data ahead of time. The first blog post your reference mentions the benefit of this on InnoDB engines, but I'm wondering, do you know if it is also equally beneficial to MyISAM? Given that this table will be read-only, do you see any advantage to InnoDB vs. MyISAM? Thanks –  Dolan Antenucci Aug 7 '13 at 19:19
My tables are all in MyISAM (for historical reasons), and for my tables, sorting does make a big different for loading speed and performance, however, I haven't benchmarked it in years. –  Ursula Aug 7 '13 at 20:24
For what it's worth to others, I presorted my data and removed indexes before loading it, then set myisam_sort_buffer_size=80G and myisam_max_sort_file_size=1000G before creating the index. Thanks for the tips Ursula –  Dolan Antenucci Aug 9 '13 at 10:25
Final follow-up: index finished creating in 7.5 hours. I noticed that it created a temp file for the MYD file first (creating a 300gb file on disk, not using much ram), and then it was busy for next 5 hours (with 80gb memory in use), eventually writing a 280gb .MYI file (the index) to disk. In hindsight, I wonder if the .MYD file was created because I created the index after loading the data instead of 1) creating the index before loading the data, 2) disabling indexes, 3) loading the data, and 4) enabling indexes. Thoughts anyone? –  Dolan Antenucci Aug 9 '13 at 17:43
@dolan - In addition to above, you can use specific tmpfs partitions using a chunk of memory that can be used for temp table operations that MyISAM may normally use the disk for, bypassing the disk bottleneck. Loads from a RAM based file dir are again significantly faster. –  cerd Aug 11 '13 at 3:47
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.