Infobright.org Forums

Where can I find an overview of code in the storage/core directory ?

2010-03-18T13:32:43-05:00

Hi,

I am working on columnar storage on flash media and I am considering using the ICE codebase as a starting point for implementing my ideas. But before I do that, I want to try to understand the codebase a bit more.

I tried going through the files in the storage/core directory to relate them to the architecture whitepaper but find many many levels of abstraction in there.

I am wondering if there is a document on how the DPN’s, RCAttribute’s, MultiIndex’s etc are all related to each other—so that I can make some sense out of it ?.

If not, then what is a good point to start if my goal is to exactly understand how DPN’s are used for filters. And where do more advanced things like histogram’s etc fit in to the overall scheme.

Any suggestions would be welcome.

I have already searched the forum and wiki (and read all the whitepapers) but couldn’t find such a ‘code design overview’ kind of a document.

Thanks a lot
-Devesh Agrawal
Grad Student at UMass Amherst

Can Infobright scale beyond 30TB’s?

2009-07-09T14:42:22-05:00

I recently was asked?

How does Infobright plan to scale beyond 30 Terabytes data warehouse size?

Infobright, based on recent powerful SMP boxes, scales to the 50 Terabyte range. Additionally, Infobright recently announced plans for our Simplified Scalability Architecture (SSA) which will allow Infobright to scale across multiple concurrent servers with a shared extensible storage. This will not only provides scalability in terms of database size, but also the number of concurrent users.

Query Performance

2010-03-17T09:09:10-05:00

select * into OUTFILE 'E:\flatfiletomysql\qData.txt'  fields terminated by ',' 
enclosed by '"' lines terminated by 'n'
from
(

select      sum(CASE di.FieldID
                  WHEN 'dipc1'
                  then DI.value
                end) as dipc1,
            sum(CASE di.FieldID
                  WHEN 'dipc2'
                  then DI.value
                end) as dipc2,
            sum(CASE di.FieldID
                  WHEN 'dipc3'
                  then DI.value
                end) as dipc3
    from    t3 DI
    where di.dsc1>=1 and di.dsc1<=10000    
    group by di.dic1,
            di.dic2
) 

We have a table which we have to pivot on around 385 columns. The above is the query that we are running. The table t3 has around 270 million rows. When we executed this query for around 10,000 records(for each dsc1 there are around 8 rows, so around 80000 rows) and write it to a text file, the time taken is around 1hour, 27 minutes. Is there any way we can optimize this query.

I would also like to copy the results of this query into a new table which has to be created within the query. When i did the same, i am
getting an error like ‘ERROR 8 (HY000): Unsupported data type.’ If i am executing the same for less number of columns say 10, the table gets created.

Which method is the most effective one, copy the results to a text file and then load the file data to a table or move the results directly to a new table created by query.

Regards,

Subbu

Brighthouse as pluggable storage engine

2010-03-18T16:16:07-05:00

Is there a way to install the brighthouse engine to an existing MySQL install? I’ve been under the impression that I could “enable” brighthouse much in the same way I can add other pluggable engines to an existing install of MySQL. I would love to add brighthouse support to an existing server, and selectively pick certain engines for certain tables. Similarly, I would like to take advantage of the existing infrastructure I have in place for support of my brighthouse based system, which is much harder to do if I start with a “fresh” install.

Unless I’m misreading it, it seems I have to install a specialized version of mysql in order to get brighthouse support.

The speed or slowlyness of Talend tInfobrihtOutput connector

2010-03-17T08:08:57-05:00

First of all I just want to say “thank you” to everybody who posted his or her questions and answers concerning Talend connector. Forum was THE place to find important clues and bits to get the Talend run with Infobright.

Newer the less, the system is running but the loading speed I do observe is still far away from the reported numbers in other configurations. That said, the Engine load 90 lines a 100 cols in about 3 sec if loaded with “load data infile ...” sql command. If submitted through Talend connector the system will never load more than 700 rows per second. This means almost two orders of magnitude difference in loading speed.

According to the docs the Talend connector is using named pipe feeding the bulk loader which shall work incrediby fast. But either java, or my computer or some other wrong option in the database is throttling the load speed.

Does anybody else share the same expirience?

I am using ICE 3.3.1 and Talend Open Studio 3.2

Sincerely
Nikola

P.S. Please appologise my not realy good english

infobright page allocation failure and corruption after server crash

2010-03-17T11:28:57-05:00

Our infobright 3.3.1 has crashed yesterday and since we’ve been rebooted him it all goes wrong…

bhloader keeps on loading even when the csv only 4 rows big.

no process is getting RES memory, only mysql takes up the 27GB VIRT memory (as defined in brighthouse.ini). Suddenly the box goes out of memory

