3 ms·

just asking for some info, but how do you make sure that multiple of your db systems are in sync (specifically interested in MySql and elasticsearch)? Hope it

just asking for some info, but how do you make sure that multiple of your db systems are in sync (specifically interested in MySql and elasticsearch)? Hope it's alright to ask you that.

sacheendra 10y ago
If you need any kind of consistency guarantee, I think you would need to use some kind of distributed transactions. If its not, you could tail the MySQL log and have a process making the same changes to elasticsearch. The elasticsearch may lag behind if there are problems.
jeffasinger 10y ago
Where I work we use several different MySQL replicas in production, where we don't expect them to be in sync. So long as the source of truth (Master MySQL node) is up to date, it's okay. For example, if we show a user how much money is in their account on every page, we can run query that on a replica, since it's fine if this is a few seconds delayed. However, immediately after an action changed their balance, on a confirmation screen, we'd want to show the value from Master. It's entirely possible that any place elasticsearch is being used just don't need consistency.
rthrfrd 10y ago
In the case of ES the short answer is; we don't. We have fault tolerance in our replication system to guarantee eventual consistency instead. I would say using ES as a consistent source of data isn't really playing to its strengths so we don't use it that way. The consistency you want is determined at read time: If you need consistency then hit MySQL, but for our use case that almost never happens as eventual consistency is usually instantaneous enough. Our other tool is to decouple lookup (which objects to fetch) and population (what data to return for each object). You can mix and match, e.g. do a lookup against an inconsistent ES but still get consistent objects by populating from MySQL (or vice versa). As others have alluded to it depends entirely on the requirements for the result set.
bpicolo 10y ago
There are actually a few strong solutions out there for Mysql, most starting with change data capture like: https://github.com/shyiko/mysql-binlog-connector-java https://github.com/shyiko/mysql-binlog-connector-java (I link that one in particular because he links to alternatives right in his readme!) Pgsql is a bit harder, but if I needed to start somewhere it would be with: https://github.com/debezium/debezium https://github.com/debezium/debezium or https://github.com/confluentinc/bottledwater-pg https://github.com/confluentinc/bottledwater-pg These are the start of pretty sophisticated solutions where you need super real-time elasticsearch indexes and can bring up infra like Kafka. For many applications, queueing an update when something hits your ORM to update, with the hourly/daily refresh is pretty satisfactory.

just asking for some info, but how do you make sure that multiple of your db systems are in sync (specifically interested in MySql and elasticsearch)? Hope it's alright to ask you that.

sacheendra 10y ago

If you need any kind of consistency guarantee, I think you would need to use some kind of distributed transactions. If its not, you could tail the MySQL log and have a process making the same changes to elasticsearch. The elasticsearch may lag behind if there are problems.

jeffasinger 10y ago

Where I work we use several different MySQL replicas in production, where we don't expect them to be in sync. So long as the source of truth (Master MySQL node) is up to date, it's okay. For example, if we show a user how much money is in their account on every page, we can run query that on a replica, since it's fine if this is a few seconds delayed. However, immediately after an action changed their balance, on a confirmation screen, we'd want to show the value from Master. It's entirely possible that any place elasticsearch is being used just don't need consistency.

rthrfrd 10y ago

In the case of ES the short answer is; we don't. We have fault tolerance in our replication system to guarantee eventual consistency instead. I would say using ES as a consistent source of data isn't really playing to its strengths so we don't use it that way. The consistency you want is determined at read time: If you need consistency then hit MySQL, but for our use case that almost never happens as eventual consistency is usually instantaneous enough. Our other tool is to decouple lookup (which objects to fetch) and population (what data to return for each object). You can mix and match, e.g. do a lookup against an inconsistent ES but still get consistent objects by populating from MySQL (or vice versa). As others have alluded to it depends entirely on the requirements for the result set.

bpicolo 10y ago

There are actually a few strong solutions out there for Mysql, most starting with change data capture like: https://github.com/shyiko/mysql-binlog-connector-java https://github.com/shyiko/mysql-binlog-connector-java (I link that one in particular because he links to alternatives right in his readme!) Pgsql is a bit harder, but if I needed to start somewhere it would be with: https://github.com/debezium/debezium https://github.com/debezium/debezium or https://github.com/confluentinc/bottledwater-pg https://github.com/confluentinc/bottledwater-pg These are the start of pretty sophisticated solutions where you need super real-time elasticsearch indexes and can bring up infra like Kafka. For many applications, queueing an update when something hits your ORM to update, with the hourly/daily refresh is pretty satisfactory.