… EXCEPT a specified set of databases and tables. With this method, every event is still… that MySQL is up and running. That said, there are plenty of other MySQL and OS-level…MySQL world, except maybe MyISAM files on top of a clustered filesystem with an external lock manager. You might say that MySQL…
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
…data at the filesystem level. The InnoDB indexes were built using ALTER TABLE fast index creation …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 …
Post: ZFS on Linux and MySQL
May 24, 2013 By 17 Comments
…t want to start a war but ZFS performance level was less than half of xfs for the …to start the snapshot while holding a “flush tables with read lock” and the flush operation will take some … ZFS on Linux is a very interesting solution for MySQL backup servers. All backup solutions have an impact …
Post: Innodb Table Locks
July 31, 2012 By 4 Comments
…. Unless table is being locked explicitly Innodb “converts” table lock to “no lock” hence eliminating conflicts on MySQL level table locks for most queries. Summary: MySQL Table level locks and Innodb Table Level locks are two separate beings. You almost never will run into problems with Innodb table level locks…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
… consistent Statement based replication. To accomplish that, row level locking databases also acquire gap locks. What is a Phantom Read A Phantom Read… MySQL thread id 3, OS thread handle 0x7f84a78ba700, query id 163 localhost msandbox TABLE LOCK table `test`.`t` trx id 72C lock mode IX RECORD LOCKS…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… MySQL 5.5.3 Metadata Locking behavior prior to MySQL 5.5.3 Prior to MySQL 5.5.3 a statement that opened a table only held meta data locks… broken because the SELECT was not repeatable although transaction isolation level of REPEATABLE-READ was used. This behavior of versions prior…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
…inevitable application specific scalability restrictions, such as row level locks. There is also a load aspect – “random arrivals… take a look at procfs for MySQL process: root@ubuntu:/var/log/mysql# cat /proc/19018/stat 19018 … 0% yes, 99% no # Tmp table 1% yes, 98% no # Tmp table on 1% yes, 98% no …
Post: Percona Server on the Raspberry Pi: Your own MySQL Database Server for Under $80
February 25, 2013 By 10 Comments
… percona-server [########################################################] 100% Installing MySQL system tables… OK Filling help tables… OK [...more setup information...-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES | [...] mysql> SELECT “Hello World!”…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…the amazing MySQL manual, especially the option and variable reference table. But …table_definition_cacheblogpercona.commanual table_lock_wait_timeoutblogpercona.commanual Table_locks_immediateblogpercona.commanual Table_locks_waitedblogpercona.commanual table_open_cacheblogpercona.commanual table…
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
… a bit of background – in Innodb row level locks are implemented by having special lock table, located in the buffer pool where small record… bits per locked row; Now lets see how much exclusinve locks take: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select… using less than 4 bits per locked row. In practice this means memory consumption by row level locks should not be the problem…
