…use LOCK TABLES but it is not efficient. The reason REPLACE could be efficient for ISAM and MyISAM, … update this really will insert NULL value, as col1 is undefined at this stage. INSERT ON DUPLICATE KEY UPDATE … cases when you want ether to insert the new row or update stats for existing row. I …
Post: Using MyISAM in production
June 17, 2006 By 36 Comments
…on using MyISAM in production. For me it is not only about table locks. Table locks is only one of MyISAM…or UPDATE LOW_PRIORITY to adjust that or you can simply set low_priority_updates option. Anyway default behavior means any UPDATE…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
…table locks or if LOCK TABLES are used. So in practice this option is used for MyISAM and MEMORY tables. max_write_lock…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
… MODE or FOR UPDATE, SELECT statements will not lock any rows while running. This is generally correct, however there a notable exception – INSERT INTO… MyISAM table so we’ll not see any write activity. Other transaction which happes to be simple primary key update is waiting on sample table…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…table_sizeblogpercona.commanual max_insert…lock_countblogpercona.commanual memlockblogpercona.commanual min_examined_row_limitblogpercona.commanual myisam_block_sizeblogpercona.commanual myisam_data_pointer_sizeblogpercona.commanual myisam…updatable…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… by sorting in case of ALTER TABLE (the big Gotcha for MyISAM tables). Are there any plans to be …simple and same for all insert cases – in fact if the insert is single value insert or the number of values … UPDATE which has to lock all rows in Innodb right now) I however did not get exact plans on …
Comment: INSERT ON DUPLICATE KEY UPDATE and REPLACE INTO
October 26, 2011 By 15 Comments
… a trigger on an innodb table that “dumps” any updates to the table to a myisam table holding a log of updates. The update table has the … if INSERT … UPDATE might lock for read access (or lock only the index, or something simmilar) while checking for key value existence, as inserts in myisam …
Post: Paul McCullagh answers your questions about PBXT
November 20, 2009 By 2 Comments
…MyISAM and InnoDB/XtraDB. Tests show that PBXT’s performance is similar to InnoDB but, depending on… InnoDB with INSERTS and UPDATES (but…REPAIR TABLE. The table …lock at all. In addition, an UPDATE or DELETE only acquires a temporary row-lock. This lock is released when the row is updated or…
Post: Implementing efficient counters with MySQL
July 1, 2007 By 16 Comments
…MyISAM table as counter table (can well work if you do not run into table locks bottleneck) or implement delayed table update…table as inserts go to. The trick you can use here is standard “shadow table” trick – use two tables insert…
Post: Innodb performance gotcha w Larger queries.
August 28, 2009 By 7 Comments
…on with parser or it is something nasty going on on Innodb level, so I tried running the test with MyISAM tables… low: do not lock gaps */ set_also_gap_locks = FALSE; } } The “REPLACE …INSERT, and it does not use this function so it did not have a bug. INSERT ON DUPLICATE KEY UPDATE, REPLACE, UPDATE…
