Hi,

    I have 2 table with 60000000 rows. one tables is infobright table another table is myisam table. 2 tables have same values.

now i have retrive the data from the both table…. i wondered… infobright table take more time than myisam table… why?

i have been using the following query only.

select id,count(code) from tbl_fact group by id limit 10;

how to improve the query speed in infobright?

How to set the optimized server variable.?

I have 4GB of RAM in that machine. what is the optimized server vaiables.?

can u any one help me…?

Thanks.
S.Ashokkumar

Hi,

Could you provide more details, please? Column types, Infobright log for this query. The latter is in bh.err file in the database directory, and it is populated by query execution messages if “ControlMessages = 2” is set in brighthouse.ini configuration file. BTW, the contents of brighthouse.ini also will be helpful.

Regards,

Hello !

Your query does not include any WHERE condition. Therefore potentially all rows must be retrieved and processed. In the case of IB, retrieving means decompression. Decompression takes time and this is why mysql can be faster - it does not need to decompress. Yes - LIMIT should shorten the execution time, but it depends on the data.

If you have more RAM and you declare MainHeapSize larger (BTW - what is its size now - see brighthouse.ini or the startup message) then more decompressed data can be cached. Then, the first time you run a query it takes time to decompress data, but subsequent query invocation should be much faster.

I am using the Linux Machine with 4 GB RAM.

Table have around 90 columns.

id - int
code - varchar(255)
.......
.......
.......

Config file have the following values…

# The following options will be passed to all MySQL clients
[client]
port     = 3306
socket       = /usr/local/infobright/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
basedir = /usr/local/infobright
datadir = /mysqldata/infobright/data

log-error = /mysqldata/infobright/log/bh.err
log-output = FILE

log = /mysqldata/infobright/log/general_query.log

log_slow_queries = /mysqldata/infobright/log/slow_query.log

port     = 3306
socket     = /tmp/mysql.sock
skip-locking
bulk_insert_buffer_size = 1G
#key_buffer_size = 1G
key_buffer = 1G
max_allowed_packet = 32M
table_cache = 512
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 8M
net_buffer_length = 8K
thread_cache_size = 32
thread_stack = 512K
query_cache_size = 8M
# Try number of CPU cores*4 for thread_concurrency
thread_concurrency = 8

#skip-networking
server-id   = 1

# Uncomment the following if you want to log updates
#log-bin=mysql-bin

default-storage-engine=brighthouse
collation_server=ascii_bin
character_set_server=ascii

[mysqldump]
quick
max_allowed_packet = 32M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 32M
sort_buffer_size = 32M
read_buffer = 1M
write_buffer = 1M

[myisamchk]
key_buffer = 32M
sort_buffer_size = 32M
read_buffer = 1M
write_buffer = 1M

[mysqlhotcopy]
interactive-timeout

[mysql-test]
host=local
user=root
port=3306
#port=5000
database=dk
# suite-timeout=60
testcase-timeout=3600

can you any one fine tune my config file with suitable to 4 GB RAM.

Thanks.
S.Ashokkumar.

Hi,

The main problem is with varchar(255) column. Compression/decompression of long text values are particularly slow in Infobright.

What may we do? First, let’s try to get rid with this decompression at all. What type of data do you store in “code”? Are there random strings, or rather one of a few (or few hundreds) of possible codes? Are NULLs frequent? Note that we want just to count non-nulls, so maybe it is possible just to store information about nulls in a separate 1/0 column?

Config file for MySQL does not contain config information for Infobright. The latter are located in the database folder, it is brighthouse.ini file.

Regards,

code -  varchar(32)
code column value is alpanumeric like ‘ABSA2F11025650’

There is no null values.

This is my brighthouse.ini file.

################## BrightHouse configuration file ####################
# To change values, uncomment the parameter and specify desired value.

############ Critical Disk Settings ############

# Data Folder: check where you installed brighthouse data folder (directory this file is in) - it should be on a fast disk.

# CacheFolder - a place in which temporary database objects (memory cache) are stored.
# Should be on a fast drive, possibly not the same as data. Allow at least 20 GB of free space (depending on database size).
CacheFolder = /usr/local/infobright/cache

############  Critical Memory Settings ############

# Note: the **default settings** below are for 2 GB machines. When more memory is avaliable, set it higher.
# System Memory   Server Main Heap Size   Server Compressed Heap Size   Loader Main Heap Size
#  3GB             1200               400

