In our last episode we learned how to modify data and table definitions.  This will come in handy as we look at building in referential integrity constraints into our database.  To begin we will need the database definition resulting from last episode's changes. You can download it here »

A word on Storage Engines

Before we can begin we need to understand a little about MySQL Storage Engines.  MySQL actually does only part of the job of parsing SQL, creating query plans, executing them and returning data sets.  Where the data is stored and retrieved there is a Storage Engine at work.  The original storage engine was MyISAM, based on the industry stalwart of ISAM …

Available for you now!  A free self-study lesson from the MySQL for Database Administrators course, the most popular course for DBAs and developers. In this interactive free lesson, students get an introduction to MySQL products and services, and an overview of the MySQL architecture.

Special Promotion for MySQL Customers with Oracle Premier Support
Get a 20% discount on Oracle Training. View this datasheet for more details.

For more information on MySQL Training, see our Learning Paths.

Available for you now!  A free self-study lesson from the MySQL for Database Administrators course, the most popular course for DBAs and developers. In this interactive free lesson, students get an introduction to MySQL products and services, and an overview of the MySQL architecture.

Special Promotion for MySQL Customers with Oracle Premier Support
Get a 20% discount on Oracle Training. View this datasheet for more details.

For more information on MySQL Training, see our Learning Paths.

Read the original article at Scale Quickly Like Birchbox – Startup Scalability 101

One of the great things about the Internet is how it has made it easier to put great ideas into practice. Whether the ideas are about improving people’s lives or a new way to sell and old-fashioned product, there’s nothing like a good little startup tale of creative disruption to deliver us from something old and tired.

We work with a lot of startup firms and we love being part of the atmosphere of optimism and ingenuity, peppered with a bit of youthful zeal - something very indie-rock-and-roll about it. But whether they are just starting out or already picking up pace every startup faces the same challenges to scale a business. Recently, we were reminded of this when we watched …

MySQL built-in replication includes a concept called relay slave, which allows you to create hierarchical database clusters. You can do the same thing with Tungsten, and this can be done in more than one way. Let's start with two distinct clusters. We can follow the recipes in the Tungsten Cookbook to install a master / slave cluster in three separate hosts and a …

I’ve never objected to someone making money from MySQL. I’ve only expressed disappointment that they weren’t doing it effectively enough. As I have predicted many times, Oracle is good at this. Oracle is the number one reason I didn’t start a new career in some other database a few years ago. Oracle is making MySQL more successful not only for Oracle, but also for the users, the community, and the competition.

We raised topic of problems with flushing in InnoDB several times, some links:

InnoDB Flushing theory and solutions
MySQL 5.5.8 in search of stability

This was not often recurring problem so far, however in my recent experiments, I observe it in very simple sysbench workload on hardware which can be considered as typical nowadays.

Hardware: HP ProLiant DL380 G6, with 72GB of RAM and RAID10 on 8 disks.

I took sysbench multi-tables workload, with 20 tables, 10,000,000 rows each. Total database size ~58GB.
MySQL version: 5.5.16

Initial benchmark, which …

I've been hanging out with the cool kids lately, and learning new technologies. One common problem I notice people have, is safely emulating atomic operations between keys without transactions. i.e. in the classic example:

User Account 1 has $100 in it.
User Account 2 has $100 in it.
Transfer $20 From 1 -> 2.

Now, assume that our database crashes during the balance transfer indicated above. What happens?

The most common way I have seen this emulated is using a third-party 'table' (a.k.a. a journal, or log) and a scheme that could probably be described as a two phase commit. I see subtle bugs in most implementations of this. I do not want to point any fingers, so I will show two naive examples in my own non-transactional database, MyISAM:

Example 1: Using the …

I've been hanging out with the cool kids lately, and learning new technologies. One common problem I notice people have, is safely emulating atomic operations between keys without transactions. i.e. in the classic example:

User Account 1 has $100 in it.
User Account 2 has $100 in it.
Transfer $20 From 1 -> 2.

Now, assume that our database crashes during the balance transfer indicated above. What happens?

The most common way I have seen this emulated is using a third-party 'table' (a.k.a. a journal, or log) and a scheme that could probably be described as a two phase commit. I see subtle bugs in most implementations of this. I do not want to point any fingers, so I will show two naive examples in my own non-transactional database, MyISAM:

Example 1: Using the …

One lesson learned in more than two decades working in this industry is that most of the IT professionals are impatient, want to achieve results immediately, and, most importantly, they don't read documentation. Much as the average geek is happy to answer many requests with a dismissive RTFM, the same geeks are not as diligent when it comes to learning about new or updated technologies. For this reason, there is a kind of documentation that is very much appreciated by busy and impatient professionals: cookbooks. And I am not talking about food. Geeks are not known for being cooks (1) and they like fast food. I am talking about collection of technical recipes, short articles where a problem is briefly stated, and a direct solution is shown. Working with Tungsten Replicator, I am constantly amazed at all the things you can do with it, and at the same time, I am amazed at how so few …