I have a Mysql Server, and when I start a very heavy query almost all
the resources of the system are taken by the query. If - during
query's execution - I start a second query (maybe very fast when run
alone...) , it seems to wait the end of the first one, i.e. it seems
that the resources are "dedicated" to the first. There is a way to
"balance" the resources between the two (or more) different
connections? My problem is that some heavy queries are freezing all
the other queries (which usually takes few seconds to be performed): I
would like to "stop" the heavy query, execute the fast query, and than
resume the first one... Is it possible?

Thanks,

Stefano

Stefano <stecrimi@gmail.com> wrote:

> I have a Mysql Server, and when I start a very heavy query almost all
> the resources of the system are taken by the query.

Possibly all *available* resources are taken by mysqld in order to
execute the query. Normally this is exactly what you want. Leaving
some resources idle would effectively slow down operations.

In case of multiple simultaneous queries (and even balancing resources
used by mysqld and other applications) - this is left to the operating
systems scheduler. The MySQL server spawns multiple threads, basically
one thread per active connection. So unless there is a *logical*
conflict between queries, all resources should be evenly shared between
all active server threads.

> If - during
> query's execution - I start a second query (maybe very fast when run
> alone...) , it seems to wait the end of the first one, i.e. it seems
> that the resources are "dedicated" to the first.

This has probably nothing to do with system resources (like CPU cycles
or I/O bandwidth) but with *database* resources. I.e. if one query
just modifies a certain record, other queries cannot modify it at the
same time. In case of MyISAM tables even reading and writing of the
same table(!) is mutually exclusive. InnoDB OTOH is much more relaxed
when it comes to contention.

The phenomenon is called "lock contention" and discussed in many places
in the MySQL manual. I.e. here:

http://dev.mysql.com/doc/refman/5.0/...ng-issues.html

You can easily spot locked threads in SHOW PROCESSLIST.


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

On 13 Ago, 10:49, Axel Schwenke <axel.schwe...@gmx.de> wrote:

> This has probably nothing to do with system resources (like CPU cycles
> or I/O bandwidth) but with *database* resources. I.e. if one query
> just modifies a certain record, other queries cannot modify it at the
> same time. In case of MyISAM tables even reading and writing of the
> same table(!) is mutually exclusive. InnoDB OTOH is much more relaxed
> when it comes to contention.
>
> The phenomenon is called "lock contention" and discussed in many places
> in the MySQL manual. I.e. here:
>
> http://dev.mysql.com/doc/refman/5.0/...ng-issues.html
>
> You can easily spot locked threads in SHOW PROCESSLIST.
>

Thank you for your answer. However, I have checked, and there are no
locks. Nevertheless a query which usually (if run alone) takes 2-3
seconds, can take up to 5 minutes when run together with an "heavy
query" (instead, if the threads have the same "priority", I would have
expected a doubling in execution time, so 4-6 seconds).

I have noticed, using SHOW PROCESSLIST, that there is a very long
starting period (state OPENING TABLE if I'm not wrong...), which
usually is almost zero...

In any case, I would try to switch from MyISAM to InnoDB.

Stefano

Stefano <stecrimi@gmail.com> wrote:
> On 13 Ago, 10:49, Axel Schwenke <axel.schwe...@gmx.de> wrote:
>
>> The phenomenon is called "lock contention" and discussed in many places
>> in the MySQL manual. I.e. here:
>>
>> http://dev.mysql.com/doc/refman/5.0/...ng-issues.html
>>
>> You can easily spot locked threads in SHOW PROCESSLIST.
>
> Thank you for your answer. However, I have checked, and there are no
> locks. Nevertheless a query which usually (if run alone) takes 2-3
> seconds, can take up to 5 minutes when run together with an "heavy
> query" (instead, if the threads have the same "priority", I would have
> expected a doubling in execution time, so 4-6 seconds).
>
> I have noticed, using SHOW PROCESSLIST, that there is a very long
> starting period (state OPENING TABLE if I'm not wrong...), which
> usually is almost zero...

So the "usually-fast-but-now-slow" queries show up in the PROCESSLIST
in state 'Opening Table' instead of 'Locked'?

This might be a problem with I/O contention. Unfortunately I/O
scheduling is much harder than CPU scheduling and IMHO Windows
(this is Windows, right?) is not too good at it.

However 5 minutes are way too much. This could even be a MySQL internal
locking issue (some global lock on table cache oder key buffer).
What MySQL version is it? Old versions (before 4.1) are known to have
deficits in this area.

> In any case, I would try to switch from MyISAM to InnoDB.

This might help even in case of I/O contention, because InnoDB does
much more buffering in memory and reduces I/O a lot. Also InnoDB does
I/O only in few dedicated threads. This I/O pattern seems to be much
better digestible by Windows :-)


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/