Infobright.org Forums

Simple query with join takes much longer than expected

2012-01-09T12:35:13-04:00

The following query takes about eight minutes to execute;

select
i.ADVERTISER_ID,
i.CAMPAIGN_GROUP_ID,
i.CAMPAIGN_ID,
i.AUCTION_ID_64,
i.TIMESTAMP AS ImpTime,
v.DATETIME AS ConvTime
from tblpvconvs v inner join digitize.tblimpressions i on v.AUCTION_ID_64 = i.AUCTION_ID_64;

tblimpressions has 1086962700 rows
tblpvconvs has 8018 rows

Is there a trick that I am missing?

Which level of transaction isolation does ICE support?

2011-12-22T04:29:29-04:00

hi,
please reply.
I use the following commands :
select @@global.tx_isolation, @@tx_isolation;show engines;

and I get some information that the default transaction isolation level is REPEATABLE-READ and the brighthouse support transactions.

Now I want change the transaction isolation level ,and I use the “SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;” command ,but it seems not work.

So Can anyone tell me Which level of transaction isolation does ICE support?
thanks a lot

is ice support snapshot backup?

2011-12-06T02:58:29-04:00

hi,
Can i use a snapshot to backup databases.
I find that some snapshot backup use mysqladmin, mysqlhotcopy, mysqldump mysqlbinlog, these are mysql client command.
I have seen then user guide that backup is just copy the entire data folder. Any other method?

thanks

I/O operation exception

2011-11-25T05:26:08-04:00

Error Messages is :
Exception during sql named pipe use: I/O operation on /data/DBname/cfg_channel_program_dim.bht/TA00000000000000.ctb failed with error - Permission denied File name /data/DBname/cfg_channel_program_dim.bht/TA00000000000000.ctb.

how to fix this error?

Convert vertical data

2011-11-09T16:44:45-04:00

I have a table that store a key=value reference of arbitrary data from my main records, and each record could have N number of key=values.

How can I convert that data to fit on ICE and use the full potential of its optimizer?

how to fix the issue “communications link failure”

2011-10-16T23:17:07-04:00

hi,
I use Kettle as etl tools to load data to Infobright. But I am getting this exception frequently when i use “infobright loader”, i hava searched google for this issue. but i still cannot fix it.

2011/10/17 10:07:56 - Infobright Loader.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : Exception during sql named pipe use: Communications link failure due to underlying exception:
2011/10/17 10:07:56 - Infobright Loader.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) :
2011/10/17 10:07:56 - Infobright Loader.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : ** BEGIN NESTED EXCEPTION **
2011/10/17 10:07:56 - Infobright Loader.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) :
2011/10/17 10:07:56 - Infobright Loader.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : java.io.EOFException
2011/10/17 10:07:56 - Infobright Loader.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) :
2011/10/17 10:07:56 - Infobright Loader.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : STACKTRACE:
2011/10/17 10:07:56 - Infobright Loader.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) :
2011/10/17 10:07:56 - Infobright Loader.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : java.io.EOFException
2011/10/17 10:07:56 - Infobright Loader.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1934)
2011/10/17 10:07:56 - Infobright Loader.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2380)
2011/10/17 10:07:56 - Infobright Loader.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2909)
2011/10/17 10:07:56 - Infobright Loader.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:798)
2011/10/17 10:07:56 - Infobright Loader.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : at com.mysql.jdbc.MysqlIO.sendFileToServer(MysqlIO.java:2883)
2011/10/17 10:07:56 - Infobright Loader.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:1989)
2011/10/17 10:07:56 - Infobright Loader.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1413)
2011/10/17 10:07:56 - Infobright Loader.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1757)
2011/10/17 10:07:56 - Infobright Loader.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : at com.mysql.jdbc.Connection.execSQL(Connection.java:3020)
2011/10/17 10:07:56 - Infobright Loader.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : at com.mysql.jdbc.Connection.execSQL(Connection.java:2949)
2011/10/17 10:07:56 - Infobright Loader.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : at com.mysql.jdbc.Statement.execute(Statement.java:538)
2011/10/17 10:07:56 - Infobright Loader.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : at com.infobright.io.InfobrightNamedPipeLoader$ExecutionThread.run(InfobrightNamedPipeLoader.java:291)
2011/10/17 10:07:56 - Infobright Loader.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) :
2011/10/17 10:07:56 - Infobright Loader.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) :
2011/10/17 10:07:56 - Infobright Loader.0 - ERROR (version 4.1.0-stable, build 14410 from 2010-11-16 16.43.28 by buildguy) : ** END NESTED EXCEPTION **

Infobright in memory

2011-10-18T11:19:33-04:00

I was wondering if anybody tried to run Infobright in memory.
Taking under consideration that compression makes the database very small, why not just give it a try?
Of course, it’s not for extremely big databases, but for some sub-set, sort of cache or most recent part…
Anyway, when I get my hands on some more memory, I’ll try it myself.
Please let me know if anybody has any thoughts or experience.

