by kuszmaul | Jun 15, 2009 | MySQL
Executive Summary: A query like TPC-H Query 17 can be sped up by large factors by using straight_joins and clustering indexes. (This entry posted by Dave.) In a previous post, we wrote about queries like TPC-H query 2, and the use of straight_join to improve...
by Baron Schwartz | Jun 15, 2009 | Insight for DBAs
When I’m looking at a server, I often want to see the /etc/my.cnf file nicely formatted, and with comments stripped. This Perl one-liner will pretty-print the file: perl -ne 'm/^([^#][^\s=]+)\s*(=.*|)/ && printf("%-35s%s\n", $1, $2)' /etc/my.cnf [client]...
by Vadim Tkachenko | Jun 11, 2009 | Insight for DBAs
Playing with TokuDB updates I noticed in SHOW PROCESSLIST unsual for MySQL State. mysql> show processlist; +----+------+-----------+--------+---------+------+---------------------------+-----------------------------+ | Id | User | Host | db | Command | Time | State...
by Zardosht.Kasheff | Jun 11, 2009 | MySQL
This post is for storage engine developers that may be interested in implementing multiple clustering keys. After blogging about TokuDB’s multiple clustering indexes feature, Baron Schwartz suggested we contribute the patch to allow other storage engine to...
by Aleksandr Kuzminsky | Jun 11, 2009 | Percona Software
Dear community, We are pleased to announce the build16 of MySQL server® with Percona patches. Since the build13 there was a couple of customer specific releases, which explains cutover in numbering and a pause between the builds. Also we prepared build for both...
by Vadim Tkachenko | Jun 9, 2009 | Benchmarks
For one our project I needed proxy solution, and mysql-proxy is one of well-known, so it was logical first choice. The obvious question which come in mind is what performance penalty we have using mysql-proxy, version 0.7.1. So it is easy to test. (By the way sysbench...
by Vadim Tkachenko | Jun 8, 2009 | Percona Software
This is probably the feature I missed most from early days when I started to use InnoDB instead of MyISAM. Since that I figured out how to survive without it, but this is first question I hear from customers who migrated from MyISAM to InnoDB – can I just copy...
by Baron Schwartz | Jun 5, 2009 | Insight for DBAs
I wanted to share a little rule of thumb I sometimes use to choosing column order in indexes. This is not specific to MySQL, it’s generally applicable to any database server with b-tree indexes. And there are a bunch of subtleties, but I will also ignore those...
by Zardosht.Kasheff | Jun 4, 2009 | MySQL
I recently posted a blog entry on clustering indexes, which are good for speeding up queries. Eric Day brought up the concern that clustering indexes might degrade update performance. This is often true, since any update will require updating the clustering index as...
by kuszmaul | Jun 1, 2009 | MySQL
In this post we’ll describe a query that accrued significant performance advantages from using a relatively long index key. (This posting is by Zardosht and Bradley.) We ran across this query recently when interacting with a customer (who gave us permission to...
by Peter Zaitsev | May 31, 2009 | Insight for DBAs
This is mainly a cheat sheet for me to remember. Nothing rocket science. It often makes sense to use netcat/nc to copy MySQL database between hosts in trusted networks. It bypasses encryption overhead of SSH and depending on configuration can be significantly faster....
by Zardosht.Kasheff | May 28, 2009 | MySQL
Yesterday, I (Zardosht) posted an entry introducing clustering indexes. Here, I elaborate on three differences between a clustering index and a covering index: Clustering indexes can create indexes that would otherwise bounce up against the limits on the maximum...
by Zardosht.Kasheff | May 27, 2009 | MySQL
In this posting I’ll describe TokuDB’s multiple clustering index feature. In general (not just for TokuDB) a clustered index or a clustering index is an index that stores the all of the data for the rows. Quoting the MySQL 5.1 reference manual: Accessing a...
by kuszmaul | May 22, 2009 | MySQL
The TokuDB storage engine for MySQL employs Fractal Tree technology. We’ve been planning to write a white paper explaining how fractal tree indexing works, but haven’t gotten to it yet. In the mean time, here are links to some academic papers that relate...
by Peter Zaitsev | May 21, 2009 | Percona Events
I find it very interesting how Sun does not get the very basic principle of true community Open Source development – you’ve got to give up on making a big splash. Traditional close source company often develop product in the secret and when it comes out as...
by Peter Zaitsev | May 21, 2009 | Insight for DBAs
Every so often I run into a situation when I need to terminate connections on MySQL server – for example, hundreds of instances of some bad query is running, making the server unusable. Many people have special scripts which can take the user, source host, or...
by Vadim Tkachenko | May 21, 2009 | Percona Software
We announce next version of our xtrabackup tool and we consider it stable enough to put label RC on it. Changelist includes: – use O_DIRECT by default for handling InnoDB files – use posix fadvise call to disable OS caching of copying files – disable...
by Vadim Tkachenko | May 20, 2009 | Percona Software
Using xtrabackup for copying files can really saturate your disks, and that why we made special option --throttle=rate 1 --throttle=rate to limit rate of IO per second. But it really works when you do local copy. What about stream backup ? Even you copy just to remote...
by Peter Zaitsev | May 20, 2009 | Percona Events
I should say I can see results of new engineering/refactoring/bug hunt efforts inside Sun/MySQL. Over last couple of weeks I started getting a lot of messages from the bugs system about bugs I reported long ago which were deferred to be fixed later or were left in...
by Vadim Tkachenko | May 17, 2009 | Insight for DBAs
Sometimes when you do profiling you can see number like this in timestamps. Periodically in our patches there was Query_time: 18446744073709550.000 in slow.log file (well, it was fixed recently, but still appears in other places). I faced this problem several years...
