RT3 speed thread

After a long time using rt 1.0.7 I took a look at upgrading to RT2. At
the time there were too many dependencies to want to try it, but this
week I decided to take the plunge.

I was going to go all the way to RT3 but didn’t see an upgrade path
directly, so I installed RT2 first, got it working, then upgraded to RT3.
I followed someone else’s trick of running a separate httpd so that I
could run RT2 and RT3 simultaneously. I have not made any changes other
than deleting some of the scrips in RT3 and adding a couple of test
tickets in RT2. My database is small, about 1700 tickets, a dozen users
and 8 queues

I read all the articles in the “RT 3.0 Speed” thread with interest,
because I have the same problem. RT3 is much slower than RT2. I tweaked
my InnoDB options (still bad), I converted the rt3 tables to MyISAM
(still bad), I made sure I had the latest version of DBIx::SearchBuilder
(0.82) (already had it). RT3 is still significantly slower.

So – I’m looking forward to the resolution of this little issue so I can
start using rt3!

Andrew Tefft
dryduck@mailworks.org

http://www.fastmail.fm - Or how I learned to stop worrying and
love email again

Andrew Tefft wrote:

I read all the articles in the “RT 3.0 Speed” thread with interest,
because I have the same problem. RT3 is much slower than RT2. I tweaked
my InnoDB options (still bad), I converted the rt3 tables to MyISAM
(still bad),

Convert back. :slight_smile:

I made sure I had the latest version of DBIx::SearchBuilder
(0.82) (already had it). RT3 is still significantly slower.

Can you give 3.0.3pre1 (in the devel area) a shot? It’s
drastically improved performance here.

Also, make sure you enable mysql’s query cache:

set-variable = query_cache_size=32M
set-variable = query_cache_type=1

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

Andrew Tefft wrote:

I read all the articles in the “RT 3.0 Speed” thread with interest,
because I have the same problem. RT3 is much slower than RT2. I tweaked
my InnoDB options (still bad), I converted the rt3 tables to MyISAM
(still bad),

Convert back. :slight_smile:

I made sure I had the latest version of DBIx::SearchBuilder
(0.82) (already had it). RT3 is still significantly slower.

Can you give 3.0.3pre1 (in the devel area) a shot? It’s
drastically improved performance here.

Also, make sure you enable mysql’s query cache:

set-variable = query_cache_size=32M
set-variable = query_cache_type=1

This would be for Mysql 4.0 only, right?

Matt

Can you give 3.0.3pre1 (in the devel area) a shot? It’s
drastically improved performance here.

Also, make sure you enable mysql’s query cache:

set-variable = query_cache_size=32M
set-variable = query_cache_type=1

This would be for Mysql 4.0 only, right?

Yes. And Mysql 4.0 is strongly recommended for RT3. For high loads,
RT 3.23 isn’t the right answer.

Matt


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

Have you read the FAQ? The RT FAQ Manager lives at http://fsck.com/rtfm

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

set-variable = query_cache_size=32M
set-variable = query_cache_type=1

This would be for Mysql 4.0 only, right?

Yes. And Mysql 4.0 is strongly recommended for RT3. For high loads,
RT 3.23 isn’t the right answer.

For a variety of reasons we’re not ready to jump to RT3 yet, but is there
any speed improvement for rt2 if mysql is moved to 4? I’m already doing
innodb tables and that helped a lot, but some of the optimizations people
have suggested for mysql only come along in 4.

Anyone tried this rt2/mysql4 and can comment?

Thanks,

Scott

I read all the articles in the “RT 3.0 Speed” thread with interest,
because I have the same problem. RT3 is much slower than RT2. I tweaked
my InnoDB options (still bad), I converted the rt3 tables to MyISAM
(still bad),

Convert back. :slight_smile:

I was planning on it :slight_smile:

I made sure I had the latest version of DBIx::SearchBuilder
(0.82) (already had it). RT3 is still significantly slower.

Can you give 3.0.3pre1 (in the devel area) a shot? It’s
drastically improved performance here.

Yep I’ll give it a try.

Also, make sure you enable mysql’s query cache:

set-variable = query_cache_size=32M
set-variable = query_cache_type=1

I had a 16M query_cache. Changed it to 32M and went back to InnoDB for
all but the table ‘sessions’ which was MyISAM to begin with. No
appreciable difference from that, but I’ll try 3.0.3pre1 now and report
back.

Andrew Tefft
dryduck@mailworks.org

http://www.fastmail.fm - A no graphics, no pop-ups email service

Well, I am happy to report that 3.0.3pre1 is MUCH faster than 3.0.2 and
it even beats rt2 siginificantly. I’m going to update my rt3 db and stick
with it.
Andrew Tefft
dryduck@mailworks.org

http://www.fastmail.fm - Choose from over 50 domains or use your own

Well, I am happy to report that 3.0.3pre1 is MUCH faster than 3.0.2 and
it even beats rt2 siginificantly. I’m going to update my rt3 db and stick
with it.

Can you give some installation details: mysql vs postgresql and
which version number? Does anyone have postgresql working fast
yet?

Les Mikesell
les@futuresource.com

