Shulou(Shulou.com)06/01 Report--

In this article, the editor for you to introduce in detail the "MariaDB Spider database sub-table method", the content is detailed, the steps are clear, the details are handled properly, I hope this "MariaDB Spider database sub-table method" article can help you solve your doubts, following the editor's ideas slowly in-depth, together to learn new knowledge.

Sub-database sub-table

Generally speaking, the database has the following practices:

Hash fragmentation: calculates a hash value based on the identity of a piece of data and assigns it to a specific database engine

Fragmentation by range: assigns a piece of data to a specific database engine based on its identity (usually a value)

Fragmentation by list: based on the identification of some fields, it is assigned to a specific database engine if the criteria are met.

There are many ways to divide libraries and tables, such as writing a code base to support multiple databases in a program, the program needs to know the address of each database and write code to support it, and use middleware to connect multiple database engines. the program only needs to know the address of the middleware.

However, after dividing the database and tables, because any two tables may be in different database instances, when the two tables are joined and queried, the interaction between the two database instances becomes more complex. When the amount of data in the cluster is large, you cannot join at will, and you may need other ways to support the aggregate query.

There are advantages and disadvantages of sub-library and sub-table, so I will not say any more here, but learn to plan first.

MariaDB Server is open source and one of the most popular relational databases. MariaDB is developed from a branch of Mysql and has always maintained compatibility with Mysql. Because of Oracle's acquisition, MySQL is owned by Oracle, there is the possibility of closed source, and gradually commercialized, become not halal, so the father of Mysql created MariaDB to be fully compatible with Mysql, open source and free.

MariaDB uses the Spider plug-in to support sublibraries and tables, and the Spider storage engine is a storage engine with built-in sharding function. It supports partitioning and xa transactions and allows tables of different MariaDB instances to be processed as if they were on the same instance.

Please refer to the material: https://mariadb.com/kb/en/spider/

In this article, the author will use MariaDB Spider to carry out the practice of dividing databases and tables.

Deploy MariaDB instance

In order to better create a practical environment for sub-libraries and tables, you need three "physical" databases and one logical database, that is, four MariaDB instances. MariaDB actually occupies a small amount of memory. The server with 4G memory is equipped with Kubernetes, and four MariaDB databases are deployed with Docker. The running speed is normal, which is enough for us to test and exercise 4G memory.

The relationship between the four databases is shown in the figure:

The logical database instance is called Spider Proxy Node, and the database instance that actually stores data is called Backend Node.

A typical Spider deployment has a shared cluster architecture. The system is suitable for any cheap hardware and has the lowest specific requirements for hardware or software. It consists of a group of computers with one or more MariaDB processes called nodes.

The node that stores the data will be designed as Backend Nodes and can be any MariaDB, MySQL, Oracle server instance, using any storage engine available in the back end.

Docker deployment

If there are not enough machines, it will be troublesome to deploy with virtual machines, so here I use the Docker rapid deployment exercise.

Reference: https://mariadb.com/kb/en/installing-and-using-mariadb-via-docker/

View the list of MariaDB image versions: https://hub.docker.com/_/mariadb/

Create four database instances directly, one of which is a Spider instance, which is distinguished by port.

Docker run-- name mariadbtest1-e MYSQL_ROOT_PASSWORD=123456-p 13306 docker.io/library/mariadb:10.7docker run-- name mariadbtest2-e MYSQL_ROOT_PASSWORD=123456-p 13307 MYSQL_ROOT_PASSWORD=123456 3306-d docker.io/library/mariadb:10.7docker run-- name mariadbtest3-e MYSQL_ROOT_PASSWORD=123456-p 13308 MYSQL_ROOT_PASSWORD=123456 3306-d docker.io/library/mariadb:10.7docker run-- name mariadbspider-e MYSQL_ROOT_PASSWORD=123456-p 13309 Switzerland 3306-d docker.io/library/mariadb:10.7

Next, go to each container instance, go to the / etc/mysql/mariadb.conf.d directory, modify the 50-server.cnf file, and run the remote access database instance. Since there are no editing commands such as nano and vi in the container, you can quickly replace the file contents with the following command:

