hhdb数据库介绍(9-26)

计算节点参数说明

masterSourceInitWaitTimeout

参数说明：

参数作用：
启动时，主存储节点在首次初始化失败后，会一直重连；若存在备存储节点且超过主存储节点初始化超时时间，则会切换到可用的备存储节点，若该节点所有存储节点都初始化失败，则整个节点不可用。如果数据节点初始化失败且无可用逻辑库，或数据节点下无存储节点，则计算节点无法启动。

2018-05-28 18:07:29.719 [WARN] [INIT] [main] r(-1) -- failed in connecting datasource:[id:182,nodeId:11 192.168.220.101:3306/db01 status:1,charset:utf8], exception:...省略...
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the sever.
2018-05-28 18:07:31.719 [INFO] [INIT] [main] b(-1) -- try reinit datasource:[id:182,nodeId:11 192.168.220.101:3306/db01 status:1,charset:utf8]

引起存储节点超时的原因有：超出系统或者数据库连接限制、存储节点用户密码认证失败、网络延迟过大等。

maxAllowedPacket

参数说明：

参数作用：
控制前端连接发送的包大小。默认64M，当发送SQL语句的大小超过默认值64M时，计算节点会给出提示（Get a packet bigger than ‘max_allowed_packet’）。

ERROR 1153 (HY000): Get a packet bigger than 'max allowed packet'

同时，show variables能够显示配置的值。

mysql> show variables like '%allowed%;
+--------------------+----------+
| variable_name      | value    |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+

maxCalciteScanSize

参数说明：

参数设置：

<property name="maxCalciteScanSize">1000000</property><!-- 由Calcite执行的SQL中单表最大扫描行数，0为不限制，默认1000000(maximum number of rows scanned in a single table in SQL executed by Calcite, value 0 means unlimited, default value is 1000000)-->

参数作用：
此参数用于控制Calcite逻辑单表抽取行数，当Calcite逻辑单表抽取行数大于maxCalciteScanSize参数设置数值时，会禁止SQL执行，并在会话中提示“unsupported statement”，与此同时，在hotdb.log日志内提示

Calcite scanned too many rows(XX> maxCalciteScanSize:XX) for table [XX] with where [] for connection

参数示例：

在未添加maxCalciteScanSize参数时，使用Calcite算法的SQL可以正常执行并返回结果：

root@127.0.0.1:hotdb 8.0.32 02:48:28> select avg(p3.id+p2.id) from zjj_sel_p2 as p2 left join zjj_sel_p3 as p3 on p2.id=p3.id;
+------------------+
| avg(p3.id+p2.id) |
+------------------+
|              532 |
+------------------+
1 row in set (4.24 sec)

与此同时，在hotdb.log日志内可见相关提示：

2023-03-31 14:48:48.087 [INFO] [CALCITE] [Labor-3] cn.hotpu.hotdb.calcite.CalciteClient() - [SELECT AVG(p3.id + p2.id) FROM zjj_sel_p2 AS `p2` LEFT JOIN zjj_sel_p3 AS `p3` ON p2.id = p3.id] executed in calcite successfully for [thread=Labor-3,id=1,user=root,host=127.0.0.1,port=3323,localport=42412,schema=HOTDB,isEtl=false,closed=false,[{_pid:7286}, {_platform:x86_64}, {_os:Linux}, {_client_name:libmysql}, {os_user:root}, {_client_version:8.0.32}, {program_name:mysql}]]

在添加maxCalciteScanSize参数并设置参数值为3后，在管理端口执行reload或重启计算节点，使用Calcite算法的SQL若Calcite逻辑单表抽取行数大于maxCalciteScanSize参数值时，则会禁止该SQL执行：

root@127.0.0.1:hotdb 8.0.32 02:51:55> select avg(p3.id+p2.id) from zjj_sel_p2 as p2 left join zjj_sel_p3 as p3 on p2.id=p3.id;
ERROR 10010 (HY000): unsupported statement for multi-table join in single avg expression

