List:General Discussion« Previous MessageNext Message »
From:Antony T Curtis Date:September 22 2011 4:42am
Subject:Re: Slower performance with LOCK TABLES
View as plain text  
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