Why dont u use a stored proc to update rows ,where u commit for every 1k or
This will be much faster than ur individual update stmt.
On Thu, Sep 22, 2011 at 8:24 PM, Hank <heskin@stripped> 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
> 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
> If you can't help answer *that* question, please stop lecturing me on the
> reasons not to use LOCK TABLES. Thanks.
> On Thu, Sep 22, 2011 at 10:19 AM, Antony T Curtis
> > Even for MyISAM tables, LOCK TABLES is not usually the best solution for
> > increasing performance. When there is little to no contention, LOCK
> > 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
> > 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
> > 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
> > 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 <
> > antonycurtis@stripped> wrote:
> >> LOCK TABLES...WRITE is very likely to reduce performance if you are
> >> 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
> >> operation may occur on the table.
> >> LOCK TABLES is only really useful for non-transactional tables and maybe
> >> 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
> >>> 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
> >>> the
> >>> tables. And before testing each run, I do restart the server so there
> >>> 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