[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:
Depends
Duplicate
is duplicated by SERVER-12667 improve the performance of complex co... Closed
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.

Platform and version Query 1 Query 2 Query 3
Windows 2.2.4 100.00% 100.00% 100.00%
Windows 2.4.4 134.75% 138.87% 137.01%
Ubuntu 2.2.4 100.00% 100.00% 100.00%
Ubuntu 2.4.4 109.45% 107.50% 121.55%


 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
Commit Date Milliseconds Scaled 1 Scaled 2 Scaled 3 Scaled 4 Scaled 5 Scaled 6 Scaled 7 Scaled 8 Notes
4a5a2afbe2ca352dd518d733274538a27760ac0b 2012-12-10 15:17:32 311.33 100.00% 101.74% 121.14% 105.89% 116.32% 66.71% 79.83% 82.07% commit before first Windows flagged commit
c1e12bcce00c771cbecbda33a22f41249ad0d6f2 2012-12-10 15:18:28 306.00 98.29% 100.00% 119.07% 104.08% 114.32% 65.57% 78.46% 80.67% first Windows flagged commit
72a3bc4bdf55951e7b482db189b975598bb6866d 2013-05-24 11:44:38 257.00 82.55% 83.99% 100.00% 87.41% 96.02% 55.07% 65.90% 67.75% commit before second Windows flagged commit
c5f43cbb26ae515e50043735e3fcea54218584c9 2013-05-24 11:44:38 294.00 94.43% 96.08% 114.40% 100.00% 109.84% 63.00% 75.38% 77.51% second Windows flagged commit
a6d055026c3a13b423e099e9d080256ac040f706 2013-05-30 11:49:11 267.66 85.97% 87.47% 104.15% 91.04% 100.00% 57.36% 68.63% 70.56% commit before Macintosh flagged commit
29623818ca553d7231a8eb7274ffb16f233670e8 2013-05-30 11:57:22 466.67 149.90% 152.51% 181.58% 158.73% 174.35% 100.00% 119.66% 123.02% Macintosh flagged commit
c2748a2bf862e026c4a919aac62a568b52abb9a2 2013-06-07 15:03:28 390.00 125.27% 127.45% 151.75% 132.65% 145.71% 83.57% 100.00% 102.81% recent master, without blockCheckSupported
e55d971e468141401db1e8b9e570eed7af3c5947 2013-06-07 16:34:22 379.33 121.84% 123.96% 147.60% 129.02% 141.72% 81.28% 97.26% 100.00% recent master, with blockCheckSupported
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
Commit Date Milliseconds Scaled 1 Scaled 2 Scaled 3 Scaled 4 Notes
c1e12bcce00c771cbecbda33a22f41249ad0d6f2 2012-12-10 15:18:28 205.00 100.00% 76.02% 52.88% 50.62% copied from previous test
a6d055026c3a13b423e099e9d080256ac040f706 2013-05-30 11:49:11 269.67 131.55% 100.00% 69.56% 66.59% commit before flagged commit
29623818ca553d7231a8eb7274ffb16f233670e8 2013-05-30 11:57:22 387.67 189.11% 143.76% 100.00% 95.72% flagged commit
e55d971e468141401db1e8b9e570eed7af3c5947 2013-06-07 16:34:22 405.00 197.56% 150.18% 104.47% 100.00% copied from previous test
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
Commit Date Milliseconds Scaled 1 Scaled 2 Scaled 3 Scaled 4 Notes
c1e12bcce00c771cbecbda33a22f41249ad0d6f2 2012-12-10 15:18:28 292.33 100.00% 82.19% 81.20% 75.28% copied from previous test
72a3bc4bdf55951e7b482db189b975598bb6866d 2013-05-24 11:44:38 355.67 121.67% 100.00% 98.80% 91.59% commit before flagged commit
c5f43cbb26ae515e50043735e3fcea54218584c9 2013-05-24 11:44:38 360.00 123.15% 101.22% 100.00% 92.70% flagged commit
e55d971e468141401db1e8b9e570eed7af3c5947 2013-06-07 16:34:22 388.33 132.84% 109.18% 107.87% 100.00% copied from previous test
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
Commit Date Milliseconds Scaled 1 Scaled 2 Scaled 3
4a5a2afbe2ca352dd518d733274538a27760ac0b 2012-12-10 15:17:32 198.00 100.00% 67.73% 50.99%
c1e12bcce00c771cbecbda33a22f41249ad0d6f2 2012-12-10 15:18:28 292.33 147.64% 100.00% 75.28%
e55d971e468141401db1e8b9e570eed7af3c5947 2013-06-07 16:34:22 388.33 196.13% 132.84% 100.00%
Ubuntu 12.04 LTS in VirtualBox under Windows 7 (CPU as above), 8 GB RAM, SSD
Commit Date Milliseconds Scaled 1 Scaled 2 Scaled 3
4a5a2afbe2ca352dd518d733274538a27760ac0b 2012-12-10 15:17:32 170.33 100.00% 85.88% 87.95%
c1e12bcce00c771cbecbda33a22f41249ad0d6f2 2012-12-10 15:18:28 198.33 116.44% 100.00% 102.41%
e55d971e468141401db1e8b9e570eed7af3c5947 2013-06-07 16:34:22 193.67 113.70% 97.65% 100.00%
Macbook Air, 4 GB RAM, SSD
Commit Date Milliseconds Scaled 1 Scaled 2 Scaled 3
4a5a2afbe2ca352dd518d733274538a27760ac0b 2012-12-10 15:17:32 181.33 100.00% 88.45% 44.77%
c1e12bcce00c771cbecbda33a22f41249ad0d6f2 2012-12-10 15:18:28 205.00 113.05% 100.00% 50.62%
e55d971e468141401db1e8b9e570eed7af3c5947 2013-06-07 16:34:22 405.00 223.35% 197.56% 100.00%
Generated at Thu Feb 08 03:21:43 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.