Bug #30760 mysql 5.0.45 is slower than 5.0.41 and more than 5.0.33
Submitted: 2 Sep 2007 3:30 Modified: 10 Feb 2008 5:28
Reporter: GIovanni M Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S5 (Performance)
Version:5.0.45 OS:Linux
Assigned to: CPU Architecture:Any
Tags: 5.0.45 slow, regression

[2 Sep 2007 3:30] GIovanni M
Description:
i have to report the same error again, but now with 5.0.45

history:

I have:
 Server: Dell PowerEdge 2900, 4GB RAM, Xeon 3 GHz Dual Core, 3 HD SAS 10K RPM (1 for os, 1 for databases, 1 extra)
OS: Linux Debian etch 4.0r1

I control the water payments in a big city en Mexico using mysql. My project started on january with mysql 5.0.33 under linux, after i migrated to 5.0.41 and now to 5.0.45 but i had problems in mysql performance:

1).- MySQL 5.0.33 execute the next statement very well and fast:

  select _locality.loc, _locality.name, count(*) deudores,_locality.zon
  from
  _locality
  left join _accs on _locality.loc=_accs.loc
  where  _accs.acctyp=1  and (_accs.cfg & 1)=0 and  _accs.nodhousings=0
  and not exists(select * from _invoices where _invoices.acc=_accs.acc and tob>=20072 and (_invoices.cfg & 32)=0 )
group by loc order by deudores desc;

2).- But MySQL 5.0.41 DON'T!!! unless i use that statement into a  subquery:

select dbn.*, count(*) padron from
(
select loc, name, count(*) deudores, zon
from
(
  select _locality.loc, _locality.name, _locality.zon
  from
  _locality
  left join _accs on _locality.loc=_accs.loc
  where  _accs.acctyp=1  and (_accs.cfg & 1)=0 and  _accs.nodhousings=0
  and not exists(select * from _invoices where _invoices.acc=_accs.acc and tob>=20072 and (_invoices.cfg & 32)=0 )
) as withnopays
group by loc
) as dbn
left join _accs on dbn.loc=_accs.loc group by dbn.loc
 order by deudores desc;

3).- And now i find that MySQL 5.0.45 can execute both SQL statements. Thanks for solve that problem. BUT NOW the the server TAKE TOOO TIME to returno the query. (toooo.... mysql 5.0.33 30 seconds aprox, 5.0.41 the same, 5.0.45 270!!! SECONDS??  WHY!!!! 

Help me please, i need to process some executive information with less time like mysql 5.0.33/41.

Notes:
a) All cases using the same amount of information.
_locality:       700 records
_accs:       340,000 records
_invoices: 1,430,000 records

b) I used good indexes. (let me say that i added more indexes to 5.0.45 in order to improve the performance but it did't help since there are another indexes).

c)I noted that linux command #mpstat and #free report that server was busy up to 40% while mysql was executing the queries. It didn't happen with mysql 5.0.33/41.

d)I all cases i use my-huge.ini as base.

e)I observe that if i try again the query is fast, but the first take too time.

f) I compiled mysql (5.0.33/41/45) in the same way, except than in 5.0.41 i commented the line #NPTL_PTHREAD_EXIT_BUG in my_thr_init.c since i can't create de initial tables, but it's no problem for 5.0.45 (i think so). My compiling line include with-big-tables, -with-extra-charsets=none...static.... IN ORDER to optimise the mysql speed.

Too thanks for support.

How to repeat:
Prove my statements.
[2 Sep 2007 5:36] Valeriy Kravchuk
Thank you for a problem report. Please, send the results of EXPLAIN for the queries on your real data from 5.0.33, 5.0.41 and 5.0.45.
[9 Dec 2007 7:41] Valeriy Kravchuk
All plans are the same on all 3 versions... I do not think this is optimizer's bug.

Anyway, please, try to repeat with a new version, 5.0.51, and inform about the results.
[9 Dec 2007 7:43] Valeriy Kravchuk
You can upload your test case to ftp://ftp.mysql.com/pub/mysql/upload/. Please, include bug number, 30760, into a filename. Send comment here when done.
[10 Jan 2008 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[11 Feb 2008 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".