Lookups replace a CHAR or VARCHAR value with an integer value for a column where the total values to unique values ratio is more than 10:1. This is particularly beneficial for fields like States and other fields that traditionally have a small range of acceptable values. So, if you had 100,000 values, and only 10,000 unique values, then you would have a good candidate for the lookup function.

The lookup function should not be used if this criteria is not met – a ratio less than 10:1 will result in slower queries and longer load times.

How do I create a Lookup?

A lookup must be created at table creation time as part of the DDL. Simply add the following to the table definition:

> <<column name>> <<column type>> … comment ‘lookup’ …

This cannot be done as part of a modify table statement, since Infobright generates the integer values on load.

• Up to 50TB of data, supporting hundreds of millions of rows in a single table
• Data loading and storage, no DML support required
• 64-bit & 32-bit Linux environments and 32-bit Windows

To learn more about Infobright technology, download the Infobright Architecture white paper from the White Paper page at http://support.infobright.com/Support/Resource-Library/Whitepapers/

ICE 4.0.3 GA – Upgrade Notes

1. With ICE 3.5.2 GA, several tuning parameters are now configured automatically and have been deprecated from the brighthouse.ini file. The deprecated parameters include:

• ServerCompressedHeapSize
• LoaderSaveThreadNumber
• BufferingLevel
• CachingLevel
• ClusterSize
• HugefileDir

2. Due to a known issue with the embedded version of MySQL, it is recommended you run the MySQL Updater program after any upgrade to avoid issues with stored procedures or table structures.

3. The enable UTF-8 flag (UseCharset = 0/1) is no longer needed and if present, must be removed from the brighthouse.ini file. For table structures created prior to ICE 3.3.2 the Charset Migration Tool (CHMT) should be run during upgrade.
4. The MySQL Query cache should be disabled before the upgrade
Linux Upgrade Instructions Using the RPM or DEB Package

1. Obtain root user access

2. Run either:

rpm -U infobright-version-platform.rpm
dpkg -i infobright-version-platform.deb

3. Change to the mysql user

4. Start the Infobright server:

/etc/init.d/mysqld-ib start

5. Run mysql upgrade tool to upgrade data folder:

cd /usr/local/inforbright
./bin/mysql_upgrade --defaults-file=/etc/my-ib.cnf --user=root --tmpdir=/tmp

Important: The MySQL Upgrade utility may display several errors regarding the use of locks with log tables and errors requiring table upgrades. The errors are all handled automatically by Infobright and/or the upgrade utility and can be ignored.

6. Re-start the Infobright server:

/etc/init.d/mysqld-ib restart

7. Confirm the build version as IB_4.0_r13151_13690:

/usr/local/infobright/bin/mysqld --version

1. With ICE 3.4.2 GA, several tuning parameters are now configured automatically and have been deprecated from the brighthouse.ini file. The deprecated parameters include:

• ServerCompressedHeapSize
• LoaderSaveThreadNumber
• BufferingLevel
• CachingLevel
• ClusterSize
• HugefileDir

2. Due to a known issue with the embedded version of MySQL, it is recommended you run the MySQL Updater program after any upgrade to avoid issues with stored procedures or table structures.

3. The enable UTF-8 flag (UseCharset = 0/1) is no longer needed and if present, must be removed from the brighthouse.ini file. For table structures created prior to ICE 3.3.2 the Charset Migration Tool (CHMT) should be run during upgrade.

Windows Upgrade Instructions

1. Stop the Infobright server using the Start Menu item.

2. Follow the Install Guide for Windows. The Install Wizard automatically detects a previous version of ICE and upgrades your Infobright installation while preserving your data and configuration settings.

3. Stop the Infobright server from the Start Menu item

4. Start the Infobright server from the Start Menu item

5. Create or ensure that the directory c:\tmp exists (necessary for step 7)

6. Run the MySQL Upgrade utility from the Windows command line:

cd "C:\Program Files\Infobright"
.\bin\mysql_upgrade.exe --defaults-file="c:\Program Files\Infobright\my-ib.ini” -uroot --tmpdir=c:\tmp

