MySQL Forums
Forum List  »  NDB clusters

Re: Cannot delete rows from a NDB table
Posted by: Mikael Ronström
Date: October 28, 2017 03:46AM

When a transaction is executed it uses a number of resources.
In NDB many of those resources are set to a preconfigured resource
(we're working on moving many of those to a common memory resource pool
in 7.6).

So for example a transaction requires one operation record
in the transaction coordinator for each row change and for reads with locks
(these are configured with MaxNoOfConcurrentOperations), it needs one
transaction record for each record (configured by MaxNoOfConcurrentTransactions).
In addition in each row you update a foreign key relation, a unique key relation
a trigger is fired that requires a trigger record
(configured by MaxNoOfFiredTriggers).

This trigger record
is released once the trigger has fired. Triggers fire when all triggers
for an update has been received and the update is prepared in all replica.

One exception is foreign key checks that are executed right before the
end of the transaction to verify that the changes are not violating any
foreign key constraints. We execute a special operation during the commit
phase that will fire all those pre-commit constraint checks (this can also
happen in some cases for unique key relations).
When a trigger fires it will use an operation
record since the trigger mostly is a new update of some sort.

All operation records, the transaction
record is held until the end of the transaction.

The number configured is actually per tc thread. So the default setting for
MaxNoOfFiredTriggers is 4000 per tc thread.

There are few more reasons why triggers are fired. If you are updating a
fully replicated there will be a trigger fired. If you are performing an
ALTER TABLE t1 REORGANIZE PARTITION on a table it will fire a reorganize
trigger.

In addition the trigger stores potential key values, after values and
before values in a memory space configured by TransactionBufferMemory.
This memory is released as soon as the trigger fired. This defaults to
1 MByte per tc thread.

So all uses of trigger records are short-lived. So this is why this resource
is not normally necessary to configure.

One reason you could see problems with it could be that you have large
transactions committing and firing off many pre-commit constraint checks.
The other reason is that you simply have very many parallel activities
going on and thus each operation has to queue for a while.

Anyways this error is always a temporary error. It is always a good idea
to program your DBMS applications to handle temporary errors. So a retry
should be sufficient. If the problem often occurs it is probably due to
overload problems at peak loads.

One more thing to consider is that a delete combined with foreign keys
that use cascading actions, is that there can be a tremendous amount of
cascading actions firing in a very short time.

Hope this helps.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Cannot delete rows from a NDB table
648
October 28, 2017 03:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.