Re: Bug 712, 914..... and now 1027 - Alexander Keremidarski - com.mysql.lists.bugs

10 messages in com.mysql.lists.bugsRe: Bug 712, 914..... and now 1027

From	Sent On	Attachments
Paul Coldrey	11 Aug 2003 00:42
Alexander Keremidarski	11 Aug 2003 03:35
Alexander Keremidarski	11 Aug 2003 18:43
bki...@sola.com.au	11 Aug 2003 22:09
Sergei Golubchik	12 Aug 2003 02:18
Alexander Keremidarski	12 Aug 2003 03:18
Alexander Keremidarski	12 Aug 2003 23:49
Tabor J. Wells	13 Aug 2003 07:04
Jim Smith	13 Aug 2003 07:18
Alexander Keremidarski	13 Aug 2003 09:11

Subject:	Re: Bug 712, 914..... and now 1027
From:	Alexander Keremidarski (sal...@mysql.com)
Date:	08/11/2003 06:43:20 PM
List:	com.mysql.lists.bugs

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