List:General Discussion« Previous MessageNext Message »
From:Hank Date:September 22 2011 6:51pm
Subject:Re: Slower performance with LOCK TABLES
View as plain text  
Like I said, the problem is not just one particular SQL statement. It is
several dozen statements operating on tables with several hundred million
records.  The problem is that I am finding that when I use LOCK TABLES,
these queries run slower (please read my ORIGINAL post with all this
information).  I am trying to find a logical or reasonable explanation WHY
this would be the case, despite the fact that the documentation states
otherwise (see: Right here:
http://dev.mysql.com/doc/refman/5.5/en/lock-tables-restrictions.html )

But if seeing some SQL will make you happy, here is just one example:

UPDATE dest d straight_join source s set d.seq=s.seq WHERE d.key=s.key;

for 140 million records in "dest" and  220 million records in "source".
 Source is indexed by key+seq (key is primary key, but seq is included as a
covering index). There is no index on dest.seq -- that index is built once
the update is complete.  This query takes about 3.5 hours when I don't use
LOCK TABLES, and over 4 hours when I do use LOCK TABLES.

-Hank


On Thu, Sep 22, 2011 at 2:18 PM, Ananda Kumar <anandkl@stripped> wrote:

> May be if u can let the audience know a sip-net of ur sql, some can help u
>
>
> On Thu, Sep 22, 2011 at 11:43 PM, Hank <heskin@stripped> wrote:
>
>>
>> Sorry, but you do not understand my original issue or question.
>>
>> -Hank
>>
>>
>>
>> On Thu, Sep 22, 2011 at 2:10 PM, Ananda Kumar <anandkl@stripped> wrote:
>>
>>> No,
>>> Use a cursor(select <column_name> to be used in where condition of
> update
>>> stmt), loop through it for each update.
>>>
>>> regards
>>> anandkl
>>>
>>>
>>> On Thu, Sep 22, 2011 at 11:36 PM, Hank <heskin@stripped> wrote:
>>>
>>>>
>>>> Actually, that would be orders of magnitude slower.
>>>>
>>>>   I'm using MYISAM tables, so there's no commit.
>>>>
>>>>
>>>>
>>>>
>>>> On Thu, Sep 22, 2011 at 1:48 PM, Ananda Kumar
> <anandkl@stripped>wrote:
>>>>
>>>>> Hi,
>>>>> Why dont u use a stored proc to update rows ,where u commit for
> every
>>>>> 1k or 10k rows.
>>>>> This will be much faster than ur individual update stmt.
>>>>>
>>>>> regards
>>>>> anandkl
>>>>>
>>>>> On Thu, Sep 22, 2011 at 8:24 PM, Hank <heskin@stripped>
> wrote:
>>>>>
>>>>>> That is what I'm doing. I'm doing a correlated update on 200
> million
>>>>>> records. One UPDATE statement.
>>>>>>
>>>>>> Also, I'm not asking for a tutorial when not to use LOCK TABLES. 
> I'm
>>>>>> trying
>>>>>> to figure out why, despite what the documentation says, using
> LOCK
>>>>>> TABLES
>>>>>> hinders performance for large update statements on MYISAM tables
> when
>>>>>> it is
>>>>>> supposed to increase performance on exactly the type of queries I
> am
>>>>>> performing.
>>>>>>
>>>>>> If you can't help answer *that* question, please stop lecturing
> me on
>>>>>> the
>>>>>> reasons not to use LOCK TABLES. Thanks.
>>>>>>
>>>>>> -Hank
>>>>>>
>>>>>>
>>>>>> On Thu, Sep 22, 2011 at 10:19 AM, Antony T Curtis
>>>>>> <antonycurtis@stripped>wrote:
>>>>>>
>>>>>> > Even for MyISAM tables, LOCK TABLES is not usually the best
> solution
>>>>>> for
>>>>>> > increasing performance. When there is little to no
> contention, LOCK
>>>>>> TABLES
>>>>>> > doesn't offer much value.
>>>>>> >
>>>>>> > MyISAM works best when you can get more work done in a
> statement:
>>>>>> Instead
>>>>>> > of executing a bunch of insert statements, combine them into
> a
>>>>>> single
>>>>>> > multi-row insert statement, as an example.
>>>>>> >
>>>>>> >
>>>>>> > On 22 Sep 2011, at 06:13, Hank wrote:
>>>>>> >
>>>>>> > Thanks for your reply.  I failed to mention that these are
> MYISAM
>>>>>> tables,
>>>>>> > so no transactions.  And like I said, this is not a
> production box
>>>>>> nor is
>>>>>> > there any application running, so there's no contention for
> the
>>>>>> tables being
>>>>>> > locked.  I'm trying to update a database design on two
> tables with
>>>>>> 200
>>>>>> > million records each, so anything I can do to increase the
>>>>>> performance of
>>>>>> > these long running queries will shorten the migration
> running time.
>>>>>> >
>>>>>> > What I was referring to was that in the documentation,  that
> when
>>>>>> using
>>>>>> > LOCK TABLES, mysql does not update the key cache until the
> lock is
>>>>>> released,
>>>>>> > versus when not using LOCK TABLES it does update the key
> cache on
>>>>>> each
>>>>>> > insert/update/delete.
>>>>>> >
>>>>>> > see: http://tuxradar.com/practicalphp/18/2/22
>>>>>> >
>>>>>> > In my testing, I'm seeing a slow down when I use LOCK TABLES
> versus
>>>>>> running
>>>>>> > the same queries without it.  I'm just trying to find a
> reason why
>>>>>> that
>>>>>> > might be the case.
>>>>>> >
>>>>>> > -Hank
>>>>>> >
>>>>>> >
>>>>>> > On Thu, Sep 22, 2011 at 12:42 AM, Antony T Curtis <
>>>>>> > antonycurtis@stripped> wrote:
>>>>>> >
>>>>>> >> LOCK TABLES...WRITE is very likely to reduce performance
> if you are
>>>>>> using
>>>>>> >> a transactional storage engine, such as InnoDB/XtraDB or
> PBXT. The
>>>>>> reason is
>>>>>> >> that only one connection is holding the write lock and
> no other
>>>>>> concurrent
>>>>>> >> operation may occur on the table.
>>>>>> >>
>>>>>> >> LOCK TABLES is only really useful for non-transactional
> tables and
>>>>>> maybe a
>>>>>> >> few specialized operations where it has its advantages
> but for
>>>>>> 99.9% of
>>>>>> >> cases, it should not be used.
>>>>>> >>
>>>>>> >> What does increase performance is the proper use of
> transactions
>>>>>> with
>>>>>> >> appropriate use of SELECT...FOR UPDATE and SELECT...LOCK
> IN SHARE
>>>>>> MODE.
>>>>>> >>
>>>>>> >> Regards,
>>>>>> >>
>>>>>> >> Antony.
>>>>>> >>
>>>>>> >>
>>>>>> >>
>>>>>> >> On 21 Sep 2011, at 20:34, Hank wrote:
>>>>>> >>
>>>>>> >>  According to everything I've read, using LOCK
> TABLES...WRITE for
>>>>>> updates,
>>>>>> >>> inserts and deletes should improve performance of
> mysql server,
>>>>>> but I
>>>>>> >>> think
>>>>>> >>> I've been seeing the opposite effect.
>>>>>> >>>
>>>>>> >>> I've been doing quite a bit of testing on a 64bit
> install of
>>>>>> CentOS 5.5
>>>>>> >>> installed as a guest OS on a VMWare ESXi 4.0
> hypervisor on a Dell
>>>>>> R610.
>>>>>> >>> There are no other VMs on this box, and there are no
> other users
>>>>>> or
>>>>>> >>> threads
>>>>>> >>> running on the OS. Just me.  I'm using this box
> strictly for
>>>>>> testing of
>>>>>> >>> large database migration scripts.
>>>>>> >>>
>>>>>> >>> It seems like when I execute some of these long
> running statements
>>>>>> >>> without
>>>>>> >>> locking the tables, the code runs quite a bit faster
> than when I
>>>>>> do lock
>>>>>> >>> the
>>>>>> >>> tables.  And before testing each run, I do restart
> the server so
>>>>>> there is
>>>>>> >>> no
>>>>>> >>> query caching and I also use FLUSH TABLES between
> each test run.
>>>>>> >>>
>>>>>> >>> All I'm asking is this:  Can anything think of a
> scenario on a
>>>>>> single
>>>>>> >>> user-box and mysql instance, that locking tables
> would cause these
>>>>>> DML
>>>>>> >>> statements to slow down compared to not locking the
> tables?
>>>>>> >>>
>>>>>> >>> Thanks,
>>>>>> >>>
>>>>>> >>> -Hank
>>>>>> >>>
>>>>>> >>
>>>>>> >>
>>>>>> >
>>>>>> >
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>
>

Thread
Slower performance with LOCK TABLESHank22 Sep
  • Re: Slower performance with LOCK TABLESAntony T Curtis22 Sep
    • Re: Slower performance with LOCK TABLESHank22 Sep
Re: Slower performance with LOCK TABLESAntony T Curtis22 Sep
  • Re: Slower performance with LOCK TABLESHank22 Sep
    • Re: Slower performance with LOCK TABLESAnanda Kumar22 Sep
      • Re: Slower performance with LOCK TABLESHank22 Sep
        • Re: Slower performance with LOCK TABLESAnanda Kumar22 Sep
          • Re: Slower performance with LOCK TABLESHank22 Sep
            • Re: Slower performance with LOCK TABLESAnanda Kumar22 Sep
              • Re: Slower performance with LOCK TABLESHank22 Sep
                • Re: Slower performance with LOCK TABLESAnanda Kumar22 Sep
                • Re: Slower performance with LOCK TABLESJohan De Meersman23 Sep
                  • Re: Slower performance with LOCK TABLESHank23 Sep
                  • Re: Slower performance with LOCK TABLESHank23 Sep
                    • Re: Slower performance with LOCK TABLESJohan De Meersman26 Sep
                  • Re: Slower performance with LOCK TABLEShsv23 Sep
Re: Slower performance with LOCK TABLESHank22 Sep