| |
MyCAT log information for understanding the operation of MyCAT not available, such as whether to adopt MyCAT separate read and write, for a query, how MyCAT performed, each slice will be distributed to which node and so on.
The default level is info, which can be set by log4j.xml debug level, so you can get more information about MyCAT running inside.
By following MyCAT start and execute a query to analyze the running processes MyCAT.
Here Go!
processors refers to the number of CPU core, aio thread pool size refers to the AIO thread pool, twice the number of CPU cores
-sysconfig params: it refers mycat startup parameters can be modified in the server.xml.
Wherein idleTimeout = 1800000 refers to the external application and mycat maximum idle connection length 30min, more than 30min, the external applications to mycat connection will be disconnected.
dataNodeHeartbeatPeriod = 10000 node heartbeat frequency of 10s again.
9066 is the administration port MyCAT
8066 is the default port MyCAT, similar to the MySQL port 3306.
Next, initialize MyCAT connection pools, the default minimum of 10 connections
Can be defined by the schema.xml at dataHost
-init backend myqsl source, create connections total 10 for hostM1 index: 0: Initialization backend MySQL connection, master node is hostM1, can be specified according to the conf directory dnindex.properties file.
The following information is then output to create a connection, the release channel.
Executive heartbeat, so the need to establish a new connection, because I was a master configuration from two, so the need to determine localhost, 192.168.244.144 and 192.168.244.146 heartbeat, localhost started 10 connections, so no need to create a new connection but hostS1 (ie 192.168.244.146) and hostS2 (ie 192.168.244.144) needs.
threadID corresponds to the process ID in MySQL
The next tip is connected to the output information MyCAT can be seen connected to MyCAT, the device will show database, show tables, select @@ version_comment limit 1 command. It is an analysis of personal feeling Cache database and table information for subsequent routing.
Next, about the implementation of select * from travelrecord, view MyCAT internal implementation logic
First, the SQL statement will determine the routing information (in accordance with the following output, MyCAT be used inside a map collection to store SQL statements routing information). If not, it will first generate routing information.
The main role of the route is used to determine the path of execution of SQL statements, including queries on which data slice.
-SQLRouteCache Miss cache: no routing information
-SQLRouteCache Add cache: generate routing information
-execute mutinode query select * from travelrecord: to confirm the query span multiple slices
-has data merge logic: merging data exist
Here you can confirm the specific node in which to read the
Because I was a master configuration from two, and the balance = "1", all readHost and stand by writeHost participate in load balancing select statement, so hostS1 (192.168.244.146) and hostS2 (192.168.244.144) are involved in select statement load balancing.
Before performing the select statement, the connection to be synchronized, simultaneous connections why it?
MyCAT order to efficiently use the MySQL backend connections, using connection pooling model is based on MySQL instance.
In Mycat connection pool, the connection is currently available MySQL data structure into a HashMap of years, Key to the current connection corresponding Database, in addition to two categories, namely, the connection is automatically submitted in accordance with or manual commit mode to distinguish, this is designed to efficiently match the query of available connections, the specific logic is as follows:
When a user session need an automatic submission to the slice when dn1 (corresponding to db1) of SQL connections, the connection pool first find whether there is an available connection on db1, if so, to see if there are auto-commit connection mode, find returns, otherwise returns submitted manually db1 on connection mode, if there is no available connection db1, then returns a random other db corresponding available connection, if there is no available connection, and the connection pool has not reached the upper limit, create a new connection and return to this logical process, we find that the user's session might not get the connection he originally wanted, such as database does not correspond to the transaction or pattern does not match, so before the implementation of specific SQL, as well as an automatic synchronization database connection the process consists of four indicators transaction isolation level, transaction mode, character set, Database, etc., after the synchronization is complete, it will implement specific SQL commands.
In the implementation of select * from travelrecord statement, because it will involve the three fragments, so MyCAT selected three read source query, wherein both the hostS2, one hostS1 on. For two connections hostS2, where the implementation of the heartbeat before a connection to be multiplexed MyCAT hostS2, you need to create another.
For connection hostS2 before reuse, because db1 time before executing the heartbeat that is used, so this schema change is false, without modification.
For connection hostS1 reuse before, because before using db1, this is db3, so schema change is true, we need to modify.
For each synchronous connection, you need to begin to execute a query in connection synchronization after.
A total of five received ok response, which corresponds to the above figure 2 total syn cmd 2 commands, the other three correspond to the image above total syn cmd 3 commands, according to the host can very easily correspond.
We can start the program,
The first is for a red box on 192.168.244.146 in dn3 fragmentation, because my whole select * from travelrecord only two output lines,
mysql> select * from travelrecord;
+ --------- + ------------ + --------------- + ------ + --- --- +
| Id | user_id | traveldate | fee | days |
+ --------- + ------------ + --------------- + ------ + --- --- +
| 1 | mysql-server2 | 2016-01-01 | 100 | 10 |
| 5000001 | mysql-server3 | 2016-01-02 | 100 | 10 |
+ --------- + ------------ + --------------- + ------ + --- --- +
2 rows in set (0.04 sec)
Only across two slices, dn1 and dn2, so no results dn3 query output, direct connection release and channel.
The contents of the blue box, and the second is related to the red box in dn1 192.168.244.144, there is the resulting output.
-field metadata inf: DataMergeService first merged information output columns.
-on row end reseponse: Representative Results dn1 of output.
Here is dn2 slice output because dn2 fragmentation is just the new connection, so the same need to be synchronized, after two received ok response received, returns the result, and then release the connection channel.
Finally DataMergeService merge all returned results.
last packet id is expected MyCAT the results back to the preceding program.
Thus, a query log analysis is completed ~
to sum up:
1. In fact, when simultaneous connections, will not necessarily have received ok response information, such as I SELECT * FROM travelrecord WHERE id = 1 statement is executed,
After the synchronization connection, direct connection and release of the channel, did not produce information received ok response did not occur on row end reseponse.
It seems that this can only be aware of the reason through the source code to.
2. usually MyCAT log analysis, generally using tailf command, but frequently the heartbeat information and indeed is interference by tailf mycat.log | grep -Ev "Timer | release channel" to filter out some of the information.
3. received ok response not only for the synchronization connection, when performing drop table operation will produce.
Therefore, received ok response should be the result of execution of a feedback. |
|
|
|