W Master write from read, memory replication between cluster nodes, single table cross-cutting, analysis report system through Server Union table
http://www.agildata.com/database-sharding/
Database Partitioning Options
It has a long been known that database partitioning are the answer to improving the performance and scalability of relational Databases. Many techniques has been evolved, including:
- Master/slave: this is the simplest option used by many organizations, with a single Master server for all write (Crea Te Update or Delete, or CRUD) operations, and one or many additional Slave servers that provide read-only operations. The Master uses standard, near-real-time database replication to each of the Slave servers. The Master/slave model can speed overall performance to a point, allowing read-intensive processing to be offloaded to the Slave servers, but there is several limitations with this approach:
- The single Master server for writes are a clear limit to scalability, and can quickly create a bottleneck.
- The Master/slave replication mechanism is ' near-real-time, ' meaning that the Slave servers was not guaranteed to has A current picture of the The data, which is in the Master. While the is fine for some applications, if your applications require a up-to-date view, this approach is unacceptable.< /li>
- Many organizations use the Master/slave approach for high-availability as well, but it suffers from this same Li Mitation given that the Slave servers is not necessarily current with the Master. If a catastrophic failure of the Master server occurs, any transactions that is pending for replication would be lost, a s Ituation that's highly unacceptable for more business transaction applications.
- Cluster Computing: cluster Computing utilizes many servers operating in a group with shared messaging between the No Des of the cluster. Most often the scenario relies on a centralized shared disk facility, typically a Storage area Network (SAN). Each node in the cluster runs a and instance of the database server, operating in various modes:
- for high-availability, many nodes in the cluster can is used for reads and only one for write (CRUD) operations. This can make reads faster, but the write transactions does not see any benefit. If a failure of one node occurs, then another node on the cluster takes over, again continuing to operating against the SH ared disk facility. This approach have limited scalability due to the single bottleneck for CRUD operations. Even the reads would ultimately hit a performance limit as the centralized GKFX disk facility can only spread the load so Much before diminishing returns is experienced. The read limitations is particularly evident when an application requires complex joins or contains non-optimized SQL STA Tements.
- more advanced clustering techniques rely on real-time memory replication between nodes, keeping the memory image of No Des in the cluster up to date via a real-time messaging system. This allows all node to operate in both read or write mode, but is ultimately limited by the amount of traffic that can B e transmitted between nodes (using a typical network or other high-speed communication mechanism). Therefore, as nodes is added, the communication and memory replication overhead increases geometrically, thus hitting SEv Ere scalability limits, often with a relatively small number of nodes. This solution also suffers from the same GKFX disk limitations of a traditional cluster, given that a growing, single LA RGE database has increasingly intensive disk I/O.
- table Partitioning: many Database management systems Support Table partitioning, where data in a single large tab Le can is split across multiple disks for improved disk I/O utilization. The partitioning is typically do horizontally (separating rows by range across disk partitions), but can was vertical in Some systems as well (placing different columns on separate partitions). This approach can help reduce the disk I/O bottleneck for a given table, but can often make joins and other operations slo Wer. Further, since the approach relies on a single server instance of the database management system, all other CPUs and memory Contention limitations apply, further limiting scalability.
- Federated Tables:an offshoot of table partitioning is the federated Table approach, where Tables can be accessed across m Ultiple servers. This approach was necessarily highly complex to administer, and lacks efficiency as the federated tables must be accessed O Ver the network. This approach could work for some reporting or analytical the tasks, but to general Read/write transactions It's not a very Li kely choice.
The common drawback with each of the these approaches are the reliance on shared facilities and resources. Whether relying on shared memory, centralized disk, or processor capacity they all suffer with scalability limitations, n OT to mention many other drawbacks, including complex administration, lack of support for critical business requirements, and high availability limitations.
Database Partitioning Options DATABASE sharding
