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 >>>>> >>> >>>>> >> >>>>> >> >>>>> > >>>>> > >>>>> >>>> >>>> >>> >> >
