Bug 437098 - poor performance with multiple queries
poor performance with multiple queries
Status: CLOSED CURRENTRELEASE
Product: Red Hat Enterprise Linux 5
Classification: Red Hat
Component: mysql (Show other bugs)
5.1
All Linux
medium Severity medium
: rc
: ---
Assigned To: Tom Lane
:
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2008-03-12 09:24 EDT by Adam Stokes
Modified: 2013-07-02 23:17 EDT (History)
5 users (show)

See Also:
Fixed In Version: 5.0.45-7
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2008-05-25 20:52:06 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)

  None (edit)
Description Adam Stokes 2008-03-12 09:24:15 EDT
Description of problem:
The Customer is experiencing problems with MySQL  and can relate to the MySql
bug below:
Bug #15815   Very poor performance with multiple queries running concurrently
-> http://bugs.mysql.com/bug.php ?id=15815
The problem is:  Query starts to take significantly longer if multiple queries are
executed at once.

Version-Release number of selected component (if applicable):
5.0.45

How reproducible:
100%

Steps to Reproduce:
CREATE TABLE `b` (
  `child_id` int(10) unsigned NOT NULL default '0',
  `b` char(20) default NULL,
  KEY `child_id` (`child_id`)
) ENGINE=InnoDB

Populate   table with 100.000+ of rows, but make sure it still fits in buffer pool.

insert into b (child_id) values (0);
insert into b (child_id) values (0);
insert into b (child_id) values (0);
insert into b (child_id) values (0);
insert into b (child_id) values (0);

insert into b select * from b;
....
insert into b select * from b;    (repeat while you get enough rows)

Create 1.sql file:

use test;
select sql_calc_found_rows  * from b limit 5;
select sql_calc_found_rows  * from b limit 5;
select sql_calc_found_rows  * from b limit 5;
select sql_calc_found_rows  * from b limit 5;
select sql_calc_found_rows  * from b limit 5;

Run 1 copy:

create 1.sh:

cat 1.sql | time mysql  >  /dev/null

Run 4 copies: 

create 4.sh:

./1.sh &
./1.sh &
./1.sh &
./1.sh &

Run 1.sh and 4.sh to see what happens for 1 and 4 connections.
  
Actual results:
performance drops, cpu usage increases on 1 cpu.

Expected results:
increased performance and utilize all cpus

Additional info:
http://bugs.mysql.com/bug.php?id=15815
This changeset contains necessary patches to fix innodb scaling
http://mysql.bkbits.net:8080/mysql-5.0/?PAGE=patch&REV=4552a85ddHCqJ_0Ar7GHnQkDE54iAg

Thanks
Comment 1 Denis Brækhus 2008-05-08 07:34:28 EDT
This also applies to RHEL5.1 (stable) with all updates. 

Why is this bug given a "low" severity? It currently makes any Red Hat shipped
version of MySQL practically unusable for any scenario where InnoDB is used. 

This bug forces us to use mysql.com provided community-releases as it is. I was
hoping it would be resolved in RHEL5.2, but if this still applies to the package
currently included in 5.3 my hopes are not up.

Some priority would be welcome.

Regards
Denis
Comment 2 Adam Stokes 2008-05-08 08:19:33 EDT
Denis,

Thanks for the update -- Ive raised the severity/priority flags. May I also
suggest that if you have a support contract with Red Hat that you file a support
request referencing this BZ as well?

Thank you,
Adam
Comment 3 Denis Brækhus 2008-05-08 08:48:23 EDT
Adam,

I have already done this, is it enough to include a link to this BZ bugid in the
description?

Regards
Denis
Comment 4 Adam Stokes 2008-05-08 08:58:16 EDT
Hi Denis,

That should be enough -- our engineers will triage that support request up to
the appropriate backline engineers to be handled.

Thank you for your support.

Adam
Comment 5 Tom Lane 2008-05-08 10:15:26 EDT
If you're seeing this in 5.0.45 then that upstream fix was already applied, and you're going to need to talk 
to MySQL AB about the problem --- fixing such things is far beyond my knowledge of that code.
Comment 6 Denis Brækhus 2008-05-08 10:23:44 EDT
I am sorry about the misunderstanding here. I do not see this behaviour with
5.0.45, but with 5.0.22 which is what RHEL5.1 ships. 

If this means I should create a separate bug instead, then I will proceed to do so.

Again sorry.
Denis
Comment 7 Tom Lane 2008-05-08 11:04:45 EDT
5.0.45 is scheduled to go out in RHEL 5.2, so you just need to wait a bit ...
Comment 8 Riaan van Niekerk 2008-05-10 09:20:49 EDT
Can someone with the necessary permissions please change the Version field from 
5.3 to 5.1. That way it will not mislead any more people (like what happened in 
Comment #1).
Comment 9 Tom Lane 2008-05-25 20:52:06 EDT
Fixed in RHEL 5.2 update.

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