2.0.9 bogs with >10K tickets

RT Users
1

Folks,

Poor MySQL is pegging CPU trying to update tickets these days. I’m not sure
when this began, but we just exceeded the 10,000 mark on tickets today, which
prompted me to spend a little time looking at the performance scaling.

Are there any knobs I can twiddle to improve MySQL’s response time? I haven’t
spent a lot of time digging into the schema . . . but would assume that a great
deal of thought had gone into this already.

Thanks!

-Darren

2

Hi Darren,

Check at archive there are some posts on indexes you can apply to your db
which greatly improves performance if you hardware is suitable for the
operations of your box. 10,000 tickets is relativaly small for a MySQL
database considering the schema of the archive.

Poor MySQL is pegging CPU trying to update tickets these days. I’m not
sure
when this began, but we just exceeded the 10,000 mark on tickets today,
which
prompted me to spend a little time looking at the performance scaling.

Regards,
SteveFrom: “Darren Nickerson” darren@dazza.org
To: rt-users@lists.fsck.com
Sent: Thursday, December 20, 2001 11:37 AM
Subject: [rt-users] 2.0.9 bogs with >10K tickets

Folks,

Are there any knobs I can twiddle to improve MySQL’s response time? I
haven’t
spent a lot of time digging into the schema . . . but would assume that a
great
deal of thought had gone into this already.

Thanks!

-Darren

rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users

3

Steve> Hi Darren,

Steve> Check at archive there are some posts on indexes you can apply to your
Steve> db which greatly improves performance if you hardware is suitable for
Steve> the operations of your box. 10,000 tickets is relativaly small for a
Steve> MySQL database considering the schema of the archive.

Thanks Steve, I DID check the archives before bothering the list and I thought
I only saw posts which referred to the 1.x release series of RT. I has assumed
that 2.0.9 would have included much of that.

I’ll take a closer look.

-Darren

4

Here’s what I have from an old email to the list:

alter table Transactions ADD INDEX TicketIndex(Ticket);
alter table Transactions ADD INDEX EffectiveTicketIndex(EffectiveTicket);
alter table Transactions ADD INDEX CreatorIndex(Creator);
alter table Attachments ADD INDEX CreatorIndex(Creator);
alter table Attachments add index ParentIndex(Parent);
alter table Attachments ADD INDEX TransactionId(TransactionId);
alter table Tickets ADD INDEX EffectiveIdIndex(EffectiveId);
alter table Tickets ADD INDEX OwnerIndex(Owner);
alter table Tickets ADD INDEX CreatorIndex(Creator);

Hope this helps.
Chris Scott
Host Orlando, Inc.

407-622-8955

5

If you installed 2.0.9, from scratch, it should be up to date on indices.
If you upgraded from an earlier rev, the upgrade process didn’t touch your database.
(We don’t have the infratstructure to do database frobbing during an update).
How much ram is being used on the database server?On Thu, Dec 20, 2001 at 12:27:40PM -0500, Darren Nickerson wrote:

On Thu, 20 Dec 2001, “Steve” == Steve Poirier wrote:

Steve> Hi Darren,

Steve> Check at archive there are some posts on indexes you can apply to your
Steve> db which greatly improves performance if you hardware is suitable for
Steve> the operations of your box. 10,000 tickets is relativaly small for a
Steve> MySQL database considering the schema of the archive.

Thanks Steve, I DID check the archives before bothering the list and I thought
I only saw posts which referred to the 1.x release series of RT. I has assumed
that 2.0.9 would have included much of that.

I’ll take a closer look.

-Darren

rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users

http://www.bestpractical.com/products/rt – Trouble Ticketing. Free.

6

Jesse> If you installed 2.0.9, from scratch, it should be up to date on
Jesse> indices. If you upgraded from an earlier rev, the upgrade process
Jesse> didn’t touch your database. (We don’t have the infratstructure to do
Jesse> database frobbing during an update).

I updated from 2.0.0 recently. Just now, I did:

diff -u rt-2-0-0/etc/schema.mysql rt-2-0-9/etc/schema.mysql

and that’s got me quaking in my boots. LOTS! of changes to the schema besides
indices, for example:

-  Content LONGBLOB NULL  ,
-  Headers LONGBLOB NULL  ,
+  Content LONGTEXT NULL  ,
+  Headers LONGTEXT NULL  ,

Ew.

Jesse> How much ram is being used on the database server?

[darren@hewes RT]$ free
total used free shared buffers cached
Mem: 770172 761096 9076 9872 43412 264428
-/+ buffers/cache: 453256 316916
Swap: 123880 123864 16

How do you recommend I proceed?

Thanks!

-Darren

7

Jesse> If you installed 2.0.9, from scratch, it should be up to date on
Jesse> indices. If you upgraded from an earlier rev, the upgrade process
Jesse> didn’t touch your database. (We don’t have the infratstructure to do
Jesse> database frobbing during an update).

I updated from 2.0.0 recently. Just now, I did:

diff -u rt-2-0-0/etc/schema.mysql rt-2-0-9/etc/schema.mysql

