TOAST stands for The Oversized-Attribute Storage Technique.
PostgreSQL uses a fixed page size (commonly 8 kB), and does not allow tuples to span multiple pages. Therefore, it is not possible to store very large field values directly. To overcome this limitation, large field values are compressed and/or broken up into multiple physical rows. Read More
This blog post describes how to deploy a TiDB cluster on GCP Google Kubernetes Engine (GKE). TiDB on Kubernetes is the standard way to deploy TiDB on public clouds.
TiDB Architecture
TiDB is designed to consist of multiple components. These components communicate with each other and form a complete TiDB system. The architecture is as follows:
TiDB server
The TiDB server is a stateless SQL layer that exposes the connection endpoint of the MySQL protocol to the outside. The TiDB server receives SQL requests, performs SQL parsing and optimization, and ultimately generates a distributed execution plan. It is horizontally scalable and provides the unified interface to the outside through the load balancing components such as Linux Virtual Server (LVS), HAProxy, or F5. It does not store data and is only for computing and SQL analyzing, transmitting actual data read request to TiKV nodes (or TiFlash nodes).
Placement Driver (PD) server
The PD server is the metadata managing component of the entire cluster. It stores metadata of real-time data distribution of every single TiKV node and the topology structure of the entire TiDB cluster, provides the TiDB Dashboard management UI, and allocates transaction IDs to distributed transactions. The PD server is “the brain” of the entire TiDB cluster because it not only stores metadata of the cluster, but also sends data scheduling command to specific TiKV nodes according to the data distribution state reported by TiKV nodes in real time. In addition, the PD server consists of three nodes at least and has high availability. It is recommended to deploy an odd number of PD nodes.
Storage servers
Storage servers
TiKV server
The TiKV server is responsible for storing data. TiKV is a distributed transactional key-value storage engine. Region is the basic unit to store data. Each Region stores the data for a particular Key Range which is a left-closed and right-open interval from StartKey to EndKey. Multiple Regions exist in each TiKV node. TiKV APIs provide native support to distributed transactions at the key-value pair level and supports the Snapshot Isolation level isolation by default. This is the core of how TiDB supports distributed transactions at the SQL level. After processing SQL statements, the TiDB server converts the SQL execution plan to an actual call to the TiKV API. Therefore, data is stored in TiKV. All the data in TiKV is automatically maintained in multiple replicas (three replicas by default), so TiKV has native high availability and supports automatic failover.
TiFlash server
The TiFlash Server is a special type of storage server. Unlike ordinary TiKV nodes, TiFlash stores data by column, mainly designed to accelerate analytical processing.
Prerequisites
Before deploying a TiDB cluster on GCP GKE, make sure the following requirements are satisfied
1) Create a project
3) Activate Cloud Shell
Ensure that you have the available quote for Compute Engine CPU in your cluster’s region.
4) Configure the GCP service
Configure your GCP project and default region.
gcloud config set core/projectgcloud config set compute/region Example: gcloud config set core/project erudite-spot-326413 gcloud config set compute/zone us-west1-a
Create a GKE cluster and node pool
Enable container.googleapis.com
gcloud services enable container.googleapis.com
Create a GKE cluster and a default node pool
gcloud container clusters create tidb --region us-west1-a --machine-type n1-standard-4 --num-nodes=1
Create separate node pools for PD, TiKV, and TiDB
gcloud container node-pools create pd --cluster tidb --machine-type n1-standard-4 --num-nodes=1 \
--node-labels=dedicated=pd --node-taints=dedicated=pd:NoSchedule
gcloud container node-pools create tikv --cluster tidb --machine-type n1-highmem-8 --num-nodes=1 \
--node-labels=dedicated=tikv --node-taints=dedicated=tikv:NoSchedule
gcloud container node-pools create tidb --cluster tidb --machine-type n1-standard-8 --num-nodes=1 \
--node-labels=dedicated=tidb --node-taints=dedicated=tidb:NoSchedule
Deploy TiDB Operator
This section describes how to deploy a TiDB Operator on GCP GKE
Install Helm
Helm is used for deploying TiDB Operator
curl -fsSL -o get_helm.sh https://raw.githubusercontent.com/helm/helm/master/scripts/get-helm-3 chmod 700 get_helm.sh ./get_helm.sh
git clone https://github.com/pingcap/tidb-operator.git && cd tidb-operator && kubectl create serviceaccount tiller --namespace kube-system && kubectl apply -f ./manifests/tiller-rbac.yaml && helm init --service-account tiller --upgrade
Helm will also need a couple of permissions to work properly. We can download them from the tidb-operator project.
Ensure that the tiller pod is running.
kubectl get pods -n kube-system
Note: If it is not running (Status: ImagePullBackOff), then run the following commands. Then check the status again.
kubectl delete -n kube-system deployment tiller-deploy helm init --service-account tiller --upgrade
Install TiDB Operator CRDs
TiDB Operator uses Custom Resource Definition (CRD) to extend Kubernetes. Therefore, to use TiDB Operator, you must first create the TidbCluster CRD, which is a one-time job in your Kubernetes cluster.
kubectl apply -f https://raw.githubusercontent.com/pingcap/tidb-operator/master/manifests/crd.yaml
Add the PingCAP repository
helm repo add pingcap https://charts.pingcap.org/
Create a namespace for TiDB Operator
kubectl create namespace tidb-admin
Install TiDB Operator
helm install ./charts/tidb-operator -n tidb-admin --namespace=tidb-admin --version v1.2.3
Make sure tidb-operator components are running.
kubectl get pods --namespace tidb-admin -l app.kubernetes.io/instance=tidb-admin
kubectl get pods --namespace tidb-admin -o wide
Deploy a TiDB Cluster and the Monitoring Component
This section describes how to deploy a TiDB cluster and its monitoring services.
Create namespace
kubectl create namespace tidb-cluster
Note: A namespace is a virtual cluster backed by the same physical cluster. This document takes tidb-cluster as an example. If you want to use other namespace, modify the corresponding arguments of -n or –namespace.
Download the sample TidbCluster and TidbMonitor configuration files
curl -O https://raw.githubusercontent.com/pingcap/tidb-operator/master/examples/gcp/tidb-cluster.yaml && \ curl -O https://raw.githubusercontent.com/pingcap/tidb-operator/master/examples/gcp/tidb-monitor.yaml
Deploy the TidbCluster and TidbMonitor CR in the GKE cluster
kubectl create -f tidb-cluster.yaml -n tidb-cluster && \ kubectl create -f tidb-monitor.yaml -n tidb-cluster
Watch Cluster Status
watch kubectl get pods -n tidb-cluster
Wait until all Pods for all services are started. As soon as you see Pods of each type (-pd, -tikv, and -tidb) are in the “Running” state, you can press Ctrl+C to get back to the command line and go on to connect to your TiDB cluster.
View the cluster status
kubectl get pods -n tidb-cluster
Get list of services in the tidb-cluster
kubectl get svc -n tidb-cluster
Access the TiDB database
After you deploy a TiDB cluster, you can access the TiDB database via MySQL client.
Prepare a bastion host
The LoadBalancer created for your TiDB cluster is an intranet LoadBalancer. You can create a bastion host in the cluster VPC to access the database.
Note: You can also create the bastion host in other zones in the same region.
gcloud compute instances create bastion \
--machine-type=n1-standard-4 \
--image-project=centos-cloud \
--image-family=centos-7 \
--zone=us-west1-a
Install the MySQL client and Connect
After the bastion host is created, you can connect to the bastion host via SSH and access the TiDB cluster via the MySQL client.
Connect to the bastion host via SSH.
gcloud compute ssh tidb@bastion
Install the MySQL Client.
sudo yum install mysql -y
Connect the client to the TiDB cluster
mysql -h ${tidb-nlb-dnsname} -P 4000 -u root
${tidb-nlb-dnsname} is the LoadBalancer IP of the TiDB service.
You can view the IP in the EXTERNAL-IP field of the kubectl get svc basic-tidb -n tidb-cluster execution result.
kubectl get svc basic-tidb -n tidb-cluster
mysql -h 10.138.0.6 -P 4000 -u root
Check TiDB Version
select tidb_version()\G
Create Test table
use test; create table test_table (id int unsigned not null auto_increment primary key, v varchar(32));
select * from information_schema.tikv_region_status where db_name=database() and table_name='test_table'\G
Query the TiKV store status
select * from information_schema.tikv_store_status\G
Query the TiDB cluster information
select * from information_schema.cluster_info\G
Access the Grafana Monitor Dashboard
Obtain the LoadBalancer IP of Grafana
kubectl -n tidb-cluster get svc basic-grafana
In the output above, the EXTERNAL-IP column is the LoadBalancer IP.
You can access the ${grafana-lb}:3000 address using your web browser to view monitoring metrics. Replace ${grafana-lb} with the LoadBalancer IP.
Scale out
Before scaling out the cluster, you need to scale out the corresponding node pool so that the new instances have enough resources for operation.
This section describes how to scale out the EKS node group and TiDB components.
Scale out GKE node group
| gcloud container clusters resize tidb –node-pool tikv –num-nodes 2 |
The following example shows how to scale out the tikv node pool of the tidb cluster to 6 nodes:
gcloud container clusters resize tidb --node-pool tikv --num-nodes 2
Note: In the regional cluster, the nodes are created in 3 zones. Therefore, after scaling out, the number of nodes is 2 * 3 = 6.
After that, execute kubectl edit tc basic -n tidb-cluster and modify each component’s replicas to the desired number of replicas. The scaling-out process is then completed.
kubectl edit tc basic -n tidb-cluster
Deploy TiFlash and TiCDC
TiFlash is the columnar storage extension of TiKV.
TiCDC is a tool for replicating the incremental data of TiDB by pulling TiKV change logs.
Create new node pools
gcloud container node-pools create tiflash --cluster tidb --machine-type n1-highmem-8 --num-nodes=1 \
--node-labels dedicated=tiflash --node-taints dedicated=tiflash:NoSchedule
gcloud container node-pools create ticdc --cluster tidb --machine-type n1-standard-4 --num-nodes=1 \
--node-labels dedicated=ticdc --node-taints dedicated=ticdc:NoSchedule
Configure and deploy
tiflash:
baseImage: pingcap/tiflash
replicas: 1
storageClaims:
- resources:
requests:
storage: 100Gi
nodeSelector:
dedicated: tiflash
tolerations:
- effect: NoSchedule
key: dedicated
operator: Equal
value: tiflash
ticdc:
baseImage: pingcap/ticdc
replicas: 1
nodeSelector:
dedicated: ticdc
tolerations:
- effect: NoSchedule
key: dedicated
operator: Equal
value: ticdc
kubectl -n tidb-cluster apply -f tidb-cluster.yaml
Delete Cluster
List existing clusters for running containers
gcloud container clusters list
Delete cluster.
gcloud container clusters delete tidb
Cheers!
In this blog post, I’m going to show you how to setup GTID replication using Mariabackup.
There are 2 main benefits of using global transaction:
Here are the general steps:
1. Ensure that the server_id value and bind_address are configured differently in my.cnf in each of the server that will be part of the replication.
In this example, we will configure a 2 node master-slave setup. The bind-address is the hostname IP.
vi my.cnf
In Master:
server_id=1 bind-address=192.168.1.115
In Replica:
server_id=2 bind-address=192.168.1.131
2. Enable binary logging and GTID strict mode in both servers
show global variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+
show global variables like '%gtid_strict_mode%'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | gtid_strict_mode | ON | +------------------+-------+
If bin logging is not enabled, you may do so by adding the line below in my.cnf
log-bin = db1-bin # Or specify an different path log-bin = /mariadb/bin/logs/bin_logs/bin_log
Restart DB service for the change to take effect.
sudo service mysql stop sudo serivice mysql start
If gtid is not enabled, add the line below in my.cnf, then enable it globally.
Add this line in cnf
gtid_strict_mode=1
Login to MariaDB, then set global_script_mode=1.
set global gtid_strict_mode=1;
3. Create a user in Master.
The replica is going to use this user connection to read the binary logs on the master and then put those into the relay logs on the replica.
CREATE USER 'repl'@'%' IDENTIFIED BY 'P@$$w0rd'; GRANT RELOAD, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%';
4. Install qpress (As root) in both Master and Slave for compression/decompression.
sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y yum install qpress -y
5. In Master, Take a full backup of the database using Mariabackup.
mariabackup --defaults-file=/etc/my.cnf --backup --compress \ --target-dir=/mariadb/backup/full_backup --user=username \ --password=pass --backup --compress --parallel=4
6. In Replica, create a directory where we will place the backup from Master
mkdir -p /mariadb/backup/rep
7. In Master, use scp to transfer the entire backup image to the replica.
# Go to the directory where you placed the backup cd /mariadb/backup scp -rp full_backup mysql@192.168.1.131:/mariadb/backup/rep
8. In Replica, Stop DB Service
sudo service mysql stop # Verify that DB service has been stopped ps -ef| grep mysqld
9. In Replica, Remove all contents in Data Directory.
mkdir -p /mariadb/data/old_data mv /mariadb/data/* /mariadb/data/old_data/ rm -rf /mariadb/data/old_data/ # Ensure that the data directory is empty cd /mariadb/data ls -la
10. In Replica, copy the backup image to data directory.
cp -rp /mariadb/backup/rep/full_backup /mariadb/data
11. In Replica, decompress and prepare backup image.
MEMORY=`grep -w innodb_buffer_pool_size /mariadb/bin/etc/my.cnf | cut -d'=' -f2` mariabackup --decompress --parallel=4 --remove-original --use-memory=$MEMORY --target-dir=full_backup
12. In Replica, prepare backup.
MARIADB_VERSION=`rpm -qa | grep MariaDB-server | grep -v debuginfo | cut -d'-' -f3 | cut -d'.' -f2` if [ $MARIADB_VERSION -le 3 ] ; then mariabackup --prepare --apply-log-only --use-memory=$MEMORY --target-dir=full_backup; fi mariabackup --prepare --use-memory=$MEMORY --target-dir=full_backup
13. In Replica, cleanup data directory, and move all files from full_backup to data directory.
ls -1v /mariadb/data | grep -v $FULL | xargs rm -rf mv /mariadb/data/$FULL/* /mariadb/data
14. In Replica, Delete the full_backup directory.
cd /mariadb/data rm -rf /mariadb/data/full_backup
15. In Replica, Rotate error logs.
cd /mariadb/bin/logs/db_logs mv error_mariadb.log error_mariadb_`date +%Y%m%d_%H%M%S`.log touch error_mariadb.log
16. In Replica, Start DB Service.
sudo service mysql start # Check if there are any errors cat /mariadb/bin/logs/db_logs/error_mariadb.log
17. In Replica, Check GTID. Take note of the GTID, because we need to use it for the next step.
cat /mariadb/data/xtrabackup_info | grep -i GTID
18. In Replica, Login in to the DB, and set the global gtid_slave_pos.
stop slave; reset slave; reset slave all; reset master; set global gtid_slave_pos='above_GTID_number_from_step18';
19. In Replica, still logged in to the DB, execute the change master to command.
change master to master_host='10.92.146.102', master_port=6603, master_user='repl', master_password='P@$$w0rd', master_connect_retry=10, master_use_gtid=slave_pos;
20. In Replica, set enable read_only.
set global read_only=1;
21. Start slave.
start slave;
22. Check replication status.
show slave status\G
Cheers!
In this blog post, I’m gonna highlight the differences in GTID replication between MySQL and MariaDB
I will not go thru step-by-step setup, because they are many resources out there that do.
The main reason to use GTID is that it makes it easier to track and compare replicated transaction between master and replica; hence, allowing simpler failover and recovery.
Here are the differences between MySQL and MariaDB
1. Composition of GTID
In MariaDB, it is composed of three separated dashed numbers like x-y-z
In MySQL, there are 2 parts:
2. Enabling GTIDs
To enable GTID, we have to set the following parameters in my.cnf
In MySQL:
gtid-mode=ON enforce-gtid-consistency
In MariaDB:
gtid_strict_mode=1
enforce_gtid_consistency does not exist in MariaDB
3. CHANGE MASTER TO statement
In order for the Replica to identify its Master (data source), and to use GTID-based auto-positioning, we need execute the CHANGE MASTER TO statement. In MySQL, we use the MASTER_AUTO_POSITION option to tell the replica that transactions will be identified by GTIDs.
Example In MySQL:
change master to master_host = '192.168.1.120', master_port=3306, master_user = 'repl', master_password = 'password', master_auto_position=1;
In MariaDB, A slave is configured to use GTID by CHANGE MASTER TO master_use_gtid=slave_pos. The replication will start at the position of the last GTID replicated to slave when the slave connects to the master. Refer to the official documentation for more info https://mariadb.com/kb/en/gtid/
Example In MariaDB:
change master to master_host='192.168.1.120', master_port=3306, master_user='repl', master_password='password', master_use_gtid=slave_pos;
Cheers!