Scalability Happiness – A Quiet Query Log

Peter Van Allen - Pin Drop

Join 7500 others and follow Sean Hull on twitter @hullsean.

There’s a lot of talk on the web about scalability. Making web applications scale is not easy. The modern web architecture has so many moving parts. How can we grapple with the underlying problem?

Also: Why Are MySQL DBAs So Hard to Find?

The LAMP stack scales well

The truth that is half right. True there are a lot of moving parts, and a lot to setup. The internet stack made up of Linux, Apache, MySQL & PHP. LAMP as it’s called, was built to be resilient, dynamic, and scalable. It’s essentially why Amazon works. Why what they’re doing is possible. Windows & .NET for example don’t scale well. Strange to see Oracle mating with them, but I digress…

[quote]
Linux and LAMP that is built on top of it, are highly scalable and dynamic to begin with.
[/quote]

Also: AirBNB Didn’t Have to Fail During an AWS Outage

Ok, so what’s this got to do with MySQL? Well a LOT.

The webserver tier, the caching layers like memcache & varnish, as well as the search tier solr. These all scale fairly easily because their assets are fixed. Or almost so.

The database tier is different. So what affects performance of a database server? Server size? Main memory? Disk speed? The truth is all of those. But

Also check out: The Sexiest New Feature of AWS Speeds Up EBS

After you setup the server – set memory settings and so forth, it’s a fairly fixed object. True there are parameters to tweak but on the whole there isn’t a ton of day-to-day tuning to do.

Well if that’s true, why does performance take a hit?! As applications grow, the db server slows down, don’t we need to tweak server settings? Do we need new hardware?

Read this: A CTO Must Never Do This

The answer is possibly, but 9 times out of 10 what really needs to happen is queries must be tuned.

[quote]
In 17 years of consulting that is the single largest cause of scalability problems. Fix those queries and your problems are over.
[/quote]

The Elephant in the Room – Query Tuning

I was talking with a colleague today at AppNexus. He said, so should we do some of that work inside the application, instead of doing a huge UNION or a large JOIN? I said yes you can move work onto the application, but it makes the application more complex. On the flip side the webserver tier is easier to scale. So there are tradeoffs.

I said this:

[quote]
By and large, if scalability is our goal, we should work to quiet the activity in the slow query log. This is an active project for developers & DBAs. Keep it quiet and your server will run well.
[/quote]

Also: Top MySQL DBA Interview Questions for Candidates, Hiring Managers & Recruiters

Yet I still talk to teams where this is mysterious. It’s unclear. There’s no conviction there. And that’s where I think DBAs are failing. Because this is our subject matter expertise, and if we haven’t convinced developer teams of this, we’re not working together enough. API teams aren’t separate from DBA and operations. Siloing technology departments is a killer…

[mytweetlinks]

As you roll out new code, if some queries show up, then those need attention. Tweak the code until the queries drop out. This is the primary project of scalability.

When should I think about upgrading hardware?

If your code is stable, but you’re seeing a steady line rising on load average of the server, *THEN* go up in hardware. Load average means cpu & disk are being taxed. The server can’t keep up.

Related: Should I use RDS or build a MySQL server on AWS?

Devops means work together!

I close with a final point. Devops means bring dev & ops together! Don’t silo them off in different wings. Communicate. DBAs it’s your job to educate Developers about scalability and help with query tuning. Devs, profile new SQL code, test with large datasets & for god sakes don’t use an ORM – it’s one of 5 things toxic to scalability. Run explain and be sure to index all the right columns.

Together we can tackle this scalability thing!

Get some in your inbox: Exclusive monthly Scalable Startups. We share tips and special content. Here’s a sample

  • brad

    I disagree with windows + .net not scaling. stackoverflow is entirely written on .Net and runs of windows with sql server back end. And they don’t even do sharding to handle the load.

    http://www.brentozar.com/archive/2011/11/how-stackoverflow-scales-sql-server-video/

    • http://www.iheavy.com/blog/ Sean Hull

      Just a little dig, Brad. :-)

      Great blog btw, I like the presentation style with talking head overlay. What did you use to make that?

      • Jim

        I’d say it was more of a generalization.

  • tobi

    Saying that language X does not scale is always false because scaling is a property of the application, not the language/framework. All languages and frameworks scale perfectly if given parallelizable workloads (like HTTP requests).

    • http://www.iheavy.com/blog/ Sean Hull

      Thx for the comments Tobi. Are you referring to my dig at .NET? I would argue there that the issue is more the underlying operating system, not so much .NET or C#.

      My personal opinion is that Windows is a desktop operating system, with its advanced and weighty user interface. As such it doesn’t really make sense in a datacenter.

      • tobi

        As I said, there is no practical built-in limit to the level of concurrency in none of the involved components (TCP-Stack, HTTP listener, ASP.NET or the .NET runtime). For that reason the entire system scales perfectly with added resources (cores). Only the application code (locks, …) might be doing stupid things that prevent it from scaling with added resources.

        The same comments are true for Linux as well.

        Scaling also is an independent of performance. Scaling means getting more output from increased input.

        • http://www.iheavy.com/blog/ Sean Hull

          Thx again Tobi. You explain the theory of scalability very nicely.

          I personally don’t see a lot of .NET internet sites at huge scale. I have seen the LAMP stack handle hundred million monthly visitor websites. Hollywood Reporter being one that I’ve worked on recently.

          Would love it if you could share the ones you’ve built on .NET.

          • tobi

            This account is anonymous so I will not share the stuff I work on. I do work on large-scale .NET sites though (some of which have been messed up by the teams working on them at the application layer). In any case I tried to make a more fundamental argument that all software stacks I know of are fundamentally scalable. Even the single-threaded node.js scales through multiple worker processes. Anyway, I feel I cannot add more to this discussion. I don’t want to derail the thread further ;-)

          • http://www.iheavy.com/blog/ Sean Hull

            Gotcha. Well do feel free. I’m glad to host a lively discussion on scalability.

            Btw, do you have a blog that we can share here, Tobi?

          • Ryan

            Hotmail, office365, azure, and stackExchange aren’t “high scale”? What about Bank of America?

            Windows severs scale just fine, and have been on par or better than Linux since acout. 1997. The GUI gets rapidly swapped out on a server, and the NT kernel performs just as well as Linux in almost every respect. And it blows linux away with some critical server-side pieces like asynchronous disk IO, which is horribly broken on Linux. This is one reason why SQL server benchmarks do well in TPC.

          • http://www.iheavy.com/blog/ Sean Hull

            I thought hotmail was originally Sun Solaris, but I guess they were able to ultimately port everything to Windows servers? Great stuff.

            Office365 does sound like a great case study. Ryan, do you have any links on how they build things, and so forth? Also how do they manage the malware challenges? Perhaps they were less problematic when you don’t have GUI users to contend with on a server?

            Thx for the comment.

  • Pingback: Июльская лента: лучшее за месяц