0

I am running a fast growing NDB cluster and I have limited (8GB) of physical memory on each of the two data nodes. I am worried that I will be running out of Data Memory so I started removing old data. However, the deletion doesn't show any significant reduction in the usage stats. Any ideas/explanation why?

Here is the stats before:

ndb_mgm> all report mem
Node 11: Data usage is 44%(85073 32K pages of total 192000)
Node 11: Index usage is 22%(20428 8K pages of total 89632)
Node 12: Data usage is 43%(83800 32K pages of total 192000)
Node 12: Index usage is 22%(20425 8K pages of total 89632)

and here are the stats after I removed around a third of the data.

ndb_mgm> all report mem
Node 11: Data usage is 41%(80557 32K pages of total 192000)
Node 11: Index usage is 15%(14223 8K pages of total 89632)
Node 12: Data usage is 41%(79274 32K pages of total 192000)
Node 12: Index usage is 15%(14226 8K pages of total 89632)

As you see, the Index usage shrunk significantly more than the Data usage. Is there anything else that needs to be done to reclaim the space of the data that I removed?

P.S. I deleted the rows from the tables with larger row sizes mostly. When I compared the table stats I noticed this on one of the tables I cleaned up:

        Rows   | Avg_row_length | Data_length
Before 1858558 |             88 |   399147008
After   241832 |             88 |   398884864

Why did the data length stay high?

Ehsan
  • 175
  • 2
  • 8

2 Answers2

3

If possible, you should re-title your question: the title references Innodb, but your question is about NDB.

Anyway, when you do deletes in NDB it just frees up memory space for future use by that table. If you want to really free it up you can do a rolling restart of the data nodes or an optimize table (depending on your version). See: http://docs.oracle.com/cd/E17952_01/refman-5.1-en/mysql-cluster-limitations-limits.html

Relevant quote: "A DELETE statement on an NDB table makes the memory formerly used by the deleted rows available for re-use by inserts on the same table only. However, this memory can be made available for general re-use by performing a rolling restart of the cluster. See Section 17.5.5, “Performing a Rolling Restart of a MySQL Cluster”.

Beginning with MySQL Cluster NDB 6.3.7, this limitation can be overcome using OPTIMIZE TABLE. See Section 17.1.6.11, “Previous MySQL Cluster Issues Resolved in MySQL 5.1, MySQL Cluster NDB 6.x, and MySQL Cluster NDB 7.x”, for more information."

Hope that helps.

drogart
  • 436
  • 2
  • 2
1

Innodb does not hand over the free space to the filesystem, instead it will be used by future inserts. If you are using the innodb_file_per_table parameter in your configuration you could reclaim the free space by issuing an OPTIMIZE TABLE. If not the only way that I know is to export the db, stop mySQL and manually delete the data files, then import db again.

There's a very similar question on SO.

Community
  • 1
drcelus
  • 1,233
  • 4
  • 14
  • 27