RE: Bug 712, 914..... and now 1027

Like a lot of others I have been investigating the work involved in
migrating our company and clients servers to a MySQL 4.0 base due to MySQL
4.0's improved fulltext indexing and replication.  But the recent issues
bought to my attention via the bug reports submitted by Paul Coldrey have
left me reconsidering my decision to upgrade and, indeed, reconsidering my
choice of open source database product.

Alexander Keremidarski indicated that he can not repeat the issue bought to
light by Paul's test case (bug #1027), yet he has not once attempted to run
the test case on RedHat 7.3 (nor even on MySQL 4.0.14) which the bug report
calls for as he considers testing performance issues between RedHat releases
to be 'irrelevant'.  

Irrelevant to whom I may ask?  Certainly not irrelevant to those of us
enjoying current performance on RH 7.3 who have now chosen to upgrade to RH
9.0.  I would not consider an upgrade from MySQL 3.23.49, RedHat 7.3 to
MySQL 4.0.14, RedHat 9.0 an unusual case, nor one where you would expect to
get your fingers burned yet I have run the test case provided by Paul and
quite easily reproduced the poor performance he has uncovered.

Using identical hardware (previously a master/slave backup system) I ran
BOTH test cases provided by Paul Coldrey on MySQL 3.23.49, RedHat 7.3 and
MySQL 4.0.14, RedHat 9.0 and can verify that I received similar poor
performance easily and repetedly.

Under MySQL 4.0.14, RedHat 9.0 both insert.sh and insert.sql took around 3
minutes and 30 seconds to insert 10,000 rows into an un-ndexed MyISAM table.

Under MySQL 3.23.49, RedHat 7.3 insert.sh took around 1 minute 30 seconds
while insert.sql took a mere 4 seconds.

Given the test cases insert a pitiful 10,000 rows into an un-indexed MyISAM
table which of the following does the MySQL team consider acceptable?

 1) The 3 minutes 30 seconds I experienced under MySQL 4.0.14, RedHat 9.0 ?

 2) The 1 minute 45 seconds Alexander Keremidarski experienced under MySQL
4.0, RedHat 9.0 ?

 3) The 4 seconds I experienced under MySQL 3.23.49, RedHat 7.3 ?

As Alexander rightly points out Pauls test case demonstrates that MySQL
performs differently on different versions of RedHat which is
understandable.  But what baffles me is why when such a marked DECREASE in
performance is bought to the attention of the MySQL developers is it
dismissed as an OS issue and investigated no further?  Even if it is a
RedHat 9.0 issue it manifests itself most obviously in MySQL which will
leave the majority of users blaming MySQL for the bad performance.

Be it a MySQL issue, be it a Red Hat issue, whatever it is, isn't it
important to the MySQL developers to maintain performance on releases
regardless of operating system version?  Its not like we're comparing RedHat
9.0 with Windows 98 here.

When my clients come to me asking to upgrade their servers what advice shall
I give? That the MySQL development team acknowledges that "different
versions of MySQL appear to run differently on different versions of RedHat"
but refuses to do anything about it so stick with MySQL 3.23.49 on RedHat
7.3????

In understand Paul Coldrey's frustration completely.  This is an issue that
warrants serious attention and unless there is a solid commitment by the
MySQL development team to ensure no performance degradation between releases
regardless of operating system versions all of us who recommend MySQL
products to our clients are putting our reputation on the line each time we
do so ... and thats something I am not prepared to do.

Yes, different versions of MySQL do run differently on different versions of
RedHat.  But when upgrading both MySQL and the OS one expects an increase
in, or similar performance not a decrease, and to say openly that is not an
issue is a slap in the face to all the developers who have loyally supported
your product over the years.

  Byron Kidd.

-----Original Message-----
From: Alexander Keremidarski [mailto:[email protected]]
Sent: Tuesday, 12 August 2003 11:14 AM
To: Paul Coldrey
Cc: [email protected]
Subject: Re: Bug 712, 914..... and now 1027


Paul,

Paul Coldrey wrote:
> Hi Alexander,
<cut>

> The simple fact is that I have some code that runs really poorly on both 
> MyISAM tables and InnoDB when using 4.0.14/RedHat 9.0 and runs quickly 
> on 3.23.49/RedHat 7.3 - I believe the test case I have sent in will show 
> this.... I think you should at least give it a try..... after all it's 
> more than a 60x slow down, that's pretty hard to miss.

Can you please explain how this is supposed to be MySQL bug?

> I believe this is the main source of my problems, the fact that in 
> tracking it down I have isolated two other bugs perhaps warrants some 
> reflection.

What are these two other Bugs you have isolated? For #712 yes you did.

