Understanding Indexing – NY Effective MySQL Meetup

Oct 07, 2011 | MySQL

At next week’s NY Effective MySQL Meetup, I will give a talk: “Understanding Indexing: Three rules on making indexes around queries to provide good performance.” The meetup is 7 pm Tuesday, October 11th, and will be held at Hive at 55 (55 Broad Street, New York, NY). Thanks to host Ronald Bradford for the invitation. […]

Indexing: The Director’s Cut

Jul 15, 2011 | MySQL

Thanks again to Erin O’Neill and Mike Tougeron for having me at the SF MySQL Meetup last month for the talk on “Understanding Indexing.” The crowd was very interactive, and I appreciated that over 100 people signed up for the event and left some very positive comments and reviews. Thanks to Mike, a video of […]

Understanding Indexing – SF MySQL Meetup

Jun 21, 2011 | MySQL

At this week’s SF MySQL Meetup, I will give a talk: “Understanding Indexing: Three rules on making indexes around queries to provide good performance.” The meetup is 7 pm tomorrow (Wednesday, 6/22), and will be held at CBS Interactive (235 2nd St., San Francisco). Thanks to hosts Erin O’Neill and Mike Tougeron for the invitation […]

Effective MySQL, a New York City Meetup

Apr 26, 2011 | MySQL

Kudos to Ronald Bradford for creating a new MySQL meetup group in New York city and giving MySQL related talks. The next one is tonight, titled “MySQL Idiosyncrasies That Bite”. Information on it can be found at http://ny.effectivemysql.com/events/16884850/. We’ll have a contingent from our New York office there this evening. We went to the last […]

Understanding Indexing – MySQL Meetup

Mar 16, 2011 | MySQL

Yesterday, at the Boston MySQL Meetup, I gave a talk on indexing. It is posted here (also goo.gl/S2LBe). In short, indexes are used to improve query performance. As a result, good indexes are designed around queries that users find important in their application. The presentation covers three simple and effective rules on how to construct […]

Scenarios where TokuDB’s Loader is Used

Sep 16, 2010 | MySQL

TokuDB’s loader uses the available multicore computing resources of the machine to presort and insert the data. In the last couple of posts (here and here), Rich and Dave presented performance results of TokuDB’s loader. Comparing load times with TokuDB 2.1.0, Rich found a 2.1x speedup on a 2 core machine, and a 4.2x speedup […]

On “Replace Into”, “Insert Ignore”, Triggers, and Row Based Replication

Aug 11, 2010 | MySQL

In posts on June 30 and July 6, I explained how implementing the commands “replace into” and “insert ignore” with TokuDB’s fractal trees data structures can be two orders of magnitude faster than implementing them with B-trees. Towards the end of each post, I hinted at that there are some caveats that complicate the story […]

On “Replace Into”, “Insert Ignore”, and Secondary Keys

Jul 21, 2010 | MySQL

In posts on June 30 and July 6, I explained how implementing the commands “replace into” and “insert ignore” with TokuDB’s fractal trees data structures can be two orders of magnitude faster than implementing them with B-trees. Towards the end of each post, I hinted at that there are some caveats that complicate the story […]

Why “insert … on duplicate key update” May Be Slow, by Incurring Disk Seeks

Jul 14, 2010 | MySQL

In my post on June 18th, I explained why the semantics of normal ad-hoc insertions with a primary key are expensive because they require disk seeks on large data sets. I previously explained why it would be better to use “replace into” or to use “insert ignore” over normal inserts. In this post, I explain […]

Making “Insert Ignore” Fast, by Avoiding Disk Seeks

Jul 06, 2010 | MySQL

In my post from three weeks ago, I explained why the semantics of normal ad-hoc insertions with a primary key are expensive because they require disk seeks on large data sets. Towards the end of the post, I claimed that it would be better to use “replace into” or “insert ignore” over normal inserts, because […]

Making “Replace Into” Fast, by Avoiding Disk Seeks

Jun 30, 2010 | MySQL

In this post two weeks ago, I explained why the semantics of normal ad-hoc insertions with a primary key are expensive because they require disk seeks on large data sets. Towards the end of the post, I claimed that it would be better to use “replace into” or “insert ignore” over normal inserts, because the […]

Making Updates Fast, by Avoiding Disk Seeks

Jun 22, 2010 | MySQL

The analysis that shows how to make deletions really fast by using clustering keys and TokuDB’s fractal tree based engine also applies to make updates really fast. (I left it out of the last post to keep the story simple). As a quick example, let’s look at the following statement:

Executing this statement has […]

Disk seeks are evil, so let’s avoid them, pt. 4

Jun 18, 2010 | MySQL

Continuing in the theme from previous posts, I’d like to examine another case where we can eliminate all disk seeks from a MySQL operation and therefore get two orders-of-magnitude speedup. The general outline of these posts is: B-trees do insertion disk seeks. While they’re at it, they piggyback some other work on the disk seeks. […]

Making Deletions Fast, by Avoiding Disk Seeks

Jun 08, 2010 | MySQL

In my last post, I discussed how fractal tree data structures can be up to two orders of magnitude faster on deletions over B-trees. I focused on the deletions where the row entry is known (the storage engine API handler::delete_row), but I did not fully analyze how MySQL delete statements can be fast. In this […]

Disk seeks are evil, so let’s avoid them, pt. 3 (Deletions)

Jun 02, 2010 | MySQL

As mentioned in parts 1 and 2, having many disk seeks are bad (they slow down performance). Fractal tree data structures minimize disk seeks on ad-hoc insertions, whereas B-trees practically guarantee that disk seeks are performed on ad-hoc insertions. As a result, fractal tree data structures can insert data up to two orders of magnitude […]

Disk seeks are evil, so let’s avoid them, pt. 2

May 25, 2010 | MySQL

In part 1, I discussed why having many disk seeks are bad (they slow down performance), and how fractal tree data structures minimize disk seeks on ad-hoc insertions, whereas B-trees practically guarantee that disk seeks are performed on ad-hoc insertions. As a result, fractal tree data structures can insert data up to two orders of […]

Disk Seeks are Evil, so Let’s Avoid Them, Part 1

May 20, 2010 | MySQL

Disk seeks are expensive. Typically, a disk can perform no more than a few hundred seeks per second. So, any database operation that induces a disk seek is going to be slow, perhaps unacceptably slow. Adding disks can sometimes help performance, but that approach is expensive, adds complexity, and anyhow minimizing the disk seeks helps […]

TokuDB Indexes are NOT in-memory (and not hash tables either)

Apr 15, 2010 | MySQL

Another plug for Bradley’s talk Thursday morning at the MySQL User’s conference. Spending the day talking to DBA’s and other potential users of TokuDB, I (Zardosht) noticed the same question/theme come up numerous times in conversation. “Oh, so your indexes are in memory, that is why iiBench is so much faster for TokuDB than InnoDB”. […]

New TokuDB 2.2.0 feature: more query progress information

Nov 17, 2009 | MySQL

Last spring, we added a feature that allows the user to see the progress of writes in a statement. Vadim liked it. In 2.2.0, in “show processlist”, we add progress information on reads. Here is an example of what “show processlist” displays on an update:

Here is an example of what “show processlist” displays […]

Better Primary Keys, a Benefit to TokuDB’s Auto Increment Semantics

Aug 03, 2009 | MySQL

In our last post, Bradley described how auto increment works in TokuDB. In this post, I explain one of our implementation’s big benefits, the ability to combine better primary keys with clustered primary keys. In working with customers, the following scenario has come up frequently. The user has data that is streamed into the table, […]