MySQL Bugs: #56829: DataMemory pages used by deleted ordered index entries are not released

Bug #56829	DataMemory pages used by deleted ordered index entries are not released
Submitted:	16 Sep 2010 20:29	Modified:	28 Sep 2010 11:02
Reporter:	Matthew Montgomery	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	ndb-7.1.6	OS:	Any
Assigned to:	Pekka Nousiainen	CPU Architecture:	Any

Description:
DataMemory pages once assigned to ordered indexes are not freed when those indexed rows are deleted.

How to repeat:
-- Nothing up my sleeve --

ndb_mgm> all report mem; 
Node 5: Data usage is 0%(6 32K pages of total 2560)
Node 5: Index usage is 0%(9 8K pages of total 1312)
Node 6: Data usage is 0%(6 32K pages of total 2560)
Node 6: Index usage is 0%(9 8K pages of total 1312)

-- Create table with no ordered indexes -- 

mysql> CREATE TABLE t1 (a INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (a) USING HASH) ENGINE=ndbcluster; 
Query OK, 0 rows affected (0.76 sec)

-- Fill it with some rows -- 

mysql> insert into t1 values(NULL); insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768;

ndb_mgm> all report mem; 
Node 5: Data usage is 4%(105 32K pages of total 2560)
Node 5: Index usage is 17%(226 8K pages of total 1312)
Node 6: Data usage is 4%(105 32K pages of total 2560)
Node 6: Index usage is 17%(227 8K pages of total 1312)

mysql> select count(*) from t1; 
+----------+
| count(*) |
+----------+
|   229376 |
+----------+
1 row in set (0.00 sec)

mysql> delete from t1 limit 32768; delete from t1 limit 32768; delete from t1 limit 32768; delete from t1 limit 32768; delete from t1 limit 32768; delete from t1 limit 32768; delete from t1 limit 32768; delete from t1 limit 32768;
Query OK, 32768 rows affected (0.46 sec)
...

Query OK, 0 rows affected (0.00 sec)

-- Presto! Back to 0% usage -- 

ndb_mgm> all report mem; 
Node 5: Data usage is 0%(6 32K pages of total 2560)
Node 5: Index usage is 0%(11 8K pages of total 1312)
Node 6: Data usage is 0%(6 32K pages of total 2560)
Node 6: Index usage is 0%(11 8K pages of total 1312)

-- Try again with ordered indexes present --

mysql> drop table t1; CREATE TABLE t1 (a INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (a)) ENGINE=ndbcluster;
Query OK, 0 rows affected (0.33 sec)

mysql> insert into t1 values(NULL); insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768; insert into t1 select NULL from t1 limit 32768;

ndb_mgm> all report mem; 
Node 5: Data usage is 6%(176 32K pages of total 2560)
Node 5: Index usage is 17%(226 8K pages of total 1312)
Node 6: Data usage is 6%(176 32K pages of total 2560)
Node 6: Index usage is 17%(227 8K pages of total 1312)

mysql> delete from t1 limit 32768; delete from t1 limit 32768; delete from t1 limit 32768; delete from t1 limit 32768; delete from t1 limit 32768; delete from t1 limit 32768; delete from t1 limit 32768; delete from t1 limit 32768;
Query OK, 32768 rows affected (0.62 sec)
...
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from t1; 
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

-- Whoops, there are some remnants! -- 

ndb_mgm> all report mem; 
Node 5: Data usage is 3%(77 32K pages of total 2560)
Node 5: Index usage is 0%(11 8K pages of total 1312)
Node 6: Data usage is 3%(77 32K pages of total 2560)
Node 6: Index usage is 0%(11 8K pages of total 1312)

Suggested fix:
These pages are reused by new rows that are added to the same table, but the list of "freed" ordered index pages should be released back to DataMemory for use elsewhere when those pages are empty.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/119018

3304 Pekka Nousiainen	2010-09-24
      bug#56829 a02_patch.diff
      the patch

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/119019

3303 Pekka Nousiainen	2010-09-24
      bug#56829 a01_test.diff
      testIndex -n Bug56829

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/119030

3305 Pekka Nousiainen	2010-09-24
      bug#56829 a03_fix.diff
      tests.txt needs blank line at end