Does that make any sense from the design point of view?
Thanks

C_G

Query performance: should I break the table based on a column value for better performance?

2011-09-21T13:54:34-04:00

Hi,

I have a table (raw_201109) with a number of columns, with one of the columns being a bigint “serial_int”. Most of the queries do have the clause “WHERE serial_int = some_number’. The possible values of serial_int can be in the order of 10s to 100s.

The table currently has 76 million rows. The overall performance of the queries on this particular database is not very good and I am looking at ways to improve performance. The following two queries will probably give an idea:-

mysql> select count(*) from raw_201109;
+----------+
| count(*) |
+----------+
| 76671448 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from raw_201109  where serial_int = 102090523644;
+----------+
| count(*) |
+----------+
| 27760361 |
+----------+
1 row in set (4.68 sec) 

My question is: since most of my queries use WHERE clause on serial_int, will my queries perform much better if I have multiple tables based on serial_int value, rather than a single table that has all the possible serial_int values? My understanding is that breaking up the tables doesn’t really help.

Thanks for your input!

Home UDF works fine on MyISAM and crashes Brighthouse table

2011-10-04T05:32:26-04:00

Hello

I have a home made UDF with which I want to use to somehow replace the match .. against queries (so that would work without index but the performance seems acceptable on the test table: 20M records ~40secs for 2 criteria on a low end development machine) that doesn’t work with tables others then MyISAM.

So I have this UDF ( test_udf() ) that works fine on MyISAM table and after a few match (the limit seems to be 80 matches) it just crashes the server.

mysql> select test_udf('+blah +test', code1) from test.test_udf_search_ib limit 81;
ERROR 2013 (HY000): Lost connection to MySQL server during query 

if the query involves a full table scan it also crashes

mysql> select test_udf('+blah +test', code1) from test.test_udf_search_ib where test_udf('+blah +test', code1) = 1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: *** NONE *** 

while all this works perfectly with a MyISAM table :

mysql> select test_udf('+blah +test', code1) from test.test_udf_search where test_udf('+blah +test', code1) = 1;
+--------------------------------+
| test_udf('+blah +test', code1) |
+--------------------------------+
|                              1 |
+--------------------------------+
1 row in set (44,43 sec) 

And with the Brighthouse table it works fine for a small amount of lines:

mysql> select test_udf('+blah +test', code1) from test.test_udf_search_ib limit 10;
+--------------------------------+
| test_udf('+blah +test', code1) |
+--------------------------------+
|                              0 |
|                              0 |
|                              1 |
|                              0 |
|                              0 |
|                              0 |
|                              0 |
|                              0 |
|                              0 |
|                              0 |
+--------------------------------+
10 rows in set (0,00 sec) 

I attach the UDF that I’m testing FYI. I compile it with

cc -I/usr/local/infobright/include/mysql/ -O -pipe -fPIC -o test_udf.so -shared -L/usr/local/infobright/lib/mysql/ -lmysqlibclient test_udf.cc 

under FreeBSD with the port I’ve written that will hopefully get its way in the port tree.

The UDF is still a proto and it should return 1 when the second argument satisfies the criteria in the first argument:

+--------------------------------------+
| test_udf('+blah +test', 'blah test') |
+--------------------------------------+
|                                    1 |
+--------------------------------------+
1 row in set (0,00 sec)

mysql> select test_udf('+blah +test', 'blah test');
+--------------------------------------+
| test_udf('+blah +test', 'blah test') |
+--------------------------------------+
|                                    1 |
+--------------------------------------+
1 row in set (0,00 sec)

mysql> select test_udf('+blah +test', 'blah test2');
+---------------------------------------+
| test_udf('+blah +test', 'blah test2') |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0,00 sec)

mysql> select test_udf('+blah +test', 'blah test hello');
+--------------------------------------------+
| test_udf('+blah +test', 'blah test hello') |
+--------------------------------------------+
|                                          1 |
+--------------------------------------------+
1 row in set (0,00 sec)

mysql> select test_udf('+blah -test', 'blah test hello');
+--------------------------------------------+
| test_udf('+blah -test', 'blah test hello') |
+--------------------------------------------+
|                                          0 |
+--------------------------------------------+
1 row in set (0,00 sec)

mysql> select test_udf('+blah test', 'blah test hello');
ERROR 1123 (HY000): Can't initialize function 'test_udf; missing a boolean operator in the token string 

Thanks

Eriam

replication and infobright

2011-07-20T10:27:59-04:00

we have a master/slave setup of two servers using mysql 5.1

we are thinking about a similar setup of an infobright master server and an infobright slave server, where the master replicates to the slave.

question: is this possible with infobright? anything to watch out for?

tks.

uwe