Tidb 分布式关系型数据库

前言

这是一个很消耗性能的数据库, 但是投入和实际性能成正比, 真正的力大砖飞, 据说达到美团规模只要1000+个节点。这也是我见过最简单部署集群的产品, 稳。高度兼容mysql, 但是有些字段命名不好会和他自带的关键字冲突。索引、分库分表等全自动优化, 当然自己指定能避免预热和不准确问题。

文档地址 https://docs.pingcap.com/zh/tidb/stable

tidb

官方推荐最小配置

实例	个数	物理机配置	IP	配置	注释
TiDB	2	16 VCore, 32 GiB RAM, 100 GiB storage	10.0.1.1, 10.0.1.2	默认端口, 全局目录配置	处理SQL请求的入口节点
PD	3	4 VCore, 8 GiB RAM, 100 GiB storage	10.0.1.4, 10.0.1.5, 10.0.1.6	默认端口, 全局目录配置	集群元数据管理和调度
TiKV	3	16 VCore, 32 GiB RAM, 2 TiB (NVMe SSD) storage	10.0.1.7, 10.0.1.8, 10.0.1.9	默认端口, 全局目录配置	分布式存储数据节点
Monitoring & Grafana	1	4 VCore, 8 GiB RAM, 500 GiB (SSD) storage	10.0.1.10	默认端口, 全局目录配置	性能监控和可视化看板(日志量庞大)

官方推荐优化

(1) 永久关闭 swap (虚拟内存)

echo "vm.swappiness = 0">> /etc/sysctl.conf
swapoff -a && swapon -a
sysctl -p

(2) 关闭防火墙服务, 使用阿里云等云服务可以关闭

sudo systemctl stop firewalld.service
sudo systemctl disable firewalld.service

(3) 优化Linux参数

echo "fs.file-max = 1000000">> /etc/sysctl.conf
echo "net.core.somaxconn = 32768">> /etc/sysctl.conf
echo "vm.overcommit_memory = 1">> /etc/sysctl.conf
sysctl -p

cat << EOF >>/etc/security/limits.conf
hjh_tidb           soft    nofile          1000000
hjh_tidb           hard    nofile          1000000
hjh_tidb           soft    stack          32768
hjh_tidb           hard    stack          32768
EOF

安装Tidb

(1) 下载并安装 TiUP。

curl --proto '=https' --tlsv1.2 -sSf https://tiup-mirrors.pingcap.com/install.sh | sh

(2) TiUP 安装完成后会提示对应 Shell profile 文件的绝对路径。在执行以下 source 命令前，需要将 ${your_shell_profile} 修改为 Shell profile 文件的实际位置。

即上面命令提示的 Shell profile 文件位置

[root@VM-0-2-opencloudos data]# curl --proto '=https' --tlsv1.2 -sSf https://tiup-mirrors.pingcap.com/install.sh | sh
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 5150k  100 5150k    0     0  4394k      0  0:00:01  0:00:01 --:--:-- 4390k
Successfully set mirror to https://tiup-mirrors.pingcap.com
Detected shell: bash
Shell profile:  /root/.bash_profile
/root/.bash_profile has been modified to add tiup to PATH
open a new terminal or source /root/.bash_profile to use it
Installed path: /root/.tiup/bin/tiup
===============================================
Have a try:     tiup playground
===============================================

Shell profile: /root/.bash_profile

source ${your_shell_profile}

(3) 安装 TiUP 的 cluster 组件

tiup cluster

(4) 编辑Tidb配置文件: 选择一个文件夹创建并写入tidb.xml配置(这里以官方最小拓扑为例)

yml

# # Global variables are applied to all deployments and used as the default value of
# # the deployments if a specific deployment value is missing.
global:
  user: "hjh_tidb"
  ssh_port: 22
  deploy_dir: "/tidb-deploy"
  data_dir: "/tidb-data"

# # Monitored variables are applied to all the machines.
monitored:
  node_exporter_port: 9100
  blackbox_exporter_port: 9115
  # deploy_dir: "/tidb-deploy/monitored-9100"
  # data_dir: "/tidb-data/monitored-9100"
  # log_dir: "/tidb-deploy/monitored-9100/log"

# # Server configs are used to specify the runtime configuration of TiDB components.
# # All configuration items can be found in TiDB docs:
# # - TiDB: https://docs.pingcap.com/zh/tidb/stable/tidb-configuration-file
# # - TiKV: https://docs.pingcap.com/zh/tidb/stable/tikv-configuration-file
# # - PD: https://docs.pingcap.com/zh/tidb/stable/pd-configuration-file
# # All configuration items use points to represent the hierarchy, e.g:
# #   readpool.storage.use-unified-pool
# #
# # You can overwrite this configuration via the instance-level `config` field.

