… often can be very inefficient. As results of Foreign Key limitations you might need to get rid of them, yet this… INTO parent(parent_id) VALUES(1,”"); INSERT INTO child(parent_id) VALUES(1,”"); set foreign_key_checks=0; delete from parent; After these actions… rows affected (0.00 sec) mysql> select * from child; +———–+——+ | parent_id | v | +———–+——+ | 1 | NULL | +———–+——+ 1 row in set (0.00 sec) mysql> update…
Post: Percona Server 5.1.59-13.0
October 16, 2011 By Leave a Comment
…are available here and from the Percona Software Repositories). Based on MySQL 5.1.59, including … reading the rows for “INSERT“, “UPDATE“ and “DELETE“ statements but not updating them (Fake Changes… suited for production environments. This feature limits the age of idle |XtraDB| transactions. …
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
… fill up the disk. #!/bin/bash # Begin by deleting things more than 7 days old find /root/tcpdumps/… ${avail}MB free)”>&2 exit 1 fi host=$(mysql -ss -e ‘SELECT p.HOST FROM information_schema.innodb_lock_… on b.trx_mysql_thread_id = p.ID LIMIT 1‘) if [ "${host}" ]; then echo “Host ${host} is blocking” …
Comment: How to find MySQL queries worth optimizing ?
September 13, 2012 By 6 Comments
…delete from tbl where id < 4803304003 AND eventtime EXPLAIN select * from alertdata where eventid < 4803304003 AND eventtime < '20120906000000' limit… ref | rows | Extra | +—-+————-+———–+——-+————————-+———+———+——+——+————-+ | 1 | SIMPLE |…
Post: Performance Schema tables stats
May 2, 2011 By 2 Comments
… 0 COUNT_DELETE: 0 SUM_TIMER_DELETE: 0 MIN_TIMER_DELETE: 0 AVG_TIMER_DELETE: 0 MAX_TIMER_DELETE: 0 *************************** 3. row… to files: select * from file_summary_by_instance where FILE_NAME like ‘%sbtest55%’ limit 5G *************************** 1. row *************************** FILE_NAME: …
Post: A quest for the full InnoDB status
October 31, 2008 By 4 Comments
… transaction list, but fortunately there are some alternatives to the limited MySQL command output. First one is that you can have…:26 12 -> /tmp/iblnLBhO (deleted) lrwx—— 1 root root 64 Oct 31 18:26 5 -> /tmp/ibuQBSgo (deleted) lrwx—— 1 root root 64 Oct…/ibsMzkIA (deleted) lrwx—— 1 root root 64 Oct 31 18:26 8 -> /tmp/ibj08Rkc (deleted) The entries are presented as symbolic links from file…
Post: Recovering Innodb table Corruption
July 4, 2008 By 22 Comments
… for data operations and so you can’t insert or delete any data (though you can create or drop Innodb tables… mysql> insert ignore into test2 select * from test limit 200; Query OK, 100 rows affected (1.47 sec) Records: 200 Duplicates: 100 Warnings: 0 mysql> insert ignore into test2 select * from test limit 300; ERROR 2013…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… table `test/child`: , CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE Trying to add in child… use 1, locked 0 MySQL thread id 8079, query id 728899 localhost root Sending data select sql_calc_found_rows * from b limit… use 1, locked 0 MySQL thread id 8078, query id 728898 localhost root Sending data select sql_calc_found_rows * from b limit…
Post: Knowing what pt-online-schema-change will do
November 1, 2012 By 1 Comment
…osc_test_t_del` AFTER DELETE ON `test`.`t` FOR EACH ROW DELETE IGNORE FROM `test`.`_t_new` …? AND `score` >= ?)) ORDER BY `guest_language`, `guest_country`, `score` LIMIT ?, 2 /*next chunk boundary*/ Not swapping tables…new, empty table. This allows two things: 1) it verifies that the tool copies the…
Comment: MySQL Server Memory Usage
September 1, 2006 By 21 Comments
…table’, ’31′ ‘Com_dealloc_sql’, ’0′ ‘Com_delete‘, ’348788′ ‘Com_delete_multi’, ‘1‘ ‘Com_do’, ’0′ ‘Com_drop_db’,…queries.log max_connections=650 open_files_limit=6144 max_connect_errors=10000 interactive_… AVAILABLE #innodb_log_buffer_size=8M # Lowered from 32M according to MySQL innodb_additional_…