Important: The MySQL Upgrade utility may display several errors regarding the use of locks with log tables and errors requiring table upgrades. The errors are all handled automatically by Infobright and/or the upgrade utility and can be ignored.

8. Stop and start the Infobright server from the Start Menu items.

Import your data into an Infobright table by using the following load syntax (all other MySQL Loader syntax is not supported):

LOAD DATA INFILE '/full_path/file_name' INTO TABLE table_name
[FIELDS
[TERMINATED BY 'char']
[ENCLOSED BY 'char']
[ESCAPED 'char']
];

The data is committed when the load completes if AUTOCOMMIT is set to on. This is default setting, but you can make it explicit by setting:

Set AUTOCOMMIT=1;

If you want to check the data via select before committing, then set AUTOCOMMIT to off:

Set AUTOCOMMIT=0;

New data can be seen by the loading session even though its not committed. Complete the load using an explicit COMMIT:

COMMIT;

For testing purposes you can generate data using this community user’s data generation tool.

For more information on using the ICE Loader and details on the supported syntax, please click on the following link to download the Loading Data in ICE document:

Download the Data Loading PDF

1. With ICE 3.5.2 GA, several tuning parameters are now configured automatically and have been deprecated from the brighthouse.ini file. The deprecated parameters include:

• ServerCompressedHeapSize
• LoaderSaveThreadNumber
• BufferingLevel
• CachingLevel
• ClusterSize
• HugefileDir

2. Due to a known issue with the embedded version of MySQL, it is recommended you run the MySQL Updater program after any upgrade to avoid issues with stored procedures or table structures.

3. The enable UTF-8 flag (UseCharset = 0/1) is no longer needed and if present, must be removed from the brighthouse.ini file. For table structures created prior to ICE 3.3.2 the Charset Migration Tool (CHMT) should be run during upgrade.

Linux Upgrade Instructions Using the Tar Package

1. Obtain root user access

2. Unpack the tarball into a temporary folder:

cd /path/to/temp/
gunzip < /path/to/infobright-3.5.2-x86_64.tar.gz | tar xvf -
cd infobright-3.5-x86_64

3. Stop the Infobright server:

/etc/init.d/mysqld-ib stop

3. Run the install script with the "--upgrade” and “--config” flags and pass in the configuration files of the previously installed version:

./install-infobright.sh --upgrade --config=/etc/my-ib.cnf

4. Run the Charset Migration Tool:

cd /usr/local/infobright
./bin/chmt --datadir=/absolute/path/to/data/directory

5. Start the Infobright server and run the mysql_upgrade utility:

/etc/init.d/mysqld-ib start
cd /usr/local/infobright
./bin/mysql_upgrade --defaults-file=/etc/my-ib.cnf --user=root --tmpdir=/tmp

Important: The MySQL Upgrade utility may display several errors regarding the use of locks with log tables and errors requiring table upgrades. The errors are all handled automatically by Infobright and/or the upgrade utility and can be ignored.

6. Re-start the Infobright server:

/etc/init.d/mysqld-ib restart

7. Confirm the build version as IB_3.5.2_r11794_12019:

/usr/local/infobright/bin/mysqld --version

ICE is available on the following platforms for 64-bit Intel or 64-bit AMD:

• Windows Server 2003
• Red Hat Enterprise Linux 5
• Debian ‘Lenny’
• CentOS 5.2

ICE is also available on the following platforms for 32-bit Intel or 32-bit AMD (Note: the 32-bit version should only be used for staging and application testing, or limited concurrent/single user deployments):

• Windows XP
• Ubuntu 8.04
• Fedora 9

System Requirements

Processor Architecture – Intel 64-bit, Intel 32-bit, AMD 64-bit, AMD 32-bit

For Multi-User Evaluation or Production Deployment:

CPU Speed

• 64-bit:

2.0GHz minimum
2.0GHz or faster dual or quad core recommended

Memory

• 64-bit:

