Venu Anuganti Blog

Recently am part of Zynga‘s database team as I was pretty much impressed with company’s database usage. As everyone knows how popular Zynga games like Farmville, Cafe World, Mafia Wars, Poker, FrontierVille, FishVille, PetVille and Treasure Island etc are. Zynga launched yet another new game today called CityVille along with series of acquisitions (latest today is NewToy). You can find current Zynga game stats from appdata.

But lot of people asked me why I am part of Zynga database team when there is no MySQL being used by any of the games; and lot of articles on the web also indicate the same. For me it does not matter if it is MySQL or NoSQL or any other system as long as the data store can help to scale the systems and/or games in this case.

As a consultant, I help lot of other companies to scale using NoSQL systems apart from MySQL especially on large data handling; as the data store solution should help to scale the systems to yield the desired results; especially MySQL should be used for typical OLTP workloads and combination of MySQL and NoSQL or any other data warehouse clusters for analytics and/or OLAP workloads by combining with right application and caching components based on the business model and how the data is generated, stored, accessed and processed.

If you don’t use the right technology for what you trying to achieve, then you can’t easily scale, and end up spending time in fixing the performance and scalability issues on day to day basis rather than concentrating on building features that is demanded by the business.

As a matter of fact, Zynga may be the second largest MySQL user after Facebook. All games at Zynga are currently powered by MySQL as the backend storage along with memcache as the middle caching layer.

Last month we expanded the MySQL shards to one of the popular game due to increased DAU (Daily Average Users), and the whole expansion of MySQL shards in production happened without any down-time or taking the game down; which is only possible if the application code is tightly integrated with the caching, backend storage and also if the servers are in the cloud and elastic in nature (unless you have your own private cloud).

Even though Data warehouse is picking very rapidly in the last year or so, but few companies who are already made a right mark in the right time could not take the market share that easily due to number of reasons. Even though am not a marketing guy to go over, but some of the basic design flaws can make things backwards at any time. Was doing research in to this space for last few months and expecting Kickfire will be either acquired or will be lead to dead end, and looks like the later is true as per the latest reports (kickfire website is also down now) even though it is backed by big investors.

Apart from what has been written in the above article by Daniel Abadi, few other reasons why it might be a failure:

• it was not directly compatible with MySQL
• over priced, not reachable by small to medium market customers
• storage solution is tied with the appliance, as it can cause more damage when rapidly changing technology like storage is tied with an architecture.
• building OLAP appliance on top of OLTP architecture without scalable MPP is a big misfire (or atleast should have built MPP to compete with XtremeData and/or Netezza on the same space as they have much better solutions)

This is my personal view and nothing related to any of the current work that am doing on data warehouse front. In case if you need any advice on current data warehouse technologies and/or want to build a scalable data warehouse infrastructure, please drop me a note.

Other day we had a small discussion about data stores and hardware; and which one drives the other when it comes to data storage solution, rather it is a hard discussion as both on its own are bigger entities; and one can not easily conclude as it depends on use cases and actually speaking data store limitation(s) drives the need for more powerful hardware for demanding scalability needs.

We all know how important the hardware is in today’s data scalability, especially when dealing with large data sets. Without hardware, it is hard to scale even if you have a powerful data store either it could be SQL (row or columnar) or NoSQL (key/value or other means) or any other data storage solution; because they are limited by the data structures & its implementation and data store performance directly depends on the hardware lately.

At times, data store vendors claim that they have scalable, high performance architecture; that means the solution is directly built on top of hardware scalability and performance by taking advantage of today’s evolving hardware technology. Also, hardware evolution is too aggressive in the recent years when compared to data store solutions due to the market share as hardware is everywhere as it is not just the storage solution.

In short, when a data store performance is directly proportional to hardware performance; that means the data store actually surpassed all of its software performance bottlenecks (algorithms, decision making, data structures etc). Overcoming from software performance is not that easy as the requirement changes day by day and it depends on data size and how data is actually:

• stored
• retrieved
• processed and
• maintained

If data is stored and retrieved from memory or non-persistent storage solution; then one does not need to worry about rest of the stuff or performance as it yields the best throughput; but memory or non-persistent solution can be a solution for smaller data sets, but not for large data sets that deals with tera bytes of data.

Other than newly evolving columnar data stores (yet to see any one solution that is really pitching with universal acceptance like Oracle/SQLServer/MySQL), NoSQL or big data warehouse solutions (like Aster data, Green Plum etc), none of the existing solutions really take advantage of the latest hardware or even the data  structures as most of the data store kernels are written years back. In today’s world; the only option for scalability is by depending on the hardware and by distributing the load across multiple systems (either in shared-nothing or shared-common or even “cloud” way…).

Hoping to see a solution, one day that actually bridges the gap between data store, hardware and scalability without the need of using multiple technologies for common use cases instead of depending on one single solution that can be universally adopted. Brian Aker in his recent interview and Baron claims the same thought.

