Tuesday, April 11, 2006

MySQL CLuster Disk Data QA

MySQL QA

Now that we have Disk Data for MySQL Cluster you can have the best of both worlds. You can use memory tables for those needing quick access and updates and Disk Data Files for those that will be too large to keep in memory all the time.

One issue with this first release is not having the ability to spread the data and undo files accross the disk sub system using the config.ini file. This can be a important issue for performance. You really don't want to have everything on one disk. Here I list a work around for moving undo and/or data files off to different drives to help in disk performance that I used during performance testing.

1) Bring up the cluster to include all data nodes
2) Each data node will create its own File System ((e.g. ndb_#_fs) where # == the DN ID)).
3) Under the data node FS create the symbolic links pointing to the other drives:

Examples Single Data Node:
[ndb_2_fs]$ ls
D1 D10 D11 D2 D8 D9 LCP
[ndb_2_fs]$ ln -s /data0/log1/ dn_logs
[ndb_2_fs]$ ln -s /data1/data/ dn_data

We now have 2 symbolic links

12 Jan 31 20:31 dn_data -> /data1/data/
12 Jan 31 20:31 dn_logs -> /data0/log1/

Note: This needs to be done for each and every data node before going to next step:

4) Now do the create statements
CREATE LOGFILE GROUP lg1
ADD UNDOFILE './dn_logs/undofile.dat'
INITIAL_SIZE 150M
UNDO_BUFFER_SIZE = 1M
ENGINE=NDB;

CREATE TABLESPACE ts1
ADD DATAFILE './dn_data/datafile.dat'
USE LOGFILE GROUP TPCB_LOG
INITIAL_SIZE 5M
ENGINE=NDB;

5) Doing the same for multi data nodes running on one host.

Give each data node its own path
[ndbd]
Id: 2
HostName: host13
FileSystemPath: /data0

[ndbd]
Id: 3
HostName: host13
FileSystemPath: /data1

DN ID 2 will create it file system on /data0 and DN ID 3 will create its file system on /data1

we then repeat the steps in steps 2 & 3 placing the files in the desired location using symbolic links, and the step 4 for the actual create.

You can then make sure the above worked correctly:

[dn_data]$ cd /data1/data/
[data]$ ls -l
total 2099304
-rw-rw-r-- 1 user group 2147581952 Jan 28 19:58 datafile.dat


Best wishes,
/JBM

No comments: