… isn’t really deleted. The MySQL server keeps an open filehandle to the file, and it won’t be deleted until the server… your data permanently. The pmp-check-mysql-deleted-files plugin will alert when files have been deleted but remain open via a filehandle… connections all trying to open or close tables can indicate LOCK_open contention. Replication is delayed or stopped. Our plugins are…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
… a shared gap lock. This lock doesn’t only affect to SELECT … FOR UPDATE. This is an example with a DELETE statement: transaction1…, ACTIVE 755 sec 4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1 MySQL thread id 3, OS… source of information: http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… MySQL manual, especially the option …Com_deleteblogpercona.commanual Com_delete_multiblogpercona.commanual Com_…lock_current_waitsblogpercona.commanual Innodb_row_lock_timeblogpercona.commanual Innodb_row_lock_time_avgblogpercona.commanual Innodb_row_lock_time_maxblogpercona.commanual Innodb_row_lock…
Post: Recovery deleted ibdata1
August 10, 2012 By 9 Comments
… -> /var/lib/mysql/ibdata1 (deleted) lrwx—— 1 root root 64 Aug 7 23:29 8 -> /var/lib/mysql/ib_logfile0 (deleted) lrwx—— 1 root root 64 Aug 7 23:29 9 -> /var/lib/mysql/ib_logfile1 (deleted) where 14101… write activity we can either stop application or lock tables: mysql> flush tables with read lock; Query OK, 0 rows affected (0.37…
Post: SELECT LOCK IN SHARE MODE and FOR UPDATE
August 6, 2006 By 51 Comments
Baron wrote nice article comparing locking hints in MySQL and SQL Server. In MySQL/Innodb LOCK IN SHARE MODE and SELECT FOR UPDATE are… affected (0.01 sec) SESSION2: mysql> select * from tst; Empty set (0.00 sec) mysql> select * from tst lock in share mode; +—+ | i… only lock current version of row. Think about similar case and row being deleted. Even if Innodb would be able to set locks…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
…looks something like this: SELECT * FROM table WHERE deleted = 0 AND group_id IN (62715996, 62716592, 62717660…,sync_array_wait_event,rw_lock_s_lock_spin,pfs_rw_lock_s_lock_fu nc,btr_search…_select,JOIN::exec,mysql_sel ect,handle_select,execute_sqlcom_select,mysql_execute_command,mysql_parse,dispatch_…
Post: ACTIVE with Locks – Now thats a problem !
June 2, 2011 By 2 Comments
… have long enough ACTIVE transactions which have some locks, like in this case: MySQL thread id 2059, query id 1014425 10.10… thread id 1224980800 3 lock struct(s), heap size 368, 1 row lock(s), undo log entries 1 MySQL thread id 1720, query… row lock(s), undo log entries 2 In this case we see transactions are holding some locks and perform some update/deletes as…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
… accumulate or fill up the disk. #!/bin/bash # Begin by deleting things more than 7 days old find /root/tcpdumps/ -type… free)”>&2 exit 1 fi host=$(mysql -ss -e ‘SELECT p.HOST FROM information_schema.innodb_lock_waits w INNER JOIN information… port 3306 and port ${port} > /root/tcpdumps/`date +%s`-tcpdump & mysql -e ‘show innodb status\Gshow full processlist’ > /root/tcpdumps/`date +%s…
Post: Resyncing table on MySQL Slave
June 29, 2008 By 11 Comments
… table but rather raname them. Unfortunately you can’t rename locked tables (ERROR 1192 (HY000): Can’t execute the given command…) mysql> load data infile ‘/tmp/rep.txt’ into table rep_new; Query OK, 0 rows affected (0.01 sec) Records: 0 Deleted: 0 Skipped: 0 Warnings: 0 mysql> rename table rep_maint to rep_old, rep…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… and HEAP for in memory tables. So first MySQL versions did not bother naming variables with storage… insert to work also if tables have “holes” (deleted rows) which can help significantly. delay_key_write …. It works for storage engines with table locks or if LOCK TABLES are used. So in practice this…
