Re: [rt-users] any page showing a user drop down in a ticket is loading slow
On Wed, Dec 18, 2013 at 02:15:06PM -0500, Adam Hobaugh wrote: > On 12/18/2013 01:07 PM, Kevin Falcone wrote: > >Your query here says that MySQL is looking at a pretty tiny dataset > >and running the same query on multiple databases here shows a wildly > >different explain and very quick results. This usually points to you > >having some enormous tables (although the explain doesn't indicate > >that) or a badly tuned mysql. > > > >Can you download and run http://mysqltuner.pl on your database. > I will have to go through this script and consult with a colleague > before running that as our database contains more than just our > ticket system. We'll see what it returns. Nothing in your data set is particularly large, which points more and more to MySQL problems. -kevin > >It would also be interesting to see > > SELECT TABLE_ROWS, TABLE_NAME > > FROM information_schema.tables > > WHERE TABLE_SCHEMA = 'rt4'; > >changing your database name where needed. > mysql> SELECT TABLE_ROWS, TABLE_NAME > -> FROM information_schema.tables > -> WHERE TABLE_SCHEMA = 'rt4_2_1'; > ++-+ > | TABLE_ROWS | TABLE_NAME | > ++-+ > |579 | ACL | > | 0 | Articles| > |1436268 | Attachments | > | 5708 | Attributes | > | 353565 | CachedGroupMembers | > | 0 | Classes | > | 36 | CustomFieldValues | > | 4 | CustomFields| > | 107281 | GroupMembers| > | 157020 | Groups | > | 1579 | Links | > | 0 | ObjectClasses | > | 14357 | ObjectCustomFieldValues | > | 4 | ObjectCustomFields | > | 45 | ObjectScrips| > | 0 | ObjectTopics| > | 162308 | Principals | > | 16 | Queues | > | 21 | ScripActions| > | 22 | ScripConditions | > | 45 | Scrips | > | 35 | Templates | > | 35232 | Tickets | > | 0 | Topics | > | 578000 | Transactions| > | 10340 | Users | > | 4518 | sessions| > ++-+ > 27 rows in set, 1 warning (0.43 sec) pgpkzJgoGvsK2.pgp Description: PGP signature
Re: [rt-users] any page showing a user drop down in a ticket is loading slow
On 12/18/2013 01:07 PM, Kevin Falcone wrote: Your query here says that MySQL is looking at a pretty tiny dataset and running the same query on multiple databases here shows a wildly different explain and very quick results. This usually points to you having some enormous tables (although the explain doesn't indicate that) or a badly tuned mysql. Can you download and run http://mysqltuner.pl on your database. I will have to go through this script and consult with a colleague before running that as our database contains more than just our ticket system. It would also be interesting to see SELECT TABLE_ROWS, TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA = 'rt4'; changing your database name where needed. mysql> SELECT TABLE_ROWS, TABLE_NAME -> FROM information_schema.tables -> WHERE TABLE_SCHEMA = 'rt4_2_1'; ++-+ | TABLE_ROWS | TABLE_NAME | ++-+ |579 | ACL | | 0 | Articles| |1436268 | Attachments | | 5708 | Attributes | | 353565 | CachedGroupMembers | | 0 | Classes | | 36 | CustomFieldValues | | 4 | CustomFields| | 107281 | GroupMembers| | 157020 | Groups | | 1579 | Links | | 0 | ObjectClasses | | 14357 | ObjectCustomFieldValues | | 4 | ObjectCustomFields | | 45 | ObjectScrips| | 0 | ObjectTopics| | 162308 | Principals | | 16 | Queues | | 21 | ScripActions| | 22 | ScripConditions | | 45 | Scrips | | 35 | Templates | | 35232 | Tickets | | 0 | Topics | | 578000 | Transactions| | 10340 | Users | | 4518 | sessions| ++-+ 27 rows in set, 1 warning (0.43 sec) Thanks! //adam
Re: [rt-users] any page showing a user drop down in a ticket is loading slow
On 18 December 2013 10:07, Kevin Falcone wrote: > Can you download and run http://mysqltuner.pl on your database. > Fun tip. You can download that with just “wget mysqltuner.pl”. There’s some redirection magic that makes it work with just that command. -- Landon Stewart :: lstew...@iweb.com Lead Specialist, Abuse and Security Management Spécialiste principal, gestion des abus et sécurité http://iweb.com :: +1 (888) 909-4932
Re: [rt-users] any page showing a user drop down in a ticket is loading slow
On Wed, Dec 18, 2013 at 12:29:05PM -0500, Adam Hobaugh wrote: > >The slow query logs are most helpful when you then run EXPLAIN on the > >query so you know why the database is having trouble. > Here is the EXPLAIN for the two queries. > > mysql> EXPLAIN SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL > ACL_3 JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) > JOIN CachedGroupMembers CachedGroupMembers_2 ON ( > CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN > CachedGroupMembers CachedGroupMembers_4 ON ( > CachedGroupMembers_4.MemberId = Principals_1.id ) WHERE > ((ACL_3.ObjectType = 'RT::Queue' AND ACL_3.ObjectId = 3) OR > (ACL_3.ObjectType = 'RT::System') OR (ACL_3.ObjectType = 'RT::Queue' > AND ACL_3.ObjectId = 3) OR (ACL_3.ObjectType = 'RT::System')) AND > (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId) AND > (ACL_3.PrincipalType = 'Group') AND (ACL_3.RightName = 'OwnTicket' > OR ACL_3.RightName = 'SuperUser') AND (CachedGroupMembers_2.Disabled > = '0') AND (CachedGroupMembers_2.GroupId = '4') AND > (CachedGroupMembers_4.Disabled = '0') AND (Principals_1.Disabled = > '0') AND (Principals_1.PrincipalType = 'User') AND (Principals_1.id > != '1') ORDER BY main.Name ASC; > ++-+--+++-+-+---+--+---+ > | id | select_type | table| type | possible_keys > | key | key_len | ref > | rows | Extra | > ++-+--+++-+-+---+--+---+ > | 1 | SIMPLE | CachedGroupMembers_2 | range | > DisGrouMem,CachedGroupMembers3,cachedgroupmembers1 | DisGrouMem > | 10 | NULL | 264 | Using > where; Using index; Using temporary; Using filesort | > | 1 | SIMPLE | main | eq_ref | PRIMARY > | PRIMARY | 4 | > rt4_2_1.CachedGroupMembers_2.MemberId |1 | > | > | 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY > | PRIMARY | 4 | rt4_2_1.main.id > |1 | Using where; Distinct | > | 1 | SIMPLE | CachedGroupMembers_4 | ref| > DisGrouMem,CachedGroupMembers3,cachedgroupmembers1 | > CachedGroupMembers3 | 5 | > rt4_2_1.CachedGroupMembers_2.MemberId |1 | Using where; Distinct > | > | 1 | SIMPLE | ACL_3| range | ACL1 > | ACL1| 85 | NULL > | 13 | Using where; Using index; Distinct| > ++-+--+++-+-+---+--+---+ > 5 rows in set (0.01 sec) Your query here says that MySQL is looking at a pretty tiny dataset and running the same query on multiple databases here shows a wildly different explain and very quick results. This usually points to you having some enormous tables (although the explain doesn't indicate that) or a badly tuned mysql. Can you download and run http://mysqltuner.pl on your database. It would also be interesting to see SELECT TABLE_ROWS, TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA = 'rt4'; changing your database name where needed. -kevin pgpwhbzre2ZKc.pgp Description: PGP signature
Re: [rt-users] any page showing a user drop down in a ticket is loading slow
On 12/18/2013 11:43 AM, Kevin Falcone wrote: On Tue, Dec 17, 2013 at 02:53:53PM -0500, Adam Hobaugh wrote: I just updated from 4.0.10 to 4.2.1 and it seems to be working well except when I am in a ticket, any page that shows a user drop down, ie display and people load incredible slowly. I enabled slow query logging and below are the two queries that appear to be causing the problem. I appreciate any help in resolving this. 10 second load times is rough. Please let me know what further information is needed. The slow query logs are most helpful when you then run EXPLAIN on the query so you know why the database is having trouble. Here is the EXPLAIN for the two queries. mysql> EXPLAIN SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_3 JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN CachedGroupMembers CachedGroupMembers_4 ON ( CachedGroupMembers_4.MemberId = Principals_1.id ) WHERE ((ACL_3.ObjectType = 'RT::Queue' AND ACL_3.ObjectId = 3) OR (ACL_3.ObjectType = 'RT::System') OR (ACL_3.ObjectType = 'RT::Queue' AND ACL_3.ObjectId = 3) OR (ACL_3.ObjectType = 'RT::System')) AND (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId) AND (ACL_3.PrincipalType = 'Group') AND (ACL_3.RightName = 'OwnTicket' OR ACL_3.RightName = 'SuperUser') AND (CachedGroupMembers_2.Disabled = '0') AND (CachedGroupMembers_2.GroupId = '4') AND (CachedGroupMembers_4.Disabled = '0') AND (Principals_1.Disabled = '0') AND (Principals_1.PrincipalType = 'User') AND (Principals_1.id != '1') ORDER BY main.Name ASC; ++-+--+++-+-+---+--+---+ | id | select_type | table| type | possible_keys | key | key_len | ref | rows | Extra | ++-+--+++-+-+---+--+---+ | 1 | SIMPLE | CachedGroupMembers_2 | range | DisGrouMem,CachedGroupMembers3,cachedgroupmembers1 | DisGrouMem | 10 | NULL | 264 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | main | eq_ref | PRIMARY| PRIMARY | 4 | rt4_2_1.CachedGroupMembers_2.MemberId |1 | | | 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY| PRIMARY | 4 | rt4_2_1.main.id |1 | Using where; Distinct | | 1 | SIMPLE | CachedGroupMembers_4 | ref| DisGrouMem,CachedGroupMembers3,cachedgroupmembers1 | CachedGroupMembers3 | 5 | rt4_2_1.CachedGroupMembers_2.MemberId |1 | Using where; Distinct | | 1 | SIMPLE | ACL_3| range | ACL1 | ACL1 | 85 | NULL | 13 | Using where; Using index; Distinct| ++-+--+++-+-+---+--+---+ 5 rows in set (0.01 sec) mysql> EXPLAIN SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_3 JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN CachedGroupMembers CachedGroupMembers_4 ON ( CachedGroupMembers_4.MemberId = Principals_1.id ) WHERE ((ACL_3.ObjectType = 'RT::Ticket' AND ACL_3.ObjectId = 35402) OR (ACL_3.ObjectType = 'RT::Queue' AND ACL_3.ObjectId = 3) OR (ACL_3.ObjectType = 'RT::System') OR (ACL_3.ObjectType = 'RT::Ticket' AND ACL_3.ObjectId = 35402) OR (ACL_3.ObjectType = 'RT::Queue' AND ACL_3.ObjectId = 3) OR (ACL_3.ObjectType = 'RT::System')) AND (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId) AND (ACL_3.PrincipalType = 'Group') AND (ACL_3.RightName = 'OwnTicket' OR ACL_3.RightName = 'SuperUser') AND (CachedGroupMembers_2.Disabled = '0') AND (CachedGroupMembers_2.GroupId = '4') AND (CachedGroupMembers_4.Disabled = '0') AND (Principals_1.Disabled = '0') AND (Principals_1.PrincipalType = 'User') AND (Principals_1.id != '1') ORDER BY
Re: [rt-users] any page showing a user drop down in a ticket is loading slow
On Tue, Dec 17, 2013 at 02:53:53PM -0500, Adam Hobaugh wrote: > I just updated from 4.0.10 to 4.2.1 and it seems to be working well > except when I am in a ticket, any page that shows a user drop down, > ie display and people load incredible slowly. I enabled slow query > logging and below are the two queries that appear to be causing the > problem. I appreciate any help in resolving this. 10 second load > times is rough. Please let me know what further information is > needed. The slow query logs are most helpful when you then run EXPLAIN on the query so you know why the database is having trouble. Since your list is short (5 rows sent) rather than 500, you're unlikely to have the common error someone else was pointing you at where you grant OwnTicket too widely. RT 4.2 also has a workaround for this case. Do you grant your OwnTicket rights out to groups per queue, roles per queue. Do you assign lots of Ticket level use groups (adminccs/ccs, etc) which then pick up OwnTicket? -kevin > > # Time: 131217 14:42:38 > # User@Host: rt_user[rt_user] @ ticket.cs.pitt.edu [136.142.23.83] > # Query_time: 9 Lock_time: 0 Rows_sent: 5 Rows_examined: 175403 > SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_3 JOIN > Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN > CachedGroupMembers CachedGroupMembers_2 ON ( > CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN > CachedGroupMembers CachedGroupMembers_4 ON ( > CachedGroupMembers_4.MemberId = Principals_1.id ) WHERE > ((ACL_3.ObjectType = 'RT::Queue' AND ACL_3.ObjectId = 3) OR > (ACL_3.ObjectType = 'RT::System') OR (ACL_3.ObjectType = 'RT::Queue' > AND ACL_3.ObjectId = 3) OR (ACL_3.ObjectType = 'RT::System')) AND > (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId) AND > (ACL_3.PrincipalType = 'Group') AND (ACL_3.RightName = 'OwnTicket' > OR ACL_3.RightName = 'SuperUser') AND (CachedGroupMembers_2.Disabled > = '0') AND (CachedGroupMembers_2.GroupId = '4') AND > (CachedGroupMembers_4.Disabled = '0') AND (Principals_1.Disabled = > '0') AND (Principals_1.PrincipalType = 'User') AND (Principals_1.id > != '1') ORDER BY main.Name ASC; > # Time: 131217 14:42:48 > # User@Host: rt_user[rt_user] @ ticket.cs.pitt.edu [136.142.23.83] > # Query_time: 8 Lock_time: 0 Rows_sent: 5 Rows_examined: 175403 > SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_3 JOIN > Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN > CachedGroupMembers CachedGroupMembers_2 ON ( > CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN > CachedGroupMembers CachedGroupMembers_4 ON ( > CachedGroupMembers_4.MemberId = Principals_1.id ) WHERE > ((ACL_3.ObjectType = 'RT::Ticket' AND ACL_3.ObjectId = 35402) OR > (ACL_3.ObjectType = 'RT::Queue' AND ACL_3.ObjectId = 3) OR > (ACL_3.ObjectType = 'RT::System') OR (ACL_3.ObjectType = > 'RT::Ticket' AND ACL_3.ObjectId = 35402) OR (ACL_3.ObjectType = > 'RT::Queue' AND ACL_3.ObjectId = 3) OR (ACL_3.ObjectType = > 'RT::System')) AND (ACL_3.PrincipalId = > CachedGroupMembers_4.GroupId) AND (ACL_3.PrincipalType = 'Group') > AND (ACL_3.RightName = 'OwnTicket' OR ACL_3.RightName = 'SuperUser') > AND (CachedGroupMembers_2.Disabled = '0') AND > (CachedGroupMembers_2.GroupId = '4') AND > (CachedGroupMembers_4.Disabled = '0') AND (Principals_1.Disabled = > '0') AND (Principals_1.PrincipalType = 'User') AND (Principals_1.id > != '1') ORDER BY main.Name ASC; > pgpxrlrab1s73.pgp Description: PGP signature
Re: [rt-users] any page showing a user drop down in a ticket is loading slow
On 17 December 2013 13:19, Adam Hobaugh wrote: > Hi Adam, > > I’m not sure if this applies but we had this issue and it was an RT ACL > issue. I remember that it was an issue with granting or not-granting > rights to special groups "Everyone" or “Unprivileged” but the details of > which I cannot recall exactly. I do remember there were large ACL joins in > the database whenever we would try to load pages with these drop downs just > like you’ve described in your post. > >Hello Landon, > > I removed the rights from Everyone and we had nothing on Unprivileged and > I still see the issue. Thanks for the suggestion though. > Maybe you need to go the other way with it and grant something? Maybe you need to grant some simple rights for showing users or something to Privileged. I just checked ours and I have “Privileged” under “General Rights” allowed to "View custom field values” configured in each queue. In the Global -> Group Rights the "View custom fields” is not granted. I wish I could remember the details of what solved it for us. I do remember some things were changed which caused the issue and then had to be rolled back. I just don’t recall what was changed that caused it. -- Landon Stewart :: lstew...@iweb.com Lead Specialist, Abuse and Security Management Spécialiste principal, gestion des abus et sécurité http://iweb.com :: +1 (888) 909-4932
Re: [rt-users] any page showing a user drop down in a ticket is loading slow
On 17 December 2013 13:19, Adam Hobaugh wrote: > Hi Adam, > > I’m not sure if this applies but we had this issue and it was an RT ACL > issue. I remember that it was an issue with granting or not-granting > rights to special groups "Everyone" or “Unprivileged” but the details of > which I cannot recall exactly. I do remember there were large ACL joins in > the database whenever we would try to load pages with these drop downs just > like you’ve described in your post. > >Hello Landon, > > I removed the rights from Everyone and we had nothing on Unprivileged and > I still see the issue. Thanks for the suggestion though. > Maybe you need to go the other way with it and grant something? Maybe you need to grant some simple rights for showing users or something to Privileged. I just checked ours and I have “Privileged” under “General Rights” allowed to "View custom field values” configured in each queue. In the Global -> Group Rights the "View custom fields” is not granted. I wish I could remember the details of what solved it for us. I do remember some things were changed which caused the issue and then had to be rolled back. I just don’t recall what was changed that caused it. -- Landon Stewart :: lstew...@iweb.com Lead Specialist, Abuse and Security Management Spécialiste principal, gestion des abus et sécurité http://iweb.com :: +1 (888) 909-4932
Re: [rt-users] any page showing a user drop down in a ticket is loading slow
On 17 December 2013 11:53, Adam Hobaugh wrote: > Hello, > > I just updated from 4.0.10 to 4.2.1 and it seems to be working well except > when I am in a ticket, any page that shows a user drop down, ie display and > people load incredible slowly. I enabled slow query logging and below are > the two queries that appear to be causing the problem. I appreciate any > help in resolving this. 10 second load times is rough. Please let me know > what further information is needed. > Hi Adam, I’m not sure if this applies but we had this issue and it was an RT ACL issue. I remember that it was an issue with granting or not-granting rights to special groups "Everyone" or “Unprivileged” but the details of which I cannot recall exactly. I do remember there were large ACL joins in the database whenever we would try to load pages with these drop downs just like you’ve described in your post. -- Landon Stewart :: lstew...@iweb.com Lead Specialist, Abuse and Security Management Spécialiste principal, gestion des abus et sécurité http://iweb.com :: +1 (888) 909-4932
