Download the PHP package jrsaunders/shard-matrix without Composer

ShardMatrix for PHP

Database Sharding system for MYSQL and Postgres

• Requirements

  • PHP 7.4^
• Supports:
  • A single Yaml configuration file
  • Multiple Nodes (DB servers)
  • Mysql
  • Postgres
  • Mysql & Postgres can be used together and hot swapped
  • Multiple Geo Locations
  • UUIDs bakes in all relevant data for tables and to which node it belongs
  • Docker
  • Kubernetes
  • Fast Asynchronous DB queries (using a purpose built GoThreaded service https://github.com/jrsaunders/go-threaded | https://hub.docker.com/r/jrsaunders/gothreaded or PHP Forking for crons or dev work)
  • Caching to File or to Redis or MemcacheD
  • Unique table columns across nodes
  • Table Grouping to ensure data is kept in the right shards so joins can be done
  • Using popular ORM from Laravel ( though your project does not need be in Laravel ) https://laravel.com/docs/7.x
  • Query building being database agnostic
  • Efficient pagination system across Nodes using caching
  • Raw SQL Queries

Quick Usage

Once you have initiated it as outlined in the INSTALLATION section below - here are some quick examples of usage.

If you are familiar with the ORM in Laravel - this is just an extension of that.

Create Table

• Creates Table across all appropriate Nodes (Mysql and Postgres simultaneously). This follows the guidance you have given in your Yaml Config file as to what tables belong on what nodes

Insert Record

• Insert Data - the system will choose an appropriate shard node and create a UUID for it that will be attributed to an appropriate node

Inserted Data

• Any further inserts done in this php process will be inserted into the same shard, if in the correct table group

Get Record By UUID and Update Record

• Get the record directly from the correct node (shard)
• Manipulate the record
• Update the record

Query Data and Conditionally Delete a Record

• Query all relevant nodes for the data
• Data returns as a Collection that can be iterated through
• Use data conditionally
• Manipulate the record and commit changes

Pagination

Pagination of Data from all shards

Pagination of Data from one shard defined by the UUID location

Installation

Installing ShardMatrix for PHP

Use Composer to install ShardMatrix, or pull the repo from github.

Preparing the YAML config file

ShardMatrix needs to know how your tables and columns and databases interact, so this config file will define this in a simple yaml file.

• You will need your credentials for your databases, and access privileges setup. Reference Yaml file

Example

This is a full example of how a configuration file should look.

Anatomy of the Configuration File

Version

Define the version. The most recent version is 1.

Table Groups

Define the table groups. As you add tables to your Application you will need to explicitly add them here to.

The group name is only used in ShardMatrix.

The table names are attributed to the groups. A table can only be in one group at a time and once you have written to the Databases, it is best not to change any table assigned to a group.

• Denotes the table groups section on config
• Denotes the name of a group of tables
• Denotes the table name

This section as it may appear.

Unique Columns in Tables

Unique Columns can be defined here. So in the users table email and username must be unique across all Nodes (shard databases).

Nodes

This is where you define your database connections, credentials, and what table groups and geos the node maybe using.

Nodes can be extended and added to as you go.

Node names must remain the same though as must the table groups they correspond to.

The anatomy of the node section.

• Denotes the where the nodes are defined
• Node Name
• DSN for connection to DB
• *optional Docker service name and port number
• *optional Geo - if a geo is stated the application inserting data will use this to choose this node to write new inserts to it
• *optional Stop new data being written here, unless connected to an existing UUID from this node
• Table groups that use this node must be defined here
• Table group user (that consists of the users, offers, payments tables)

The Node Section as it may appear in the config yaml.

Once Yaml Config File is Complete

Save the file to where the application is, in either a protected directory or externally inaccessible directory.

Alternatively it can be made into a Kubernetes Secret and given to your application that way.

Initiate in PHP

In these examples we have saved our Config file as shard_matrix.yaml and placed it in the same directory as our applications index php.

Basic Setup Using Only PHP and Webserver Resources

• Our config file
• Specifying a local directory to write db data to when it needs to

Setup Using Only GoThreaded and Redis

• Our config file
• Changes the service from PHP forking for asynchronous queries to GoThreaded
• Uses GoThreaded for asynchronous DB calls when we have to query all relevant shards
• This overwrites the PdoCache Service that was using writing to file, and now instead uses Redis caching