For #914 I can't agree you have provided repeatable test case which exposes
any Bug.

You initially talked about "Slower INSERTs" which turned out into using bad 
sequence of DELETE, INSERT, DELETE, INSERT which effectively prevents
concurrent 
SELECTs during INSERT. This is known, documented and easily avoidable
limitation 
of concurrent INSERTs with MyISAM tables.

http://www.mysql.com/doc/en/MyISAM.html
7.1 MyISAM Tables
...
You can INSERT new rows in a table that doesn't have free blocks in the
middle of 
the datafile, at the same time other threads are reading from the table 
(concurrent insert). A free block can come from an update of a dynamic
length row 
with much data to a row with less data or when deleting rows. When all free
blocks 
are used up, all future inserts will be concurrent again.


> [24 Jul 5:32pm] Paul Coldrey
> ...
> . When permissions are rebuilt the insert in mysqlbug2 happens and insert
around
> 120,000 rows. All these rows have the same SequenceId. Once the new
permissions
> have been fully inserted the previous permissions (with a lower
SequenceId) are
> deleted.


After that you switched to:


> [4 Aug 6:10am] Paul Coldrey
> 
> As a quick update for anyone who may be working on this.... it appears the
> problem may be related to RedHat 9.0. Firstly I moved all the tables that
might
> suffer from concurrency issues to InnoDB and this fixed some problems but
the
> speed was still dismal.
> 
> I then installed an old machine with RedHat 7.3 and did some tests with
both
> 4.0.14 and 3.23.57. Performance is similar for both versions.


Later you wrote:

> [7 Aug 9:27pm] Paul Coldrey
> ...
> Sadly, (at least in the interests of getting this fixed!) from the
command-line
> I couldn't replicate the problem! Hence, it's only slow when run from PHP
4.3.2
> on RedHat 9 with any MySQL 4.0 or 4.1.....

Later you provided vmstat output without where it came from - 3.23 on RH
7.3, 3.23 
on RH9, 4.0 on RH7.3 or 4.0 on RH9


All you are saying so far in this Bug report is that MySQL is slower on
RedHat 9 
than on RedHat 7.3

Yes different versions of MySQL appears to run differently on different
versions 
of RedHat.

Please explain how can we consider this MySQL bug when none of your tests so
far 
can demonstrate degradation in speed between 3.23 and 4.0 when both are
running 
under same RedHat version?

Send us please test case which demonstrates difference in speed between
MySQL 3.23 under RedHat RedHat 7.3 and MySQL 4.0 under RedHat 7.3
and also test case which demonstrates difference in speed between
MySQL 3.23 RedHat 9 and MySQL 4.0 on RedHat 9

Anything else is irrelevant.

Test case you provided for #1027 runs at same speed when both 3.23 and 4.0
are 
running under RedHat 9

Here it is:

3.23.58
date; mysql < inserts.sql; date
Tue Aug 12 04:28:32 EEST 2003
Tue Aug 12 04:28:45 EEST 2003

= 13 seconds

4.0.15
# date; mysql < inserts.sql; date
Tue Aug 12 04:32:03 EEST 2003
Tue Aug 12 04:32:18 EEST 2003

= 15 seconds

Considering that machine runs several other processes I don't agree this is
noticeable difference

Yes 4.0.15 appears to be slower, but not 60 times slower.

3.23.58
date; mysql < ./inserts.sh; date;

Tue Aug 12 04:33:41 EEST 2003
Tue Aug 12 04:34:50 EEST 2003
date; mysql < ./inserts.sh; date;

= 1 min 09 seconds

4.0.15
date; mysql < ./inserts.sh; date;

Tue Aug 12 04:38:01 EEST 2003
Tue Aug 12 04:39:11 EEST 2003

= 1 min 10 seconds



Please note that I don't say there is no such bug in MySQL! What I am saying
is 
that your report does not demonstrate how to repeat this bug.
Without being able to repeat it we can't test it nor we can fix it.


Note also that none of our binary is optimized for RedHat 9

RedHat 9 is only major Linux distribution so far which implements both NPTL
and 
LinuxThreads.

NPTL is neither stable nor standard yet. That is why we don't build
NPTL-optmized 
binaries. We can't afford doing this before we have tested NPTL thoroughly.

Best regards

-- 
  Are you MySQL certified? -> http://www.mysql.com/certification
  For technical support contracts, visit https://order.mysql.com/?ref=msal
     __  ___     ___ ____  __
    /  |/  /_ __/ __/ __ \/ /    Mr. Alexander Keremidarski
<[email protected]>
   / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Full-Time Developer
  /_/  /_/\_, /___/\___\_\___/   Sofia, Bulgaria
          <___/  www.mysql.com




-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]

-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]