Description of problem: 1000 consecutive updates of a single column on a single record on a table containing only that record takes about 25.5 seconds. The same operation over the same version of MySQL on RedHat 7.3 on the same hardware takes 0.053 seconds. Version-Release number of selected component (if applicable): RedHat Enterprise AS 3 MySQL version 4.0.16 recompiled on RedHat Enterprise AS 3 using the default .spec on the same hardware. How reproducible: Every time Steps to Reproduce: 1. Install source package of MySQL 4.0.16. 2. Build it using rpmbuild with the default .spec file. 3. Create a test table on a test database with a single char(1) column. 4. Create a batch of 1000 update transactions on that record and run it. 5. Repeat the same process with RedHat Enterprise AS 3 and RedHat linux 7.3 timing it. Actual results: RedHat Enterprise 3 is 500 times slower on MySQL 4.0.16 for consecutive updates. Expected results: About the same speed or better than RedHat 7.3. Additional info:
Well... a couple of things here. 1. Red Hat does not support MySQL 4.0.x 2. How is this a MySQL bug (did you mean to open it against the Linux distro instead)?
1. OK, but I'm asking for OS support in this case, not MySQL. 2. I meant to open it against the commercially supported distro RedHat Enterprise AS3. The same difference in speed is reported by others in MySQL bug tracking lists and MySQL attributes this to RedHat OS. Again, the exact same version of MySQL with the exact same test in the same hardware gives results that are 500 times slower.
After opening a case with MySQL this is the response from them: "One of the possibilities of why this build might go wrong is that MySQL got linked with NPTL instead with the LinuxThreads. Our benchmark team has done some extensive measurements with NPTL and found them very unreliable, MUCH slower then good old LinuxThreads and much less stable. The only advantage of NPTL so far is the ability to create a larger number of threads. Right now it seems to me that what you experience is something that we already experienced with NPTL."
ping.. it's been about a month.. is this bug still valid and/or pending any form of work? I'm about to try this on AS3-UPD1 with 4.0.17 :)
It's still valid. Compiling MySQL in AS3 makes it very slow. MySQL team thinks that it's related to NPTL. We don't have a solution and we needed to roll back to RedHat 7.3 where a version compiled there runs 500 times faster in sequencial inserts.
Dumb question, did you try the MySQL built binaries or any optimizations to work around these problems?
We can not run the MySQL built binaries as we need to compile our own UDF (User Defined Functions) in the server. We used the included .spec file with MySQL 4.0.16 for the compilation without any additional optimization. But the same .spec file is the one that we use with RedHat 7.3 and the resulting binary works 500 times faster.
Here's the situation: we aren't supporting MySQL 4.* in Red Hat, and we won't be until the licensing problem is worked out. So right now I can't justify spending any of my own time on this. If your guess is correct that this is exposing a performance problem with NPTL, then it's possible that some of the guys working on NPTL might be interested. But we don't seem to have enough evidence here to justify reassigning the bug to that bunch. Can you try running this with LinuxThreads instead of NPTL? According to the info I looked at, it should be sufficient to set LD_ASSUME_KERNEL=2.4.1 in your environment.
Looks like the problem originates in compile time (probably because of the linkage with NPTL instead of linuxthreads). Once it's compiled on RedHat AS 3 it will run slower, even replacing the kernel by an stock 2.4.22 kernel (not a RedHat kernel). I'm unable to test it again now as I uninstalled RedHat AS 3 to go back to 7.3 that works perfectly fast.
I'm happy using the precompile MySQL binaries, which shouldn't cause that problem (eg: those are not built against NTPL kernels). I'll certianly test with LD_ASSUME_KERNEL=2.4.1 as well to see what that does too. FWIW, the precompiled MySQL-Max packages have UDF which may meet the OP's needs. I guess I must be ignorant, what is the "licensing problem"?
If I correctly interpreted the docs I found on the Web, the choice between NPTL and LinuxThreads is *not* made at compile time, it is made at run time, based on checking the environment for LD_ASSUME_KERNEL. So it should be fairly easy to test whether this is the issue or not --- and I'm not sure how MySQL's precompiled binaries would avoid the problem, unless they are overriding the environment variable internally. If flipping the environment variable doesn't seem to make any difference at all, I'd suspect they are overriding it. The licensing problem is that as of 4.0, MySQL's client libraries are GPL not LGPL, which means they cannot legally be linked with applications that are not themselves GPL, notably BSD-licensed apps such as PHP. That leaves us with the choice of shipping a crippled PHP (and other apps), or sticking to MySQL 3.*. We're sticking. I understand that this is under discussion and MySQL AB may readjust their license soon to remove the problem, but I'm not up on the details.
Looks like the default .spec in MySQL source RPM includes a statically compiled server (see below). So the difference between NPTL and linuxthreads may be relevant at compile time. [root@sdssqldev01 root]# rpm -ql MySQL-server | grep mysqld /usr/bin/mysqld_multi /usr/bin/mysqld_safe /usr/bin/safe_mysqld /usr/lib/mysql/mysqld.sym /usr/sbin/mysqld /usr/share/man/man1/mysqld.1.gz /usr/share/man/man1/mysqld_multi.1.gz /usr/share/man/man1/mysqld_safe.1.gz [root@sdssqldev01 root]# ldd /usr/sbin/mysqld not a dynamic executable
Created attachment 97220 [details] mysql benhmark compare All of this is on RHEL-ASv3/UPD-1 (2.4.21-9) on a Dell 2550 (2) P3 with 2GB RAM. # This is the plain MySQL built binaries 1 mysql-Linux_2.4.21_9.ELsmp_i686 : MySQL 4.0.17 standard # This is the MySQL built binaries with export LD_ASSUME_KERNEL=2.4.1 # In the startup script, plus the tuning from the mysql-large.cnf example file 2 mysql_241_tuned-Linux_2.4.21_9.ELsmp_i68: MySQL 4.0.17 standard log # This is the MySQL built binaries with just LD_ASSUME_KERNEL set and no tuning # whatsoever. 3 mysql_lda-241-Linux_2.4.21_9.ELsmp_i686 : MySQL 4.0.17 standard I am building 4.0.17 against AS3/UPD-1 usign the mysql standard .spec and will test that shortly.
I built 4.0.17 from source on an AMD64 box (4-way 1.4GHz) with RHEL3 on it. I find 1000 updates take about 0.061 to 0.064 seconds. I do not have an identical box running 7.3 to compare against, but I would assume that this box is not all that much faster than yours. (The multiple CPUs presumably don't help since all the updates are done in one thread.) So it looks to me like there's no problem... can you think of a reason for my results to differ from yours? The impression I get from MySQL's website is that their prebuilt binaries include a version of glibc that has been hand-hacked by MySQL AB. So I wouldn't be surprised if that version has some odd behavior with NPTL, given that MySQL clearly don't like NPTL and haven't tried hard to make their code work with it. But I'm not seeing why there'd be a problem when you build from source and use the stock RHEL3 glibc.
I think that the main reason is architecture - I tested both in the same x86/32 bits architecture, on a HP DL360 with dual Xeon 3GHz CPU and Hyperthreading enabled, while you tested it in two entirely different architectures. There may be a racing condition that doesn't appear in 64 bits but affects peformance severely on 32 bits. And again, remember that I compiled it from scratch with the same included .spec in both, RedHat 7.3 and RHEL3 with stock gcc/glibc so I don't think that the theory of the hand-hacked glibc version as a source for the high performance is correct. Is there any way for you to try compiling and testing it on an x86/32bits architecture over RHEL3 and compare results?
On a fairly vanilla x86 system (dual 450MHz PIII) running RHEL3 Update 1, I find it takes about 0.400-0.450 seconds to feed a script containing 1000 update commands to mysql. This seems consistent with the results I get on another machine running RHL 8.0 (0.19 sec, but this is a 1GHz P4) as well as the previously cited AMD64 results. I do not see any effect from setting LD_ASSUME_KERNEL, with either mysqld (statically linked) or mysqld-max (dynamically linked). So I'm still stuck at "can't reproduce the problem". The only remaining variable I can see is that you were testing on dual Xeons. I don't have access to such a machine at the moment (the one in our office is down). Can you reproduce the problem on a non-Xeon machine? How about if you disable hyperthreading and/or use just one CPU?
My machines were dual p3 1.2 Ghz Xenon's, these are pre-HT machines. I will try to write a script that does a simple 1000 inserts into a table instead of running the mysql-bech suite. If possible, could you run the benches on your machine or share your script you used so we can have some faith in the numbers? ;-) As an update to the last post, a rebild of the stock MySQL rpm's (both max and vanilla) in the taroon environemnt yields slightly better performance everywhere but the insert tests which are still slower than I'd like and think should be. At this point I'm also going to look at the disk performance and possible kernel performance issues with the upd-1 kernel as noted on taroon-list and in bug 113851..
# time while (( $i < 1000 )); do mysql < testql; i=`expr $i + 1`;done real 0m6.063s user 0m1.240s sys 0m2.240s mike.cc# # cat testql use test; insert into test.testac set object_id='1',object_title='foo'; # # time while (( $i < 1000 )); do mysql < testql; i=`expr $i + 1`;done real 0m21.207s user 0m0.350s sys 0m1.690s # cat testql use test; #insert into test.testac set object_id='1',object_title='foo'; update testac set object_id=object_id+1; # Uhh.. What do you need me to do to get you workable worthwhile info?
The only difference that I can see between your test and mine is that you are using RHEL3 update 1 while I used the stock RHEL3 AS. I will download and install update 1, recompile MySQL 4.0.16 and test it again. I'll provide feedback here after that.
At first I shall note what the test case itself is flawed, you're not testing "1000 updates" but instead 1000 start_client+connect + update. The update itself it taking very small time here. If you wish to test update speed itself just create single file with 1000 updates. The performance difference in this particular case might come for example from different resolving configuration or some scheduling differences - process which forks 1000 of times in the loop might get some scheduling penalty. I would also recommend testing with --skip-name-resolve option to make sure host name resolve does not play its rolve here. Speaking about MySQL official binaries - they are compiled statically, so they will not use NPTL in any configuration. The main difference in the binaries they are linked to, however is reduced thread_stack size, which is done to allow having more than 1024 threads. NPTL itself should not be the case in this particular case. The benchmarks really show it is slower on some multi user loads, compared to LinuxThreads, even in case of dynamic linking. For single user load as described in this case the performance is almost the same.
I've just tested RHEL AS3 update 1 and the problem is definitively fixed. Now the speed is comparable to what we get on RHL 7.3. What did change between the original RHEL AS3 release and the update 1? Anyway, the problem is fixed.
I have to guess that the problem was really in glibc or kernel threading support, if it went away in update 1. Anyway that's good news, and I'll mark this bug closed.