Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- When using mirroring I had noticed that the mirror server is bombarde by a huge number of connections also if the application is opening a very limited number of them.
- To Validate this I had setup a PXC Cluster with 5 nodes, and MySQL "standard" with async replication.
- I had setup 3 HG
- Hg 80 with One PXC node in 192.168.1.5
- HG 600 with The mysql master in 192.168.1.104
- HG 601 with the readers '192.168.1.107','192.168.1.109','192.168.1.111'
- Query rules managing the traffic:
- ``` insert into mysql_query_rules (rule_id,username,destination_hostgroup,mirror_hostgroup,active,retries,match_digest,apply) values(200,'load_RW',80,600,1,3,'^SELECT.*FOR UPDATE',1);
- insert into mysql_query_rules (rule_id,username,destination_hostgroup,mirror_hostgroup,active,retries,match_digest,apply) values(201,'load_RW',80,601,1,3,'^SELECT ',1);
- LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK;```
- the application running is sysbench using the following :
- ```
- sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --mysql-host=192.168.1.50 --mysql-port=3311 --mysql-user=load_RW --mysql-password=test --mysql-db=mirror_test --db-driver=mysql --oltp-tables-count=5 --oltp-tablesize=50000 --max-requests=0 --num-threads=1 prepare
- sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua --mysql-host=192.168.1.50 --mysql-port=3311 --mysql-user=load_RW --mysql-password=test --mysql-db=mirror_test --db-driver=mysql --oltp-tables-count=5 --oltp-tablesize=50000 --max-requests=0 --max-time=900 --oltp-point-selects=5 --oltp-read-only=on --num-threads=1 --oltp-reconnect-mode=query --oltp-skip-trx=on --report-interval=10 --mysql-ignore-errors=all run
- ```
- What happens is that on the real HG the load is sent and manage correctly.
- But on the mirror one the proxy seems opening tons of conenctions at the same time.
- In prepare with 1 thread
- +-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
- | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv | Latency_ms |
- +-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
- | 600 | 192.168.1.104 | 3306 | ONLINE | 10 | 1 | 11 | 0 | 30 | 9652055 | 0 | 659 | <--- was suppose to be one?
- | 601 | 192.168.1.104 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 659 |
- | 601 | 192.168.1.107 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 697 |
- | 601 | 192.168.1.109 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 729 |
- | 601 | 192.168.1.111 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 553 |
- | 80 | 192.168.1.5 | 3306 | ONLINE | 0 | 1 | 1 | 0 | 60 | 19304110 | 0 | 174 |
- +-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
- 6 rows in set (0.00 sec)
- When running with 10 threads:
- ```
- +-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
- | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv | Latency_ms |
- +-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
- | 600 | 192.168.1.104 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
- | 601 | 192.168.1.104 | 3306 | ONLINE | 68 | 63 | 427 | 0 | 52439 | 2507121 | 212328194 | 838 | <- ??
- | 601 | 192.168.1.107 | 3306 | ONLINE | 18 | 96 | 213 | 0 | 133769 | 6410816 | 544384950 | 689 | <- ??
- | 601 | 192.168.1.109 | 3306 | ONLINE | 82 | 53 | 424 | 0 | 58440 | 2790445 | 235455374 | 1681 | <- ??
- | 601 | 192.168.1.111 | 3306 | ONLINE | 17 | 83 | 345 | 0 | 132994 | 6376162 | 542993723 | 1502 | <- ??
- | 80 | 192.168.1.5 | 3306 | ONLINE | 7 | 3 | 10 | 0 | 377756 | 37391208 | 317866458 | 176 | <-- 10 conn
- +-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
- 6 rows in set (0.00 sec)
- ```
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement