Duplicate records being for formed for Internal Materialized View cluster

[pranavk]

Hi,

I am testing internal materialized views over a cluster. When I run a query over one of the nodes of a cluster (say node A) to fetch 25000 records (with internal materialization switched on), the cache gets formed on node A and presumably gets synced using the JgroupsObjectReplicator. When I run the same query on the cache formed in the other node (node B), I noticed that I was getting 25600 records. When I analyzed the records, I saw there were only 1024 distinct records each of which repeated 25 times.

I am using Teiid Embedded 8.10. I saw a similar issue with 8.11 too.

And I am querying over a JDBC source (MSSQL server )

Would you have any idea why this could be occurring?

Thanks,

Pranav

[rareddy]

Are you saying you duplicated above with Teiid Server 8.11 running in EAP?

[shawkins]

There are several things to look at here.  The first would be to understand if the row counts are different - generally I don't see how that would be possible unless each node loads it's own version of the data.  And even in that case you would still expect them to become consistent after the second load is broadcast.

As for duplicate rows, the first thing to look as is what your view logic is doing.  Of course you can quickly determine if materialization is involved by either disabling it or using option nocache and seeing if the results are the same.

[pranavk]

I am using Teiid embedded 8.10 (& 8.11 too) and setting the jgroups file in EmbeddedConfiguration for setting up the clustered mode  Internal Materialized views.

[pranavk]

Yes, we observe this behavior of duplicate records only when Internal Materialization is switched on, without it we are getting the original set of 25k records.

The logical view I used did not contain any transformations or criterion, it was just mapping to the columns of the source model. If required, I can share the query being executed at the source.

[shawkins]

Yes, I do see an issue.  The replication is repeating only the first tree page.  Can you log something for this?

[pranavk]

Sure, I have logged TEIID-3558. Would this be available in a patch or something like 8.11.1?

Thanks

[shawkins]

Yes, it will be in 8.12 Alpha1 (in approximately 2 weeks) and 8.11.1 (in approximately 4 weeks).

[pranavk]

Thanks Steve,

There was another observation I wanted to share. I ran a select * query over DB table lying in MSSQL server installed on the same machine, having 48 million records and 7 columns. I had switched on Internal Materialized caching on this view model, which was made over the source model by directly mapping all columns (and without any transformations). It took around 100 mins for the this query to execute (including caching time). The db table size was seen to be around 4GB, but the temp files created by Teiid for this file took around 9.5GB of space. JVM size available was 8GB.

Could you point me to a reason for this/any suggestion to improve the performance?

[rareddy]

Pranav,

You can not compare sizes directly to the database and Teiid. In Teiid it is serialized with size of java objects of the data, so there will be a overhead compare to the native sizes. However, even though you have 8GB, Teiid should effectively manage the memory so that you are not running into OutOfMemory issues.

BTW, the performance is issue you mentioning is it about how long it takes to cache? Effectively  the system is reading each row and saving in Teiid. Have you done any other tests to compare how much time it should take read 48 million records, and save? Also, practically it is not much useful to cache source table as is in Teiid, unless you are trying to stop calls to the source database. Most of the times, you want to  materialize a view that takes more time to pull the data and processing it like aggregating, sorting etc, so that you can avoid doing it every time and improve performance. In the previous case, you just avoided a data access, but you removed the co-location of data with other tables, thus may limit the push down functionality. The only difference is, is reading from a locally cached table faster or reading from remote db is faster? IMO, that particular statistic gets smudged when table size is relatively large.

Ramesh..

[pranavk]

Thanks Ramesh, that was helpful. Ofcourse, the one I mentioned, is not the best use case for Internal Materialized view.