…TABLE ENABLE KEYS. my.cnf [mysqld] datadir=/mnt/mysql56/data basedir=/usr/local/mysql socket=/var/lib/mysql/mysql…_buffer_pool_instances=16 metadata_locks_hash_instances=32 table_open_cache_instances=8 …myisam tmpdir=/dev/shm innodb_undo_logs=32 innodb_old_blocks_time=0 table_open_cache=2048 table…
Post: Choosing a MySQL HA Solution - Post-Webinar Q&A
June 7, 2013 By Leave a Comment
… the MySQL manual. You can configure the slave to only replicate a specific database, a set of databases, or specific tables; you… 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 MySQL/NDB…
Post: ZFS on Linux and MySQL
May 24, 2013 By 17 Comments
… server can easily handle the write load of all the MySQL instances. The original idea was to configure them with raid… best with InnoDB, with MyISAM you’ll have to start the snapshot while holding a “flush tables with read lock” and the flush… together, ZFS on Linux is a very interesting solution for MySQL backup servers. All backup solutions have an impact on performance…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…referring to the amazing MySQL manual, especially the option and variable reference table. But just as …lock_countblogpercona.commanual memlockblogpercona.commanual min_examined_row_limitblogpercona.commanual myisam_block_sizeblogpercona.commanual myisam_data_pointer_sizeblogpercona.commanual myisam…
Post: Innodb Table Locks
July 31, 2012 By 4 Comments
… about MySQL 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 write (on MySQL…
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 processlist…
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… many tables which are being repaired, each may allocate myisam_sort_buffer_size and MySQL could crash or go swapping. Second – table will be locked…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… still have some tables in MyISAM format in “mysql” database. Plus HEAP/MEMORY tables are used for temporary tables which may become MyISAM if they are… 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…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
…MyISAM Does Not Scale, or it does quite well – two main things stopping you is table locks and global mutex on the KeyCache. Table Locks…
Post: Slow DROP TABLE
June 16, 2009 By 21 Comments
…LOCK_open mutex when the old table files are deleted: | 3 | root | localhost | test | Query | 7 | rename result table | ALTER TABLE large_table ENGINE=MyISAM…
