Infobright.org Forums

Question re concurrent query performance

2010-03-12T10:54:23-05:00

Posted on behalf of an IEE evaluator:

I have a puzzling scenario that I wanted to run by you in order to see if understanding Infobright’s internals causes it to make any more sense. I have been working to test concurrent query performance, starting with concurrency scaling for multiple instances of the same query. That went mostly as expected. I now am testing concurrent performance involving a mix of queries and am seeing strange behavior.

For this scenario, I have chosen three different queries to run in various mixes against the system: One fast query (0.12 seconds), one slightly slower query (1.1 seconds) and a very slow query (60.7 seconds). What I have noticed is that while a long-running query is being processed, the two slower queries will return significantly faster average execution times (.12 goes to .06, 1.1 goes to 0.551). This is three concurrent queries on a 4-core box, so the CPU is not being maxed. It seems as if there is something being loaded into memory or cache while the large query runs that is being used by the other queries, but that they must load for themselves if nothing else is happening. Knowing what you do about Infobright, is there a simple explanation for this behavior? The benefit appears to disappear with longer queries, so is this some kind of trade off that was made given the usual longer-running nature of many data warehouse queries?

Bulk Load How it works?

2010-03-12T10:48:54-05:00

Hello, I wonder how the bulk load, if you have any manual or something, because I do not go well and I like to see on that ruling and I have to change for me out properly. Thank you very much and I hope you can help.

Marc

PD: If possible answer in Spanish best =)! Thanks

Instant integration and database

2010-03-10T11:03:41-05:00

Hello,
We have been looking for a real time data integration program.
We have not found the right software yet.

Basically we’ll be using the tool for the migration of contacts and data in real time.

Thanks for your help!

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 :^)

Data Load Time

2010-03-10T10:20:24-05:00

We are loading a 43 MB file containing 2 million rows with just 3 columns - all integers. The following is the syntax we used

LOAD DATA LOCAL INFILE 'D:DatasetFact.txt' INTO TABLE DatasetFact FIELDS
 TERMINATED BY ',' LINES TERMINATED BY 'n'; 

The following are some Sample rows

15253,89,226338677
15253,165,226340065
15253,231,226342095

The following is the table script

CREATE TABLE `entitydatasetpairs` (
  `EntityKey` int(11) NOT NULL,
  `CategoryKey` int(11) NOT NULL,
  `LogicalPrimaryKey` int(11) NOT NULL
) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=latin1; 

This is running on the database server which has 12 GB of memory with a Quad Core processor running Windows Server 2008 Standard Edition.

To load this 2 million rows it has taken 31 minutes. This seems like a very slow.

Is there something we can do to improve the performance of this loading.

Thanks
Subu

Query generating “uncompressed memory block(s) leaked out” warnings

2010-03-05T17:41:14-05:00

Hello,

I am using ICE 3.3.0.

Getting this warnings when running a query:
2010-03-05 16:06:27 Warning: 10054 uncompressed memory block(s) leaked out.
2010-03-05 16:06:27 Warning: 10054 compressed memory block(s) leaked out.
2010-03-05 16:10:32 Brighthouse engine shutdown.

Turned on
ControlMessages = 2

The query was hanging at the point of sorting data:
..
..
2010-03-05 16:04:18 [1] Packrows after exact evaluation (WHERE):
2010-03-05 16:04:18 [1] (t0): 1 all packrows, 1 to open (including 1 full)
2010-03-05 16:04:18 [1] (t1): 131 all packrows, 131 to open (including 131 full)
2010-03-05 16:04:18 [1] Join execution plan:
2010-03-05 16:04:18 [1] Cnd(0): VC:0(t0a*) = VC:5(t1a*) (35.96)
2010-03-05 16:04:18 [1] Traversed all 12 rows.
2010-03-05 16:04:21 [1] Produced 685018 tuples.
2010-03-05 16:04:21 [1] Tuples after inner join 0-1 [hash]: 685018
2010-03-05 16:04:21 [1] Sorting 685018 rows…
2010-03-05 16:04:22 [1] Sorted with 64+64-bit keys.
2010-03-05 16:04:57 [1] Stopped by user.

Some how the warning message won’t come out until I shutdown the database.

Tried changing the system memory, heap size,.. etc but did not make a difference.

Any help would be much appreicated.

Thanks and regards,
Addie

Using InforBright engine..

2010-03-03T02:54:23-05:00

Hello Everybody,

I am a newbie here and pretty novice when it comes about using Infobright.

I am using MySQL as my database. I tried using infobright. Started the infobright client and executed ‘show databases;’ but I cannot see all tables which are there in MySQL. I created a new table using engine=BRIGHTHOUSE suffix and tried to load sql dump into it but I am getting some weird errors like ‘wrong data or column’.

