1

I am considering using Galera Active-Active with five MySQL nodes. The nodes sit behind a load balancer, and the application can write to any of the nodes.

My application reads / writes / updates to the same table roughly 1000 times per second. The writes are usually around 100k of data.

The typical DB logic will be like this:

(1) do a select to see if the data exists in the database

(2) if not, insert the data

(3) more processing

(4) update some of the data

The select (1) will happen around 75 million times per day. The inserts (2) and updates (4) around 1 million times per day.

A. Am I right in thinking Galera will be constantly locking the table, causing writes and updates to be slow?

B. Am I right in thinking the synchronisation of data between nodes may take seconds, or longer, so there is a risk a select (1) will report the data hasn't yet been inserted, but actually an insert (2) has already been done, but it just hasn't been synchronised with all nodes yet?

Tom Brock
  • 920
  • 7
  • 29

1 Answers1

1

First, be aware that (1) can only be advisory. That is, (1) might say "data does not exist", but then (2) will find a that the data is there. Or that discovery won't happen until the COMMIT?

Please add to your list any START TRANSACTION and COMMIT. Meanwhile, I will assume the entire 4 steps are in a single transaction, although I would suggest putting (1) outside the transaction.

How far apart (ping time) are the nodes? If they are in the same building, the sync may take only milliseconds. (I say 'may' because the 1K actions per second is likely to be somewhat stressful.)

I think this may be best:

(1) See if row exists -- 98% of the time, this will avoid doing the rest.
BEGIN;
(2), (3), (4);  -- check after each step; 1% of collisions will be caught here
COMMIT;  -- again check; still another 1% get caught here.

That is, abandon striving for perfection (a single test catches 100%). Instead, play a numbers game such that you usually do-the-optimal-thing, but then catch the odd case infrequently enough to not hurt performance noticeably.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Thanks for your reply @RickJames. All the database queries (1 - 4) are done independently, and are not part of a single transaction. Actually they are done by different servers. So one server will be doing selects only, one will be doing inserts, and one will be doing updates. The nodes are on the same private network. Can you clarify what you mean by "check after each step". What exactly would I be checking for? Thanks again. – Tom Brock Nov 06 '18 at 07:13
  • @TomBrock - I worry that between the time a check (select) is done and an insert is done, some other process might have already added the row. In this case, the could be a "duplicate key" error that should be checked for. I need to know more about the code to be less vague on my answer. – Rick James Nov 06 '18 at 15:52