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