与此同时，在hotdb.log日志内可见相关提示：

2023-03-31 14:52:00.749 [INFO] [INNER] [Labor-4] cn.hotpu.hotdb.calcite.CalciteEnumerator() - Calcite scanned too many rows(20> maxCalciteScanSize:3) for table [HOTDB.ZJJ_SEL_P3] with where [] for connection:[thread=Labor-4,id=1,user=root,host=127.0.0.1,port=3323,localport=43012,schema=HOTDB,isEtl=false,closed=false,[{_pid:7286}, {_platform:x86_64}, {_os:Linux}, {_client_name:libmysql}, {os_user:root}, {_client_version:8.0.32}, {program_name:mysql}]], current SQL:select avg(p3.id+p2.id) from zjj_sel_p2 as p2 left join zjj_sel_p3 as p3 on p2.id=p3.id, session:AutoCommitTransactionSession in [thread=Labor-4,id=1,user=root,host=127.0.0.1,port=3323,localport=43012,schema=HOTDB,isEtl=false,closed=false,[{_pid:7286}, {_platform:x86_64}, {_os:Linux}, {_client_name:libmysql}, {os_user:root}, {_client_version:8.0.32}, {program_name:mysql}]]

maxConnections & maxUserConnections

参数说明：

参数作用：
计算节点支持限制前端连接数功能，出现访问过载时可辅助限制流量。

maxConnections是前端连接的最大连接数，是计算节点所允许的同时前端数据库连接数的上限。用户可以根据实际需要设置maxConnections，适当调整该值，不建议盲目提高设值。

maxUserConnections是同一个账号能够同时连接到计算节点的最大连接数。用户前端最大连接数可以为空，为空时默认给0即不限制用户连接数，此时以前端最大连接数为准。

当连接数大于所设置的值时，创建前端连接会有如下提示:

root> mysql -uzy -pzy -h127.0.0.1 -P9993

Warning: Using a password on the command line interface can be insecure.
ERROR 1040 (HY000): too many connections

可以通过在服务端口set修改maxConnections和maxUserConnections的值，参数为GLOBAL级别：

mysql> set global max_connections = 5000;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%max_connections%;
+-----------------+-------+
| variable_name   | value |
+-----------------+-------+
| max_connections | 5000  |
+-----------------+-------+

mysql> set global max_user_connections = 1000;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%max_user_connections%;
+----------------------+-------+
| variable_name        | value |
+----------------------+-------+
| max_user_connections | 1000  |
+----------------------+-------+

maxIdleTransactionTimeout

参数说明：

参数设置：

<property name="maxIdleTransactionTimeout">864000000</property>

maxIdleTransactionTimeout参数默认值为86400000毫秒，即24小时，表示事务内最后一次SQL完成后超过24小时未提交事务，则判定为超时事务，计算节点在hotdb.log中以[INFO] [WATCHDOG] WatchDogLongTransactionCheckHandler标签记录连接IP、端口、用户名、逻辑库、lastsql、是否autocommit、后端连接的innodb_trx等信息，并关闭连接，自动回滚事务。

参数仅在enableWatchdog=true时生效。Watchdog中maxIdleTransactionTimeout每10分钟检测一次，在Watchdog对maxIdleTransactionTimeout的检测中判断连接的事务空闲时间，如果超出设定的阈值，则关闭连接；故实际的事务空闲时间不等于设定的阈值。

例如，事务空闲时间超出设定的阈值，将关闭连接，此时查看日志：

