I recently came across an interesting blog post on RedMonk (not surprising, as I read most of their posts). It’s called It’s Beginning to Look a Lot Like SQL and basically it talks about query language for NoSQL databases. It seems that as NoSQL becomes more popular, users want to do more with it – a good level of querying, for example, is needed.

Now of course, since NoSQL is a family of products that work in radically different ways, it’s not certain that this is possible (or even desirable – read Alex Popescu’s post on the subject).

But my question is – why do you even need a query language for NoSQL data stores? After all, running queries on distributed data might be complex to implement, and time consuming. The better architecture would look like this:

The application code is accessing NoSQL for primary key based access. Write operations go to NoSQL and MySQL, and complex queries (from the code, reporting, or BI systems) run with standard SQL (popular and familiar to most developers out there) against the MySQL database.

It’s a similar concept to Data Warehouse, only updates occur on the MySQL store in real time.

Now, of course, there are scaling and performance issues associated with running writes on MySQL, and that’s where ScaleBase fits in – as tt will allow MySQL to perform as required.

As we approach our general availability release, we’re starting to see popular usage scenarios for ScaleBase. I thought this would be a great time to publish three of the common scenarios we see. I’ll probably publish more in the next blog post.

Machine Generated Data

I personally love this term, coined by Curt Monash (see here). It means that the data is generated by machines (sensors, RFID tags, etc) and the amount of data is growing at a much faster pace than human generated data. In my previous, pre-startup, life I’ve seen some examples of machine generated data – and all the applications had major database issues owing to the incredible number of writes the databases had to handle.

ScaleBase looks like a perfect solution to this situation – as it demonstrated at numerous POCs of companies who do Machine Generated Data. I’m still not at liberty to discuss specific customers, but that will change soon, I promise.

The concept of massive writes (machine generated data systems can reach 60% and even 70% writes) is supported very nicely with ScaleBase. Because each database feels only a small portion of the overall system write operations, unlimited database hits/second can be supported. In some POCs, we have reached thousands of writes per second – and the ScaleBase instances weren’t even close to choking.

Online Gaming

Online Gaming is another topic altogether. Massive writes are accompanied by a massive number of reads, and latency of reads is critical. Many social gaming applications work on cloud environments (like Amazon EC2) and basically try to keep most of their data in the database cache. This is a good technique, but limited by the size of machines that Amazon supports – which is not too large (read more in the great Baron Schwartz post here).

That scenario is ScaleBase bread and butter. Since we make every database smaller in size,  each database can store most of its data in cache, resulting in a major performance improvement.

On a personal note, as a long time gamer I am very excited to see which companies are evaluating ScaleBase. My only regret is that Sierra Online didn’t survive to run Quest For Glory 15 on ScaleBase

SaaS Solutions

Well, of course SaaS solutions can suffer from massive writes or from overly large databases, but I want to address a different topic – multi-tenancy.

We hardly ever talk about multi-tenancy with ScaleBase, but we offer great multi-tenancy solutions. We can store data on a specific customer in the nearest geographic database, or ensure that an EU customer’s details are stored in an EU data center. So you can meet regulatory demands, or improve database response time – in a way that is transparent to the application.

This is just another capability of our sharding solution; instead of dividing data based on a hash function you can shard data based on a predefined list or range of values. We saw quite a few POCs that targeted this feature – and they worked like a charm.

I was always told about the “rule of three” – so I just focused on the most popular scenarios we’ve seen. I’ll look into some other scenarios in my next post, which will probably be published next week.

You may have heard a few months back news that California radio broadcaster Harold Camper promised the world would begin to end, starting on May 21. Like them all, the rapture as intended, was filled with fear to unsettle the weak and frail and to grab some spotlight. Well, at this writing it hasn’t happened and we can continue with our normal, daily lives.  Similarly, you probably don’t live on this planet if you haven’t heard of the GigaOM interview with Prof. Michael Stonebraker, titled “Facebook Trapped in MySQL-  ‘Fate Worse than Death’”.   Many have written about it (honestly, most comments have been very negative).

