… getting the monitores working – i created the different tables for the innodb (Standard, Lock,Tablespace,Table) and the server doesn’t report the data…
Comment: InnoDB's gap locks
May 7, 2012 By 14 Comments
… ENGINE INNODB STATUS information as follow: —TRANSACTION 0 27638, ACTIVE 1207 sec, process no 2909, OS thread id 1100101952 inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1216, 1 row lock(s) MySQL thread… `GEN_CLUST_INDEX` of table `test`.`t` trx id 0 27638 lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… 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…’re just using Innodb tables and you’re not actively changing users, stored procedures etc (which are stored in MyISAM tables anyway) you…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… row lock(s), undo log entries 1 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 space id 19 page no 4 n bits 80 index `age` of table… no need to create locks to prevent that from happening. 2- innodb_locks_unsafe_for_binlog = 1. Disables the gap locks except for foreign…
Comment: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
Davi, Yes. However for Innodb tables FLUSH TABLE is nil – so what we care about is just about Locking the table to prevent writes to… TABLES WITH READ LOCK suppose to flush tables not just make them read only. Most tools however do not need FLUSH PART (for Innodb tables) and might be we just need different command which locks table read only without flushing.
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
…mysqldump –single-transaction to get consistent backup for their Innodb tables without making database read only. In most cases it…– — Dumping data for table `C` — LOCK TABLES `C` WRITE; /*!40000 ALTER TABLE `C` DISABLE KEYS */; /*!40000 ALTER TABLE `C` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET…
Comment: Announcing Percona XtraBackup 2.0.0 GA
April 4, 2012 By 11 Comments
With xtrabackup, it only has to lock tables that aren’t InnoDB, it has the ability to take backups of InnoDB tables *without* taking locks and without any overhead of block device or filesystem snapshots.
Comment: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 26, 2012 By 20 Comments
… is derived from that. Essentially, we are using FLUSH TABLES WITH READ LOCK, and then run ‘lvcreate -s’ to create a snapshot… of the innodb in the target location fails, though, even though it has been made even under FLUSH TABLES WITH READ LOCK (I…. We are now using ‘service mysql stop’ instead of FLUSH TABLES WITH READ LOCK to get reliable snapshots from a donor box.
Comment: Innodb Performance Optimization Basics
March 28, 2012 By 70 Comments
… am facing major issues with slow query output. The tables are in InnoDB with MySQL 5 and RHEL 5. Its a 32… innodb_additional_mem_pool_size = 2M innodb_log_file_size = 65M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 A count command with approx 3 million records is taking too long and all my transactions are locked…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…temporary_tables \G *************************** 1. row *************************** SESSION_ID: 7234 TABLE_SCHEMA: test TABLE_NAME: my ENGINE: InnoDB NAME: #sql516_1c42_2 TABLE… (509995888 + 2623331) File system 294352 (82672 + 211680) Lock system 318875832 (318747272 + 128560) Recovery system 0 (0 +…
