1. Problems encountered in development
With the growth of business, the historical data table already has 400-500 million data. Previously, a table was created every month according to the scheduled task. When inserting, the data was inserted into the current table according to the current time. Cross month query is a problem when performing associated query. Just a new project tried to use Mycat to solve this problem. Two libraries were used to fragment the historical table.
2. Mycat integration steps
2.1 install Mysql on two servers (mysql-5.7.26 is used here)( https://note.youdao.com/s/KlTcX694)
2.2 install Mycat (version 1.6 is used here)
After decompression, enter the conf directory of Mycat for configuration:
2.2.1 schema.xml
<schema name="xinchai" checkSQLschema="false" sqlMaxLimit="100"> <!-- auto sharding by id (long) --> <!-- <table name="user_profile" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" /> --> <!-- global table is auto cloned to all defined data nodes ,so can join with any table whose sharding node is in the same data node --> <table name="history" primaryKey="id" autoIncrement="true" dataNode="distribute(dn1$0-6,dn2$0-6)" rule="sharding-by-date-customer" /> <table name="mycat_sequence" dataNode="dn10" autoIncrement="true" primaryKey="id"></table> </schema>
Note:
1. There are two key tables. One is the business table history and the sequence table mycat_sequence
2. dataNode is a data node. It is configured in < dataNode / >. I use two servers and configure 14 partition nodes, as follows:
<dataNode name="dn10" dataHost="datahost1" database="test" /> <dataNode name="dn11" dataHost="datahost1" database="test-db1" /> <dataNode name="dn12" dataHost="datahost1" database="test-db2" /> <dataNode name="dn13" dataHost="datahost1" database="test-db3" /> <dataNode name="dn14" dataHost="datahost1" database="test-db4" /> <dataNode name="dn15" dataHost="datahost1" database="test-db5" /> <dataNode name="dn16" dataHost="datahost1" database="test-db6" /> <dataNode name="dn20" dataHost="datahost2" database="test" /> <dataNode name="dn21" dataHost="datahost2" database="test-db1" /> <dataNode name="dn22" dataHost="datahost2" database="test-db2" /> <dataNode name="dn23" dataHost="datahost2" database="test-db3" /> <dataNode name="dn24" dataHost="datahost2" database="test-db4" /> <dataNode name="dn25" dataHost="datahost2" database="test-db5" /> <dataNode name="dn26" dataHost="datahost2" database="test-db6" />
3. If the global self incrementing ID is used and the ID is not written during insert addition, the following configuration is required (in the form of database sequence rather than local file)
(1) The table attribute autoIncrement="true" needs to be configured,
(2) Add a sequence for the history table in the sequence table (the sequence table and the add statement need to be executed in the node library instead of mycat)
INSERT INTO MYCAT_SEQUENCE(name,current_value,increment,remark) VALUES ('HISTORY', 1, 100,'Historical data usage');(3) In sequence_ db_ Add a sequence storage node in conf.properties
#sequence stored in datanode GLOBAL=dn10 COMPANY=dn10 CUSTOMER=dn10 ORDERS=dn10 HISTORY=dn10
(4) On the server Configuration in XML:
<property name="sequnceHandlerType">1</property>
Step 1: when using the local file method and ID self increment, exceptions often occur when inserting data in batches, and it is not easy to maintain. Therefore, it is recommended to configure the global auto increment sequence in the form of database:
fetch Param Values error.
Note: configure auto increment sequence statement:
-- 1,Create on the first database node MYCAT_SEQUENCE surface
DROP TABLE IF EXISTS MYCAT_SEQUENCE;
CREATE TABLE MYCAT_SEQUENCE (
name VARCHAR(50) NOT NULL,
current_value INT NOT NULL,
increment INT NOT NULL DEFAULT 1,
remark varchar(100),
PRIMARY KEY(name)) ENGINE=InnoDB;
-- 2,Create stored procedure -- get current sequence Value of
DROP FUNCTION IF EXISTS `mycat_seq_currval`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `mycat_seq_currval`(seq_name VARCHAR(50)) RETURNS varchar(64)
CHARSET latin1
DETERMINISTIC
BEGIN
DECLARE retval VARCHAR(64);
SET retval="-999999999,null";
SELECT concat(CAST(current_value AS CHAR),",",CAST(increment AS CHAR) ) INTO retval FROM
MYCAT_SEQUENCE WHERE name = seq_name;
RETURN retval ;
END
;;
DELIMITER ;
-- 3,Create a stored procedure to get the next sequence
DROP FUNCTION IF EXISTS `mycat_seq_nextval`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `mycat_seq_nextval`(seq_name VARCHAR(50)) RETURNS varchar(64)
CHARSET latin1
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = current_value + increment WHERE name = seq_name;
RETURN mycat_seq_currval(seq_name);
END
;;
DELIMITER ;
-- 4,Create stored procedure, set sequence
DROP FUNCTION IF EXISTS `mycat_seq_setval`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `mycat_seq_setval`(seq_name VARCHAR(50), value INTEGER)
RETURNS varchar(64) CHARSET latin1
DETERMINISTIC
BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = value
WHERE name = seq_name;
RETURN mycat_seq_currval(seq_name);
END
;;
DELIMITER ;
-- 5,insert record
INSERT INTO MYCAT_SEQUENCE(name,current_value,increment,remark) VALUES ('HISTORY', 1, 100,'Historical data usage');2.2.2 server.xml
<mycat:server xmlns:mycat="http://org.opencloudb/">
<system>
<property name="defaultSqlParser">druidparser</property>
<property name="sequnceHandlerType">1</property>
<!-- <property name="useCompression">1</property>--> <!--1 For on mysql Compression protocol-->
<!-- <property name="processorBufferChunk">40960</property> -->
<!--
<property name="processors">1</property>
<property name="processorExecutor">32</property>
-->
<!--The default is 65535 64 K be used for sql Maximum text length when parsing -->
<!--<property name="maxStringLiteralLength">65535</property>-->
<!--<property name="sequnceHandlerType">0</property>-->
<!--<property name="backSocketNoDelay">1</property>-->
<!--<property name="frontSocketNoDelay">1</property>-->
<!--<property name="processorExecutor">16</property>-->
<!--
<property name="mutiNodeLimitType">1</property> 0: Enable decimal order (default); 1: Enable 100 million level data sorting
<property name="mutiNodePatchSize">100</property> 100 million order batch
<property name="processors">32</property> <property name="processorExecutor">32</property>
<property name="serverPort">8066</property> <property name="managerPort">9066</property>
<property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>
<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> -->
</system>
<user name="root">
<property name="password">test123</property>
<property name="schemas">test</property>
</user>
<!-- <cluster> <node name="cobar1"> <property name="host">127.0.0.1</property>
<property name="weight">1</property> </node> </cluster> -->
<!-- <quarantine> <host name="1.2.3.4"> <property name="user">test</property>
</host> </quarantine> -->
</mycat:server>Configure logical library account and password
2.2.3 in rule Configure sharding rules in XML (monthly sharding is adopted this time)
<mycat:rule xmlns:mycat="http://org.opencloudb/"> <tableRule name="sharding-by-date-customer"> <rule> <columns>created_time</columns> <algorithm>sharding-by-date</algorithm> </rule> </tableRule> <function name="sharding-by-date" class="org.opencloudb.route.function.PartitionByMonth"> <property name="dateFormat">yyyy-MM-dd HH:mm:ss</property> <!--Date format--> <property name="sBeginDate">2021-01-01 00:00:00</property> <!--Start date--> <!-- <property name="sPartionDay">30</property> --> <property name="sEndDate">2021-12-31 23:59:59</property> </function> </mycat:rule>
By viewing the source code of mycat, you can see the rules of segmentation according to months: number of segments = year difference * 12 + (month difference + 1)
For example, if I set sbegindate: 2021-01-01 and snddate: 2021-12-31, the number of slices is: 0 * 12 + (12 - 1 + 1) = 12 slices.
Note that the actual number of database partitions cannot be less than the calculated number of partitions
@Override
public void init() {
try {
if (StringUtil.isEmpty(sBeginDate) && StringUtil.isEmpty(sEndDate)) {
nPartition = 12;
scene = 1;
initFormatter();
beginDate = Calendar.getInstance();
beginDate.set(Calendar.MONTH, 0);
endDate = Calendar.getInstance();
endDate.set(Calendar.MONTH, 11);
return;
}
beginDate = Calendar.getInstance();
beginDate.setTime(new SimpleDateFormat(dateFormat)
.parse(sBeginDate));
initFormatter();
// If in rule XML is configured as follows
if (sEndDate != null && !sEndDate.equals("")) {
endDate = Calendar.getInstance();
endDate.setTime(new SimpleDateFormat(dateFormat).parse(sEndDate));
nPartition = ((endDate.get(Calendar.YEAR) - beginDate.get(Calendar.YEAR)) * 12
+ endDate.get(Calendar.MONTH) - beginDate.get(Calendar.MONTH)) + 1;
if (nPartition <= 0) {
throw new java.lang.IllegalArgumentException("Incorrect time range for month partitioning!");
}
} else {
nPartition = -1;
}
} catch (ParseException e) {
throw new java.lang.IllegalArgumentException(e);
}
}3. About Springboot project connecting Mycat
3.1 add jdbc connection in yml:
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
username: root
password: xinchai123
url: jdbc:mysql://192.168.3.221:8066/test?autoReconnect=true&useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai&allowMultiQueries=true&zeroDateTimeBehavior=convertToNull
druid:
initial-size: 5
min-idle: 5
max-active: 5
max-wait: 5000
time-between-eviction-runs-millis: 6000
min-evictable-idle-time-millis: 30000
web-stat-filter:
enabled: true
url-pattern: /*
exclusions: '*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*'
stat-view-servlet:
enabled: true
url-pattern: /druid/*
login-username: root
login-password: root
reset-enable: false3.2 add mysql connection jar package
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.30</version>
</dependency>Step on Pit 2: it is recommended to use the package of 5.1.30, and the package of 5.1.38 will be abnormal:
com.mysql.jdbc.PacketTooBigException: Packet for query is too large (4 > -1). You can change this value on the server by setting the max_allowed_packet' variable
In addition, if an exception IndexOutofBoundException occurs when inserting data: Mycat fragment data, it should be caused by incorrect fragment rule configuration.
4. About the testing tool provided by Mycat {testool Tar (self download)
There are many areas that need to be improved for the official documents. The examples given should be more standardized. Although they are open source software for the benefit of everyone, they also hope to let developers avoid detours.
The insertion test statement given by the official cannot be used. It took a long time to solve the problem:
4.1 create mydate create in the unzipped bin directory SQL template file:
total=10
sql=INSERT INTO `history` (`timepoint`, `state`, `code`, `serial_number`, `location_state`, `lat`, `lng`, `speed`, `pressure_value`, `engine_torque`, `engine_speed`, `friction_torque`, `engine_fuel_flow`, `scr_up_nox`, `scr_down_nox`, `residual_reactant`, `intake_value`, `scr_in_temperature`, `scr_export_temperature`, `dpf_differential_pressure`, `engine_coolant_temperature`, `power`, `actual_egr`, `setting_egr`, `created_time`, `idt_version`, `fault_status`) VALUES ('2021-07-25 23:59:59', '0', '${char([a-f,0-9]8:10)}', '34', 0, '41.88891', '123.5265', '0.0', '0.0', '0', '0', '0', '0.00', '0.00', '0.00', '0', '0.00', '0.0', '0.0', '0.0', '0', '0', '0', '0', '${date(yyyyMMdd-[2021-2021]y)}', 1, '0');4.2 execution statement:
test_stand_insert_perf.bat jdbc:mysql://192.168.3.221:8066/xinchai root xinchai123 10 "file=mydata-create.sql"
Step 3: the sentence sql=xxx in the sql template file cannot be wrapped
Step 4: execute statement test_stand_insert_perf.bat, the last file = mydata create SQL needs quotation marks (official documents do not...)
start-up ./mycat start stop it ./mycat stop restart ./mycat restart View status ./mycat status Foreground operation ./mycat console