[JBoss JIRA] (TEIID-3578) server becomes unresponsive after running for a while
by Jorge Herrera (JIRA)
[ https://issues.jboss.org/browse/TEIID-3578?page=com.atlassian.jira.plugin... ]
Jorge Herrera updated TEIID-3578:
---------------------------------
Attachment: server_startup.log
> server becomes unresponsive after running for a while
> -----------------------------------------------------
>
> Key: TEIID-3578
> URL: https://issues.jboss.org/browse/TEIID-3578
> Project: Teiid
> Issue Type: Bug
> Components: Server
> Affects Versions: 8.7.1
> Environment: RHEL 6, AWS VM, JBoss EAP (standalone) 6.3.2.GA
> JBoss Data Virtualization 6.1.0.ER4
> Reporter: Jorge Herrera
> Assignee: Steven Hawkins
> Attachments: server.log, server_startup.log, stackTrace_TEIID-3578, threaddump.txt
>
>
> Server becomes unresponsive, it does ack new sessions, but nothing else, admin-console, and direct queries are completely unresponsive. If I reboot the server it works just fine. Disk space / RAM / CPU all seem just fine.
> [root@BIJDVDev2 ~]# df
> Filesystem 1K-blocks Used Available Use% Mounted on
> /dev/xvde1 6062016 4601808 1397596 77% /
> none 7619624 0 7619624 0% /dev/shm
> [BIJDVDev2.dev.a1.vary.redhat.com] [11:38:48 AM]
> [root@BIJDVDev2 ~]# top
> top - 11:43:05 up 14 days, 13:31, 1 user, load average: 0.00, 0.00, 0.00
> Tasks: 151 total, 1 running, 150 sleeping, 0 stopped, 0 zombie
> Cpu(s): 0.1%us, 0.1%sy, 0.0%ni, 99.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
> Mem: 15239248k total, 5387488k used, 9851760k free, 203540k buffers
> Swap: 0k total, 0k used, 0k free, 2887904k cached
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 8 months
[JBoss JIRA] (TEIID-3578) server becomes unresponsive after running for a while
by Van Halbert (JIRA)
[ https://issues.jboss.org/browse/TEIID-3578?page=com.atlassian.jira.plugin... ]
Van Halbert commented on TEIID-3578:
------------------------------------
> Maybe we need to look at multiple logging levels for command logging. Using INFO level, just write out the
Begin/End for the user query, but only write the "END" for source query.
We do distinguish a trace level which provides the query plan as well.
I'm not sure how much value there would be in just omitting the source start events. If you end up putting the source sql in the source end event then the data volume could be similar.
[van] I wasn't thinking of volume, but the number of actual log entries. And by eliminating 1 for each source query, a 3 source query could go from 8 entries to 5 entries. That's over 33% reduction in the number of log entries backing up.
> server becomes unresponsive after running for a while
> -----------------------------------------------------
>
> Key: TEIID-3578
> URL: https://issues.jboss.org/browse/TEIID-3578
> Project: Teiid
> Issue Type: Bug
> Components: Server
> Affects Versions: 8.7.1
> Environment: RHEL 6, AWS VM, JBoss EAP (standalone) 6.3.2.GA
> JBoss Data Virtualization 6.1.0.ER4
> Reporter: Jorge Herrera
> Assignee: Steven Hawkins
> Attachments: server.log, server_startup.log, stackTrace_TEIID-3578, threaddump.txt
>
>
> Server becomes unresponsive, it does ack new sessions, but nothing else, admin-console, and direct queries are completely unresponsive. If I reboot the server it works just fine. Disk space / RAM / CPU all seem just fine.
> [root@BIJDVDev2 ~]# df
> Filesystem 1K-blocks Used Available Use% Mounted on
> /dev/xvde1 6062016 4601808 1397596 77% /
> none 7619624 0 7619624 0% /dev/shm
> [BIJDVDev2.dev.a1.vary.redhat.com] [11:38:48 AM]
> [root@BIJDVDev2 ~]# top
> top - 11:43:05 up 14 days, 13:31, 1 user, load average: 0.00, 0.00, 0.00
> Tasks: 151 total, 1 running, 150 sleeping, 0 stopped, 0 zombie
> Cpu(s): 0.1%us, 0.1%sy, 0.0%ni, 99.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
> Mem: 15239248k total, 5387488k used, 9851760k free, 203540k buffers
> Swap: 0k total, 0k used, 0k free, 2887904k cached
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 8 months
[JBoss JIRA] (TEIID-3578) server becomes unresponsive after running for a while
by Van Halbert (JIRA)
[ https://issues.jboss.org/browse/TEIID-3578?page=com.atlassian.jira.plugin... ]
Van Halbert commented on TEIID-3578:
------------------------------------
It sounds like our database logging option in general is a bad design. Definitely needs rethinking. And in 6.2, the default will be configuring the command/audit logging with async handlers.
> server becomes unresponsive after running for a while
> -----------------------------------------------------
>
> Key: TEIID-3578
> URL: https://issues.jboss.org/browse/TEIID-3578
> Project: Teiid
> Issue Type: Bug
> Components: Server
> Affects Versions: 8.7.1
> Environment: RHEL 6, AWS VM, JBoss EAP (standalone) 6.3.2.GA
> JBoss Data Virtualization 6.1.0.ER4
> Reporter: Jorge Herrera
> Assignee: Steven Hawkins
> Attachments: server.log, stackTrace_TEIID-3578, threaddump.txt
>
>
> Server becomes unresponsive, it does ack new sessions, but nothing else, admin-console, and direct queries are completely unresponsive. If I reboot the server it works just fine. Disk space / RAM / CPU all seem just fine.
> [root@BIJDVDev2 ~]# df
> Filesystem 1K-blocks Used Available Use% Mounted on
> /dev/xvde1 6062016 4601808 1397596 77% /
> none 7619624 0 7619624 0% /dev/shm
> [BIJDVDev2.dev.a1.vary.redhat.com] [11:38:48 AM]
> [root@BIJDVDev2 ~]# top
> top - 11:43:05 up 14 days, 13:31, 1 user, load average: 0.00, 0.00, 0.00
> Tasks: 151 total, 1 running, 150 sleeping, 0 stopped, 0 zombie
> Cpu(s): 0.1%us, 0.1%sy, 0.0%ni, 99.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
> Mem: 15239248k total, 5387488k used, 9851760k free, 203540k buffers
> Swap: 0k total, 0k used, 0k free, 2887904k cached
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 8 months
[JBoss JIRA] (TEIID-3578) server becomes unresponsive after running for a while
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3578?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3578:
---------------------------------------
> Maybe we need to look at multiple logging levels for command logging. Using INFO level, just write out the Begin/End for the user query, but only write the "END" for source query.
We do distinguish a trace level which provides the query plan as well.
I'm not sure how much value there would be in just omitting the source start events. If you end up putting the source sql in the source end event then the data volume could be similar.
> For demo's, it looks good because you can then use the Dashboard to view the logs. But it may not be the best option in all use cases.
Yes, using the db logging option in general is not something that should be done in a production scenario. Even having command logging turned on in production would be specific to debugging an issue that is not reproducible in a staging environment or there is some specific need for a post audit.
However it's hard to say what exactly is going on in this particular case other than the teiid threads are blocking on the logging queue as confirmed above by Ramesh. There's no indication as to why the db appender is waiting on the session bean or service registry threads are waiting. These states have occurred for others:
https://developer.jboss.org/thread/173023
https://issues.jboss.org/secure/attachment/12389923/stuck-deploy-eap-jsta... / https://issues.jboss.org/browse/JBIDE-19641
So we'd need to see what versions are affected and how Teiid or his environment is contributing.
> server becomes unresponsive after running for a while
> -----------------------------------------------------
>
> Key: TEIID-3578
> URL: https://issues.jboss.org/browse/TEIID-3578
> Project: Teiid
> Issue Type: Bug
> Components: Server
> Affects Versions: 8.7.1
> Environment: RHEL 6, AWS VM, JBoss EAP (standalone) 6.3.2.GA
> JBoss Data Virtualization 6.1.0.ER4
> Reporter: Jorge Herrera
> Assignee: Steven Hawkins
> Attachments: server.log, stackTrace_TEIID-3578, threaddump.txt
>
>
> Server becomes unresponsive, it does ack new sessions, but nothing else, admin-console, and direct queries are completely unresponsive. If I reboot the server it works just fine. Disk space / RAM / CPU all seem just fine.
> [root@BIJDVDev2 ~]# df
> Filesystem 1K-blocks Used Available Use% Mounted on
> /dev/xvde1 6062016 4601808 1397596 77% /
> none 7619624 0 7619624 0% /dev/shm
> [BIJDVDev2.dev.a1.vary.redhat.com] [11:38:48 AM]
> [root@BIJDVDev2 ~]# top
> top - 11:43:05 up 14 days, 13:31, 1 user, load average: 0.00, 0.00, 0.00
> Tasks: 151 total, 1 running, 150 sleeping, 0 stopped, 0 zombie
> Cpu(s): 0.1%us, 0.1%sy, 0.0%ni, 99.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
> Mem: 15239248k total, 5387488k used, 9851760k free, 203540k buffers
> Swap: 0k total, 0k used, 0k free, 2887904k cached
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 8 months
[JBoss JIRA] (TEIID-3568) Order By and Limit are not getting pushed to the database, when Union and join are used together.
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-3568?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-3568:
---------------------------------------
> In this condition if the joins are left outer then as per my understanding we would not ending with fewer rows if the query supports ordered limit.
I think we have a common understanding of the scenario. Please update the issue to an enhancement request.
> Order By and Limit are not getting pushed to the database, when Union and join are used together.
> --------------------------------------------------------------------------------------------------
>
> Key: TEIID-3568
> URL: https://issues.jboss.org/browse/TEIID-3568
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.1
> Reporter: Guru Prasad
> Assignee: Steven Hawkins
> Attachments: ShowPlan.txt
>
>
> Order By and Limit are not getting pushed to the database, when Union and join are used together.
> In this scenario there if the underlying table has millions of records the query never returns with data.
> *Query 1*: Using only Join without union, this works fine.
> SELECT u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod, u.evtcatcode FROM (
> select evttypecode, evtsysid, evtutctod, evtsystod, evtcatcode from XYZ.Tab1
> ) as u
> LEFT OUTER JOIN XYZ.CATEGORY AS ct ON u.evtcatcode = ct.evtcatcode
> WHERE (u.EVTUTCTOD >= {ts'2015-06-03 19:20:00.8'}) AND (u.EVTUTCTOD <= {ts'2015-06-03 19:20:01.0'}) ORDER BY u.evtsysid LIMIT 8
> PROCESSOR PLAN:
> AccessNode(0) output=[evttypecode AS evttypecode, evtsysid AS evtsysid, evtutctod AS evtutctod, evtsystod AS evtsystod, evtcatcode AS evtcatcode]
> SELECT g_0.EVTTYPECODE AS c_0, g_0.EVTSYSID AS c_1, g_0.EVTUTCTOD AS c_2, g_0.EVTSYSTOD AS c_3, g_0.EVTCATCODE AS c_4 FROM ABC.Tab1 AS g_0 LEFT OUTER JOIN ABC.CATEGORY AS g_1 ON g_0.EVTCATCODE = g_1.EVTCATCODE WHERE (g_0.EVTUTCTOD >= {ts'2015-06-03 19:20:00.8'}) AND (g_0.EVTUTCTOD <= {ts'2015-06-03 19:20:01.0'}) ORDER BY c_1 LIMIT 8
> *Query 2*: Using only Union without any join, this also works fine.
> SELECT u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod, u.evtcatcode FROM (
> select evttypecode, evtsysid, evtutctod, evtsystod, evtcatcode from XYZ.Tab1
> UNION ALL
> select evttypecode, evtsysid, evtutctod, evtsystod, evtcatcode from XYZ.Tab2
> ) as u
> WHERE (u.EVTUTCTOD >= {ts'2015-06-03 19:20:00.8'}) AND (u.EVTUTCTOD <= {ts'2015-06-03 19:20:01.0'}) ORDER BY u.evtsysid LIMIT 8
> PROCESSOR PLAN:
> AccessNode(0) output=[evttypecode AS evttypecode, evtsysid AS evtsysid, evtutctod AS evtutctod, evtsystod AS evtsystod, evtcatcode AS evtcatcode]
> SELECT g_1.EVTTYPECODE AS c_0, g_1.EVTSYSID AS c_1, g_1.EVTUTCTOD AS c_2, g_1.EVTSYSTOD AS c_3, g_1.EVTCATCODE AS c_4 FROM ABC.Tab1 AS g_1 WHERE (g_1.EVTUTCTOD >= {ts'2015-06-03 19:20:00.8'}) AND (g_1.EVTUTCTOD <= {ts'2015-06-03 19:20:01.0'}) UNION ALL
> SELECT g_0.EVTTYPECODE AS c_0, g_0.EVTSYSID AS c_1, g_0.EVTUTCTOD AS c_2, g_0.EVTSYSTOD AS c_3, g_0.EVTCATCODE AS c_4 FROM ABC.Tab2 AS g_0 WHERE (g_0.EVTUTCTOD >= {ts'2015-06-03 19:20:00.8'}) AND (g_0.EVTUTCTOD <= {ts'2015-06-03 19:20:01.0'}) ORDER BY c_1 LIMIT 8
> *Query 3*: Using both Union and join, this does not push down the order by and limit.
> SELECT u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod, u.evtcatcode FROM (
> select evttypecode, evtsysid, evtutctod, evtsystod, evtcatcode from XYZ.Tab1
> UNION ALL
> select evttypecode, evtsysid, evtutctod, evtsystod, evtcatcode from XYZ.Tab2
> ) as u
> LEFT OUTER JOIN XYZ.EVTTYPE AS tp ON tp.evttypecode = u.evttypecode
> LEFT OUTER JOIN XYZ.CATEGORY AS ct ON u.evtcatcode = ct.evtcatcode
> WHERE (u.EVTUTCTOD >= {ts'2015-06-03 19:20:00.8'}) AND (u.EVTUTCTOD <= {ts'2015-06-03 19:20:01.0'}) ORDER BY u.evtsysid LIMIT 8
> PROCESSOR PLAN:
> ProjectNode(0) output=[u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod, u.evtcatcode] [u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod, u.evtcatcode]
> LimitNode(1) output=[u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod, u.evtcatcode] limit 8
> SortNode(2) output=[u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod, u.evtcatcode] [SORT] [u.evtsysid]
> JoinNode(3) [MERGE JOIN (SORT/ALREADY_SORTED)] [LEFT OUTER JOIN] criteria=[u.evtcatcode=evtcatcode] output=[u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod, u.evtcatcode]
> JoinNode(4) [MERGE JOIN (SORT/ALREADY_SORTED)] [LEFT OUTER JOIN] criteria=[u.evttypecode=evttypecode] output=[u.evtcatcode, u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod]
> AccessNode(5) output=[u.evttypecode, u.evtcatcode, u.evtsysid, u.evtutctod, u.evtsystod]
> SELECT g_1.EVTTYPECODE AS c_0, g_1.EVTCATCODE AS c_1, g_1.EVTSYSID AS c_2, g_1.EVTUTCTOD AS c_3, g_1.EVTSYSTOD AS c_4 FROM ABC.Tab1 AS g_1 WHERE (g_1.EVTUTCTOD >= {ts'2015-06-03 19:20:00.8'}) AND (g_1.EVTUTCTOD <= {ts'2015-06-03 19:20:01.0'})
> UNION ALL SELECT g_0.EVTTYPECODE AS c_0, g_0.EVTCATCODE AS c_1, g_0.EVTSYSID AS c_2, g_0.EVTUTCTOD AS c_3, g_0.EVTSYSTOD AS c_4 FROM ABC.Tab2 AS g_0 WHERE (g_0.EVTUTCTOD >= {ts'2015-06-03 19:20:00.8'}) AND (g_0.EVTUTCTOD <= {ts'2015-06-03 19:20:01.0'})
> AccessNode(6) output=[evttypecode] SELECT g_0.EVTTYPECODE AS c_0 FROM ABC.EVTTYPE AS g_0 ORDER BY c_0
> AccessNode(7) output=[evtcatcode] SELECT g_0.EVTCATCODE AS c_0 FROM ABC.CATEGORY AS g_0 ORDER BY c_0
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 8 months
[JBoss JIRA] (TEIID-3578) server becomes unresponsive after running for a while
by Van Halbert (JIRA)
[ https://issues.jboss.org/browse/TEIID-3578?page=com.atlassian.jira.plugin... ]
Van Halbert commented on TEIID-3578:
------------------------------------
For demo's, it looks good because you can then use the Dashboard to view the logs. But it may not be the best option in all use cases.
> server becomes unresponsive after running for a while
> -----------------------------------------------------
>
> Key: TEIID-3578
> URL: https://issues.jboss.org/browse/TEIID-3578
> Project: Teiid
> Issue Type: Bug
> Components: Server
> Affects Versions: 8.7.1
> Environment: RHEL 6, AWS VM, JBoss EAP (standalone) 6.3.2.GA
> JBoss Data Virtualization 6.1.0.ER4
> Reporter: Jorge Herrera
> Assignee: Steven Hawkins
> Attachments: server.log, stackTrace_TEIID-3578, threaddump.txt
>
>
> Server becomes unresponsive, it does ack new sessions, but nothing else, admin-console, and direct queries are completely unresponsive. If I reboot the server it works just fine. Disk space / RAM / CPU all seem just fine.
> [root@BIJDVDev2 ~]# df
> Filesystem 1K-blocks Used Available Use% Mounted on
> /dev/xvde1 6062016 4601808 1397596 77% /
> none 7619624 0 7619624 0% /dev/shm
> [BIJDVDev2.dev.a1.vary.redhat.com] [11:38:48 AM]
> [root@BIJDVDev2 ~]# top
> top - 11:43:05 up 14 days, 13:31, 1 user, load average: 0.00, 0.00, 0.00
> Tasks: 151 total, 1 running, 150 sleeping, 0 stopped, 0 zombie
> Cpu(s): 0.1%us, 0.1%sy, 0.0%ni, 99.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
> Mem: 15239248k total, 5387488k used, 9851760k free, 203540k buffers
> Swap: 0k total, 0k used, 0k free, 2887904k cached
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 8 months
[JBoss JIRA] (TEIID-3578) server becomes unresponsive after running for a while
by Van Halbert (JIRA)
[ https://issues.jboss.org/browse/TEIID-3578?page=com.atlassian.jira.plugin... ]
Van Halbert commented on TEIID-3578:
------------------------------------
Maybe we need to look at multiple logging levels for command logging. Using INFO level, just write out the Begin/End for the user query, but only write the "END" for source query.
> server becomes unresponsive after running for a while
> -----------------------------------------------------
>
> Key: TEIID-3578
> URL: https://issues.jboss.org/browse/TEIID-3578
> Project: Teiid
> Issue Type: Bug
> Components: Server
> Affects Versions: 8.7.1
> Environment: RHEL 6, AWS VM, JBoss EAP (standalone) 6.3.2.GA
> JBoss Data Virtualization 6.1.0.ER4
> Reporter: Jorge Herrera
> Assignee: Steven Hawkins
> Attachments: server.log, stackTrace_TEIID-3578, threaddump.txt
>
>
> Server becomes unresponsive, it does ack new sessions, but nothing else, admin-console, and direct queries are completely unresponsive. If I reboot the server it works just fine. Disk space / RAM / CPU all seem just fine.
> [root@BIJDVDev2 ~]# df
> Filesystem 1K-blocks Used Available Use% Mounted on
> /dev/xvde1 6062016 4601808 1397596 77% /
> none 7619624 0 7619624 0% /dev/shm
> [BIJDVDev2.dev.a1.vary.redhat.com] [11:38:48 AM]
> [root@BIJDVDev2 ~]# top
> top - 11:43:05 up 14 days, 13:31, 1 user, load average: 0.00, 0.00, 0.00
> Tasks: 151 total, 1 running, 150 sleeping, 0 stopped, 0 zombie
> Cpu(s): 0.1%us, 0.1%sy, 0.0%ni, 99.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
> Mem: 15239248k total, 5387488k used, 9851760k free, 203540k buffers
> Swap: 0k total, 0k used, 0k free, 2887904k cached
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 8 months
[JBoss JIRA] (TEIID-3578) server becomes unresponsive after running for a while
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-3578?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-3578:
-------------------------------------
[~jorge.herrera] Couple suggestions based on above
1) Do you really need database based command/audit logging. If not you can remove that, may be use file based logging
2) Change the overflow-action to "DISCARD", but in this situation the log messages will be lost
The question really is why is database appender not functioning, are using the built-in test database H2 for this?
> server becomes unresponsive after running for a while
> -----------------------------------------------------
>
> Key: TEIID-3578
> URL: https://issues.jboss.org/browse/TEIID-3578
> Project: Teiid
> Issue Type: Bug
> Components: Server
> Affects Versions: 8.7.1
> Environment: RHEL 6, AWS VM, JBoss EAP (standalone) 6.3.2.GA
> JBoss Data Virtualization 6.1.0.ER4
> Reporter: Jorge Herrera
> Assignee: Steven Hawkins
> Attachments: server.log, stackTrace_TEIID-3578, threaddump.txt
>
>
> Server becomes unresponsive, it does ack new sessions, but nothing else, admin-console, and direct queries are completely unresponsive. If I reboot the server it works just fine. Disk space / RAM / CPU all seem just fine.
> [root@BIJDVDev2 ~]# df
> Filesystem 1K-blocks Used Available Use% Mounted on
> /dev/xvde1 6062016 4601808 1397596 77% /
> none 7619624 0 7619624 0% /dev/shm
> [BIJDVDev2.dev.a1.vary.redhat.com] [11:38:48 AM]
> [root@BIJDVDev2 ~]# top
> top - 11:43:05 up 14 days, 13:31, 1 user, load average: 0.00, 0.00, 0.00
> Tasks: 151 total, 1 running, 150 sleeping, 0 stopped, 0 zombie
> Cpu(s): 0.1%us, 0.1%sy, 0.0%ni, 99.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
> Mem: 15239248k total, 5387488k used, 9851760k free, 203540k buffers
> Swap: 0k total, 0k used, 0k free, 2887904k cached
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 8 months
[JBoss JIRA] (TEIID-3578) server becomes unresponsive after running for a while
by Ramesh Reddy (JIRA)
[ https://issues.jboss.org/browse/TEIID-3578?page=com.atlassian.jira.plugin... ]
Ramesh Reddy commented on TEIID-3578:
-------------------------------------
Looks like it :(
{code}
<async-handler name="TEIID_COMMAND_LOG">
<level name="DEBUG"/>
<queue-length value="50"/>
<overflow-action value="block"/>
<subhandlers>
<handler name="TEIID_JPA_LOG"/>
</subhandlers>
</async-handler>
<async-handler name="TEIID_AUDIT_LOG">
<level name="DEBUG"/>
<queue-length value="50"/>
<overflow-action value="block"/>
<subhandlers>
<handler name="TEIID_JPA_LOG"/>
</subhandlers>
</async-handler>
{code}
> server becomes unresponsive after running for a while
> -----------------------------------------------------
>
> Key: TEIID-3578
> URL: https://issues.jboss.org/browse/TEIID-3578
> Project: Teiid
> Issue Type: Bug
> Components: Server
> Affects Versions: 8.7.1
> Environment: RHEL 6, AWS VM, JBoss EAP (standalone) 6.3.2.GA
> JBoss Data Virtualization 6.1.0.ER4
> Reporter: Jorge Herrera
> Assignee: Steven Hawkins
> Attachments: server.log, stackTrace_TEIID-3578, threaddump.txt
>
>
> Server becomes unresponsive, it does ack new sessions, but nothing else, admin-console, and direct queries are completely unresponsive. If I reboot the server it works just fine. Disk space / RAM / CPU all seem just fine.
> [root@BIJDVDev2 ~]# df
> Filesystem 1K-blocks Used Available Use% Mounted on
> /dev/xvde1 6062016 4601808 1397596 77% /
> none 7619624 0 7619624 0% /dev/shm
> [BIJDVDev2.dev.a1.vary.redhat.com] [11:38:48 AM]
> [root@BIJDVDev2 ~]# top
> top - 11:43:05 up 14 days, 13:31, 1 user, load average: 0.00, 0.00, 0.00
> Tasks: 151 total, 1 running, 150 sleeping, 0 stopped, 0 zombie
> Cpu(s): 0.1%us, 0.1%sy, 0.0%ni, 99.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
> Mem: 15239248k total, 5387488k used, 9851760k free, 203540k buffers
> Swap: 0k total, 0k used, 0k free, 2887904k cached
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 8 months
[JBoss JIRA] (TEIID-3568) Order By and Limit are not getting pushed to the database, when Union and join are used together.
by Guru Prasad (JIRA)
[ https://issues.jboss.org/browse/TEIID-3568?page=com.atlassian.jira.plugin... ]
Guru Prasad commented on TEIID-3568:
------------------------------------
In this condition if the joins are left outer then as per my understanding we would not ending with fewer rows if the query supports ordered limit.
*For translator not supporting in line views*
1. Currently we are executing the right query independent of the left query
2. Executing the left query with the filter condition, but without order by and limit (Single query is executed as this supports union)
3. Merge the results of left and right query based on the join criteria
4. Sort the results based on the sort column
5. Apply limit
If we apply the order by and limit in step 2, it will discarded the same rows which step 5 is performing. Am I missing something?
*For translator not supporting union*
1. Currently we are executing the right query independent of the left query
2. Executing the left query with the filter condition, but without order by and limit for each table/view separately
3. Union of the results from step 2
4. Merge the results of step 3 and step 1 based on the join criteria
5. Sort the results based on the sort column
6. Apply limit
If we have applied the order by and limit in step 2 individually for both the queries and then performed the rest of the steps we would be getting the same rows, these steps are being followed if the query has no joins but has only union command.
Below is the plan if the query has union without the join
LimitNode(0) output=[convert(evttypecode, integer), evtsysid AS evtsysid, evtutctod AS evtutctod, evtsystod AS evtsystod, convert(evtcatcode, integer)] limit 8
SortNode(1) output=[convert(evttypecode, integer), evtsysid AS evtsysid, evtutctod AS evtutctod, evtsystod AS evtsystod, convert(evtcatcode, integer)] [SORT] [evtsysid]
UnionAllNode(2) output=[convert(evttypecode, integer), evtsysid AS evtsysid, evtutctod AS evtutctod, evtsystod AS evtsystod, convert(evtcatcode, integer)]
ProjectNode(3) output=[convert(evttypecode, integer), evtsysid AS evtsysid, evtutctod AS evtutctod, evtsystod AS evtsystod, convert(evtcatcode, integer)] [convert(evttypecode, integer), evtsysid AS evtsysid, evtutctod AS evtutctod, evtsystod AS evtsystod, convert(evtcatcode, integer)]
AccessNode(4) output=[evttypecode, evtsysid, evtutctod, evtsystod, evtcatcode] SELECT g_0.EVTTYPECODE AS c_0, g_0.EVTSYSID AS c_1, g_0.EVTUTCTOD AS c_2, g_0.EVTSYSTOD AS c_3, g_0.EVTCATCODE AS c_4 FROM DB2DS.VIEWOBJ1 AS g_0 WHERE (g_0.EVTUTCTOD >= {ts'2015-06-03 19:20:00.8'}) AND (g_0.EVTUTCTOD <= {ts'2015-06-03 19:20:01.0'}) ORDER BY c_1 LIMIT 8
ProjectNode(5) output=[convert(evttypecode, integer), evtsysid AS evtsysid, evtutctod AS evtutctod, evtsystod AS evtsystod, convert(evtcatcode, integer)] [convert(evttypecode, integer), evtsysid AS evtsysid, evtutctod AS evtutctod, evtsystod AS evtsystod, convert(evtcatcode, integer)]
AccessNode(6) output=[evttypecode, evtsysid, evtutctod, evtsystod, evtcatcode] SELECT g_0.EVTTYPECODE AS c_0, g_0.EVTSYSID AS c_1, g_0.EVTUTCTOD AS c_2, g_0.EVTSYSTOD AS c_3, g_0.EVTCATCODE AS c_4 FROM DB2DS.VIEWADM1 AS g_0 WHERE (g_0.EVTUTCTOD >= {ts'2015-06-03 19:20:00.8'}) AND (g_0.EVTUTCTOD <= {ts'2015-06-03 19:20:01.0'}) ORDER BY c_1 LIMIT 8
> Order By and Limit are not getting pushed to the database, when Union and join are used together.
> --------------------------------------------------------------------------------------------------
>
> Key: TEIID-3568
> URL: https://issues.jboss.org/browse/TEIID-3568
> Project: Teiid
> Issue Type: Bug
> Affects Versions: 8.1
> Reporter: Guru Prasad
> Assignee: Steven Hawkins
> Attachments: ShowPlan.txt
>
>
> Order By and Limit are not getting pushed to the database, when Union and join are used together.
> In this scenario there if the underlying table has millions of records the query never returns with data.
> *Query 1*: Using only Join without union, this works fine.
> SELECT u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod, u.evtcatcode FROM (
> select evttypecode, evtsysid, evtutctod, evtsystod, evtcatcode from XYZ.Tab1
> ) as u
> LEFT OUTER JOIN XYZ.CATEGORY AS ct ON u.evtcatcode = ct.evtcatcode
> WHERE (u.EVTUTCTOD >= {ts'2015-06-03 19:20:00.8'}) AND (u.EVTUTCTOD <= {ts'2015-06-03 19:20:01.0'}) ORDER BY u.evtsysid LIMIT 8
> PROCESSOR PLAN:
> AccessNode(0) output=[evttypecode AS evttypecode, evtsysid AS evtsysid, evtutctod AS evtutctod, evtsystod AS evtsystod, evtcatcode AS evtcatcode]
> SELECT g_0.EVTTYPECODE AS c_0, g_0.EVTSYSID AS c_1, g_0.EVTUTCTOD AS c_2, g_0.EVTSYSTOD AS c_3, g_0.EVTCATCODE AS c_4 FROM ABC.Tab1 AS g_0 LEFT OUTER JOIN ABC.CATEGORY AS g_1 ON g_0.EVTCATCODE = g_1.EVTCATCODE WHERE (g_0.EVTUTCTOD >= {ts'2015-06-03 19:20:00.8'}) AND (g_0.EVTUTCTOD <= {ts'2015-06-03 19:20:01.0'}) ORDER BY c_1 LIMIT 8
> *Query 2*: Using only Union without any join, this also works fine.
> SELECT u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod, u.evtcatcode FROM (
> select evttypecode, evtsysid, evtutctod, evtsystod, evtcatcode from XYZ.Tab1
> UNION ALL
> select evttypecode, evtsysid, evtutctod, evtsystod, evtcatcode from XYZ.Tab2
> ) as u
> WHERE (u.EVTUTCTOD >= {ts'2015-06-03 19:20:00.8'}) AND (u.EVTUTCTOD <= {ts'2015-06-03 19:20:01.0'}) ORDER BY u.evtsysid LIMIT 8
> PROCESSOR PLAN:
> AccessNode(0) output=[evttypecode AS evttypecode, evtsysid AS evtsysid, evtutctod AS evtutctod, evtsystod AS evtsystod, evtcatcode AS evtcatcode]
> SELECT g_1.EVTTYPECODE AS c_0, g_1.EVTSYSID AS c_1, g_1.EVTUTCTOD AS c_2, g_1.EVTSYSTOD AS c_3, g_1.EVTCATCODE AS c_4 FROM ABC.Tab1 AS g_1 WHERE (g_1.EVTUTCTOD >= {ts'2015-06-03 19:20:00.8'}) AND (g_1.EVTUTCTOD <= {ts'2015-06-03 19:20:01.0'}) UNION ALL
> SELECT g_0.EVTTYPECODE AS c_0, g_0.EVTSYSID AS c_1, g_0.EVTUTCTOD AS c_2, g_0.EVTSYSTOD AS c_3, g_0.EVTCATCODE AS c_4 FROM ABC.Tab2 AS g_0 WHERE (g_0.EVTUTCTOD >= {ts'2015-06-03 19:20:00.8'}) AND (g_0.EVTUTCTOD <= {ts'2015-06-03 19:20:01.0'}) ORDER BY c_1 LIMIT 8
> *Query 3*: Using both Union and join, this does not push down the order by and limit.
> SELECT u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod, u.evtcatcode FROM (
> select evttypecode, evtsysid, evtutctod, evtsystod, evtcatcode from XYZ.Tab1
> UNION ALL
> select evttypecode, evtsysid, evtutctod, evtsystod, evtcatcode from XYZ.Tab2
> ) as u
> LEFT OUTER JOIN XYZ.EVTTYPE AS tp ON tp.evttypecode = u.evttypecode
> LEFT OUTER JOIN XYZ.CATEGORY AS ct ON u.evtcatcode = ct.evtcatcode
> WHERE (u.EVTUTCTOD >= {ts'2015-06-03 19:20:00.8'}) AND (u.EVTUTCTOD <= {ts'2015-06-03 19:20:01.0'}) ORDER BY u.evtsysid LIMIT 8
> PROCESSOR PLAN:
> ProjectNode(0) output=[u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod, u.evtcatcode] [u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod, u.evtcatcode]
> LimitNode(1) output=[u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod, u.evtcatcode] limit 8
> SortNode(2) output=[u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod, u.evtcatcode] [SORT] [u.evtsysid]
> JoinNode(3) [MERGE JOIN (SORT/ALREADY_SORTED)] [LEFT OUTER JOIN] criteria=[u.evtcatcode=evtcatcode] output=[u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod, u.evtcatcode]
> JoinNode(4) [MERGE JOIN (SORT/ALREADY_SORTED)] [LEFT OUTER JOIN] criteria=[u.evttypecode=evttypecode] output=[u.evtcatcode, u.evttypecode, u.evtsysid, u.evtutctod, u.evtsystod]
> AccessNode(5) output=[u.evttypecode, u.evtcatcode, u.evtsysid, u.evtutctod, u.evtsystod]
> SELECT g_1.EVTTYPECODE AS c_0, g_1.EVTCATCODE AS c_1, g_1.EVTSYSID AS c_2, g_1.EVTUTCTOD AS c_3, g_1.EVTSYSTOD AS c_4 FROM ABC.Tab1 AS g_1 WHERE (g_1.EVTUTCTOD >= {ts'2015-06-03 19:20:00.8'}) AND (g_1.EVTUTCTOD <= {ts'2015-06-03 19:20:01.0'})
> UNION ALL SELECT g_0.EVTTYPECODE AS c_0, g_0.EVTCATCODE AS c_1, g_0.EVTSYSID AS c_2, g_0.EVTUTCTOD AS c_3, g_0.EVTSYSTOD AS c_4 FROM ABC.Tab2 AS g_0 WHERE (g_0.EVTUTCTOD >= {ts'2015-06-03 19:20:00.8'}) AND (g_0.EVTUTCTOD <= {ts'2015-06-03 19:20:01.0'})
> AccessNode(6) output=[evttypecode] SELECT g_0.EVTTYPECODE AS c_0 FROM ABC.EVTTYPE AS g_0 ORDER BY c_0
> AccessNode(7) output=[evtcatcode] SELECT g_0.EVTCATCODE AS c_0 FROM ABC.CATEGORY AS g_0 ORDER BY c_0
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
10 years, 8 months