… using ALTER TABLE fast index creation (merge sort). For the MyISAM tests I used a 10GB key buffer. I used ALTER TABLE DISABLE…_buffer_size=32M innodb_buffer_pool_instances=16 metadata_locks_hash_instances=32 table_open_cache_instances=8 sort_buffer_size=128k…_tmp_storage_engine=myisam tmpdir=/dev/shm innodb_undo_logs=32 innodb_old_blocks_time=0 table_open_cache=2048 table_definition_cache…
Post: Choosing a MySQL HA Solution - Post-Webinar Q&A
June 7, 2013 By Leave a Comment
… replicate a specific database, a set of databases, or specific tables; you can also configure the slave to replicate everything EXCEPT a specified set of databases and tables. With this method, every event is still written to the… the MySQL world, except maybe MyISAM files on top of a clustered filesystem with an external lock manager. You might say that…
Post: ZFS on Linux and MySQL
May 24, 2013 By 17 Comments
… best with InnoDB, with MyISAM you’ll have to start the snapshot while holding a “flush tables with read lock” and the flush…
Post: Using MyISAM in production
June 17, 2006 By 36 Comments
… own view on using MyISAM in production. For me it is not only about table locks. Table locks is only one of MyISAM limitations you need… in MyISAM are rather fast so it rarely is the problem The other misconception about table locks is if you have 95% reads table locks… you enable this option – globally, for table or for set of statements (by using LOCK TABLES/UNLOCK TABLES) be careful. This option may improve…
Post: Innodb Table Locks
July 31, 2012 By 4 Comments
… 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 and Innodb when it comes to Table Level Locks. For MyISAM tables running UPDATE query on the table is essentially equivalent to locking table for…
Post: MyISAM Scalability and Innodb, Falcon Benchmarks
October 12, 2007 By 10 Comments
… scalability problems, this time We are faced with one for MyISAM tables. We saw that several times in synthetic benchmarks but never… NULL, PRIMARY KEY (`id`), KEY `id` (`id`,`stat`) ) ENGINE=MyISAM; CREATE TABLE `t2` ( `id` int(11) unsigned NOT NULL auto_increment, `val… only simple select query which should be executed without exclusive table locking. Even on 16 threads we’re getting performance worse than…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… for any SELECT statement to complete in order to complete locking tables. This means if you have workload which includes some very… getting field list from all tables) Reads though would not be blocked, ie there is no MYISAM like table lock priority problem with pending WRITE query blocks any READ queries to execute on the table. mysql> show…
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 Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… new selects to the table until it is executed. It works for storage engines with table locks or if LOCK TABLES are used. So in practice this option is used for MyISAM and MEMORY tables. max_write_lock_count Variable… table. Works with table locks so again practically applies to MYISAM and MEMORY tables. preload_buffer_size – Buffer used for Key preloading, which works for MYISAM tables…
Post: How fast is FLUSH TABLES WITH READ LOCK?
April 24, 2010 By 30 Comments
… not flushing tables yet. It’s waiting to get the lock. Flushing tables After the FLUSH TABLES WITH READ LOCK command finally acquires the lock, it must begin flushing data. This does not apply to all storage engines. However, MyISAM… FLUSH TABLES WITH READ LOCK will complete quickly. In some cases, it is unavoidable. This includes backing up a mixture of MyISAM and…
