[rt-users] Mysql queries slow

[William Graboyes]

Hi Ken,

I do not have the full hardware specs.  It is a hosted RT system.  The specs
that I do have access to are as follows:

Probably a dual core single CPU system, speed unknown
2gb of ram
80 gb hd space they are using md but I do not know the configuration
particulars.

I know it is not much help.  I have applied all the mysql tuning tricks I
know to it.

Thanks,
Bill



On Thu, Jul 22, 2010 at 06:09, Kenneth Marshall <ktm at rice.edu> wrote:

> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20100722/3621aacb/attachment.htm>