2019-07-01 18:09:24.528 [INFO] [WATCHDOG] [$NIOREACTOR-20-RW] cn.hotpu.hotdb.mysql.nio.handler.WatchDogLongTransactionCheckHandler(123) - Session [thread=Thread-13,id=1,user=ztm,host=127.0.0.1,port=3323,localport=46138,schema=PM] has not been queryed for 593s. executed IUDs:[UPDATE customer_auto_1 SET city = 'xxxx' WHERE id = 1]. binded connection:[MySQLConnection [node=2, id=59, threadId=14921, state=borrowed, closed=false, autocommit=false, host=10.10.0.202, port=3307, database=db_test251, localPort=52736, isClose:false, toBeClose:false] lastSQL:SET autocommit=0;UPDATE customer_auto_1 SET city = 'xxxx' WHERE id = 1]. innodb_trx:[(ds:2 trx_id:3435056156 trx_state:RUNNING trx_started:2019-07-01 17:59:33 trx_requested_lock_id:NULL trx_wait_started:NULL trx_weight:3 trx_mysql_thread_id:14921 trx_query:NULL trx_operation_state:NULL trx_tables_in_use:0 trx_tables_locked:1 trx_lock_structs:2 trx_lock_memory_bytes:1136 trx_rows_locked:1 trx_rows_modified:1 trx_concurrency_tickets:0 trx_isolation_level:REPEATABLE READ trx_unique_checks:1 trx_foreign_key_checks:1 trx_last_foreign_key_error:NULL trx_adaptive_hash_latched:0 trx_adaptive_hash_timeout:0 trx_is_read_only:0 trx_autocommit_non_locking:0 )]. we will close this session now.

maxJoinSize

参数说明：

参数作用：
JOIN中间结果集允许执行的最大行数。中间结果集的计算方法：当SQL语句中没有条件时，计算笛卡尔积；当SQL语句中有条件时，计算符合join条件的行数。

当JOIN中间结果集大于设置的行数时，会提示如下信息：

mysql> select * from customer_auto_1 a join customer_auto_3 b on a.postcode=b.postcode;

ERROR 1104 (HY000): The SELECT would examine more than MAX_JOIN_SIZE rows; check your maxJoinSize in server.xml

可通过set session max_join_size修改当前会话参数值，使JOIN中间结果集在1~ 2124000000之间：

mysql> show variables like '%max_join_size%;
+---------------+-------+
| variable_name | value |
+---------------+-------+
| max_join_size | 5000  |
+---------------+-------+
 
mysql> set global max_user_connections = 1000;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%max_user_connections%;
+----------------------+------------+
| variable_name        | value      |
+----------------------+------------+
| max_user_connections | 2124000000 |
+----------------------+------------+

mysql> set session max_join_size=1;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like '%max_user_connections%;
+----------------------+-------+
| variable_name        | value |
+----------------------+-------+
| max_user_connections | 1     |
+----------------------+-------+

mysql> select * from bn_a_jwy a join bn_c_jwy b on a.a=b.a where a.a='d';
ERROR 1104 (HY000): The SELECT would examine more than MAX_JOIN_SIZE rows; check your maxJoinSize in server.xml

maxLatencyForRWSplit

参数说明：

参数作用：
开启读写分离后，主从延迟小于设置的延迟时间时，读从库:

mysql> select * from cd;
+----+-------+
| id | name  |
+----+-------+
| 1  | slave |
| 2  | slave |
| 3  | slave |
| 4  | slave |
| 5  | slave |
+----+-------+
5 rows in set (0.00 sec)

开启读写分离后，当可读从库的延迟超过设置的时间后，会读主库:

mysql> select * from cd;
+----+--------+
| id | name   |
+----+--------+
| 1  | master |
| 2  | master |
| 3  | master |
| 4  | master |
| 5  | master |
+----+--------+
5 rows in set (0.00 sec)

maxNotInSubquery

参数说明：

参数设置：
server.xml中maxNotInSubquery参数配置 如下配置：

<property name="maxNotInSubquery">20000</property><!--子查询中最大not in个数 -->

