Community Task: OTRS Performance Improvement

English! place to talk about development, programming and coding
Post Reply
ferrosti
Znuny superhero
Posts: 723
Joined: 10 Oct 2007, 14:30
Znuny Version: 3.0
Location: Hamburg, Germany

Community Task: OTRS Performance Improvement

Post by ferrosti »

Dear folks,

with this post I am trying to gather a community to improve OTRSs performance for large environments. I do have plenty of ideas, some have already been realized, but I do not have that much time and ressources to manage it all on my own, especially when it comes to other environments.

On the one hand we have OTRSs backends as there are
- webserver
- database
- file system
- backup
On the other hand we have OTRS internal code that can be devided into
- perl code itself
- SQL code
These lead directly to OTRSs functionality
- fulltext search
- mail filters
- mail fetching itself

I am aware of GUI improvements, but there were too many discussions in the past (including an OTRS fork), which I´d like to leave out for a while. My idea is that this could come as extra package, whilst I´d like to see the results of the afore mentioned to become part of the standard distribution. Otherwise a rebellion might be scheduled later ;)

Being honestly I am a little selfish by saying that this project should start on the 3.0 version, since it will take lots of time and for the selfish part => I run these ones only.

My ideas ATM are to try to port Daniel Obee´s Booster to 3.0, use sphinx search for fulltext searches and improve OTRSs SQL code.

I´d like to get some input from all of you and summarize it later to have a direction to go to.

Have a nice weekend,

Chris
openSuSE on ESX
IT-Helpdesk: OTRS 3.0
Customer Service: OTRS 3.0 (upgraded from 2.3)
Customer Service (subsidiary): OTRS 3.0
+additional test and development systems
Daniel Obee
Moderator
Posts: 644
Joined: 19 Jun 2007, 17:11
Znuny Version: various
Real Name: Daniel Obée
Location: Berlin

Re: Community Task: OTRS Performance Improvement

Post by Daniel Obee »

Hi all,

yesterday I made a first code review of the 3.0.x to prepare a new version of the MySQL Booster (2.4.x version to find here). The new version will need much much less configuration by the user (none to be precise) and will be even less invasive. This is because OTRS 3.0 already laid the basis.

