Query that can be used to view Region info:
SELECT r.name region_name,
r.type,
z.zoneid,
z.name zone_name,
n.nodeid,
n.hostname
FROM system.zones z,
system.regions r,
system.nodeinfo n
WHERE z.region = r.region
AND n.zone = z.zoneid
ORDER BY r.type;
clieu I don't have a running Transylvania setup yet, so I have not tried things out, but apparently all nodes from all regions are visible in system.nodeinfo. Is that correct?
In order to ignore the nodes that are not in the same region it itself is in, MaxScale needs to know the region it is in. Can that be figured out at runtime or does it have to be a configuration setting of MaxScale?
The region changes mean that MaxScale needs to alter its behaviour depending on the version of Xpand. How can MaxScale detect that it is Transylvania it is talking to? If regions are not used, will that query return the relevant data or does MaxScale need to handle that case separately?
> all nodes from all regions are visible in system.nodeinfo. Is that correct?
Yes
> In order to ignore the nodes that are not in the same region it itself is in, MaxScale needs
> to know the region it is in. Can that be figured out at runtime or does it have to be a
> configuration setting of MaxScale?
I'm not sure. Would it make sense for only the Primary to use the boot strap process, and MaxScale in the secondary region should be manually configured? What would runtime configuration look like?
> The region changes mean that MaxScale needs to alter its behaviour depending on the
> version of Xpand. How can MaxScale detect that it is Transylvania it is talking to? If
> regions are not used, will that query return the relevant data or does MaxScale need to
> handle that case separately?
The Transylvania release number (afaik) will be 23.08.xx. You could key off that (or just check if the version is Xpand 5.3, 6.x)
> Would it make sense for only the Primary to use the boot strap process, and MaxScale in the
> secondary region should be manually configured?
If the primary goes down and Xpand promotes the secondary to primary, what happens when the old primary comes back up again? Does it become the secondary or is the current primary demoted back to being secondary so that the old primary again becomes the current primary?
If the secondary only temporarily becomes the primary, that could support MaxScale being configured differently in the primary and secondary cases. If the primary/secondary roles can switch permanently (or until the next region failure) then I think MaxScale should be configured in the same manner everywhere.
> What would runtime configuration look like?
Without being able to figure out the region autonomously, there would have to be a specific region setting.
[Monitor]
|
type=monitor
|
module=xpandmon
|
...
|
region=us-east-1
|
The downside of that is that the configuration of each MaxScale instance would have to be different (different region entry). Although I suppose they anyway have to be as you probably want to use nodes in your region as bootstrap nodes.
If the region can reliably be figured out from within an instance, then something like this would suffice.
[Monitor]
|
type=monitor
|
module=xpandmon
|
...
|
use_regions=true
|
However, the way you figure out the region from within an instance seems a bit shady - https://stackoverflow.com/questions/4249488/find-region-from-within-an-ec2-instance - so being explicit about it seems like a better initial choice. At a later stage region=auto could mean that the region is figured out by MaxScale itself.
clieu It seems to me that MaxScale can ignore the zones completely and treat all nodes in a region alike, irrespective of the zone they are in. Is that correct?
What is the layout of the system.regions table? I'll create that manually in my docker xpand-single setup and then I can do most of the work with that.
Currently when MaxScale refreshes its view of the cluster it executes
SELECT ni.nodeid, ni.iface_ip, ni.mysql_port, ni.healthmon_port, sn.nodeid
|
FROM system.nodeinfo AS ni
|
LEFT JOIN system.softfailed_nodes AS sn ON ni.nodeid = sn.nodeid;
|
Conceptually I want to add WHERE region = 'this_region' to that query.
Sorry, I'm not great at JIRA formatting ;p
[root@alpo002 ~]# mysql
|
Welcome to the MariaDB monitor. Commands end with ; or \g.
|
Your MySQL connection id is 31746
|
Server version: 5.0.45-Xpand-transylvania-18705
|
|
|
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
|
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
|
MySQL [(none)]> select * from system.regions;
|
+---------------------+-----------+-----------+
|
| region | name | type |
|
+---------------------+-----------+-----------+
|
| 7255261506333243396 | us-east-2 | PRIMARY |
|
| 7255261506427736068 | us-east-1 | SECONDARY |
|
| 7255261506522226692 | us-west-2 | OBSERVER |
|
+---------------------+-----------+-----------+
|
3 rows in set (0.00 sec)
|
|
|
MySQL [(none)]> show create table system.regions;
|
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| regions | CREATE TABLE `regions` (
|
`region` oid not null,
|
`name` varchar(256) CHARACTER SET utf8 not null,
|
`type` varchar(65535) CHARACTER SET utf8 not null,
|
PRIMARY KEY (`region`) /*$ PAYLOAD (`name`,`type`) */,
|
UNIQUE KEY `_regions_ind_1` (`name`)
|
) CHARACTER SET utf8 COLLATE utf8_general_ci /*$ AUTO_STATISTICS=NONE */
|
|
|
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
Thank clieu! No problem with the formatting, I can tune later 
Could you do the same with system.zones and also system.nodeinfo.
Ok, so without regions, when MaxScale refreshes its view of the cluster it executes:
SELECT ni.nodeid, ni.iface_ip, ni.mysql_port, ni.healthmon_port, sn.nodeid
|
FROM system.nodeinfo AS ni
|
LEFT JOIN system.softfailed_nodes AS sn ON ni.nodeid = sn.nodeid;
|
With regions, when MaxScale should only consider the nodes that are in its region, I think the equivalent query is:
SELECT nir.nodeid, nir.iface_ip, nir.mysql_port, nir.healthmon_port, sn.nodeid
|
FROM (SELECT ni.nodeid, ni.iface_ip, ni.mysql_port, ni.healthmon_port, z.region
|
FROM system.nodeinfo AS ni LEFT JOIN system.zones AS z ON ni.zone = z.zoneid) AS nir
|
LEFT JOIN system.softfailed_nodes AS sn ON nir.nodeid = sn.nodeid
|
WHERE nir.region = the_region;
|
clieu could you verify that the query does the right thing? I am still not able to run Transylvania myself.