Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MyCAT log analysis     - Installation Elementary OS Freya 20 things to do (Linux)

- Zabbix API and PHP configuration (Server)

- Java rewrite equals method (Programming)

- Eclipse, Tomcat configuration JNDI connection Oracle data source example (Server)

- RedHat virtual machine to install VMware Tools (Linux)

- Use Linux firewall camouflage defense hacked (Linux)

- How to use Monit to deploy a server to monitor the system (Server)

- Text analysis tools - awk (Linux)

- Ubuntu Apache virtual host configuration (Server)

- Git commands (Linux)

- Ubuntu install Liferea news subscription software (Linux)

- ORA-08102 errors (Database)

- Linux centralized log server rsyslog (Server)

- Java Concurrency -volatile keywords (Programming)

- RedHat Linux 6.5 Enterprise Edition installation Redis 3.0.3 (Database)

- Linux POST fstab configuration file read-only variable can not be modified problem (Linux)

- shell script: a key to install LAMP, LNMP script (Server)

- OpenDaylight Helium version installed (Linux)

- JavaScript common functions summary (Programming)

- Linux terminal program running in the background (Linux)

 
         
  MyCAT log analysis
     
  Add Date : 2018-11-21      
         
         
         
  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.
     
         
         
         
  More:      
 
- Use preseed script to install Ubuntu 14.04 (Linux)
- libreadline.so.6: can not open shared object file problem solution (Linux)
- Oracle 11gr2 new APPEND_VALUES tips (Database)
- Create a DLL using MinGW and Attention (Programming)
- Tsung first test installation (Linux)
- Advanced permissions Linux file system settings (Linux)
- Execute command sentence can result in equipment permanently bricked in Linux laptop (Linux)
- Secondary exponential smoothing prediction method implemented in Python (Programming)
- MySQL database master never solve the synchronization method (Database)
- GRUB2 boot Ubuntu Manual (Linux)
- RedHat Linux 7 build yum source server (Server)
- How do I switch from NetworkManager to systemd-network on Linux (Linux)
- numpy and SciPy installation under Python for scientific computing package (Linux)
- PL / SQL in forall simple test (Database)
- Linux System Getting Started Tutorial: Installing Brother printer in Linux (Linux)
- stat Usage: Get permission to file the corresponding figures (Linux)
- How to enhance the security of Linux systems (Linux)
- Java static code analysis tool Infer (Programming)
- The most concise explanation of JavaScript closures (Programming)
- Java thread pool: ExecutorService, Executors (Programming)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.