OTRS on IIS 7.5 + SQL 2008 (solution to performance issues)

Dont create your support topics here! No new topics with questions allowed!

Moderator: crythias

Forum rules
Dont create your support topics here! No new topics with questions allowed!
Post Reply
WarrenB
Znuny newbie
Posts: 9
Joined: 02 Feb 2013, 20:53
Znuny Version: OTRS 3.2.1
Real Name: Warren Bryington
Company: First Consulting Alliance

OTRS on IIS 7.5 + SQL 2008 (solution to performance issues)

Post by WarrenB »

We rolled out OTRS at our office in November 2012; and as a MS based company we decided to go the IIS + MS-SQL route.
Unfortunately performance was dismal... Clicking on anything took around 10 seconds to load.

We are running multiple OTRS instances on the same IIS instance (to different MS-SQL databases) but it wasn't even running smoothly with just a single instance up and running. I did not have time to investigate or find an alternative... so despite the speed issues we continued to use OTRS (as it met out base requirements) and was barely usable.

I recently upgraded succesfully from 3.1.19 to 3.2.1 (on Windows) and decided to spend some time getting the performance issues sorted out.
I have gotten much better performance now and will provide feedback below as to how I got it to run nicely on Windows with MS SQL.

Perl:

The WIKI article mentions that the 64bit version of ActivePerl (for windows) still does not contain PerlEX, but then doesn't advise on how to set everything up correctly. Unfortunately most of the articles online show how to setup with perl.exe not perlex.dll.... so are really slow in their implementations. And furthermore without PerlEX the platform is a very slow as each time anything runs a call to perl.exe is made - which the loads it into memory and processes the script.

I downloaded (and installed) the 32bit version of ActivePerl v5.14.3.1404 so that PerlEx.dll would be available.
Unfortunately on the newer versions of IIS there are still some manual processes that need to be run to setup everything to run on PerlEX.

Do not make the handlers to Executable: c:\Perl\bin\perl.exe "%s" %s - this is slow and inefficient!
This article http://blogs.iis.net/wadeh/archive/2009 ... iis-7.aspx shows how to setup correctly.
I also increased the "Maximum Worker Processes" in IIS for the AppPool to more than 1 so that it can scale better (ours is currently on 3.. but I will be testing different values)

This article http://www.ivoronline.com/Coding/Langua ... nually.php explains what PerlEx is and why this should be used instead of the .exe method.

Perl Module Updates
Once Perl is installed it needs to be updated to get the latest modules.
This can be done at the command prompt by running: ppm upgrade --install

MS SQL
If you read the article at http://www.martin-evans.me.uk/node/65 you will see that an ODBC conenction to MS SQL is slow.
Fortunately there is another way to connect to the database - using DBI:ADO.
This the line to add to the config.pm configuration file to use ADO:
$Self->{'DatabaseDSN'} = "DBI:ADO:Provider=sqloledb;Trusted Connection=yes;Server=$Self->{DatabaseHost},1433;database=$Self->{Database}";

I am still testing if everything is still working 100% but from my initial testing the performance is now great and everything appears to be fine with the changes that I have made.

Hopefully this post will be of use to someone else and the WIKI can be updated.
WarrenB
Znuny newbie
Posts: 9
Joined: 02 Feb 2013, 20:53
Znuny Version: OTRS 3.2.1
Real Name: Warren Bryington
Company: First Consulting Alliance

