1 . VoltDB 分享 第二讲 运维与最佳实践 杜华兴 1 17-May-20 

2 . 基础概念 2 17-May-20 

3 .配置文件 • Deployment.xml <deployment> <cluster hostcount="2" sitesperhost=”8"> </cluster> </deployment> • 两种生效方式： 初始化时：voltdb init --config=filepath/deployment.xml 运行时：voltadmin update filepath/deployment.xml Security settings Snapshot Importer or Exporter Heartbeat timeout DR replication settings Resource limit • 示例 examples/HOWTOs/deployment-file-examples 3 

4 .VoltDB分区策略 • 数据自动分布，请求自动转发，对客户端透明。 • 客户端不用连接多个节点，只用连接一个节点，即可访问整个集群。 <deployment> Queue Queue Queue <cluster hostcount=“3" core core core sitesperhost=”8"> </cluster> </deployment> 。。。。 。。。。 。。。。 4 

5 .VoltDB 集群高可用 • 分区冗余，容忍节点故障 • 备份数量可配置 kfactor • 故障转移自动化 • 复制是同步的 • 不影响吞吐量 <deployment> <cluster kfactor=“1" </cluster> </deployment> 5 

6 .VoltDB 集群高可用 • K-factor = # of server failures tolerated • K-factor + 1 = # of copies of data • Partitions = hosts * sitesperhost / (# of copies) 1 2 5 6 9 10 3 4 7 8 11 12 6 K-factor = 0 

7 .VoltDB 集群高可用 • K-factor = # of server failures tolerated • K-factor + 1 = # of copies of data • Partitions = hosts * sitesperhost / (# of copies) 1 2 5 6 3 4 3 4 1 2 5 6 7 K-factor = 1 

8 .VoltDB 集群高可用 • K-factor = # of server failures tolerated • K-factor + 1 = # of copies of data • Partitions = hosts * sitesperhost / (# of copies) 1 2 1 2 1 2 3 4 3 4 3 4 8 K-factor = 2 

9 .集群副本&分区 副本与分区的关系 • 原本和多个副本必须在不同的node上 • Unique partitions = (nodes * partitions/node) / (K + 1) （Unique partitions N+） 1 2 1 2 3 4 3 4 5 9 K-factor = 1 

10 .集群副本&分区数 1 2 5 6 3 4 3 4 1 2 5 6 siteprehost=4 K-factor = 1 1 2 1 2 1 2 3 3 3 10 K-factor = 2 

11 .集群副本&分区数 1 2 1 2 4 5 4 5 3 3 6 6 hostcount = 4 kfactor=1 11 

12 .集群扩容 voltdb start --add --host=ServerA • 在线扩容，保持原有分区关系不变，数据自动rebalance • 满足分区和副本关系 1 2 5 6 3 4 3 4 1 2 5 6 1 2 5 6 3 4 7 8 3 4 1 2 5 6 7 8 12 K-factor = 1 

13 .集群扩容 • 在线扩容，保持原有分区关系不变，数据自动rebalance 1 2 5 6 3 4 3 4 1 2 5 6 1 2 5 6 3 4 7 8 7 8 3 4 1 2 5 6 9 10 9 10 13 K-factor = 1 

14 .集群缩容 • voltadmin resize • 在线缩容，保持原有分区关系不变，数据自动rebalance 1 2 5 6 3 4 3 4 1 2 5 6 1 2 5 6 3 4 7 8 7 8 3 4 1 2 5 6 9 10 9 10 14 K-factor = 1 

15 .Rebalance <deployment> .. . <systemsettings> <elastic duration="15" throughput="1"/> </systemsettings> </deployment> 15 

16 .数据持久化-Snapshot 定时快照： <snapshot prefix="flightsave" frequency="30m" retain="3" /> <paths> <snapshots path="/etc/voltdb/autobackup/" /> </paths> 手工快照： $ voltadmin save --blocking /tmp/voltdb/backup "TestSnapshot" 使用系统工具管理快照 exec @SnapshotScan directory-path ; exec @SnapshotDelete String[] directory-paths, String[] Unique-IDs ; 16 

17 .数据持久化-Commandlog <commandlog enabled=“true” logsize=“1024” synchronous=”false" > <frequency time=“200” transactions="1000"/> </commandlog> Snapshots 记录数据 Command logs 记录命令 17 