when stracing bhloader i get:
...
stat(”/var/lib/mysql/facebox/LOG_MOBILE_PROFILEVIEW.bht/TA00000001797486.ctb”, 0x7ffff9a35730) = -1 ENOENT (No such file or directory)
stat(”/var/lib/mysql/facebox/LOG_MOBILE_PROFILEVIEW.bht/TA00000001797487.ctb”, 0x7ffff9a35730) = -1 ENOENT (No such file or directory)
stat(”/var/lib/mysql/facebox/LOG_MOBILE_PROFILEVIEW.bht/TA00000001797488.ctb”, 0x7ffff9a35730) = -1 ENOENT (No such file or directory)
stat(”/var/lib/mysql/facebox/LOG_MOBILE_PROFILEVIEW.bht/TA00000001797489.ctb”, 0x7ffff9a35730) = -1 ENOENT (No such file or directory)
stat(”/var/lib/mysql/facebox/LOG_MOBILE_PROFILEVIEW.bht/TA00000001797490.ctb”, 0x7ffff9a35730) = -1 ENOENT (No such file or directory)
stat(”/var/lib/mysql/facebox/LOG_MOBILE_PROFILEVIEW.bht/TA00000001797491.ctb”, 0x7ffff9a35730) = -1 ENOENT (No such file or directory)
stat(”/var/lib/mysql/facebox/LOG_MOBILE_PROFILEVIEW.bht/TA00000001797492.ctb”, 0x7ffff9a35730) = -1 ENOENT (No such file or directory)
stat(”/var/lib/mysql/facebox/LOG_MOBILE_PROFILEVIEW.bht/TA00000001797493.ctb”, 0x7ffff9a35730) = -1 ENOENT (No such file or directory)
stat(”/var/lib/mysql/facebox/LOG_MOBILE_PROFILEVIEW.bht/TA00000001797494.ctb”, 0x7ffff9a35730) = -1 ENOENT (No such file or directory)
stat(”/var/lib/mysql/facebox/LOG_MOBILE_PROFILEVIEW.bht/TA00000001797495.ctb”, 0x7ffff9a35730) = -1 ENOENT (No such file or directory)
stat(”/var/lib/mysql/facebox/LOG_MOBILE_PROFILEVIEW.bht/TA00000001797496.ctb”, 0x7ffff9a35730) = -1 ENOENT (No such file or directory)
stat(”/var/lib/mysql/facebox/LOG_MOBILE_PROFILEVIEW.bht/TA00000001797497.ctb”, 0x7ffff9a35730) = -1 ENOENT (No such file or directory)
stat(”/var/lib/mysql/facebox/LOG_MOBILE_PROFILEVIEW.bht/TA00000001797498.ctb”, 0x7ffff9a35730) = -1 ENOENT (No such file or directory)
stat(”/var/lib/mysql/facebox/LOG_MOBILE_PROFILEVIEW.bht/TA00000001797499.ctb”, 0x7ffff9a35730) = -1 ENOENT (No such file or directory)
...

There must be some corruption somehwere, can anybody help me?

Valid looking license file produces: License data verification failed out of nowhere after a few days of use.

2010-03-17T17:45:53-05:00

Even though everything looks right, here is what I get:

Error Log:
2010-03-16 21:23:58 License file path : c:program filesinfobrightiblicense-mvaccarello-25969019.lic
2010-03-16 21:23:58 License data verification failed.
The License File appears to be correct:
LicenseID=25969019
User=michael
Email=mvaccarello@4sightbi.com
Version=3
Start=2010/03/11
Expire=2010/03/25

### Infobright Signature Begin ###
big bunch of numbers and stuff ....
### Infobright Signature End ###

The INI File has:
LicenseFile=‘c:\program files\infobright\iblicense-mvaccarello-25969019.lic’

time_to_sec mysql function

2010-03-17T09:42:59-05:00

I have a table with a time field. I have used time_to_sec function to convert the time to seconds from midnight. I need this to create 5 min buckets for data…

select
sec_to_time((floor(time_to_sec(time)/300)+1)*300) as time_bucket,
count(*)
from
trades
where
date=“20090908”
group by time_bucket
order by time_bucket

1) I dont see time_to_sec & sec_to_time in the list of optimized functions for Infobright. So does that mean my query is running slow (it takes 70 seconds for about 25M records)
2) is there a better way I can write the above query?

thanks for your help in advance.

Nishant

Need help with InfoBright crash on Union with Order by

2010-03-11T20:06:07-05:00

Problem: InfoBright v3.3.1 and v3.3.2beta crash and disconnect with the following setup/query on Windows XP Pro:

1) create a new table
CREATE TABLE `MYTABLE` (
`ID` VARCHAR(100) NOT NULL
);

2) populate table with the following six rows of data:
100
100
200
300
300
300

3) run this query

(select distinct ID, count(ID) as aaa from MYTABLE where ID=‘100’ or ID=‘300’ group by ID)
union
(select distinct ID, 0 as aaa from MYTABLE where ID not in (select ID from MYTABLE where ID=‘100’ or ID=‘300’))
order by ID

4)
crash

5) desired output
ID aaa
100 2
200 0
300 3

Also, any suggestions on a more elegant way to write that query are appreciated :^)

Problems with running a Stored Function

2010-03-16T20:58:57-05:00

Hi, I have a trades table with about 200M records. There is also a stored function called fnClosePrice() with the following definition…
BEGIN
declare cp float;
set cp=0.45;
return cp;
END

select fnClosePrice(); runs as expected and returns 0.45

A simple query on the trades table like the following also runs fine…

select bsid,time from trades limit 100;

However, when I include the function in the above query i get an error…

select bsid, fnClosePrice() from trades limit 100;

The query includes syntax that is not supported by the Infobright Optimizer. Either restructure the query with supported syntax, or enable the MySQL Query Path in the brighthouse.ini file to execute the query with reduced performance.

what am I missing here? any insight is appreciated.

thanks