What has been done in 3.0.x:
  • In 3.0.x the xxx seemingly started to work the "LOWER" problem that puts the brakes on OTRS's database performance. Unfortunately they didn't go all the way so the major problems are still there.
  • (Even the 3.1.x is still lowering the customer_id in ticket searches. This is even more irritating as the ticket search was moved to a brand new TicketSearch.pm and it would have taken a single line of code to fix this. Won't go deeper because the 3.1 is out of scope for me at the moment.)
  • The DB drivers (Kernel/System/DB/mysql.pm etc.) contain the new attribute $Self->{'DB::CaseInsensitive'} to provide information if a database needs lowering is needed at all (MySQL doesn't).
  • Kernel/System/DB.pm fully supports the CaseInsensitive attribute to avoid lowering if unnecessary
  • Kernel/System/CustomerUser/DB.pm widely supports the CaseInsensitive attribute. CustomerUpdate unfortunately seem to simply have been forgotten so there's still room for a small improvement.
What's left:
The last MySQL Booster also included an enhanced Kernel/System/SearchProfile.pm - I guess we can skip this for lack of relevance. Besides the small but annoying flaw in the /CustomerUser/DB.pm... What about the ticket.pm?!? Within the ticket.pm is the main key to a better OTRS performance. I cannot but wonder why the core module of the system still does not support case insensitivity.

So the next weeks (days, if possible) I will try to come up with a better version of the ticket.pm. I will post it here for discussion and later put it into an opm package for easier deployment. I'd be happy to have some beta testers. Just drop me a line.

Happy spring time!
Dan
ferrosti
Znuny superhero
Posts: 723
Joined: 10 Oct 2007, 14:30
Znuny Version: 3.0
Location: Hamburg, Germany

Re: Community Task: OTRS Performance Improvement

Post by ferrosti »

Just in case it helps a little, I´ve made a file list, grepping for 'LOWER' in $OTRS_HOME/Kernel/.

Code: Select all

otrs30_develop:/opt/otrs/Kernel # find . -name "*" -type f|xargs grep -n LOWER
./Config/Defaults.pm:1347:            # CaseSensitive will control if the SQL statements need LOWER()
./System/Ticket/ArticleSearchIndex/RuntimeDB.pm:164:                $FullTextSQL .= " LOWER($Field) LIKE LOWER('$Value')";
./System/SystemAddress.pm:323:            . "AND LOWER(value0) = LOWER(?)",
./System/User/Preferences/DB.pm:113:        . " LOWER($Self->{PreferencesTableValue}) LIKE LOWER('"
./System/CustomerUser/Preferences/DB.pm:108:        . " LOWER($Self->{PreferencesTableValue}) LIKE LOWER('"
./System/CustomerUser/DB.pm:161:            $SQL .= "LOWER($Self->{CustomerKey}) = LOWER('$UserLogin')";
./System/CustomerUser/DB.pm:262:                    $SQLExt .= " LOWER($Field) LIKE LOWER('$PostMasterSearch') $LikeEscapeString ";
./System/CustomerUser/DB.pm:287:                $SQL .= "LOWER($Self->{CustomerKey}) LIKE LOWER('$UserLogin') $LikeEscapeString";
./System/CustomerUser/DB.pm:483:                .= "LOWER($Self->{CustomerKey}) = LOWER('" . $Self->{DBObject}->Quote($User) . "')";
./System/CustomerUser/DB.pm:749:        $SQL .= "LOWER($Self->{CustomerKey}) = LOWER('"
./System/CustomerUser/DB.pm:897:                $SQL .= "LOWER($Param{LoginCol}) = LOWER('"
./System/SearchProfile.pm:167:            . "search_profile WHERE profile_name = ? AND LOWER(login) = LOWER('$Login')",
./System/SearchProfile.pm:210:            . " profile_name = ? AND LOWER(login) = LOWER('$Login')",
./System/SearchProfile.pm:240:        SQL => "SELECT profile_name FROM search_profile WHERE LOWER(login) = LOWER('$Login')",
./System/Ticket.pm:4325:            $SQLExt .= 'LOWER(st.customer_id) IN (';
./System/Ticket.pm:4334:                $SQLExt .= "LOWER('" . $Self->{DBObject}->Quote($_) . "')";
./System/Ticket.pm:4545:            $SQLExt .= " AND LOWER(st.freekey$Number) LIKE LOWER('"
./System/Ticket.pm:4560:                    $SQLExtSub .= " LOWER(st.freekey$Number) LIKE LOWER('"
./System/Ticket.pm:4579:            $SQLExt .= " AND LOWER(st.freetext$Number) LIKE LOWER('"
./System/Ticket.pm:4595:                    $SQLExtSub .= " LOWER(st.freetext$Number) LIKE LOWER('"
./System/DB.pm:929:as for locator objects the functioning of SQL function LOWER() can't
./System/DB.pm:1084:                        $SQLA .= "LOWER($Key) $Type LOWER('$Word')";
./System/DB.pm:1116:                        $SQLA .= "LOWER($Key) $Type LOWER('$Word')";
./System/User.pm:195:        $SQL .= " LOWER($Self->{UserTableUser}) = ?";
./System/User.pm:373:            . " WHERE LOWER($Self->{UserTableUser}) = ?",
./System/User.pm:581:        $SQL .= " LOWER($Self->{UserTableUser}) LIKE LOWER('"
./System/User.pm:693:            . " WHERE LOWER($Self->{UserTableUser}) = ?",
./System/User.pm:738:                . " WHERE LOWER($Self->{UserTableUser}) = ?",
./System/CustomerCompany.pm:251:        $SQL .= "LOWER($Self->{CustomerCompanyKey}) = LOWER('"
./System/CustomerCompany.pm:255:        $SQL .= "LOWER($Self->{CustomerCompanyKey}) = LOWER('"
./System/CustomerCompany.pm:323:    $SQL .= " WHERE LOWER($Self->{CustomerCompanyKey}) = LOWER('"
./System/CustomerCompany.pm:419:                    $SQLExt .= " LOWER($_) LIKE LOWER('" . $Self->{DBObject}->Quote($Part) . "') ";
./System/CustomerCompany.pm:426:                $SQL .= " LOWER($Self->{CustomerCompanyKey}) LIKE LOWER('"
openSuSE on ESX
IT-Helpdesk: OTRS 3.0
Customer Service: OTRS 3.0 (upgraded from 2.3)
Customer Service (subsidiary): OTRS 3.0
+additional test and development systems
Daniel Obee
Moderator
Posts: 644
Joined: 19 Jun 2007, 17:11
Znuny Version: various
Real Name: Daniel Obée
Location: Berlin

Re: Community Task: OTRS Performance Improvement

Post by Daniel Obee »

I could see some of the modules being of some relevance, but the big shot for sure is the ticket.pm. Let's do that first and see if the rest is worth the effort at all. I'd put the relevance like that:

./System/Ticket.pm – major relevance, first shot
./System/CustomerUser/DB.pm – major relevance, but already 90% done (lowering just on CustomerUpdate)
./System/CustomerCompany.pm - hard to say since I don't use the feature

Worth a glance:
./System/User.pm
./System/User/Preferences/DB.pm
./System/SearchProfile.pm

Probably of minor relevance:
./System/CustomerUser/Preferences/DB.pm
./System/SystemAddress.pm

to be ignored:
./System/Ticket/ArticleSearchIndex/RuntimeDB.pm –should be Static anyway if performance is of interest
Daniel Obee
Moderator
Posts: 644
Joined: 19 Jun 2007, 17:11
Znuny Version: various
Real Name: Daniel Obée
Location: Berlin

Re: Community Task: OTRS Performance Improvement

Post by Daniel Obee »

Here comes the first strike. Worked my way through the ticket.pm to eliminate the lowering instances. The module is still downward compatible/compatible with other DBs since I put in a check for the driver:

(line 135 ff)

Code: Select all

	# remove LOWER if CaseInsensitive = 1 (dob)
	if ( !$Self->{DBObject}->GetDatabaseFunction('CaseInsensitive') ) {
		$Self->{BLow} = 'LOWER';
	}
For the rest of the module I just had to replace any occurrence of LOWER with $Self->{BLow}.

Also modified sub _TicketGetFirstResponse for a streamlined SQL - if you plan to add more external article types it might be better to leave the original code.

Please test! My testing options are way limited, so I beg you to put some of your time into testing if ever possible.

Greets
Dan
You do not have the required permissions to view the files attached to this post.
ferrosti
Znuny superhero
Posts: 723
Joined: 10 Oct 2007, 14:30
Znuny Version: 3.0
Location: Hamburg, Germany

Re: Community Task: OTRS Performance Improvement

Post by ferrosti »

UberBusy ATM, but I´ll come back to you.
Many thanks for your effort!
openSuSE on ESX
IT-Helpdesk: OTRS 3.0
Customer Service: OTRS 3.0 (upgraded from 2.3)
Customer Service (subsidiary): OTRS 3.0
+additional test and development systems
sectrix
Znuny newbie
Posts: 8
Joined: 25 Mar 2012, 17:38
Znuny Version: otrs3

Re: Community Task: OTRS Performance Improvement

Post by sectrix »

Try this:

ALTER TABLE otrs.dynamic_field_value ADD INDEX speed (field_id , object_id )
Post Reply