参数作用：
控制子查询中最大NOT IN个数，默认20000，当执行的SQL语句中NOT IN子查询为分片表且带有聚合函数，此时去重后的NOT IN个数超过默认值20000时，计算节点会限制该SQL执行，并给出ERROR提示

(ERROR 1104 (HY000): The sub SELECT would examine more than maxNotInSubquery rows; check your maxNotInSubquery in server.xml）

例如：（为方便测试，设置maxNotInSubquery 为10）

mysql> use pm
Database changed

mysql> show tables;
+------------------+
| Tables_in_PM     |
+------------------+
| customer_quan_2  |
| customer_route_1 |
| customer_route_2 |
+------------------+
3 rows in set (0.00 sec)

mysql> select * from customer_route_2 a where a.postcode not in (select postcode from customer_route_1 b where b.id > 205119 limit 20);
ERROR 1104 (HY000): The sub SELECT would examine more than maxNotInSubquery rows; check your maxNotInSubquery in server.xml

日志中会以[INFO] [SQL]标签记录对应信息

2019-10-08 14:33:41.725 [INFO] [SQL] [$NIOExecutor-3-2] cn.hotpu.hotdb.j.h(2626) - unsupported subquery:[thread=$NIOExecutor-3-2,id=152197,user=ztm,host=127.0.0.1,port=3323,localport=49458,schema=PM] AutoCommitTransactionSession in [thread=$NIOExecutor-3-2,id=152197,user=ztm,host=127.0.0.1,port=3323,localport=49458,schema=PM], sql:select * from customer_route_2 a where a.postcode not in (select postcode from customer_route_1 b where b.id > 205119 limit 20), error code:1104, error msg:The sub SELECT would examine more than maxNotInSubquery rows; check your maxNotInSubquery in server.xml

同时，在日志和3325端口show @@systemconfig能够查看配置的值，该参数在修改后可reload生效。

mysql> show @@systemconfig;

config | {[enableFlowControl](#enableFlowControl):"true",[recordSql](#recordSql):"false",[defaultMaxLimit](#defaultMaxLimit):"10000","bakPassword":"hotdb_config","bakUrl":"jdbc:mysql://192.168.220.138:3306/hotdb_config_249ha","managerPort":"3325","heartbeatPeriod":"2",[cryptMandatory](#cryptMandatory):"false","password":"hotdb_config",[enableCursor](#enableCursor):"false","username":"hotdb_config",[enableXA](#enableXA):"false",[errorsPermittedInTransaction](#errorsPermittedInTransaction):"true",[strategyForRWSplit](#strategyForRWSplit):"0",[enableWatchdog](#enableWatchdog):"false","haNodeHost":"192.168.220.139:3325",[maxJoinSize](#maxJoinSize):"9148M",[maxNotInSubquery](#maxNotInSubquery):"10",[pingLogCleanPeriodUnit](#pingLogCleanPeriodUnit):"0",[clientFoundRows](#clientFoundRows):"false",[joinCacheSize](#joinCacheSize):"236","enableHeartbeat":"true","url":"jdbc:mysql://192.168.220.138:3306/hotdb_config_249ha",[parkPeriod](#parkPeriod):"100000",[maxSqlRecordLength](#maxSqlRecordLength):"4000",[joinBatchSize](#joinBatchSize):"46000",[enableSubquery](#enableSubquery):"true","heartbeatTimeoutMs":"500",[pingPeriod](#pingPeriod):"300",[joinLoopSize](#joinLoopSize):"18500","VIP":"192.168.220.171",[joinable](#joinable):"true","maxUserConnections":"4900",[pingLogCleanPeriod](#pingLogCleanPeriod):"1",[dataNodeIdleCheckPeriod](#dataNodeIdleCheckPeriod):"120",[deadlockCheckPeriod](#deadlockCheckPeriod):"3000",[sqlTimeout](#sqlTimeout):"3600","bakUsername":"hotdb_config","enableLatencyCheck":"true",[waitSyncFinishAtStartup](#waitSyncFinishAtStartup):"true","checkVIPPeriod":"500",[statisticsUpdatePeriod](#statisticsUpdatePeriod):"0",[usingAIO](#usingAIO):"0",[showAllAffectedRowsInGlobalTable](#showAllAffectedRowsInGlobalTable):"false",[maxLatencyForRWSplit](#maxLatencyForRWSplit):"1000","maxConnections":"5000",[enableSleep](#enableSleep):"false",[waitForSlaveInFailover](#waitForSlaveInFailover):"true",[autoIncrement](#autoIncrement):"true",[processorExecutor](#processorExecutor):"4",[highCostSqlConcurrency](#highCostSqlConcurrency):"400","latencyCheckPeriod":"500","processors":"16",[weightForSlaveRWSplit](#weightForSlaveRWSplit):"50","haState":"master",[readOnly](#readOnly):"false",[timerExecutor](#timerExecutor):"4","serverPort":"3323",[frontWriteBlockTimeout](#frontWriteBlockTimeout):"10000",[switchoverTimeoutForTrans](#switchoverTimeoutForTrans):"3000"}
1 row in set (0.01 sec)

maxReconnectConfigDBTimes

参数说明：

参数设置：
server.xml中maxReconnectConfigDBTimes参数如下配置：

<property name=" maxReconnectConfigDBTimes ">3</property><!-- 最大重试连接配置库次数 -->

参数作用：
防止计算节点启动、HA切换或reload时配置库连接耗时过长，增加配置库重连次数。超过最大重试连接次数（重连耗时默认是3*2s），会自动切换到从配置库连接。

maxSqlRecordLength

参数说明：

参数作用：
该参数是指操作日志智能分析中的SQL纪录时的最大长度。

执行的SQL语句超过设置的长度后，会自动截取，用省略号…代替，如下图所示：

maxFlowControl

参数说明：

参数设置：
server.xml的maxFlowControl参数设置为256

<property name="maxFlowControl">256</property><!-- 各数据源流控限制上限[32,1024](Maximum concurrency limit(between 32 and 1024) for flow control in each datasource) -->

参数作用：
在开启存储节点限流时，控制后端各存储节点单次最大并发个数，以免因流量过大而导致存储节点服务异常

maxLatencyForReadOnly

参数说明：

参数设置：
server.xml中maxLatencyForReadOnly参数配置：

<property name="maxLatencyForReadOnly">4000</property><!-- 只读实例从库最大延迟(ms)(Maximum delay of readable Standby Slave in instance readonly ) -->

参数作用
控制开启实例级别只读后，可读从库允许的最大延迟时间：单机房内复制延迟超过该参数设置的值开始读其他可用从机或主机（先读其他可用从机，无可用从机时读主机），灾备模式下的灾备机房，复制延迟超过该参数设置的值开始拒绝读取。

如：主从数据节点下，maxLatencyForReadOnly设置值为4000，从库的复制延迟大于4000ms，此时只读计算节点的读操作发往主库；若小于4000ms则读操作发往优先级最高的从库

root@192.168.210.135:(none) 8.0.30 05:00:06> show @@latency;
+------+---------------------------+---------------------------+---------+
| dn | info        | backup_info     | latency |
+------+---------------------------+---------------------------+---------+
| 20 | 192.168.210.136:3311/db01| 192.168.210.54:3308/db01 | 5011 ms |
+------+---------------------------+---------------------------+---------+
1 rows in set (0.02 sec)

灾备模式下灾备机房的只读计算节点超出maxLatencyForReadOnly设置的值时拒绝读取

root@192.168.210.136:hotdb 8.0.30 06:44:09> select count(*) from test75;
ERROR 10185 (HY000): Datasource's lantecy is too big

maxCachedTrxIud

参数说明：

参数设置：

<property name="maxCachedTrxIud">100</property><!-- 缓存供日志输出的事务中执行的IUD语句的最大数量，默认100条(Maximum number of cached IUD statements in transaction for log, default 100.) -->

参数作用：
缓存需要输出日志的SQL场景，可通过该参数设置需要输出SQL的条数，方便问题排查定位。目前主要的场景有：事务异常、死锁检测、watchdog等。

设置maxCachedTrxIud的值为2，测试死锁场景，计算节点输出的死锁日志中，只输出了2条SQL

maxTempFileSize

参数说明：

参数设置：

<property name="maxTempFileSize">100G</property><!-- 连接堆外缓存数据的最大临时文件大小，默认为100G(Max temporary file size for connection to cache data, default is 100G) -->

参数作用：
连接堆外缓存数据的最大临时文件大小，默认为100G。单位只能以G、M、K结尾。

maxMinorityElectionTimeoutMills

参数说明：

参数设置：

<property name="maxMinorityElectionTimeoutMills">0</property><!--控制特定条件下计算节点自动下线不再发起选举的机制(ms) -->

参数作用：
econdary计算节点发现primary心跳超时触发选举，如果这个计算节点收到的集群成员信息始终为少数节点，即小于((集群成员总数/2)+1)，且在该参数超时前都未选举成功，则该计算节点自动下线，不再发起选举，默认0，不开启此机制。

例：A机房和B机房构成双活集群，A机房3个计算节点，B机房2个计算节点，primary节点在A机房。若AB机房之间网络故障，B中心的计算节点会不停发起选举，此参数合理配置可使B中心计算节点及时下线。

maxNumberForSelect

参数说明：

参数设置：

<property name="maxNumberForSelect">0</property><!-- 控制SQL查询语句返回的最大结果条数（为0代表不限制,最>大：1000）(The max number limits of results returned by SQL query, 0 for no limit, max: 1000) -->

参数作用：
控制SQL查询语句返回的最大结果条数（为0代表不限制）。

当maxNumberForSelect设置为5时，表示对于SQL查询语句返回的最大结果条数为5条。举例说明如下，对于一张有10行的数据的hotdb_t6表，查出来的结果仅有5行，并会给出warning的提示信息：

root@127.0.0.1(hotdb) 8.0.30 05:00:06> select count(*) from hotdb_t6;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.03 sec)

root@127.0.0.1(hotdb)  8.0.30 05:00:20> select * from hotdb_t6;
+---+----+-----+------+
| a | aa | aaa | aaaa |
+---+----+-----+------+
| 4 | 44 | d   | dd   |
| 5 | 55 | e   | ee   |
| 6 | 66 | a   | aa   |
| 7 | 77 | b   | bb   |
| 1 | 11 | a   | aa   |
+---+----+-----+------+
5 rows in set, 1 warning (0.01 sec)

Warning (Code 10268): The query result exceeds the maxNumberForSelect parameter value and includes data that is not fully displayed

当maxNumberForSelect设置为0时，表示不对SQL查询语句返回的最大结果条数做任何限制。举例说明如下，对于一张有10行的数据的hotdb_t6表，查出来的结果会显示所有行数：

root@127.0.0.1(hotdb) 8.0.30 05:0030> select count(*) from hotdb_t6;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.03 sec)

root@127.0.0.1(hotdb)  8.0.30 05:00:40> select * from hotdb_t6;
+----+------+-----+------+
| a  | aa   | aaa | aaaa |
+----+------+-----+------+
|  4 |   44 | d   | dd   |
|  5 |   55 | e   | ee   |
|  6 |   66 | a   | aa   |
|  7 |   77 | b   | bb   |
|  1 |   11 | a   | aa   |
|  2 |   22 | b   | bb   |
|  3 |   33 | c   | cc   |
|  8 |   88 | c   | cc   |
|  9 |   99 | d   | dd   |
| 10 | 1010 | f   | ff   |
+----+------+-----+------+
10 rows in set (0.01 sec)