Sometimes MySQL Replication may run out of sync – because of its own buts or operational limitations or because of application mistake, such… the master: LOCK TABLE tbl WRITE; SELECT * FROM table INTO OUTFILE ‘/tmp/tbl.txt’; DELETE FROM tbl; LOAD DATA INFILE ‘tmp/tbl.txt… rows affected (0.01 sec) Records: 0 Deleted: 0 Skipped: 0 Warnings: 0 mysql> rename table rep_maint to rep_old, rep…
Post: Fighting MySQL Replication Lag
September 22, 2008 By 7 Comments
…which result in low hanging fruit troubleshooting MySQL Replication Lag First fact you absolutely …if you’re using explicit transactions all updates from the transactions are buffered together and …Query Chopping – this can be running update/delete with LIMIT in the loop, controlling maximum amount of…
Post: Percona XtraDB Cluster: Failure Scenarios with only 2 nodes
July 25, 2012 By 6 Comments
…mysql> select * from percona; +—-+—————+——–+ | id | inserted_from | name | +—-+—————+——–+ | 2 | percona1 | lefred | | 3 | percona2 | kenny | +—-+—————+——–+ 2 rows in set (0.00 sec) percona2 mysql> select * from… to delete the…: gcs.fc_limit=9999999; …
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
…. As results of Foreign Key limitations you might need to get rid…(1,”"); set foreign_key_checks=0; delete from parent; After these actions we …mysql> select * from child; +———–+——+ | parent_id | v | +———–+——+ | 1 | NULL | +———–+——+ 1 row in set (0.00 sec) mysql…
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/…)”>&2 exit 1 fi host=$(mysql -ss -e ‘SELECT p.HOST FROM information_schema.innodb_lock_waits w …_schema.processlist p on b.trx_mysql_thread_id = p.ID LIMIT 1′) if [ "${host}" ]; then echo “Host ${host…
Post: Filling the tmp partition with persistent connections
August 6, 2012 By 14 Comments
… files has MySQL opened: # lsof -p 6112|grep ML mysqld 6112 root 38u REG 7,0 106594304 18 /tmp/MLjw4ecJ (deleted) mysqld… REG 7,0 62390272 26 /tmp/ML19sFCA (deleted) There are lot of files in deleted status that are filling the partition. The… partition is limited you should check the free space on it with a monitoring system like Nagios. Trending graphs from Cacti are…
Post: Limiting InnoDB Data Dictionary
February 11, 2009 By 13 Comments
… if table is used or not on MySQL level. We made it by checking MySQL table_cache. If table is placed in table_cache we consider it as used, if not – we can delete it from… data dictionary may exceed innodb_dict_size_limit, as we can’t delete any table entry from it. To finalize this post small…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… HEAP for in memory tables. So first MySQL versions did not bother naming variables with… be badly corrupted on crash. delayed_insert_limit, delayed_insert_timeout, delayed_queue_size Delayed … replication/binlogging related options – when to delete binary logs from master. It does not affect Innodb …
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 14 Comments
…sec) mysql> show status like “Handler%”; +—————————-+——-+ | Variable_name | Value | +—————————-+——-+ | Handler_commit | 0 | | Handler_delete | 0 |…mysql> explain (select * from people where age=18 order by last_online desc limit 10) UNION ALL (select * from…
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
… caching rather than MySQL level caching but it is other story. Lets talk a bit about features and limitations of Query Cache… Other portion of overhead comes from modification statements which you can calculate by (Com_insert+Com_delete+Com_update+Com_replace)/Qcache… points – as protection from one query wiping your all query cache option query_cache_limit was implemented which limits result set which can…
