Today, one of the MySQL database server response time has been increased within the pool of servers and noticed it was returning the query in 0.20 secs (randomly) as opposed[...]

Not much to add really to the bug I’ve filed here: bug#67099.

I personally can think of some very nasty consequences of applying this on the slaves I manage, and the reason I’m posting the bug is that while I guess this is too late to fix in 5.6 as it’s effectively a new feature, I’m sure many sites may bump into this and be somewhat disappointed if they want to use the new GTID feature and have several slaves.  Hence, if the fix/feature has to go into MySQL 5.7 then I hope it goes in sooner rather than later. We will see.

Updated: 2013-09-19

I probably should have updated this earlier but it does seem that Oracle have taken these comments on board. See: WL6559.  It looks like they plan to do this for 5.7 which is good news. I’m really hoping …

While the index merge access types certainly are useful for a number of queries, there has been some frustration expressed both from customers and the community about how it...

1. is not used when it should have been
2. is used when ref access is obviously better
3. merges suboptimal indexes
4. is too restricted in which conditions can be used

I could come up with numerous examples of related bugs and feature requests dating back more than six years. To list a few: 17673, 30151, 23322, 65274, …

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