OTRS on IIS 7.5 + SQL 2008 (solution to performance issues)
Moderator: crythias
Forum rules
Dont create your support topics here! No new topics with questions allowed!
Dont create your support topics here! No new topics with questions allowed!
-
- 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)
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.
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.
-
- 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
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?
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?
-
- 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
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?
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?
-
- 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
http://www.perlmonks.org/bare/?node_id=659991
Can't test, though.
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} );
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
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
-
- 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
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):
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)
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?
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 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
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?
-
- 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
I typed too soon - I cannot reply to tickets now - get database errors.
I have reverted to the ODBC DSN for now.
I have reverted to the ODBC DSN for now.
-
- 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
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!
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!
-
- 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
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.
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
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
Re: OTRS on IIS 7.5 + SQL 2008 (solution to performance issues)
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.
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.
==== www.chess-rivals.com ====
Re: OTRS on IIS 7.5 + SQL 2008 (solution to performance issues)
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
"Testing": ((OTRS Community Edition)) and git Master
Never change Defaults.pm! :: Blog
Professional Services:: http://www.otrs.com :: enjoy@otrs.com
-
- 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)
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.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.