For the record and the benefit of the community, I'm going to share
the admittedly less than optimal solution we eventually arrived at.
After finding out that the main cause of the slowdown was the result of
OTRS issuing SQL statements with a WHERE clause similar to
"LOWER(CustomerKey) LIKE LOWER('Search String')" we attempted to
configure OTRS to realize that the CustomerKey is numeric in all cases.
The results we got from that ranged from OTRS erroring out completely to
OTRS only retrieving part of the record in question. In the end, the
only way we were able to cause OTRS to not use the LOWER() LIKE LOWER()
SQL statements was to go into the perl modules and directly edit the
strings used to composite the SQL statements and remove the LOWER()
functions. The end result being that queries which formerly took 2-3
minutes (of which there were four in the process of creating a single
ticket) now took under five seconds. This solution will not work
generically as I doubt most sites can be certain of numeric keys.
This e-mail was just for your edification.
Robert McCarroll
Systems Administration
NYS Department of Civil Service
-----Original Message-----
From: McCarroll, Robert
Sent: Friday, May 21, 2010 7:32 AM
To: 'otrs@otrs.org'
Subject: Performance issue with multiple customer backends
First off, thanks to Phillippe Martignier for answering my previous
question.
Having configured OTRS to work with one Oracle backend, we recently
added a read-only view for the development version of our user database
which contains a modest number of test users (1.5 Million records) and
restricted the interface to searching only on Customer ID. However,
we've noted that requests for customer user data now take 1.5 minutes to
complete whereas a SQL statement for the same logical search ( SELECT *
FROM customer_user WHERE customer_id IS LIKE '*Search String Entered*';
) returns within a second of issuing the query.
While investigating this issue we determined that OTRS was nowhere
near taxing the available resources of the webserver, the network or the
database during this time. We also determined that it communicated with
the database server in short but dense bursts at intervals of ten to
fifteen seconds while processing the request. It does not appear to
matter which database the customer user ultimately gets found in, nor
how much history the user has. Our test have recorded the process as
taking anywhere from one to three minutes, averaging a minute and a
half.
Can anyone help me narrow down what might be causing this slowdown and
suggest a way to alleviate it?
Robert McCarroll
Systems Administration
NYS Department of Civil Service
---------------------------------------------------------------------
OTRS mailing list: otrs - Webpage: http://otrs.org/
Archive: http://lists.otrs.org/pipermail/otrs
To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs
NEW! ENTERPRISE SUBSCRIPTION - Get more information NOW!
http://www.otrs.com/en/support/enterprise-subscription/
