…discarded designs and benchmarks.) First, we set long_query_time to 0 in order to log …| percona-playback –mysql-host 127.0.0.1 –mysql-username playback –mysql-password PaSSwOrd –mysql-schema schema_name…fake_changes to prevent INSERTs, UPDATEs, and DELETEs from corrupting the data on the standby…
Post: Recovery deleted ibdata1
August 10, 2012 By 9 Comments
… time. The following recovery technique is based on this fact and it allowed to salvage the database. Actually, the files were deleted long time ago – 6 months or so. As long as file is open physically it still…:/var/lib/mysql# chown -R mysql ib* root@localhost:/var/lib/mysql# And restart MySQL: root@localhost:/var/lib/mysql# /etc/init.d/mysql restart After…
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 11 Comments
… The pmp-check-mysql-deleted-files plugin will alert when files have been deleted but remain open…happen at inconvenient times. The pmp-check-mysql-file-privs plugin checks whether MySQL owns every … getting stuck, has long-running transactions, or other problems. The pmp-check-mysql-innodb plugin …
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
… a “server stall” is a long-running transaction. If a transaction remains open for a very long time without committing, and has modified… long time ago never to run a script that will let data accumulate or fill up the disk. #!/bin/bash # Begin by deleting… port 3306 and port ${port} > /root/tcpdumps/`date +%s`-tcpdump & mysql -e ‘show innodb status\Gshow full processlist’ > /root/tcpdumps/`date +%s…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… MV which use those aggregate functions. Now I delete some line items from orders: mysql> delete -> from order_lines -> where order_id -> between… which uses the complete refresh method takes a very long time to refresh: mysql> call flexviews.refresh( -> flexviews.get_id(‘demo’,'complete_example2…
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_…commanual Com_stmt_send_long_datablogpercona.commanual Com_…runningblogpercona.commanual time_formatblogpercona.commanual time_zoneblogpercona.commanual timed_mutexesblogpercona….
Post: Fighting MySQL Replication Lag
September 22, 2008 By 7 Comments
…MySQL Replication Lag First fact you absolutely need to remember is MySQL Replication is single threaded, which means if you have any long…long running updates. Queries or transactions containing multiple update queries which add up to long time… update/delete and having multiple queries to delete it …
Post: Load management Techniques for MySQL
May 6, 2012 By 3 Comments
… frequent cases with performance problems with MySQL is what they happen every so often or certain times. Investigating them we find out… need to know it is not MySQL problem, might be even not problem with your MySQL configuration, queries and hardware, even though… with monopolizing replication thread. For example if I need to delete old data instead of DELETE FROM TBL WHERE ts
Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By 3 Comments
… their salaries on time!). mysql> delete from salaries where emp_no = 10001; Query OK, 17 rows affected (0.15 sec) mysql> drop table salaries…_Ignore_DB mysql-bin.000022 336796712 Using the coordinates above, I can start searching for the position of the DELETE statement so… this method is when you are planning to execute a long running ALTER or server upgrade, this method can be good…
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
… set (0.00 sec) mysql> delete from compressed where val=16; Query OK, 1 row affected (0.00 sec) mysql> select * from compressed…. Also, notice that it takes a long time to delete. There is no index on this table. mysql> delete from data where val=16; Query OK, 1 row affected (3.14 sec) mysql…
