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