Have you ever wanted to get a list of indexes and their columns for all tables in a MySQL database without having to iterate over SHOW INDEXES FROM ‘[table]’? Here are a couple ways…

The following query using the INFORMATION_SCHEMA STATISTICS table will work prior to MySQL GA 5.6 and Percona Server 5.5.

SELECT table_name AS `Table`,
       index_name AS `Index`,
       GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns`
FROM information_schema.statistics
WHERE table_schema = 'sakila'
GROUP BY 1,2;

This query uses the INNODB_SYS_TABLES, …

Today at a conference I was approached with a task, typical for a modern web app.

We have a chat system, and need to store and show all messages in the system.
There is no limit as to how long you store, and how much you can see.

There are two types of queries:
- get all incoming messages for a given user, in chronological order, with pagination.
- show a dialogue of two users, in chronological order, with pagination.

A user is identified by 32 bit uid.
A message can be uniquely identified by sender_uid and created_time (32 bit), or uid (destination user) and created_time.

If you store the whole thing in a single table, <uid, created_time, sender_uid, message &gt, you're messed up with random reads when you need to show a user inbox in chronological order.

If you store the same message in two places, you get the same mess, but at write …

Cloud databases have a memory problem.   Continuent has been doing a lot of Amazon deployments lately, and it is becoming apparent that memory utilization in those environments is more than just an inconvenience.  In this article I would like to discuss the memory problem that we see in customer implementations and some new features of Tungsten Enterprise that help alleviate it for MySQL.

The Cloud Memory Problem and Database Arrays

As I discussed in a recent article about prefetch, the amount of RAM allocated to the InnoDB buffer pool is one of the principle determinants of MySQL performance.  The speed difference between using a page in the buffer pool …

World’s leading online address book, Plaxo, deploys SchoonerSQL instead of Oracle database.Watch this on-demand webinar where Ethan Erchinger, VP of Operations and SolutionArchitecture at Plaxo, talks about the CIO challenge he faced and why he chose to deploySchoonerSQL instead of Oracle.Also hear how SchoonerSQL offers IT decision makers an alternative that delivers both highperformance and high availability at a lower license cost than status-quo solutions.
http://info.schoonerinfotech.com/MBPSSTP_OND.html?at=Recorded-Webinar&an=M-T3S-StopTradingPerformance

Let’s be clear from the get-go: contrary to what this picture and the title of this post may suggest, we are not giving Oliver - SkySQL’s honorary mascot - away next week! He was very good, though, to pose for this picture to help us better illustrate the size of this inflatable seal! We’ll let your imagination decide what else this photo represents. Or better yet, join us in Santa Clara, next week, at what’s looking to be a pretty exciting gathering of MySQL-aficionados!

That’s right, the SkySQL & MariaDB: Solutions Day for the MySQL Database and Percona Live MySQL Conference & Expo events are just around the corner!

So what’s the deal with the Consulting Lounge?

Well, next Friday (April 13th), at the SkySQL and MariaDB …

Percona has released GA version of the Percona XtraDB Cluster.

Percona XtraDB Cluster is free, open source solution and is based on MySQL 5.5.21 community edition. It has all the goodies coming from Percona Server patches which Percona has further merged to work with with the replication API patches by Codership. And for replication, Percona XtraDB Cluster uses the Galera replication library 2.0.
Check out Percona announcement

Percona MySQL Support also now features a Percona XtraDB Cluster option. With this Percona Support option, users of Percona XtraDB Cluster are assured of high quality, cost effective, 24X7 support for their production MySQL deployments.

I was honored to get two talks accepted at the MySQL conference this year, but interestingly both of them are actually only indirectly about MySQL.  

The first is called “Writing non-blocking code for interaction with data systems and web services in Node.js and Perl”.  This sounds fancier than it is, I picked up some non-blocking programming in Perl and a bit of Node.js (which, strangely, is pretty straightforward once you write some Perl AnyEvent) my last year or so at Yahoo.  This talk really is just meant to be a gentle introduction to what this type of coding is, how it works, and some of the things you can do with it.  My goal is to present something I could have …

I was honored to get two talks accepted at the MySQL conference this year, but interestingly both of them are actually only indirectly about MySQL.  

The first is called “Writing non-blocking code for interaction with data systems and web services in Node.js and Perl”.  This sounds fancier than it is, I picked up some non-blocking programming in Perl and a bit of Node.js (which, strangely, is pretty straightforward once you write some Perl AnyEvent) my last year or so at Yahoo.  This talk really is just meant to be a gentle introduction to what this type of coding is, how it works, and some of the things you can do with it.  My goal is to present something I could have …

The MySQL query cache is a special buffer, where database stores the text of a SELECT statement together with the corresponding result that was sent to the client. For as long as no table that a statement refers to changes in any way, including the contents, the cached result can be re-used to answer any identical sub-sequent SELECT statements. But how to tell whether a query was executed or returned from the cache?

There are at least three ways to check it.

Method 1

MySQL exposes a number of runtime statistics that are accessible with SHOW STATUS statement. Among the long list of various counters, one is called Com_select which shows how many times a SELECT statement was executed. However if a SELECT is served from the query cache, it does not actually execute, so it is not accounted in Com_select. The conclusion must be that if a query runs, but it …

I am excited to announce the availability of the GA release of our new product Percona XtraDB Cluster.

Percona XtraDB Cluster is a High Availability and Scalability solution for MySQL Users and is based on Percona Server 5.5.20.

With this release we make clustering very easy and affordable for everyone. You can convert your existing MySQL replication setup into a cluster in just a couple minutes.

Tomorrow, 4-April-2012, I will be giving a webinar where I will explain what Percona XtraDB Cluster is and how does it …