[rt-users] Mysql queries slow

[Kenneth Marshall]

On Wed, Jul 21, 2010 at 06:19:51PM -0700, William Graboyes wrote:
> Hi List,
> 
> As an example of what I  am talking about the query `select count(id) from
> Attachments;`  The returned result is 174039, but it takes 39.1549 seconds
> to return that simple query.  The Transactions table returns 343259 in .4358
> seconds.  Does anyone have some optimization tips beyond what is already on
> the wiki.
> 
> After a little more of my own tweaking I have the Attachments query down to
> 24.9559 seconds.
> 
> Has anyone successfully integrated RT3 with memcached?  Would I be better
> off moving the mysql server to it's own server?
> 
> Running version:
> RT 3.8.7
> MySQL 5.0.67
> 
> Total tickets as of this writing:
> 7282
> 
> Total time on RT:
> 1yr 3m
> 
> Thanks in advance for any help that can be provided.
> 
> 
> Thanks,
> Bill


Hi Bill,

You mentioned your version of the software but no details of your
actual hardware. To provide the answer to the count(*) query, the
entire table concerned needs to be read from disk. For your 
Attachments result off 39s for 174039, is that the value for the
first time the query is run or the value after multiple runs when
the table is cached in memory? We use PostgreSQL as the backend
and the first time the select query is run:

# select count(*) from attachments;
  count  
---------
 2807604
(1 row)

Time: 16707.404 ms

But the second time, the result is much faster because of caching:

# select count(*) from attachments;
  count  
---------
 2807622
(1 row)

Time: 2909.343 ms

Similarly for the transactions table:

# select count(*) from transactions;
  count  
---------
 6468511
(1 row)

Time: 4030.046 ms

And for the 2nd run with caching:

# select count(*) from transactions;
  count  
---------
 6468511
(1 row)

Time: 1094.672 ms

It does seem like your times are slower, but it could easily
be the hardware setup that you are using for RT.

Cheers,
Ken