Red Hat Bugzilla – Bug 112433
MySQL 4.0.16 is about 500 times slower in sequential updates than in RedHat linux 7.3
Last modified: 2013-07-02 22:59:46 EDT
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.
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)
4. Create a batch of 1000 update transactions on that record and run
5. Repeat the same process with RedHat Enterprise AS 3 and RedHat
linux 7.3 timing it.
RedHat Enterprise 3 is 500 times slower on MySQL 4.0.16 for
About the same speed or better than RedHat 7.3.
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
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
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
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
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
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
[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@1.2Ghz with
# 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
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
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
# cat testql
insert into test.testac set object_id='1',object_title='foo';
# time while (( $i < 1000 )); do mysql < testql; i=`expr $i + 1`;done
# cat testql
#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.