Now this is what I am doing..it could sound silly but I am doing it Kindly correct me if I am doing anything wrong.

I want to access MYSQL with infobright from the front end (J2EE,Springs) simply to perform some analysis? How should I approach it? When I do ‘show engines’ in SQLyog, I dont get infobright there and when I do ‘show engines’ on this infobright client I get brighthouse as an engine but dont get all those tables…

Hope my que is not very confusing..Kindly let me know if you need more clarification from my side.

Thanks,
Pankaj

Blank pentaho pages

2010-03-01T08:24:30-05:00

Hi all,

I’m having trouble working out how to see any useful pages in the pentaho webapp. Basically if I go to http://xx.xx.xx.xx:8080/pentaho I can log in as one of the demo users, then a blank page loads with the (window) title “Pentaho BI Platform”. Subsequent request to this URL take me straight to the blank page. Page source:

<?xml version=“1.0” encoding=“utf-8”?>
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
<html >
<head>
<title>Pentaho BI Platform</title>
<meta http-equiv=“refresh” content=“0;URL=/pentaho”>
</head>
<body>
</body>
</html>

If I point my browser at port 80 on the appliance, i see the ICE32 appliance welcome page ok, and http://xx.xx.xx.xx:8080/pentaho/welcome/welcome.html works just fine (but the JS links don’t work), and http://xx.xx.xx.xx:8099 gives me the pentaho management console, which seem to work fine. These suggest tomcat is up and running ok.

Set up: clean appliance (downloaded today) with the following changes only (as in README):
Updated web.xml/base-url with my appliance’s IP
Updated /etc/hosts with my appliance’s IP
sudo /etc/init.d/pentaho stop
sudo /etc/init.d/pentaho start

I’ve not done anything with shared folders, as I saw the message “initial install is using small internal local disk;
for larger data sets, you need to use shared/data and shared/cache…” and thought i’d get things working first without worrying about big data sets. Should this be a problem?

I’ve checked the logs in /usr/local/pentaho/biserver-ce/tomcat/logs, but not much shows up other than the fact the server has started.

Any thoughts?

Cheers,

Ben

ICE Installation/startup errors

2010-02-26T17:21:31-05:00

ok guys..my mistake…Just figured out that RHEL4 is not in the list of supported platform for ICE

++++++++++++++++++++++++++++++++++++++++++++++++++++++

DO NOT READ BELOW

I am a newbie to ICE. Just started installing the latest ICE 3.3.1 on Red Hat Enterprise Linux 4 when I got whole bunch of installation errors

[root@amrtrgt2 ~]# rpm -ivh infobright-3.3.1-x86_64-ice.rpm
Preparing… ########################################### [100%]
Installing infobright 3.3.1 x86_64
The installer will generate /tmp/ib3.3.1-install.log install trace log.
1:infobright ########################################### [100%]
Creating/Updating datadir and cachedir
Creating user mysql and group mysql
Installing default databases
/usr/local/infobright/bin/my_print_defaults: /lib64/tls/libc.so.6: version `GLIBC_2.4’ not found (required by /usr/local/infobright/bin/my_print_defaults)
Neither host ‘amrtrgt2’ nor ‘localhost’ could be looked up with
/usr/local/infobright/bin/resolveip
Please configure the ‘hostname’ command to return a correct
hostname.
If you want to solve this at a later stage, restart this script
with the—force option
Failed on infobright initialization. You can investigate log files inside /usr/local/infobright/data.
System Physical memory: 11955(MB)
/var/tmp/rpm-tmp.35637: line 296: /usr/local/infobright/data/brighthouse.ini: No such file or directory
mv: cannot stat `/usr/local/infobright/data/brighthouse.ini.tmp’: No such file or directory
Infobright optimal ServerMainHeapSize is set to 4000(MB)
Infobright optimal ServerCompressedHeapSize is set to 500(MB)
Infobright optimal LoaderMainHeapSize is set to 800(MB)
Infobright server installed into folder /usr/local/infobright
Installation log file /tmp/ib3.3.1-install.log
———————————————————
To activate infobright server, please run ./postconfig.sh script from /usr/local/infobright-3.3.1-x86_64.
Example command: cd /usr/local/infobright-3.3.1-x86_64; ./postconfig.sh

On starting ICE server, it says

[root@amrtrgt2 infobright-3.3.1-x86_64]# /etc/init.d/mysqld-ib start
./bin/my_print_defaults: /lib64/tls/libc.so.6: version `GLIBC_2.4’ not found (required by ./bin/my_print_defaults)
./bin/my_print_defaults: /lib64/tls/libc.so.6: version `GLIBC_2.4’ not found (required by ./bin/my_print_defaults)
Starting MySQL.FATAL: kernel too old

