The Kingshard of MySQL middleware

Kingshard is a high-performance MySQL proxy project developed by go, kingshard in satisfying basic read/write separation functions,

Committed to simplifying MySQL sub-Library table operations, allowing DBAs to easily and smoothly implement MySQL database expansion through Kingshard.

The performance of the Kingshard is approximately 80% of the performance of the directly connected MySQL.

I. Basic functions

Supports SQL read-write separation (verified)

Supports load balancing through weights between multiple slave,slave. (verified)

Support for SQL blacklist mechanism (verified)

Sub-table (verified), Kingshard supports hash and range sub-tables by integer, and supports a table by date,

Sharding (verified)

Sharding supports the following features:

1. Support by integer hash and range table mode.

2. Support the time table by year, month and day dimension.

3. Support Cross-node sub-table, sub-table can be distributed across different nodes.

4. Supports aggregation functions such as Count,sum,max and min across nodes.

5. A JOIN operation that supports a single table, which supports a table and another join operation that does not divide the table.

6. Support for operations such as order By,group by,limit across nodes.

7. Support for sending SQL to specific node execution.

8. Supports the execution of transactions on a single node and does not support distributed transactions across multiple nodes.

9. Supports non-transactional updating (insert,delete,update,replace) of child tables on multiple node.

Performance testing of Kingshard.

Factors affecting the performance of Kingshard

1. Client side concurrency number. The number of concurrent numbers is too small to give full play to the performance of Kingshard. The exact number of values is suitable for pressure measurement.

2.max_conns_limit. The specific settings are also related to the hardware, need to pressure test, the official recommendation set to 128, according to the official pressure test results obtained,

When set to greater than 128, performance is not significantly increased

Official Stress test Report:

Https://github.com/flike/kingshard/blob/master/doc/KingDoc/kingshard_performance_test.md

Kingshard Official documents

Https://github.com/flike/kingshard/blob/master/README_ZH.md

Golang installation of the environment

Http://www.linuxidc.com/Linux/2015-02/113159.htm

Yaml detection

http://www.yamllint.com/

Kingshard of the Guardian process

Nohup./bin/kingshard-config=/etc/ks.yaml &

Here is a sample configuration file

####### Hash sub-table #######################

---

Addr: "0.0.0.0:9696"

Blacklist_sql_file:/home/dengwang/software/src/github.com/flike/blacksql/blacklist

Log_level:debug

Log_path:/home/dengwang/software/src/github.com/flike/kingshard/log

Nodes

-

Down_after_noalive:32

Master: "mysql91:3306"

Max_conns_limit:64

Name:node1

Password: [email protected]

Slave: "Host55:[email protected],host51:[email protected]"

User:root

Password:kingshard

Proxy_charset:gbk

Schema

Db:test

Default:node1

Nodes

-Node1

Shard

-

Key:tid

Locations

-4

Nodes

-Node1

Table:tt

Type:hash

User:kingshard

# # # Range Sub-table #####################################################################

---

Addr: "0.0.0.0:9696"

Blacklist_sql_file:/home/dengwang/software/src/github.com/flike/blacksql/blacklist

Log_level:debug

Log_path:/home/dengwang/software/src/github.com/flike/kingshard/log

Nodes

-

Down_after_noalive:32

Master: "mysql91:3306"

Max_conns_limit:64

Name:node1

Password: [email protected]

Slave: "Host55:[email protected],host51:[email protected]"

User:root

Password:kingshard

Proxy_charset:utf8

Schema

Db:test

Default:node1

Nodes

-Node1

Shard

-

Key:tid

Locations

-4

Nodes

-Node1

Table:tt

Type:range

table_row_limit:1000

User:kingshard

############# Sub-table in sharding

---

Addr: "0.0.0.0:9696"

Blacklist_sql_file:/home/dengwang/software/src/github.com/flike/blacksql/blacklist

Log_level:debug

Log_path:/home/dengwang/software/src/github.com/flike/kingshard/log

Nodes

-

Down_after_noalive:32

Master: "host51:3306"

Max_conns_limit:64

Name:node1

Password: [email protected]

Slave: "host52:3306"

User:root

-

Down_after_noalive:32

Master: "host55:3306"

Max_conns_limit:64

Name:node2

Password: [email protected]

Slave: "host56:3306"

User:root

-

Down_after_noalive:32

Master: "host53:3306"

Max_conns_limit:64

Name:node3

Password: [email protected]

Slave: "host53:3306"

User:root

Password:kingshard

Proxy_charset:utf8

Schema

Db:test

Default:node3

Nodes

-Node1

-Node2

-Node3

Shard

-

Key:tid

Locations

-4

-4

Nodes

-Node1

-Node2

Table:tt

table_row_limit:1000

Type:range

User:kingshard

This article is from the "SQL Server MySQL" blog, so be sure to keep this source http://dwchaoyue.blog.51cto.com/2826417/1754009

The Kingshard of MySQL middleware