Mysql queries slow

RT Users
1

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

2

with no real mysql tweaking on RT 3.8.8 I get:

mysql> select count(id) from Attachments;
| count(id) |
| 41636 |
1 row in set (0.62 sec)

while significantly less that yours, .62 seconds isn’t too shabby…-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com on behalf of William Graboyes
Sent: Wed 7/21/2010 8:19 PM
To: rt-users
Subject: [rt-users] Mysql queries slow

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

3

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

4

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,
BillOn Thu, Jul 22, 2010 at 06:09, Kenneth Marshall ktm@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

5

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

Hi Bill,

It looks like you are being constrained by your hardware. The rows
in the Transactions table are much, much smaller than those in the
Attachments table. This means that on a system such as yours with
only 2GB of memory, pretty much the entire Transactions table can
be cached in memory. Unless you have very small attachments, this
will not be the case for the Attachments table. I suspect that the
lookups are quick but retrieving all the attachments via effectively
random I/O on a single disk is holding you back.

Cheers,
Ken

6

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.

It’s worth trying out mysqltuner.pl and seeing what it tells you.
(http://mysqltuner.pl)