When InnoDB compresses a page it needs the result to fit into its predetermined compressed page size (specified with KEY_BLOCK_SIZE). When the result does not fit we call that a compression failure. In this case InnoDB needs to split up the page and try to compress again. That said, compression failures are bad for performance and should be minimized.

Whether the result of the compression will fit largely depends on the data being compressed and some tables and/or indexes may contain more compressible data than others. And so it would be nice if the compression failure rate, along with other compression stats, could be monitored on a per table or even on a per index basis, wouldn't it?

This is where the new INFORMATION_SCHEMA table in MySQL 5.6 kicks in. INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX provides exactly this helpful information. It contains the following fields:

Ever since its first release, we are continuing consolidating and developing InnoDB Full-Text Search feature. There is one recent improvement that worth blogging about. It is an effort with MySQL Optimizer team that simplifies some common queries’ Query Plans and dramatically shorted the query time. I will describe the issue, our solution and the end result by some performance numbers to demonstrate our efforts in continuing enhancement the Full-Text Search capability.

The Issue:

As we had discussed in previous Blogs, InnoDB implements Full-Text index as reversed auxiliary tables. The query once parsed will be reinterpreted into several queries into related auxiliary tables and then results are merged and consolidated to come up with the final result. So at the end of the query, we’ll have all matching records on hand, sorted by their ranking or by their Doc IDs.

Unfortunately, MySQL’s optimizer and …

Read the original article at Anatomy of a Performance Review

A lot of firms come to us with a specific scalability problem. “Our user base is growing rapidly and the website is falling over!” Or they’re selling more widgets, “Our shopping cart is slowing down and we’re seeing users abandon their purchases”. These are real startup growing pains, so what to do?

We like to take a measured approach with these types of challenges, so we thought it would be helpful to run through a hypothetical scenario and see how we work.

Having trouble with scalability? Check out our 5 things toxic to scalability piece. …

For some of you who situated near New York City I am happy to announce that you could attend two events related to leading Full-Text search engines in open source – Sphinx Search.

First meeting organized by NYPHP meetup on Tuesday, September 25th at IBM, 590 Madison Avenue, New York. I’ll be speaking about search services in cloud environment and distributed search tips and tricks. Event is free, please RSVP.

One week later on October 1st, I’ll be doing tutorial about MySQL and Sphinx “Full-text based services with Sphinx and MySQL” …

This week we present the Sphinx Storage Engine. Ear Candy is a gotcha about setting variables in the configuration file, and At the Movies is a video about MariaDB.

Events
MySQL Connect will be held in San Francisco on Saturday September 29th and Sunday September 30th. The schedule is now online.

read more

While putting PostgreSQL 9.2 through it’s paces, I noticed some behavior that was eerily familiar. Back in January of 2006, Peter Zaitsev opened a bug against MySQL 4.1 that complained of a comparison of an out-of-range constant triggering a key lookup (later distilled to a feature request to “statically evaluate predicates using implicit type constraints”). [...]

Next gen app providers (and perhaps more specifically, database architects) are clamoring for database technologies that just work. At least, that’s the message we got from one of our newest customers: Mozilla. Earlier this month, we caught up with Sheeri Cabral, database architect at Mozilla and and overall MySQL rock star, to get the down-and-dirty on why [...] Read More

I wrote about the performance gains with libmysqld a few days ago but I had just too many things in my head to do a proper comparison with the MySQL Cluster / Server protocol. Yes, libmysqld is faster, but not as much faster as I thought, and blogged about. What happened was that I had another thing to try which I had forgotten about, which was to test using the Client / Server protocol without the dreaded CLIENT_COMPRESS flag (see more on this here).

Without CLIENT_COMPRESS, I could see NDB performance improve by some 25 - 30 %. But with InnoDB, which I just tested, I achieved some 98 k row reads per second! Yikes, I should have tested that one before comparing with libmysqld (in which case I got 115 k …

My now long-running series of posts on getting max performance from a very simple MySQL Cluster setup (see details here) is continuing here. As a short intro to what I am trying out here, is to see if I can validate the claim that MySQL Cluster / NDB would be a good replacement for a Key Value Store (KVS) such as MongoDB. I test this in a rather simple single-server environment, but this is for a reason, just not a coincidence: The reason is that RAM is getting inexpensive and servers that can take a lot of RAM are also getting less expensive, which in turns means that the saying that many small servers are more cost-effective that few big ones, might not be as valid as it used to be. Also, I wanted to test what MySQL Cluster can do for me, from a KVS centric view. In short, I run on one server (16 Gb RAM, 8 cores) with all data in …

Tomorrow, August 22 at 10:00am PDT, I’ll present a webinar called Full Text Search Throwdown.  This is a no-nonsense performance comparison of solutions for full text indexing for MySQL applications, including:

• LIKE predicates and regular expressions
• MyISAM FULLTEXT indexes
• InnoDB FULLTEXT indexes
• Apache Solr
• Sphinx Search
• Trigraphs

I’ll compare the performance for building indexes and querying indexes.

If you’re developing an application with text search features, this will be a very practical and informative overview of your technology options!

Register for this free webinar at  …