18 .数据库持久化的建议 • 建议使用SSD 存储用于数据持久化 • 在读写速度较慢的硬件设备上使用异步commandlog • 将commandlog 和 commandlog snapshot分别写入两个独立的存储设备中 <paths> <commandlog path="/faskdisk/voltdblog/" /> <commandlogsnapshot path="/opt/voltdb/cmdsnaps/" /> </paths> • 如果使用异步commandlog方式，建议时间间隔100毫秒或更高 18 

19 .数据备份与恢复 $ # Issue once • 部署新环境 $ voltadmin pause $ voltadmin save --blocking /opt/archive/ mydb • 更新数据库实例 $ voltadmin shutdown <deployment> <cluster hostcount=“3" sitesperhost=”8“ kfactor=“2”> </cluster> </deployment> $ # Issue next two commands on all servers $ voltdb init --dir=~/mydb --config=deployment.xml --force $ voltdb start --dir=~/mydb --host=svr1,svr2,svr3 --count=3 --pause $ voltadmin restore /opt/archive mydb $ voltadmin resume • 异构产品数据迁移 voltadmin save --format=csv –table=tableA,tableB /date/backup mytables 19 

20 . Datebase Replication 20 17-May-20 

21 .主从模式 主集群 <dr id="1" role="master"/> <dr id="2" role="replica"> <connection 从集群 source="MasterSvrA,MasterSvrB"/> </dr> Cluster1 CREATE TABLE customers ( Binary Logs Snapshot Cluster2 Master 读写 Replica customerID INTEGER NOT NULL, 只读 firstname VARCHAR(128), lastname VARCHAR(128) ); DR TABLE customers; 21 

22 .主从模式 voltadmin promote cluster1 voltadmin dr reset --cluster=2 cluster2 读写 读写 只读 22 

23 .双活模式 <dr id="1" role="xdcr" > 集群1 <connection source=“XJserverX,XJserverY" /> </dr> <dr id=“2" role="xdcr" > Binary Snapshot <connection source=“BJserverX,BJserverY" /> Logs 集群2 </dr> </dr> cluster1 CREATE TABLE customers ( cluster2 读写 读写 customerID INTEGER NOT NULL, firstname VARCHAR(128), lastname VARCHAR(128) ); DR TABLE customers; 23 

24 .双活模式 Binary Snapshot Logs voltadmin dr reset --cluster=cluster2 cluster1 cluster2 读写 读写 24 

25 . 数据库安全 25 17-May-20 

26 .用户&角色 Role: CREATE ROLE dbuser; Assigning access: CREATE PROCEDURE ALLOW dbuser FROM CLASS MyProc1; User： <deployment> <security enabled="true"/> <users> <user name="operator" password="mech" roles="administrator, ops" /> <user name="developer" password="tech" roles="ops,dbuser" /> <user name="clientapp" password="xyzzy" roles=”dbuser" /> </users> </deployment> Encrypt： voltdb mask 26 

27 .用户&角色 预置权限 CREATE ROLE developer WITH sql, allproc; 权限 描述 DEFAULTPROCREAD Access to read-only default procedures(TABLE.select) Access to all default procedures (TABLE.select,TABLE.insert, DEFAULTPROC TABLE.delete, TABLE.update, andTABLE.upsert) SQLREAD Access to read-only ad hoc SQL queries (SELECT) SQL Access to all ad hoc SQL queries and defaultprocedures ALLPROC Access to all user-defined stored procedures Full access to all system procedures, all user-defined procedures, ADMIN as well as default procedures, ad hoc SQL, and DDL statements 预置角色 角色名称 描述 administrator Full access to all system procedures, all user-defined procedures, as well as default procedures, ad hoc SQL, and DDL statements User Access to all ad hoc SQL queries and defaultprocedures Access to all user-defined stored procedures 27 

28 .TLS/SSL 启用SSL通信 <ssl enabled="true" external="true" internal="true" dr="true"> <keystore path="/etc/ssl/local/mydb.keystore" password="mypasswd"/> <truststore path="/etc/ssl/local/mydb.truststore" password="mypasswd"/> </ssl> 为sqlcmd启用SSL传输 sqlcmd --ssl=localcert.txt --server=svr1 --ssl=cert.txt cat cert.txt trustStore=/etc/ssl/local/mydb.truststore trustStorePassword=mypasswd 28 

29 .数据导入导出 导出 导入 File Exporter Kafka Importer HTTP Exporter Kinesis Importer JDBC Exporter CSV Importer Kafka Exporter RabbitMQ Exporter Elasticsearch Exporter 29