We have a few sql statements that take up to 10 minutes to run.
they are run like
INSERT INTO blah (SELECT...
and that Select probably has a join or two...

Tables are MyISAM.

when this is running my whole website goes down as the login page has a
select that has to wait for that big query to finish.

What I would like to do, is the following
1. put a WRITE lock on the table so other selects will work
2. run the statement without SELECT locking
3. remove the write lock


so my 2 questions are:
1. Is there a way to have select Not lock the table.
2. while the write lock prevent my insert from working, or will it still
go because it was in the same session?

> We have a few sql statements that take up to 10 minutes to run.
> they are run like
> INSERT INTO blah (SELECT...
> and that Select probably has a join or two...
>
> Tables are MyISAM.

Well, this could be due to the fact that the SELECT returns a huge
number of records, or to the fact that the SELECT itself is slow. I'd
start with taking a good look at the SELECT part and seeing if I can
simplify it or speed it up with an index.


Best regards

On Fri, 10 Mar 2006 23:16:18 +0100, Dikkie Dik wrote:

>> We have a few sql statements that take up to 10 minutes to run.
>> they are run like
>> INSERT INTO blah (SELECT...
>> and that Select probably has a join or two...
>>
>> Tables are MyISAM.
>
> Well, this could be due to the fact that the SELECT returns a huge
> number of records, or to the fact that the SELECT itself is slow. I'd
> start with taking a good look at the SELECT part and seeing if I can
> simplify it or speed it up with an index.
>
>
> Best regards

There's not doubt about it, it's a query thats going to run for a long
time. The select has been optimized as much as we could, and indexes have
already been put in place(and helped tremendously).
The query processes anywhere from 50,000 to 2,000,000 records. We are
working on ways to lower that number a lot, but in the near future were
going to need to be able to push in well over 2mil records.

Originally I had thought having do individual inserts, so that other
processes could get in too. Unfortunately this is not an option.

Arty <please.no.mail@domain.tld> wrote:
> We have a few sql statements that take up to 10 minutes to run.
> they are run like
> INSERT INTO blah (SELECT...
> and that Select probably has a join or two...
>
> Tables are MyISAM.
>
> when this is running my whole website goes down as the login page has a
> select that has to wait for that big query to finish.

That could easily be fixed by doing

CREATE TEMPORARY TABLE foo ...
INSERT INTO foo SELECT ...
INSERT INTO blah SELECT * FROM foo
DROP TABLE foo

That way you would decouple the SELECT and the INSERT. However the
table you SELECT from will be locked for writes anyway. Probably you
should switch to a storage engine that implements multiversioning.
Currently this reads: switch to InnoDB.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

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

On Mon, 13 Mar 2006 17:48:51 +0100, Axel Schwenke wrote:

> Arty <please.no.mail@domain.tld> wrote:
>> We have a few sql statements that take up to 10 minutes to run.
>> they are run like
>> INSERT INTO blah (SELECT...
>> and that Select probably has a join or two...
>>
>> Tables are MyISAM.
>>
>> when this is running my whole website goes down as the login page has a
>> select that has to wait for that big query to finish.
>
> That could easily be fixed by doing
>
> CREATE TEMPORARY TABLE foo ...
> INSERT INTO foo SELECT ...
> INSERT INTO blah SELECT * FROM foo
> DROP TABLE foo
>
> That way you would decouple the SELECT and the INSERT. However the
> table you SELECT from will be locked for writes anyway. Probably you
> should switch to a storage engine that implements multiversioning.
> Currently this reads: switch to InnoDB.
>
>
> XL

That would lock that table for same period of time while creating the temp
table, then we would have to re insert it.
I don't recall why, but where unable to switch to InnoDB. But we have
looked at it in the past.

Hi!

"Axel Schwenke" <axel.schwenke@gmx.de> kirjoitti
viestissä:jp74vd.m1r.ln@idefix.xl.local...
> Arty <please.no.mail@domain.tld> wrote:
>> We have a few sql statements that take up to 10 minutes to run.
>> they are run like
>> INSERT INTO blah (SELECT...
>> and that Select probably has a join or two...
>>
>> Tables are MyISAM.
>>
>> when this is running my whole website goes down as the login page has a
>> select that has to wait for that big query to finish.
>
> That could easily be fixed by doing
>
> CREATE TEMPORARY TABLE foo ...
> INSERT INTO foo SELECT ...
> INSERT INTO blah SELECT * FROM foo
> DROP TABLE foo
>
> That way you would decouple the SELECT and the INSERT. However the
> table you SELECT from will be locked for writes anyway. Probably you
> should switch to a storage engine that implements multiversioning.
> Currently this reads: switch to InnoDB.

additionally, you should put innodb_locks_unsafe_for_binlog to my.cnf. That
option removes also the row locks from the SELECT tables.

> XL
> --
> Axel Schwenke, Senior Software Developer, MySQL AB

Best regards,

Heikki

Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php