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]