Pushed into mysql-5.1-telco-7.0 5.1.47-ndb-7.0.19 (revid:pekka@mysql.com-20100924194505-ubqdlqu9arifmgil) (version source revid:pekka@mysql.com-20100924181907-9bmtnkqdff9b727q) (merge vers: 5.1.47-ndb-7.0.19) (pib:21)

Pushed into mysql-5.1-telco-6.3 5.1.47-ndb-6.3.38 (revid:pekka@mysql.com-20100924121636-sdtfjgo78bpzaknp) (version source revid:pekka@mysql.com-20100924121636-sdtfjgo78bpzaknp) (merge vers: 5.1.47-ndb-6.3.38) (pib:21)

pushed to 6.3.38, 7.0.19 and 7.1.8

Documented bugfix in the NDB-6.3.38, 7.0.19, and 7.1.8 changelogs as follows:

      DataMemory pages, once assigned to ordered indexes, were not ever freed
      after rows belonging to the corresponding indexes were deleted.

Closed.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/119379

3288 Martin Skold	2010-09-29 [merge]
      Merge
      removed:
        cluster_change_hist.txt
      modified:
        mysql-test/collections/default.experimental
        mysql-test/suite/ndb/r/ndb_database.result
        mysql-test/suite/ndb/t/ndb_database.test
        sql/ha_ndbcluster.cc
        sql/ha_ndbcluster.h
        sql/ha_ndbcluster_binlog.cc
        sql/handler.cc
        sql/handler.h
        sql/sql_show.cc
        sql/sql_table.cc
        storage/ndb/include/kernel/GlobalSignalNumbers.h
        storage/ndb/include/kernel/signaldata/FsReadWriteReq.hpp
        storage/ndb/include/mgmapi/mgmapi.h
        storage/ndb/include/ndbapi/NdbDictionary.hpp
        storage/ndb/src/kernel/blocks/ERROR_codes.txt
        storage/ndb/src/kernel/blocks/dbdict/Dbdict.cpp
        storage/ndb/src/kernel/blocks/dbdih/DbdihMain.cpp
        storage/ndb/src/kernel/blocks/dblqh/Dblqh.hpp
        storage/ndb/src/kernel/blocks/dblqh/DblqhMain.cpp
        storage/ndb/src/kernel/blocks/dbtup/Dbtup.hpp
        storage/ndb/src/kernel/blocks/dbtup/DbtupIndex.cpp
        storage/ndb/src/kernel/blocks/dbtup/DbtupMeta.cpp
        storage/ndb/src/kernel/blocks/dbtux/Dbtux.hpp
        storage/ndb/src/kernel/blocks/dbtux/DbtuxBuild.cpp
        storage/ndb/src/kernel/blocks/dbtux/DbtuxMaint.cpp
        storage/ndb/src/kernel/blocks/dbtux/DbtuxNode.cpp
        storage/ndb/src/kernel/blocks/dbtux/DbtuxTree.cpp
        storage/ndb/src/kernel/blocks/ndbfs/AsyncFile.cpp
        storage/ndb/src/kernel/blocks/ndbfs/AsyncFile.hpp
        storage/ndb/src/kernel/blocks/ndbfs/Ndbfs.cpp
        storage/ndb/src/kernel/blocks/ndbfs/Ndbfs.hpp
        storage/ndb/src/kernel/blocks/ndbfs/VoidFs.cpp
        storage/ndb/src/kernel/blocks/suma/Suma.cpp
        storage/ndb/src/kernel/blocks/suma/Suma.hpp
        storage/ndb/src/kernel/main.cpp
        storage/ndb/src/ndbapi/DictCache.cpp
        storage/ndb/src/ndbapi/DictCache.hpp
        storage/ndb/src/ndbapi/NdbDictionary.cpp
        storage/ndb/src/ndbapi/NdbDictionaryImpl.cpp
        storage/ndb/src/ndbapi/NdbDictionaryImpl.hpp
        storage/ndb/test/include/NdbRestarter.hpp
        storage/ndb/test/ndbapi/testIndex.cpp
        storage/ndb/test/ndbapi/testRestartGci.cpp
        storage/ndb/test/ndbapi/testSystemRestart.cpp
        storage/ndb/test/run-test/daily-basic-tests.txt
        storage/ndb/test/src/NdbRestarter.cpp