[JBoss JIRA] (TEIID-5643) When querying a larger dataset, teiid answers with 504 Gateway Time-out and throws exception
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5643?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5643:
---------------------------------------
> I am very sorry for stealing you the time and like to thank you a lot for your help. It seems like my ha proxy config was wrong. This timed out the connection and was responsible for the timeout. Once again, I am very sorry to introduce unrequired work. I will close the issue now.
No problem. I do agree though that the long initial wait time is not great. So I'm making a commit under TEIID-5500 to help with the initial load times. It sacrifices some overall performance especially in federated scenarios for a greatly reduced initial latency and lower penalty should the cached result set be pushed out of the cache before the end of the pages is reached.
> I do have one further question regarding the topic.
This is now captured under TEIID-5680 correct?
> When querying a larger dataset, teiid answers with 504 Gateway Time-out and throws exception
> --------------------------------------------------------------------------------------------
>
> Key: TEIID-5643
> URL: https://issues.jboss.org/browse/TEIID-5643
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 11.2.1
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Major
> Attachments: Heap Dump analysis tool.pdf, logfile2.txt, logfile3.txt
>
>
> Please note, I am using Teiid 11.2.0 and not 11.2.1 as specified above in this ticket. I was not able to select a version 11.2.0.
> The issue: I tried to query a table with a larger amount of records (about 600.000 records in the table). Teiid responds with
> 504 Gateway Time-out. The server didn't respond in time.
> If Teiid tries to respond with 600.000 entries to such a request, it is reasonable that it runs out of memory or other weird things happen. However, I would expect that in such a case Teiid just answers with a fixed amount of records, say for example 1000, and provides a kind of curser to enable subsequent requests to retrieve more data.
> I am currently trying to find a workaround for the issue, as the issue blocks my work. Is there maybe a kind of configuration option that I have to set explicitly for such a kind of behavior I previously sketched?
> Further note: When I send such a kind of select * request on the table to Teiid, I am also not able for a certain time interval, about a minute, to get a responds to different queries which select a single element on the mentioned table. Such a request then also results in a 504 Gateway Time-out.
> Attached error log:
> 2019-02-01 22:33:36,126 WARN [org.jboss.jca.core.connectionmanager.pool.strategy.OnePool] (Worker2_QueryProcessorQueue5) eayVFuq9y7OY IJ000407: No lazy enlistment available for my_nutri_diary
> 2019-02-01 22:33:36,135 INFO [org.teiid.CONNECTOR] (Worker2_QueryProcessorQueue5) eayVFuq9y7OY MySQLExecutionFactory Commit=true;DatabaseProductName=MySQL;DatabaseProductVersion=8.0.13;DriverMajorVersion=8;DriverMajorVersion=0;DriverName=MySQL Connector/J;DriverVersion=mysql-connector-java-8.0.13 (Revision: 66459e9d39c8fd09767992bc592acd2053279be6);IsolationLevel=2
> 2019-02-01 22:36:56,959 WARN [org.teiid.CONNECTOR] (Worker2_QueryProcessorQueue5) eayVFuq9y7OY Connector worker process failed for atomic-request=eayVFuq9y7OY.0.0.0: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.`idCode` AS c_0, g_0.`lc` AS c_1, g_0.`product_name` AS c_2, g_0.`origins` AS c_3, g_0.`brands` AS c_4, g_0.`quantity` AS c_5, g_0.`nova_group` AS c_6, g_0.`nutrition_grade_fr` AS c_7, g_0.`ingredients_text_with_allergens` AS c_8, g_0.`energy_100g` AS c_9, g_0.`carbohydrates_100g` AS c_10, g_0.`sugars_100g` AS c_11, g_0.`proteins_100g` AS c_12, g_0.`fat_100g` AS c_13, g_0.`saturated_fat_100g` AS c_14, g_0.`saturated_fat_modifier` AS c_15, g_0.`salt_100g` AS c_16, g_0.`sodium_100g` AS c_17 FROM `FDBProducts` AS g_0 ORDER BY c_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:127)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:393)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:229)
> at com.sun.proxy.$Proxy51.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:61)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:278)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
> at java.lang.Thread.run(Thread.java:748)
> Caused by: java.sql.SQLException: Java heap space
> at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
> at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
> at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
> at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:974)
> at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1024)
> at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:504)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:119)
> ... 18 more
> 2019-02-01 22:36:56,977 WARN [org.teiid.PROCESSOR] (default task-4) eayVFuq9y7OY TEIID30020 Processing exception for request eayVFuq9y7OY.0 'TEIID30504 my_nutri_diary: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.`idCode` AS c_0, g_0.`lc` AS c_1, g_0.`product_name` AS c_2, g_0.`origins` AS c_3, g_0.`brands` AS c_4, g_0.`quantity` AS c_5, g_0.`nova_group` AS c_6, g_0.`nutrition_grade_fr` AS c_7, g_0.`ingredients_text_with_allergens` AS c_8, g_0.`energy_100g` AS c_9, g_0.`carbohydrates_100g` AS c_10, g_0.`sugars_100g` AS c_11, g_0.`proteins_100g` AS c_12, g_0.`fat_100g` AS c_13, g_0.`saturated_fat_100g` AS c_14, g_0.`saturated_fat_modifier` AS c_15, g_0.`salt_100g` AS c_16, g_0.`sodium_100g` AS c_17 FROM `FDBProducts` AS g_0 ORDER BY c_0]'. Originally TeiidProcessingException 'Java heap space' SQLError.java:129. Enable more detailed logging to see the entire stacktrace.
> 2019-02-01 22:36:56,987 WARN [org.teiid.PROCESSOR] (default task-4) TEIID16053 Unable to process odata request due to: TEIID30504 my_nutri_diary: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.`idCode` AS c_0, g_0.`lc` AS c_1, g_0.`product_name` AS c_2, g_0.`origins` AS c_3, g_0.`brands` AS c_4, g_0.`quantity` AS c_5, g_0.`nova_group` AS c_6, g_0.`nutrition_grade_fr` AS c_7, g_0.`ingredients_text_with_allergens` AS c_8, g_0.`energy_100g` AS c_9, g_0.`carbohydrates_100g` AS c_10, g_0.`sugars_100g` AS c_11, g_0.`proteins_100g` AS c_12, g_0.`fat_100g` AS c_13, g_0.`saturated_fat_100g` AS c_14, g_0.`saturated_fat_modifier` AS c_15, g_0.`salt_100g` AS c_16, g_0.`sodium_100g` AS c_17 FROM `FDBProducts` AS g_0 ORDER BY c_0] Increase the log level to see the entire stack trace.
> 2019-02-01 22:36:57,213 WARN [org.teiid.CONNECTOR] (Worker2_QueryProcessorQueue6) NdaDihMR2Vlt Connector worker process failed for atomic-request=NdaDihMR2Vlt.0.0.1: org.teiid.translator.jdbc.JDBCExecutionException: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.`idCode` AS c_0, g_0.`lc` AS c_1, g_0.`product_name` AS c_2, g_0.`origins` AS c_3, g_0.`brands` AS c_4, g_0.`quantity` AS c_5, g_0.`nova_group` AS c_6, g_0.`nutrition_grade_fr` AS c_7, g_0.`ingredients_text_with_allergens` AS c_8, g_0.`energy_100g` AS c_9, g_0.`carbohydrates_100g` AS c_10, g_0.`sugars_100g` AS c_11, g_0.`proteins_100g` AS c_12, g_0.`fat_100g` AS c_13, g_0.`saturated_fat_100g` AS c_14, g_0.`saturated_fat_modifier` AS c_15, g_0.`salt_100g` AS c_16, g_0.`sodium_100g` AS c_17 FROM `FDBProducts` AS g_0 ORDER BY c_0]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:127)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:393)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:229)
> at com.sun.proxy.$Proxy51.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:104)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266)
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:61)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:278)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
> at java.lang.Thread.run(Thread.java:748)
> Caused by: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
7 years
[JBoss JIRA] (TEIID-5680) Improve performance of odata expand operations
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5680?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5680:
---------------------------------------
With 11.2.x the rewritten odata query is also as a join:
/*+ cache(ttl:300000 scope:USER) */ SELECT g0.idDiaryEntry, g0.AmountInG, X__1.expr1 AS expr3 FROM my_nutri_diary.Diary AS g0 LEFT OUTER JOIN (SELECT ARRAY_AGG((g1.idCode, g1.product_name, g1.brands, g1.energy_100g) ORDER BY g1.idCode) AS expr1, g1.idCode FROM my_nutri_diary.FDBProducts AS g1 GROUP BY g1.idCode) AS X__1 ON g0.fkIdProductCode = X__1.idCode WHERE (g0.AddedDateTime >= ?) AND (g0.AddedDateTime <= ?) AND (g0.MealNumber = ?) ORDER BY g0.idDiaryEntry LIMIT 100
And with the cardinality hints, does produce an acceptable plan.
> I mean does Teiid behave differently with different orders of magnitude for the cardinality or is there just something like small or large table depending on a given threshold?
There is some behavioral difference for "small" sizes - less than a single batch, typically 256 rows. Beyond that relative approximate sizes are all that is needed. Costing routines above the source node level are not based upon full column level histograms, but you can refine things further by setting the column level DISTINCT_VALUES, NULL_VALUE_COUNT, MAX_VALUE, and MIN_VALUE values as well. Generally just setting the table cardinality is all that is sufficient to correctly influence join planning.
> Is this something I need to update in the lifecycle when tables grow larger or do I just make an educated guess how the future will look like?
If the VDB imports the metadata itself, then it will pick up fresh estimates at import time - which can be triggered by either not caching the source metadata or deleting the metadata and reloading the vdb.
If the VDB specifies the metadata there is not a built-in facility yet that will attempt to update it's costing statistics at runtime. There are several facilities for that including a custom metadata repository and the alter statement that can be run on an ephemeral basis without a metadata repository to set the cardinality of table. In our openshift environment we will likely implement runtime update of costing metadata from source and from query results as we'll have a well defined persistent store handy.
So if you fully specify the metadata as ddl, then it may need updated if the relative sizes are no longer representative.
> Improve performance of odata expand operations
> ----------------------------------------------
>
> Key: TEIID-5680
> URL: https://issues.jboss.org/browse/TEIID-5680
> Project: Teiid
> Issue Type: Enhancement
> Components: OData
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Major
> Attachments: test2.txt
>
>
> Hello Ramesh and Steven,
> this is a follow up regarding an observation in the discussion from TEIID-5643. I thought I open an extra issue for the topic as this seems not to be related to TEIID-5500.
> As you already know, I am using SAPUI5 as frontend for ODATA requests. SAPUI5 supports binding of a user interface control group (like a list with its list items) to a single ODATA path at a time only. If the control group items require additional information which is stored in a different table in the database, I have to expand those parameters in the odata query.
> When doing so, I am running in a serious performance issue with TEIID, which would render the approach of using sapui5 with Teiid infeasible if we cannot find a way to speedup the issue. At the moment I have a small table with entries (table Diary with about 20 records) for which the query extracts several items (just a single one in the example given below). Now the filtered item is expanded with data from a larger table in the database (FDBProducts with about 680.000 records). The whole query takes about 15s to be processed. The query is given as:
> https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary?$select=Amount...
> I checked the output when using
> <logger category="org.teiid.CONNECTOR"><level name="TRACE"/></logger>
> This shows the problem. It seems the join operation is not pushed down to the database but the data are rather joined within Teiid. Teiid therefore downloads the entire dataset of the large FDBProducts table, which makes the expand approach infeasible for real world datasets with a certain size. So my question is, if you can modify Teiid to push down the entire join operation to the underlaying database (I assume this would be the most efficient approach), or alternatively query just the items from the table to be joined which where filtered from the first table if the first option is not possible?
> Thanks for your help.
> Christoph
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
7 years
[JBoss JIRA] (TEIID-5680) Improve performance of odata expand operations
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5680?page=com.atlassian.jira.plugin... ]
Christoph John edited comment on TEIID-5680 at 3/11/19 1:06 PM:
----------------------------------------------------------------
Hello Steven thanks for the feedback. Then it seems now is the time for me to migrate to 12.0 I am not aware of the CARDINALITY metadata concept yet. Need to get hands on this first as well. But I will try out your recommendation.
As a matter of principle I expect to have a large row count in a productive scenario for the Diary table as well. The small number of rows is only because I am running with a small test setup. For the CARDINALITY to make sense for TEIID, what kind of category limits are there to be specified. I mean does Teiid behave differently with different orders of magnitude for the cardinality or is there just something like small or large table depending on a given threshold? How will I have to use the metadata feature in a productive environment? Is this something I need to update in the lifecycle when tables grow larger or do I just make an educated guess how the future will look like?
Thanks for your help.
Best regards,
Christoph
was (Author: cjohn001):
Hello Steven thanks for the feedback. Then it seems now is the time for me to migrate to 12.0 I am not aware of the CARDINALITY metadata concept yet. Need to get hands on this first as well. But I will try out your recommendation.
As a matter of principle I expect to have a large row count in a productive scenario for the Diary table as well. The small number of rows is only because I am running with a small test setup. For the CARDINALITY to make sense for TEIID, what kind of categories are there to be specified. I mean does Teiid behave differently with different orders of magnitude for the cardinality or is there just something like small or large table depending on the threshold? How will I have to use the metadata feature in a productive environment? Is this something I need to update in the livecycle when tables grow larger?
Thanks for your help.
Best regards,
Christoph
> Improve performance of odata expand operations
> ----------------------------------------------
>
> Key: TEIID-5680
> URL: https://issues.jboss.org/browse/TEIID-5680
> Project: Teiid
> Issue Type: Enhancement
> Components: OData
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Major
> Attachments: test2.txt
>
>
> Hello Ramesh and Steven,
> this is a follow up regarding an observation in the discussion from TEIID-5643. I thought I open an extra issue for the topic as this seems not to be related to TEIID-5500.
> As you already know, I am using SAPUI5 as frontend for ODATA requests. SAPUI5 supports binding of a user interface control group (like a list with its list items) to a single ODATA path at a time only. If the control group items require additional information which is stored in a different table in the database, I have to expand those parameters in the odata query.
> When doing so, I am running in a serious performance issue with TEIID, which would render the approach of using sapui5 with Teiid infeasible if we cannot find a way to speedup the issue. At the moment I have a small table with entries (table Diary with about 20 records) for which the query extracts several items (just a single one in the example given below). Now the filtered item is expanded with data from a larger table in the database (FDBProducts with about 680.000 records). The whole query takes about 15s to be processed. The query is given as:
> https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary?$select=Amount...
> I checked the output when using
> <logger category="org.teiid.CONNECTOR"><level name="TRACE"/></logger>
> This shows the problem. It seems the join operation is not pushed down to the database but the data are rather joined within Teiid. Teiid therefore downloads the entire dataset of the large FDBProducts table, which makes the expand approach infeasible for real world datasets with a certain size. So my question is, if you can modify Teiid to push down the entire join operation to the underlaying database (I assume this would be the most efficient approach), or alternatively query just the items from the table to be joined which where filtered from the first table if the first option is not possible?
> Thanks for your help.
> Christoph
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
7 years
[JBoss JIRA] (TEIID-5680) Improve performance of odata expand operations
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5680?page=com.atlassian.jira.plugin... ]
Christoph John commented on TEIID-5680:
---------------------------------------
Hello Steven thanks for the feedback. Then it seems now is the time for me to migrate to 12.0 I am not aware of the CARDINALITY metadata concept yet. Not to get hands on this first as well. But I will try out your recommendation.
As a matter of principle I expect to have a large row count in a productive scenario for the Diary table as well. The small number of rows is only because I am running with a small test setup. For the CARDINALITY to make sense for TEIID, what kind of categories are there to be specified. I mean does Teiid behave differently with different orders of magnitude for the cardinality or is there just something like small or large table depending on the threshold? How will I have to use the metadata feature in a productive environment? Is this something I need to update in the livecycle when tables grow larger?
Thanks for your help.
Best regards,
Christoph
> Improve performance of odata expand operations
> ----------------------------------------------
>
> Key: TEIID-5680
> URL: https://issues.jboss.org/browse/TEIID-5680
> Project: Teiid
> Issue Type: Enhancement
> Components: OData
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Major
> Attachments: test2.txt
>
>
> Hello Ramesh and Steven,
> this is a follow up regarding an observation in the discussion from TEIID-5643. I thought I open an extra issue for the topic as this seems not to be related to TEIID-5500.
> As you already know, I am using SAPUI5 as frontend for ODATA requests. SAPUI5 supports binding of a user interface control group (like a list with its list items) to a single ODATA path at a time only. If the control group items require additional information which is stored in a different table in the database, I have to expand those parameters in the odata query.
> When doing so, I am running in a serious performance issue with TEIID, which would render the approach of using sapui5 with Teiid infeasible if we cannot find a way to speedup the issue. At the moment I have a small table with entries (table Diary with about 20 records) for which the query extracts several items (just a single one in the example given below). Now the filtered item is expanded with data from a larger table in the database (FDBProducts with about 680.000 records). The whole query takes about 15s to be processed. The query is given as:
> https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary?$select=Amount...
> I checked the output when using
> <logger category="org.teiid.CONNECTOR"><level name="TRACE"/></logger>
> This shows the problem. It seems the join operation is not pushed down to the database but the data are rather joined within Teiid. Teiid therefore downloads the entire dataset of the large FDBProducts table, which makes the expand approach infeasible for real world datasets with a certain size. So my question is, if you can modify Teiid to push down the entire join operation to the underlaying database (I assume this would be the most efficient approach), or alternatively query just the items from the table to be joined which where filtered from the first table if the first option is not possible?
> Thanks for your help.
> Christoph
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
7 years
[JBoss JIRA] (TEIID-5680) Improve performance of odata expand operations
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5680?page=com.atlassian.jira.plugin... ]
Christoph John edited comment on TEIID-5680 at 3/11/19 1:04 PM:
----------------------------------------------------------------
Hello Steven thanks for the feedback. Then it seems now is the time for me to migrate to 12.0 I am not aware of the CARDINALITY metadata concept yet. Need to get hands on this first as well. But I will try out your recommendation.
As a matter of principle I expect to have a large row count in a productive scenario for the Diary table as well. The small number of rows is only because I am running with a small test setup. For the CARDINALITY to make sense for TEIID, what kind of categories are there to be specified. I mean does Teiid behave differently with different orders of magnitude for the cardinality or is there just something like small or large table depending on the threshold? How will I have to use the metadata feature in a productive environment? Is this something I need to update in the livecycle when tables grow larger?
Thanks for your help.
Best regards,
Christoph
was (Author: cjohn001):
Hello Steven thanks for the feedback. Then it seems now is the time for me to migrate to 12.0 I am not aware of the CARDINALITY metadata concept yet. Not to get hands on this first as well. But I will try out your recommendation.
As a matter of principle I expect to have a large row count in a productive scenario for the Diary table as well. The small number of rows is only because I am running with a small test setup. For the CARDINALITY to make sense for TEIID, what kind of categories are there to be specified. I mean does Teiid behave differently with different orders of magnitude for the cardinality or is there just something like small or large table depending on the threshold? How will I have to use the metadata feature in a productive environment? Is this something I need to update in the livecycle when tables grow larger?
Thanks for your help.
Best regards,
Christoph
> Improve performance of odata expand operations
> ----------------------------------------------
>
> Key: TEIID-5680
> URL: https://issues.jboss.org/browse/TEIID-5680
> Project: Teiid
> Issue Type: Enhancement
> Components: OData
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Major
> Attachments: test2.txt
>
>
> Hello Ramesh and Steven,
> this is a follow up regarding an observation in the discussion from TEIID-5643. I thought I open an extra issue for the topic as this seems not to be related to TEIID-5500.
> As you already know, I am using SAPUI5 as frontend for ODATA requests. SAPUI5 supports binding of a user interface control group (like a list with its list items) to a single ODATA path at a time only. If the control group items require additional information which is stored in a different table in the database, I have to expand those parameters in the odata query.
> When doing so, I am running in a serious performance issue with TEIID, which would render the approach of using sapui5 with Teiid infeasible if we cannot find a way to speedup the issue. At the moment I have a small table with entries (table Diary with about 20 records) for which the query extracts several items (just a single one in the example given below). Now the filtered item is expanded with data from a larger table in the database (FDBProducts with about 680.000 records). The whole query takes about 15s to be processed. The query is given as:
> https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary?$select=Amount...
> I checked the output when using
> <logger category="org.teiid.CONNECTOR"><level name="TRACE"/></logger>
> This shows the problem. It seems the join operation is not pushed down to the database but the data are rather joined within Teiid. Teiid therefore downloads the entire dataset of the large FDBProducts table, which makes the expand approach infeasible for real world datasets with a certain size. So my question is, if you can modify Teiid to push down the entire join operation to the underlaying database (I assume this would be the most efficient approach), or alternatively query just the items from the table to be joined which where filtered from the first table if the first option is not possible?
> Thanks for your help.
> Christoph
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
7 years
[JBoss JIRA] (TEIID-5678) loopback improvements
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5678?page=com.atlassian.jira.plugin... ]
Steven Hawkins resolved TEIID-5678.
-----------------------------------
Resolution: Done
Added better handling of limit/offset, char lengths, and wait times.
> loopback improvements
> ---------------------
>
> Key: TEIID-5678
> URL: https://issues.jboss.org/browse/TEIID-5678
> Project: Teiid
> Issue Type: Task
> Components: Misc. Connectors
> Reporter: Steven Hawkins
> Assignee: Steven Hawkins
> Priority: Major
> Fix For: 12.2
>
>
> The loopback translator does not handle offset/limit very well as it will return infinite results if they are allowed to be pushed. It also should determine string length from metadata if possible rather than requiring the char length be set to the smallest value possible.
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
7 years
[JBoss JIRA] (TEIID-5680) Improve performance of odata expand operations
by Steven Hawkins (Jira)
[ https://issues.jboss.org/browse/TEIID-5680?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5680:
---------------------------------------
This isn't really an expand issue, it's just a general planning question. At least on master I see this is planned as expected if cardinality metadata is added to FDBProducts and Diary.
The odata layer generates the query:
/*+ cache(ttl:300000 scope:USER) */ SELECT g0.idDiaryEntry, g0.AmountInG, /*+ MJ */ (SELECT ARRAY_AGG((g1.idCode, g1.product_name, g1.brands, g1.energy_100g) ORDER BY g1.idCode) FROM my_nutri_diary.FDBProducts AS g1 WHERE g1.idCode = g0.fkIdProductCode) FROM my_nutri_diary.Diary AS g0 WHERE ((g0.AddedDateTime >= CONVERT(?, timestamp)) AND (g0.AddedDateTime <= CONVERT(?, timestamp))) AND (g0.MealNumber = CONVERT(?, string)) ORDER BY g0.idDiaryEntry LIMIT 0, 100 /* gKjgnveX1UoB */
For 12.0 (TEIID-4498) that is rewritten as a join:
/*+ cache(ttl:300000 scope:USER) */ SELECT g0.idDiaryEntry, g0.AmountInG, X__1.expr1 AS expr3 FROM my_nutri_diary.Diary AS g0 LEFT OUTER JOIN (SELECT ARRAY_AGG((g1.idCode, g1.product_name, g1.brands, g1.energy_100g) ORDER BY g1.idCode) AS expr1, g1.idCode FROM my_nutri_diary.FDBProducts AS g1 GROUP BY g1.idCode) AS X__1 ON g0.fkIdProductCode = X__1.idCode WHERE (g0.AddedDateTime >= ?) AND (g0.AddedDateTime <= ?) AND (g0.MealNumber = ?) ORDER BY g0.idDiaryEntry LIMIT 100
Note that the aggregation is still being performed in the inline view rather than above the join. This is because the logic is allowing full multiple such aggregations (such as what would be used with sibling expands) rather than seeing is that can be raised higher with more grouping - there is room for improvement there as it would allow for the join to be pushed.
With cardinality metadata on both Diary and FDBProducts, for example:
CREATE FOREIGN TABLE FDBProducts (
...
OPTIONS(NAMEINSOURCE '`FDBProducts`', UPDATABLE 'TRUE', CARDINALITY 600000);
The value doesn't have to be exact, it just needs to convey an approximate row count. With that I do see a good plan being created - it will use a dependent join to take the key values from Diary and supply them as a predicate to FDBProducts:
SELECT g_0.idCode, (g_0.idCode, g_0.product_name, g_0.brands, g_0.energy_100g) FROM my_nutri_diary.FDBProducts AS g_0 WHERE g_0.idCode IN (<dependent values>)
There is still an improvement to be had there though. The rule to push limit values isn't being run until after the determination of the join type is made - we should instead see that Diary is limited to 100 rows and choose a dependent join regardless of the FDBProducts size.
I'd first rerun your scenario with the cardinality metadata, then we can determine if you need additional changes.
> Improve performance of odata expand operations
> ----------------------------------------------
>
> Key: TEIID-5680
> URL: https://issues.jboss.org/browse/TEIID-5680
> Project: Teiid
> Issue Type: Enhancement
> Components: OData
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Major
> Attachments: test2.txt
>
>
> Hello Ramesh and Steven,
> this is a follow up regarding an observation in the discussion from TEIID-5643. I thought I open an extra issue for the topic as this seems not to be related to TEIID-5500.
> As you already know, I am using SAPUI5 as frontend for ODATA requests. SAPUI5 supports binding of a user interface control group (like a list with its list items) to a single ODATA path at a time only. If the control group items require additional information which is stored in a different table in the database, I have to expand those parameters in the odata query.
> When doing so, I am running in a serious performance issue with TEIID, which would render the approach of using sapui5 with Teiid infeasible if we cannot find a way to speedup the issue. At the moment I have a small table with entries (table Diary with about 20 records) for which the query extracts several items (just a single one in the example given below). Now the filtered item is expanded with data from a larger table in the database (FDBProducts with about 680.000 records). The whole query takes about 15s to be processed. The query is given as:
> https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary?$select=Amount...
> I checked the output when using
> <logger category="org.teiid.CONNECTOR"><level name="TRACE"/></logger>
> This shows the problem. It seems the join operation is not pushed down to the database but the data are rather joined within Teiid. Teiid therefore downloads the entire dataset of the large FDBProducts table, which makes the expand approach infeasible for real world datasets with a certain size. So my question is, if you can modify Teiid to push down the entire join operation to the underlaying database (I assume this would be the most efficient approach), or alternatively query just the items from the table to be joined which where filtered from the first table if the first option is not possible?
> Thanks for your help.
> Christoph
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
7 years
[JBoss JIRA] (TEIID-5681) Error when trying to sort an expanded data set
by Christoph John (Jira)
Christoph John created TEIID-5681:
-------------------------------------
Summary: Error when trying to sort an expanded data set
Key: TEIID-5681
URL: https://issues.jboss.org/browse/TEIID-5681
Project: Teiid
Issue Type: Bug
Components: OData
Reporter: Christoph John
Assignee: Steven Hawkins
Hello together,
when using orderby together with an odata expand operation I get and error. The statement I am trying looks as follows:
https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary?$select=idDiar...
The error is:
<error xmlns="http://docs.oasis-open.org/odata/ns/metadata">
<code>null</code>
<message>
TEIID31202 Detected that an already sorted set of values was not in the expected order (typically UTF-16 / UCS-2). Please check the translator settings to ensure character columns used for joining are sorted as expected.
</message>
</error>
Please note, when using orderby without expand, the orderby is working as expected. The attribute which shall be used for ordering has the following type:
<Property Name="MealNumber" Type="Edm.String" Nullable="false" MaxLength="1">
In the underlaying database it is defined as an ENUM('1','2','3','4','5')
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
7 years
[JBoss JIRA] (TEIID-5680) Improve performance of odata expand operations
by Christoph John (Jira)
[ https://issues.jboss.org/browse/TEIID-5680?page=com.atlassian.jira.plugin... ]
Christoph John updated TEIID-5680:
----------------------------------
Component/s: OData
> Improve performance of odata expand operations
> ----------------------------------------------
>
> Key: TEIID-5680
> URL: https://issues.jboss.org/browse/TEIID-5680
> Project: Teiid
> Issue Type: Enhancement
> Components: OData
> Reporter: Christoph John
> Assignee: Steven Hawkins
> Priority: Major
> Attachments: test2.txt
>
>
> Hello Ramesh and Steven,
> this is a follow up regarding an observation in the discussion from TEIID-5643. I thought I open an extra issue for the topic as this seems not to be related to TEIID-5500.
> As you already know, I am using SAPUI5 as frontend for ODATA requests. SAPUI5 supports binding of a user interface control group (like a list with its list items) to a single ODATA path at a time only. If the control group items require additional information which is stored in a different table in the database, I have to expand those parameters in the odata query.
> When doing so, I am running in a serious performance issue with TEIID, which would render the approach of using sapui5 with Teiid infeasible if we cannot find a way to speedup the issue. At the moment I have a small table with entries (table Diary with about 20 records) for which the query extracts several items (just a single one in the example given below). Now the filtered item is expanded with data from a larger table in the database (FDBProducts with about 680.000 records). The whole query takes about 15s to be processed. The query is given as:
> https://morpheus.fritz.box/odata4/svc/my_nutri_diary/Diary?$select=Amount...
> I checked the output when using
> <logger category="org.teiid.CONNECTOR"><level name="TRACE"/></logger>
> This shows the problem. It seems the join operation is not pushed down to the database but the data are rather joined within Teiid. Teiid therefore downloads the entire dataset of the large FDBProducts table, which makes the expand approach infeasible for real world datasets with a certain size. So my question is, if you can modify Teiid to push down the entire join operation to the underlaying database (I assume this would be the most efficient approach), or alternatively query just the items from the table to be joined which where filtered from the first table if the first option is not possible?
> Thanks for your help.
> Christoph
--
This message was sent by Atlassian Jira
(v7.12.1#712002)
7 years