My kernel version is

[root@amrtrgt2 infobright-3.3.1-x86_64]# uname -a
Linux amrtrgt2 2.6.9-5.ELsmp #1 SMP Wed Jan 5 19:29:47 EST 2005 x86_64 x86_64 x86_64 GNU/Linux

Am I using wrong Linux version?

Any help will be appreciated.

UNION ALL very slow

2010-02-25T18:03:26-05:00

When I run the following query UNION ALL I get the 1000 rows in 25-30 seconds. I’ve reduced the query to its simplest form for demonstration purposes.

SELECT z.account_number
FROM tickets.order_tickets_200911 z
WHERE z.order_date >= '2009-11-16 00:00:00'
  and z.order_date <= '2009-11-16 23:59:59'
UNION ALL
SELECT e.account_number
  FROM tickets.execution_tickets_200911 e
 WHERE e.execution_date >= '2009-11-16 00:00:00'
   AND e.execution_date <= '2009-11-16 23:59:59'
LIMIT 1000 

From bh.err for the UNION:

2010-02-25 17:42:51 [730] T:-1 = TABLE_ALIAS(T:0,“order_tickets_200911”)
T:-2 = TMP_TABLE(T:-1)
VC:-2.0 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:4))
A:-1 = T:-2.ADD_COLUMN(VC:-2.0,LIST,“account_number”,“ALL”)
VC:-2.1 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:0))
VC:-2.2 = CREATE_VC(T:-2,EXPR(“2009-11-16 00:00:00”))
F:0 = CREATE_FILTER(T:-2,VC:-2.1,>=,VC:-2.2,<null>,WHERE)
VC:-2.3 = CREATE_VC(T:-2,EXPR(“2009-11-16 23:59:59”))
F:0 = AND(F:0,VC:-2.1,<=,VC:-2.3,<null>)
T:-2.FILTER(F:0)
T:-3 = TABLE_ALIAS(T:1,“execution_tickets_200911”)
T:-4 = TMP_TABLE(T:-3)
VC:-4.0 = CREATE_VC(T:-4,PHYS_COL(T:-3,A:2))
A:-1 = T:-4.ADD_COLUMN(VC:-4.0,LIST,“account_number”,“ALL”)
VC:-4.1 = CREATE_VC(T:-4,PHYS_COL(T:-3,A:1))
VC:-4.2 = CREATE_VC(T:-4,EXPR(“2009-11-16 00:00:00”))
F:1 = CREATE_FILTER(T:-4,VC:-4.1,>=,VC:-4.2,<null>,WHERE)
VC:-4.3 = CREATE_VC(T:-4,EXPR(“2009-11-16 23:59:59”))
F:1 = AND(F:1,VC:-4.1,<=,VC:-4.3,<null>)
T:-4.FILTER(F:1)
T:-2 = UNION(T:-2,T:-4,1)
T:-2.MODE(LIMIT,0,1000)
RESULT(T:-2)

2010-02-25 17:42:51 Type conversion for VC:2
2010-02-25 17:42:51 Type conversion for VC:3
2010-02-25 17:42:51 [730] Merging conditions…
2010-02-25 17:42:51 [730] Initial execution plan (non-join):
2010-02-25 17:42:51 [730] Cnd(0): VC:1(t0a0) BET. VC:6 AND VC:9 (17.29)
2010-02-25 17:42:51 [730] Packs/packrows after KN evaluation:
2010-02-25 17:42:51 [730] (t0) Pckrows: 9758, susp. 19 (9242 empty 497 full). Packs opened in 1 cond.: 19
2010-02-25 17:42:51 [730] Packrows after exact evaluation (WHERE):
2010-02-25 17:42:51 [730] (t0): 9758 all packrows, 516 to open (including 497 full)
2010-02-25 17:42:51 Type conversion for VC:2
2010-02-25 17:42:51 Type conversion for VC:3
2010-02-25 17:42:51 [730] Merging conditions…
2010-02-25 17:42:51 [730] Initial execution plan (non-join):
2010-02-25 17:42:51 [730] Cnd(0): VC:1(t0a1) BET. VC:6 AND VC:9 (12.69)
2010-02-25 17:42:51 [730] Packs/packrows after KN evaluation:
2010-02-25 17:42:51 [730] (t0) Pckrows: 142, susp. 4 (133 empty 5 full). Packs opened in 1 cond.: 4
2010-02-25 17:42:51 [730] Packrows after exact evaluation (WHERE):
2010-02-25 17:42:51 [730] (t0): 142 all packrows, 9 to open (including 5 full)

