MySQL Forums
Forum List  »  NDB clusters

OPTIMIZE TABLE not reclaiming memory usage after adding data nodes
Posted by: William Wilson
Date: March 31, 2017 11:49AM

I have a test cluster running on 6 VMs. The management nodes are on their own VMs, while each data node shares its VM with an sql node.

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]	4 node(s)
id=21	@192.168.1.6  (mysql-5.7.17 ndb-7.5.5, Nodegroup: 0, *)
id=22	@192.168.1.7  (mysql-5.7.17 ndb-7.5.5, Nodegroup: 0)
id=23	@192.168.1.8  (mysql-5.7.17 ndb-7.5.5, Nodegroup: 1)
id=24	@192.168.1.9  (mysql-5.7.17 ndb-7.5.5, Nodegroup: 1)

[ndb_mgmd(MGM)]	2 node(s)
id=11	@192.168.1.4  (mysql-5.7.17 ndb-7.5.5)
id=12	@192.168.1.5  (mysql-5.7.17 ndb-7.5.5)

[mysqld(API)]	4 node(s)
id=31	@192.168.1.6  (mysql-5.7.17 ndb-7.5.5)
id=32	@192.168.1.7  (mysql-5.7.17 ndb-7.5.5)
id=33 (not connected, accepting connect from 192.168.1.8)
id=34 (not connected, accepting connect from 192.168.1.9)

There were originally only two data/sql nodes, and I added two more data/sql nodes to test adding them online.

Everything worked well, except that OPTIMIZE TABLE is not reclaiming space as I expected.

mysql> select node_id, memory_type, used, total, (100 * used / total) as percent from ndbinfo.memoryusage;
+---------+---------------------+----------+----------+---------+
| node_id | memory_type         | used     | total    | percent |
+---------+---------------------+----------+----------+---------+
|      21 | Data memory         | 28409856 | 83886080 | 33.8672 |
|      21 | Index memory        |  1720320 | 19136512 |  8.9897 |
|      21 | Long message buffer |   262144 | 67108864 |  0.3906 |
|      22 | Data memory         | 26574848 | 83886080 | 31.6797 |
|      22 | Index memory        |  1441792 | 19136512 |  7.5342 |
|      22 | Long message buffer |   393216 | 67108864 |  0.5859 |
|      23 | Data memory         | 13107200 | 83886080 | 15.6250 |
|      23 | Index memory        |   753664 | 19136512 |  3.9384 |
|      23 | Long message buffer |   393216 | 67108864 |  0.5859 |
|      24 | Data memory         | 13107200 | 83886080 | 15.6250 |
|      24 | Index memory        |   753664 | 19136512 |  3.9384 |
|      24 | Long message buffer |   393216 | 67108864 |  0.5859 |
+---------+---------------------+----------+----------+---------+
12 rows in set (0.03 sec)

Memory usage on node_id 21 remained unchanged after running OPTIMIZE TABLE on all NDBCLUSTER tables outside of the 'mysql' schema. I additionally restarted ndbmtd on node_id 22, and its memory usage dropped only a couple percent, as seen above.

I noticed that MySQL 5.7 documentation no longer lists ndbcluster as supported by the OPTIMIZE TABLE statement (MySQL 5.5 doc lists it as supported). Is this the case?

--------

EDIT:

If it helps, I assumed OPTIMIZE TABLE would reclaim memory on the original data nodes because of two things. First is item #8 of the Basic Procedure for adding data nodes (https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-online-add-node-basics.html):

    "8. Reclaim the space freed on the “old” nodes by issuing, for each NDBCLUSTER table, an OPTIMIZE TABLE statement in the mysql client."

Second is the 5.5 documentation of OPTIMIZE TABLE:

    "OPTIMIZE TABLE is also supported for dynamic columns of in-memory NDB tables."

--------

EDIT 2:

I'm thinking this has to do with fixed versus varsized memory. I don't follow what would make in-memory tables one or the other, but here is the output of `ndb_desc`:

ndb_desc sbtest -d sysbench_test -p --ndb-nodeid=33
-- sbtest --
Version: 33554433
Fragment type: HashMapPartition
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 4
Number of primary keys: 1
Length of frm data: 320
Max Rows: 0
Row Checksum: 1
Row GCI: 1
SingleUserMode: 0
ForceVarPart: 1
PartitionCount: 4
FragmentCount: 4
PartitionBalance: FOR_RP_BY_LDM
ExtraRowGciBits: 0
ExtraRowAuthorBits: 0
TableStatus: Retrieved
Table options:
HashMap: DEFAULT-HASHMAP-3840-4
-- Attributes --
id Unsigned PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR
k Unsigned NOT NULL AT=FIXED ST=MEMORY DEFAULT 0
c Char(120;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY DEFAULT ""
pad Char(60;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY DEFAULT ""
-- Indexes -- 
PRIMARY KEY(id) - UniqueHashIndex
PRIMARY(id) - OrderedIndex
k(k) - OrderedIndex
-- Per partition info -- 
Partition	Row count	Commit count	Frag fixed memory	Frag varsized memory	Extent_space	Free extent_space	
0         	24943    	99964       	10715136         	0                    	0            	0                 	
1        	25089    	25089       	10747904         	0                    	0            	0                 	
2        	25007    	25007       	5373952          	0                    	0            	0                 	
3        	24961    	24961       	5373952          	0                    	0            	0                 	


NDBT_ProgramExit: 0 - OK



Edited 3 time(s). Last edit at 04/06/2017 03:26PM by William Wilson.

Options: ReplyQuote


Subject
Views
Written By
Posted
OPTIMIZE TABLE not reclaiming memory usage after adding data nodes
204
March 31, 2017 11:49AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.