How to kill locked queries

--0-2131562642-1287039683=:70316
Content-Type: text/plain; charset=us-ascii

All,

Is there a mysql configuration to kill queries that have been locked for quite
some time. If there's none what is an alternative approach to kill these locked
queries and what is the root cause of it?

Thanks,
Mon



--0-2131562642-1287039683=:70316--

Re: How to kill locked queries

--90e6ba6e81b87c70b704928e7721
Content-Type: text/plain; charset=ISO-8859-1

The root cause is another query that has tables locked that your "locked"
queries want. Behind that may be, for example, an inefficient but
often-executed query, high I/O concurrency that has a cumulative slowing
effect, or maybe simply a long-running update that might be better scheduled
during the quiet hours.

You can kill the "locked" queries, but it is likely that more will simply
appear in their place. You can kill the evil monster query, but depending on
what's actually going on it's quite possible that one of the waiting ones
will take it's place as the resource hog.

Sometimes a quick fix is to simply restart the service, if there's just too
much competition for the same locks; but obviously the problem will just
reappear later.

What you need to do is figure out which query is holding the locks, and see
if you can optimize and/or reschedule it.


On Thu, Oct 14, 2010 at 9:01 AM, monloi perez wrote:

> All,
>
> Is there a mysql configuration to kill queries that have been locked for
> quite
> some time. If there's none what is an alternative approach to kill these
> locked
> queries and what is the root cause of it?
>
> Thanks,
> Mon
>
>
>




--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--90e6ba6e81b87c70b704928e7721--

Re: How to kill locked queries

--001636426a03aafeb004928e7dd2
Content-Type: text/plain; charset=ISO-8859-1

Hi Mon,

Killing locked queries is not the first step in database tuning.
Queries locked for a long time usually depend on slow updates that lock
other updates or selects,
this happen on MyISAM (or table level locking engines).
If you are really sure you want and can without problems kill the queries
you can do a simple job to check and kill.
I would rather check why those queries are locked anyway.

Aloha

Claudio


2010/10/14 monloi perez

> All,
>
> Is there a mysql configuration to kill queries that have been locked for
> quite
> some time. If there's none what is an alternative approach to kill these
> locked
> queries and what is the root cause of it?
>
> Thanks,
> Mon
>
>
>




--
Claudio

--001636426a03aafeb004928e7dd2--

Re: How to kill locked queries

--0-492304054-1287040766=:12904
Content-Type: text/plain; charset=us-ascii

Does this happen if your table is InnoDB?

Thanks all,
Mon



________________________________
From: Claudio Nanni
To: monloi perez
Cc: mysql mailing list
Sent: Thu, October 14, 2010 3:16:38 PM
Subject: Re: How to kill locked queries

Hi Mon,

Killing locked queries is not the first step in database tuning.
Queries locked for a long time usually depend on slow updates that lock other
updates or selects,
this happen on MyISAM (or table level locking engines).
If you are really sure you want and can without problems kill the queries you
can do a simple job to check and kill.
I would rather check why those queries are locked anyway.

Aloha

Claudio



2010/10/14 monloi perez

All,
>
>Is there a mysql configuration to kill queries that have been locked for quite
>some time. If there's none what is an alternative approach to kill these locked
>queries and what is the root cause of it?
>
>Thanks,
>Mon
>
>
>


--
Claudio




--0-492304054-1287040766=:12904--

Re: How to kill locked queries

--20cf301d3a4c5310d704928ea74a
Content-Type: text/plain; charset=ISO-8859-1

On Thu, Oct 14, 2010 at 9:19 AM, monloi perez wrote:

> Does this happen if your table is InnoDB?
>


That depends on the type of lock. If no lock type is specified, InnDB will
prefer row locks, while MyISAM will do table locks.

That may help, unless all your queries are trying to access the same rows
anyway :-) You really need to figure out *what* is being locked and *why*
before you can fix it.

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--20cf301d3a4c5310d704928ea74a--

Re: How to kill locked queries

On Thu, Oct 14, 2010 at 3:28 AM, Johan De Meersman wrote:
> That depends on the type of lock. If no lock type is specified, InnDB will
> prefer row locks, while MyISAM will do table locks.
>
> That may help, unless all your queries are trying to access the same rows
> anyway :-)

Even that can work without locking in InnoDB if only one query is
trying to modify the rows. Unlike MyISAM, readers do not block
writers in InnoDB and vice versa.

- Perrin

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org