But running each portion separately, the queries are sub-second? Why? Even with more criteria, joins to dimension tables and 10 or 11 columns the individual queries take but a few seconds while the UNION ALL never finishes.

From bh.err (first query):

2010-02-25 17:45:07 [736] T:-1 = TABLE_ALIAS(T:0,“order_tickets_200911”)
T:-2 = TMP_TABLE(T:-1)
VC:-2.0 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:4))
A:-1 = T:-2.ADD_COLUMN(VC:-2.0,LIST,“account_number”,“ALL”)
VC:-2.1 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:0))
VC:-2.2 = CREATE_VC(T:-2,EXPR(“2009-11-13 00:00:00”))
F:0 = CREATE_FILTER(T:-2,VC:-2.1,>=,VC:-2.2,<null>,WHERE)
VC:-2.3 = CREATE_VC(T:-2,EXPR(“2009-11-13 23:59:59”))
F:0 = AND(F:0,VC:-2.1,<=,VC:-2.3,<null>)
T:-2.FILTER(F:0)
T:-2.MODE(LIMIT,0,1000)
RESULT(T:-2)

2010-02-25 17:45:07 Type conversion for VC:2
2010-02-25 17:45:07 Type conversion for VC:3
2010-02-25 17:45:07 [736] Merging conditions…
2010-02-25 17:45:07 [736] Initial execution plan (non-join):
2010-02-25 17:45:07 [736] Cnd(0): VC:1(t0a0) BET. VC:6 AND VC:9 (17.22)
2010-02-25 17:45:07 [736] Packs/packrows after KN evaluation:
2010-02-25 17:45:07 [736] (t0) Pckrows: 9758, susp. 19 (9276 empty 463 full). Packs opened in 1 cond.: 19
2010-02-25 17:45:07 [736] Packrows after exact evaluation (WHERE):
2010-02-25 17:45:07 [736] (t0): 9758 all packrows, 482 to open (including 463 full)
2010-02-25 17:45:07 [736] Displaying result: 1000 rows.
2010-02-25 17:45:07 [736] Total data packs actually loaded (approx.): 9
2010-02-25 17:45:07 [736]——————————————————————————————————————

From bh.err (second query):

2010-02-25 17:47:05 [736] T:-1 = TABLE_ALIAS(T:0,“execution_tickets_200911”)
T:-2 = TMP_TABLE(T:-1)
VC:-2.0 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:2))
A:-1 = T:-2.ADD_COLUMN(VC:-2.0,LIST,“account_number”,“ALL”)
VC:-2.1 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:1))
VC:-2.2 = CREATE_VC(T:-2,EXPR(“2009-11-16 00:00:00”))
F:0 = CREATE_FILTER(T:-2,VC:-2.1,>=,VC:-2.2,<null>,WHERE)
VC:-2.3 = CREATE_VC(T:-2,EXPR(“2009-11-16 23:59:59”))
F:0 = AND(F:0,VC:-2.1,<=,VC:-2.3,<null>)
T:-2.FILTER(F:0)
T:-2.MODE(LIMIT,0,1000)
RESULT(T:-2)

2010-02-25 17:47:05 Type conversion for VC:2
2010-02-25 17:47:05 Type conversion for VC:3
2010-02-25 17:47:05 [736] Merging conditions…
2010-02-25 17:47:05 [736] Initial execution plan (non-join):
2010-02-25 17:47:05 [736] Cnd(0): VC:1(t0a1) BET. VC:6 AND VC:9 (12.69)
2010-02-25 17:47:05 [736] Packs/packrows after KN evaluation:
2010-02-25 17:47:05 [736] (t0) Pckrows: 142, susp. 4 (133 empty 5 full). Packs opened in 1 cond.: 4
2010-02-25 17:47:05 [736] Packrows after exact evaluation (WHERE):
2010-02-25 17:47:05 [736] (t0): 142 all packrows, 9 to open (including 5 full)
2010-02-25 17:47:05 [736] Displaying result: 1000 rows.
2010-02-25 17:47:05 [736] Total data packs actually loaded (approx.): 0
2010-02-25 17:47:05 [736]——————————————————————————————————————

Some vitals on our system:

tickets.order_tickets_200911 - 639467519 records
tickets.execution_tickets_200911 - 9290427 records

Red Hat Enterprise Linux Server release 5.1 / 64Gb RAM
IB_3.2_GA_5316(ice)
data and cache directories over NFS (Netapp SAN)

ServerMainHeapSize = 24000
ServerCompressedHeapSize = 4000
LoaderMainHeapSize = 1200

Thanks for the help!