In the previous post of this series (which can be found here) I discussed how to identify tables that can serve as good candidates for sharding.

Once you have decided which tables should be sharded (all the rest should be global tables), the choice of sharding keys is rather straightforward, as most will use the table primary key as the shard key. Of course, if multiple tables are sharded, and there is a foreign key relationship between these tables, then the foreign key will serve as the shard key for some tables.

Many people attempt to shard based on customer_id or a resource id, but I have seen how this usually fails in production environments. It is very hard to know in advance which customers belong together in the same database, and since customers can suddenly increase their traffic, this might create an unbalanced situation in which some …

Review of the O’Reilly Strata Making Data Work Conference

(reprinted from my guest blog for the Cloud Council of 7)

Monica Rogati of LinkedIn told a story of the early days at the firm, when the reporting system consisted of a single server under someone’s desk. One day, someone needed an Ethernet cable and unplugged the machine from the data outlet in the wall. LinkedIn’s data reporting, its life blood, instantly came to a screeching halt.

The Push to the …

I’m sure you are aware that the last Percona server release includes a new improved MEMORY storage engine for MySQL.
This new engine is based on Dynamic Row Format and offers some of great features, specialy for VARCHAR, VARBINARY, TEXT and BLOB fields in MEMORY tables.

But because this new MEMORY engine by Percona has some limitations and because Percona server hasn’t used it for its internal temporary tables yet, I would like to talk about what can be the real benefits to have a brand new MEMORY engine based on Dynamic row format specialy for internal memory tables.

Just remember or discover how MySQL uses …

I will be joining a stellar class of speakers at the Insight Out DB Showcase in Tokyo from Oct 19-21, 2011. This event covers several RDBMS technologies including MySQL.

My topics are:

• Better MySQL Security and Administration
• Mastering MySQL Performance Tuning

Oracle last week quietely announced the addition of new extended capabilities in MySQL Enterprise Edition, confirming the adoption of the open core licensing strategy, as we reported last November.

The news was both welcomed and derided. Rather than re-hashing previous arguments about open core licensing, what interests me more about the move is how it illustrates the different strategies adopted by Sun and Oracle for driving revenue from MySQL, and how a single project can be used to describe most of the major strategies …

The very popular MySQL for Database Administrators course has been updated to bring you more on InnoDB, MySQL's default storage engine, more on MySQL tools and even better hands-on exercises. This course is the cornerstone curriculum for MySQL Database Administrators and brings you all the skills you need to be a great MySQL DBA.

In this course students learn how to secure users privileges, set resource limitations, access controls and describe backup and recovery basics. Students also learn how to create and use stored procedures, triggers and views.

Roughly three years ago I was writing about Direct MySQL Stream Access - a way to access the low-level stream PHP's mysqlnd library is using. Back then this had been a patch against PHP's mysqli extension. As such a feature is quite dangerous (you can easily mess with the connection state which confuses mysqlnd and/or the MySQL server) we didn't push it into the main PHP tree. Now three years later it's time to look at this again as we don't need to patch PHP anymore.

Since the mentioned patch was written mysqlnd got a plugin interface about which I was talking before. This plugin-interface, especially in the version of PHP 5.4, makes it trivial to implement this feature.

PHP_FUNCTION(mysqlnd_to_stream)
{
    zval *conn_zv;
    MYSQLND *conn;

    if …

The very popular MySQL for Database Administrators course has been updated to bring you more on InnoDB, MySQL's default storage engine, more on MySQL tools and even better hands-on exercises. This course is the cornerstone curriculum for MySQL Database Administrators and brings you all the skills you need to be a great MySQL DBA.

In this course students learn how to secure users privileges, set resource limitations, access controls and describe backup and recovery basics. Students also learn how to create and use stored procedures, triggers and views.

There has been a lot of discussions lately about various non-SQL languages that provide access to databases without having to resort to using SQL. I wondered how difficult it would be to implement such an interface, so as an experiment, I implemented a simple interface in Python that similar to the document-oriented interfaces available elsewhere. The interface generate SQL queries to query the database, but does not require any knowlegdge of SQL to use. The syntax is inspired by JQuery, but since JQuery works with documents, the semantics is slightly different.

A simple example would look like this:

from native_db import *
server = Server(host='127.0.0.1')
server.test.t1.insert({'more': 3, 'magic': 'just a test', 'count': 0})
server.test.t1.insert({'more': 3, 'magic': 'just another test', 'count': 0})
server.test.t1.insert({'more': 4, 'magic': 'quadrant', 'count': 0}) …

We've now come a long way since our first steps at creating our online bookshop database. Now we need to start to think about how to sell the books and store details about the sales.  This is the time we need to start understanding database transactions.

Database transactions are very similar to real world transactions. They define a set of steps required to happen together in order for a transaction to be complete.  A real-world example might be that you buy a trinket from a store.  You find the trinket, then take it to the counter, find out the price, hand over the cash and receive your trinket.  That is a completed transaction.  Should you not have the available cash, the transaction would not be able to be completed and you would need to return the item, effectively rolling back that …