Your message dated Tue, 11 Mar 2014 13:00:09 +0000 with message-id <[email protected]> and subject line Bug#512759: fixed in request-tracker4 4.2.3-1 has caused the Debian Bug report #512759, regarding "request-tracker4: Database schema - possible missing index for Postgres on Groups table" to be marked as done.
This means that you claim that the problem has been dealt with. If this is not the case it is now your responsibility to reopen the Bug report if necessary, and/or fix the problem forthwith. (NB: If you are a system administrator and have no idea what this message is talking about, this may indicate a serious mail system misconfiguration somewhere. Please contact [email protected] immediately.) -- 512759: http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=512759 Debian Bug Tracking System Contact [email protected] with problems
--- Begin Message ---Package: request-tracker3.8 Version: 3.8.2-1~experimental1 Severity: normal Tags: patch This is a similar bug report to #512653. Tested on an instance with 50,000 tickets. Box has 4x3.2Ghz Xeon cores and a reasonable amount of memory. Although there are two existing indexes for the Groups table, I've been consistantly seeing one particular query slowing RT down under Postgres. The query is: SELECT * FROM Groups WHERE LOWER(Domain) = LOWER($1) AND LOWER(Type) = LOWER($2); And I've tested with the following substitutions for the bind: SELECT * FROM Groups WHERE LOWER(Domain) = LOWER('SystemInternal') AND LOWER(Type) = LOWER('Privileged'); Under load, I've seen execution times of > 400ms on this query, though on an unloaded box it's much lower: rt=# explain analyze SELECT * FROM Groups WHERE LOWER(Domain) = LOWER('SystemInternal') AND LOWER(Type) = LOWER('Privileged'); QUERY PLAN ------------------------------------------------------------------------------------------------------------- Seq Scan on groups (cost=0.00..5628.92 rows=5 width=66) (actual time=0.023..109.991 rows=1 loops=1) Filter: ((lower((domain)::text) = 'systeminternal'::text) AND (lower((type)::text) = 'privileged'::text)) Total runtime: 110.039 ms (3 rows) Creating a specific index for this query speeds things up significantly (about 1000x faster in terms of actual execution speed): rt=# CREATE INDEX Groups3 ON Groups (LOWER(Domain), LOWER(Type)); CREATE INDEX rt=# explain analyze SELECT * FROM Groups WHERE LOWER(Domain) = LOWER('SystemInternal') AND LOWER(Type) = LOWER('Privileged'); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on groups (cost=4.35..23.61 rows=5 width=66) (actual time=0.128..0.130 rows=1 loops=1) Recheck Cond: ((lower((domain)::text) = 'systeminternal'::text) AND (lower((type)::text) = 'privileged'::text)) -> Bitmap Index Scan on groups3 (cost=0.00..4.34 rows=5 width=0) (actual time=0.122..0.122 rows=1 loops=1) Index Cond: ((lower((domain)::text) = 'systeminternal'::text) AND (lower((type)::text) = 'privileged'::text)) Total runtime: 0.172 ms (5 rows) I've seen this query called quite often - in fact pretty much any time a permissions check is invoked. Rather than adding this INDEX, is it necessary to make these case insensitive? The LOWERs are put in by _MakeClauseCaseInsensitive of DBIx::SearchBuilder::Handle::Pg, which is called on line 362 of RT::Record by LoadByCols. The particular queries which this INDEX deal with are called by: LoadSystemInternalGroup of Group_Overlay.pm LoadSystemRoleGroup of Group_Overlay.pm Given that there are only 9 distinct types in the groups table, would it be better to remove the case change on at least the types column? That said, I can't imagine it being that easy to do so for one column... Anyway, digression aside, patch attached. Andrew Nicols -- Systems Developer e: [email protected] im: [email protected] t: +44 (0)1524 5 10147 Lancaster University Network Services is a limited company registered in England and Wales. Registered number: 4311892. Registered office: University House, Lancaster University, Lancaster, LA1 4YW -- System Information: Debian Release: 5.0 APT prefers testing APT policy: (500, 'testing') Architecture: i386 (i686) Kernel: Linux 2.6.26-1-vserver-686-bigmem (SMP w/2 CPU cores) Locale: LANG=en_GB.UTF-8, LC_CTYPE=en_GB.UTF-8 (charmap=UTF-8) Shell: /bin/sh linked to /bin/bash--- schema.Pg 2009-01-22 15:05:25.000000000 +0000 +++ schema.Pg.new 2009-01-23 12:32:30.000000000 +0000 @@ -140,6 +140,7 @@ ); CREATE UNIQUE INDEX Groups1 ON Groups (Domain,Instance,Type,id, Name); CREATE INDEX Groups2 On Groups (Type, Instance, Domain); +CREATE INDEX Groups3 ON Groups (LOWER(Domain), LOWER(Type)); -- }}}
--- End Message ---
--- Begin Message ---Source: request-tracker4 Source-Version: 4.2.3-1 We believe that the bug you reported is fixed in the latest version of request-tracker4, which is due to be installed in the Debian FTP archive. A summary of the changes between this version and the previous one is attached. Thank you for reporting the bug, which will now be closed. If you have further comments please address them to [email protected], and the maintainer will reopen the bug report if appropriate. Debian distribution maintenance software pp. Dominic Hargreaves <[email protected]> (supplier of updated request-tracker4 package) (This message was generated automatically at their request; if you believe that there is a problem with it please contact the archive administrators by mailing [email protected]) -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Format: 1.8 Date: Sun, 02 Mar 2014 16:04:58 +0000 Source: request-tracker4 Binary: request-tracker4 rt4-clients rt4-standalone rt4-fcgi rt4-apache2 rt4-db-postgresql rt4-db-mysql rt4-db-sqlite rt4-doc-html Architecture: source all Version: 4.2.3-1 Distribution: experimental Urgency: medium Maintainer: Debian Request Tracker Group <[email protected]> Changed-By: Dominic Hargreaves <[email protected]> Description: request-tracker4 - extensible trouble-ticket tracking system rt4-apache2 - Apache 2 specific files for request-tracker4 rt4-clients - mail gateway and command-line interface to request-tracker4 rt4-db-mysql - MySQL database backend for request-tracker4 rt4-db-postgresql - PostgreSQL database backend for request-tracker4 rt4-db-sqlite - SQLite database backend for request-tracker4 rt4-doc-html - HTML documentation for request-tracker4 rt4-fcgi - External FastCGI support for request-tracker4 rt4-standalone - Standalone web server support for request-tracker4 Closes: 512759 644187 654697 712147 733516 735490 Changes: request-tracker4 (4.2.3-1) experimental; urgency=medium . * New upstream release (Closes: #733516) - update (Build)-Depends - update Apache config to match upstream docs - improve PostgreSQL indices (Closes: #512759) - don't enable GPG (or SMIME) by default (Closes: #654697) - include database upgrade script and NEWS entry - update debian/copyright - include NEWS item alerting of incompatible changes - update Lintian overrides for new file paths * Add rt4-standalone package (Closes: #644187) (thanks, Bastian Blank) * Log to a file rather than syslog by default (Closes: #712147) * Update Standards-Version (no changes) * Add a note about how to configure RT from the root URL (Closes: #735490) Checksums-Sha1: 4ea3c861aace375edd58d4cf88c7d1b3297752de 5456 request-tracker4_4.2.3-1.dsc bc844d528ab0e19f47c8acd688d737b739cebd7e 1316650 request-tracker4_4.2.3.orig-third-party-source.tar.gz e9e48c0a6d6b005e15c2f65c7919f70b086e5569 7546577 request-tracker4_4.2.3.orig.tar.gz 2f365a450f3ec00c066f5305cd09e1996f5891de 69772 request-tracker4_4.2.3-1.debian.tar.xz 3dc69d33175c17829b7ccd53295a8d2d6fd887f9 3027832 request-tracker4_4.2.3-1_all.deb d547219cbce1bdaf7a264fe77962cfa4e3deac54 50412 rt4-clients_4.2.3-1_all.deb 488fd0f53511d771fb08757c415f918dbaa918a1 15342 rt4-standalone_4.2.3-1_all.deb 1298b61f3f53a5f78103519aa3c83e4f01d56c84 17674 rt4-fcgi_4.2.3-1_all.deb baae116029a14b54fd2551f51f56b015bafc735f 16632 rt4-apache2_4.2.3-1_all.deb 2aa2b061d1eeebdbe638bce69488b4ccb7092f25 15938 rt4-db-postgresql_4.2.3-1_all.deb 47b7cb934e53715f0e373517301575dd109c7b3f 15942 rt4-db-mysql_4.2.3-1_all.deb 23c43b2cb1f4acc9f66da8192b4ec6c2758abe3d 16064 rt4-db-sqlite_4.2.3-1_all.deb e9401d9ea9d725e5aba2a9fabd1a90b657db5d19 974390 rt4-doc-html_4.2.3-1_all.deb Checksums-Sha256: cdb4eb2fac089371004f8415bf727cea575457918252f242125b66752b439e77 5456 request-tracker4_4.2.3-1.dsc 2886a23e80003ac42fe1f43d62500e8e6ae1b18ab5087f003cdf699ed126e810 1316650 request-tracker4_4.2.3.orig-third-party-source.tar.gz 9353ac5354786c96f846c39b3f861ae78853b09e3b37cbbe89109aaa714e9a66 7546577 request-tracker4_4.2.3.orig.tar.gz 448013e1b1f9629bd10f686cb81d2d17a09fdf7b10da81e06e6acba10b8ea4b4 69772 request-tracker4_4.2.3-1.debian.tar.xz c7a2df78ca5800b19bfe4d4e162e80fc9fe652dc245540ba745c05d7c7a019c9 3027832 request-tracker4_4.2.3-1_all.deb 627120d2a5cc46e10580248bb74178c2ab04a5a84fc959c14443ca52267a7f94 50412 rt4-clients_4.2.3-1_all.deb 37be2345db6c750bdfb2d1a93ce6eef4344db576849dcd5ce832062aa0d77ff8 15342 rt4-standalone_4.2.3-1_all.deb 771bd880f7492bbfe1de160117d76ed3f7bd8902e71d5aefdda57dd551daf957 17674 rt4-fcgi_4.2.3-1_all.deb 560c5287b0a7e4a615a58138c44c064eb8620e2437026b1be0493bfdb9f6d014 16632 rt4-apache2_4.2.3-1_all.deb 6e8c796ca9d41babd5fd421aea9583424dc489fcc2b86b75c1a41696ec5c3b37 15938 rt4-db-postgresql_4.2.3-1_all.deb 5db1f14789687e2e85aaa8963bb88cdcd6af1cb08e9771e67240f0670babda4a 15942 rt4-db-mysql_4.2.3-1_all.deb d8774edb79ff43277b708c1c63796919c51e8568895b46caadbf7a786ae7f8b4 16064 rt4-db-sqlite_4.2.3-1_all.deb 35681d173efda690d992329c92bcd1eb9399df42e71fdc873e49c2d5371404c8 974390 rt4-doc-html_4.2.3-1_all.deb Files: 019c283ab81f532fd63b0f1bc0c5a662 5456 misc optional request-tracker4_4.2.3-1.dsc b70761f8f346feaf20f8be8926520138 1316650 misc optional request-tracker4_4.2.3.orig-third-party-source.tar.gz 6a60ad33d2f16d76ee73dbc181744eb9 7546577 misc optional request-tracker4_4.2.3.orig.tar.gz ed5c5d73235c907137cdf20ff91c479a 69772 misc optional request-tracker4_4.2.3-1.debian.tar.xz 89ef0ee537ac48bb1e2db3ec4d8133f1 3027832 misc optional request-tracker4_4.2.3-1_all.deb d642f43898f61700bea544471e72809e 50412 misc optional rt4-clients_4.2.3-1_all.deb caf5894e17753134845616c5e5848778 15342 misc optional rt4-standalone_4.2.3-1_all.deb 195e89719cad0b3a029cf91cc2a97c30 17674 misc optional rt4-fcgi_4.2.3-1_all.deb 561cc9d75aa343478baab5d5313f94ef 16632 misc optional rt4-apache2_4.2.3-1_all.deb 2b28edfaa8125d32c602875d97a8ac5e 15938 misc optional rt4-db-postgresql_4.2.3-1_all.deb 234878dcfac9c00dbf911f29e1383636 15942 misc optional rt4-db-mysql_4.2.3-1_all.deb f9613d89db1f66ef328dfb5a0435560b 16064 misc optional rt4-db-sqlite_4.2.3-1_all.deb 19fdaa3c9e714f2e568367a1e60c2a1b 974390 doc optional rt4-doc-html_4.2.3-1_all.deb -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBAgAGBQJTE3I5AAoJEMAFfnFNaU+yj48P/1JRWL2ETUPEbyRuXiWrJkmF YIruTCOPbW2Hk7VBSWNhA/aAKhhMhm3fg3vnNhJECdf+TJrKKSvLhH0JYrA2t74b WkyK/gJG2xcwItfqKFIlpW5ObQ9whSW3yhM2lN6i9DW48tPAxu01wIUWolkDf16q 9a2BVEGlPGWJlGmrq/AJbnzrp8fKMl8h4mQEyyoo/ws+dgWA6tdZz7Taidj32XpE Dzaii4pwfL0V5luVyuOVRXJoFzgedFhhNuCKSwb51w3kjOqUrEym3sXACa3wlZK8 JePnnV/a4BHqNwtONB38phq8BWJDpZZq8/x92KqsApJel7auELIm1auK0MyKlwTM KTtfYxDVdx8FIHw0RBA6+Y3Ko5rHLuc5Jsw5z2r+ayvRblb/hRctqh8q7psTaY8Z iN/lgMcSfO6D+5uZkLMdY+aRhiEKO5JdWkECeTflSrNy2WVlboJGNrn8jgLNU104 fKjy2BCiy0KfCGd+OMfc/eUb8yAi0dwYYzW0ohTsGK6jhCxmMPgVB+Jt6EiwYhnu 6guQ86++36ZewoOsdv6GZ7Mnp7rWt+hpb4nC/eLBIYMSneHTIEFaoWbM6t7yTVLf 2gII1OHF8vWu9vvmkzKqDjPrdCcpFnIUz9Kl50itp1ppQJP8pgUZcn7EdkThpXZy pms0TnXFLKc8WThRV5Ej =Jkkn -----END PGP SIGNATURE-----
--- End Message ---
