MySQL Performance: MySQL => Queries locking the database

Members

Help

Home

Home » Performance » MySQL » Queries locking the database

Show: Today's Messages :: Show Polls :: Message Navigator

Queries locking the database [message #139]

Wed, 06 September 2006 06:42

geelwortel
Messages: 25
Registered: September 2006

Junior Member

After a HUGE performance loss on MySQL version 5 we moved back to version 3. The main problem we have now is that one of the queries are locking the database every time it is running. The query is:
Select count(*) as NoRec from gen_Main Left Join con_History ON (gen_Main.WKey = con_History.WKey) Where con_History.RecActive = 'y' And con_History.MOID = 1 And con_History.DepId =14

Can someone please help

Report message to a moderator

Re: Queries locking the database [message #140 is a reply to message #139 ]

Wed, 06 September 2006 06:46

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

Heh, That is a drop. Why did not you go to 4.0 or 4.1 ? Or were you leaping from 3.23 straight to 5 ?

What do you mean by locking the database all the time ?

I would suggest to run EXPLAIN for the query both on 3.23 and 5.0 to see if it is executed differently.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: Queries locking the database [message #141 is a reply to message #139 ]

Wed, 06 September 2006 07:00

geelwortel
Messages: 25
Registered: September 2006

Junior Member

Hi Peter

We jumped from 3.23 to 5 and back again in the space of a month and a half. We tried 4.1 in December 2005 with the same results, the server was VERY slow and it had a big impact on our business.

The query locks the tables that it is working on. I look at the database through MySQL administrator and I can see that the users connection that runs this query is selectting but all the other connections is "locked" in status. This stops the users from accessing the data.

A bit of background info on the server and type of applications running on this server. The db server is accessed by +- 50 users on the local network with a program developed in C++. Intranet user also access the same server and databases with internet applications wrote is .net/asp. The problem comes in when the Intranet users access the same database that the C++ users access

Report message to a moderator

Re: Queries locking the database [message #142 is a reply to message #141 ]

Wed, 06 September 2006 07:11

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

First, I would check what exactly happened with 5.0 or 4.1 upgrade. 3.23 is very old and you will need to move from it sooner or later as it is pretty much unsupported any more. Ie no security fixes will be included.

Ie I would check different queries separately and see which of them became to run slower. Do not hope 5.2 or whatever will magically solve your problem.

How does EXPLAIN looks for this query on 3.23 and 5.0 ?

Also let me know if you're looking for professional help with your application.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: Queries locking the database [message #266 is a reply to message #142 ]

Thu, 19 October 2006 21:28

spud
Messages: 2
Registered: October 2006

Junior Member

i remember something related when i migrated to mysql 4.1,

can you try it?

SELECT count(*) as NoRec FROM gen_Main gm LEFT JOIN con_History ch ON (gm.gen_Main.WKey = ch.WKey) WHERE ch.RecActive = 'y' And ch.MOID = 1 And ch.DepId =14

and, do yu really need left join? use join if you don't Wink

Report message to a moderator

Re: Queries locking the database [message #285 is a reply to message #139 ]

Wed, 25 October 2006 04:24

geelwortel
Messages: 25
Registered: September 2006

Junior Member

Hi Peter and spud. Thanx for you replies.
spud
The Developers need the left joins and cant' go with joins.
Peter
We are working with mysql to solve our problems before we go to v5.x.
Peter and spud
We solved the problem by adding a couple of new indexes to the db. This made a HUGE difference. The query was running in +- 4 mins, it's now running in +- 10 secs.

Report message to a moderator

Re: Queries locking the database [message #286 is a reply to message #285 ]

Wed, 25 October 2006 04:48

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

Yeah, Adding indexes often improves performance.
However it does not explain why did you get regression by moving to MySQL 5.0

Anyway it is great the problem is now solved.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: Queries locking the database [message #288 is a reply to message #139 ]

Wed, 25 October 2006 06:06

geelwortel
Messages: 25
Registered: September 2006

Junior Member

Hi Peter
It looks like we solved the v5.x performance problem with the help of Mysql support team. We had alot of connection and connection lost issues but we added the skip-name-resolve command to my.cnf file and now mysql is flying. Let me just say it wasn't a mysql problem but rather a network issue.

Andrew

Report message to a moderator

Re: Queries locking the database [message #290 is a reply to message #288 ]

Wed, 25 October 2006 09:58

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

I see good you found the problem.

It is still strange why it would be triggered by upgrade to 5.0
Or did you have it on different host with different settings ?

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: Queries locking the database [message #294 is a reply to message #139 ]

Thu, 26 October 2006 01:55

geelwortel
Messages: 25
Registered: September 2006

Junior Member

We installed a new OS(Gentoo) but used the same hardware. The name lookup problem was from the OS out to the workstation. Workstations connected to the ip addr. instantaniously but the OS could not find the workstations that quickly. MySQL was running fine in the background it just didn't received the queries quick enough to process them.

Report message to a moderator

Re: Queries locking the database [message #295 is a reply to message #294 ]

Thu, 26 October 2006 09:54

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

I guess you just combined connection creation and query processing in your description Smile

Host name resolve may slow down connection speed but it has no affect on query execution - hostname is not resolved when queries are executed, only on connection stage.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: Queries locking the database [message #296 is a reply to message #139 ]

Thu, 26 October 2006 11:06

geelwortel
Messages: 25
Registered: September 2006

Junior Member

That is 100% correct. Smile

We solved 2 problems with one issue. It started as slow queries which was fixed with indexes and the connection problems was fixed with skip-name-resolve.

I must say it was a newbie thing, if something doesn't work the complete package is crap Embarassed

BUT I learned alot through this mistake...

Report message to a moderator

Previous Topic:	performance of query for normalized DB
Next Topic:	Order of columns in indexes

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Tue Jan 6 23:21:09 EST 2009

Total time taken to generate the page: 0.02502 seconds

.:: Contact :: Home :: MySQL Support by Percona.com ::.