Bulk Loading into infiniDB

The bulk loader from infinDB is pretty fasty, I loaded 1.8 million records in 62 seconds not bad going.  Their PDF instructions are pretty comprehensive so here is just a snippet to get you started and having a go.

 

You must first create an XML file based on the table you want to load to, then you issue the import command. EASY :)

This is based on a standard install in the default locations

cd /usr/local/Calpont/bin/

./colxml schema_name -t table_name -d ‘delimiter’ -l textfilename_that_will_be_imported -j job_id

e.g.

./colxml pentaho -t dim_customer_details_tab -d ‘;’ -l customer_details.txt -j 1000

 

The text file to be imported must resided in the fillowing area

/usr/local/Calpont/data/bulk/data/import

 

To then import the file

cd /usr/local/Calpont/bin/

./cpimport -j Job_Id from above

e.g.

./cpimport -j 1000

 

 


Moving db,data, data1

If you want to move these files for what ever reason, genrally space and performance then

in this example I am moving them to an area in /u10/mysql-infinidb

 

cp /usr/local/Calpont/mysql/db /u10/mysql-infinidb/

chown -R mysql:mysql  /u10/mysql-infinidb/db

cp /usr/local/Calpont/data /u10/mysql-infinidb

chown -R mysql:mysql  /u10/mysql-infinidb/data

cp /usr/local/Calpont/data1 /u10/mysql-infinidb/

chown -R mysql:mysql  /u10/mysql-infinidb/data1

 

Then link the folders back

 ln -s /u10/mysql-infinidb/db /usr/local/Calpont/mysql/db

 ln -s /u10/mysql-infinidb/data /usr/local/Calpont/data

 ln -s /u10/mysql-infinidb/data1 /usr/local/Calpont/data1

 

 


Installing infiniDB community edition on Linux

I am in the realms of infiniDb today and it appears easy to install the only problem is it doesn’t work without a bit of effort

I am installing on Centos 5.6 but other distros also work, adapt the install part to your OS

OK the easy bits to start with

Download form here You have to register but thats OK

Please NOTE if you have mysql running then you need to stop this as infinidb uses port 3306, this can be changed see later on in the post

The basics are taken from here

You need to install boost to start with

yum install boost

Once downloaded unzip and untar the rpm file

tar -zxf

This creates 3 rpm files. To install

rpm -i calpont*.rpm

You need to run the post installation script now

./usr/localCalpony/bin/install-infinidb.sh

To start infinidb the instructions are a little wrong to start the database

./usr/local/Calpont/bin/infinidb start

This will start mysql and the infinidb engine

To get a mysql command

./usr/local/Calpont/bin/calpontAlias

You can then run

idbmysql  and you will get the traditional promprt you now have a working system.

To test

idbmysql

create database testdatabase;

create table testtable(test varchar(10)) engine=infinidb;

Now the fun bits

I have mysql running on my server and also as I want to make this scalable so I want to move the database files to a different location.

Changing the port is simple.

vi /usr/local/Calpont/mysql/my.cnf

Find 3306 and change to what ever port number you want I used 3307 it may be reservered for another function, but this server is only going to run mysql and is never going to be presented to the outside world.

You can then restart the server to see if all is well.

Moving the database is a littl more fun. Make sure the database is stopped

Create a directory in the location that you want oto store the files

e.g /u10/mysql-infinidb therefore

mkdir /u10/mysql-infinidb

cp -r /usr/local/Calpont/mysql/db  /u10/mysql-infinidb/

chown -R mysql:mysql /u10/mysql-infinidb

You then ned to change the my.cnf file and the mysql-Calpont file

vi /usr/local/Calpont/mysql/my.cnf

Find and alter the following line.  I commented out as can be seen just for easier dugugging

There is another way of doing this here

#datadir         = /usr/local/Calpont/mysql/db
datadir         = /u10/mysql-infinidb/db

vi /etc/init.d/mysql-Calpont

Find the following lines

# @bug 2104. remove all left over vtables. Look for @0024vtable ($vtable).
# “@0024vtable” is a reserved word of InfiniDB and can not be used in regular user table name.
cd db
find . -name ‘@0024vtable*’ | xargs /bin/rm -f
cd ..
and change to

# @bug 2104. remove all left over vtables. Look for @0024vtable ($vtable).
# “@0024vtable” is a reserved word of InfiniDB and can not be used in regular user table name.
#cd db
cd /u10/mysql-infinidb/db This is the location that you created in the previous step
find . -name ‘@0024vtable*’ | xargs /bin/rm -f
#cd ..
    cd /usr/local/Calpont/mysql

You should then be able to start the database

I use Webmin for looking after mysql and it can be configured to look after inifindb as well



Follow

Get every new post delivered to your Inbox.

Join 30 other followers