Description:
I have reported this on the forum and mailing list but received no feedback.
My cluster is working fine and I can create schemes, add tables then data etc etc. I can even add a logfile and tablespace groups without any errors and the files appear in my data storage location.
%%%%%%%%%%%%%
%%system configuration
%%%%%%%%%%%%%
1 mgm FC6 Mysql 5.1.18
2 API FC6 Mysql 5.1.18
4 NDB FC6 Mysql 5.1.18 4G RAM
All installed via the latest binaries.
%%%%%%%%%%%%%
%%config.ini
%%%%%%%%%%%%%
[NDBD DEFAULT]
NoOfReplicas=2
DataDir= /var/lib/mysql-cluster
DataMemory=1800M
IndexMemory=1000M
MaxNoOfAttributes=5000
MaxNoOfConcurrentOperations=500000
MaxNoOfTables=1600
MaxNoOfTriggers=10000
NoOfFragmentLogFiles=200
TimeBetweenLocalCheckpoints=12
#params for data to disk
DiskPageBufferMemory=500M #def ??
SharedGlobalMemory=100M #def 20M
# management node settings
[NDB_MGMD]
HostName=10.103.143.187
DataDir= /var/lib/mysql-cluster
#LogDestination = /var/lib/mysql-cluster
#1a
[NDBD]
HostName=10.103.143.188
#1b
[NDBD]
HostName=10.103.143.189
#2a
[NDBD]
HostName=10.103.143.190
#2b
[NDBD]
HostName=10.103.143.194
#sql1
[MYSQLD]
HostName=10.103.143.191
#sql2
[MYSQLD]
#spare sql node that any machine can use. specifically for ndb_restore really
[MYSQLD]
%%%%%%%%%%%%%
%%my.cnf for all machines
%%%%%%%%%%%%%
[mysqld]
ndbcluster
ndb-connectstring=10.103.143.187
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
#params to spee dup cluster
ndb_force_send = 0
ndb_use_exact_count = 0
[client]
socket=/var/lib/mysql/mysql.sock
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysql.server]
user=mysql
#basedir=/usr/local/mysql
[mysql_cluster]
ndb-connectstring=10.103.143.187
%%%%%%%%%%%%%
%%ndb_mgm> SHOW
%%%%%%%%%%%%%
ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)] 4 node(s)
id=2 @10.103.143.188 (Version: 5.1.18, Nodegroup: 0, Master)
id=3 @10.103.143.189 (Version: 5.1.18, Nodegroup: 0)
id=4 @10.103.143.190 (Version: 5.1.18, Nodegroup: 1)
id=5 @10.103.143.194 (Version: 5.1.18, Nodegroup: 1)
[ndb_mgmd(MGM)] 1 node(s)
id=1 @10.103.143.187 (Version: 5.1.18)
[mysqld(API)] 3 node(s)
id=6 @10.103.143.191 (Version: 5.1.18)
id=7 (not connected, accepting connect from any host)
id=8 (not connected, accepting connect from any host)
My issue is when i create larger tablespace/data.dat files, the number of free extents is incorrect. Exact queries are in the "how to repeat section"
How to repeat:
These queries are performed on a cluster with one schema, but no tables or data.
CREATE LOGFILE GROUP un_lg_1 ADD UNDOFILE 'un_undo_1.dat' INITIAL_SIZE 1000M UNDO_BUFFER_SIZE 50M ENGINE NDB;
CREATE TABLESPACE un_ts_1 ADD DATAFILE 'un_data_1.dat' USE LOGFILE GROUP un_lg_1 INITIAL_SIZE 10000M ENGINE NDB;
They return NO errors.
This query shows the problems (i have formatted to shrink its width):
mysql> select file_name,logfile_group_name,tablespace_name,free_extents, total_extents,extent_size from information_schema.files;
+---------------+--------------------+-----------------+--------------+-----
|fle_nam | lgfle_grp_name | tpace_name | free_extnt | tot_extnts |xtent_size
+---------------+--------------------+-----------------+--------------+------
| un_data_1.dat | un_lg_1 | un_ts_1 | 1808 | 10000 | 1048576 |
| un_data_1.dat | un_lg_1 | un_ts_1 | 1808 | 10000 | 1048576 |
| un_data_1.dat | un_lg_1 | un_ts_1 | 1808 | 10000 | 1048576 |
| un_data_1.dat | un_lg_1 | un_ts_1 | 1808 | 10000 | 1048576 |
| un_undo_1.dat | un_lg_1 | NULL | NULL | 262144000 | 4 |
| un_undo_1.dat | un_lg_1 | NULL | NULL | 262144000 | 4 |
| un_undo_1.dat | un_lg_1 | NULL | NULL | 262144000 | 4 |
| un_undo_1.dat | un_lg_1 | NULL | NULL | 262144000 | 4 |
| NULL | un_lg_1 | NULL | 262007812 | ULL | 4 |
+---------------+--------------------+-----------------+---------
Everything has been created, but the issue:
Why are there only 1808 free extents out of a total of 10000, when there is no data in the database. I have added data in to a table using these files, and the data IS added, but after a time of adding larger amounts of data, the number of free extents drops from 1808 rather than the full 10000 (this was an exercise to see if data made the free number start from 100% when soem data was added).
I've just created a tablespace of data.dat=5G and the number of free extents is 908 and not 10000.
Suggested fix:
I expect I might have some parameters set illegally?
I have read that 1G for undo_file.dat should suffice, but I'm not sure of the size of the undo_buffer_size. I used to get errors (5.1.17) trying to set this number > 50M, but now (5.1.18) it appears I can set this to atleast 70M having just tried it.
I do not know how these files sizes should marry between logfile groups and tablespace, maybe this is the problem?
