2

We have a 3 node MySQL database cluster than runs on Percona. I have a stored procedure that inserts a lot of rows in one hit, potentially over a million rows.

I've been warned (by our DBA) this might cause the cluster to crash, but I can't find anything online regarding this or that would suggest this might happen.

Can anyone provide some insight?

Improve this question
5
  • With "3 node MySQL database cluster than runs on Percona", do you mean Percona XtraDB Cluster aka Galera?
    – jynus
    Apr 8, 2015 at 12:02
  • I don't know if it will hit some limit and "crash", but it seems feasible that there would be some kind of trouble. Are all million rows in a single transaction? A single INSERT?
    – Rick James
    Apr 8, 2015 at 12:26
  • @jynus yes that's what I mean!
    – Ashley
    Apr 8, 2015 at 12:47
  • @RickJames Yes, it does an INSERT INTO table SELECT * FROM tmp_table.
    – Ashley
    Apr 8, 2015 at 12:47
  • You're going to hurt the cluster with large inserts like that. Crashing, perhaps but likely flow control will cripple you.
    – eroomydna
    Apr 8, 2015 at 13:39

1 Answer 1

Reset to default
2

Some general considerations, first:

  1. MySQL, and I am talking about InnoDB specifically, is optimized for OLTP loads, specially for good performance for point SELECTS and writes. That doesn't mean that you cannot do bulk inserts (I get 600 000 row insertions/second on my desktop-grade SSD), but that has an impact on the performance of other operations, mainly by taking extra locks and extra resources on transaction procesing/index merging/etc.

  2. Large transaction processing overhead exacerbates when distributing those over the network on a tight clustering solution (for a MySQL model). The fact that you have to load synchronously a large number of rows over several nodes usually requires a large amount of memory and makes them a problem for tightly-attached-node solutions such as NDB and Galera.

Now into Galera specifics:

  • The first limitation that you will face is that by default, ws_max_size_rows is 128K (128 000 rows) and wsrep_max_ws_size is 1GB. Although those values are configurable, the load process will directly fail if the transactions are larger than the values configured.
  • I wouldn't be surprised that the cluster could crash - You can see an example here that happened during an import- ending up with 2 nodes getting an OOM even if that specific problem was later fixed. Extra memory is needed in order to certify every transaction among all nodes before actually executing it.
  • If you want the cluster to work adequately, I would recommend loading the files in chunks, using several transactions. There are event tools like [pt-fifo-split][1] that will help you do that. As an alternative, you could load those files individually on each node one at a time while they are temporally desynced.

This is one question that should not be argued about- If you have a production cluster, you should have a test cluster in which you can try your thesis first. Best case scenario, you will get a horrible performance/a deadlock. Normally, you will run out of memory and 1+ nodes will crash, leading to a full cluster crash.

To know more about the reasons behind galera behavior, and how to avoid downtime at the cost of sacrificing performance, you should read:

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.