and that’s got me quaking in my boots. LOTS! of changes to the schema besides
indices, for example:

  • Content LONGBLOB NULL ,
  • Headers LONGBLOB NULL ,
  • Content LONGTEXT NULL ,
  • Headers LONGTEXT NULL ,

The changes from LONGBLOB to LONGTEXT, iirc, get us case-insensitive searching.
There aren’t, to my knowledge, any incompatible changes. You can add all the indices
by hand if you want or you can do a full database dump, install the new schema and indices
and then restore the content from the dump.

It’s probbly also worth looking at your system config. As I read that, you’ve got 768M of ram
and 128M of swap, which is full…Is mysql eating all that ram?

Jesse> How much ram is being used on the database server?

[darren@hewes RT]$ free
total used free shared buffers cached
Mem: 770172 761096 9076 9872 43412 264428
-/+ buffers/cache: 453256 316916
Swap: 123880 123864 16

How do you recommend I proceed?

Thanks!

-Darren

http://www.bestpractical.com/products/rt – Trouble Ticketing. Free.

8

Jesse> The changes from LONGBLOB to LONGTEXT, iirc, get us case-insensitive
Jesse> searching. There aren’t, to my knowledge, any incompatible
Jesse> changes. You can add all the indices by hand if you want or you can
Jesse> do a full database dump, install the new schema and indices and then
Jesse> restore the content from the dump.

Well that wasn’t a hard decision.

I dumped & dropped the database, recreated it using the 2.0.9 schema, and
re-imported. Man, was I sweating! :wink: It worked just fine though.

Things have definitely improved!! Thanks!

Jesse> It’s probbly also worth looking at your system config. As I read
Jesse> that, you’ve got 768M of ram and 128M of swap, which is full…Is
Jesse> mysql eating all that ram?

[root@hewes hylafax]# free
total used free shared buffers cached
Mem: 770172 762464 7708 8652 15340 362456
-/+ buffers/cache: 384668 385504
Swap: 123880 123068 812

It’s misleading, I’ll admit. Big-ass amount of stuff cached. The really important line is the -/+ buffers/cache line, which makes is clear I’ve got almost 400MB of RAM free.

-d

9

Darren> Things have definitely improved!! Thanks!

Substring searches by requestor and bulk updates on the returned results
still aren’t really feasible somehow though, wven with the new schema and
indices. MySQL’s processlist shows:

| 16 | rt2 | localhost | rt2 | Query | 326 | Copying to tmp table |
SELECT DISTINCT main.* FROM Tickets main, Watchers Watchers_1, Watchers W
atchers_3 LEFT JOIN Users |

And man . . . . does it ever chug. 3+ minutes even. Normal?

-d

10

It’s probably worth turning on full query logging and doing some query analysis to see what other
indices would be helpful. I’d love to hear what you find.

-jOn Thu, Dec 20, 2001 at 06:08:35PM -0500, Darren Nickerson wrote:

On Thu, 20 Dec 2001, “Darren” == Darren Nickerson wrote:

Darren> Things have definitely improved!! Thanks!

Substring searches by requestor and bulk updates on the returned results
still aren’t really feasible somehow though, wven with the new schema and
indices. MySQL’s processlist shows:

| 16 | rt2 | localhost | rt2 | Query | 326 | Copying to tmp table |
SELECT DISTINCT main.* FROM Tickets main, Watchers Watchers_1, Watchers W
atchers_3 LEFT JOIN Users |

And man . . . . does it ever chug. 3+ minutes even. Normal?

-d

http://www.bestpractical.com/products/rt – Trouble Ticketing. Free.

11

I dumped & dropped the database, recreated it using the 2.0.9
schema, and re-imported. Man, was I sweating! :wink: It worked
just fine though.

Things have definitely improved!! Thanks!

Darren, et al:

MySQL isn’t my strongest area. I have an older base instance of RT (2.0.4
through many upgrades to 2.0.9 while retaining the database each time)
just as you and would like to follow in your footsteps regarding speeding
the database queries on my upgrade to 2.0.11. Would you mind supplying the
exact commands you used to dump and re-import the rt2 database?

Thanks,
Christian

Christian Gilmore
Team Lead
Web Infrastructure & Tools
IBM Software Group

12

Christian,

No guarantees here - please backup your database before trying this. Dropping a
database really sends it to bit-heaven.

The post I was thinking of was:

http://lists.fsck.com/pipermail/rt-users/2001-December/005796.html

so:

mysqldump -c -t rt2 > rt2.sqldump (dumps the data)
mysqldump -d rt2 > rt2.sqlschema (dumps only the schema)

then you have to do:

mysqladmin drop rt2 (drop the database)
mysqladmin create rt2 (creates a new, empty one)

Then you need to import the SQL schemas from a new RT distribution, which give
you all the jazzy new indices etc:

cd rt-2-0-x/etc
mysql rt2 < schema.mysql (sets up the RT schemas)

and finally re-import your data:

mysql rt2  < rt2.sqlschema
mysql rt2  < rt2.sqldump

(with whatever usernames and passwords you have specified in each case using the -p flags if appropriate).

Hope this helps!

-Darren