List:General Discussion« Previous MessageNext Message »
From:Hank Date:September 22 2011 6:13pm
Subject:Re: Slower performance with LOCK TABLES
View as plain text  
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