I took the same table as I used for MySQL Group by Performance Tests to see how much MySQL can sort 1.000.000 rows, or rather return top 10 rows from sorted result set which is the most typical way sorting is used in practice.
I tested full table scan of the table completes in 0.22 seconds giving us about 4.5 Million of rows/sec. Obviously we can’t get sorted result set faster than that.
I placed temporary sort files on tmpfs (/dev/shm) to avoid disk IO as a variable as my data set fits in memory anyway and decided to experiment with sort_buffer_size variable.
The minimum value for sort_buffer_size is 32K which gives us the following speed:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | mysql> select * from gt order by i desc limit 10; +--------+------------------------------------------+ | i | c | +--------+------------------------------------------+ | 100000 | 635e8e8f8e3b9dc547bbd3deaadb1f297f691729 | | 100000 | 0a7750a1393e77a2871ecfb39d5032d0b0f7c37c | | 100000 | 0db0601036fb9d1d5e17631d4d1bed9149675bb3 | | 100000 | eb6d2b5ed1897bdd0ff6e22ee1b44814ffb8f912 | | 100000 | 1bff67cc134e316dad5370de38020bef818ec45c | | 99999 | 635da2e73d88dbe5f7297253680398e58d32ff65 | | 99999 | a1feec5f8ee6c6a96723a2a0b57c418bb3ced929 | | 99999 | 72b934f76863791f740b96858d5acb6a60459644 | | 99999 | 855b47aaa25054e77dcc27de5def8de1e265f371 | | 99999 | 81980bcd9dbaa565f22a93ce1faf9e9d53407f0a | +--------+------------------------------------------+ 10 rows in set (0.56 sec) |
Not bad ! Even though MySQL does not optimize “get top N sorted rows” very well it takes just 2.5 times longer than full table scan to get the data. And this is with minimum sort_buffer allowed when a lot of sort merge passes are required for sort completion:
1 2 3 4 5 6 7 8 9 10 | mysql> show status like "sort%"; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Sort_merge_passes | 321 | | Sort_range | 0 | | Sort_rows | 10 | | Sort_scan | 1 | +-------------------+-------+ 4 rows in set (0.00 sec) |
As you can see from this show status output MySQL only counts completely sorted rows in Sort_rows variable. In this case 1.000.000 of rows were partially sorted but only 10 rows fetched from the data file and sent and only they are counted. In practice this means Sort_rows may well understate sort activity happening on the system.
Lets now increase sort_buffer_size and see how performance is affected:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | set sort_buffer_size=100000; mysql> select * from gt order by i desc limit 10; 10 rows in set (0.44 sec) mysql> show status like "sort%"; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Sort_merge_passes | 104 | | Sort_range | 0 | | Sort_rows | 10 | | Sort_scan | 1 | +-------------------+-------+ 4 rows in set (0.00 sec) |
OK raising sort_buffer_size to 100K gives quite expected performance benefit, now we’re just 2 times slower than table scan of the query and considering table size was about 60MB we have 120MB/sec sort speed, while 2.000.000 rows/sec is of course more relevant in this case.
Still a lot of sort merge passes lets go with even higher buffer sizes.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | set sort_buffer_size=1000000; mysql> select * from gt order by i desc limit 10; 10 rows in set (0.70 sec) mysql> show status like "sort%"; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Sort_merge_passes | 10 | | Sort_range | 0 | | Sort_rows | 10 | | Sort_scan | 1 | +-------------------+-------+ 4 rows in set (0.00 sec) set sort_buffer_size=10000000; mysql> select * from gt order by i desc limit 10; 10 rows in set (1.34 sec) mysql> show status like "sort%"; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Sort_merge_passes | 1 | | Sort_range | 0 | | Sort_rows | 10 | | Sort_scan | 1 | +-------------------+-------+ 4 rows in set (0.00 sec) |
Wait it is not right. We’re increasing sort_buffer_size and number of sort_merge_passes decreases appropriately but it does not help sort speed instead it drops 3 times from 0.44sec to do 1.34sec !
Lets try it even higher to finally get rid of sort merge passes – may be it is sort merge which is inefficient with large sort_buffer_size ?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | mysql> set sort_buffer_size=100000000; Query OK, 0 rows affected (0.00 sec) mysql> select * from gt order by i desc limit 10; +--------+------------------------------------------+ | i | c | +--------+------------------------------------------+ | 100000 | eb6d2b5ed1897bdd0ff6e22ee1b44814ffb8f912 | | 100000 | 635e8e8f8e3b9dc547bbd3deaadb1f297f691729 | | 100000 | 1bff67cc134e316dad5370de38020bef818ec45c | | 100000 | 0db0601036fb9d1d5e17631d4d1bed9149675bb3 | | 100000 | 0a7750a1393e77a2871ecfb39d5032d0b0f7c37c | | 99999 | 41f091f4074717bf80d2b1a788e6a4a122057d11 | | 99999 | 049d9591ef0f584deaaf0433c0f3eda8631bdb85 | | 99999 | 72b934f76863791f740b96858d5acb6a60459644 | | 99999 | f0a42a16a41b4249da7c31f2d9556f05622a87b4 | | 99999 | 35de8ae483779e6024c51998eb5b5e69e02eb74c | +--------+------------------------------------------+ 10 rows in set (1.55 sec) mysql> show status like "sort%"; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Sort_merge_passes | 0 | | Sort_range | 0 | | Sort_rows | 10 | | Sort_scan | 1 | +-------------------+-------+ 4 rows in set (0.00 sec) |
Nope. We finally got rid of sort_merge_passes but our sort performance got even worse !
I decided to experiment a bit further to see what sort_buffer_size is optimal for given platform and given query (I did not test if it is the same for all platforms or data sets) – The optimal sort_buffer_size in this case was 70K-250K which is quite smaller than even default value.
The CPU in question was Pentium 4 having 1024K of cache.
A while ago I already wrote what large buffers are not always better but I never expected optimal buffer to be so small at least in some conditions.
What do we learn from these results:
- Benchmark your application Unfortunately general tuning guidelines can be wrong for your particular case, or generally wrong because they tend to reprint the manual which is often written based on theoretical expectations rather than supported by large amount of testing.
- sort_merge_passes are not that bad. Setting your sort_buffer_size large enough so there is zero sort_merge_passes may not be optimal.
- World is full of surprises I obviously did not expect to get such results, and this is not any exception. Even spending a lot of time optimizing and otherwise working with MySQL I continue to run in results which surprise me. Some are later expected others come from underlying bugs and later fixed.
Hi Peter! Nice couple posts on buffer allocation concepts. I’m wondering…do your findings suggest that the main bottleneck in these cases is actually the memory allocation itself? Your results seem to indicate that the sort_merge execution is very fast compared to the memory allocation speed for large mem amounts.
I do not think it is buffer allocation. We’re speaking about single sort buffer allocation per sort duration and that can’t be taking any significant amount.
There was the case when buffer allocation was a bottleneck – with dependent subselect with order by – but in that case this was to happen a lot of times per query.
Generally I’d see more profiling on this case because it is not how it should be. I suspect it is cache efficiency which is the issue here.
Would be nice if stuff like this could end up into a script which will be included with the mysql distros so you could just run “find_optimal_settings.sh” (or whatever it might be named :P) and return a few minutes later and it would present optimal sizes for sort_buffer_size among other tweakable variables.
Or does this perhaps already exists ?
My guess is on CPU cache. Would be interesting to see optimal value on a CPU with more cache (2 or 4MB)
Apachez,
As I mentioned I’m not sure all of the settings would will be simply Hardware+OS related. Though some benchmarks to show best values for such likely architecture dependent variables would be interesting.
I wonder if this is some kind of interaction with the LIMIT statement. If you don’t use LIMIT, does it change which setting does the best?
Perrin, yes my thoughts too. But for this particular testcase I observed the same on a Q6600 cpu (2x4MB or whatever they have as L2). Values roughly between 100-256k were fastest while a sort_buffer_size of 512k or larger showed a slowdown (not much but still repeatable).
Peter, do you think you could post some my.cnf’s for various setups which you have found be working good and speedy? You spoke about this for more than a year ago 😉 (look in the comments for http://www.mysqlperformanceblog.com/2006/06/06/are-larger-buffers-always-better/ ) For example based on your knowledge and findings, how would a my.cnf (mysql 5.x) recommended by you look for a Q6600 cpu system with 4 gig ram and using only myisam (aswell as myisam + innodb)? 🙂
Would an index on the sort field make any difference in performance?
Hi Peter, Apachez!
Apachez, that’s a very interesting suggestion (find_optimal_settings.sh). I am meeting with the guys from University of Maryland SKOLL Cluster on Wednesday. They are assisting our QA team by continually building and testing hundreds of thousands of configurations across their 200-machine cluster. I will bring up this idea as something to think about long-term: getting optimal config settings for a variety of (common?) architectures and platforms.
Cheers, and thanks for the good idea!
Jay
Perrin,
I did not check without limit as in this case there would be too much overhead for fetching data.
Apachez,
It is not that simple as memory size – a lot of stuff is workload specific.
Peter, but still – some my.cnf examples here at mysqlperformanceblog.com would be great.
Jay, any reponse yet from the Maryland dudes and dudettes ?
Yes… We should create commented my.cnf examples sometime.
Apachez,
We had a very good meeting in New York and you’ll be hearing more about this in the coming weeks on my blog and Giuseppe’s. Stay tuned. 🙂
-jay
Awesome article. Its interesting to find that giving mysql more memory does not help performance. This is totally opposite to what many believe. I am sure this is a result of the amount of System Cache available to mysql. Makes alot of sense considering intel has been moving away from high CPU speeds to beefed up 4, 6 and 8 MB cache with multiple cores. I would be curious to see what the speed improvement is when using one of these beefed up chips.
Scott, an index that can satisfy both the WHERE and ORDER BY clauses can be expected to eliminate the need to sort.
Whitesites, larger buffers often still do help performance on mixed query loads but what this does show is that if you can have the default low and increase it only in the sessions that benefit from larger sizes you can get the best of both worlds. As you optimise queries and indexes you can also find that the optimal value for a particular workload changes.
Hi Peter,
I wonder how the size of this buffer would affect real loaded systems?
Obviously on many real systems disk IO comes into the equation and then disk seek time can start playing a big factor.
I would be interested to see if the results change in this kind of situation.
Kind Regards,
Lachlan
Lachlan,
Indeed it can affect things. And there are more variables like table structure of course – I have not tested these yet.
On this subject.. if you have a huge un-ordered myisam table and wish to get it into sort order before dumping it to INNODB, which is going to be faster?
a) Add an index on column to be sorted and select by that index
b) Dump the data to flat file and sort using unix sort then re-import
c) Select using table-scan and sort as per your example
d) alter to innodb with primary key (which is the objective anyway)
I’m currently doing (a) but I’m wondering if this is a big waste of time.
Further to my last question..
As described in this blog post, a table scan with order by/limit does a very fast sort if you want to get the top-most rows .. but I find it useless if you want to use it to create a full ordered insert (for example) for innodb.
My large table spends hours ‘sorting result’, even though I can get top 20 results within 15 minutes. I wonder if there are some short-cuts taken by mysql where limit is used. Perhaps a table-scan, with a running best-of list, for example. That would not then be a sort, would it?
In order to do an ordered bulk insert to innodb by PK I am resorting to external sort of a file with unix sort then feeding it with batch inserts, rather than feeding it to myisam then converting, or feeding to myisam then sort .. order by .. insert, or feeding un-ordered PK to innodb directly then doing alter table engine=innodb to “defragment” PK.
Am I on the right track?
I second other commenters asking for commented My.cnf files from the magicians of this blog! that would certainly be very interesting and useful.
Hi
Is there any way that I can sort in numeric mode or in string mode…..?????
Let me know any if possible.
Thanks
Good ideas . I was enlightened by the points , Does anyone know where my company might get access to a blank DS-2003 version to fill in ?