Support for MySQL read replicas

[mkalinowski-fandom]

Hello! We're introducing Kratos to our infrastructure and because of our scale, we simply can't use the single MySQL database for all queries. Generally, we use primary-replica replication and configure apps to use primary for writes and replicas for reads or implement such an approach if it's our own code. It seems that such configuration is not possible in Kratos since the one and only data source can be configured for it.

Do you plan to implement such a feature in the future? What do you generally think about this approach to configure separate read- and write-databases for Kratos? Do you know whether someone tried to use some alternative approaches like using ProxySQL to route queries to appropriate databases and how it works with Kratos?

[aeneasr]

It is expected to use something like HAProxy for load balancing as this is in the domain of database operations and not Ory Kratos! This works well everywhere, is the standard for modern db stacks, is generalizable, and means you can optimize it for your env independent of the software (eg Kratos) you use.

[aeneasr]

The problem is also that every DB solves it differently and we can’t support multiple HA DB type set ups which also depend on how HA is achieved for every individual set up. For example, CRDB has no such concept. PostreSQL solves it differently than MySQL. AuroraDB again does it differently. Unfortunately the tooling in Go also has no support in it’s DBAL. That is why we can’t support (due to amount of work) this at the moment.

{ad}If you are worried about scale, there’s always Ory Cloud where we take care of all of this stuff in our own opinionated way :){/ad}

[harnash]

True. DB setups vary quite a lot but maybe something like https://gorm.io/docs/dbresolver.html is worth considering? If one needs it is there and allows quite a flexibility how the traffic is routed. Again the purpose of this thread is to gauge the long/medium term goal for this kind of challenge and we might contribute to the solution if that will bring us closer to what we need.

[aeneasr]

I think maintainers of gobuffalo would love to see this type of capability! Knowing the internals though a bit it might be quite a piece of work. It might make sense for people to share their HA set ups in the docs for now? For ourselves we just rely on CRDB‘s internal routing so we don’t have a lot of experience with Postres/MySQL unfortunately.

[harnash]

OK. We'll post our setup here and let's keep it open for a while so others can chime in. Keeping kind of FAQ with viable setups sounds like a good idea until more concrete/general solution is worked out.

[aeneasr]

SGTM :)

[noells]

Hello! We are having this very same issue and we were wondering if a scenario with 2 Kratos deployments sharing the same DB would work OK. Let me explain a bit deeper:

• Kratos 1 will have the DB write/read endpoint configured as data source and will be able to write
• Kratos 2 will be configured with DB read only endpoint

With this in mind, we could do some networking to only forward read only queries to Kratos 2. Would that be safe? Our main concerns

• does Kratos perform any write operations in apparently read only request? Something like update a last_accessed field or similar stuff
• any background job that wouldn't work with this approach?

To add more context to this: we are using oathkeeper with Kratos and we are progressively starting to migrate to this new system. We are worried with the performance as we have a high-load system and every authenticated request will, theoretically, end in a request from oathkeeper to Kratos, hence the need for the read only Kratos scenario.

cc/ @aeneasr

cc2/ Hey @harnash , may I ask how did you finally solved this? Thanks!

[harnash]

Hey @noells.

I think you will run into problems with this setup. There are some endpoints that create records though they are GETs (i.e. /selfservice/*/browser.
We are using ProxySQL which does the routing based on the queries Kratos generates. We still have issues with some sessions being dropped but we are not 100% sure if that is related to ProxySQL or not.
Our ProxySQL config looks more or less like this:

ysql_servers =
    (
        { address="${PROXYSQL_MYSQL_SERVERS_MASTER_ADDRESS}", port=3306, hostgroup=${PROXYSQL_PRIMARY_HOSTGROUP}, max_connections=100, compression=1 },
        { address="${PROXYSQL_MYSQL_SERVERS_SLAVE_ADDRESS}", port=3306, hostgroup=${PROXYSQL_REPLICA_HOSTGROUP}, max_connections=100, compression=1 }
    )

    mysql_users =
    (
        { username="${PROXYSQL_MYSQL_USERS_USERNAME}", password="${PROXYSQL_MYSQL_USERS_PASSWORD}", default_hostgroup=1, active=1}
    )

    mysql_query_rules =
    (
        # queries to a master
        {
            rule_id=1
            active=1
            match_pattern="^SELECT .* FOR UPDATE"
            destination_hostgroup=${PROXYSQL_QUERY_WRITE_HOSTGROUP}
            apply=1
        },
        # queries to a replica/slave
        {
            rule_id=2
            active=1
            match_pattern="^SELECT .* FROM (courier_messages|networks|identity_recovery_addresses|identity_verifiable_addresses|identities|identity_credential_types|identity_credential_identifiers|schema_migration)"
            destination_hostgroup=${PROXYSQL_QUERY_SELECT_HOSTGROUP}
            apply=1
        },
        {
            rule_id=3
            active=1
            match_pattern="^SELECT .* FROM sessions"
            destination_hostgroup=${PROXYSQL_QUERY_SESSIONS_HOSTGROUP}
            apply=1
        },
        # queries to a master
        {
            rule_id=4
            active=1
            match_pattern=".*"
            destination_hostgroup=${PROXYSQL_QUERY_OTHER_HOSTGROUP}
            apply=1
        }
    )

    mysql_replication_hostgroups =
    (
        { writer_hostgroup=1, reader_hostgroup=2, comment="MySQL Replication 5.7" }
    )

where

PROXYSQL_QUERY_SESSIONS_HOSTGROUP : 1,
PROXYSQL_PRIMARY_HOSTGROUP : 1,
PROXYSQL_REPLICA_HOSTGROUP : 2,
PROXYSQL_QUERY_SELECT_HOSTGROUP : 2,
PROXYSQL_QUERY_WRITE_HOSTGROUP : 1,
PROXYSQL_QUERY_OTHER_HOSTGROUP : 1,

We've just recently switch all queries hitting session table to use primary replica to minimise chance that user will be randomly logged out due a to some replication issues.

Dealing with primary/replica in the code would be much more precise than dealing with extra layer between Kratos and DB but it is not trivial and requires some extra tooling around that (waitForSlaves() etc.).

[noells]

Thanks a lot for sharing @harnash , really helpful!!