We at ScaleBase talk about sharding so much, it’s difficult for us to see why someone wouldn’t want to shard. But just because we’re so enthusiastic about our transparent sharding mechanism, it doesn’t mean we can’t understand the very basic question, “When do I shard?”
Well, it’s not the most difficult question to answer. I’ll keep it short: if your database exceeds the memory you have on a single machine, you should shard. If you hit I/O, your performance suffers, and sharding will assist.
Why? That’s easy to explain.
Databases in general (and MySQL is no exception) try to cache data. Because accessing memory is so much faster than accessing disk (even with SSDs), database providers have developed rather sophisticated caching algorithms. For instance, running a query caches the query and its results. Indexes are stored in memory so that, when running a query, the database doesn’t have to hit the disk …

This is the third blog post in a series designed to assist companies who wish to migrate their code from Oracle to MySQL. You can read the previous post here.

I went over some of the difficult topics you’ll face when migrating from Oracle to MySQL. However, I left out the topic of database scalability (after all – this is a ScaleBase blog).

Oracle users are used to having a very clear scalability path. You start with an Oracle Standard edition, and if your budget allows, you increase hardware (memory, CPU), improve your storage speed, buy Oracle Enterprise edition and use portioning. If all that fails, you move to a distributed RAC environment. If you’re really on the high end, you buy ExaData2. This is where your journey ends. There is nothing “better”.

That’s great for enterprise …

This is the second blog post in a series designed to assist companies who wish to migrate their code from Oracle to MySQL.

In the first post of the series I tried to explain why you would like your web platform to run on a MySQL database, and not on an Oracle database. In this post, I’ll try to focus on the changes that you need to plan for when migrating from an Oracle environment.

Code

Probably the most obvious change is in code. There is no way around it – you’ll have to change your code.

1. SQL statements.
   While ANSI SQL 92 is a standard, Oracle offers extensions to the spec – and those are used by most developers, sometimes without their being aware of it.
   Of course, when moving to MySQL, those SQL statements will need to change. Some will require only minor …

This is the first blog post in a series designed to assist companies who wish to migrate their code from Oracle to MySQL.

During the World War II “Battle of the Bulge”, General McAuliffe said to the German forces who asked for his surrender: “Nuts!” The rest is history – he won the battle, and the allied forces won the war.

Some things are like that. So absurd that “Nuts” is the only possible reaction. And frankly – running your web infrastructure on an Oracle database is one of those things.

Now, the pricing issue is very well covered. Just see here. And for most people, this should be enough. We had a customer migrating from a 7M USD environment to a 200K yearly environment (licensing and support) – definitely worth the migration hassle!

But it’s not …

ScaleBase is happy to sponsor the Percona Live London MySQL Conference. If you plan to attend, you can catch our booth on the expo floor or attend Liran’s session – “The Benefits of Database Sharding” at 2PM, October 25th at the Bishopsgate Suite.

In the previous post of this series (which can be found here) I discussed how to migrate your data once you have decided how to shard your schema.

Once your data is sharded, it’s time to modify your application code. I will not dive into the many open source platforms that provide partial sharding support (Hibernate Shards, Gizzard, and the like), and will take Java (sorry, old habits are hard to overcome) as an example – however, the same holds true for any programming language.

Without Using ORM

If you wrote your code without an Object/Relational Mapping tool, kudos to you. Sharding will be easier, as you control the SQL statements.

Upgrading Connection Pool

Your first task is to write a connection pool that is “sharding” aware.  The class should look something like this:
…

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 …

This is the first in a three part series of blogs in which I’ll try to explain how to take an existing application and shard it.

Database Sharding has proven itself a very successful strategy for scaling relational databases. Almost every large web-site/SaaS solution uses sharding when writing to its relational database. The reason is pretty simple – relational database technology is showing its age and just can’t meet today’s requirements: a massive number of operations/second, a lot of open connections (since there are many application servers talking to the database), huge amounts of data, and a very high write ratio (anything over 10% is high when it comes to relational databases).

Many sites and blogs posts explain what sharding is, for example here and …

Backing up data is critical for production databases – and there are a lot of well-known solutions for backing up databases.

When the database is sharded, backing up data becomes problematic. If the backup is not synchronized across all shards, data inconsistency might occur. In this blog post I’ll try to detail the possible backup scenarios for sharded databases when using ScaleBase.

Backup Types

Let’s start by understanding the different backup types that are out there. You can read all about it here.

A physical backup involves copying all database files to a different location. Copying can take several hours for a decent database if it’s done to a disk or a tape. It might take only seconds if the database files reside on SAN/NAS storage hardware that supports snapshot technology.

A logical backup is a …

Despite my best intentions, I haven't posted on this blog for a while, which is a shame! I've become busy writing on so many other places since I moved into my new role in the Oracle Linux product management team in April. I've learned a lot and I am feeling quite at home here! The team is excellent and very nice to work with — I am slowly getting the "Big Picture".

But even though I've been neglecting this blog, there are a lot of things that are publicly visible and document some of my activites:

I've created two podcasts for the Oracle Linux podcasts:

• OCFS2: Features and Benefits
• …