{"action":"created","issue":{"url":"https://api.github.com/repos/spring-projects/spring-integration/issues/2628","repository_url":"https://api.github.com/repos/spring-projects/spring-integration","labels_url":"https://api.github.com/repos/spring-projects/spring-integration/issues/2628/labels{/name}","comments_url":"https://api.github.com/repos/spring-projects/spring-integration/issues/2628/comments","events_url":"https://api.github.com/repos/spring-projects/spring-integration/issues/2628/events","html_url":"https://github.com/spring-projects/spring-integration/issues/2628","id":380312055,"node_id":"MDU6SXNzdWUzODAzMTIwNTU=","number":2628,"title":"JdbcChannelMessageStore: poor performance for large buffers (Oracle+?)","user":{"login":"abrensch","id":5775537,"node_id":"MDQ6VXNlcjU3NzU1Mzc=","avatar_url":"https://avatars0.githubusercontent.com/u/5775537?v=4","gravatar_id":"","url":"https://api.github.com/users/abrensch","html_url":"https://github.com/abrensch","followers_url":"https://api.github.com/users/abrensch/followers","following_url":"https://api.github.com/users/abrensch/following{/other_user}","gists_url":"https://api.github.com/users/abrensch/gists{/gist_id}","starred_url":"https://api.github.com/users/abrensch/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/abrensch/subscriptions","organizations_url":"https://api.github.com/users/abrensch/orgs","repos_url":"https://api.github.com/users/abrensch/repos","events_url":"https://api.github.com/users/abrensch/events{/privacy}","received_events_url":"https://api.github.com/users/abrensch/received_events","type":"User","site_admin":false},"labels":[{"id":598127484,"node_id":"MDU6TGFiZWw1OTgxMjc0ODQ=","url":"https://api.github.com/repos/spring-projects/spring-integration/labels/waiting-for-reporter","name":"waiting-for-reporter","color":"e99695","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":22,"created_at":"2018-11-13T16:28:05Z","updated_at":"2018-11-15T21:45:33Z","closed_at":null,"author_association":"NONE","body":"## Bug report\r\n\r\nHi,\r\n\r\nhaving a performance-issue with JdbcChannelMessageStore.pollMessageFromGroup (spring integration 4.3.17, Oracle 12c ):\r\n\r\nthe processing time of the polling query (OracleChannelMessageStoreQueryProvider.getPollFromGroupQuery) scales linear with the number of messages for that group, adding about 1 microsecond per message to the query-time, so starting from 50k buffered messages it gets nasty.\r\n\r\nLooking at Oracle's execution plan it has to scan and sort the table on every poll. Oracle CAN create execution plans that take the sort-order from an index, but that's very touchy and everything in the query that does not look like just following the index will cause it to fallback to the default scan+sort plan.\r\n\r\nI did not check for Postgres and MySQL, so maybe it's just an Oracle issue?\r\n\r\nThe indexing from the schema-oracle.sql script looks quite sophisticated, so it seems it was engeneered at some point in time for high performance but degraded with later extensions?\r\n\r\nthanx for checking,\r\n\r\nregards, Arndt\r\n\r\n \r\n\r\n"},"comment":{"url":"https://api.github.com/repos/spring-projects/spring-integration/issues/comments/439201489","html_url":"https://github.com/spring-projects/spring-integration/issues/2628#issuecomment-439201489","issue_url":"https://api.github.com/repos/spring-projects/spring-integration/issues/2628","id":439201489,"node_id":"MDEyOklzc3VlQ29tbWVudDQzOTIwMTQ4OQ==","user":{"login":"garyrussell","id":483832,"node_id":"MDQ6VXNlcjQ4MzgzMg==","avatar_url":"https://avatars3.githubusercontent.com/u/483832?v=4","gravatar_id":"","url":"https://api.github.com/users/garyrussell","html_url":"https://github.com/garyrussell","followers_url":"https://api.github.com/users/garyrussell/followers","following_url":"https://api.github.com/users/garyrussell/following{/other_user}","gists_url":"https://api.github.com/users/garyrussell/gists{/gist_id}","starred_url":"https://api.github.com/users/garyrussell/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/garyrussell/subscriptions","organizations_url":"https://api.github.com/users/garyrussell/orgs","repos_url":"https://api.github.com/users/garyrussell/repos","events_url":"https://api.github.com/users/garyrussell/events{/privacy}","received_events_url":"https://api.github.com/users/garyrussell/received_events","type":"User","site_admin":false},"created_at":"2018-11-15T21:45:33Z","updated_at":"2018-11-15T21:45:33Z","author_association":"MEMBER","body":"Hmmm - just tried this on 12c\r\n\r\n```sql\r\nSQL> SELECT /*+ INDEX(INT_CHANNEL_MESSAGE INT_CHANNEL_MSG_PRIORITY_IDX) */ INT_CHANNEL_MESSAGE.MESSAGE_ID from INT_CHANNEL_MESSAGE where \r\nINT_CHANNEL_MESSAGE.GROUP_KEY = 'foo' and INT_CHANNEL_MESSAGE.REGION = 'DEFAULT' order by MESSAGE_PRIORITY DESC NULLS LAST, CREATED_DATE, \r\nMESSAGE_SEQUENCE fetch first row only for update skip locked;\r\nSELECT /*+ INDEX(INT_CHANNEL_MESSAGE INT_CHANNEL_MSG_PRIORITY_IDX) */ INT_CHANNEL_MESSAGE.MESSAGE_ID from INT_CHANNEL_MESSAGE where \r\nINT_CHANNEL_MESSAGE.GROUP_KEY = 'foo' and INT_CHANNEL_MESSAGE.REGION = 'DEFAULT' order by MESSAGE_PRIORITY DESC NULLS LAST, CREATED_DATE, 'MESSAGE_SEQUENCE fetch first row only for update skip locked\r\n*\r\nERROR at line 1:\r\nORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.\r\n```\r\n\r\nSo it looks like the sort can't be avoided. At least with the hint, the sort won't actually do anything.\r\n\r\nI guess the moral is don't use a priority queue with large groups."}}
{"actor":{"display_login":"garyrussell"}}