Keeping an RDBMS in sync with Go

[crashkid]

I have been given a project to query various API’s in our environment like CloudStack and Kubernetes and update an RDBMS database (currently mysql 8) that is used for reporting and capacity planning. There is an existing LAMP-based portal being used for reporting so I’m stuck just augmenting what they have for now; long term I plan to move this into Kubernetes and use something like elasticsearch or solr instead of an RDBMS.

Anyhooo… for the immediate term.

What is the best approach to keeping a database current where you have a lot of churn on rows - some being updated, new ones being inserted and ones being deleted. Updating and inserting is pretty straight forward, I was more curious the best approach to deleting rows that reference objects that no longer exist in our environment.

Is there a “slicker” approach than this for processing objects that need to be deleted:

1. run my collection job and get current list of objects
2. query database for list of objects
3. delete items that don’t exist in the results from step 1

This is a very costly approach and curious if there was a better approach, or library or something? Keeping in mind I can’t change the database at this time.

Much thx.
BH

[Christophe_Meessen]

It is unclear what you expect as answer. It looks like you have to use MySQL.

My advise would be to group multiple insertions, updates or deletion in each SQL requests. This really speed things up.

I had to insert logging records in a MySQL database. What I did is queued the records, and once the queue reached 200 records, I would insert them. I also had a timer to send whatever is in the queue at periodic time intervals. This time interval can be long, depending on the latency of database update you can tolerate.

It’s worth the effort to implement this from the start. With a single insertion, update or delete operation per SQL request, you will get poor performances. But it may be good enough for your use case. It’s up to you.