One of the main responsibilities of any database administrator is to keep a close eye on how database performance is impacting size and storage. Decisions will have to be made on whether or not to make changes within the database structure or application itself, or to make the changes on the storage and resource side instead.
Here are two key indicators that your MySQL Database is getting too large: Read More
A default installation of MySQL is easy to perform, but if you really want your databases to sing, you should tune them like you would tune a piano. In MySQL tuning pertains to either the application or the database system. In this post, we cover some common tuning techniques and best practices to increase your MySQL application performance. An upcoming post will discuss tuning the DB system via your my.cnf configuration file.
Optimize – If you are working with a MySQL database that has a table or tables that are constantly changing, disk space will be wasted and in need of optimization. This is where the OPTIMIZE Table command comes in handy. You should create automation around this command or run it periodically. In order to run this command on a table, perform the following query:
“OPTIMIZE TABLE <Database>.<Table>.”
Got a cool $4 Million sitting around? That’s what a 30-second Super Bowl commercial costs this year, and advertisers are banking on huge ROI — major website traffic increases and big social engagement.
But are they ready?
Spikes in traffic (perhaps the biggest companies will see all year) require massive transactional processing power. Without it, advertisers inch closer to disaster with every user click — and while there’s never a good time for an outage, when there’s a $4m investment on the line, the pressure is on.
Here are a few strategies to help advertisers and social channels to avoid major fumbles:
Just like the Ravens and the 49ers, advertisers and social media networks have one shot to prove themselves this Sunday. With the proper infrastructure in place, these organizations can score big — but an unexpected outage send them to the locker room early.
News flash: The first ever combined database scalability and replication solution just hit the market.
This isn’t brochure ware. Our newly formed partnership with Codership means our customers have instant access to a single solution that solves two very real and urgent database concerns – all in the interest of achieving ultimate database performance.
In a nutshell, here are the highlights:
But wait! There’s more! Check out the official announcement here.
Next gen app providers (and perhaps more specifically, database architects) are clamoring for database technologies that just work. At least, that’s the message we got from one of our newest customers: Mozilla.
Earlier this month, we caught up with Sheeri Cabral, database architect at Mozilla and and overall MySQL rock star, to get the down-and-dirty on why she and her team chose ScaleBase to back the company’s new App Store. Here’s some of what she had to say…
“With ScaleBase backing Mozilla Marketplace, we don’t have to worry about database capacity or availability. The day the marketplace launches, Mozilla’s infrastructure is set to seamlessly scale, ensuring the highest service levels.”
“I’m excited to put ScaleBase in front of many of our applications because high availability is critical to Mozilla’s success.”
Check out the full video here.
Just like every closer needs a setup guy, every next-gen application provider needs a partner that can facilitate performance
We see thousands of new online and mobile apps launching every day. Cool stuff – but what happens when demand climbs and databases can’t keep up?
Facebook Blames Outage On Database Failure
RBS Bank joins the IT failures ‘Hall of Shame’
In other words, headlines we’d all like to avoid.
The long-term success (and reputation) of any online app or store hangs on a company’s ability to provide uninterrupted access and availability. Translation: Database performance and scalability.
That’s why smart companies, like Mozilla, are thinking about database performance first.
Mozilla’s using ScaleBase’s Data Traffic Manager to maximize availability and scalability of databases worldwide in advance of launching its much-anticipated online app store: Mozilla Marketplace. The new marketplace will give app developers a cost-effective, high-performance platform for creating and delivering applications, and consumers a one-stop-shop for downloading mobile apps. Once the store launches, ScaleBase’s unique ability to distribute data to multiple datacenters across distributed geographies – all while centralizing the management of it – will be used to move relevant data to datacenters close to each customer’s location. A database scalability strategy only the most innovative companies are adopting.
Additionally, with ScaleBase difference, you’ll never know when there’s an outage because the data is distributed across multiple locations.
Every next-gen app and app-store provider should have the same goal: Deliver the best possible service to customers – with around-the-clock access. And for our friends at Mozilla, perhaps a news story that looks something like this: “Always-Available Online App Store First Choice for Buyers and Developers.”
If you polled small to midsize tech teams on their biggest database headaches, cost would be at the top of the list — without question. If you haven’t already run into this, the number one reason (and complaint we hear all the time) is hardware.
Hardware is pricey. And time consuming to implement, plus there’s a learning curve — all of which create problems for most of the companies we come across where the primary concern is instant database scalability to meet user demands.
Echoing this, Boris Livshitz, Sever Data Lead at AppDynamics, a ScaleBase customer, explained what drove the company to seek out a new database scalability solution to SearchDataManagement.com:
From the [AppDynamics] founder all the way down, everyone had been asking for an architecture that can scale, and our customers had been asking for an architecture that would scale because they would see performance problems. They want to instantly scale and they want to make sure that we can instantly scale along with them.
And what was the hold up with most of the solutions they tested?
It’s the hardware that really makes it expensive, and you can’t really negotiate that stuff.
So where did AppDynamics turn?
AppDynamics first began looking into database sharding technologies about a year and a half ago, when it became evident that the company needed to scale its MySQL implementation with the needs of its customers — and that continuing to throw hardware at the problem would eventually become prohibitively expensive.
For some companies, throwing new, expensive hardware at the database to increase performance is fine — but for others, like AppDynamics, and web 2.0 entrepreneurs looking to get their venture off the ground, it just isn’t a viable or strategic option.
We’re watching the database industry evolve faster than ever and there are sky high expectations from users for immediate access to applications.
Here’s what we offer: No hardware installation. No changes to existing infrastructure. Instant scalability and the ability to adapt to dynamic business environments.
The good and the bad news: The database landscape is evolving quicker than ever thanks to the ubiquity of cloud solutions and increasing expectations for real-time access to applications and information from anywhere.
The really good news: You don’t have to rework your entire database infrastructure to take advantage of the momentum.
By definition, tools exist and are designed to fix, solve and eliminate problems – regardless of the industry. But, when it comes to scaling a database, companies face many of the same questions/concerns. Do I need to change my infrastructure? How steep is the learning curve? What kind of application adjustments will I need to make? And most importantly what is this going to cost?
Neovise, an independent research and analysis firm, tackled these issues head on in its recent whitepaper, “Database performance, scale, and management issues can have a substantial effect on overall business performance and market position.” Here are a couple key takeaways:
For more details on the database scalability landscape, please check out the complete Neovise whitepaper: http://www.scalebase.com/resources/white-papers/
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 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.
First of all, I have some comments on the benchmark method itself.
The benchmark results are surprising.
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?
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, 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.
Hi Milo - I totally agree. You need to understand how you're...September 6, 10:33 am by liran
