FW: [rt-users] RT 3.2.3 -> 3.4.4

[Ruslan Zakirov]

On 10/25/05, J.P. Racine <racinejp at vianet.ca> wrote:
>
> I think that I must have something seriously wrong with the database after an
> upgrade of rt3.2.3 to 3.4.4.  In particular how my CachedGoupMembers table has
> an index length of  225001472 for 2471666 rows... I think that mysqld is just
> stalling on a table copy because the mysqld cpu ( cache hits ) go to %99 and
> frequently abort client conections.  If I kill the mysql process the
> 'Tickets_Overlay.pm' related web UI pages work as they 'normally should'.

[snip]

> These two queries are causing the most troupble:...
>
> SELECT DISTINCT main.* FROM Users main , Principals Principals_1,
> CachedGroupMembers CachedGroupMembers_2  WHERE ((CachedGroupMembers_2.GroupId =
> '1102001')) AND ((CachedGroupMembers_2.MemberId = Principals_1.id)) AND
> ((Principals_1.Disabled = '0')) AND ((main.id = Principals_1.id))    ORDER BY
> main.Name ASC

[snip]

>
> SELECT DISTINCT main.* FROM Users main , Principals Principals_1,
> CachedGroupMembers CachedGroupMembers_2  WHERE ((CachedGroupMembers_2.GroupId =
> NULL)) AND ((CachedGroupMembers_2.MemberId = Principals_1.id)) AND
> ((Principals_1.Disabled = '0')) AND ((main.id = Principals_1.id))    ORDER BY
> main.Name ASC
EXPLAIN is useless because you have changed query, could you resend
correct EXPLAIN.

>
> +----+-------------+----------------------+--------+--------------------+-------
> -----+---------+-----------------------------------+------+---------------------
> --------------------------------------+
> | id | select_type | table                | type   | possible_keys      | key
> | key_len | ref                               | rows | Extra
> |
> +----+-------------+----------------------+--------+--------------------+-------
> -----+---------+-----------------------------------+------+---------------------
> --------------------------------------+
> |  1 | SIMPLE      | CachedGroupMembers_2 | ref    | DisGrouMem,GrouMem |
> DisGrouMem |       5 | const                             |    1 | Using where;
> Using index; Using temporary; Using filesort |
> |  1 | SIMPLE      | Principals_1         | eq_ref | PRIMARY            |
> PRIMARY    |       4 | rt3.CachedGroupMembers_2.MemberId |    1 | Using where
> |
> |  1 | SIMPLE      | main                 | eq_ref | PRIMARY,Users3     |
> PRIMARY    |       4 | rt3.Principals_1.id               |    1 |
> |
> +----+-------------+----------------------+--------+--------------------+-------
> -----+---------+-----------------------------------+------+---------------------
> --------------------------------------+
> 3 rows in set (0.00 sec)
>
> Suggestions? Is this a mysql table access problem/bug? Should you be using a
> GROUP BY instead of ORDER BY?  Technically the requests work but the database
> looks hosed...
>
> -jp
>
> >-----Original Message-----
> >From: J.P. Racine [mailto:racinejp at vianet.ca]
> >Sent: Friday, October 21, 2005 6:57 PM
> >To: 'rt-users at lists.bestpractical.com'
> >Subject: RE: [rt-users] RT 3.2.3 -> 3.4.4
> >
> >The speed issues are related to SELECT DISTICT where using the query builder
> >causes a timeout ( past what fastcgi is set to 120-240 )
> >
> >FastCGI 2.4.2
> >Apache 1.3.34
> >Mysql 4.1.15
>
>
>
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> Be sure to check out the RT Wiki at http://wiki.bestpractical.com
>
> Buy your copy of our new book, RT Essentials, today!
>
> Download a free sample chapter from http://rtbook.bestpractical.com
>


--
Best regards, Ruslan.