Professional Web Applications Themes

Big statement locks table too long - MySQL

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. ...

  1. #1

    Default Big statement locks table too long

    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?

    Arty Guest

  2. #2

    Default Re: Big statement locks table too long

    > 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
    Dikkie Dik Guest

  3. #3

    Default Re: Big statement locks table too long

    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 Guest

  4. #4

    Default Re: Big statement locks table too long

    Arty <please.no.maildomain.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: [url]http://dev.mysql.com/doc/refman/5.0/en/[/url]
    MySQL User Forums: [url]http://forums.mysql.com/[/url]
    Axel Schwenke Guest

  5. #5

    Default Re: Big statement locks table too long

    On Mon, 13 Mar 2006 17:48:51 +0100, Axel Schwenke wrote:
    > Arty <please.no.maildomain.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.

    Arty Guest

  6. #6

    Default Re: Big statement locks table too long

    Hi!

    "Axel Schwenke" <axel.schwenkegmx.de> kirjoitti
    viestissä:jp74vd.m1r.lnidefix.xl.local...
    > Arty <please.no.maildomain.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
    [url]http://www.innodb.com/order.php[/url]


    Heikki Tuuri Guest

Similar Threads

  1. Long SQL Statement Via ASP
    By MDW in forum ASP Database
    Replies: 2
    Last Post: November 7th, 10:10 AM
  2. Replies: 1
    Last Post: September 15th, 05:46 PM
  3. [LONG] Beers in an Access Table
    By Eric in forum Microsoft Access
    Replies: 3
    Last Post: July 24th, 04:48 PM
  4. Max number table in the union statement
    By David Portas in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 1st, 07:49 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139