… Innodb table locking is a bit more complicated than traditional MyISAM table locks. Let me start with some examples. First lets run SELECT Query… level locks, so table locked on MySQL level with LOCK TABLES will not show up out there. Now you may spotted important difference between MyISAM…
Post: Paul McCullagh answers your questions about PBXT
November 20, 2009 By 2 Comments
… architecture makes performance characteristics different to both MyISAM and InnoDB/XtraDB. Tests show that PBXT…architecture. It’s job is to clean up after a transaction. Basically it recognizes which… selected in this manner. I should also mention that the consequence of this is that SELECT … LOCK…
Post: Using MyISAM in production
June 17, 2006 By 36 Comments
… of rows may end up being updated, and you might not know which ones. Concurrency. MyISAM uses table locks and has concurrent inserts which can go concurrently with selects. This is sometimes presented as…
Post: How fast is FLUSH TABLES WITH READ LOCK?
April 24, 2010 By 30 Comments
… lock, anything else that currently holds the lock must finish what it’s doing. That means that every currently running query, including SELECT… READ LOCK command finally acquires the lock, it must begin flushing data. This does not apply to all storage engines. However, MyISAM does… TABLES WITH READ LOCK will complete quickly. In some cases, it is unavoidable. This includes backing up a mixture of MyISAM and InnoDB…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…SELECT statement to complete in order to complete locking tables. This means if you have workload which includes some very long SELECT…is no MYISAM like table lock priority …lock option with Percona Xtrabackup. Many other tools have similar option. This option can be especially helpful when backing up…
Post: Innodb performance gotcha w Larger queries.
August 28, 2009 By 7 Comments
…statements. So with single statement MyISAM was about 10% faster, …select_lock_type != LOCK_NONE && trx->mysql_thd != NULL && thd_is_select(trx->mysql_thd)) { /* It is a plain locking SELECT and the isolation level is low: do not lock gaps */ set_also_gap_locks = FALSE; } looking up…
Post: Concurrent inserts on MyISAM and the binary log
May 14, 2008 By 7 Comments
… a MyISAM table. The inserts were not happening concurrently with SELECT statements; they were blocking and the process list was filling up with… ordinary SELECT gets a lock on the table like this: Locked – read Low priority read lock But on INSERT…SELECT, you’ll see this: Read lock…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…lock_countblogpercona.commanual memlockblogpercona.commanual min_examined_row_limitblogpercona.commanual myisam_block_sizeblogpercona.commanual myisam_data_pointer_sizeblogpercona.commanual myisam…
Post: MySQL Backup tools used by Percona Remote DBA for MySQL
March 13, 2013 By 8 Comments
…them there periodically. You can also spin up an EC2 instance to load your …myisam and innodb tables. Global read lock only held until myisam tables are dumped. We are researching into how we could further improve lock… “FLUSH TABLE WITH READ LOCK“. Namely when a select is blocking the flush from …
Post: The perils of InnoDB with Debian and startup scripts
January 28, 2009 By 13 Comments
…happen on a server with MyISAM tables, if there are enough… 6356 | user | statistics | select … | 6357 | user | statistics | select … | 6358 | user | Sending data | select … | 6359 | user | statistics | select … | 6360 | user | …this is effectively a global lock. The debian-sys-maint…cleared up…