Very nice and interesting post from Michael Stonebraker explaining how errors dictate CAP Theorem (Consistency, Availability and Partition-tolerance); as only one objective from the CAP can be achieved during normal error conditions as NoSQL system seems to relax the consistency model as CAP theorem anyway proves that one can’t get all 3 at the same time, by favoring partition based availability

As most NoSQL systems adopt Eventual Consistency (depends and some systems it is configurable, and here is yet another nice article on variations in eventual consistency from Werner Vogels, CTO of Amazon) especially when data is distributed across the cluster of systems. Stonebraker suggests CA (Consistency and Availability, typical SQL system) rather than AP (Availability and Partition-Tolerance, typical NoSQL) by explaining the different error scenarios.

For example (just for fun); let’s consider the same error conditions, one by one and see how this can be adopted in distributed cloud computing (SQL or NoSQL)…

• Application errors;  and up on error, system needs to rollback to its previous consistent state…

In case of transactional system, it is easy to rollback if the unit of work is in transaction (automatically by DBMS or manually by the application). In key/value pair, as easy as calling delete (key). Few systems use append-only mode (where there is no concept of updates or deletes, few SAS companies in the valley also use MySQL/InnoDB/MyISAM in this append only mode where system never gets any deletes/updates as everything runs on INSERT and SELECT only), updating with older time-stamp or serial-number can revert the latest change (depending on how the latest value is read).

• Repeatable system crash on bad query request or bad data or something…

It is really hard to escape from this failure as other system in the cluster or replica can also fail for the same condition unless the replicated system is on a different version (rare case) or other degree of fault tolerance.

• Unrepeatable system crash (System failure, hardware/power issue, data center down, network issue…)

If it is a system failure; then this can be handled by fail-over to another system or replica in the LAN or WAN. NoSQL defines much better solution for fail-over with online substitution of nodes to the cluster. The only issue is, if data is persistent in master node and if it implements read-your own write or monotonic read consistency model (same value upon subsequent requests); and before the change propagates to replica of other nodes; then the consistency model fails unless the model is designed such that more than one node shares the same write.

• Partition failure in LAN, WAN

If bunch of partitions/nodes fail within LAN or WAN due to network, power failure etc; then it is hard to fail-over unless there is a replica or same copy within LAN or WAN. But due to network and/or power redundancy in data center or cloud environment; this failure is very unlikely.

• Fail-over due to slowness or poor response

When one of the node starts performing slow due to higher load or more data processing; instead of failing over to another node (typical NoSQL concept), Stonebraker recommends building a system that can take the load spikes; but it is really hard due to growing data needs and poor capacity planning as things change over time. So, one option is to retire by fail-over to newer one (not always possible if you already have latest and greatest specs) or adding more nodes to take the load spikes (distributed).

But in case of SQL with shared-nothing or shared-common architecture; it is not always possible to distribute the load to more than one system (for example, client X in system A, but when A exceeds the load; only option is to split client X into system A and B; but client X can’t be split due to data integrity or lack of common layer that can interact with both the nodes and perform the join/merge operations)

Both has its advantages and dis-advantages; but if NoSQL adopts strict consistency model as that of SQL; then it is hard to scale in the distributed environment where that architecture is more or less demanded by many big web applications where the scalability comes only by distribution and consistency has to be sacrificed to some extent to get the blend of performance + scalability + high availability

Its been a while anyone talked about Intel’s Hyper-Threading performance when it comes to databases. There were enough posts about disabling Hyper-Threading  completely when it comes to MySQL/InnoDB workloads way back when we had enough issues with scalability of InnoDB on multi-core systems. But things has changed quite a bit in the fast year or two in terms of multi-core support (thanks to Innobase/Mark Callaghan/Google and Percona). I still see lot of production servers running with HT disabled completely either in BIOS (append noht to kernel parameter) or manually disabling the CPUs (echo 0 > /sys/devices/system/node/node[0-1]/cpu[2-..]/online ).

Last few weeks or so; I ran quite a few tests on new production launch with 8-core CPUs and decided to go ahead with HT enabled (along with Intel Turbo mode) as it seems to give close to 15-20% performance gain. Ran tests which are completely InnoDB CPU/memory bound and HT seems to enable much better performance in every test mode.

Also tried with different thread schedulers with IO bound loads; still HT seems to give much better results; especially when most of the workload seems to be with 8-50 threads; and HT seems to give much better boost in that range; so no point to disable HT.

I tested both Intel Xeon X5570 and E5530 and here is the test results for CPU bound work load.

It is a clear indication that HT is not a bottleneck. In both the cases, turbo boost is enabled though.

As you can also notice; Intel Xeon X series CPUs yield much better results than E series (obvious as X-series is meant for performance where as E is for economy), which is close to $1000 extra, but yields ~15-20% performance for InnoDB workloads. You can find the comparison between these CPUs from Intel. If you are buying any new system and if you can spare extra $$; then always go for X series as its worth the price for performance