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

[16 Sep 2010 20:29] Matthew Montgomery
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.
[24 Sep 2010 11:09] Bugs System
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
[24 Sep 2010 11:17] Bugs System
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
[24 Sep 2010 12:18] Bugs System
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
[24 Sep 2010 19:47] Bugs System
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)
[24 Sep 2010 19:48] Bugs System
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)
[28 Sep 2010 10:36] Jonas Oreland
pushed to 6.3.38, 7.0.19 and 7.1.8
[28 Sep 2010 11:02] Jon Stephens
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.
[29 Sep 2010 10:55] Bugs System
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