# ServerMainHeapSize - Size of the main memory heap in the server process, in MB
ServerMainHeapSize=400

# ServerCompressedHeapSize - Size of the compressed memory heap in the server process, in MB.
ServerCompressedHeapSize=200

# LoaderMainHeapSize - Size of the memory heap in the loader process, in MB.
LoaderMainHeapSize=300

############ Logging Settings ############

# ControlMessages - Set to 2 to turn the control messages on. This is usually needed by Infobright to support performance investigation.
# ControlMessages = 0

############  Other Settings ############

# ClusterSize - maximum size of data files in MB [10 - 2000].
# Decreasing ClusterSize may make differential backup easier, but overall performance may decrease for large databases.
# ClusterSize = 2000

# KNFolder - Directory where the Knowledge Grid is stored.
# KNFolder = BH_RSI_Repository

# AllowMySQLQueryPath can be set to 0 to disable MySQL Query path or 1 to enable it.
# AllowMySQLQueryPath = 0

Hi,

Since there is no null values in code, please try:

The answer should be the same.

About brighthouse.ini: to obtain good performance, it should be tuned to your system size. For 32-bit 4 GB Linux machine I would set:

(note that ‘#’ is a comment sign).

Regards,

Which server is better whether 32 bit or 64 bit?

I am using 32 bit server with 4 GB RAM, but it doesn’t give the expected performance improvement. Its give low performance than the MyISAM.

I have tested with various queries with group by / joins / range etc. Every thing gives the poor performance.

How to increase the query speed in infobright compare to MyISAM?

64-bit server is better, especially for larger memory amounts. Even for 4 GB it may be faster, since it is possible to use e.g. 2 GB for ServerMainHeapSize (which would be risky in 32-bit system).

About particular queries - it is hard to say without examples. There are cases where MyISAM will be faster - e.g. some cases of LIMIT, as well as massive data retrieval like “SELECT *”. Infobright is designed to be effective for analytical-type queries, like aggregations, as well as for data sizes for which MyISAM will just fail.

Regards,

We have very much interested to test the Infobright Enterprise Edition. We didn’t get the expected performance of Infobright compared with MyISAM in 32 bit machine.  So based on your suggestion, we have planned to test the Infobright with 64 bit machine.  We have planned to get a rented 64 bit machine. I want to know from you before to rent a machine, what is the suitable hardware and software specification to test the Infobright, like, in which CPU,  CPU vendor like Intel / AMD / etc…,  RAM size, OS version(Linux), Kernal version, required libraries, etc. So let me know the detailed specifications as soon as possible. Once we get the best performance from this, we will move this to the production.

      When will we expect the next version for infobright? 

Thanks.
S.Ashokkumar.
DBA - India

Hello,

When will we expect the next version for infobright?

Thanks very much for your interest, and you’ll be happy to hear that the new version ICE 3.2 RC1 will be out by the end of July!

Kind regards,
Andrew

I have installed the ICE in the 64 bit machine, in that, i stoped the mysql service, then i have changed the data dir in the config file
/etc/my-ib.cnf, again i tried to start the mysql service, but its not start. If i changed to the default data dir, again its work.  Why?
how to change the data dir in the infobright?

# The MySQL server
[mysqld]
basedir = /usr/local/infobright
#datadir = /usr/local/infobright/data   #—Default Data Dir
datadir = /mysql/infobright/data     #—New Data Dir

This config not working, doesnt start the mysql service.

Thanks.
S.Ashokkumar.

Will mysql server variable settings involve in infobright performance improvement? like…

bulk_insert_buffer_size = 2G
#key_buffer_size = 1G
key_buffer = 2G
max_allowed_packet = 64M
table_cache = 512
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 8M
net_buffer_length = 8K
thread_cache_size = 32
thread_stack = 512K
query_cache_size = 8M
# Try number of CPU cores*4 for thread_concurrency
thread_concurrency = 8

What is the optimized mysql sever variable settings for 8 GB RAM?

Hi,

Please try to use brighthouse.ini configuration file. The general MySQL *.cnf files does not involve Infobright engine settings, so all these settings cited above are not relevant. The correct configuration file is located in the database directory (not in MySQL directory), i.e. in the same place the database/table files are stored, and its name is brighthouse.ini.

Regards,