Big help...

I wanted to get my table sizes in infobright, and this page came to my help...

SELECT table_schema,table_name,engine, table_rows, avg_row_length,
(data_length+index_length)/1024/1024 as total_mb,(data_length)/1024/1024 as data_mb,
(index_length)/1024/1024 as index_mb, CURDATE() AS today
FROM information_schema.tables
WHERE table_schema='mySchemaName'
ORDER BY 7 DESC

Thanks Ron...
in reference to: Calculating your database size | MySQL Expert | MySQL Performance | MySQL Consulting (view on Google Sidewiki)

Explain plan of Infobright

We all knw that Infobright is based on MySql. However, when I started with Infobright, I was equally unaware of both.

Today I got to know about Infobright's explain plan collection technique, and realized how different it is from Oracle's.

A typical explain plan from oracle talks about the path it follows to retrieve the data, and somehow makes common sense to read a plan.

On the other hand, an explain plan from Infobright looks nothing like the Oracle's plan. Its a set of cryptic rows put together. An example of such a plan -

2010-03-18 01:31:01 [4] T:-1 = TABLE_ALIAS(T:0,"myTableName")
T:-2 = TMP_TABLE(T:-1)
VC:-2.0 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:20))
A:-1 = T:-2.ADD_COLUMN(VC:-2.0,AVG,"avg(col1)","ALL")
A:-2 = T:-2.ADD_COLUMN(VC:-2.0,MAX,"max(col1)","ALL")
A:-3 = T:-2.ADD_COLUMN(VC:-2.0,MIN,"min(col1)","ALL")
A:-4 = T:-2.ADD_COLUMN(,COUNT,"count(1)","ALL")
VC:-2.1 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:2))
A:-5 = T:-2.ADD_COLUMN(VC:-2.1,GROUP_BY,"col2","ALL")
VC:-2.2 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:5))
A:-6 = T:-2.ADD_COLUMN(VC:-2.2,GROUP_BY,"col3","ALL")
VC:-2.3 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:7))
A:-7 = T:-2.ADD_COLUMN(VC:-2.3,GROUP_BY,"col4","ALL")
VC:-2.4 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:11))
A:-8 = T:-2.ADD_COLUMN(VC:-2.4,GROUP_BY,"col5","ALL")
VC:-2.5 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:12))
A:-9 = T:-2.ADD_COLUMN(VC:-2.5,GROUP_BY,"col6","ALL")
VC:-2.6 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:13))
A:-10 = T:-2.ADD_COLUMN(VC:-2.6,GROUP_BY,"col7","ALL")
VC:-2.7 = CREATE_VC(T:-2,PHYS_COL(T:-2,A:-1))
T:-2.ADD_ORDER(VC:-2.7,DESC)
RESULT(T:-2)

And, guess what, to generate a plan, you have to change a setting in the initialization parameters file, and then bounce the db. I wonder if there have been any instances when someone had to look into production database's performance and therefore needed to look at some plan.

Being as new a system as it is, I cant really complain, since such things of maturity will slowly creep in. The focus right now is probably elsewhere, building the functionality, and probably performance.

in reference to: Infobright.org Forums | Enable MySQL Logging (view on Google Sidewiki)

availability of native drivers !!!

As I mentioned earlier, I was busy experimenting with a columnar database, InfoBright.

The experience till now is mixed, in some scenarios, the data loading is very good, as much as 40k rows per second, whereas through other channels its a poorer 500 rows per second. When I go with their built in loader, its lightening fast, but when I try from Pentaho or Informatica, its measly.

Apparantly, the drivers and the compatibility of the third party tools do play a role in attaining the performance.

The fact that they dont have any native driver published, is a huge bottleneck. Informatica dont even have a native connector for MySql, the more known cousin of InfoBright (being open source, the core engine of InfoBright is based on MySql only).

One thing came out of this experience for sure, the liking for open source got better and better. During the hunt to see the reason for slow performance from Pentaho, I even tried and managed to get the source code of the plugin (transformation) that pentaho uses for Infobright. Its pure java and it was a very powerful and awakening feeling to see the code of the item. I felt as if I have the power, the choice to make a difference, make it better. :)


I am currently exploring other options, one of them includes creating a dump file (read : csv) of the data, and then launching the command line tool to load the data into target db. I dont like it, but lets see if there is any better (read:faster) way around...