Bug 112433 - MySQL 4.0.16 is about 500 times slower in sequential updates than in RedHat linux 7.3
MySQL 4.0.16 is about 500 times slower in sequential updates than in RedHat l...
Status: CLOSED CURRENTRELEASE
Product: Red Hat Enterprise Linux 3
Classification: Red Hat
Component: mysql (Show other bugs)
3.0
i686 Linux
medium Severity high
: ---
: ---
Assigned To: Tom Lane
:
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2003-12-19 12:12 EST by Flavio Villanustre
Modified: 2013-07-02 22:59 EDT (History)
6 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2004-04-09 10:08:25 EDT
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)
mysql benhmark compare (6.55 KB, text/plain)
2004-01-23 15:16 EST, nathan r. hruby
no flags Details

  None (edit)
Description Flavio Villanustre 2003-12-19 12:12:39 EST
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:
Comment 1 Patrick Macdonald 2003-12-19 12:26:28 EST
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)?
Comment 2 Flavio Villanustre 2003-12-19 12:41:29 EST
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.
Comment 3 Flavio Villanustre 2003-12-26 10:35:45 EST
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."
Comment 4 nathan r. hruby 2004-01-23 10:05:13 EST
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 :)
Comment 5 Flavio Villanustre 2004-01-23 10:23:37 EST
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.
Comment 6 nathan r. hruby 2004-01-23 10:27:41 EST
Dumb question, did you try the MySQL built binaries or any
optimizations to work around these problems?
Comment 7 Flavio Villanustre 2004-01-23 10:31:20 EST
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.
Comment 8 Tom Lane 2004-01-23 11:38:01 EST
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.
Comment 9 Flavio Villanustre 2004-01-23 11:47:09 EST
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.
Comment 10 nathan r. hruby 2004-01-23 11:53:28 EST
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"?
Comment 11 Tom Lane 2004-01-23 12:35:36 EST
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.
Comment 12 Flavio Villanustre 2004-01-23 13:55:12 EST
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
Comment 13 nathan r. hruby 2004-01-23 15:16:05 EST
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
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.
Comment 14 Tom Lane 2004-01-29 18:28:40 EST
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.
Comment 15 Flavio Villanustre 2004-01-29 19:49:03 EST
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?
Comment 16 Tom Lane 2004-02-06 14:14:58 EST
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?
Comment 17 nathan r. hruby 2004-02-06 14:35:56 EST
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.. 
Comment 18 nathan r. hruby 2004-02-06 16:17:41 EST
# 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?
Comment 19 Flavio Villanustre 2004-02-07 16:29:46 EST
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.
Comment 20 Peter Zaitsev 2004-02-16 14:48:42 EST
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. 
Comment 21 Flavio Villanustre 2004-02-17 14:50:33 EST
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.
Comment 22 Tom Lane 2004-04-09 10:08:25 EDT
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.

Note You need to log in before you can comment on or make changes to this bug.