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.