MySQL Bugs: #53705: Data memory usage increases while restarting data nodes

Bug #53705	Data memory usage increases while restarting data nodes
Submitted:	17 May 2010 14:23
Reporter:	Geert Vanderkelen	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	mysql-5.1-telco-7.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Data memory usage increases after data nodes got restarted when there are updates ongoing.

Just restarting nodes without any load doesn't show any increase.

How to repeat:
Simple test setup with 2 data nodes, 100M data memory and all default settings.

Test table:

CREATE TABLE t1 (
id INT UNSIGNED NOT NULL,
vc VARCHAR(128),
vclong VARCHAR(1280),
PRIMARY KEY (id)
) ENGINE=NDB DEFAULT CHARSET=latin1;

We fill up the table with random data, 100000 tuples (NDB
API application provided)
The memory usage is already off, as Node 3 is 'more' full, but
that's maybe because of the limited DataMemory?

Node 2: Data usage is 85%(2736 32K pages of total 3200)
Node 2: Index usage is 12%(160 8K pages of total 1312)
Node 3: Data usage is 92%(2960 32K pages of total 3200)
Node 3: Index usage is 12%(160 8K pages of total 1312)

The partition info for table t1 shows that the row count is
approximately the same:

Partition	Row count
1 40023 
0 39977

After running 80000 updates on all rows, data was replaced
with data of the same length. This means that the data itself
doesn't grow but is replaced 'in-place'. The DM and IM did
not increase.

Restarting data node 2, while doing updates:

Node 2: Data usage is 92%(2944 32K pages of total 3200)
Node 2: Index usage is 12%(160 8K pages of total 1312)
Node 3: Data usage is 92%(2960 32K pages of total 3200)
Node 3: Index usage is 12%(160 8K pages of total 1312)

Restarting data node 3, while doing updates

Node 2: Data usage is 92%(2944 32K pages of total 3200)
Node 2: Index usage is 12%(160 8K pages of total 1312)
Node 3: Data usage is 94%(3024 32K pages of total 3200)
Node 3: Index usage is 12%(160 8K pages of total 1312)

Memory usage increased after the restarts.
Doing an additional restart of node 2, nothing changed.
Restarting node 3, again decreased the memory usage:

Node 2: Data usage is 92%(2960 32K pages of total 3200)
Node 2: Index usage is 12%(160 8K pages of total 1312)
Node 3: Data usage is 92%(2944 32K pages of total 3200)
Node 3: Index usage is 12%(160 8K pages of total 1312)

Doing a system restart:

Node 2: Data usage is 85%(2736 32K pages of total 3200)
Node 2: Index usage is 12%(160 8K pages of total 1312)
Node 3: Data usage is 85%(2736 32K pages of total 3200)
Node 3: Index usage is 12%(160 8K pages of total 1312)

Originally found on Linux, reproduced on Mac using MySQL Cluster 7.0.14 (or 7.1.3).

This could maybe be http://bugs.mysql.com/bug.php?id=56829

Could you please please retest wo/ ordered index geert.
I.e change table to 
CREATE TABLE t1 (
id INT UNSIGNED NOT NULL,
vc VARCHAR(128),
vclong VARCHAR(1280),
PRIMARY KEY (id) using hash
) ENGINE=NDB DEFAULT CHARSET=latin1;

Jonas,

Exact same effect without ordered index. Below test is rerun of the original, and then with the same table but USING HASH. All tests while updating the records.

-Geert

With Ordered Index:
--------------------

ndb_mgm> all report mem
Node 2: Data usage is 85%(2736 32K pages of total 3200)
Node 2: Index usage is 3%(160 8K pages of total 5152)
Node 3: Data usage is 85%(2736 32K pages of total 3200)
Node 3: Index usage is 3%(160 8K pages of total 5152)

ndb_mgm> 3 restart

ndb_mgm> all report mem
Node 2: Data usage is 85%(2736 32K pages of total 3200)
Node 2: Index usage is 3%(160 8K pages of total 5152)
Node 3: Data usage is 86%(2752 32K pages of total 3200)
Node 3: Index usage is 3%(160 8K pages of total 5152)

ndb_mgm> 2 restart

ndb_mgm> all report mem
Node 2: Data usage is 91%(2912 32K pages of total 3200)
Node 2: Index usage is 3%(160 8K pages of total 5152)
Node 3: Data usage is 86%(2752 32K pages of total 3200)
Node 3: Index usage is 3%(160 8K pages of total 5152)

ndb_mgm> all restart

ndb_mgm> all report mem
Node 2: Data usage is 85%(2736 32K pages of total 3200)
Node 2: Index usage is 3%(160 8K pages of total 5152)
Node 3: Data usage is 85%(2736 32K pages of total 3200)
Node 3: Index usage is 3%(160 8K pages of total 5152)

Without Ordered Index:
-----------------------

ndb_mgm> all report mem
Node 2: Data usage is 83%(2686 32K pages of total 3200)
Node 2: Index usage is 3%(160 8K pages of total 5152)
Node 3: Data usage is 83%(2686 32K pages of total 3200)
Node 3: Index usage is 3%(160 8K pages of total 5152)

ndb_mgm> 2 restart

ndb_mgm> all report mem
Node 2: Data usage is 90%(2894 32K pages of total 3200)
Node 2: Index usage is 3%(160 8K pages of total 5152)
Node 3: Data usage is 83%(2686 32K pages of total 3200)
Node 3: Index usage is 3%(160 8K pages of total 5152)

ndb_mgm> 3 restart

ndb_mgm> all report mem
Node 2: Data usage is 90%(2894 32K pages of total 3200)
Node 2: Index usage is 3%(160 8K pages of total 5152)
Node 3: Data usage is 90%(2894 32K pages of total 3200)
Node 3: Index usage is 3%(160 8K pages of total 5152)

ndb_mgm> all restart

ndb_mgm> all report mem
Node 2: Data usage is 83%(2686 32K pages of total 3200)
Node 2: Index usage is 3%(160 8K pages of total 5152)
Node 3: Data usage is 83%(2686 32K pages of total 3200)
Node 3: Index usage is 3%(160 8K pages of total 5152)