5 Things Toxic to DB Scalability Over the Cloud
If you’re using MySQL checkout 5 ways to boost MySQL scalability in the cloud-
- Object Relational Mappers
- Synchronous, Serial, Coupled or Locking Processes
- Having No Metrics
- One Copy of Your Database
- Lack of Feature Flags
1. Object Relational Mappers (ORM)
On the performance side the picture is a bit different. By leaving SQL query writing to an ORM, you are faced with complex queries that the database cannot optimize well. What’s more ORMs don’t allow easy tweaking of queries, slowing down the tuning process further.
What’s ORM ?
Object-relational mapping in computer software is a programming technique for converting data between incompatible type systems in object-oriented programming languages. This creates, in effect, a “virtual object database” that can be used from within the programming language. by Wikipedia
2. Synchronous, Serial, Coupled or Locking Processes
Locking in a web application operates something like traffic lights in the real world. Replacing a traffic light with a traffic circle often speeds up traffic dramatically. That’s because when you’re out somewhere in the country where there’s very little traffic, no one is waiting idly at a traffic light for no reason. What’s more even when there’s a lot of traffic, a traffic circle keeps things flowing. If you need locking, better to use InnoDB tables as they offer granular row level locking than table level locking like MyISAM tables.
Avoid things like semi-synchronous replication that will wait for a message from another node before allowing the code to continue. Such waits can add up in a highly transactional web application with many thousands of concurrent sessions.
Avoid any type of two-phase commit mechanism that we see in clustered databases quite often. Multi-phase commit provides a serialization point so that multiple nodes can agree on what data looks like, but they are toxic to scalability. Better to use technologies that employ an eventually consistent algorithm.
3. One Copy of Your Database
Without replication, you rely on only one copy of your database. In this configuration, you limit all of your webservers to using a single backend datastore, which becomes a funnel or bottleneck. It’s like a highway that is under construction, forcing all the cars to squeeze into one lane. It’s sure to slow things down. Better to build parallel roads to start with, and allow the application aka the drivers to choose alternate routes as their schedule and itinerary dictate.
Using MySQL? Checkout how to Easy Replication Setup with Hotbackups.
4. Having No Metrics
Having no metrics in place is toxic to scalability because you can’t visualize what is happening on your systems. Without this visual cue, it is hard to get business units, developers and operations teams all on the same bandwagon about scalability issues. If teams are having trouble groking this, realize that these tools simple provide analytics for infrastructure.
There are tons of solutions too, that use SNMP and are non-invasive. Consider Cacti, Munin, OpenNMS, Ganglia and Zabbix to name a few. Metrics collections can involve business metrics like user registrations, accounts or widgets sold. And of course they should also include low level system cpu, memory, disk & network usage as well as database level activity like buffer pool, transaction log, locking sorting, temp table and queries per second activity.
[The starting point to discover what and how much you use Newvem presents you with a comprehensive picture of the resources in your cloud. Get Started with Newvem for Free!]
5. Lack of Feature Flags
Applications built without feature flags make it much more difficult to degrade gracefully. If your site gets bombarded by a spike in web traffic and you aren’t magically able to scale and expand capacity, having inbuilt feature flags gives the operations team a way to dial down the load on the servers without the site going down. This can buy you time while you scale your webservers and/or database tier or even retrofit your application to allow multiple read and write databases.
Without these switches in place, you limit scalability and availability.
This post originally posted on iHeavy
[Newvem analytics tracks your AWS cloud utilization:
- Hourly Utilization Pattern Analysis
- Reserved Instances Decision Tool
- Resource Resizing Opportunities
About the Author
Sean Hull, Founder and Senior Consultant at Heavyweight Internet Group. Author, speaker, and small business owner with sixteen years independent consulting experience. MySQL Database Administrator with EC2 Deployment scalability, disaster recovery, business continuity and troubleshooting specialties, in a wide variety of businesses.
Keywords: Cloud computing, Object Relational Mappers, ORM, Cloud Scalability, MySQL, ISAM, InnoDB, Performance, Database in The cloud, Monitoring, DB Backup, Data replication