Echo'[server] [mysqld] pid-file = / run/mysqld/mysqld.pidbasedir = / usrdatadir = / var/lib/mysqltmpdir = / tmplc-messages-dir = / usr/share/mysqllc-messages = en_USskip-external-lockingbind-address = 0.0.0.0expire_logs_days = 10 minutes- Set-server = utf8mb4collation-server = utf8mb4_general_ ci [embedded] [mariadb] [mariadb-10.7]'> 50-server.cnf

Then check the IP in the host of each container:

Docker inspect-- format=' {{.NetworkSettings.IPAddress}} 'mariadbtest1 mariadbtest2 mariadbtest3 mariadbspider

172.17.0.2

172.17.0.3

172.17.0.4

172.17.0.5

Then open the container named mariadbspider and follow the Spider plug-in inside:

Apt updateapt install mariadb-plugin-spider virtual machine deployment

Here you need four virtual machines, each of which needs to install the MariaDB database engine and some toolkits first.

Please refer to: https://mariadb.com/kb/en/spider-installation/

First install MariaDB Community Server, the database engine, on each virtual.

If you use a virtual machine to deploy and install, you need to replace the domestic image source in order to quickly download the required package. The Centos server can quickly update the image source by directly following the command. If it is a Debain series, you can find the corresponding image source by yourself.

Wget-O / etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo# clears cache yum clean all# to generate new cache yum makecache

Next, configure MariaDB's official package repository:

Sudo yum install wgetwget https://downloads.mariadb.com/MariaDB/mariadb_repo_setupecho "fd3f41eefff54ce144c932100f9e0f9b1d181e0edd86a6f6b8f2a0212100c32c mariadb_repo_setup" | sha256sum-c-chmod + x mariadb_repo_setupsudo. / mariadb_repo_setup-- mariadb-server-version= "mariadb-10.7"

Update the mirror source cache again:

# clear cache yum clean all# to generate new cache yum makecache

Install the MariaDB community server and package dependencies:

Sudo yum install MariaDB-server MariaDB-backup

Next, the configuration allows remote access to the database.

The configuration files for MariaDB are all in / etc/my.cnf. After opening the / etc/my.cnf.d/ directory, modify the server.cnf file to allow remote access. Find the bind-address attribute and remove #.

# bind-address=0.0.0.0 ↓ bind-address=0.0.0.0

To understand the role of each configuration, please refer to https://mariadb.com/docs/deploy/community-spider/

Change the password. Because the database deployed in bare metal does not have a password, it needs to be configured manually.

Open the terminal and execute the following command:

Mysql-u root-pset password for root @ localhost = password ('123456')

Then execute quit; to exit the database operation terminal.

If you prompt that root does not exist, use mysql-u mysql-p with an empty password and press enter directly. If not, refer to: https://www.whuanle.cn/archives/1385

Then restart the database instance:

Systemctl restart mariadbsystemctl status mariadb

Then check the firewall configuration, or perform sudo iptables-F to clean up the firewall configuration.

MariaDB configuration

Some of the main attributes in the MariaDB configuration file are described as follows:

Field description bind_address binding access address max_connections maximum number of connections thread_handling setting MariaDB Community Server how to handle thread log_error error log output file for client connection

MariaDB basic maintenance commands:

Description commands start sudo systemctl start mariadb stop sudo systemctl stop mariadb restart sudo systemctl restart mariadb enable sudo systemctl enable mariadb during startup disable sudo systemctl disable mariadb status sudo systemctl status mariadb check each instance

After you deploy the database, you need to connect to each database for testing to check that the database is working properly.

Configure Spider

Open the mariadbspider database instance, execute the following command, load the spider plug-in, and set it to the Spider database instance.

INSTALL SONAME 'ha_spider'

Execute the command to query whether the Spider plug-in has been started:

SELECT * FROM mysql.plugin

Please refer to the material: https://mariadb.com/kb/en/spider-installation/

Remote list

The MariaDB Spider pattern has been set up, and it's time to put it into practice.

In this schema, a table in Spider corresponds to a database with the same name in a database instance, that is, the database name system, with the same name.

First, in three database instances, create a test database named test1, and then execute the command to create the table:

