…lock preventing the insertion of data to other sessions. How to troubleshoot gap locks? Is possible to detect those gap locks…locks can be different. In the following link there is a good source of information: http://dev.mysql…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…to complete. Here is example how it can look: mysql> show processlist; …lock to wait for other read lock in order to be set. As a workaround you can consider having scripts which would check…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
…in set (0.00 sec) You can even go deeper to check which sessions have created which temporary tables (both in memory and not): mysql…in set (0.00 sec) Innodb Memory Usage Finally it is often helpful to check how much memory Innodb has allocated. In… (82672 + 211680) Lock system 318875832 (318747272 +…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
…how MySQL‘s Transactions work with DDL, In particular ALTER TABLE. When ALTER TABLE is Performed in… apply to DATA which is stored in this…*/; — – Dumping data for table `C` — LOCK TABLES `C` WRITE; /*!40000 ALTER …KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS …
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 8 Comments
…to monitor in MySQL. Those are only suggestions; any given deployment might want to monitor only some of them, as appropriate, and might need to include others as well. Here’s how you can use our new monitoring plugins to check… creates a lot of bloat or locking in the database server. This plugin …
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
…1,2 MySQL allows us to set FOREIGN_KEY_CHECKS=0 to disable …to verify consistency. How can we do that? Quality Control Queries We need to check for orphaned rows in every parent-child relationship in…to automate quality control and detect orphans early so you can correct them. 1Innodb locking…
Post: How to use tcpdump on very busy hosts
April 18, 2011 By 17 Comments
… # duration to capture tcpdump data in seconds (1200 = 20 min) TMP=’/tmp’ LOCKFILE=”$self.lockfile” TMPfile=”$self.temp” RESfile=”$self.result” # check lock file if [ -e "$TMP/$LOCKFILE" ]; then echo “$self: lock file $LOCKFILE already exists, aborting” exit 1 fi # set trap to be…
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
… a bit with INSERT … SELECT I decided to check is Innodb locks are relly as efficient in terms of low resource usage as they… see how long does it take: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select count(i) from sample lock in share mode; +———-+ | count(i) | +———-+ | 1638400 | +———-+ 1 row in set (7.02 sec…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
…to get amount of IO per query or per Select. It is good to check…How to get CPU consumption per query ? You can take a look at procfs for MySQL process: root@ubuntu:/var/log/mysql…locks, etc. The ratio between Wait Time and CPU time is very helpful to see how…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 10 Comments
…how much time MySQL spends in…mysql> SHOW PROFILE FOR QUERY 1; +———————-+———-+ | Status | Duration | +———————-+———-+ | starting | 0.000032 | | checking … 0.01 | 2 | 0.0000070000 | | System lock | 0.000010 | 0.01 | 1 |… could add in a UNION to inject another…