Re: OTRS on IIS 7.5 + SQL 2008 (solution to performance issu

Post by WarrenB »

Ok - Looks like ADO does not work well with unicode strings.
We have some tickets that include these that don't load anymore. :(

One was sent by a customer with the chacter é in their name.

If I change the DSN back to use DBI:ODBC then it works (but is slow again).
Does anyone have any thoughts on how this can be resolved?
WarrenB
Znuny newbie
Posts: 9
Joined: 02 Feb 2013, 20:53
Znuny Version: OTRS 3.2.1
Real Name: Warren Bryington
Company: First Consulting Alliance

Re: OTRS on IIS 7.5 + SQL 2008 (solution to performance issu

Post by WarrenB »

Looks like DBI:ADO has some issues with some characters.
We have some tickets that cannot load (customer names contain é).

If we change back to DBI:ODBC then the tickets load.

Does anyone have a suggestion of how to fix?
crythias
Moderator
Posts: 10169
Joined: 04 May 2010, 18:38
Znuny Version: 5.0.x
Location: SouthWest Florida, USA
Contact:

Re: OTRS on IIS 7.5 + SQL 2008 (solution to performance issu

Post by crythias »

http://www.perlmonks.org/bare/?node_id=659991

Code: Select all

# for ADO usage use DBI; 
use Win32::OLE; 
Win32::OLE->Option( CP => Win32::OLE::CP_UTF8 );

my $dbh = DBI->connect('dbi:ADO:Provider=Microsoft.Jet.OLEDB.4.0;Data +Source=some.mdb', '','', {LongReadLen=>100000} );
Can't test, though.
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
WarrenB
Znuny newbie
Posts: 9
Joined: 02 Feb 2013, 20:53
Znuny Version: OTRS 3.2.1
Real Name: Warren Bryington
Company: First Consulting Alliance

Re: OTRS on IIS 7.5 + SQL 2008 (solution to performance issu

Post by WarrenB »

Thanks for the this info - But that example DSN would be to connect to an Access database.

This is my connection DSN (SQL running on port 1433):

Code: Select all

$Self->{'DatabaseDSN'} = "DBI:ADO:Provider=sqloledb;driver={SQL Server Native Client 10.0};Server=$Self->{DatabaseHost},1433;database=$Self->{Database};";
The code does provide a few things to try but even after hard-coding directly into Kernel\System\DB.pm (and Kermal\System\DB\mssql.pm) there is no difference if
the Win32:OLE->Option is set or if the attributes for the connection are changed just to be LongReadLen = 10000.

I made the following change in Kernel\System\DB\mssql.pm (just to test a theory)

Code: Select all

# set encoding of selected data to utf8
$Self->{'DB::Encode'} = 0;  #this was originally 1
This has resulting is everything displaying correctly (and quickly) using the ADO DSN.
But I do not know what this setting changes (or rather what is now broken in the background) - Do you know what this setting changes?
WarrenB
Znuny newbie
Posts: 9
Joined: 02 Feb 2013, 20:53
Znuny Version: OTRS 3.2.1
Real Name: Warren Bryington
Company: First Consulting Alliance

Re: OTRS on IIS 7.5 + SQL 2008 (solution to performance issu

Post by WarrenB »

I typed too soon - I cannot reply to tickets now - get database errors.
I have reverted to the ODBC DSN for now. :(
WarrenB
Znuny newbie
Posts: 9
Joined: 02 Feb 2013, 20:53
Znuny Version: OTRS 3.2.1
Real Name: Warren Bryington
Company: First Consulting Alliance

Re: OTRS on IIS 7.5 + SQL 2008 (solution to performance issu

Post by WarrenB »

I gave up on getting this to work and eventually spent a few hours migrating from MSSQL to MySQL (and all the headache involved with it).
It was well worth the effort; Things that used to take around 10 sec are now a few ms.

My current setup is IIS7.5 + PerlEx + MySQL... and performance is excellent!
ferrosti
Znuny superhero
Posts: 723
Joined: 10 Oct 2007, 14:30
Znuny Version: 3.0
Location: Hamburg, Germany

Re: OTRS on IIS 7.5 + SQL 2008 (solution to performance issu

Post by ferrosti »

It seems like DBI and ADO do have different defaults for their connection character set.
In your case you should find out how to
a) convert your DB to UTF-8 as default
b) change your ADO connection string to UTF-8 These errors should be gone by then.

I have been working with ADO 2.5 like 12-13 years ago on mySQL Servers from Access / VBA Frontends. It worked like a charm and way better than ODBC or local connection to Access.

Just like all OTRS 3+ handbooks and whitepapers state: make sure everything is running with codepage UTF-8.
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
waleeed1
Znuny newbie
Posts: 1
Joined: 25 Feb 2016, 14:10
Znuny Version: adfaf

Re: OTRS on IIS 7.5 + SQL 2008 (solution to performance issues)

Post by waleeed1 »

I recently upgraded succesfully from 3.1.19 to 3.2.1 (on Windows) and decided to spend some time getting the performance issues sorted out.
I have gotten much better performance now and will provide feedback below as to how I got it to run nicely on Windows with MS SQL.
jojo
Znuny guru
Posts: 15019
Joined: 26 Jan 2007, 14:50
Znuny Version: Git Master
Contact:

Re: OTRS on IIS 7.5 + SQL 2008 (solution to performance issues)

Post by jojo »

OTRS 3.2 (and also 3.3.) are out support. Also windows (and MS SQL) are not supported any more starting with OTRS 5. I strongly advice to switch to Linux or any othe Unix based system.
"Production": OTRS™ 8, OTRS™ 7, STORM powered by OTRS
"Testing": ((OTRS Community Edition)) and git Master

Never change Defaults.pm! :: Blog
Professional Services:: http://www.otrs.com :: enjoy@otrs.com
PedFastTech
Znuny newbie
Posts: 7
Joined: 06 Jun 2012, 00:28
Znuny Version: 3.1.1

Re: OTRS on IIS 7.5 + SQL 2008 (solution to performance issues)

Post by PedFastTech »

waleeed1 wrote:I recently upgraded succesfully from 3.1.19 to 3.2.1 (on Windows) and decided to spend some time getting the performance issues sorted out.
I have gotten much better performance now and will provide feedback below as to how I got it to run nicely on Windows with MS SQL.
Please share! Some of us are Windows/MSSQL shops and will have to choose between sticking with OTRS 3.3.x and finding a ITSM solution that works on Windows and MSSQL.
Post Reply