Recently, I was working with a client that asked about using Percona Xtrabackup to take a snapshot of a particular MySQL schema and then reload it with a different schema name on the same server. It caught me off guard because I’d never really thought about it – typically, I’ve used Xtrabackup simply to clone […]
As an instructor with Percona I’m sometimes asked about the differences between the READ COMMITTED and REPEATABLE READ transaction isolation levels. There are a few differences between READ-COMMITTED and REPEATABLE-READ, and they are all related to locking.
Here’s a quick tip I know some of us has overlooked at some point. When doing SELECT … UNION SELECT, where do you put the the INTO OUTFILE clause? On the first SELECT, on the last or somewhere else? The manual has the answer here, to quote: Only the last SELECT statement can use INTO […]
I’ve heard this question a lot, but never thought to blog about the answer. “Is there a performance difference between putting the JOIN conditions in the ON clause or the WHERE clause in MySQL?” No, there’s no difference. The following queries are algebraically equivalent inside MySQL and will have the same execution plan.
SELECT * FROM A, B WHERE A.ID = B.ID;
SELECT * FROM A JOIN B ON A.ID = B.ID;
SELECT * FROM A JOIN B USING(ID);
The feature I announced some time ago http://www.mysqlperformanceblog.com/2009/06/08/impossible-possible-moving-innodb-tables-between-servers/ is now available in our latest releases of XtraBackup 0.8.1 and XtraDB-6. Now I am going to show how to use it (the video will be also available on percona.tv). Let’s take tpcc schema and running standard MySQL ® 5.0.83, and assume we want to copy order_line […]
One of my favorite MySQL configurations for high availability is master-master replication, which is just like normal master-slave replication except that you can fail over in both directions. Aside from MySQL Cluster, which is more special-purpose, this is probably the best general-purpose way to get fast failover and a bunch of other benefits (non-blocking ALTER […]
This is the fourth post in our MySQL Fabric series. In case you’re joining us now, we started with an introductory post, and then discussed High Availability (HA) using MySQL Fabric here (Part 1) and here (Part 2). Today we will talk about how MySQL Fabric can help you scale out MySQL databases with sharding. Introduction At the […]
In my previous post about geo-spatial search in MySQL I described (along with other things) how to use geo-distance functions. In this post I will describe the geo-spatial distance functions in more details. If you need to calculate an exact distance between 2 points on Earth in MySQL (very common for geo-enabled applications) you have at […]
I’ve been running a benchmark today on my old test box with conventional hard drives (no raid with BBU) and noticed something unusual in the CPU utilization statistics being reported. The benchmark was run like this:
sysbench --num-threads=64 --max-requests=0 --max-time=600000 --report-interval=10 --test=oltp --db-driver=mysql --oltp-dist-type=special --oltp-table-size=1000000 --mysql-user=root --mysql-password=password run
Which means: create 64 threads and hammer the database with queries as quickly as possible. As the test […]
This is the third post in our MySQL Fabric series. If you missed the previous two, we started with an overall introduction, and then a discussion of MySQL Fabric’s high-availability (HA) features. MySQL Fabric was RC when we started this series, but it went GA recently. You can read the press release here, and see this blog post from Oracle’s Mats […]