Since Rev. Camper and Prof Stonebraker took similar tacts in making their points, we at ScaleBase thought the best way to settle the nerves of those still hiding under their beds awaiting their destiny would be in turn, join the crowd in fiction.  Our stance is likely more probable though, In what Facebook IT would do. Please find our supposition of their response to Prof Stonebraker’s recent call for attention:

Dear Prof. Stonebraker:

First thing first – it is impossible to reduce your role in shaping the database world as we know it today.  Your mark has been noted many years ago.  However, with that said, we are not aware of much experience you have had with building massively scale sites. And frankly – this makes a huge difference – the difference between theory and demonstrated results of successful implementations.

So it is no wonder that we at Facebook and so many other people around the world can take exception to your recent interview that we face a “Fate worse than death”.

Let’s start with your first reason – Facebook runs thousands of servers, too many you state and poorly architected.  Facebook and any of the other popular web sites or large enterprises running internal applications manage this way, it is a very complex thing to do – BUT it is totally manageable.  More so – many SysOps managers can easily explain why the TCO of running thousands of standard hardware machines instead of few specialized machines is the ideal decision to make.

Why? Since with standard hardware, when one machine goes down – you can easily replace it. With specialized hardware – this is not the case. Training and hiring good ops guys (even more in the world of DevOps) for that specialized hardware is extremely expensive, and since IT people can be transient in nature the Total Cost of Ownership of those specialized machines is sky-rocketing. Just ask Oracle ExaData customers about their TCO experience.

So, unless you Prof. Stonebraker, suggest running all of Facebook off one server (which was not a statement explicitly made in the interview and had you, then your  argument would have been more absurd), than this is just a claim made, with no substance, and you aren’t suggesting a good solution either.

What we could understand from the interview is that VoltDB would require less servers than MySQL. Well, let’s assume, for discussion sake, that VoltDB is 4 times faster than MySQL, and it would require Facebook to have one fourth of the servers it currently uses (1000 instead of 4000, as it says) . This of course, ignores data locality needs (putting database servers in the Data Center that is closest to the customer), high availability requirements (have backup copies of the data) and other requirements.

Yet if you run 1000 database servers – it’s not better than running 4000 servers. It’s the same thing. And frankly – you’d rather run 4000 instances of a well-known solution like MySQL instead of 1000 instances of a never heard of solution like VoltDB. Just the risk of failure, the need for new, expensive DBAs of tribal knowledge, the changed eco-system (what kind of storage does VoltDB support) makes us cringe? Does it work with SSD? Tons of questions someone will have to research – all will eventually addup to a huge price hit for Facebook or any new startup out there who risks new development with VoltDB.

We were also confused about your suggestion that databases can work without caching. Every single application we know caches data from the database. It’s built into our O/R mapping layer, specialized code/libraries we use. You put a caching layer in-front of your application database because maintaining ACID is complex ; if you just need quick reads and don’t care about the latest update – a cache is great;if you don’t want the database overhead of consistency and locking (does VoltDB have some locking in place?) – you use a cache. If you don’t want the network overhead – you use a local cache. Are there any benchmarks published for your company’s performance without cache?

(We would love FaceBook guys to write this paragraph J) We do think the industry should take time looking more into a company called ScaleBase. They believe in running multiple database servers is a must, MySQL is a great solution, and you should continue using it. They also believe in cache and use NoSQL for relevant pieces of your data. But, when it comes to the database, your options of scaling-out are limited, and instead of writing your sharding code – you should use ScaleBase LoadBalancer . It seems to us their approach is pragmatic, grounded and less risky.

Just to summarize – we don’t fear the end is near, we are actually confident in our ability to continue scaling Facebook, we are proud of our accomplishments while enjoying well understood and tried and true standards such as MySQL, built to manage the problem we encounter . Likewise, we will continue to evaluate new innovations that come to market that uphold the promise of leveraging our previous investments in our technologies and people.

For the record, we are more like-minded to the great leader Franklin Delano Roosevelt with his rally for “Nothing to Fear”  as opposed to others grabbing attention by claiming the end is near.