Here are my particulars:

Sun E450, 1 gig of memory, 2 processors, Solaris 7

perl 5.8.0 – new installation, so all modules installed during the rt2
and rt3 installations are the latest versions available as of this week.

apache 1.3.27, mod_perl 1.27

mysql 4.0.13 with the following relevant entries from my.cnf. There’s one
other database (besides rt1 and rt2) that gets a lot more action than rt
(I’m currently the only active rt user). mysqld is currently hovering at
about 32 megs ram and 400 megs RSS (sheesh). There’s probably more
tweaking that can be done here, but I’ve only had mysql 4 installed for a
few days.

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 32M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 1M
read_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size= 32M
query_cache_type=1

Try number of CPU’s*2 for thread_concurrency

thread_concurrency = 4

Uncomment the following if you are using InnoDB tables

innodb_data_home_dir = /usr/local/mysql/var/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/var/
innodb_log_arch_dir = /usr/local/mysql/var/

You can set …_buffer_pool_size up to 50 - 80 %

of RAM but beware of setting memory usage too high

innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M

Set …_log_file_size to 25 % of buffer pool size

innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

Andrew Tefft
dryduck@mailworks.org

http://www.fastmail.fm - Choose from over 50 domains or use your own

“l” == les les@futuresource.com writes:

l> Can you give some installation details: mysql vs postgresql and
l> which version number? Does anyone have postgresql working fast
l> yet?

Well, I just made an executive decision that I don’t really want to
learn mysql all over again, so I’m moving my RT3 install back over to
Postgres later this afternoon (no real tickets in it so it is easy :wink:

I’ll see how it holds up. RT2 holds up great with Postgres. I added
referential integrity constraints which makes it really easy to clean
out dead tickets… Hint, hint Jesse (yes I know you have a partial
set of them for RT3 but I think they should be standard and part of
the schema from the start!)

Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

“LR” == Larry Rosenman ler@tntdental.com writes:

set of them for RT3 but I think they should be standard and part of
the schema from the start!)
LR> Can you tell me what you added? (I just converted to RT3/PG 7.3.2
LR> 2 weeks ago for my little consulting biz from RT2/PG 7.3.2).

Here are the referential integrity constraints for Postgres for RT2.
I’ve used this on Postgres 7.2. I haven’t had the opportunity to make
these for RT3 yet, but Jesse has a start for MySQL in the same
directory as the schema files.

Really, all you need to do is draw a line from one field of one table
to the field/table which links them and make the corresponding ALTER
TABLE like below. The key is knowing which lines to draw…

The PRIMARY KEYs I added because for some reason my install was
missing them. Again these are for RT2 NOT RT3.

ALTER TABLE KeywordSelects ADD PRIMARY KEY (id);
ALTER TABLE Attachments ADD PRIMARY KEY (id);
ALTER TABLE Queues ADD PRIMARY KEY (id);
ALTER TABLE Links ADD PRIMARY KEY (id);
ALTER TABLE Groups ADD PRIMARY KEY (id);
ALTER TABLE Watchers ADD PRIMARY KEY (id);
ALTER TABLE ScripConditions ADD PRIMARY KEY (id);
ALTER TABLE Transactions ADD PRIMARY KEY (id);
ALTER TABLE Scrips ADD PRIMARY KEY (id);
ALTER TABLE ACL ADD PRIMARY KEY (id);
ALTER TABLE GroupMembers ADD PRIMARY KEY (id);
ALTER TABLE ObjectKeywords ADD PRIMARY KEY (id);
ALTER TABLE Keywords ADD PRIMARY KEY (id);
ALTER TABLE Users ADD PRIMARY KEY (id);
ALTER TABLE Tickets ADD PRIMARY KEY (id);
ALTER TABLE ScripActions ADD PRIMARY KEY (id);
ALTER TABLE Templates ADD PRIMARY KEY (id);

ALTER TABLE Transactions ADD CONSTRAINT transfk1 FOREIGN KEY (Ticket) REFERENCES Tickets(id) MATCH FULL ON DELETE CASCADE;
ALTER TABLE Attachments ADD CONSTRAINT attachfk1 FOREIGN KEY (TransactionID) REFERENCES Transactions(id) MATCH FULL ON DELETE CASCADE;
ALTER TABLE Watchers ADD CONSTRAINT watchfk1 FOREIGN KEY (Value) REFERENCES Tickets(id) MATCH FULL ON DELETE CASCADE;
ALTER TABLE ObjectKeywords ADD CONSTRAINT objectfk1 FOREIGN KEY (ObjectId) REFERENCES Tickets(id) MATCH FULL ON DELETE CASCADE;
ALTER TABLE Links ADD CONSTRAINT linksfk1 FOREIGN KEY (LocalTarget) REFERENCES Tickets(id) MATCH FULL ON DELETE CASCADE;
ALTER TABLE Links ADD CONSTRAINT linksfk2 FOREIGN KEY (LocalBase) REFERENCES Tickets(id) MATCH FULL ON DELETE CASCADE;

Can you give some installation details: mysql vs postgresql and
which version number? Does anyone have postgresql working fast
yet?

