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