FaceBook IT

I recently came across a good blog post from Venu Anuganti. It’s not new, but it’s still very relevant, and can shed some light on the issues I raised in my last post concerning TPCC performance results.

Just a quick reminder – MySQL is indeed a great relational database server and one that is used practically everywhere these days (check out the Alexa top 1000 list – it’s the most dominant database by far). However, when data and concurrent sessions need scale, MySQL might have a hard time keeping up. Throwing hardware at the problem will help, but eventually the database server itself becomes the bottleneck. The reason for this lies in the number of things the database needs to do for each user operation. For each query, for instance, the database needs to parse, optimize, find an execution plan, execute it, and manage transaction logs, transaction isolation, and row level locks.

So why doesn’t scaling up help? Because in some cases, adding memory and process constructs (buffers for example) above a certain point will result in a negative impact on performance. And that’s what Venu’s blog is all about.

So let’s dig into three very important memory constructs: table cache, read buffer, and sort buffer. These should grow as database data and concurrent sessions grow. However, beyond a certain value (which is not so high), you’ll suffer from a major decrease in performance.

Let’s take the sort_buffer_size parameter. It controls the size of the sort buffer allocated per user. It defaults to a very low value of 2M. If we scale-up to a machine with tons of free RAM, we might be tempted to increase this size to 64M or even 128M. However, this action will cause a major performance decrease, making performance up to six or even ten times slower. And the more concurrent sessions, the greater the performance penalty! Read Venu’s post for a more technical explanation, and this great post for tips on sort_buffer_size preferred sizes.

What can we learn from this example? That you can’t solve all your problems by throwing memory at them. The only solution you can count on for scaling your database is scale out. Have multiple MySQL instances serve the same load, and build your solution so you can add more machines when the load increases. The best way to do this is to have those databases independent of one another – a technique called sharding.

So if this is what you need, give ScaleBase a try, since we can give you sharding without needing you to do any of the heavy lifting.

I recently came across the results of a TPC-C benchmark on MySQL based RDS databases. You can see the paper here. I think the results may throw light on many questions concerning MySQL scalability in general and RDS scalability in particular.

TPC-C

TPC-C is a standard database benchmark, used to measure database performance. Database vendors invest big bucks in running this test and showing off which database is faster, and can scale better.

It is a write intensive test, so it doesn’t necessarily reflect the behavior of the database in your application. But it does give some very important insights into what you can expect from your database under heavy load.

The Benchmark Process

First of all, I have some comments on the benchmark method itself.

1. Generally, the benchmarking was  orderly and methodical – which increases the credibility of the results.
2. The benchmark generator client was tpcc-mysql which is an open-source implementation provided by Percona. It’s a good implementation, although I think DBT-2, provided by Apache, is more widely used.
3. Missing are the mix weights of the different TPC-C transactions in the benchmark.  The TPC-C benchmark contains six types of transactions (“new order”, “delivery”, “stock query”, etc.) and the mix weights are important.
4. The benchmark focused on throughput, not latency. I think that although TPC-C is mostly about throughput (transactions per minute) it’s recommended to address the latency (“avg response time” for example) as well.
5. TPC-C “number of warehouses” determines the size of the database being tested. The number of warehouses ranged between one and 32 in this benchmark. In MBs we’re talking 100MB-3GB. That’s usually a small database, and I would be interested in seeing how the benchmark ranks with 1000 warehouses (around 100 GB) or even more.
6. The entire range of RDS instance types was examined, from small to quadruple Extra Large (4XL). Some very interesting results appeared, which will be the focus of this blog post.

Results Analysis

The benchmark results are surprising.

1. With hardly any dependency on the database size, MySQL reaches its optimal throughput at around 64 concurrent users. Anything above that causes throughput degradation.
2. Throughput is improving as machines get stronger. However, there is a sweet-spot, a point where adding hardware doesn’t help performance. The sweet spot is around the XL machine, which reaches a throughput of around 7000 tpm. 2XL and 4XL machines don’t improve this limit.

It would seem that the Scale-Up scalability approach is rather limited, and bounded. And unfortunately, it has some rather close bounds.