My experience with RT 3.0.2 on PostgreSql 7.3.2 : CRAWLING
(FreeBSD)

–Sophie

Can you give some installation details: mysql vs postgresql and
which version number? Does anyone have postgresql working fast
yet?

My experience with RT 3.0.2 on PostgreSql 7.3.2 : CRAWLING
(FreeBSD) '
Have you tuned the PG buffers?

It flies for me on UnixWare.

LER

–Sophie


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

Have you read the FAQ? The RT FAQ Manager lives at http://fsck.com/rtfm


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

Have you read the FAQ? The RT FAQ Manager lives at http://fsck.com/rtfm

Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

±le 30/05/03 15:45 -0500, Larry Rosenman écrivait :
|
|| --On Friday, May 30, 2003 16:39:22 -0400 Sophie Rockhsar sophia@aps.org
| wrote:
|
|>> Can you give some installation details: mysql vs postgresql and
|>> which version number? Does anyone have postgresql working fast
|>> yet?
|>
|> My experience with RT 3.0.2 on PostgreSql 7.3.2 : CRAWLING
|> (FreeBSD) '
| Have you tuned the PG buffers?
|
| It flies for me on UnixWare.

how much tuned ?

Mathieu Arnold

±le 30/05/03 15:45 -0500, Larry Rosenman écrivait :
|
|

| wrote:
|
|>> Can you give some installation details: mysql vs postgresql and
|>> which version number? Does anyone have postgresql working fast
|>> yet?
|>
|> My experience with RT 3.0.2 on PostgreSql 7.3.2 : CRAWLING
|> (FreeBSD) '
| Have you tuned the PG buffers?
|
| It flies for me on UnixWare.

how much tuned ?

shared_buffers=8192

max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes
max_fsm_pages = 100000 # min 1000, fsm is free space map, ~6 bytes


Mathieu Arnold

Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Yes. And Mysql 4.0 is strongly recommended for RT3. For high loads,
RT 3.23 isn’t the right answer.

Yup, and I was using MySQL 4 and spent far too long with an unusable RT3. I
have for obvious operational reasons been forced to go back to RT2 and am
not overly keen on trying the experiment again because of all the hassle and
time it cost me last time.

(NB: that probably seemed harsh … wasn’t a dig or a flame, just a comment on
my experience … if it wasn’t so important, and I had more time to play,
wouldn’t have been such an issue).

The last time I spoke about this, I was discussing about the extraordinary
amount of ACL lookups that appeared to be causing the speed issue … has
anyone had any more experience of this ??

Cheers,

Simon.

Can you give some hard numbers on how slow RT3 gets ? While it isn’t
fast, I currently do not see that the database is the bottleneck.
But I only have about two hundred tickets in the database, maybe it is
a scaling problem.

Hard and fast numbers ?

Anything from about 1 to 5(+) minutes from clicking a link to having the
results displayed.

Watching MySQL logging as it goes through, it seems to be just ACL lookup after
ACL lookup until the page eventually displays.

I did however also wonder if it was a scaling problem, as my stats are (or
when I was testing RT3 were) in the region of:

Users: 12828
Groups: 170161
GroupMembers: 114993
CachedGroupMembers: 387317
Queues: 20
ACL: 201
Tickets: 39308
Transactions: 149330

Cheers,

Simon.

PS: As you will note, I have cc’d this back to the list as I think any
open discussion we can have on this in order to resolve the problem is
only going to benefit everyone.

Anything from about 1 to 5(+) minutes from clicking a link to having the
results displayed.

What kind of link ? View a single ticket ? Search tickets ?

Watching MySQL logging as it goes through, it seems to be just ACL lookup after
ACL lookup until the page eventually displays.

You could do thousands of ACL lookups per minute. Any rough number on how
many queries before the page displays ?

I found that a simple creation of a single ticket using the mailgate for an
existing user takes 113 SQL commands (21 * insert, 12 * update, 80 * select).
But that’s still only 2 seconds and about 30% of the CPU time.

Greetings,
,eM""=. a"-. Michael van Elst
dWWMWM" - :GM==; mlelstv@dev.de.cw.net
:WWMWMw=–. “W=’ cable & wireless
9WWMm==-.
”-Wmw-" CABLE & WIRELESS

From: Michael van Elst

You could do thousands of ACL lookups per minute. Any rough number on how
many queries before the page displays ?

I’ve seen MySQL (in other situations) become very slow if it must perform
a multi-table join as part of a query operation and the temporary table
exceeds the memory buffer making the subsequent sort happen in disk files.
Response can drop from seconds to minutes at that threshold.

Les Mikesell
les@futuresource.com

Can you give some hard numbers on how slow RT3 gets ? While it isn’t
fast, I currently do not see that the database is the bottleneck.
But I only have about two hundred tickets in the database, maybe it is
a scaling problem.

Hard and fast numbers ?

Anything from about 1 to 5(+) minutes from clicking a link to having the
results displayed.

Watching MySQL logging as it goes through, it seems to be just ACL lookup after
ACL lookup until the page eventually displays.

So. I believe that 3.0.3pre1 fixes this issue. I’d love to know if
that’s the case for you.

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