Cross platform database support is really challenging for any dba, specially for them who is working on sql server since long time because who knows what question may raise tomorrow from which database or version or edition. Last week a developer came with her database issue on one of mysql database. Error she was facing “Lock wait timeout exceeded; try restarting transaction” during update on large table having around 15 millions records, she was updating records through joins from another table. She tried 2-3 times on production, after 1-2 minutes this error was throwing.
I said try it on development/local with same data size, result was still same erro. Then started googling found some positive configurations and did some changes on db instance level settings. But result was just few seconds improvements only & final out put was error. There are below early actions we have taken :
SET join_buffer_size = 1024 * 1024 * 42;
SET innodb_write_io_threads = 16;
SET innodb_lock_wait_timeout=100;
Then I decided to change the database engine of that table which is being used. Actually it was InnoDB and table size is huge. Multiple engines in a single database is mysql pretty cool feature, we can configure multiple engines in single database based on requirement. InnoDb creates locks for maintaining concurrency like sql server full recovery mode. MyISM is another engine which works opposite of Innodb free from locks. So decided to change the engine during updates on testing server, so executed below query. Then tried to update the records.
alter table <table> engine = MyISAM
updated table set
alter table <table> engine = InnoDB
It worked and table updated in 5 seconds only.
I said try it on development/local with same data size, result was still same erro. Then started googling found some positive configurations and did some changes on db instance level settings. But result was just few seconds improvements only & final out put was error. There are below early actions we have taken :
SET join_buffer_size = 1024 * 1024 * 42;
SET innodb_write_io_threads = 16;
SET innodb_lock_wait_timeout=100;
Then I decided to change the database engine of that table which is being used. Actually it was InnoDB and table size is huge. Multiple engines in a single database is mysql pretty cool feature, we can configure multiple engines in single database based on requirement. InnoDb creates locks for maintaining concurrency like sql server full recovery mode. MyISM is another engine which works opposite of Innodb free from locks. So decided to change the engine during updates on testing server, so executed below query. Then tried to update the records.
alter table <table> engine = MyISAM
updated table set
alter table <table> engine = InnoDB
It worked and table updated in 5 seconds only.
Comments
Post a Comment
Plz dont forget to like Facebook Page..
https://www.facebook.com/pages/Sql-DBAcoin/523110684456757