MySQL Bugs: #16869: Cluster: MySQLD Deletes should be batched to default of MaxNoOfLocalOperations

Bug #16869	Cluster: MySQLD Deletes should be batched to default of MaxNoOfLocalOperations
Submitted:	28 Jan 2006 16:33	Modified:	16 Mar 2006 10:36
Reporter:	Jonathan Miller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.6-alpha	OS:	Linux (Linux 32 Bit OS)
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
So we allow a customer to build a database with RAM Limit of 2GB of index + data, but then ehy are not able to delete rows from a table with more then 200,000 rows in it with out reducing index and data ram size.

We have to figure some way of batching deletes done from a mysql client to where it does not fail with out of MaxNoOfLocalOperations.

[DB DEFAULT]
NoOfReplicas: 2
IndexMemory: 500M
DataMemory: 1300M
BackupMemory: 64M
MaxNoOfLocalOperations: 300000
MaxNoOfTables: 200
StopOnError: 1
MaxNoOfConcurrentScans: 100
DataDir: /space/run
DiskPageBufferMemory: 500M
#DiskPageBufferMemory: 4M
MaxNoOfConcurrentOperations: 500000

Database changed
mysql> select count(*) from history;
+----------+
| count(*) |
+----------+
|   271634 |
+----------+
1 row in set (0.00 sec)

mysql> delete from history;
ERROR 1297 (HY000): Got temporary error 1217 'Out of operation records in local data manager (increase MaxNoOfLocalOperations)' from NDBCLUSTER

Time: Saturday 28 January 2006 - 17:27:10
Status: Permanent error, external action needed
Message: Memory allocation failure, please decrease some configuration parameters (Configuration error)
Error: 2327
Error data: ArrayPool<T>::setSize malloc failed
Error object: ../../../../../../storage/ndb/src/kernel/vm/ArrayPool.hpp line: 245 (block: CMVMI)
Program: /home/ndbdev/jmiller/builds/libexec/ndbd
Pid: 26980
Trace: /space/run/ndb_2_trace.log.8
Version: Version 5.1.6 (alpha)

How to repeat:
See above

The problem is that "delete from" is transactional.
You should use "truncate table" if you just want to delete all rows wo/ transactions.
(Or it's probably faster to drop/recreate with that many rows)

I would perfer to allow management decide what will be and what won't be fixed. Thanks.

Jon, could you please review the documetation on this limitation and see if we can improve? Maybe give it a section to itself. Or added the limitation information with work arounds to the config section talking about this setting?

PS: Will send you the Local version one too.

My thoughts where to move, or copy or have something simular to: (from
Limitations)

Note that bulk loading, TRUNCATE TABLE, and ALTER TABLE are handled as special
cases by running multiple transactions, and so are not subject to this
limitation.

to http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-db-definition.html
Under "Transaction Parameters" -> MaxNoOfLocalOperations

As has been pointed out, this is already documented under Limitations.

Actually you are wrong on this one. The limitations states nothing about MaxNoOfLocalOperations

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

Updated 4.1/5.0/5.1 Cluster Limitations sections.

Hi, Jon

-- quote
The maximum number of transactions that can be performed is set using the configuration parameters MaxNoOfConcurrentOperations and MaxNoOfLocalOperations. Note that bulk loading, TRUNCATE TABLE, and ALTER TABLE are handled as special cases by running multiple transactions, and so are not subject to this limitation.
-- quote

This is 100% wrong.
No of transactions is not in any way affected by MaxNoOfConcurrentOperations (or Local)

Any transaction can not insert/update/delete more the MaxNoOfConcurrentOperations.
Since this is somewhat complicated, i think you should keep it short and refer to documenation instead.

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).