Re: [rt-users] UseSQLForACLChecks / CurrentUserCanSee
On Fri, Jan 25, 2013 at 02:15:02AM +0400, Ruslan Zakirov wrote: On Fri, Jan 25, 2013 at 1:52 AM, k...@rice.edu k...@rice.edu wrote: We are running version 3.8.13 with a PostgreSQL 9.2 backend. I will try to look into it further and see if I can locate the cause of the pathological performance issues. Then you don't have one performance improvement that is only in RT 4.0+, grab patch from b5b357766929465521281c2886456c6b1fe24824 commit. Pg's log of slow queries would be next stop. -- Best regards, Ruslan. Hi Ruslan, I could not figure out how to pull this patch from the git commit, but here are the two top queries from the DB logs: SELECT count ( DISTINCT main.id ) FROM tickets main JOIN groups groups_1 ON ( groups_1.domain = '' ) AND ( groups_1.instance = main.id ) LEFT JOIN cachedgroupmembers cachedgroupmembers_2 ON ( cachedgroupmembers_2.memberid = '' ) AND ( cachedgroupmembers_2.groupid = groups_1.id ) WHERE ( main.STATUS != '' ) AND ( main.queue = '' AND main.STATUS = '' AND ( ( main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR
Re: [rt-users] UseSQLForACLChecks / CurrentUserCanSee
On Fri, Jan 25, 2013 at 8:14 PM, k...@rice.edu k...@rice.edu wrote: On Fri, Jan 25, 2013 at 02:15:02AM +0400, Ruslan Zakirov wrote: On Fri, Jan 25, 2013 at 1:52 AM, k...@rice.edu k...@rice.edu wrote: We are running version 3.8.13 with a PostgreSQL 9.2 backend. I will try to look into it further and see if I can locate the cause of the pathological performance issues. Then you don't have one performance improvement that is only in RT 4.0+, grab patch from b5b357766929465521281c2886456c6b1fe24824 commit. Pg's log of slow queries would be next stop. -- Best regards, Ruslan. Hi Ruslan, I could not figure out how to pull this patch from the git commit, but here are the two top queries from the DB logs: git show sha or https://github.com/bestpractical/rt/commit/b5b357766929465521281c2886456c6b1fe24824.patch SELECT count ( DISTINCT main.id ) FROM tickets main JOIN groups groups_1 ON ( groups_1.domain = '' ) AND ( groups_1.instance = main.id ) LEFT JOIN cachedgroupmembers cachedgroupmembers_2 ON ( cachedgroupmembers_2.memberid = '' ) AND ( cachedgroupmembers_2.groupid = groups_1.id ) WHERE ( main.STATUS != '' ) AND ( main.queue = '' AND main.STATUS = '' AND ( ( main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR main.queue = '' OR
[rt-users] UseSQLForACLChecks / CurrentUserCanSee
Dear RT community, I have been testing the option UseSQLForACLChecks which is based on the function CurrentUserCanSee and the initial testing is disappointing. Here are 3 representative front page loads with and without SQL-checks enabled: with-SQL: 16s, 13.8s, 14.5s --- 14.7s average no-SQL: 2.3s, 0.99s, 0.97s --- 1.42s average That is a 10x slowdown and it does not get any better with reloads. In some of the messages on the mailing list, it is mentioned that the performance is not bad for some setups. Is there a reference setup that is defined that produces these not bad timings? Alternatively, does anyone have a setup that is working well or that has an appropriate cache layer setup? Regards, Ken
Re: [rt-users] UseSQLForACLChecks / CurrentUserCanSee
On Fri, Jan 25, 2013 at 12:57 AM, k...@rice.edu k...@rice.edu wrote: Dear RT community, I have been testing the option UseSQLForACLChecks which is based on the function CurrentUserCanSee and the initial testing is disappointing. Here are 3 representative front page loads with and without SQL-checks enabled: with-SQL: 16s, 13.8s, 14.5s --- 14.7s average no-SQL: 2.3s, 0.99s, 0.97s --- 1.42s average That is a 10x slowdown and it does not get any better with reloads. In some of the messages on the mailing list, it is mentioned that the performance is not bad for some setups. Is there a reference setup that is defined that produces these not bad timings? Alternatively, does anyone have a setup that is working well or that has an appropriate cache layer setup? Hello Ken, You probably read description of the option, right? I always suspected that there will be instances for which performance degrades dramatically because of the option. So far we had one case when option works good for core RT and behaves badly in a proprietary extension. For most of our customers it works great. A few changes have been made since the option was introduced to fix ACL problems and performance issues, so as always you should mention RT version. We would love to see more details on your case. The option will be turned on by default in RT 4.2. Regards, Ken -- Best regards, Ruslan.
Re: [rt-users] UseSQLForACLChecks / CurrentUserCanSee
On Fri, Jan 25, 2013 at 01:08:30AM +0400, Ruslan Zakirov wrote: On Fri, Jan 25, 2013 at 12:57 AM, k...@rice.edu k...@rice.edu wrote: Dear RT community, I have been testing the option UseSQLForACLChecks which is based on the function CurrentUserCanSee and the initial testing is disappointing. Here are 3 representative front page loads with and without SQL-checks enabled: with-SQL: 16s, 13.8s, 14.5s --- 14.7s average no-SQL: 2.3s, 0.99s, 0.97s --- 1.42s average That is a 10x slowdown and it does not get any better with reloads. In some of the messages on the mailing list, it is mentioned that the performance is not bad for some setups. Is there a reference setup that is defined that produces these not bad timings? Alternatively, does anyone have a setup that is working well or that has an appropriate cache layer setup? Hello Ken, You probably read description of the option, right? I always suspected that there will be instances for which performance degrades dramatically because of the option. So far we had one case when option works good for core RT and behaves badly in a proprietary extension. For most of our customers it works great. A few changes have been made since the option was introduced to fix ACL problems and performance issues, so as always you should mention RT version. We would love to see more details on your case. The option will be turned on by default in RT 4.2. Regards, Ken -- Best regards, Ruslan. Hi Ruslan, We are running version 3.8.13 with a PostgreSQL 9.2 backend. I will try to look into it further and see if I can locate the cause of the pathological performance issues. Regards, Ken
Re: [rt-users] UseSQLForACLChecks / CurrentUserCanSee
On Fri, Jan 25, 2013 at 1:52 AM, k...@rice.edu k...@rice.edu wrote: We are running version 3.8.13 with a PostgreSQL 9.2 backend. I will try to look into it further and see if I can locate the cause of the pathological performance issues. Then you don't have one performance improvement that is only in RT 4.0+, grab patch from b5b357766929465521281c2886456c6b1fe24824 commit. Pg's log of slow queries would be next stop. -- Best regards, Ruslan.
