Shulou(Shulou.com)06/01 Report--

Mycat sharding table global self-increment primary key test mycat sharding table global self-increment primary key test 1. Global sequence number introduction in the case of implementing sub-library and sub-table, the database self-increasing primary key can no longer guarantee the global uniqueness of the self-increment primary key. To this end, MyCat provides global sequence and provides a variety of implementations, including local configuration and database configuration.

1. Local file mode

How to use server local disk files

two。 Database mode

The way to use a dedicated database

3. Local timestamp mode

Using the timestamp algorithm

4. Distributed ZK ID generator

Distributed ID generator based on ZK and local configuration (you can obtain the unique InstanceID of the cluster (computer room) through ZK, or you can configure InstanceID through configuration files)

5.Zk increment mode

Another way to generate ZK

6. Other ways 2. Detailed explanation of configuration mode 1. Local file mode

Configuration method:

Make the following configuration in the sequence_conf.properties file:

# this is the settings of the global table GLOBAL_SEQ.HISIDS=GLOBAL_SEQ.MINID=1001GLOBAL_SEQ.MAXID=1000000000GLOBAL_SEQ.CURID=1000# below is the settings of the custom table

Where HISIDS represents the used history segment (generally, no special needs can not be configured), MINID represents the minimum ID value, and MAXID represents the maximum

ID value, and CURID represents the current ID value.

Configuration in server.xml:

0

Note: sequnceHandlerType needs to be configured to 0, which means local file mode is used.

Examples of use:

Insert into table1 (id,name) values (next value for MYCATSEQ_GLOBAL,'test')

Cons: when MyCAT is rereleased, the sequence in the configuration file will revert to its original value.

Advantages: local loading, fast reading speed.

[detailed configuration and testing of local files]

1.1 modify the configuration file server.xml to specify that the encryption method is local file mode

0

1.2 configure in schema.xml file, add table tt,ID as primary key, slice on dn$1-3, mod-log in sharding mode

1.3 modify the configuration in the sequence_conf.properties file as follows:

# this is the settings of the global table GLOBAL.HISIDS=GLOBAL.MINID=10001GLOBAL.MAXID=1000000000GLOBAL.CURID=10000# below is the settings TT.HISIDS=TT.MINID=1001TT.MAXID=2000TT.CURID=1000 of the custom table

1.4 create a table tt

CREATE TABLE tt (`id` INT (10) UNSIGNED NOT NULL AUTO_INCREMENT, `name_ `INT (10) UNSIGNED NOT NULL,PRIMARY KEY (`id`)) DEFAULT CHARSET=utf8

View table structure

Mysql > desc tt +-+ | Field | Type | Null | Key | Default | Extra | +-+-- -+ | id | int (10) unsigned | NO | PRI | NULL | auto_increment | | name_ | int (10) unsigned | NO | | NULL | | +-- -+ 2 rows in set (0.01 sec)

Check the table sharding

Mysql > explain select * from tt +-+-+ | DATA_NODE | SQL | +-+-+ | dn1 | SELECT * FROM tt LIMIT | 3000000 | | dn2 | SELECT * FROM tt LIMIT 3000000 | | dn3 | SELECT * FROM tt LIMIT 3000000 | +-+-+ 3 rows in set (0.07 sec)

1.5 insert data Test

Insert a line that uses the global serial number

Insert into tt (id,name_) values (next value for MYCATSEQ_GLOBAL,1)

View serial number usage

Mysql > SELECT * FROM TT;+-+-+ | id | name_ | +-+-+ | 0 | 1 | +-+-+ 1 row in set (10001 sec)

Insert a line using the TT serial number

Insert into tt (id,name_) values (next value for MYCATSEQ_TT,2)

Check the sequence for good use.

Mysql > SELECT * FROM TT ORDER BY name_;+-+-+ | id | name_ | +-+-+ | 10001 | 1 | | 1001 | 2 | +-+-+ 2 rows in set (sec)

Insert more data and query results

Mysql > SELECT * FROM TT ORDER BY name_; +-+-+ | id | name_ | +-+-+ | 10001 | 1 | 1001 | 2 | 1002 | 3 | 1003 | 4 | 1004 | 5 | 1005 | 6 | +-+-+ 6 rows in set (sec)

The situation on the fragmentation of these data

Mysql (db1) > select * from tt;+-+-+ | id | name_ | +-+-+ | 1002 | 3 | | 1005 | 6 | +-+-+ 2 rows in set (sec) mysql (db2) > select * from tt +-+-+ | id | name_ | +-+-+ | 1003 | 4 | +-+-+ 1 row in set (sec) mysql (db3) > select * from tt +-+-+ | id | name_ | +-+-+ | 1001 | 2 | 1004 | 5 | 10001 | 1 | +-+

OK It works!!

Advantages: simple configuration, local files, fast reading and writing speed disadvantages: causing MYCAT to become stateful middleware, which is not conducive to the deployment of cluster 2. Database mode has obvious shortcomings, that is, after MYCAT cluster switching and database master-slave switching of keeping sequence, many uncontrollable contents need to deal with these problems very carefully, and the maintenance cost is high. Although MYCAT is stateless, there is a single point problem 3. Local timestamp mode

ID= 64-bit binary (42 (milliseconds) + 5 (machine ID) + 5 (business code) + 12 (repeat accumulation)

Server.xml:

two

Sequence_time_conf.properties:

WORKID=0-31 any integer DATAACENTERID=0-31 any integer

It's a good way. Distributed ZK ID generator

-this is the one I want to test the most.

Configuration options:

three

Description of the principle:

The connection information for Zk is configured uniformly in the zkURL property of myid.properties.

Distributed ID generator based on ZK and local configuration (can obtain unique InstanceID of cluster (computer room) through ZK or configure InstanceID through configuration file) ID structure: long 64-bit, ID can occupy up to 63-bit current time millis (microsecond timestamp 38-bit, can be used for 17 years) instanceId (instance ID, can be obtained through ZK or configuration file, 5-bit, or decimal 0-31) threadId (thread ID,9 bit) increment (self-increment, 6-bit)

A total of 63 bits, which can withstand the concurrency of 1000 * (2 ^ 6) = 640000 in a single machine room and single machine.

No sad view lock, no strong competition, higher throughput

Configuration file: sequence_distributed_conf.properties, as long as the configuration: INSTANCEID=ZK is to get InstanceID from the ZK.

Main steps to configure the ZK ID generator

4.1 configure zookeeper

Zookeeper is a subproject of hadoop

General production needs to configure zookeeper cluster, odd number of nodes, at least three nodes

In order to test the relevant features of mycat, we only build a single-node zookeeper

ZooKeeper Standalone mode

Download the ZooKeeper package from the Apache website (zookeeper.apache.org). I chose version 3.3.4 (zookeeper-3.3.4.tar.gz), which is very easy to install on a Linux machine. You only need to unzip it and simply configure it to start the ZooKeeper server process.

Change the zoo_sample.cfg under the zookeeper-3.3.4/conf directory to zoo.cfg, and the contents of the configuration file are as follows:

TickTime=2000dataDir=/home/hadoop/storage/zookeeperclientPort=2181initLimit=5syncLimit=2

Start the ZooKeeper server process below:

Cd zookeeper-3.3.4/bin/zkServer.sh start

In this way, zookeeper can be used, and the configuration of the firewall can be handled by itself. When setting up a production network in ZooKeeper distributed mode or boasting that the computer room is distributed, add 4.2 configure mycat.

It mainly involves three aspects: modify myid.properties, modify server.xml, modify sequence_distributed_conf.properties

Myid.properties:

LoadZk=true # use zk to manage the addresses and ports of mycat and IDzkURL=127.0.0.1:2181 # zk servers clusterId=010 # IDmyid=01001 of the mycat cluster in this computer room # name of the IDclusterNodes=mycat-02 # mycat node of the mycat in the cluster # server booster; booster install on db same server,will reset all minCon to 1

Server.xml:

Druidparser 1 3306 3308 65535 3 password dbykt

Sequence_distributed_conf.properties:

INSTANCEID=01CLUSTERID=01

After the above three files have been modified, copy all the files under conf to the conf/zkconf/ directory

Cp * .txt * .xml * .properties zkconf/

Initialize:

Cd. / bin/./init_zk_data.sh4.3 starts mycat

First use console to see if the startup is normal.

. / bin/mycat console

Start after normal.

. / bin/mycat start4.4 Test mycat ZK distributed ID Generator

Use the tool Jmeter, which will not be discussed here

Configuration table ot1:

.

Restart mycat

. / bin/mycat restart

Build the table (the table is more complex because the insertion performance is also tested)

CREATE TABLE `ot1` (`id` bigint (20) NOT NULL AUTO_INCREMENT,.. PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8

Jmeter insert data test

The sql statements used are as follows:

INSERT INTO `ot1` (`id`,.. ) VALUES (next value for MYCATSEQ_OT,.. );

Query the results of observation

Mysql > SELECT id FROM ot1 ORDER BY id;. | 3894484648986705980 | | 389448464902020260414 | | 3894484649053814784 | | 3894484649053814847 | | 3894484649926230018 | 3894484649926230019 | 3894484649926230020 | 3894484649926230021 | 3894484649926230021 | 38944844849959784454 | 3894484649959784455 | | 3894484484484995978784456 | 389448448449995978784456 | 3894484484999338889 | 3894484489993350023 | 3895004484846448334848484604484848460448448,48,48,48,48,48,48,48,48,48,48,999338889 | 389448448448499999338889 | 3894484484999999338889 | 38944844844899995978784455 | 389448448448499995978784455 | 389448448448499995978784455 | 3894484644899995978784456 | 389448448994999338889 | 389448448999330023 | 389500448484642626230020 | 38944844844649959444848460448| 77950048|

Total amount of query data:

Mysql > select count (id) id_count from ot1;+-+ | id_count | +-+ | 37784391 | +-+

Find out the maximum ID:

Mysql > select max (id) id from ot1; +-+ | id | +-+ | 3897338173617897525 | +-+ 1 row in set (0.03 sec)

Determine if there is a duplicate ID:

Mysql > select count (id), id from ot1 group by id having count (id) > 1 min empty set (1 min 42.85 sec) 4.5 conclusion

The distributed ZK ID generator is really powerful. This provides a new scheme for database double activity across computer rooms, and makes the final consistency of data under the double A computer room architecture have a new idea.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.