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