… got a lot of questions, and did not have enough time to answer all of them, so let me try to… the beginning to prevent dead locks? So you would get a lock wait instead of a dead lock. No, that would cause blocking…
Comment: ext4 vs xfs on SSD
May 3, 2012 By 23 Comments
… (especially with regards to metadata fragmentation), so maybe it’s time to do another benchmark. What I found with XFS is… to indicate that XFS had some bottleneck internally, maybe the lock contention issue that Dave was referring to above. Disclaimer: I…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
… the impact of FLUSH TABLES WITH READ LOCK can be quite large because of the time it may take to complete this… it works. Even though with general lock compatibility guidelines Read Lock should not conflict with another Read Lock, it does for this statement… reason (other than code design) for read lock to wait for other read lock in order to be set. As a workaround…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
…` — LOCK TABLES `C` WRITE; /*!40000 ALTER TABLE `C` DISABLE KEYS */; /*!40000 ALTER TABLE `C` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014… are potential solutions for this problem ? you can use mysqldump –lock-all-tables instead which does not have this problem at…
Post: Percona Toolkit 2.1 with New Online Schema Change Tool
April 5, 2012 By 9 Comments
… tables with no blocking or downtime. As you know, MySQL locks tables for most ALTER operations, but pt-online-schema-change performs the ALTER without any locking. Client applications can continue reading and writing the table with… is not new to the toolkit. We developed it some time ago for a client and quietly included it, without knowing…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…0 DATA_LENGTH: 16384 INDEX_LENGTH: 0 CREATE_TIME: NULL UPDATE_TIME: NULL *************************** 2. row *************************** SESSION_ID: 7234 TABLE_SCHEMA… 512619219 (509995888 + 2623331) File system 294352 (82672 + 211680) Lock system 318875832 (318747272 + 128560) Recovery system 0 (0 + …
Comment: Innodb Performance Optimization Basics
March 28, 2012 By 70 Comments
…_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 A count command with approx 3 million… long and all my transactions are locked. select count(1),field1 from Table where REQUEST_TIME >= ’2012-03-26′ group by field1…
Comment: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 24, 2012 By 20 Comments
…”. Even without Flush tables with read lock, LVM snapshot should be recoverable 100% of the time. You might have found recovery bug… and innodb snapshot. In certain versions FLUSH TABLES WITH READ LOCK would not block “commit” operation which caused wrong binary log…
Comment: Best kept MySQLDump Secret
March 29, 2012 By 11 Comments
…_set_client = @saved_cs_client */; — – Dumping data for table `z` — LOCK TABLES `z` WRITE; /*!40000 ALTER TABLE `z` DISABLE KEYS */; INSERT…’); /*!40000 ALTER TABLE `z` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; …
Post: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
…’re different) offer flexibility in schemas Microsecond time resolution for DATETIME and similar time-based types, as well as microseconds in SHOW… well as adding a lot of nice features like less lock contention, better diagnostics, improvements to partitioning, and the addition of…
