Note: Due to the port to MySQL 5.1 GA, the underlying data structures have changed. Please see step 4 below on how to run the MySQL Updater to migrate your previous database to 3.1.1 during the upgrade. After upgrading to 3.1.1 you will not be able to use a 3.1.1 database with a previous versions of ICE. We recommend you backup your data before upgrading in case you wish to downgrade for any reason.

1. Obtain root user access

2. Unpack the tarball into a temporary folder:

cd /path/to/temp/
gunzip < /path/to/infobright-3.3.1-x86_64.tar.gz | tar xvf -
cd infobright-3.3.1-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. 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.

5. Re-start the Infobright server:

/etc/init.d/mysqld-ib restart

6. Confirm the build version as IB_3.3.1_r6997_7017:

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

Note: Due to the port to MySQL 5.1 GA, the underlying data structures have changed. Please see step 3 below on how to run the MySQL Updater to migrate your previous database to 3.1.1 during the upgrade. After upgrading to 3.1.1 you will not be able to use a 3.1.1 database with a previous versions of ICE. We recommend you backup your data before upgrading in case you wish to downgrade for any reason.

1. Stop ICE 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 ICE installation while preserving your data and configuration settings.

3. After installation, 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.

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

Note: Due to the port to MySQL 5.1 GA, the underlying data structures have changed. Please see step 4 below on how to run the MySQL Updater to migrate your previous database to 3.1.1 during the upgrade. After upgrading to 3.1.1 you will not be able to use a 3.1.1 database with a previous versions of ICE. We recommend you backup your data before upgrading in case you wish to downgrade for any reason.

For upgrading on Windows XP, please see Upgrade Guide for Windows.

To upgrade Infobright on Linux 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. Start the ICE server:

/etc/init.d/mysqld-ib start

4. 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.

5. Re-start the Infobright server:

/etc/init.d/mysqld-ib restart

6. Confirm the build version as IB_3.3.1_r6997_7017:

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

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 -
ln -s /usr/local/infobright-3.3-x86_64-ice infobright
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-
--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=3306]
--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 --force (bypass system compatibility checking)
./install-infobright.sh --datadir=/usr/local/infobright/data --cachedir=/usr/local/infobright/cache --port=3306 --config=/etc/my-ib.cnf --socket=/tmp/mysql-ib.sock --user=mysql --group=mysql --force

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

Installation Instructions

1. Download the install package infobright-3.3-win32.zip to the Windows XP machine on which you are installing Infobright

2. Double click the infobright-3.3-win32.zip file to unzip the install.exe file

3. Double click on the infobright-3.3-win32.exe file to launch the Install Wizard. Click Next to continue

4. Click I Agree to accept the GPL license agreement

5. By default ICE is installed in C:\Program Files\Infobright. To change the default location, either enter the folder name in the field or click Browse… to select desired install location on your compute. Click Install to accept the install location

6. After the Install Wizard completes the installation, choose if you want ICE to start on completion of the installation. Click Finish to complete the installation

7. To connect to the ICE command line interface, run:

Start/All Programs/Infobright/Infobright Command Line Client

8. To uninstall ICE, select “Infobright Uninstall” under the Infobright program group in the Windows Start Menu:

Start/All Programs/Infobright/Infobright Uninstall

Configuration Settings

The install determines optimum memory settings based on the physical memory of the system. The 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 by editing the file brighthouse.ini within the data directory.

The Install Wizard automatically creates ICE as a Windows Service, which allows the Infobright server to be started and stopped automatically when you boot or shutdown Windows. If you do not want ICE to start on boot, open the Services window from the Control Panel and change the Startup Type for Infobright from “Automatic” to “Manual.”

To manually start the Infobright server, from the Windows Start Menu run:

Start/All Programs/Infobright/Infobright Start

To manually stop the Infobright server, from the Windows Start Menu run:

Start/All Programs/Infobright/Infobright Stop

