With Performance Schema improvements in MySQL 5.6 I think we’re in the good shape with insight on what is causing performance bottlenecks as well as where CPU resources are spent. (Performance Schema does not accounts CPU usage directly but it is something which can be relatively easily derived from wait and stage information). Where we’re still walking blind with MySQL is resource usage – specifically Memory Usage.

I can’t count how many time I had to scratch my head with system configured to consume only few GBs in global buffers growing to consume much more for some unknown needs leaving me puzzled whenever it is user variables, complex stored procedures temporary tables or something else. Not only such connection related allocations are invisible but many global allocations are poorly visible too. Sure, we know how much memory was allocated for Innodb Buffer Pool or Query Cache but amount of memory used for …

Two MySQL Tech Tours will happen during January in the Bay Area. Pleasanton will be January 22nd followed the 23rd by Belmont.

Big Data and High Availability with MySQL

Please join us for our Tech Tour event hosted by Oracle MySQL experts and learn more about Oracle’s strategy for MySQL, including continued investment in the world’s most popular open source database. The MySQL experts will be on hand to introduce new features in the MySQL 5.6 GA and discuss the new commercial extensions.

Register for any of our sessions and learn about:

MySQL High Availability Strategies (including …

This podcast would not achieve its goal if there were no listeners to learn about MySQL. This week we play a bunch of bloopers as our year-end gift to you. We hope these make you laugh!

2011 Blooper Retrospective

I think MariaDB has had a great few weeks recently and the timeline of these events are important.

1. 27 November 2012 – WiredTree Adds MariaDB for Faster MySQL Database Performance (well worth reading their motivations to switch)
2. 29 November 2012 – Monty Program & SkySQL release the MariaDB Client Library for C & Java
3. 4 December 2012 – MariaDB Foundation is announced, see ZDNet coverage.
4. mid-December 2012 – Wikimedia Foundation starts migrating …

One of the most complex problem I’ve seen as a developer is dealing with some kind of hierarchy in a data model. It’s not much about storing the relationship but more it comes to generating reports that take into account this hierarchy.

In this post, I will expose a simple approach I’ve found while dealing with these issues. For the sake of this demonstration we will use the following scenario:

You are managing an affiliate program in which you pay users $5 for each user they refer. On top of that you have a 3-tier commissions on purchases these users make. You pay 5% of the total amount to direct referrals, 2% on level 2 referrals and 1% on level 3 referrals.

Table Structure

Here is the table structure we will be using for this scenario.

Users

As it's the end of the year it might be time to check your partition definitions.

If you forget to add a new partition in time partitions with no MAXVALUE might start to throw errors:

mysql> create table nye (`event_id` int not null auto_increment,`edate` year(4) not null, description varchar(200), 

    -> primary key(`event_id`,`edate`)) 

    -> partition by range( edate ) (

    -> partition p2010 VALUES LESS THAN (2011), 

    -> partition p2011 VALUES LESS THAN (2012), 

-> partition p2012 VALUES LESS THAN (2013) );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO nye(edate,description) VALUES('2010','twenty ten'); 

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO nye(edate,description) VALUES('2011','twenty eleven');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO …

Since I started working as a software engineer, I’ve seen a lot of people misusing or trying to avoid MySQL Join statement. These statements can actually be quite handy when used properly.

In this post, I will demonstrate how to effectively use the Join statement with some common use cases. The scenario will be the following:

You have a list of users and track their activities. You want to build an internal reporting tool.

Table Structure

Here is the table structure we will be using for these common use cases.

Users

Moral of the upcoming story: when your systems rely on someone else’s software, make sure you test it thoroughly to understand how it works.

I found a couple of interesting things about the go-mysql-driver driver for Go. One is that it prepares every query before executing it, and then closes it afterwards, if you’re just using the db.Query() or db.QueryRow() functionality. There is zero benefit to this; Bill Karwin has probably listed it as an antipattern somewhere. I asked for one query, but I got three.

The other is that it doesn’t open the database connection when you call db.Open(). You can call that function and get no error. The first query on the resulting “db” object will actually connect to MySQL. Thus it’s actually kind of like the other major opensource Go/MySQL driver ( …

This is a recurrent question made by our MySQL Support customers:

How can I audit the login attempts in MySQL?

Logging all the attempts or just the failed ones is a very important task on some scenarios. Unfortunately there are not too many audit capabilities in MySQL Community so the first option to audit MySQL’s authentication process is to get all the information we need from logs.

General Query Log

The first option is the General Query Log. Let’s see an example:

Enable the log:

general_log_file        = /var/log/mysql/mysql.log
general_log             = 1

User correctly authenticated:

121227  8:31:49       38 Connect   root@localhost on 
                   38 Query     select @@version_comment limit 1

User not correctly …

This year’s last log buffer edition looks at the marvelous and splendid blog posts of Oracle, SQL Server and the MySQL databases which promise to bring more and more next year. This Log Buffer #301 promises to be the anchor for yet another action packed blogging carnival next year.

Oracle:

Kristin Rose is asking to join hands and hearts this holiday season with Oracle.

Phill Hill has a nice post about Open as in Transparent: Instructure Conducts 2nd Public Security Audit on …