So, what’s the bottleneck?

• CPU is an unlikely candidate, as CPU power doubles on 2XL and quadruples on 4XL machines.
• I/O is also an unlikely candidate, since machine memory (RAM) doubles on 2XL and quadruples on 4XL machines. More RAM means more database cache buffers, thus reduced I/O.
• Concurrency? Number of concurrent users? Well, we saw that optimal throughput is achieved with around 64 concurrent sessions on the database. See figure 4b in the benchmark report. With one user, the throughput is 1,000 transactions per user, but with 256 users, it drops to one transaction per user!

Results Explanation

It is definitely weird that when more parallel users are added, we get performance degradation – a lower throughput than the one we have with fewer parallel users.

Well, the bottleneck is the database server itself. A database is a complex, sophisticated machine, with tons of tasks to perform in each and every millisecond it runs. For each query, the database needs to parse, optimize, find an execution plan, execute it, and manage transaction logs, transaction isolation and row level locks.

Consider the following example. A simple update command needs an execution plan to get the qualifying rows to update and then, reading those rows, it must lock each and every row. If the command updates 10k rows – this can take a while. Then, each update is executed on the tables, on each of the relevant indexes of that table, and also written to the transaction log file. And that’s for just a simple update.

Or another example – a complex query that takes a long time to run. Even one second is a lot of time in a highly loaded database. During its run, rows from source table(s) of that query are updated (or added, or deleted). The database must maintain “Isolation level” (see our isolation level blog), meaning that the user’s result must be a snapshot of the query as it was when the query started. New values in a row that was updated after the query began, even if committed, should not be part of the result. Rather, the database should go and find the “old snapshot” of the row, meaning the way the row looked at the beginning of the query. InnoDB stores information about old versions of rows in its tablespace. This information is stored in a data structure called a rollback segment. And if we go back to the previous example – here’s another task the database has to do – update the rollback segment.

We must remember that every command issued to the database actually generates dozens of recursive commands inside the database engine. With increasing sessions or user concurrency, the load inside the database engine increases exponentially. The database is a great machine, but it has its limits.

So What Do We Do?

So, we understand the database is crowded, and has hardware sweet-spots, which complicates the Scale Up solution – which is expensive, and doesn’t give the required performance edge. Even specialized databases have their limits and while the sweet-spot changes every database has one.

There are a lot of possible solutions to this problem – adding a caching layer is a must, to decrease the number of database hits, and any other action that can reduce the number of hits on the database (like NoSQL solutions) is welcome.

But the actual database solution is scale-out. Instead of one database engine, we’ll have several, say ten. Instead of 128 concurrent users or even 256 concurrent users (that, according to the TPC-C benchmark, bring the worst results), we’ll have ten databases with 26 users on each, and each database can reach 64 users (up to 640 concurrent users). And of course, if needed, we can add more databases to handle the increased scale.

It will be interesting to see how Scale Out solutions for databases handle TPC-C. For disclosure, we at ScaleBase are currently running a TPC-C benchmark and will publish our results shortly.

WHAT’S NEXT?

• Download the ScaleBase Database Load Balancer for a free trial.
• Read our white paper, and learn about the ScaleBase Database Load Balancer solution.
• Check out our other posts for more technical info.

Everybody knows that using prepared statements for your database access greatly improves latency times. My point, in this blog post, is that it can also improve your database scalability. How? Just read on.

What does a database do with a statement?

Well, each statement takes a toll on the database resources, as there are many tasks to be done with each SQL statement. For example, the database needs to maintain isolation level (read more about the pain that surrounds this issue here). It needs to flush buffers to disk when a commit occurs. It needs to maintain locking. And it also needs to do a very complex task – parse and optimize the SQL statement.

This is a necessity. The database has to parse the SQL command, and string parsing is a CPU intensive action, especially for a complex language such as SQL.

Optimizing – well, optimizing is even more complex than parsing. Since optimizing involves building the best execution plan for the query, and can greatly improve the performance of a query, it has to be executed.

