We recently moved to a completely new moodle setup. We were running Moodle 1.5.x on a MS2003 server and MySQL. The new setup is Moodle 1.7 using Microsoft SQL Server. Both the database server (a 2 node cluster) and the web server are very lightly loaded but moodle is quite slow. My guess is that there are some database tweaks I can do to help things along but am uncertain where to start. There appear to be lots of info for tweaking MySQL, but can't find much about changes to MS SQL. One other bit is that the site is run entirely on HTTPS. The reason for this is that we have a PIX firewall that does HTTP traffic inspection. This inspection causes Word and Powerpoint uploads to moodle to fail. Rather than turn off the inspection (it can't be configured, as far as I can tell, to ignore certain sites or files at all. It's either on or it's off.) we made everything HTTPS so that the pix doesn't mess with it.
We noticed this slowness even before the site was made live so there would only be two people even hitting the site at all. Then it was REALLY bad, especially when loading the first page, but we found that hiding unused courses made it at least workable.
MSSQL server optimizations
Number of replies: 5
Re: MSSQL server optimizations
Aaron
Interesting post - let's focus on your PIX problems first 
.
I'm wondering what in your PIX config causes this failure of uploads. We have the moodle server running behind two PIXes without any issues at all. Can you possibly post back what that HTTP traffic setting is (if there is a security issue - then email me)? If you can get back to using http for normal work, then it may reduce the load on your server quite a lot and make the site more responsive for your users.
Thanks
Ken
Re: MSSQL server optimizations
It's the http inspection module. The configuration command to turn it on is simply "inspect http". The webserver is located at a remote hosting site. Our campus network and the servers at the remote site are connected via an IPSec VPN tunnel between 2 PIX's. Users on campus that did file uploads across the VPN had no trouble. It was only when they were accessing from off campus, through the internet, to the remote site's public IP (NAT'd by the PIX) that they would encounter this problem. If they navigated to the file upload page in moodle, then hand edited the URL to change http to https, it would work fine.
We had a similar problem with SMTP inspection on the pix munging up connections to the exchange server in a similar fashion. IT was a known issue and the only fix was to shut off the smtp inspection entirely.
We had a similar problem with SMTP inspection on the pix munging up connections to the exchange server in a similar fashion. IT was a known issue and the only fix was to shut off the smtp inspection entirely.
Re: MSSQL server optimizations
This sounds very Windows specific. May be the people at "Windows-based Servers" can help you http://moodle.org/mod/forum/view.php?id=6799
Re: MSSQL server optimizations
Hi Aaron
You're right about that inspect http command. From what I've read, there's a performance hit on the PIX and the overall suggestion is to turn it off and try reverting back to the fixup protocol http 80 command (which is what we have in our configs). There's due to be a patch from Cisco out for this in April, so wait until then. We're still on version 6.x of the PIX IOS, which explains why we're not experiencing any issues.
You're also right about the lack of specific Microsoft SQL Server notes in the performance docs. But that is probably because support for it is new - give it time and I'm sure more will be added .
Ken
Re: MSSQL server optimizations
I reposted this over on the Windows based servers to see if anyone has anything there.