Advertisement
Guest User

Untitled

a guest
Sep 20th, 2016
86
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 4.90 KB | None | 0 0
  1. 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.
  2. To Validate this I had setup a PXC Cluster with 5 nodes, and MySQL "standard" with async replication.
  3. I had setup 3 HG
  4. Hg 80 with One PXC node in 192.168.1.5
  5. HG 600 with The mysql master in 192.168.1.104
  6. HG 601 with the readers '192.168.1.107','192.168.1.109','192.168.1.111'
  7.  
  8. Query rules managing the traffic:
  9. ``` 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);
  10. 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);
  11. LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK;```
  12.  
  13. the application running is sysbench using the following :
  14. ```
  15. 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
  16.  
  17. 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
  18. ```
  19.  
  20. What happens is that on the real HG the load is sent and manage correctly.
  21. But on the mirror one the proxy seems opening tons of conenctions at the same time.
  22.  
  23. In prepare with 1 thread
  24. +-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
  25. | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv | Latency_ms |
  26. +-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
  27. | 600 | 192.168.1.104 | 3306 | ONLINE | 10 | 1 | 11 | 0 | 30 | 9652055 | 0 | 659 | <--- was suppose to be one?
  28. | 601 | 192.168.1.104 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 659 |
  29. | 601 | 192.168.1.107 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 697 |
  30. | 601 | 192.168.1.109 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 729 |
  31. | 601 | 192.168.1.111 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 553 |
  32. | 80 | 192.168.1.5 | 3306 | ONLINE | 0 | 1 | 1 | 0 | 60 | 19304110 | 0 | 174 |
  33. +-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
  34. 6 rows in set (0.00 sec)
  35.  
  36.  
  37. When running with 10 threads:
  38. ```
  39. +-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
  40. | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv | Latency_ms |
  41. +-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
  42. | 600 | 192.168.1.104 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
  43. | 601 | 192.168.1.104 | 3306 | ONLINE | 68 | 63 | 427 | 0 | 52439 | 2507121 | 212328194 | 838 | <- ??
  44. | 601 | 192.168.1.107 | 3306 | ONLINE | 18 | 96 | 213 | 0 | 133769 | 6410816 | 544384950 | 689 | <- ??
  45. | 601 | 192.168.1.109 | 3306 | ONLINE | 82 | 53 | 424 | 0 | 58440 | 2790445 | 235455374 | 1681 | <- ??
  46. | 601 | 192.168.1.111 | 3306 | ONLINE | 17 | 83 | 345 | 0 | 132994 | 6376162 | 542993723 | 1502 | <- ??
  47. | 80 | 192.168.1.5 | 3306 | ONLINE | 7 | 3 | 10 | 0 | 377756 | 37391208 | 317866458 | 176 | <-- 10 conn
  48. +-----------+---------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
  49. 6 rows in set (0.00 sec)
  50. ```
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement