Bug #28642 Tablespace returning incorrect usage status
Submitted: 24 May 2007 9:44 Modified: 11 Jul 2007 9:27
Reporter: Gordon Leonard Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Disk Data Severity:S2 (Serious)
Version:5.1.18 OS:Linux (Fedora Core 6)
Assigned to: Jonas Oreland CPU Architecture:Any
Tags: data, disk, extent, size, Tablespace

[24 May 2007 9:44] Gordon Leonard
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?
[3 Jul 2007 14:43] Jonas Oreland
The bug was "only" in the reporting of free extents.
[3 Jul 2007 23:29] 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/30183

ChangeSet@1.2504, 2007-07-03 16:29:25+02:00, jonas@perch.ndb.mysql.com +1 -0
  ndb - bug#28642 - Tablespace returning incorrect usage status
    make free 64 bit (as it represents free bytes, not free extents as originally designed)
[4 Jul 2007 10:03] Jon Stephens
Documented bugfix in telco-6.2.4 changelog; left PQ status.
[10 Jul 2007 13:27] Bugs System
Pushed into 5.1.21-beta
[11 Jul 2007 9:27] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.1.21 changelog.
[6 Sep 2007 9:43] Jon Stephens
Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

Bugfix also documented in mysql-5.1.15-ndb-6.1.18 changelog.