… gap locks? Is possible to detect those gap locks using SHOW ENGINE INNODB STATUS: —TRANSACTION 72C, ACTIVE 755 sec 4 lock struct… to create locks to prevent that from happening. 2- innodb_locks_unsafe_for_binlog = 1. Disables the gap locks except for foreign…://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the default isolation level…
Comment: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 25, 2012 By 20 Comments
… file. 2. copy data files other than InnoDB. 3. get the binlog file and binlog postion information. And if we ensure the following… only use InnoDB as our engine, no other engines used. 3. get binlog file and binlog postion information from xtrabackup_binlog_pos_innodb file since InnoDB will wirte…
Comment: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 27, 2012 By 20 Comments
… 11:25:22 InnoDB: Starting an apply batch of log records to the database… … InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 15906301, file name ../log/binlog.000290 … This is a slave, it has no binlog…: binlog.000062 Relay_Log_File: relaylog.000181 That does not match. So what is this information, and how is it useful to…
Comment: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… not use similar patch as in Google Patches II, where InnoDB file writes can be altogether disabled using a global variable setting? For InnoDB based databases, this allows for immediate locking of writes, safe BINLOG position snapshot…
Comment: ext4 vs xfs on SSD
March 20, 2012 By 23 Comments
Useful results for when we move to SSDs.. Were sync_binlog=1 and innodb_flush_at_trx_commit=1 ?
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 17 Comments
… can be happening on Unix at a time. On Windows, InnoDB uses native async I/O, and can perform many read-aheads… the WHERE condition in a search. Please use this with care: you MUST use row-based binlogging and replication, and remember that the… see the full write speed of the disk for throughput. INNODB uses fuzzy checkpointing to commit data to disk. The problem that…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…innodb_thread_concurrencyblogpercona.commanual innodb_thread_sleep_delayblogpercona.commanual Innodb_truncated_status_writesblogpercona.commanual innodb_use_native_aioblogpercona.commanual innodb_use_sys_mallocblogpercona.commanual innodb_versionblogpercona.commanual innodb…
Post: Beware: ext3 and sync-binlog do not play well together
January 21, 2009 By 19 Comments
…-point-selects=0 –oltp-range-size=0 –mysql-table-engine=innodb –mysql-user=root –max-requests=0 –max-time=60 –mysql… basically the same. Using XFS instead of EXT3 gives expected results – we get 2350 transactions/sec with sync_binlog=1 and 2550… be opening binlog with O_DSYNC flag if sync_binlog=1 instead of using fsync will help ? Or may be binlog pre-allocation would…
Comment: Tuning for heavy writing workloads
October 15, 2009 By 9 Comments
… it). Whether innodb_support_xa is needed or not is depend on the each user. If innodb_support_xa=1 and using binlog, 2 times fsync() is done for the each transaction. It is “prepare” fsync() and “commit” fsync(), the writing binlog for… binlog yet, and be committed if the transaction is committed in the binlog. So innodb_support_xa=1 seems to be safer than innodb…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 21 Comments
…’m not using replication and have my binary log disabled ? If replication is not used you can enable innodb_locks_unsafe_for_binlog option, which will relax locks which Innodb sets on statement execution… unsafe fore replication and point in time recovery, so use innodb_locks_unsafe_for_binlog option with caution. Note disabling binary logs is…
