do u have index on dest,key On Fri, Sep 23, 2011 at 12:21 AM, Hank <hes...@gmail.com> wrote:
> > 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 >>>>>>> >>> >>>>>>> >> >>>>>>> >> >>>>>>> > >>>>>>> > >>>>>>> >>>>>> >>>>>> >>>>> >>>> >>> >> >