So, since we must parse, and we must optimize – why mention it? There is no way around it. However, let’s just be clear. Since both actions are CPU intensive they have great impact on the performance of the database and the number of concurrent actions it can perform – hence their impact on database scalability.

What is the database scalability barrier?

A good proof for this point is the wonder post by Yoshinori Matsunobu. You can read it here. It explains just how much time the database spends on SQL parsing, and shows what happens when you “cut out” the SQL parsing bit of the database and use the InnoDB engine API directly.

In the blog post Mr. Matsunobu was able to reach a state where his MySQL supported more hits than Memcache.

So I think it is safe to say that SQL parsing and statement optimization have a great effect on database scalability.

What does a database do with a prepared statement?

So what can we do? Must we remove our beloved ORM code and replace it with direct InnoDB calls? Well, for some applications that’s definitely true; but not for all, and for most applications it’s just not feasible.

Welcome to the world of prepared statements. With prepared statements, the database first gets the statement, parses and optimizes it, and from that moment on just gets parameters and executes an already parsed and optimized statement – resulting in great performance improvements for the client and a much more “relaxed” action for the database.

Note that the prepared statement is handled on a connection level – one more great reason to use connection-pooling mechanisms.

How to do it

If you’re not familiar with using prepared statements, I gathered some links that explain how to write them in your programming language.

PHP

http://php.net/manual/en/pdo.prepared-statements.php

Java

http://download.oracle.com/javase/tutorial/jdbc/basics/prepared.html

Note that for legacy reasons the MySQL JDBC driver only mimics prepared statements on the client side, and does not translate those to Server Side Prepared Statements – so the performance benefits are lost. To enforce Server Side Prepared Statements use the useServerPrepStmts parameter. Check here for more info.

Ruby

I’m not a Ruby expert, but it looks like ActiveRecord doesn’t support prepared statements, so that’s a big problem with Rails.

Ruby itself however, does support prepared statements, http://blog.aizatto.com/2007/05/19/connecting-to-mysql-using-ruby/

C#

For MySQL – http://dev.mysql.com/doc/refman/5.0/en/connector-net-programming-prepared.html

Summary

Prepared statements offer many attractions – in performance, security and more. I strongly urge you to use prepared statements whenever you can – you will feel the benefits immediately.

Want to read what the 451 group had to say about ScaleBase? Read the report here - ScaleBase – 451 Group Impact Report – March 2011

I’ll be delivering a lecture on Big Data in Cloud environments at the Las Vegas Interop 2011 (see here for more info). We at ScaleBase believe that the Big Data problem is coming to the OLTP world (actually, we believe it’s already here; at least that’s what our customers tell us), and that our solution can really help out with Big Data in OLTP. Here’s how.

The first thing is to define what Big Data is. Essentially, it’s based on three parameters: the volume of your data, the velocity of the data, and the complexity of the analysis. All parameters are important, also in OLTP, because data is being accumulated  in the OLTP database (since no one dares move it), velocity increases as the number of users increase, and analysis these days also occurs in OLTP databases.

So, how can ScaleBase help if you have Big Data on your OLTP database? Let’s try to divide this answer over the parameters of Big Data:

1. Volume of Data. The bigger your database is, the slower it is. Any DML (Data Manipulation Language – commands like Insert, Update and Delete) operation takes longer, since bigger indexes need to be updated. (As a matter of fact, IDG claims that if your database is bigger than 1TB you have a BigData problem).
   We, in ScaleBase, make each database smaller – thus making each specific database work much faster.
2. Velocity of Data. The number of operations a database gets has huge effects on its performance, not just on the level of supporting many ops/second, but because of the ACID nature of the database. Check out our post on database isolation to get more info on the effect of ops/second on database performance.
   Since ScaleBase splits the load, each database will get less ops/second and will perform much faster.
3. Complexity of analysis. This relates to the complexity of the queries that the database needs to execute. The same case of database isolation holds here too, but also, and more importantly, this is where the Map-Reduce nature of ScaleBase comes into effect. Complex queries will run in a parallel manner, on multiple databases, where each database is smaller in size and gets less ops/second. In our tests, this accounts for major performance improvements.

