If you haven’t seen it, Josh Berkus has a very concise way to look at the confusing mess that is database “clustering” from the point of view of three distinct types of users: transactional, analytic, and online. I think that using this kind of distinction could help keep discussions [...]
Ever been around a group of people discussing some technology and heard Cool-Whip phrases like this?
It’s not about MySQL versus PostgreSQL, it’s about using the right tool for the job.
Or how about this one?
You need to take the important factors into account before you decide whether [hot new fad] or [...]
I was at OpenSQL Camp 2009 in Portland last weekend. I thought the event was very well done. On Friday we had a pizza party at Old Town Pizza, which was awesome. Saturday and Sunday were breakfast, sessions, lunch (yum), and sessions and hacking. These were held at [...]
My favorite quote from last weekend’s OpenSQL Camp was something like this: “Calling a database NoSQL is like calling a car horseless. It isn’t descriptive.” I think the term NoSQL really needs to die. It is meaningless, divisive, overly broad, and just generally unhelpful.
Related posts:[...]
You used to use mk-query-digest to aggregate and report on MySQL’s slow query log. Then it got the ability to grab query events from polling SHOW PROCESSLIST. Next we thought, really, how hard can it be to implement the libmysql wire protocol, so we can sniff TCP packets? … [...]
Last week I attended the Enterprise LAMP Summit and Camp in Nashville, Tennessee. I enjoyed the event and met or reconnected with a lot of great people. I was glad to be able to spend time with some folks from the Postgres community. My own sessions focused on MySQL.
During [...]
My Dell laptop’s battery died (slowly). Thinking I was being clever, I bought a replacement part from a name brand instead of Dell. That one died, too, but much more spectacularly. If I buy another battery, this time from Dell, I’ll have spent more on batteries than on the laptop.
This [...]
Optimizing Oracle Performance
Optimizing Oracle Performance. By Cary Millsap, O’Reilly 2003. Page count: about 375 pages with appendices. (Here’s a link to the publisher’s site.)
This is easily one of the best books I’ve ever read on performance optimization. I’ve just finished reading [...]
Someone recently posted this to an email list as a sample of an interesting SHOW INNODB STATUS output:
mysql> SHOW ENGINE INNODB STATUS\G _______ _______ |\ /|( ____ \( ____ \ | ) ( || ( \/| ( \/ | | | || (_____ | (__ | | | |(_____ )| __) | | | | [...]MySQL Proxy is a really neat tool. I remember a few years back when I first saw Jan talking about it. Back in those days it was significantly different than it is now, but the concept remains the same: direct your database traffic through a man-in-the-middle. Chris Calender’s post [...]
As an alternative to another recent blog post that answered the question “how can I truncate all the tables in my database,” I thought I’d show another way to do it, which does not use the INFORMATION_SCHEMA.
$ wget http://www.maatkit.org/get/mk-find $ perl mk-find --exec 'TRUNCATE TABLE %D.%N'The other example is how [...]
I’ve released version 1.1.4 of my improved Cacti templates. Unlike the prior release, which was solely bug fixes, this one includes new graphs in the MySQL template. Some of the graphs are of data that’s exposed in standard MySQL versions, but some of it is available only in [...]
Ever seen this in SHOW INNODB STATUS and wondered what it means?
------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 4634, seg size 4636,I’ve never been quite sure, and Peter didn’t really clarify things himself, so I took a look at the source. If I’m not mistaken, [...]
The Art of Capacity Planning
The Art of Capacity Planning. By John Allspaw, O’Reilly 2008. Page count: 130 pages. (Here’s a link to the publisher’s site.)
This is an outstanding book. As far as I know Ewen Fortune was the first Perconian [...]
I’ve just released version 1.1.3 of the Cacti templates I wrote for MySQL. This is a bug-fix release only, and affects only ss_get_mysql_stats.php. To upgrade from the previous release, upgrade ss_get_mysql_stats.php. Don’t forget to save and restore your configuration options, if any. (Note that there is a feature to [...]
Maatkit’s mk-loadavg tool is a helpful way to gather information about infrequent conditions on your database server (or any other server, really). We wrote it at Percona to help with those repeated cases of things like “every two weeks, my database stops processing queries for 30 seconds, but [...]
Do you know what this is?
debian:~/mysql-cacti-templates/t# php mysql.php not ok 1 - basic test to check that the test suite works # Failed test (/root/mysql-cacti-templates/t/mysql.php at line 9) # got: Array ( [b] => 2 ) # Looks like you failed 1 tests of 1.It is the beginning of a test suite, which is long [...]
I’ve bought my plane tickets for OpenSQL Camp US 2009, in Portland, Oregon on the weekend of November 14th. I’m really looking forward to the event and seeing friends again. And I’m psyched that the third incarnation of this event is taking place! I wish I could have been [...]
Yesterday I attended CPOSC 2009. The conference was great. It was very well run, and I liked the sessions. I would definitely attend this conference again, and will recommend that Percona sponsor it next year. I attended the following talks:
Stop Worrying and Start Monitoring with Nagios (Andrew Libby) DRBD, Network [...]A client recently asked me to fix some Cacti graphs that had broken after upgrading the Cacti templates I wrote for MySQL. The symptoms were weird; I’m not sure I understand fully what happened, but some of the graphs were OK and some had only part of the data [...]
A friend of mine works for a company that’s starting a place called OpenSpace. They did a funny YouTube ad for it. If you work from home, give it a look — it brought a smile to my face. I love the “trying to write on a whiteboard [...]
I am speaking at Enterprise LAMP Summit 2009 (and also should be speaking at the Camp the next day, but I see the schedule isn’t quite updated yet). My talk at the Summit will not be on the future landscape of MySQL [...]
I’ll be attending and presenting at the 2009 Central Pennsylvania Open-Source Conference. My session is on Maatkit. I see Tom Clark has a session on MySQL performance! I hope to see you there — I’ve really become a fan of these regional conferences.
By the [...]
I’ve been investigating a few different storage engines for MySQL lately, and something I’ve noticed is that they all list what they support, but they generally don’t say what they don’t support. For example, Infobright’s documentation shows a list of every data type supported. What’s missing? Hmm, I don’t [...]
Gotta love those three-dimensional charts. They sure are shiny! Look at this:
FusionCharts graphs
This is a screenshot of FusionCharts graphs. But wait, on closer inspection: 5 and 7 are the same height? 6 is exactly twice as tall as 2?
Pseudo-three-dimensional charts are the bane of my [...]
Key-value databases are catching fire these days. Memcached, Redis, Cassandra, Keyspace, Tokyo Tyrant, and a handful of others are surging in popularity, judging by the contents of my feed reader.
I find a number of things interesting about these tools.
There are many more of them than open-source traditional relational databases. (edit: [...]I used to develop with MySQL, and those were the golden days. These days I don’t write queries so much. But yesterday I needed to answer this question: are there any issues in our issue-tracking system that meet the following criteria?
The last two or more emails are from the customer These [...]Spammers seem to have gotten smarter recently, and able to post garbage to Google Groups. There was some spam on a couple of the groups I’ve created, and members helped me find a way to stop it. I’m glad they did, because since then it’s stopped an increasing flow of [...]
There’s a really important difference between a unique index (MySQL’s answer to a “unique constraint”) and a primary key in MySQL. Please take a look at this:
CREATE TABLE `t` ( `a` int, `b` int, `c` int, UNIQUE KEY `a` (`a`,`b`) )The combination of columns a, b should uniquely identify any tuple [...]
A lot of times I’m looking at several time-series samples of numbers embedded in free-form text, and I want to know how the numbers change over time. For example, two samples of SHOW INNODB STATUS piped through grep wait might contain the following:
Mutex spin waits 0, rounds 143359179688, OS waits [...]I’ve been thinking recently about the failure scenarios of MySQL replication clusters, such as master-master pairs or master-master-with-slaves. There are a few tools that are designed to help manage failover and load balancing in such clusters, by moving virtual IP addresses around. The ones I’m familiar with don’t always do [...]
A short time ago in a galaxy nearby, Domas Mituzas wrote about contention profiling with GDB stack traces. Mark Callaghan found the technique useful, and contributed an awk script (in the comments) to aggregate stack traces and identify which things are blocking most threads. I’ve used it myself a [...]
I’m going to present on Maatkit at the CPOSC conference in central Pennsylvania on Saturday, October 17th 2009. I’ll give an overview of the toolkit, which is no longer an easy task in a single session. I see a number of other interesting sessions have been accepted. It [...]
There are two OpenSQL Camp events in 2009. This is a conference about any and all open-source databases. The slogan of the inaugural event was “of, by, and for the community.”
The first of this year’s events just ended last weekend in Germany, and I’m really sad that neither I [...]
Newer Linux kernels have per-process I/O accounting and you can use the iotop tool to find out what’s performing I/O, but in many cases I’m trying to find the source of an I/O problem in an older kernel. I found sort of a hack-ish way to do that today, while [...]
Beginning Database Design: From Novice to Professional
Beginning Database Design: From Novice to Professional. By Clare Churcher, Apress, 2007. Page count: 230 pages. (Here’s a link to the publisher’s site.)
My wife bought a copy of this book, and recently I took it [...]
You probably know that it’s possible to set configuration variables to log queries that don’t use indexes to the slow query log in MySQL. This is a good way to find tables that might need indexes.
But what if the slow query log isn’t enabled and you are using (or [...]
I’m going to present at the EdUIConf conference 2009. This is a conference focused in two directions: Web professionals in higher education, and higher education for web professionals. I believe it’s going to be comparable to, or at least in the same vein as, some of the more popular [...]
Every once in a while, I find myself needing to round a number up to the nearest even power of 10, or round a time interval to the nearest quarter of an hour, or something like that. This is actually quite simple, but for some reason I always find myself [...]
Ergohuman Executive Chair With Headrest
I’ve upgraded my office chair to the Ergohuman Executive Chair With Headrest. I had a chair that was less adjustable and was leather, so in the warmer summer months it was not very pleasant to sit on.
So [...]
Do you test your application systematically when you upgrade or reconfigure your database server? You should! Here’s a real (anonymized) story of what happens if you don’t.
When we upgraded to 5.0.62 (from 5.0.27 and 5.0.45), our code broke for queries like this:
SELECT SUM(amt) FROM daily_amt WHERE day = FROM_UNIXTIME(1222889772);
The problem here [...]
A while ago I wrote about a tool to help make upgrades safer. Since then, we have gotten several people to help sponsor development on this tool, and a few of our customers are using it to help find problems before they upgrade their systems.
I can’t think of a [...]
Maatkit version 4334 is ready for download. I see that I missed posting a release announcement about last month’s release of Maatkit. I’ll try to cover the important bits about the last two releases here. Daniel has been posting the release announcements to the mailing list recently, so I’ll [...]
Ryan posted an article on the MySQL Performance Blog about how to use mk-query-digest to analyze and understand your memcached usage with the same techniques you use for MySQL query analysis. This is an idea that came to me during the 2009 MySQL Conference, while talking to our friends [...]
If you don’t like DRM (Digital Restrictions Management), there are lots of ways to protest it. One of the simplest and most effective is to pick a massively popular DRM’ed device, and express your dismay to the manufacturer and users. Here’s an idea:
Browse to the DRM-laden Amazon Kindle’s [...]Cruising through my blog reader today, and came upon this blog post from Zmanda:
Our price increased today. Now we are one-tenth the cost of Symantec.That is absolute genius. It is impossible to ignore, and will probably be remembered longer than almost anything else they could have written.
Related posts:[...]
My brother sent me these thoughts, slightly edited:
Recently I looked at the [Wikipedia] Linux page. To my surprise it’s not about the kernel Linux, it’s about the “OS”. I looked at discussion and it appears that by general consensus the term Linux applies to the unix like operating system, and [...]
I just adore Google Code. But the default wiki view (a list of pages, sorted by last-modified) is lacking something. Fortunately, it’s fixable. Here’s the before:
Default wiki list
And here’s the after:
Wiki with sidebar and default page
Here’s how:
Create [...]
MySQL Administrator's Bible
MySQL Administrator’s Bible by Sheeri K. Cabral and Keith Murphy, 2009. Page count: 800+ pages. (Here’s a link to the publisher’s site.)
This book is a comprehensive reference guide to MySQL that’s accessible to beginning DBAs or DBAs familiar with another [...]
I’ll be attending the IT Management Conference later this year in New York. My proposals weren’t chosen so I won’t be speaking, but I wanted to attend whether I’m speaking or not. It should be a very useful conference for those in or near management or leadership positions. Email [...]
Suppose that you’re writing a new Maatkit tool (just a random example, really) and its job is to measure the difference in execution of queries. The simplest metric is execution time.
Now suppose that you’re trying to figure out a metric of badness. The query executes in a second on [...]
I’m using Google Docs for a project I’m working on right now, and I ran into a problem with spacing. There appears to be no way to get spacing between paragraphs. You can control spacing between lines, but that’s not the same thing. Actually, it’s quite easy. You just [...]
Last weekend, my brother and I attended SELF 2009. A few thoughts on it:
The mixture of sessions was interesting. There were some really good ones. I think the best session I attended was an OpenSolaris/NetBeans/Glassfish/Virtualbox/ZFS session, given by a Sun employee. He was an excellent presenter, and really showed [...]
Seen in #maatkit on Freenode:
I never realized just how terrible recovering MyISAM from a crash can be
Sad but true — it can be pretty painful. This is one of the reasons I pretty much recommend InnoDB (okay, okay, XtraDB) for most data unless it’s read-only.
Related posts:[...]
I’m happy that Lenz Grimmer and Giuseppe Maxia have organized an OpenSQL Camp event in Germany in August. Hooray!
[...]Just thought it was worth pointing out that Percona Xtrabackup is not just for XtraDB. It works great for InnoDB tables, too.
So if mysqldump can’t handle it anymore, LVM snapshots kill your server and you don’t want to buy proprietary backup software, you might take a look at Xtrabackup.
[...]As you can probably guess, I’m catching up on reading my blogs. I’ve just read with interest about TokuDB’s multiple clustering indexes. It’s kind of an obvious thought, once someone has pointed it out to you. I’ve only been around products that insist there can be Only One clustered [...]
Tokutek have said they are working towards explaining their indexing algorithms. I spoke to some of the Tokutek people over the last 14 months or so about this, although I didn’t really start to pay attention until the beginning of the year. While Vadim, Peter and I were writing [...]
In the last six months, things have gotten much busier in the world of MySQL performance. That is, making MySQL and InnoDB scale faster out of the box. This is a great trend and I hope it keeps going. At this point I’m fighting to find enough time to read [...]
I tweaked the mext script so it auto-detects the necessary column widths for each sample.
Get mext here. [...]It seems that a lot of people want to try to improve MySQL performance by focusing on server status counters and configuration variables. Looking at counters, and “tuning the server,” is better than nothing, but only barely. You care first and foremost about how long it takes to execute a [...]
I’ve had several customers in the last week or so who need a way to verify that their application will work well after an upgrade. I’m seeking input on a new tool to help with MySQL upgrades. Please add comments, either here or on the bug report, or [...]
One of the minor things I’ve noticed when I use Thunderbird on my IMAP accounts is that it doesn’t download IMAP messages until I click on them. Then I have to wait until I can see the message. I have heard that this is supposed to be fixed in Thunderbird [...]
A while ago I posted about a comment a Sun performance engineer made about a scalable replacement for InnoDB. At the time, I did not believe it referred to Falcon. In hindsight, it seems even clearer that the Sun performance experts were already working hard on InnoDB itself.
Sun’s [...]
In the past I have taken a somewhat neutral stance on the non-Free nature of the MySQL documentation, but after the recent discussion about it I’ve reconsidered. I have changed my position; now I see the restrictions on the documentation as a serious problem for the community and partner ecosystem.
The [...]
I’ve packaged up and released version 1.1.2 of the Cacti templates I’ve written for MySQL, Apache, memcached, nginx etc.
Anyone who would like to help write documentation (or do anything else, for that matter) is welcomed to participate. I’ll give commit access at the drop of a hat.
Changelog:
2009-05-07: version 1.1.2 * [...]As I was driving with a colleague in California a couple of weeks ago during the conference, the topic of conversation turned to the notion that Percona and the rest of the MySQL community really need the presence of a central entity that provides a recognized home for the MySQL [...]
Maatkit version 3519 is ready for download. There are a lot of changes in this release, many of which are incompatible with previous releases. There are also a lot of important new features. Read on for the details.
First, thanks to everyone who contributed to this month’s release. A [...]
If you code by writing tests that fail, and then fixing the tests by writing the code, then you might find yourself switching to a terminal, running the test, ad nauseum. Part 1 of my tip is to run the test in a loop that takes a single keystroke to [...]
Domas Mituzas mentioned this recently. It’s so cool I just have to write about it. Here’s an easy command to fork off a bunch of jobs in parallel: xargs.
seq 10 20 | xargs -n 1 -P 5 sleepThis will send a sequence of numbers to xargs, which will divide [...]
At the MySQL conference, a person who used to hold an important position in an important sales organization told us something like the following: “You know, you guys at Percona are great, but you have a big problem. You don’t have any $500,000 customers who only file one support incident [...]
Peter wrote about this recently, but I don’t know if it was really clear what was going on.
Point One: Sphinx can be contacted by the MySQL protocol. Not “as a MySQL storage engine.” Not “from MySQL.” It understands the MySQL protocol itself. So from the protocol point of [...]
Cacti is a great tool for collecting information about systems and graphing it. However, it likes to use SNMP, and SNMP is often not desirable. Instead, I often see the need for a method that is:
Secure. Use trusted, well-known, encrypted communication. Do not open up new ports. Zero install on [...]The slides for my session on Maatkit at the MySQL Conference are uploaded. They should appear on the speaker presentation files page, but I have heard that it can take a few days. Please let me know if they’re not there by May 1, 2009.
I did not have [...]
Selena Deckelmann has posted some ideas about a “hacker’s cooperative” for PostgreSQL.
[...]As you may have noticed, Calpont has been advertising themselves as an open-source storage engine for MySQL. And yet there is no source code.
A bit of back-story. When we were choosing speakers for the Percona Performance Conference, I personally reviewed Calpont’s submission and sent them an [...]
MySQL 5.4 was released this week at the MySQL conference, and billed as the “community release.” This seemed odd to some of us, and I heard lots of comment on it in the hallways. After all, the release was a surprise; the community didn’t know it was happening.
A source from [...]
This morning’s MySQL Conference keynotes had a number of inaccuracies, but one that I wish to point out is the characterizations of Drizzle. It looks to me like some Sun/Oracle/MySQL folks are trying to take credit, now that they’re seeing it become a resounding success, for the good work [...]
How Life Imitates Chess, by Garry Kasparov, 2008. 204 pages.
I’ve been wanting to write a review of this book for a while. I forget now exactly when I got it, but I have greatly enjoyed reading it. I have read it several times. It’s a [...]
I’m presenting about Maatkit, the toolkit I created to make life better with MySQL, at the MySQL conference next week.
I’m going to give you a whirlwind tour throught some of Maatkit’s features and functionality. The toolkit is much too large and complex to cover more than a small [...]
Having written about what I think is cool about the upcoming MySQL Conference and the MySQL Camp, now I want to finish up with what I’d like to see at the Percona Performance Conference. Just to recap, this is a conference we created to serve those who want to [...]
It’s a feature preview with many limitations, but this is still good news. This has been a pretty severe performance limitation for replication in MySQL, which has prompted many a workaround.
Interestingly, the feature preview is based on MySQL 5.1, which has recently seemed to be getting some [...]
I wrote previously about what I’m looking forward to at the upcoming MySQL Conference (next week!). Now I want to write about the free, community-organized unconference being held concurrently, MySQL Camp 2009.
It runs from Sunday through Thursday of next week, which is even longer than the MySQL conference. It [...]
This is really cool. I just learned this: you can treat a Perl variable just like a filehandle (read, write, etc).
my $buffer = ""; open my $fh, ">", \$buffer or die $OS_ERROR; print $fh "hello, world\n";Now $buffer contains “hello, world\n”. You can do the same kinds of things when reading from a [...]
I haven’t really decided my schedule yet during the conference, but I thought I’d mention these sessions that look interesting to me.
I’m presenting a session on how to use Maatkit, which I think attendees will get a lot of benefit from.
Tuesday This is Not a Web App: The Evolution [...]I always say that the ultimate MySQL tuning script is an expert human. To that end, I generally try to build tools that help a human be more productive with the raw information from MySQL. One of the things we look at during a performance audit is the MySQL [...]
Some unsettling things happened in MySQL in the past week or so.
New storage engine not mentioned in the changelogThere’s a bit of a storm brewing over at the MySQL Performance Blog, where Vadim reports discovering a new storage engine added without mention in the 5.1.33 changelog. This is [...]
I want to show you two very expensive SQL idioms that almost everyone uses. These are not query optimization problems — they are financially expensive patterns of SQL.
These queries don’t do what the user thinks they do, and thus they create subtle bugs. And the bugs cause a lot of [...]
As I said yesterday, I was only able to attend a couple of talks at the PostgreSQL Conference East 2009 this morning before leaving to catch my flight back to Virginia.
Effects of Flash and SSDs on PostgreSQL — by Jignesh Shah of Sun Microsystems. Jignesh went through a [...]I missed Friday and part of Saturday, but I was at PostgreSQL Conference East 2009 for part of Saturday (and will be for part of Sunday too.) A brief recap of the talks I attended:
PostgreSQL Backup and Recovery — by Kevin Kempter of Consistent State. I arrived halfway [...]WARNING: nearly every word of this post is intentionally false. This is an April Fool’s joke.
There is so much misinformation out there about how MySQL works. If you’ve talked to someone from PostgreSQL, you’ve surely heard some of it: “MySQL doesn’t even have transactions,” for example. And this from [...]
Maatkit version 3329 has been released, as per our usual monthly release cycle.
Significant changes in this version include some bug fixes to mk-duplicate-key-checker, and a lot of new features in mk-query-digest. We made some changes to mk-table-checksum and mk-table-sync. We also updated shared code in all tools.
Interesting and significant [...]
SQL and Relational Theory
SQL and Relational Theory How to Write Accurate SQL Code by C. J. Date, O’Reilly 2009. Page count: 266 pages of “real” text, plus hefty appendixes. (Here’s a link to the publisher’s site: SQL and Relational Theory How [...]
Here’s a quick productivity tip: when optimizing queries by rewriting them to different forms that should return the same results, you can verify that you get the same results by taking a checksum of them.
Just set your pager to md5sum:
mysql> pager md5sum - PAGER set to 'md5sum -' mysql> select * from [...]I’ve made plans to attend part of Saturday and part of Sunday at PostgreSQL Conference East 2009. I wish I could be there the whole time. Anyway I’m just thrilled that I’ll get to see people again, and meet new people I didn’t get to meet before.
I think Josh [...]
