1 / 21

Agenda

Agenda. What is Sharding Benefits of Sharding Alternatives of Sharding When to start Sharding. What Is Sharding. Wikipedia:

waseem
Download Presentation

Agenda

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Agenda • What is Sharding • Benefits of Sharding • Alternatives of Sharding • When to start Sharding

  2. What Is Sharding • Wikipedia: • Horizontal partitioning is a database design principle whereby rows of a database table are held separately, rather than splitting by columns (which is what normalization and vertical partitioning do, to differing extents). Each partition forms part of a shard, which may in turn be located on a separate database server or physical location.

  3. Example

  4. Who Uses Sharding • Every big web site you can think of • FaceBook, Twitter, Flickr to name a few

  5. Benefits Of Sharding • Sharding lets you: • Scale Out database • Increate number of concurrent transactions • Improve performance • Decrease latency • Make the database elastic

  6. Performance Improvement • Size • Table size is reduced • Index size is reduced • More in memory – less disk access • Hits • Isolation is a pain • Less hits per database – less isolation

  7. Database Isolation • Database needs to maintain copies of the data per user, to ensure transaction boundaries • More users – more copies • Longer transactions – more copies • Indexes are stored on actual data • Copies are problematic • See here for complete explanation - http://www.scalebase.com/isolation-levels-in-relational-databases/ • Sharding helps reduces # of transaction per database shard

  8. Alternatives For Sharding • Tuning • Scale Up • Read/Write Splitting • NoSQL

  9. Database Tuning • There are many ways to tune your database • Allot of data online, check out this post • http://forge.mysql.com/wiki/Top10SQLPerformanceTips

  10. Database Tuning – Some Examples • innodb_buffer_pool_size • Holds the data and indexes of tables in memory. • Bigger buffer results in faster row lookups. • The bigger the better. • Default – 8M • Query Cache • Keeps the result of queries in memory until they are invalidated by writes. • query_cache_size • total size of memory available to query caching • query_cache_limit • the maximum number of kilobytes one query may be in order to be cached. • query_cache_size= 128MB • query_cache_limit= 4MB

  11. Scaling Up Hardware • Usually DB gets the strongest servers • However – there is a limit to how much performance gains you can get from increasing hardware • Some data: http://www.mysqlperformanceblog.com/2011/01/26/modeling-innodb-scalability-on-multi-core-servers/

  12. SSD • Solid State Drive • Better latency and access time than regular HDD • Cost more per GB (but prices are dropping) • VadimTkachenko from Percona gave a great lecture on SSD at MySQL Conf2011 • (see slides at http://en.oreilly.com/mysql2011/public/schedule/detail/17117) • Claims you can expect up to X7 performance from SSD

  13. Read/Write Splitting • Write to MySQL master, read from 1 (or more) slaves • Excellent read scaling • Many issues: • Since replication is a-synchronous – read might not be up to date • Transactions create stickiness • Code changes

  14. NoSQL • A term used to designate databases which differ from classic relational databases in some way. These data stores may not require fixed table schemas, and usually avoid join operations and typically scale horizontally. Academics and papers typically refer to these databases as structured storage, a term which would include classic relational databases as a subset. http://en.wikipedia.org/wiki/NoSQL

  15. NoSQL Types • Key/Value • A big hash table • Examples: Voldemort, Amazon Dynamo • Big Table • Big table, column families • Examples: Hbase, Cassandra • Document based • Collections of collections • Examples: CouchDB, MongoDB • Graph databases • Based on graph theory • Examples: Neo4J • Each solves a different problem

  16. NO-SQL http://browsertoolkit.com/fault-tolerance.png

  17. When To Start Sharding • Database Size (including indexes) > available memory • When databases go to disk, bad things happen • Too many hits/second • High write/read ration

  18. Downsides of Sharding • Start small, end big • TCO • Management • Backup • Time to market

  19. Benefits Of ScaleBase • Sharding – no hassle • No hidden costs • New features • Easy administration

  20. Thank You • Join us at our next webinar, Scaling MySQL on EC2, on January 10th, 2012.

More Related