So, if you feel your OLTP database suffers from Big Data problems – drop us a mail or register for our beta.

The AWS East Region outage showed all of us the importance of running our apps and databases across multiple Amazon regions (or multiple cloud providers). In this post, I’ll try to explain how to build a MySQL (or Amazon RDS) redundant site.

For simplicity, we create a passive redundant site. This means that the site is not used during normal operation and only comes into action when the primary site crashes. There are many reasons for choosing such an architecture – it’s easy to configure, simple to understand, and minimizes the risk of data collision. The downside is that you have hardware just sitting around doing nothing.

Still, it’s a common enough scenario. So what do we need to do to make it work?

Data Synchronization

We need to synchronize the database. This is done by means of database replication. Now, there are two options for database replication: synchronous and a-synchronous. Synchronous replication is great; it ensures that the backup database is identical to the primary database. However, it also means that each DML operation on the primary database must be executed on the backup, and the result is sent to the client only after the backup database has completed the operation (at least at commit level). This is very slow when the backup database is located in a remote geographical location (as a good friend of mine once said – sometimes the speed of light is just too slow).

A-synchronous replication is better for remote sites, but can result in data loss, depending on the “lag” between the primary database and the backup database.

We MySQL customers (RDS customers included) only have a- synchronous replication (and, starting from 5.5, semi- synchronous replication, which on most deployments will behave in the same way as a-synchronous replication).

After the replication is configured, the application needs to be updated to make sure that it recognizes database crashes, and will failover to the backup database.

Detecting Crashes

There are several possibilities for crashes and their detection.

The first, and simplest, is if the entire region is down, which causes both the application servers and the databases to crash. In this case, the CDN, or a DNS load balancing technique, will failover to the backup region. This requires sys-ops work but is transparent to the application.

The problems start when the database tier of the region crashes, let’s say from storage problems. That might create a scenario where the application gets the user request, but since the database is down it fails to respond. In that scenario, special code needs to be developed, at the application level, to detect database crashes and initiate failover to remote databases. This code must appear in every access point to the database.

A quick side note – Amazon RDS actually does just that, but across different availability zones, which, as the recent AWS crash proved, is good but not good enough.

The ScaleBase Advantage

So what can we offer? We’ll save you the time writing the code that detects database crashes and initiates failover to the backup database. Our solution will work with any programming language, any O/R mapping tool, and will even let you handle backup databases in the same region and in multiple regions – just to be really safe.

If this interests you – just register for our beta here.

Matt Aslett from the 451 group created a term called “NewSQL”. On the definition of NewSQL, Aslett writes:

“NewSQL” is our shorthand for the various new scalable/high performance SQL database vendors. We have previously referred to these products as ‘ScalableSQL’ to differentiate them from the incumbent relational database products. Since this implies horizontal scalability, which is not necessarily a feature of all the products, we adopted the term ‘NewSQL’ in the new report.

And to clarify, like NoSQL, NewSQL is not to be taken too literally: the new thing about the NewSQL vendors is the vendor, not the SQL.

As with NoSQL, under the NewSQL umbrella you can see various providers, with various solutions.

I think these can be divided into several sub-types.

1. New MySQL storage engines. These give MySQL users the same programming interface, but scale very well. You can find our good friends from Xeround, Akiban, or Schooner in this field. The good part is that you still use MySQL, but on the downside it’s not supporting other databases (at least not easily) and even MySQL users need to migrate their data to these new databases.
2. New databases. These completely new solutions can support your scalability requirements. Of course, some (hopefully minor) changes to the code will be required, and data migration is still needed.
3. Transparent Sharding. ScaleBase, which offers such a solution, lets you get the scalability you need from the database, but instead of rewriting the database, you can use your existing one. This allows you to reuse your existing skill set and eco-system, and you don’t need to rewrite your code or perform any data migration – everything is simple and quick.

As in NoSQL, I believe each NewSQL solution has its own spot, answering specific needs. I believe that if you have existing applications and want to let them scale – you should definitely give ScaleBase a try.