… view on using MyISAM in production. For me it is not only about table locks. Table locks is only one of MyISAM limitations you… know which ones. Concurrency. MyISAM uses table locks and has concurrent inserts which can go concurrently with selects. This is sometimes presented as… there are long selects running. Fragmentation. This is common problem for pretty much all storage engines. It is however also different. MyISAM has…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… any SELECT statement to complete in order to complete locking tables. This means if you have workload which includes some very long SELECT queries… though would not be blocked, ie there is no MYISAM like table lock priority problem with pending WRITE query blocks any READ… and either kill FLUSH TABLES WITH READ LOCK and fail backup or kill long running SELECT queries to let backup to proceed, but…
Post: Test Drive of Solid
September 22, 2006 By 5 Comments
…long ago Solid released …locked or even modified unless you require locking read, in which case SELECT FOR UPDATE or SELECT … LOCK…before test: mysql> select * from test2; +—-+———–+ | id | names | +—-+———–+ | 1 | Mysql | | 2 | Solid | | 3 | MyISAM | +—-+———–+ Session1: …
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 queries, must finish. So if there is a long-running query on the system, or… READ LOCK command finally acquires the lock, it must begin flushing data. This does not apply to all storage engines. However, MyISAM does…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 22 Comments
… be Innodb – even if writes are done in MyISAM table. So why was this done, being pretty … what pure SELECT would give. This by the way applies to SELECT .. LOCK IN SHARE MODE and SELECT … FOR UPDATE … rows by small bulks, which do not cause long enough locks to cause the problems. To complete this …
Post: Moving from MyISAM to Innodb or XtraDB. Basics
November 22, 2010 By 2 Comments
… have hidden dependencies of MyISAM table lock behavior in your applications, also check if your application handled deadlocks well. MyISAM will not produce… MyISAM it is often for people to do updates in small chunks, almost row by row to avoid holding table lock for long… want to avoid excessive SELECT COUNT(*) FROM TBL (with no where clause) which is very fast for MyISAM but does table/index…
Comment: Managing Slave Lag with MySQL Replication
January 25, 2010 By 15 Comments
… replicate a MyISAM master table to Inoodb Slave? The reason behind this kind of config is to avoid long select statement locks to prevent… queried against a large table and the query took too long to run which prevented any update on that table, hence… into such a situation. Is it possible to have master MYISAM table to be replicated on Slave INNODb table? What are…
Comment: Using LVM for MySQL Backup and Replication Setup
August 5, 2008 By 53 Comments
Crazy question w/r/t LVM snapshots on a myisam slave: If updates via replication are not cached(further they … read lock” or will “stop slave” be sufficient? Does the same apply to buffers? IE, we are already ok with running myisam tables, at cost of locking during the flush in the middle of a long select, is it really worth…
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: Paul McCullagh answers your questions about PBXT
November 20, 2009 By 2 Comments
… perform InnoDB with SELECTs under circumstances. The implementation of full-durability has changed the performance characteristics of PBXT from “MyISAM-like” to… is therefore write locked). This means that PBXT does not normally need to maintain long lists of row-level locks. This is also…
