From 31 minutes to 2 seconds Hot Indexing Overview

TokuDB v5.0 introduces several features that are new to the MySQL world. Recently, we posted on HCAD: Hot Column addition and Deletion. In this post, we talk about Hot Indexing.

What happens when you try to add a new index, as follows?

mysql> create index example_idx on example_tbl (example_field);

In standard MySQL 5.1 InnoDB, the table example_tbl gets locked while all indexes, including the primary key, get rebuilt. In the InnoDB plugin for 5.1, as well as in previous releases of TokuDB, things are improved in that the table is only locked while the one index is built. This still however can easily cause hours of downtime.

…

If you're using Innodb and transactions with JDBC and your MySQL application, you should know about a couple of debugging features that made their way into MySQL Connector/J 5.1.15.

First, by adding "includeThreadNamesAsStatementComment=true" to your JDBC URL, you will get the current Java thread's name that is executing SQL on a given connection as a statement comment, visible in SHOW FULL PROCESSLIST output:

mysql> show full processlist;
+------+------+-----------------+------+---------+------+----------+------------------------------------------------------+
| Id   | User | Host            | db   | Command | Time | State    | Info                                                 |
+------+------+-----------------+------+---------+------+----------+------------------------------------------------------+
...                                        |
| 2939 | test | localhost:59339 | test | Query   |    5 | Updating | /* java …

Why use PBMS?

I have talked to people about why they should use PBMS to handle BLOB data often enough, so I was surprised when someone asked me where they could find this information and I discovered I had never actually written it down anywhere.  So here it is.
If you are unfamiliar with PBMS, PBMS stands for PrimeBase Media Streaming. For details please have a look at the home page for BLOB Streaming.
  Both MySQL and Drizzle are not designed to handle BLOB data efficiently. This is not a storage engine problem, most storage engines can store BLOB data reasonably efficiently, but the problem is in the server architecture itself. The problem is that the BLOB data is transferred to and from the server as part of the regular result set. To do this both the server and the client must allocate a buffer large enough to hold the entire BLOB. DBMSs …

Memcached is one of the technologies that holds the modern Internet together, but do you know what it actually does? Brian Aker has certainly earned the title of Memcached guru, and below he offers a peek under the hood. He'll also provide a deeper dive into Memcached in a tutorial at the upcoming 2011 MySQL Conference.

What problem is Memcached meant to solve?

Brian Aker: In an operation like a database or an application …

We are very excited to announce the availability of the 2.1 version of Calpont InfiniDB Community.  This release includes support for the std, stddev, stddev_pop, stddev_sample, var_pop, var_samp, and variance statistical functions.  The release also includes support for enclosed by characters within the cpimport utility as well as a number of bug fixes that you can see at http://bugs.launchpad.net/infinidb.

You can download the latest InfiniDB binaries, source code, and...

And I mean that, come see me, say hello, buy me a beer (extra points!). Or even more so, come see my session at 10:50 on tuesday morning April 12. I'll be speaking on how to manage large datasets in an Amazon EC2 environment, and this is largely based on my experiences at doing just that at my new job (or new, I've been doing it for more that 6 months now) as Database Architect at Recorded Future.

This will not be an incredibly technical presentation, in terms of showing actual code and things. Rather, I will look at some of the issues when running in an EC2 environment, and how we manage it here at Recorded Future. Also, I will present a bit of how our architecture works, which is more relevant that one may thinks, as we have Cloud based architectures on mind all the time, all our development, testing and productions servers run in the cloud.

Anyway, this is …

Version 1.2 of Slave Readahead is now available for download here. If you don't know what this little project is about, it is used to pre-warm the MySQL Cache for the Replication thread on MySQL slaves.

It is built to support MySQL 5.5 and up only, as it uses some new commands in MySQL (like the SHOW RELAYLOG EVENTS admin command). For more information regarding this little project, either read this blogpost or download the documentation for the project.

/Karlsson

“The least expensive query is the query you never run.”

Data access is expensive for your application. It often requires CPU, network and disk access, all of which can take a lot of time. Using less computing resources, particularly in the cloud, results in decreased overall operational costs, so caches provide real value by avoiding using those resources. You need an efficient and reliable cache in order to achieve the desired result. Your end users also care about response times because this affects their work productivity or their enjoyment of your service. This post describes some of the most common cache methods for MySQL.

Popular cache methods

The MySQL query cache

When the query cache is enabled, MySQL examines each query to see if the contents have been stored in the query cache. If the results have been cached they are used instead of actually running the query.. This improves the response time …