As part of Percona Remote DBA for MySQL service we recognize that reliable backups are one of the most important things we can bring to the table. In my experience handling emergencies, the single worst thing that can happen is finding out you don’t have backups available when some sort of data loss or catastrophic [...]
Using MySQL Sandbox with Percona Server
February 16, 2013 By 3 Comments
One of the most useful tools if you’re working with multiple versions of MySQL Servers is MySQL Sandbox which allows you to maintain many different versions of MySQL, Percona Server, MariaDB. If you’re just working with single sandbox you can just use MySQL Sandbox in its most basic way and it will work:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | root@smt2:~/sandboxes# make_sandbox /tmp/Percona-Server-5.5.29-rel29.4-401.Linux.x86_64.tar.gz unpacking /tmp/Percona-Server-5.5.29-rel29.4-401.Linux.x86_64.tar.gz Executing low_level_make_sandbox --basedir=/tmp/5.5.29 \ --sandbox_directory=msb_5_5_29 \ --install_version=5.5 \ --sandbox_port=5529 \ --no_ver_after_name \ --my_clause=log-error=msandbox.err ... no_run = no_show = do you agree? ([Y],n) Y loading grants ... sandbox server started Your sandbox server was installed in $HOME/sandboxes/msb_5_5_29 |
However [...]
The Optimization That (Often) Isn’t: Index Merge Intersection
December 14, 2012 By 6 Comments
Prior to version 5.0, MySQL could only use one index per table in a given query without any exceptions; folks that didn’t understand this limitation would often have tables with lots of single-column indexes on columns which commonly appeared in their WHERE clauses, and they’d wonder why the EXPLAIN plan for a given SELECT would [...]
Knowing what pt-online-schema-change will do
November 1, 2012 By 1 Comment
pt-online-schema-change is simple to use, but internally it is complex. Baron’s webinar about pt-online-schema-change hinted at several of the tool’s complexities. Consequently, users often want to know before making changes what pt-online-schema-change will do when it runs. The tool has two options to help answer this question: –dry-run and –print. When ran with –dry-run and –print, pt-online-schema-change changes nothing [...]
Recovering from a bad UPDATE statement
October 19, 2012 By 5 Comments
Did you just run an UPDATE against your 10 million row users table without a WHERE clause? Did you know that in MySQL 5.5 that sometimes you can recover from a bad UPDATE statement? This is possible if you are running in binlog_format=ROW ! Imagine this scenario:
1 2 3 4 5 6 | CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` varchar(10) NOT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB; INSERT INTO `t1` (`c2`) VALUES ('michael'), ('peter'), ('aamina'); |
We run an accidental UPDATE statement that [...]
Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
I presented a webinar this week to give an overview of Tools and Techniques for Index Design. Even if you missed the webinar, you can register for it, and you’ll be emailed a link to the recording. I’d like to invite folks who are interested in tools for query optimization to attend the new Percona [...]
How to lag a slave behind to avoid a disaster
September 11, 2012 By 5 Comments
MySQL Replication is useful and easy to setup. It is used for very different purposes. For example: split read and writes run data mining or reporting processes on them disaster recovery Is important to mention that a replication server is not a backup by itself. A mistake on the master, for example a DROP DATABASE [...]
Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6
August 30, 2012 By 2 Comments
I got the message in the morning today about the bug being fixed in MySQL 5.6.6…. which I reported in Early 2006 (while still being with MySQL) and running MySQL 4.1 I honestly thought this issue was fixed long ago as it was indeed pretty annoying. I must say I’m very impressed with Oracle team [...]
MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
I had a lot of questions on my MySQL Indexing: Best Practices Webinar (both recording and slides are available now) We had lots of questions. I did not have time to answer some and others are better answered in writing anyway. Q: One developer on our team wants to replace longish (25-30) indexed varchars with [...]
A case for MariaDB’s Hash Joins
May 31, 2012 By Leave a Comment
MariaDB 5.3/5.5 has introduced a new join type “Hash Joins” which is an implementation of a Classic Block-based Hash Join Algorithm. In this post we will see what the Hash Join is, how it works and for what types of queries would it be the right choice. I will show the results of executing benchmarks [...]
