One Running Query Blocks Other Connection Windows

[vygi]

I have big tables with lots of data (up to 30GB+), so some qeries aren't fast and take 5-10-20 minutes and even longer to finish.

Worse thing: when I start such two slow queries in parallel on two separate databases, SQLyog does not “release” grayed-out windows and dos not show any results at all as long as at least one query is running.

Example:

– start query A on server A

– start query B on server B

– few minutes later I can see via SHOW PROCESSLIST that query A is finished but both connections in SQLyog are still blocked

– only when also B is finished, SQL releases both windows and show both results.

Same deal with three queries: started in parallel, the two faster queries are waiting until the slowest one is finished.

Vygi

[peterlaursen]

@vygi

is this a new issue with version 6.0, or did you only get across that recently?

[peterlaursen]

In 'the first shot' we cannot reporduce this.  Every query displays when finished and other queries are running.

I wonder if your system is getting close to full memory use?

[vygi]

peterlaursen wrote on Jun 5 2007, 01:39 PM:

is this a new issue with version 6.0, or did you only get across that recently?

I am not sure about it but I think I have sometimes experienced it before.

peterlaursen wrote on Jun 5 2007, 01:59 PM:

In 'the first shot' we cannot reporduce this.  Every query displays when finished and other queries are running.

I wonder if your system is getting close to full memory use?

My PC has 512 MB physical memory, 70MB are free right now, but it often uses some virtual memory as I run many parallel processes.

I don't see any significant slow-down, though.

Looks like it only happens when the 2nd query takes longer than the 1st one:

If I start 1st and then 2nd — SQLyog displays 2nd result and continues with 1st if 2nd is quicker, but waits for 2nd if 1st is faster.

I hope I can test more today.

[peterlaursen]

OK .. we will have to test more in depth too.

but repainting in the RESULT tab involves Win32 API calls.  So I just wondered if it was Windows itself who 'waited'.

[vygi]

peterlaursen wrote on Jun 6 2007, 08:37 AM:

OK .. we will have to test more in depth too.

but repainting in the RESULT tab involves Win32 API calls.  So I just wondered if it was Windows itself who 'waited'.

Don't know… but it would be strange if Windows would wait several minutes to repaint.

Note that in same time I can easily switch to any other application with no problems.

I also can open new (let's say the 3rd) connection (Ctrl-M dies not work but “File / New Connection…” does) and perform here new queries with absolutely no delays. In the meantime, 1st SQLyog connection (with long ago finished query) will still wait for the 2nd.

[peterlaursen]

please tell – how do yo uexecute queries simultaneously?  From different connection windows?

[vygi]

peterlaursen wrote on Jun 6 2007, 09:46 AM:

please tell – how do yo uexecute queries simultaneously?  From different connection windows?

So I have two servers with two huge databases, and two connection windows to them.

1: I start query A on server A. It takes let's say 2 minutes

2: 30 sec later I switch to connection B and start query B on server B; it takes 5 minutes.

Now I have to wait 5 minutes until the B query is finished. SQLyog does remains “busy” on A connection until then, although query A finishes in between.

I saw that there is no such problem if A query takes longer and B is quick.

[peterlaursen]

this was also how I understood.  Just wanted to be perfectly sure.

[vygi]

Thanks;

and I just want to say that I can perfectly reproduce it like described above with two MySQL 5.0 servers and SQLyog 6.0 RC2, on German WinXP Professional SP2.

[Author]

Posts