CREATE TABLE s (id INT NOT NULL AUTO_INCREMENT, code VARCHAR (10), PRIMARY KEY (id))

Then in the mariadbspider instance, execute the command, create a logical table, and bind the table to the mariadbtest1 instance.

CREATE TABLE s (id INT NOT NULL AUTO_INCREMENT, code VARCHAR (10), PRIMARY KEY (id)) ENGINE=SPIDER COMMENT 'host "172.17.0.2", user "root", password "123456", port "3306"'

Pay attention to replace your IP, and pay attention to the port. If the container accesses the container, use 3306 directly.

If it is not properly configured, the database does not correspond, and so on, it may appear:

> 1046-No database selected

> time: 0.062s

Then in mariadbspider, insert four pieces of data:

INSERT INTO s (code) VALUES ('a'); INSERT INTO s (code) VALUES ('b'); INSERT INTO s (code) VALUES ('c'); INSERT INTO s (code) VALUES ('d')

If you open each of the three instances, you will find that the inserted data will only appear in the mariadbtest1 because the table is only bound to it. You can also add, delete, check and modify this table on mariadbspider, and all operations will be synchronized to the corresponding database instance.

Benchmark performance test

SysBench is a modular, cross-platform, and multithreaded benchmark tool that supports Windows and Linux to evaluate operating system parameters that are important for systems running databases under high loads. The idea of this benchmark suite is to quickly get an impression of system performance without setting a complex database benchmark or even installing the database at all. It can be tested to:

File iUniver performance

Scheduler performance

Memory allocation and transfer speed

POSIX threads achieve performance

Database server performance (OLTP benchmark)

Project address: https://github.com/akopytov/sysbench

Linux can install binary packages directly.

Debian/Ubuntu

Curl-s https://packagecloud.io/install/repositories/akopytov/sysbench/script.deb.sh | sudo bashsudo apt-y install sysbench

RHEL/CentOS:

Curl-s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bashsudo yum-y install sysbench

Fedora:

Curl-s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash sudo dnf-y install sysbench

Arch Linux:

Sudo pacman-Suy sysbench

Sysbench command format:

Sysbench-threads=2-report-interval=3-histogram-time=50-db-driver=mysql-mysql-host=-mysql-db= mysql-user= mysql-password= run

First, create simulation data under the current specific database:

Sysbench oltp_read_write-db-driver=mysql-mysql-user=root-mysql-password=123456-mysql-host=123.123.123.123-mysql-port=13309-mysql-db=test1 preparesysbench 1.0.18 (using system LuaJIT 2.1.0-beta3) Creating table 'sbtest1'...Inserting 10000 records into' sbtest1'Creating a secondary index on 'sbtest1'...

Then run the test:

Sysbench oltp_read_write-- db-driver=mysql-- mysql-user=root-- mysql-password=123456-- mysql-host=123.123.123.123-- mysql-port=13309-- mysql-db=test1 runSQL statistics: queries performed: read: 112 write: 32 other: 16 total: 160 transactions: 8 (0.80 per sec.) Queries: 160 (15.96 per sec.) Ignored errors: 0 (0.00 per sec.) Reconnects: 0 (0.00 per sec.) General statistics: total time: 10.0273s total number of events: 8Latency (ms): min: 1244.02 avg: 1253.36 Max: 1267.87 95th percentile: 1258.08 sum: 10026.85Threads fairness: events (avg/stddev): 8.00000.00 execution time (avg/stddev): 10.0269Comp0.00

Or generate a histogram every 3 seconds:

Sysbench oltp_read_write-threads=2-report-interval=3-histogram-time=50-table-size=1000000-db-driver=mysql-mysql-user=root-mysql-password=123456-mysql-host=123.123.123.123-mysql-port=13309-mysql-db=test1 run

Clean up the data generated by the simulation:

Sysbench oltp_read_write-db-driver=mysql-mysql-user=root-mysql-password=123456-mysql-host=123.123.123.123-mysql-port=13309-mysql-db=test1 cleanup

When sysbench runs the test, the optional parameters are as follows:

Use-time= to run for a fixed time

Use-events=0 to set no restrictions on executed queries

Disable prepared statements using-db-ps-mode=disable

Use-report-interval= to get drawing points

Get a histogram with-- histogram