4GB minimun
16GB or more recommended (and at least 2GB per core)

For Personal Evaluation, or Single/Limited User Deployments:

CPU Speed

• 32-bit:

1.6GHz minimum
2.0GHz or faster dual or quad core recommended

• 64-bit:

1.8GHz minimum
2.0GHz or faster dual or quad core recommended

Memory

• 32-bit:

1GB minimum
2GB or more recommended

• 64-bit:

2GB minimum
4GB or more recommended

Installation Instructions

1. Download the install package (e.g. infobright-3.4-x86_64.rpm) to the server where you are installing Infobright

2. Obtain root user access

3. To install the RPM package, run:

rpm -i infobright_version_name.rpm [optional: --prefix=path]

To install the DEB package, run:

dpkg -i infobright_version_name.deb

Note: Please do not install ICE in the root or home directories due to possible MySQL permission checking issues during install, start up, and/or load.

4. To change the default install options, after installation run:

/usr/local/infobright/postconfig.sh

You can run this script at any time after installation to change the datadir, CacheFolder, socket, and port. The script must be run as root and ICE must not be running.

5. The installation determines the optimum memory settings based on the physical memory of the system. You may change these settings by editing the file brighthouse.ini within the data directory.

Important: The memory settings assume that there are no other services on the machine consuming significant memory. If this is not the case, please lower the memory settings for Infobright.

6. To start or stop ICE, run:

/etc/init.d/mysqld-ib start
/etc/init.d/mysqld-ib stop

7. To connect to ICE, use the script mysql-ib:

/usr/bin/mysql-ib [optional:db_name]

8. To uninstall ICE, run either:

rpm -e infobright
dpkg -r infobright

Important: It is critical that you increase the memory settings for systems running more than 2GB of physical memory or performance will be severely impacted.

The following table lists the parameters and some recommended values based on the amount of system memory (values are expressed in megabytes).

Note: The values are commented out (preceded by #) in the brighthouse.ini file, which causes them to default to the application minimum allowed values of 600, 256, and 320 respectively.

8. To start or stop the Infobright server, run:

/etc/init.d/mysqld-ib start
/etc/init.d/mysqld-ib stop

For details, run

bin/mysqld_safe --help

9. To connect to Infobright server, use the script mysql-ib located in /usr/bin

Creating the Database from an Existing Schema

1. Download the install package (e.g. infobright-3.3-x86_64-ice.tar.gz) to the server where you are installing Infobright

2. Obtain root user access

3. Change to the parent location in which you want to install (e.g. /usr/local)

cd /usr/local

Note: Please do not install in the root or home directories due to possible MySQL permission checking issues during install, start up, and/or load.

4. Unpack the tarball, which will create the product directory (infobright-3.3-x86_64-ice/), and create a symbolic link ‘infobright’ to the product folder

gunzip < /path/to/infobright-3.3-x86_64-ice.tar.gz | tar xvf -
cd /usr/local/infobright

5. Run the install script with the "--help” flag to check for system configuration and provide examples of directory parameters

./install-infobright.sh --help

Parameters required for installation -
--datadir=infobright data folder [--datadir=/usr/local/infobright/data]
--cachedir=infobright cache folder [--cachedir=/usr/local/infobright/cache]
--config=mysql conf file to be created [--config=/etc/my-ib.cnf]
--port=infobright server port [--port=5029]
--socket=socket file to be used by this server [--socket=/tmp/mysql-ib.sock]
--user=user to be created if not exist [--user=mysql]
--group=user group be created if not exist [--group=mysql]

Example command
./install-infobright.sh (install with defaults)
./install-infobright.sh --help
./install-infobright.sh --datadir=/usr/local/infobright/data --cachedir=/usr/local/infobright/cache --port=5029 --config=/etc/my-ib.cnf --socket=/tmp/mysql-ib.sock --user=mysql --group=mysql

6. Run the install script again, this time with directory parameters. If parameters are used that already exist, an error will occur (for example running the same script with parameters twice)

Install Guide Part 2