Installation Instructions

1. Download the install package (e.g. infobright-3.3-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

Instead, the Query Execution Log is used to track the execution plan, provide rough statistics, and provide insights into performance bottlenecks.

Maintaining the Query Execution Log

The query execution log is located:

• in bh.err file in your database directory (configurable as log-error value in the --config file provided at installation, /etc/my-ib.cnf by default),
• on console, if the server is run with --console option

By default, the Query Execution Log contains information on server start and shutdown, as well as values of execution parameters (from the brighthouse.ini file). To enable more detailed query execution logging, follow these steps:

1. Open the brighthouse.ini configuration file, located in your database directory

2. Set the following option:

ControlMessages = 2

3. Restart the Infobright server to execute the change

The execution log for a query may be quite large (several KBs), and the bh.err log may grow substantially. For a server executing a lot of small queries it is recommended either to switch the logging off (ControlMessages = 0), or to implement an automatic backup scheme. A shell script (Unix) which copies the error log to a backup file and re-initializes the bh.err file is available here

Set ControlMessages = 1 to obtain a log in more compact form (without timestamps).

Understanding Query Execution Log

Most of the lines of the log consists of a timestamp, and a session number in a square bracket. The session number is maintained by a querying tool – some tools use different numbers for each subsequent query, some other will keep the number for the next queries. The number in the log is useful to determine which message is generated by which query (running in parallel). For example:

2009-05-11 14:30:10 [6] (t0): 25 all packrows, 25 to open (including 25 full)
2009-05-11 14:30:11 [2] Displaying result: 1000 rows.

These two log messages are connected to two different queries (session 6 and 2) and should not be understood as a sequence of events.

When a query starts, its compiled version is displayed in the log, as a sequence of internal Infobright execution steps:

2009-05-12 09:29:26 [1] T:-1 = TABLE_ALIAS(T:0)
T:-2 = TMP_TABLE(T:-1)
A:-1 = T:-2.ADD_COLUMN(TERM(t:-1 a:0),LIST,"RECORDTYPE","ALL")
...

The query is then executed by the Infobright optimizer, and each step is described in the log. Before the exact calculation, a result of rough execution is presented, including a total number of data packs, and data packs excluded by KNs. The log for a query is completed after displaying the summary of loaded and decompressed (i.e. not present in cache) data packs:

2009-05-12 09:29:26 [1] Displaying result: 20 rows.
2009-05-12 09:29:26 [1] Total data packs actually loaded (approx.): 35
2009-05-12 09:29:26 [1] ----------------------------------------------------------------------------

The query log is a useful tool for identifying bottlenecks and pinpointing errors. The following messages may indicate problems:

Table ./mydb/clients (41) accessed by MySQL engine.

The Infobright table was accessed by MySQL engine, instead of Infobright one. Possible reasons:

• Unsupported syntax is used.
• Join with non-Infobright table is performed.
• MySQL format is used in export, instead of Infobright text format.
• MySQL is gathering statistics of Infobright tables either for ETL tool, or to optimize query (e.g. outer joins) before sending it back to Infobright engine.

All cases except the last one mean that the query will be very slow.

2009-05-29 07:41:35 [7] Warning: a big intermediate object created (~268 GB)

This message means that a substantial disk space will be used to execute the query. In most cases it is not intended and may indicate a mistake in query formulation – e.g. a full Cartesian join of tables with millions of rows.

2009-11-06 18:52:20 [1] Starting joiner loop (4017000000000 rows).

Joiner loop is the least efficient joining algorithm in Infobright (just a quadratic loop through all pairs of rows). The number in message indicates the number of pairs to be checked. If this number is large (as above), it is probably better to stop query and rethink its formulation, to avoid joins declared in implicit way, e.g.:

...WHERE t1.x + t2.y > 5... > ...WHERE t1.x > 5 - t2.y...

The latter formulation will use much faster joining algorithm.

For some long-lasting operations it is possible to forecast approximate total time basing on log:

2009-11-06 19:05:57 [1] Aggregating: 1000000000 tuples left, 0 gr. found so far
2009-11-06 19:17:59 [1] Aggregating: 864292787 tuples left, 3677031 gr. found so far

Using timestamps we can conclude that 722 sec. was spent on 136 mln. rows (13.6%), which means that the query will probably run for another 1 h 16 minutes.

• Hardware resources (available RAM, number of cores, etc.)
• Operating system (Red Hat Enterprise Linux, Ubuntu, CentOS, etc.)

Based on the user’s environment, the following Tip & Tricks pages discuss which parameters can be tweaked and provides guidelines for appropriate parameters values:

• Optimizing Queries
• How and When to use Lookups
• Supported Data Types and Values
• Efficient Data Types
• Optimized SQL Functions and Operators
• Knowledge Grid Statistics
• Query Execution Log
• Linux Tuning

Columns stored as integers are much more efficient (easier to sort, better described by Knowledge Nodes, etc.) than other data types. So if possible, store data as any of the following:

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
DECIMAL — note: storing as (10,0) will be much faster than (10,5)
DATE, TIME

Special case data types:

CHAR, VARCHAR — these types are covered in the Knowledge Grid, but where possible should be replaced numeric values, as they are better optimized and faster to decompress

Less optimized data types:

BINARY, VARBINARY
FLOAT
DOUBLE
TINYTEXT, TEXT

DECIMAL is a different, relatively less efficient PHYSICAL representation of INTEGER. DECIMAL is much more efficient than FLOAT or DOUBLE and therefore, should be used in place of FLOAT or DOUBLE if possible.

ICE is a data storage engine with proprietary compression, storage, and retrieval methods. It leverages many features of MySQL including a support for variety widely-used APIs (e.g. SQL, ODBC, JDBC).

There are specific SQL commands that have been optimized for Infobright, meaning they use the Knowledge Grid to resolve queries. For other MySQL functions, Infobright will resolve them by defaulting to the MySQL optimizer in order to resolve the query. This can be considerably slower as the MySQL optimizer is row-oriented, is not able to use the Knowledge Grid and must decompress significantly more data. In these cases the query will return with a warning. Use SHOW WARNINGS; to see the message:

Query syntax not implemented in Infobright, executed by MySQL engine.

There are several things that can be done to optimize queries within Infobright:

Use Optimized SQL Functions and Operators

Use More Efficient Data Types

OR Function
The OR consistently has poorer performance because it essentially creates two separate intermediate structures and then gathers results together, omitting repetitions. The latter operation is very time-consuming. If possible, OR functions should be replaced by IN or UNION ALL (if there are no repetitions possible):

Example:

UNION Function
UNION is slower than UNION ALL because it must detect and omit repetitions in the result (for a very large output, it uses operations like sorting etc.). If repetitions are not harmful, or if it is already known that the result does not contain repetitions, UNION ALL should be used.

Leveraging Columnar Architecture

Infobright is a columnar database, which means that access to every column of data is independent. As a consequence, it is important how many fields of one row of data are being accessed. For optimal performance, you should avoid accessing columns that not being used in the query results. Instead of SELECT * FROM… you should rather select only the necessary columns.

Limiting Tables Used in Results

The Infobright engine may optimize join execution if there is a possibility that one of the involved tables is not used in further phases of execution, like SELECT list, GROUP BY, ORDER BY. E.g., assuming t2 is a large fact table:

This query may be sometimes transformed into:

In such cases the inner query produces the grouping, which is nearly what is needed, but it is defined in terms of t2 only. Then the grouping is refined by externally transforming it into grouping by t1. Sometimes such transformations may improve performance by an order of magnitude, but it works only for aggregations which may be combined from parts (like sum).

A simpler example of transformation:

and a faster equivalent (using only t2 on SELECT list):