server_configs:
  tidb:
  log.slow-threshold: 300
  binlog.enable: false
  binlog.ignore-error: false
  tikv:
  # server.grpc-concurrency: 4
  # raftstore.apply-pool-size: 2
  # raftstore.store-pool-size: 2
  # rocksdb.max-sub-compactions: 1
  # storage.block-cache.capacity: "16GB"
  # readpool.unified.max-thread-count: 12
  readpool.storage.use-unified-pool: false
  readpool.coprocessor.use-unified-pool: true
  pd:
  schedule.leader-schedule-limit: 4
  schedule.region-schedule-limit: 2048
  schedule.replica-schedule-limit: 64

pd_servers:
  - host: 10.0.1.4
  # ssh_port: 22
  # name: "pd-1"
  # client_port: 2379
  # peer_port: 2380
  # deploy_dir: "/tidb-deploy/pd-2379"
  # data_dir: "/tidb-data/pd-2379"
  # log_dir: "/tidb-deploy/pd-2379/log"
  # numa_node: "0,1"
  # # The following configs are used to overwrite the `server_configs.pd` values.
  # config:
  #   schedule.max-merge-region-size: 20
  #   schedule.max-merge-region-keys: 200000
  - host: 10.0.1.5
  - host: 10.0.1.6

tidb_servers:
  - host: 10.0.1.1
  # ssh_port: 22
  # port: 4000
  # status_port: 10080
  # deploy_dir: "/tidb-deploy/tidb-4000"
  # log_dir: "/tidb-deploy/tidb-4000/log"
  # numa_node: "0,1"
  # # The following configs are used to overwrite the `server_configs.tidb` values.
  # config:
  #   log.slow-query-file: tidb-slow-overwrited.log
  - host: 10.0.1.2

tikv_servers:
  - host: 10.0.1.7
  # ssh_port: 22
  # port: 20160
  # status_port: 20180
  # deploy_dir: "/tidb-deploy/tikv-20160"
  # data_dir: "/tidb-data/tikv-20160"
  # log_dir: "/tidb-deploy/tikv-20160/log"
  # numa_node: "0,1"
  # # The following configs are used to overwrite the `server_configs.tikv` values.
  # config:
  #   server.grpc-concurrency: 4
  #   server.labels: { zone: "zone1", dc: "dc1", host: "host1" }
  - host: 10.0.1.8
  - host: 10.0.1.9

monitoring_servers:
  - host: 10.0.1.10
  # ssh_port: 22
  # port: 9090
  # deploy_dir: "/tidb-deploy/prometheus-8249"
  # data_dir: "/tidb-data/prometheus-8249"
  # log_dir: "/tidb-deploy/prometheus-8249/log"

grafana_servers:
  - host: 10.0.1.10
  # port: 3000
  # deploy_dir: /tidb-deploy/grafana-3000

alertmanager_servers:
  - host: 10.0.1.10
  # ssh_port: 22
  # web_port: 9093
  # cluster_port: 9094
  # deploy_dir: "/tidb-deploy/alertmanager-9093"
  # data_dir: "/tidb-data/alertmanager-9093"
  # log_dir: "/tidb-deploy/alertmanager-9093/log"

(5) 创建并启动集群

tiup cluster deploy hjh v8.2.0 ./hjh_tidb.yaml --user root -p

根据命令提示执行启动命令

tiup cluster start hjh --init

然后会让你输入服务器的密码, 这里十台服务器密码最好一样, 这样做当然是为了让他自动搭建! 最后一直等待他完成就好了, 喝茶~ 执行结束会返回一个随机密码(上面那个步骤也可以指定密码), root + 返回的密码连接数据库。 java应用连接tidb api即10.0.1.1、10.0.1.2, 其他他搞定(自动优化), 那里不够加那里。

执行会返回如下信息

Started cluster `hjh` successfully
The root password of TiDB database has been changed.
The new password is: '这里显示的是数据库的密码, 如果启动时没有输入密码, 那么就是这个密码'.
Copy and record it to somewhere safe, it is only displayed once, and will not be stored.
The generated password can NOT be get and shown again.

(6) 其他操作查看集群状态

tiup cluster display hjh

在线编辑xml

tiup cluster edit-config hjh

重启

tiup cluster reload hjh

扩容缩容就是在线编辑xml修改ip, 然后重启!不需要设置分库的数据区间!非常简单!

禁用 only_full_group_by SQL 模式

sql

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

SET GLOBAL sql_mode=(SELECT REPLACE(@@global.sql_mode,'ONLY_FULL_GROUP_BY',''));

Tidb 分布式关系型数据库 ​

前言 ​

官方推荐最小配置 ​

官方推荐优化 ​

安装Tidb ​

Tidb 分布式关系型数据库

前言

官方推荐最小配置

官方推荐优化

安装Tidb