Multi server separated out in two data centers and geographic locations

For clarification my comment in the mailing list was talking about mcsapi only. This does not appear to be an mcsapi issue so my comment does not apply.

After several system shutdown/start cycles, the (unreleased) 1.1.3 system failed to start having

tail /usr/local/mariadb/columnstore/mysql/db/pm-01.err

2018-01-09 16:20:42 139938857060160 [ERROR] mysqld: File '/usr/local/mariadb/columnstore/mysql/db/ClientRotator caught exception: InetStreamSocket::connect: connect() error: Connection refused to: InetStreamSocket: sd: 48 inet: xx.xx.xx.xx port: 8601' not found (Errcode: 2 "No such file or directory")

2018-01-09 16:20:42 139938857060160 [ERROR] Failed to open log (file '/usr/local/mariadb/columnstore/mysql/db/ClientRotator caught exception: InetStreamSocket::connect: connect() error: Connection refused to: InetStreamSocket: sd: 48 inet: xx.xx.xx.xx port: 8601', errno 2)

Thanks to the clarification from linuxjedi, I downgraded the system from the (unreleased) 1.1.3 to release 1.1.2 GA.

It appears that the observed problem with a distributed system was caused by data centers firewalls that dropped idle connections. After implementing some kind of a keep-alive ping (a periodic execution of the same data aggregation query) the MariaDB ColumnStore 1.1.2 no longer reported broken pipe or lost connection issues.

The distributed multi-server system exhibits the following behavior, when on a combined UM/PM1 node I use mcsmysql to execute the same query (an then quit):

• first execution is fast (~0.3s) and the ExeMgr on UM/PM1 node logs this query in debug.log
• second execution is fast (~0.3s) and the ExeMgr on UM/PM2 node logs this query in debug.log
• third execution is slow (~18s) and the ExeMgr on UM/PM3 node (in another data center) logs this query.

It appears that upon mcsmysql launch on UM/PM1 node there is a round-robin assignment of the UM/ExeMgr node responsible for the query. (If I remain in the mcsmysql prompt that has been assigned to the remote UM/PM3 node, each query execution remains slow.)

This behavior is in agreement with InifiniDB documentation on "Automatic round-robin distribution/scale-out of queries (based on connection id) across all UMs."

It is possible to bind the query execution to UM/ExeMgr on a particular UM/PM node?

The round-robin distribution of queries (based on connection id) across all UMs is hardcoded at
https://github.com/infinidb/infinidb/blob/master/dbcon/execplan/clientrotator.cpp#L238

As a workaround, I use a stored procedure that KILLs it's own connection if CONNECTION_ID%3 equals 2, since this corresponds to queries using ExeMgr in a remote data center.

hi sasha, there is an enhancement to productize this MCOL-499 but as a workaround you can modify the columnstore.xml to force the connection from mariadb to exemgr to be localhost.

1. stop the system using the stopsystem command
2. On PM1, edit the Columnstore.xml. There are sections for ExeMgr1 and ExeMgr2. You can change the IP address for both to 127.0.0.1. Effectively, the round-robin mechanism will send queries to the local node for execution
3. start the system using the start system command

The effect will be that the mysqld process will then always use the same exemgr process on the same host.

Hi dthompson,

Thanks a lot for the guidance. I changed the ExeMgr IP addresses to 127.0.0.1 on all three nodes. Following this change, the MariaDB Columstore system (distributed across two data centers) executes queries stably.