PlanetMysql.ru — информация о СУБД MySQL

Recently some customers running on Amazon EC2 asked me how to configure a HA environment for their ScaleBase instance.
For instance, let’s look at the following architecture:

To ensure that Scalebase is not a single point of failure, several ScaleBase instances can be used – so if one crashes, other instances can handle its connections.

This is quite simple to do:

1. Start the EC2 instance to be used for the ScaleBase configuration
   1. Install MySQL on the machine and follow the database preparation instructions defined in the ScaleBase installation guide.
   2. Install ScaleBase with the –mode=ALL parameter.
   3. Create an EC2 instance that will be used as an AMI for all ScaleBase installations.
      1. Download and install ScaleBase on the machine using the InstallMachine script. Make sure you use the –mode=LB parameter, to connect to an existing configuration.
      2. Create an AMI out of the instance (in the EC2 management console, right click on the instance and select the “Create Image” option).
      3. Start instances based on the created AMI.
      4. Create an EC2 Load Balancer (see EC2 documentationfor more information).
         1. Make sure all the instances you created are mapped to the Load Balancer.
         2. Make sure the Load Balancer is connected to TCP port 9000, and is using this port for Health Check.
         3. Direct all communication to the Load Balancer DNS.

That’s it. Easy – and works like a charm.

There is a huge amount of buzz around NOSQL, and we at ScaleBase are happy to see companies making the move to NOSQL. Despite what some people might think, we consider it a blessed change. It is time for applications to stop having a single data store – namely a relational database (probably Oracle) – and start using the best tool for the job.

In the last couple of years, since NOSQL technologies broke into our world, a lot of experience has been gathered on how to use them. Mainly, we see NoSQL technologies used for one of the following scenarios:

• Queries that require a very short response time
• Storing data without a well-defined schema, or storing data with a frequently modified schema

Now, I’m not in any way saying that NOSQL solutions are not used for other scenarios as well; I’m only saying that from our experience here at ScaleBase ,  these are the most common scenarios.

Other needs, like data backup, complex joins queries, consistent data storage – all are still being delivered by relational databases.

So the implementation is along the lines of a hybrid model – NOSQL for some tasks, MySQL (or other database, but MySQL is by far the most popular) for others.

ScaleBase is determined to assist in the relational database part of the problem, letting it scale and perform – just as the NOSQL side can scale and perform by itself (and frankly it can scale and perform very well, as this was the original requirement for most NOSQL solutions).

As NOSQL solutions grow in popularity and use, I expect we’ll see “design patterns” pop up – when to use relational databases and when to use NOSQL solutions (and of course – which one). For now, if you’re architecting your new web application/SaaS solution or social game – try to learn from the architectures of existing sites. You can get some at http://highscalability.com , and others at http://nosql.mypopescu.com/.

ScaleBase Releases Database TPC-C Performance Results

Technology achieves unprecedented transaction speed for a MySQL database at a low cost

Boston, Mass., December 12, 2011 – ScaleBase, Inc. today announced the results of its MySQL database benchmark, based on the industry-standard TPC-C test. ScaleBase has achieved an unmatched 180,000 Transactions per Minute – the highest result for a MySQL database – while running on an Amazon RDS environment. Cost per Transaction was reported to be 50 cents, which demonstrates the cost-effectiveness of the ScaleBase solution on the Amazon EC2 cloud. Full details of the benchmark can be found at http://www.scalebase.com/resources/performance/.

TPC, the Transaction Processing Performance Council, defines transaction processing and database benchmarks and delivers reliable, independent results to the industry. The TPC-C benchmark is a popular yardstick for comparing Online Transaction Processing (OLTP) performance on various hardware and software configurations.

The ScaleBase Database Load Balancer is a packaged solution for transparently scaling MySQL databases. ScaleBase utilizes two techniques for scaling: read-write splitting and transparent sharding (a technique for massively scaling-out relational databases). The software enables MySQL to scale transparently, without forcing developers to change a single line of code or perform a long data migration process. The technology is ideally suited for any application in which scalability, performance and speed are critical, including: gaming, e-commerce, SaaS, machine-generated data, Web 2.0 and more.

“Some people feel that by using MySQL they stand the chance of limiting their performance options, however, these TPC-C results proves that  this simply is no longer the case,” said Rob Levine, ScaleBase’s VP of Sales. “Without writing specialized code you can still get top performance – perhaps optimal performance – at an affordable rate, accounting for the requisite hardware and infrastructure resources. Especially in today’s economy, getting such great performance and optimizing every dollar spent can save companies substantial amounts of money.”

ScaleBase’s Database Load Balancer solution has been successfully used by numerous customers since its official release in August 2011.

About ScaleBase

ScaleBase has developed an innovative database load balancing technology that enables MySQL users to achieve scalability and high availability, without changing a single line of application code. ScaleBase utilizes two techniques for scaling: read-write splitting and transparent sharding, which is a method for massively scaling-out relational databases. The ScaleBase technology is ideally suited for any application in which scalability, performance and speed are critical, including: gaming, e-commerce, SaaS, machine-generated data and more. The company is privately-held and headquartered near Boston, Mass. Follow @SCLBase on Twitter.

Media Contact

Candice Perodeau

508-475-0025 x112

cperodeau@rainierco.com

There are several ways to implement sharding in your application. The first and by far the most popular, is to implement it inside your application. It can be implemented as part of your own Data Access Layer, database driver, or an ORM extension. However, there are many limitations with such implementation, which drove us, at ScaleBase, to look for an alternative architecture.

As the above diagram shows, ScaleBase is implemented as a standalone proxy. There are several benefits to using such an architecture.

First and foremost, since the sharding logic is not embedded inside the application, third party applications can be used, be it MySQL Workbench, MySQL command line interface or any other third party product. This translates to a huge saving in the day-to-day costs of both developers and system administrators.

Backup can be executed via the proxy, and so allows users to consistently backup a sharded environment – not an easy task when sharding is developed internally.

Since the application server machines are usually highly utilized (as they should be, to optimize costs), running additional code on application server machines will just slow them down. Running the code on external proxies allows for a more efficient division of tasks between the servers, and allows requests to be unaffected by data crunching (for instance cross-shard queries) requests.

So all in all there are many reasons to run sharding code outside the scope of the application and application server. If you’re interested – we’d love to chat.

The ScaleBase Analysis tool gives a schema a grade between 1 and 100 for being “sharding compatible”. It’s a neat feature, but many ask me how the grade is calculated. Well – here goes.

First of all, a good schema is one that is easy to shard. Database Normalization is usually a good thing when sharding. It means that finding the sharding key is easy, relationships between tables are clear, and the queries themselves are usually much simpler. So we try to give a grade on how well the schema is normalized.

After the sharding configuration is determined (see here on how this should be done), we review your MySQL General Log, to understand the value you can expect from the sharding configuration:

• Statements that run on sharded tables, and hit a single shard.
  • Since each database is smaller in size, performance of a single shard improves. As a result, statements of this kind will give major performance improvements.
  • Bottom line – major performance improvement over the original configuration.
  • Statements that run on sharded tables, and hit multiple shards.
    • These statements run on multiple shards, in a map-reduce manner. It means they will run faster than running on a single, big, database – but they will run slower than statements that need to hit just one shard.
    • Bottom line – better performance than the original configuration.
    • Statements that run on global tables, and hit a single shard.
      • These are queries on global tables. Basically, the performance improvement should be similar to what’s seen on statements that run on a single shard (depending on the global table size of course).
      • Bottom line – major performance improvement over the original configuration.
      • Statements that run on global tables, and hit multiple shards.
        • DML operations over global tables need to hit all the shards. These will run slower than the original configuration, so if you see you have a lot of these, try to turn the specific global tables into split tables.

Sounds simple, right? Well, try to take all these parameters into consideration, and build a sharding configuration on your own. If you don’t want to – try out our ScaleBase Analyzer – for a free tool that can build the sharding configuration for you.

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 twice.
But if the database is big, it won’t fit into memory. Sometime even the index won’t fit into memory. This is when you start seeing database performance degradation. So the best date to start sharding is when you can’t add more memory to your database server. This can come sooner rather than later. As we all know, data is booming, and if you’re running in the cloud there is only so much memory your cloud provider will give you. With sharding, every machine has its own data, which fits in RAM. And if you need more – just add an additional shard.
The other parameter is the number of concurrent connections. If you reach the limit of connections your machine can handle, it’s time to shard your database. Every sharded database gets less hits/second, requires fewer connections – and can work faster.
So, if your database does not fit in memory, or if you have too many concurrent users hitting your database – try out ScaleBase, for our transparent sharding solution.

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 applications. The number of users is relatively small, the read ratio is extremely high (>95%), and scale is contained. For most enterprise internal systems the scale is known years in advance.

The “short transaction” model (a term coined by Curt Monash) is radically different. Be it web facing applications, mobile apps, or machine generated content (another Curt Monash term); be it enterprise or startup; it’s impossible to know what the scale will be. The write ratio is very high, since most reads go to a distributed caching layer, to improve query latency – and everybody wants the number of users to increase in a logarithmic scale.

The Oracle scaling solutions just don’t cut it. But in MySQL the situation is even worse.  Developers who are used to “IT handles scaling” suddenly run into scaling limits much faster, then IT throws the hot potato back at them and asks them to write their app so it’s easy to scale!

That’s something you should prepare yourself for when you move to MySQL. IT scaling solutions, although limited in the Oracle world, are non-existent in the MySQL world. Every solution requires code changes, and developers are becoming more and more DevOps.

