[SERVER-9883] Performance issues with distinct and count queries Created: 09/Jun/13 Updated: 26/Nov/18 Resolved: 26/Nov/18 |
|
| Status: | Closed |
| Project: | Core Server |
| Component/s: | Performance |
| Affects Version/s: | 2.4.4 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major - P3 |
| Reporter: | Tad Marshall | Assignee: | Asya Kamsky |
| Resolution: | Done | Votes: | 7 |
| Labels: | None | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Issue Links: |
|
||||||||||||
| Operating System: | ALL | ||||||||||||
| Participants: | |||||||||||||
| Description |
|
Some types of queries are slower in 2.4.4 than in builds from the 2.2 series. This slowdown is most evident in Windows builds, but may affect other platforms as well. This table shows version 2.2.4 versus version 2.4.4 for three sample queries. The values are query durations scaled to the time taken by the query on version 2.2.4 in order to hide differences between queries, platforms and hardware (so 2.2.4 is always 100%). Numbers above 100% indicate a slowdown between versions.
|
| Comments |
| Comment by Asya Kamsky [ 26/Nov/18 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
There have been multiple improvements made to count and distinct queries - the examples used to generate tables in this ticket are on the order of 4X faster than they were in 2.2/2.4. Closing as gone away. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Tad Marshall [ 09/Jun/13 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Rounding out the platform performance tests, here is the Solaris/SmartOS build running on a Joyent instance. This table mostly seems to show that you can't trust timings in the cloud to tell you much. The change that was first flagged as a Windows slowdown shows up here as a speedup. The 21% Windows slowdown between c1e12b and 72a3bc is here another speedup. The 43% Mac slowdown from a6d055 to 296238 shows up in this table as a 74% slowdown. But the average of the last three measurements is 41.36% slower than the average of the first three measurements, so there is some evidence of lost performance here as well. Joyent SmartOS cloud instance
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Tad Marshall [ 09/Jun/13 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Running 'git bisect run' on the Macbook Air to try to identify the commit responsible for the 97.56% loss in speed, commit 29623818ca553d7231a8eb7274ffb16f233670e8 was flagged. This again shows that performance loss was not in a single commit. The flagged commit lost 43.76% performance, but 31.55% had been lost by the time of the previous commit and another 4.47% has been lost since then. Macbook Air, 4 GB RAM, SSD
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Tad Marshall [ 09/Jun/13 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Continuing with 'git bisect' on Windows to try to find the slowdown since the commit flagged above, it looks like the performance changes were not all in a single commit. 'git bisect run' flagged commit c5f43cbb26ae515e50043735e3fcea54218584c9 as the 'first failing commit', but that is an artifact of the value I chose as the cutoff for failure. This commit should not have been the cause of the slowdown because the code it added is not called, and the immediately previous commit shows similar performance. This table shows that by the time of the flagged commit, we had lost 23.15% of our performance and that we have lost another 7.87% since then. Windows 7, Intel Core i7-980X (6 cores w/HT, 3.33 GHz), 12 GB RAM, Samsung 840 Pro SSD
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Comment by Tad Marshall [ 09/Jun/13 ] | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
It seems that there are multiple commits responsible for the slowdown. Using 'git bisect' on Windows, I was able to identify one commit that accounted for a 47.64% slowdown on a modified version of query 3, but a recent master is slower still by another 32.84%. These slowdowns combine to 96.13% ... we're almost twice as slow in master compared to a 2.3.2-pre- version on Windows. Linux only took a 16% performance hit from this commit, and is actually faster now than it was with that commit. Mac OS/X took a 13% performance hit from this commit but took another 97.56% loss in speed since then, combining to a 123.35% increase in query time. Windows 7, Intel Core i7-980X (6 cores w/HT, 3.33 GHz), 12 GB RAM, Samsung 840 Pro SSD
Ubuntu 12.04 LTS in VirtualBox under Windows 7 (CPU as above), 8 GB RAM, SSD
Macbook Air, 4 GB RAM, SSD
|