Sysbench has three processes or execution modes:

Prepare: prepare actions for test execution that require them, such as creating the necessary files on disk for fileio tests, or populating the test database for database benchmarking.

Run: run the actual test specified with the testname parameter. This command is provided by all tests.

Cleanup: delete temporary data after the test runs in the creation of a test.

You can also refer to another article by the author and use another method to benchmark: https://www.whuanle.cn/archives/1388

Join the backend database

In the remote table section, when we create a table, we bind a database instance. In fact, we can also configure multiple database instances to Spider in advance. Here are the configuration commands executed in Spider:

CREATE SERVER mariadbtest1 FOREIGN DATA WRAPPER mysql OPTIONS (HOST '172.17.0.2, DATABASE' test1', USER 'root', PASSWORD' 123456, PORT 3306); CREATE SERVER mariadbtest2 FOREIGN DATA WRAPPER mysql OPTIONS (HOST '172.17.0.3, DATABASE' test1', USER 'root', PASSWORD' 123456, PORT 3306) CREATE SERVER mariadbtest3 FOREIGN DATA WRAPPER mysql OPTIONS (HOST '172.17.0.4, DATABASE' test1', USER 'root', PASSWORD' 123456, PORT 3306); Hash fragmentation

In this section, we fragment a table, and when inserting data, the data is automatically shredded into three database instances.

In the three data node databases, under the test1 database, execute the command to create the table:

CREATE TABLE shardtest (id int (10) unsigned NOT NULL AUTO_INCREMENT, k int (10) unsigned NOT NULL DEFAULT'0), c char (120) NOT NULL DEFAULT'', pad char (60) NOT NULL DEFAULT'', PRIMARY KEY (id), KEY k (k))

At this point, all three database instances have the same table.

Then in the mariadbspider instance, execute the command, create a logical table, and connect the table to the three database instances through the sliced schema.

CREATE TABLE test1.shardtest (id int (10) unsigned NOT NULL AUTO_INCREMENT, k int (10) unsigned NOT NULL DEFAULT'0), c char (120) NOT NULL DEFAULT'', pad char (60) NOT NULL DEFAULT'', PRIMARY KEY (id), KEY k (k)) ENGINE=spider COMMENT='wrapper "mysql", table "shardtest" 'PARTITION BY KEY (id) (PARTITION pt1 COMMENT=' srv "mariadbtest1"', PARTITION pt2 COMMENT=' srv "mariadbtest2"', PARTITION pt3 COMMENT=' srv "mariadbtest3"')

Then open https://github.com/whuanle/write_share_database and find the shard test data. SQL file, which contains a lot of simulation data.

You can observe that the data of the three database instances are different.

Slice according to the range of values

The choice of the sharding method lies in the PARTITION BY attribute. For example, if the hash shard is calculated based on a key, the configuration command is PARTITION BY KEY (id), or PARTITION BY range columns () if the sharding is based on the value range.

) ENGINE=spider COMMENT='wrapper "mysql", table "shardtest" 'PARTITION BY range columns (k) (PARTITION pt1 values less than (5000) COMMENT=' srv "mariadbtest1"', PARTITION pt2 values less than (5100) COMMENT=' srv "mariadbtest2" 'PARTITION pt3 values less than (5200) COMMENT=' srv "mariadbtest3"'); fragment according to the list

According to the list fragmentation, usually a field, the data can be divided into different types, and the data can be grouped according to the contents of this field.

) ENGINE=spider COMMENT='wrapper "mysql", table "shardtest" 'PARTITION BY list columns (k) (PARTITION pt1 values in (' 4900, '4901,' 4902') COMMENT=' srv "mariadbtest1", PARTITION pt2 values in ('5000,' 5100') COMMENT=' srv "mariadbtest2" 'PARTITION pt3 values in (' 5200, '5300') COMMENT=' srv "mariadbtest3")

When the k field of the data has a value of 4900, 4901, or 4902, it will be shredded to the mariadbtest1 instance.

After reading this, the article "the method of dividing databases and tables in MariaDB Spider database" has been introduced. If you want to master the knowledge points of this article, you still need to practice and use it yourself to understand it. If you want to know more about related articles, welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.