Or shall I say all but ScaleBase! The ScaleBase solution supports write scaling, a perfect fit for your short-transaction application. And it’s transparent, so it can be implemented by IT rather than by DevOps.

Summary

I started this series by trying to explain why the Oracle database shouldn’t be used for web applications. I continued by explaining what kind of challenges you will face once you decide to migrate. I ended the series by looking at the scalability angle, trying to show how even here Oracle and MySQL differ – and how that effects the IT and R&D processes.

If you plan to migrate from Oracle to MySQL – drop a comment here. I’d love to assist.

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 changes (For example. When working with paging and wanting to limit number of records returned in a resultset, MySQL’s LIMIT clause will have to replace Oracle’s use of ROWNUM pseudo-variable. OR ANOTHER EXAMPLE: Oracle’s “old” outer join syntax includes (+) near tables in the FROM clause. However since Oracle9i the ANSI join syntax is supported and highly recommended by Oracle themselves!),), but some (like queries using CONNECT BY or many of the aggregate functions Oracle database offers) will require massive query and code changes.
   Even if you use an ORM tool, code changes might be required, although they will probably be much smaller.
2. Stored procedures.
   While stored procedures and triggers are supported by MySQL, they are rarely used. On the other hand, stored procedures are extremely popular in the Oracle database environment – and are very powerful.
   Every stored procedure will have to be migrated, probably manually, to run inside the application. This might result in a major performance hit, since stored procedures run in the database, “close” to the data, and many developers use them for data-crunching tasks.
   All in all, the stored procedures will probably have to be re-designed, not just re-written.

Developers’ “state of mind”

While code can be migrated, and a cost can be attached to such migration, Oracle developers often require some time before they can effectively develop code for MySQL. The limitations mentioned in the above section, and the different development tools, are difficult to get used to.

But even more – it is difficult to get used to what the database does and doesn’t offer. We’ll dive into that topic later, when we talk about scalability and high availability.

DBAs

Oracle and MySQL DBA are similar in title only. Their day-to-day job is very different.

1. Different tools.
   There is no MySQL CLI for Oracle, and SQL-PLUS can’t connect to a MySQL database.
   But those are the minor changes – SQL-Developer and MySQL Workbench are not that different.
   However, backup tools, performance monitoring utilities, and database audits are all very different, and it takes time to get used to those new tools. You should expect that at least some of the tools you currently have in your environment will not support MySQL, and you’ll have to find new tools. For some tasks, those tools are nowhere to be found, and will require in-house development.
2. Scripting.
   DBAs are measured by the quality of their scripts, and most Oracle database environments are heavily scripted. Database start, stop, error fixing, and data collection all use database engine utilities, and all those scripts will have to be rewritten.
3. Tuning parameters.
   A friend once told me about how they migrated their application from Oracle to SQL-Server. It took them three months to migrate the application and six months to tune the database. And this story is not uncommon; tuning parameters vary greatly between databases since every database has a different architecture.
4. Central management and administration. Adding an index, for example. When I have 10 DBs, I have to create on every DB, make sure it succeeded, etc…

High Availability and Scalability

Oracle users know that when they require high availability or high scalability they can turn to RAC. MySQL doesn’t offer such an option; MySQL Cluster is very different from MySQL standalone. We’ll discuss the high availability and scalability topic in our next post.

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 just pricing. While my DBA friends might kill me for this, MySQL is actually better than Oracle when it comes to modern environments. The main reason is that Oracle RAC sucks when it comes to scaling writes, while MySQL has tons of scaling alternatives out there (ScaleBase, of course, is the best one, but you’ll be surprised to see that other solutions exist). And writes are what modern applications are all about. Your application stores everything, since everything might be useful. Reads come from the cache anyway.

But Oracle RAC sucks when it comes to write scalability. All the nodes in the Oracle RAC cluster chatter with each other about locking rows and physical storage blocks – so the more writes, the more chatter, the more network data explosion – killing your scalability.

Yeah, I know the SalesForce story.  They have the largest Oracle installation in the known universe. But frankly, SalesForce is not your regular web application; it’s an enterprise application in a SaaS mode. Gaming, web, mail, social – any modern, consumer facing app, just can’t work with Oracle. And how many SaaS companies have the deep pockets that SalesForce has for paying Oracle? (After what Larry Ellison did to Marc Benioff, I wouldn’t be surprised if SalesForce just switched to MySQL or Postgress).

Oh, and Oracle RAC doesn’t even work on virtual environments, so no RAC in the public cloud – probably the most popular environment for web apps these days.

So if your database doesn’t scale, what good does it do for your web application? None.

You pay more – and get less. And almost nobody does that anymore.

In the following installments of this blog I’ll discuss how to migrate from Oracle to MySQL, on both code and experience levels.

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.