32-bits vs 64-bits MySQL performance
Hello list, Finally I've got a working Opteron, so I decided to test its stability and speed. I imported a backup of the database it is going to run and ran a few queries we use in our scripts on them to test how fast it was. We ran this script a few times with different concurrency levels using apache and ab and then we started a 32 bits mysql version and ran the same script with the same parameters again. There were some results I didn't expect. These are the results I got: First number is # threads, second and third queries/s, last is % difference Threads 64 bits 32 bits % 1 1944,56 2462,48 26,6% 2 2893,32 3604,71 24,6% 3 2867,71 3654,71 27,4% 4 2818,35 3519,48 24,9% 5 2768,75 3478,66 25,6% Apparently the 32 bits version of MySQL is almost 25% faster than the 64-bit flavor of MySQL. The queries used here are 'heavy' queries, not the average selects. Every result you see here above is the result of requesting 5000 times the script, resulting in approx. 250.000 queries on the MySQL server. To avoid coincidences in the queries we chose, I made a second script, containing 'light' queries, the results: Threads 64 bits 32 bits % 1 7091,16 6808,51 -4,0% 2 8358,85 8779,57 5,0% 3 9290,33 9678,81 4,2% 4 9346,16 10075,78 7,8% 5 9514,71 10289,20 8,1% 7 9495,10 10306,70 8,5% Less difference, but the 32-bits version is still faster than the 64-bits, with more threads the difference gets up to 16%. The used MySQL version is 4.0.16 for both 32 bits and 64 bits and I'm running SuSE Linux Beta Distribution version 8.2-beta9 with a 2.4.19-NUMA kernel on a Dual Opteron 246 w/ 4G RAM on an Tyan K8S motherboard. After the benchmarks I have a few questions, - Why is the 64-bits version so much slower than the 32-bits version? The 64-bits version is compiled with gcc 3.2.1 on a SuSE box (without optimalisations apparently), the 32-bits is compiled with gcc 2.95.3 with optimalisations '-O2 -mcpu=pentiumpro'. Is the difference in performance due to these compiler options or is there something else I totally overlooked? - Is it useful to compile my own MySQL (with gcc 3.3?) or do I have to wait until MySQL is better optimized for the Opteron? A lot of questions and text, but I hope someone can enlighten me on this matter. tia, - kees
Kees Hoekzema
Hello list,
Finally I've got a working Opteron, so I decided to test its stability and speed. I imported a backup of the database it is going to run and ran a few queries we use in our scripts on them to test how fast it was.
We ran this script a few times with different concurrency levels using apache and ab and then we started a 32 bits mysql version and ran the same script with the same parameters again. There were some results I didn't expect.
I would like to see those results with our current version (GCC 3.3 which is faster than 3.2, current glibc) on SuSE Linux 9.0. Do you have access to it? Or can you give us some testcase to reproduce? Andreas
These are the results I got: First number is # threads, second and third queries/s, last is % difference
Threads 64 bits 32 bits % 1 1944,56 2462,48 26,6% 2 2893,32 3604,71 24,6% 3 2867,71 3654,71 27,4% 4 2818,35 3519,48 24,9% 5 2768,75 3478,66 25,6%
Apparently the 32 bits version of MySQL is almost 25% faster than the 64-bit flavor of MySQL. The queries used here are 'heavy' queries, not the average selects. Every result you see here above is the result of requesting 5000 times the script, resulting in approx. 250.000 queries on the MySQL server.
To avoid coincidences in the queries we chose, I made a second script, containing 'light' queries, the results:
Threads 64 bits 32 bits % 1 7091,16 6808,51 -4,0% 2 8358,85 8779,57 5,0% 3 9290,33 9678,81 4,2% 4 9346,16 10075,78 7,8% 5 9514,71 10289,20 8,1% 7 9495,10 10306,70 8,5%
Less difference, but the 32-bits version is still faster than the 64-bits, with more threads the difference gets up to 16%.
The used MySQL version is 4.0.16 for both 32 bits and 64 bits and I'm running SuSE Linux Beta Distribution version 8.2-beta9 with a 2.4.19-NUMA kernel on a Dual Opteron 246 w/ 4G RAM on an Tyan K8S motherboard.
After the benchmarks I have a few questions, - Why is the 64-bits version so much slower than the 32-bits version? The 64-bits version is compiled with gcc 3.2.1 on a SuSE box (without optimalisations apparently), the 32-bits is compiled with gcc 2.95.3 with optimalisations '-O2 -mcpu=pentiumpro'. Is the difference in performance due to these compiler options or is there something else I totally overlooked?
- Is it useful to compile my own MySQL (with gcc 3.3?) or do I have to wait until MySQL is better optimized for the Opteron?
A lot of questions and text, but I hope someone can enlighten me on this matter.
tia, - kees
Andreas -- Andreas Jaeger, aj@suse.de, http://www.suse.de/~aj SuSE Linux AG, Deutschherrnstr. 15-19, 90429 Nürnberg, Germany GPG fingerprint = 93A3 365E CE47 B889 DF7F FED1 389A 563C C272 A126
I've been load testing mysql on a dual opteron running suse 8.2beta-9 as well. But I've seen better performance with the 64-bit verion of mysql. I'm using mysql the 4.0.15-standard binaries from mysql's website. The main difference is that I'm performance testing inserts not queries. In my tests the 32-bit version is about 20% slower that the 64-bit version, at least with the inserts I'm doing. I'm doing a very high volume of inserts. One of the first things I did when I got the system was to run the mysql benchmark suite on both the 32 and 64 bit version. The 64-bit version was clearly faster here as well but I dont remember by how much. I'm not sure how large your dataset is but if you want I could run the same tests on my system to see if they come out with simular results. Mark Kees Hoekzema wrote:
Hello list,
Finally I've got a working Opteron, so I decided to test its stability and speed. I imported a backup of the database it is going to run and ran a few queries we use in our scripts on them to test how fast it was.
We ran this script a few times with different concurrency levels using apache and ab and then we started a 32 bits mysql version and ran the same script with the same parameters again. There were some results I didn't expect.
These are the results I got: First number is # threads, second and third queries/s, last is % difference
Threads 64 bits 32 bits % 1 1944,56 2462,48 26,6% 2 2893,32 3604,71 24,6% 3 2867,71 3654,71 27,4% 4 2818,35 3519,48 24,9% 5 2768,75 3478,66 25,6%
Apparently the 32 bits version of MySQL is almost 25% faster than the 64-bit flavor of MySQL. The queries used here are 'heavy' queries, not the average selects. Every result you see here above is the result of requesting 5000 times the script, resulting in approx. 250.000 queries on the MySQL server.
To avoid coincidences in the queries we chose, I made a second script, containing 'light' queries, the results:
Threads 64 bits 32 bits % 1 7091,16 6808,51 -4,0% 2 8358,85 8779,57 5,0% 3 9290,33 9678,81 4,2% 4 9346,16 10075,78 7,8% 5 9514,71 10289,20 8,1% 7 9495,10 10306,70 8,5%
Less difference, but the 32-bits version is still faster than the 64-bits, with more threads the difference gets up to 16%.
The used MySQL version is 4.0.16 for both 32 bits and 64 bits and I'm running SuSE Linux Beta Distribution version 8.2-beta9 with a 2.4.19-NUMA kernel on a Dual Opteron 246 w/ 4G RAM on an Tyan K8S motherboard.
After the benchmarks I have a few questions, - Why is the 64-bits version so much slower than the 32-bits version? The 64-bits version is compiled with gcc 3.2.1 on a SuSE box (without optimalisations apparently), the 32-bits is compiled with gcc 2.95.3 with optimalisations '-O2 -mcpu=pentiumpro'. Is the difference in performance due to these compiler options or is there something else I totally overlooked?
- Is it useful to compile my own MySQL (with gcc 3.3?) or do I have to wait until MySQL is better optimized for the Opteron?
A lot of questions and text, but I hope someone can enlighten me on this matter.
tia, - kees
One further note: You might want to compare these 3 scenarios: - 64-bit mysql under 64-bit kernel - 32-bit mysql under 64-bit kernel - 32-bit mysql under 32-bit kernel Andreas -- Andreas Jaeger, aj@suse.de, http://www.suse.de/~aj SuSE Linux AG, Deutschherrnstr. 15-19, 90429 Nürnberg, Germany GPG fingerprint = 93A3 365E CE47 B889 DF7F FED1 389A 563C C272 A126
One thing I also tested was the performance of the in memory (HEAP) table type. I noticed that it used quite a bit more RAM. The 64-bit version uses about twice as much ram for the index_length, although the data_length was about the same. However, on disk the index and data file size were the same on 32-bit vs 64-bit. The key_buffer config variable is one of the most important config variables in mysql. If the 64-bit version is using twice as much ram for index buffering then its really holding half as many keys? Thats my guess but I could be wrong. I wonder if doubling the key_buffer size config var in the 64-bit version would change your query results. Mark Andreas Jaeger wrote:
One further note: You might want to compare these 3 scenarios:
- 64-bit mysql under 64-bit kernel - 32-bit mysql under 64-bit kernel - 32-bit mysql under 32-bit kernel
Andreas
On Monday 27 October 2003 06:43, Andreas Jaeger wrote:
I would like to see those results with our current version (GCC 3.3 which is faster than 3.2, current glibc) on SuSE Linux 9.0. Do you have access to it? Or can you give us some testcase to reproduce?
Unfortunatly I don't have SuSE Linux 9.0, neither can I find a reseller in the Netherlands, and I have no idea which store would sell the AMD64 version of it. I can try to compile it on beta-9 which has gcc 3.3 also, but every time I compiled MySQL it was unstable under heavy load. but I can try :). I only used select queries on the database, no inserts because more than 90% of our queries are selects. Maybe i can try to use turn on the binlog and run that over a recent snapshot, but I haven't done that before so I have to find out how that works. On Monday 27 October 2003 08:14, Mark Horton wrote:
I'm not sure how large your dataset is but if you want I could run the same tests on my system to see if they come out with simular results.
I'm only running selects, the script i'm using is simulating a user visiting our website, basicly running random selects. The dataset I use is 960MB when dumped with MySQLDump, MySQL reports that the dataset is approx 1.4GB large with 6.518.269 rows. On Monday 27 October 2003 08:18, Andreas Jaeger wrote:
One further note: You might want to compare these 3 scenarios:
- 64-bit mysql under 64-bit kernel - 32-bit mysql under 64-bit kernel - 32-bit mysql under 32-bit kernel
I'v already done the first two scenarios, I will try to do the third one if I have fysical access to the server again (soon). Furthermore, i will try to play with the key_buffer setting (btw, I do use a lot of innodb, will innodb be affected by the keybuffer?) thank you all for the suggestions so far -kees
Kees Hoekzema
On Monday 27 October 2003 06:43, Andreas Jaeger wrote:
I would like to see those results with our current version (GCC 3.3 which is faster than 3.2, current glibc) on SuSE Linux 9.0. Do you have access to it? Or can you give us some testcase to reproduce?
Unfortunatly I don't have SuSE Linux 9.0, neither can I find a reseller in the Netherlands, and I have no idea which store would sell the AMD64 version of it. I can try to compile it on beta-9 which has gcc 3.3 also, but every time I compiled MySQL it was unstable under heavy load. but I can try :).
Try Amazon... Andreas -- Andreas Jaeger, aj@suse.de, http://www.suse.de/~aj SuSE Linux AG, Deutschherrnstr. 15-19, 90429 Nürnberg, Germany GPG fingerprint = 93A3 365E CE47 B889 DF7F FED1 389A 563C C272 A126
After the benchmarks I have a few questions, - Why is the 64-bits version so much slower than the 32-bits version?
Most likely you're running in some scenario where the increased footprint from 64bit pointers/longs hurts you (e.g. less efficient caching). It's unlikely that a compiler change would help you much there; gcc does not change data structures. If there are tunables in MySQL that allow you to increase the available memory for caching increasing them may help. If that doesn't help I would just run the 32bit version on the 64bit kernel. -Andi
participants (4)
-
Andi Kleen -
Andreas Jaeger -
Kees Hoekzema -
Mark Horton