I’m testing with MySqlRouter and I’m getting the error:
I am testing with 1000 threads.
The error does not occur when I perform the test directly on the Primary or Secondary nodes.
2024-09-01 16:04:02 metadata_cache INFO [7fa31c2756c0] Using unreachable_quorum_allowed_traffic=‘none’
2024-09-01 16:04:02 metadata_cache INFO [7fa31c2756c0] Using read_only_targets=‘secondaries’
2024-09-01 16:04:02 metadata_cache INFO [7fa31c2756c0] Potential changes detected in cluster after metadata refresh (view_id=0)
2024-09-01 16:04:02 metadata_cache INFO [7fa31c2756c0] Metadata for cluster ‘myCluster’ has 3 member(s), single-primary:
2024-09-01 16:04:02 metadata_cache INFO [7fa31c2756c0] cluster01:24801 / 33060 - mode=RW
2024-09-01 16:04:02 metadata_cache INFO [7fa31c2756c0] cluster02:24802 / 33060 - mode=RO
2024-09-01 16:04:02 metadata_cache INFO [7fa31c2756c0] cluster03:24803 / 33060 - mode=RO
2024-09-01 16:04:14 routing INFO [7fa3152006c0] Stop accepting connections for routing routing:bootstrap_rw listening on 0.0.0.0:6446
2024-09-01 16:04:14 routing ERROR [7fa3152006c0] [routing:bootstrap_rw_split] connecting to backend(s) for client from 10.10.10.20:37810 failed: connect(/* cluster02 /): ignored, connect(/ cluster03 /): ignored, resolve(cluster01) failed after 0ms: Too many open files, end of destinations: no more destinations
2024-09-01 16:04:14 routing INFO [7fa3152006c0] Stop accepting connections for routing routing:bootstrap_rw_split listening on 0.0.0.0:6450
2024-09-01 16:04:14 routing ERROR [7fa313e006c0] [routing:bootstrap_rw_split] connecting to backend(s) for client from 10.10.10.20:37828 failed: connect(/ cluster02 /): ignored, connect(/ cluster03 /): ignored, connect(/ cluster01:24801 /): is quarantined, end of destinations: no more destinations
2024-09-01 16:04:14 routing ERROR [7fa3134006c0] [routing:bootstrap_rw_split] connecting to backend(s) for client from 10.10.10.20:37842 failed: connect(/ cluster03 /): ignored, connect(/ cluster02 /): ignored, connect(/ cluster01:24801 */): is quarantined, end of destinations: no more destinations
2024-09-01 16:04:14 routing INFO [7fa309e006c0] Start accepting connections for routing routing:bootstrap_rw_split listening on ‘0.0.0.0:6450’
2024-09-01 16:04:14 routing INFO [7fa309e006c0] Start accepting connections for routing routing:bootstrap_rw listening on ‘0.0.0.0:6446’
2024-09-01 16:04:14 routing WARNING [7fa309e006c0] accepting new connection failed at accept(): generic:24, Too many open files
2024-09-01 16:04:14 routing WARNING [7fa309e006c0] accepting new connection failed at accept(): generic:24, Too many open files
System data:
MySQL 8.4.2
Ubuntu: 24.04 LTS
MySQL Router Ver 8.4.2
Have you searched https://bugs.mysql.com/ for anything similar? I would make the nofile unlimited for the user account that mysqlrouter is running under.
Then, I would look at ProxySQL, as it is more performant, and offers far, far more features than MySQL Router.
Yes I tested ProxySQL, but I found mysqlrouter to be better.
I set the mysqlrouter user to unlimited and the error still persists.
The tests I’m doing don’t make me feel very confident about using INNODB CLUSTER.
“InnoDB Cluster” is just Oracle’s marketing term for “Group Replication + Router”. You can use Group Replication without router.
For our reasoning on ProxySQL over mysqlrouter:
Personally, the #1 issue I have with router is that you must rewrite your application to do R/W splitting. Whereas using ProxySQL does not require any modifications to application code.
Have you checked the runtime_mysql_query_rules table to ensure the rules have been loaded?
Additionally, ProxySQL will not split/multiplex connections within the same transaction. SELECT queries cannot be sent to different hosts while inside the same transaction with UPDATE/DELETE. You need to disable transactions on sysbench in order to see the splitting.
SELECT queries inside a trx would be considered “critical reads” and need to see the most up to date data, which would be the data potentially changed within the trx. To split non-critical reads, they need to be outside of the transaction.