This version is exactly the same as 0.81_04. It contains changes to
massively improve performance when using postgresql for complex selects.
Jesse
Request Tracker — Best Practical Solutions – Trouble Ticketing. Free.
Jesse Vincent (jesse) writes:
This version is exactly the same as 0.81_04. It contains changes to
massively improve performance when using postgresql for complex selects.
Hi Jesse,
I'm testing here 3.0.2pre4 with 0.81_04, and I'm observing _very_
long query times and heavy CPU usage from PgSQL. It's a 7.3.2 PgSQL
running on a 550 MHz PIII, with FastCGI.
When I mean heavy, something like 8-10 seconds with PgSQL at 50-60%
CPU to be able to reply to a ticket (albeit one from a dataset migrated
from RT2) via the web interface.
Phil
_ _ |_ | regnauld@catpipe.net catpipe Systems ApS |
((||_ | *BSD solutions, consulting, development |
| Tlf.: +45 7021 0050 http://www.catpipe.net/ |
This version is exactly the same as 0.81_04. It contains changes to
massively improve performance when using postgresql for complex selects.
I never saw anyone respond to my emails on the subject where there was
called for testing with pgsql and your newer DBIx::SearchBuilder. In
those emails I said that performance hadn’t changed very much to make it
worthwhile.
So I wonder what changed which makes you say the above?
Jeroen Ruigrok van der Werven <asmodai(at)wxs.nl> / asmodai / a capoeirista
PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B
http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/
Only the wisest and the stupidest of men never change…
I never saw anyone respond to my emails on the subject where there was
called for testing with pgsql and your newer DBIx::SearchBuilder. In
those emails I said that performance hadn’t changed very much to make
it
worthwhile.So I wonder what changed which makes you say the above?
Truth to tell, on my Pg box, I didn’t notice much improvement in
performance, either. 
David
David Wheeler AIM: dwTheory
david@kineticode.com ICQ: 15726394
Yahoo!: dew7e
Jabber: Theory@jabber.org
Kineticode. Setting knowledge in motion.[sm]
This version is exactly the same as 0.81_04. It contains changes to
massively improve performance when using postgresql for complex selects.I never saw anyone respond to my emails on the subject where there was
called for testing with pgsql and your newer DBIx::SearchBuilder. In
those emails I said that performance hadn’t changed very much to make it
worthwhile.So I wonder what changed which makes you say the above?
Heh. True, the SearchBuilder pgsql performance appears to be pathetic.
We looked together with Phil into the queries & timings, and “SET
enable_nestloop = off” thingy transforms one query which took
3.8 sec into a query which takes 0.25 sec. (This can be put into
DBIx::SearchBuilder::Handle::Pg inside Connect method).
But!
There was another query, which took ~27 second, regardless of any
planner’s settings I could get hold of:
SELECT DISTINCT main.* FROM Users main JOIN Principals as Principals_3
ON main.id = Principals_3.id JOIN CachedGroupMembers as
CachedGroupMembers_6 ON Principals_3.Id =
CachedGroupMembers_6.MemberId JOIN Principals as Principals_1 ON
main.id = Principals_1.id JOIN CachedGroupMembers as
CachedGroupMembers_7 ON Principals_1.id =
CachedGroupMembers_7.MemberId JOIN Principals as Principals_4 ON
CachedGroupMembers_6.GroupId = Principals_4.Id, Groups Groups_2, ACL
ACL_5 WHERE ((ACL_5.RightName = ‘SuperUser’)OR(ACL_5.RightName =
‘OwnTicket’)) AND ((CachedGroupMembers_7.GroupId = ‘4’)) AND
((Principals_1.Disabled = ‘0’)) AND ((Principals_1.PrincipalType =
‘User’)) AND ((Principals_3.PrincipalType = ‘User’)) AND
(ACL_5.ObjectType = ‘RT::System’ OR (ACL_5.ObjectType = ‘RT::Queue’
AND ACL_5.ObjectId = ‘5’) ) AND ( (ACL_5.PrincipalId = Principals_4.Id
AND Principals_4.id = Groups_2.Id AND ACL_5.PrincipalType = ‘Group’ AND
(Groups_2.Domain = ‘SystemInternal’ OR Groups_2.Domain = ‘UserDefined’
OR Groups_2.Domain = ‘ACLEquivalence’)) OR ( ( (Groups_2.Domain =
‘RT::Queue-Role’ AND Groups_2.Instance = ‘5’) OR ( Groups_2.Domain =
‘RT::Ticket-Role’ AND Groups_2.Instance = ‘771’) ) AND Groups_2.Type =
ACL_5.PrincipalType AND Groups_2.Id = Principals_4.id AND
Principals_4.PrincipalType = ‘Group’) ) ORDER BY main.Name ASC;
I did not have time since Friday to try and rearrange it, but my gut
feeling is that in case of PostgreSQL, it is very profitable to convert
such monstrous joins into subselects.
\Anton.
Perl is strongly typed, it just has very few types. – Dan Sugalski
I never saw anyone respond to my emails on the subject where there was
called for testing with pgsql and your newer DBIx::SearchBuilder. In
those emails I said that performance hadn’t changed very much to make
it
worthwhile.So I wonder what changed which makes you say the above?
Truth to tell, on my Pg box, I didn’t notice much improvement in
performance, either.
Well, I was one of the people that noticed a massive improvment in execution
speed… Sadly it that gain appears to of have been all environment.
We moved from a PII/300 to a pIII/800 and our query times dropped from
roughly 15 minutes to around 5 seconds. At the same time, we also moved to a
new searchbuilder and from Postgres 7.2 to pg 7.3. Since I know that a
pIII/800 is not 1500 times as fast as a pII/300 I assumed that it was one of
the softwares that caused the improvement. We then spent some time fully
upgrading our RT3 machine, only to discover that the query was taking around
8 minutes on that box.
At that point, we just got a new machine, and now we can use RT fairly
happily. We only have a small number of users, and it is so much faster than
before that everyone thinks that it is fast 
We also did some work with
hand optimising queries early on, and know that they can be much faster as
well…
Cheers,
Paul
Well, I was one of the people that noticed a massive improvment in execution
speed… Sadly it that gain appears to of have been all environment.
Any DBA worth his or her salt will tell you that 80% of all performance
issues with databases come from badly created queries. And with some
careful examining and rewriting you will gain huge speed-ups.
Then you get OS tuning and hardware…
And I’ve seen this too often in real environments to discard their
wisdom on this matter.
Do note that I am not saying that getting better hardware will not be a
solution, but it is the wrong solution in the most cases.
Jeroen Ruigrok van der Werven <asmodai(at)wxs.nl> / asmodai / a capoeirista
PGP fingerprint: 2D92 980E 45FE 2C28 9DB7 9D88 97E6 839B 2EAC 625B
http://www.tendra.org/ | http://www.in-nomine.org/~asmodai/diary/
Only the wisest and the stupidest of men never change…
Where can I find a list of feature improvements?
John
At 04:45 AM 5/2/2003, Jesse Vincent wrote:
This version is exactly the same as 0.81_04. It contains changes to
massively improve performance when using postgresql for complex selects.Jesse–
Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.
rt-devel mailing list
rt-devel@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-devel
<>< Proverbs 3:5-6 “Trust in the Lord with all your heart and lean not on
your own understanding;
in all your ways acknowledge him, and he
will make your paths straight.”
There’s really no need to crosspost something like this to both rt-users
and rt-devel. Can you tell us what postgres queries it’s bogging down
on. Have you done a vacuum and analyze since you did your RT2 import?
If not, the query optimizer won’t have any idea what’s going on and
you’ll get extremely slow performance.
-jOn Fri, May 02, 2003 at 01:56:04PM +0200, Phil Regnauld wrote:
Jesse Vincent (jesse) writes:
This version is exactly the same as 0.81_04. It contains changes to
massively improve performance when using postgresql for complex selects.Hi Jesse,
I’m testing here 3.0.2pre4 with 0.81_04, and I’m observing very
long query times and heavy CPU usage from PgSQL. It’s a 7.3.2 PgSQL
running on a 550 MHz PIII, with FastCGI.When I mean heavy, something like 8-10 seconds with PgSQL at 50-60%
CPU to be able to reply to a ticket (albeit one from a dataset migrated
from RT2) via the web interface.Phil
–
_ _ |_ | regnauld@catpipe.net catpipe Systems ApS |
((||_ | *BSD solutions, consulting, development |
| Tlf.: +45 7021 0050 http://www.catpipe.net/ |
Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.
[Again, this doesn’t need to be cross-posted to both rt-devel and
rt-users]
I never saw anyone respond to my emails on the subject where there was
called for testing with pgsql and your newer DBIx::SearchBuilder. In
those emails I said that performance hadn’t changed very much to make
it
worthwhile.So I wonder what changed which makes you say the above?
Truth to tell, on my Pg box, I didn’t notice much improvement in
performance, either.
A number of folks wrote me to say that they saw dramatic speedups with
the new searchbuilder. I would, of course, appreciate additional help
tuning for postgres. I know there’s been a suggested column type change
in the ACL system for faster querying which I haven’t had a chance to
lookup, but more work by the folks who know postgres well would be
appreciated.
-j
David
–
David Wheeler AIM: dwTheory
david@kineticode.com ICQ: 15726394
Yahoo!: dew7e
Jabber: Theory@jabber.org
Kineticode. Setting knowledge in motion.[sm]
Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.
[Again, this doesn’t need to be cross-posted to both rt-devel and
rt-users]
I never saw anyone respond to my emails on the subject where there was
called for testing with pgsql and your newer DBIx::SearchBuilder. In
those emails I said that performance hadn’t changed very much to make
it
worthwhile.So I wonder what changed which makes you say the above?
Truth to tell, on my Pg box, I didn’t notice much improvement in
performance, either.A number of folks wrote me to say that they saw dramatic speedups with
the new searchbuilder. I would, of course, appreciate additional help
tuning for postgres. I know there’s been a suggested column type change
in the ACL system for faster querying which I haven’t had a chance to
lookup, but more work by the folks who know postgres well would be
appreciated.
Well yes, it dropped from `i-don’t-know’ to 8 minutes with this giant
query. Creating two additional indexes dropped it further to about 1
minute – which is still too slow. Certainly I could rewrite some query
manually to get some speedups if it helps (i.e. if it is integratable
into RT or SearchBuilder).
Christian Zagrodnick
gocept gmbh & co. kg - schalaunische strasse 6 - 06366 koethen/anhalt
fon. +49 3496 3099112, +49 179 1463644
fax. +49 3496 3099118
A number of folks wrote me to say that they saw dramatic speedups with
the new searchbuilder. I would, of course, appreciate additional help
tuning for postgres. I know there’s been a suggested column type change
in the ACL system for faster querying which I haven’t had a chance to
lookup, but more work by the folks who know postgres well would be
appreciated.Well yes, it dropped from `i-don’t-know’ to 8 minutes with this giant
query. Creating two additional indexes dropped it further to about 1
minute – which is still too slow. Certainly I could rewrite some query
manually to get some speedups if it helps (i.e. if it is integratable
into RT or SearchBuilder).
Actually, I’d appreciate it if you could have a look at:
http://lists.fsck.com/pipermail/rt-devel/2003-April/003697.html
I think we’re currently around aidan’s partially-optimized query.
Would you mind having a look and seeing if you can improve on his work?
I’m happy to put in more effort to speed postgres up, if I can get more
input about what will help.
-j
–
Christian Zagrodnickgocept gmbh & co. kg - schalaunische strasse 6 - 06366 koethen/anhalt
fon. +49 3496 3099112, +49 179 1463644
fax. +49 3496 3099118
Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.
I think this query is optimised enough for now. At least it takes less
than a second.
I’ll look for other queries during the week.On Sun, May 04, 2003 at 08:48:50AM -0400, Jesse Vincent wrote:
A number of folks wrote me to say that they saw dramatic speedups with
the new searchbuilder. I would, of course, appreciate additional help
tuning for postgres. I know there’s been a suggested column type change
in the ACL system for faster querying which I haven’t had a chance to
lookup, but more work by the folks who know postgres well would be
appreciated.Well yes, it dropped from `i-don’t-know’ to 8 minutes with this giant
query. Creating two additional indexes dropped it further to about 1
minute – which is still too slow. Certainly I could rewrite some query
manually to get some speedups if it helps (i.e. if it is integratable
into RT or SearchBuilder).Actually, I’d appreciate it if you could have a look at:
http://lists.fsck.com/pipermail/rt-devel/2003-April/003697.html
I think we’re currently around aidan’s partially-optimized query.
Would you mind having a look and seeing if you can improve on his work?
I’m happy to put in more effort to speed postgres up, if I can get more
input about what will help.-j
–
Christian Zagrodnickgocept gmbh & co. kg - schalaunische strasse 6 - 06366 koethen/anhalt
fon. +49 3496 3099112, +49 179 1463644
fax. +49 3496 3099118–
Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.
Christian Zagrodnick
gocept gmbh & co. kg - schalaunische strasse 6 - 06366 koethen/anhalt
fon. +49 3496 3099112, +49 179 1463644
fax. +49 3496 3099118
so now…
– issued on viewing a ticket, unformatted
SELECT DISTINCT main.* FROM Tickets main JOIN Groups as Groups_1 ON main.id = Groups_1.Instance JOIN Principals
as Principals_2 ON Groups_1.id = Principals_2.ObjectId JOIN CachedGroupMembers as CachedGroupMembers_3 ON Principals_2.id =
CachedGroupMembers_3.GroupId JOIN Principals as Principals_4 ON CachedGroupMembers_3.MemberId = Principals_4.id JOIN Users as
Users_5 ON Principals_4.ObjectId = Users_5.id WHERE ((main.EffectiveId = main.id)) AND ((main.Type = ‘ticket’)) AND ( ( (
(lower(Users_5.EmailAddress) = ‘sales@tastaturen.com’)AND(Groups_1.Domain = ‘RT::Ticket-Role’)AND(Groups_1.Type =
‘Requestor’)AND(Principals_2.PrincipalType = ‘Group’) ) ) AND ( (main.Status = ‘open’)OR(main.Status = ‘new’) ) ) ORDER BY
main.Priority DESC LIMIT 10
– duration: 20.513569 sec
– basicly the same just readable
SELECT DISTINCT
main.*
FROM
Tickets main
JOIN Groups as Groups_1 ON main.id = Groups_1.Instance
JOIN Principals as Principals_2 ON Groups_1.id = Principals_2.ObjectId
JOIN CachedGroupMembers as CachedGroupMembers_3 ON
Principals_2.id = CachedGroupMembers_3.GroupId
JOIN Principals as Principals_4 ON
CachedGroupMembers_3.MemberId = Principals_4.id
JOIN Users as Users_5 ON Principals_4.ObjectId = Users_5.id
WHERE
main.EffectiveId = main.id
AND main.Type = ‘ticket’
AND lower(Users_5.EmailAddress) = ‘sales@tastaturen.com’
AND Groups_1.Domain = ‘RT::Ticket-Role’
AND Groups_1.Type = ‘Requestor’
AND Principals_2.PrincipalType = ‘Group’
AND (main.Status = ‘open’ OR main.Status = ‘new’)
ORDER BY
main.Priority DESC
LIMIT 10
– 20 secs still
– explain analyze showed the problem: one small change in joining et voila.
SELECT DISTINCT
main.*
FROM
Tickets main
JOIN (
Groups as Groups_1
JOIN Principals as Principals_2 ON Groups_1.id = Principals_2.ObjectId
JOIN CachedGroupMembers as CachedGroupMembers_3 ON
Principals_2.id = CachedGroupMembers_3.GroupId
JOIN Principals as Principals_4 ON
CachedGroupMembers_3.MemberId = Principals_4.id
JOIN Users as Users_5 ON Principals_4.ObjectId = Users_5.id
) ON main.id = Groups_1.Instance
WHERE
main.EffectiveId = main.id
AND main.Type = ‘ticket’
AND lower(Users_5.EmailAddress) = ‘sales@tastaturen.com’
AND Groups_1.Domain = ‘RT::Ticket-Role’
AND Groups_1.Type = ‘Requestor’
AND Principals_2.PrincipalType = ‘Group’
AND (main.Status = ‘open’ OR main.Status = ‘new’)
ORDER BY
main.Priority DESC
LIMIT 10
– duration: 0.526543 sec
I suspect this is aplicaple in several situations where the ticket is
joined.On Sun, May 04, 2003 at 08:48:50AM -0400, Jesse Vincent wrote:
A number of folks wrote me to say that they saw dramatic speedups with
the new searchbuilder. I would, of course, appreciate additional help
tuning for postgres. I know there’s been a suggested column type change
in the ACL system for faster querying which I haven’t had a chance to
lookup, but more work by the folks who know postgres well would be
appreciated.Well yes, it dropped from `i-don’t-know’ to 8 minutes with this giant
query. Creating two additional indexes dropped it further to about 1
minute – which is still too slow. Certainly I could rewrite some query
manually to get some speedups if it helps (i.e. if it is integratable
into RT or SearchBuilder).Actually, I’d appreciate it if you could have a look at:
http://lists.fsck.com/pipermail/rt-devel/2003-April/003697.html
I think we’re currently around aidan’s partially-optimized query.
Would you mind having a look and seeing if you can improve on his work?
I’m happy to put in more effort to speed postgres up, if I can get more
input about what will help.-j
–
Christian Zagrodnickgocept gmbh & co. kg - schalaunische strasse 6 - 06366 koethen/anhalt
fon. +49 3496 3099112, +49 179 1463644
fax. +49 3496 3099118–
Request Tracker... So much more than a help desk — Best Practical Solutions – Trouble Ticketing. Free.
Christian Zagrodnick
gocept gmbh & co. kg - schalaunische strasse 6 - 06366 koethen/anhalt
fon. +49 3496 3099112, +49 179 1463644
fax. +49 3496 3099118
– issued on viewing a ticket, unformatted
– basicly the same just readable
– 20 secs still– explain analyze showed the problem: one small change in joining et voila.
– duration: 0.526543 secI suspect this is aplicaple in several situations where the ticket is
joined.
Now that is perhaps the most interesting searchbuilder-related thing
I’ve heard all day. Thank you.
I suspect I’ll have time to kill on the plane to the UK on monday. I’ll
try to get out a test release of searchbuilder then to see what it can
do.
j