Database

MySQL Optimizations

Partitioning

MySQL partitioning is a concept with 2 contexts primarily the horizontal partitioning and the other being vertical partitioning.

Partitioning of relational data, it usually refers to decomposing your tables or breaking your tables either row-wise (horizontally) or column-wise (vertically).

Vertical partitioning, aka row splitting, uses the same splitting techniques as database normalization, but usually the term (vertical / horizontal) data partitioning refers to a physical optimization whereas normalization is an optimization on the conceptual level.

This is usually preferred when there is rare data used in one table, and you move the rare data into another table, thus reducing the overall size when running queries.

Horizontal partitioning“, or sharding, is replicating [copying] the schema, and then dividing the data based on a shard key.

Vertical partitioning” involves dividing up the schema (and the data goes along for the ride).

Horizontal Partitioning in data base

Keeping all the fields Ex:Table Employees has id,name,Geographical location ,email,designation,phone

Ex:1.Keeping all the fields and distributing records in multiple machines.say id= 1-100000 or 100000-200000 records in one machine each and distributing over multiple machines.

Ex:2.Keeping separate databases for Regions EG: Asia Pacific,North America

Key:Picking set of rows based on a criteria

Vertical Partitioning in data base

It is similar to Normalization where the same table is divided in to multiple tables and used with joins if required.

Ex: id, name,designation is put in one table and phone , email which may not be frequently accessed are put in another.

Key:Picking set of columns based on a criteria.

  • Horizontal/Vertical Scaling is different from partitioning

Horizontal Scaling:

is about adding more machines to enable improved responsiveness and availability of any system including database.The idea is to distribute the work load to multiple machines.

Vertical Scaling:

is about adding more capability in the form of CPU,Memory to existing machine or machines to enable improved responsiveness and availability of any system including database.In a virtual machine set up it can be configured virtually instead of adding real physical machines.

https://www.nylas.com/blog/growing-up-with-mysql/

MySQL does not need SQL

https://medium.com/@jeeyoungk/how-sharding-works-b4dec46b3f6

http://highscalability.com/blog/2013/4/15/scaling-pinterest-from-0-to-10s-of-billions-of-page-views-a.html

 http://softwareas.com/horizontally-scaling-databases-mysqlpostgres-sharding/

 https://github.com/evanelias/jetpants

Elias_percona_live_sc_2013

Massively Sharded MySQL at Tumblr Presentation

RDS_WhitePaper

https://noc.wikimedia.org/

http://blog.maxindelicato.com/2008/12/scalability-strategies-primer-database-sharding.html

MySQL Sharding with ProxySQL

Why you don’t want to shard.

https://blog.asana.com/2015/04/sharding-is-bitter-medicine/

http://project-voldemort.com

vitess.io

http://www.craigkerstiens.com/2012/11/30/sharding-your-database/

MySQL Sharding Models for SaaS Applications

Leave a Reply

Your email address will not be published. Required fields are marked *