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