Like I said, the problem is not just one particular SQL statement. It is several dozen statements operating on tables with several hundred million records. The problem is that I am finding that when I use LOCK TABLES, these queries run slower (please read my ORIGINAL post with all this information). I am trying to find a logical or reasonable explanation WHY this would be the case, despite the fact that the documentation states otherwise (see: Right here: http://dev.mysql.com/doc/refman/5.5/en/lock-tables-restrictions.html )
But if seeing some SQL will make you happy, here is just one example: UPDATE dest d straight_join source s set d.seq=s.seq WHERE d.key=s.key; for 140 million records in "dest" and 220 million records in "source". Source is indexed by key+seq (key is primary key, but seq is included as a covering index). There is no index on dest.seq -- that index is built once the update is complete. This query takes about 3.5 hours when I don't use LOCK TABLES, and over 4 hours when I do use LOCK TABLES. -Hank On Thu, Sep 22, 2011 at 2:18 PM, Ananda Kumar <anan...@gmail.com> wrote: > 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 <hes...@gmail.com> wrote: > >> >> Sorry, but you do not understand my original issue or question. >> >> -Hank >> >> >> >> On Thu, Sep 22, 2011 at 2:10 PM, Ananda Kumar <anan...@gmail.com> 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 <hes...@gmail.com> 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 <anan...@gmail.com>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 <hes...@gmail.com> 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 >>>>>> <antonycur...@verizon.net>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 < >>>>>> > antonycur...@verizon.net> 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 >>>>>> >>> >>>>>> >> >>>>>> >> >>>>>> > >>>>>> > >>>>>> >>>>> >>>>> >>>> >>> >> >
