While investigating why my ticket system was getting so slow, I found a
problem with the otrs-schema.postgresql.sql file.
It includes:
CREATE TABLE ticket
(
id serial,
However, that should almost certainly be a serial8:
CREATE TABLE ticket
(
id serial8,
... like the article table.
The way it is defined now as a "serial" makes it an integer instead of a
bigint. However, other fields in other tables that refer to it, such as
article.ticket_id, assume that ticket.id is a bigint.
This problem slows down OTRS a lot with PostgreSQL because some queries
that try to do joins between (e.g.) article.ticket_id and ticket.id can't
use an index, because the types differ.
For example, this is a common OTRS query used every time a queue is
viewed using the RuntimeDB Ticket Accelerator:
SELECT ar.id as ca, st.name, ti.id, ar.create_by, ti.create_time_unix,
ti.until_time, ts.name, tst.name
FROM ticket ti, article ar, article_sender_type st, ticket_state ts,
ticket_state_type tst
WHERE ti.ticket_lock_id not in ( 1, 3 )
AND ti.user_id = 2
AND ar.ticket_id = ti.id
AND st.id = ar.article_sender_type_id
AND ts.id = ti.ticket_state_id
AND ts.type_id = tst.id
ORDER BY ar.create_time DESC;
In testing on my system (about 40,000 tickets), that query takes 1944 ms
to execute, because this join between an integer and a bigint doesn't use
an index:
AND ar.ticket_id = ti.id
If ticket.id is a bigint, that same query takes only 44 ms -- a savings
of almost two full seconds every time the queue is viewed.
So, in the otrs-schema.postgresql.sql file, ticket.id needs to be changed
to a serial8. (In the MySQL version of the file, it's already a BIGINT,
so I'm fairly sure this is the correct thing to do with no nasty side
effects.) I have made this change on my system and everything seems to
work fine, but faster.
If other people suffer from this problem and are using PostgreSQL 7.4 or
later, this should allow you to change the column of your existing
database from an integer to a bigint even while it's live:
BEGIN;
LOCK TABLE ticket;
ALTER TABLE ticket ADD COLUMN new_col bigint;
UPDATE ticket SET new_col = id;
ALTER TABLE ticket DROP COLUMN id;
ALTER TABLE ticket RENAME COLUMN new_col TO id;
ALTER TABLE ticket ADD PRIMARY KEY (id);
ALTER TABLE ticket ALTER COLUMN id SET DEFAULT
nextval('"ticket_id_seq"'::text);
COMMIT;
ANALYZE;
(No guarantees, of course, but it worked for me.)
I have also discovered several other trivially fixable issues that
dramatically slow down OTRS with PostgreSQL (many of the SQL queries are
made in a way that prevents PostgreSQL from using bigint indexes); I'll
post more about them separately and try to provide a patch that fixes it
once I'm confident I've found them all.
--
Robert L Mathews, Tiger Technologies http://www.tigertech.net/
"Ignorance more frequently begets confidence than does knowledge."
-- Darwin
_______________________________________________
OTRS mailing list: dev - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/dev
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev
