Hello! I use MySQL as my primary datastore and use Elasticsearch to further
index the documents.
My problem is keeping the data in ES in sync with MySQL.
Currently I have two methods in mind:
whenever add or update an entry in MySQL, do the action together in ES.
Do some cron jobs that periodically keep ES in sync with the data in
MySQL.
For method 2 I wonder how can I check if an entry is already indexed in
Elasticsearch. And would it be efficient at all if I have to check every
entry to see if it is updated?
I am new to the technology and I am afraid I had missed some really obvious
and established solutions here. Or otherwise the "normal" way this
situation is handled?
I would do 1/ to have a more near real time search.
Also, I'd the idea that I have an object in memory and I simply push it to MySQL and to ES in the same time. No need to read again the object from MySQL to index it in another process (proposition 2)
That said you could use also a Message Queue in the middle if you want to be able at some point to stop your ES cluster without stopping your application.
This is what I did in the past.
Hello! I use MySQL as my primary datastore and use Elasticsearch to further index the documents.
My problem is keeping the data in ES in sync with MySQL.
Currently I have two methods in mind:
whenever add or update an entry in MySQL, do the action together in ES.
Do some cron jobs that periodically keep ES in sync with the data in MySQL.
For method 2 I wonder how can I check if an entry is already indexed in Elasticsearch. And would it be efficient at all if I have to check every entry to see if it is updated?
I am new to the technology and I am afraid I had missed some really obvious and established solutions here. Or otherwise the "normal" way this situation is handled?
I have done something similar years ago when I was working for a newspaper.
We kept articles in database and full text was done with external program.
There was a trigger the tables with articles that on every change operation
adds record in a queue table. Something like this:
article_id, opetation_type, table_name
Then there was a cron jon every minute that reads from this table and
On delete deletes the entry
On Update deletes the entry and generates new simple page with the new
artice – only title and content and put it on indexer to be indexed
On insert generates new simple page with the new artice – only title
and content and put it on indexer to be indexed
Articles have been placed in some directory like this:
/<root_dir>/<table_name>//.html. Then this path was returned
and easy parsed to generate appropriate link to the artice.
After success removes respective record from the queue and we have near
realtime
This can be done with ES but much easier
best reragards,
Nickolay Kolev
08 януари 2014, сряда, 21:13:35 UTC+2, arthurX написа:
Hello! I use MySQL as my primary datastore and use Elasticsearch to
further index the documents.
My problem is keeping the data in ES in sync with MySQL.
Currently I have two methods in mind:
whenever add or update an entry in MySQL, do the action together in ES.
Do some cron jobs that periodically keep ES in sync with the data in
MySQL.
For method 2 I wonder how can I check if an entry is already indexed in
Elasticsearch. And would it be efficient at all if I have to check every
entry to see if it is updated?
I am new to the technology and I am afraid I had missed some really
obvious and established solutions here. Or otherwise the "normal" way this
situation is handled?
+1 having a queue and consumers between your source of truth and ES is a
great approach. You cab decouple and independently scale ( and stop when
needed as DP said) the different components, minimising impact to your
users.
On 09/01/2014 7:35 AM, "David Pilato" david@pilato.fr wrote:
I would do 1/ to have a more near real time search.
Also, I'd the idea that I have an object in memory and I simply push it to
MySQL and to ES in the same time. No need to read again the object from
MySQL to index it in another process (proposition 2)
That said you could use also a Message Queue in the middle if you want to
be able at some point to stop your ES cluster without stopping your
application.
This is what I did in the past.
Hello! I use MySQL as my primary datastore and use Elasticsearch to
further index the documents.
My problem is keeping the data in ES in sync with MySQL.
Currently I have two methods in mind:
whenever add or update an entry in MySQL, do the action together in ES.
Do some cron jobs that periodically keep ES in sync with the data in
MySQL.
For method 2 I wonder how can I check if an entry is already indexed in
Elasticsearch. And would it be efficient at all if I have to check every
entry to see if it is updated?
I am new to the technology and I am afraid I had missed some really
obvious and established solutions here. Or otherwise the "normal" way this
situation is handled?
In case of MySQL, you can write a listener to the MySQL binlog to
index/delete rows, by assuming your data model provides an 1:1 relationship
between SQL rows and ES docs.
If that is not possible because your model is based on table joins, and you
can live with a single node (non-scalable) solution, you may look at the
JDBC river https://github.com/jprante/elasticsearch-river-jdbc which was
built for demonstration purposes.
For checking if a doc is already indexed, you should use update operation
with the versioning feature of ES. Updates are like indexing behind the
scenes with a previous read operation. It is efficient, because you save an
extra round trip between server and client.
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant
logo are trademarks of the
Apache Software Foundation
in the United States and/or other countries.