Queries locking the database

MySQL & MariaDB Other MySQL® Questions
1

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

2

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.

3

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

4

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.

5

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.

6

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.

7

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

8

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 ?

9

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.

10

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

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.

11

That is 100% correct. )

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 :o BUT I learned alot through this mistake…