Working on customer case today I ran into interesting problem – query joining about 20 tables (thank you ORM by joining all tables connected with foreign keys just in case) which would take 5 seconds even though in the read less than 1000 rows and doing it completely in memory. The plan optimizer picked was very good one, yet you could notice EXPLAIN itself was taking same 5 seconds, which points to problem with optimizer performance. Note though if you have subqueries these might need to be executed during EXPLAIN phase yet making it unusable to check the optimizer performance.

Solution for this problem was to use set optimizer_search_depth=0, rarely used option which as per manual will chose best value automatically. Making this change I could bring optimization, and full query execution time to less than 50ms. …

Topics for this podcast:

*OpenStack, Amazon, Eucalyptus and Citrix engage in open cloud warfare
*Microsoft spins off new company for openness
*Updates on automation players Puppet Labs and Opscode with Chef
*Percona turns attention to MySQL high availability
*Open APIs as the fifth pillar of modern IT openness

iTunes or direct download (28:42, 4.9MB)

We are running internally a lot of benchmarks on our recently announced Percona XtraDB Cluster, and I am going to publish these results soon.
But before that I wanted to mention that proper benchmark of distributed system comes with a lot of challenges.
I am saying that not to complain, but to make sure, if you are going to benchmark XtraDB Cluster yourself, there is a lot of things to take into account.

And it seems that one component, which was not much important before, now appears as critical peace, which easily can became bottleneck in the benchmarks – this is network.

In case of simple client-server setup, the network is not fully utilized.

But as …

A customer recently was asking why our MySQL Performance Monitor (MPM) is not working on Windows...? The answer is short: It was developed on Linux and never tested on Windows...

But I was wondering how much effort it would take to make it work on Windows as well.

I was quite surprised how fast it was to make the basic functionality working on Windows. It took me less than one hour to install, configure and patch MPM.

Patch MPM

The file FromDualMySQLagent.pm has to be patched at 2 locations. The lock file name must be something understandable by Windows (for example C:\Temp\FromDualMySQLagent.lock. We will fix that in the next MPM release.

 40   # Should NOT be hard coded, tofix later!!!
 41   # Does not work on Windows!
 42   my $lAgentLockFile = '/tmp/FromDualMySQLagent.lock';
 43   # Check if lock file already exists and complain if yes
...
533   # Does not work on Windows!
534 …

Challenges of Big Databases with MySQL

Many database management tasks become difficult as you move from millions of rows and gigabytes of data to billions of rows and terabytes of data. Such tasks include ingesting data while maintaining indexes; changing schemas without downtime; and supporting connections, replication, and backup. For some scaling problems (connections and replication), MySQL is better than most of the competition. For others, such as indexing, schema changes, and backup, MySQL has typically been harder to use. Fortunately, the tasks MySQL does well are in its core, whereas the tasks that are more difficult can be solved with storage engine plug-ins.

This presentation discusses how MySQL’s storage engines have recently made dramatic progress in large …

This week we talk with Mark Atwood of RedHat about OpenShift, a free, auto-scaling Platform as a Service (PaaS) for applications. Use promotional code "oursql" when signing up!

News/Events/Feedback
We are very thankful for our listeners! Last week we won a MySQL Community Contributor of the Year award (Sarah Novotny, Gerry Narvaja and Sheeri Cabral, who have all been hosts, won the award). Here's a picture of the award we all won.

read more

Log Buffer Editions are marching along, and this Log Buffer #268 is once again all about Oracle, MySQL, and SQLServer plus some peeks at some of other glittering database technologies like PostgreSQL and DB2. Sit back and enjoy. Oracle: Martin has produced another scenario based blog post about Shrinking Tables to Aid Full Scans. What [...]

Ronald Bradford and Giuseppe Maxia (hey guys!) wrote about different ways to ignore a database when using mysqldump –all-databases over the past couple of days.

Whilst the solutions are interesting, I wondered why not attack it from the proper approach, and add the option to mysqldump itself? Honestly, the patch is trivial, and doing anything against INFORMATION_SCHEMA with lots of databases and tables … well let’s just say … group_concat_max_len is the least of your worries..

15 minutes later I had a working solution:

To my surprise, I also found Bug#3228, created a little over 8 years ago.. I’ve posted the patch …

Register for this webinar "Replication, Read-Consistent Slaves & Redundancy" where we'll discuss various replication solutions and explain why SchoonerSQL offers the best synchronous replication solution. The webinar will also cover performance benchmarks and how read-consistent slaves (redundant database nodes) can help your application stay online 24x7, providing 99.999% availability. 

Replication, Read-Consistent Slaves & Redundancy Thursday, April 26, 2012, 10 a.m. PT / 1 p.m. ET
Webinar link
http://info.schoonerinfotech.com/ReadConsistentSlavesWebinar


There is also another webinar on how you can "Free your Data and Budget".  This is a business discussion aimed at helping IT decision makers make better choices to protect …

I wanted to evaluate MariaDB's virtual column and see if it could store business rules next to table data. With virtual columns this could be done and if you specify as 'virtual', would not even take up space on disk.

The imaginary use case is that there is a sales team in a company and you would like to evaluate if a particular salesperson is eligible for a bonus. In order to get a bonus, you need to sell above the average for the day and be in the top 5 amongst the salespeople.

So here is what I did:

MariaDB [test]> create table salespeople (id int unsigned not null auto_increment primary key, salesperson_id int unsigned not null , `date` datetime not null default 0, sold decimal(15,2) not null default 0, day_avg decimal(15,2) not null default 0,  above_avg char(1) as …