| Author |
 Topic Search  Topic Options
|
godot 
Newbie
Joined: 19 June 2006
Location: Hong Kong
Status: Offline
Points: 7
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Database System Resource Overload
Posted: 19 June 2006 at 10:11am |
Hi,
My Forum has been running rather smoothly until I encountered this problem:
Server Error in Forum Application
An error has occured while executing SQL query on database.
Please contact the forum administrator.
Support Error Code:- err_Access_get_forum_data
File Name:- default.asp
Error details:-
Microsoft JET Database Engine
System resource exceeded.
I highlighted the problem to my service provider and the reply was:
This error might be caused by many reasons. Most of the time it is caused by a constantly opened connection to the database or an opened Recordset which consumes a lot of resources might generate this issue. Please try to go through your code to see if all the connections or recordsets are closed properly and the references to them have been set to Nothing after they become useless. Also, we can try to open the Recordset with less resource consuming cursor types. For example, open it with a dynamic cursor will be better.
We suspect one or more of the sites in the server are having this problem. We would appreciate your help by checking thru your scripts to minimize the problem. We will also investigate to pin-point other sites that are having this issue.
Can I ask if the code in version 8 is robust and does not have the issues raised by the service provider?
Thanks.
Edited by godot - 19 June 2006 at 10:12am
|
 |
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 19 June 2006 at 10:54am |
|
I can 100% confirm that the code in version 8 is totally robust and all recordsets and database connections are closed the the objects released as soon as they are no longer required.
Months of time was spent on version 8 optimising each and every database query to use less server resources and for performance, making Web Wiz Forums one of the most optimised and low resource bulletin board systems presently available.
Web Wiz Forums has even gone further and uses the web servers memory to cache common database queries to cut down on server and database resources. Array's are used to store database quires instead of recordsets improve performance further and cut down on server resources.
With the SQL Server version so called 'Dirty' database reads are performed whereby the database table is NOT locked during reads, and in most cases only row locks are used when the database is updated.
You can assure your web host that the code is very optimised so the fault would not be in the forum code.
If your forum gets allot of traffic and posts then it looks like your host needs to allocate more resources to the SQL server as bulletin board systems, by their very nature, do require allot of database reads.
The error you mention often happens if the SQL Server is running out of memory, your host maybe trying a slightly underhand tactic of blaming users code, rather than go to the expense and trouble of upgrading the SQL servers hardware. This is often the case with shared SQL Server environment were the host places as many databases on the 1 SQL server as possible to maximise revenue.
The amount of resources that Web Wiz Forums requires, the forum on this site can run on MSDE, with over 100,000 posts, 9,000 members, and 30 to 100 active users at all times. MSDE is a free cut down version of SQL Server that only allows 5 concurrent connections, and is further throttled. This really shows how low resources intensive the Web Wiz Forums optimised code really is.
Edited by -boRg- - 19 June 2006 at 10:56am
|
|
|
|
godot
Newbie
Joined: 19 June 2006
Location: Hong Kong
Status: Offline
Points: 7
|
Post Options
Thanks(0)
Quote Reply
Posted: 19 June 2006 at 12:44pm |
Thanks, boRg for the prompt reply. I suspected that it was not the fault of the code but something else.
I think the server is either unstable or it lacks of memory. All these point to the need to upgrade the servers as more databases are been added. The servers might not be able to handle the traffic it seems.
|
|
dpyers
Senior Member
Joined: 12 May 2003
Status: Offline
Points: 3937
|
Post Options
Thanks(0)
Quote Reply
Posted: 19 June 2006 at 7:07pm |
|
The causes raised by your host are valid for MS Access data bases... but it's a system-wide resource and the problem may be caused by some other site. As borg stated though, all db connections in the standard forum are closed and resources freed up.
If you have mods, you may want to check them though. I've seen some open connections left by mods. Otherwise, the problem is with some other site.
Bad MS access code is the biggest single cause of IIS instability.
|
Lead me not into temptation... I know the short cut, follow me.
|
|
SMR Group
Newbie
Joined: 05 June 2006
Status: Offline
Points: 31
|
Post Options
Thanks(0)
Quote Reply
Posted: 20 June 2006 at 5:00am |
Access is supposed to handle up to 255 concurrent connections, and that can mean a lot of users... provided not many rows are involved and everyone is using code that closes each session the moment it's not needed, as Web Wiz does. If you're using a shared Access database then a) the number of connections can quickly get soaked up and b) if somebody else's code "forgets" to close the query you're done for.
The Jet engine makes things worse as it hogs even more system resources. The service provider's explanation is valid, but relates to all their users.
Solution... switch to MySQL or similar, something that was actually designed to run on a server.
|
|
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 20 June 2006 at 12:32pm |
|
The 255 connections is Microsoft theoretical amount, in reality this is allot less and with a web server you usually go through the connection pool which only has 20 connections available for Access.
Access is also a desktop flat office file and not really up to anything in a web environment other then small database access very infrequently.
Microsoft have finally realised this, and this is why they have come up with their free SQL Server Express 2005, based on SQL Server architecture, but free, and you can attach SQL Servers MDF files to it making it as simple as Access to use.
However, as it is relatively new it may take web hosts sometime to catch onto this and offer it as an alternative option to Access.
|
|
|
|
godot
Newbie
Joined: 19 June 2006
Location: Hong Kong
Status: Offline
Points: 7
|
Post Options
Thanks(0)
Quote Reply
Posted: 23 June 2006 at 9:03am |
Thanks guys for the insight. My service provider has since opted to increase the virtual memory in the server.
I suppose since I'm on'shared-hosting', I will have to live with the possibility of other users with 'bad scripts' affecting my uptime.
Anyway, I'm wondering if migrating my files from the present Win 2000 server to Win 2003 will help boost performance. But I was warned that Win 2003 works very differently from that of Win 2000 - and they will not be in a position to help 'tweak' or configure the scripts if necessary. My questions are:
1) Will migrating my present Forum Ver 8 require any tedious tweaking and configuration? I hope there isn't a need to tweak anything actually.
2) Is it worthwhile, justifiable to migrate to the 2003 server?
Appreciate your views and advice.
Edited by godot - 23 June 2006 at 9:16am
|
|
WebWiz-Bruce
Admin Group
Web Wiz Developer
Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9844
|
Post Options
Thanks(0)
Quote Reply
Posted: 23 June 2006 at 11:55am |
If they are having to increase the virtual memory on the server it must be well overloaded as the virtual memory should only be used after the physical memory runs out. Windows 2003 server isn't that much different to Windows 2000 when it comes to running ASP and Web Wiz Forums runs exactly the same on Win 2003 as it does on Win 2000. The way IIS works on Windows 2003 is a vast improvement over Win 2000 and is allot more stable, with sites with bad scripting less likely to take down the whole web server. The performance is also better. The only tweaking you may need to do to Web Wiz Forums is updating the database path to the Access database if it is in a different location to the present server. Web Wiz Guide offer it's own hosting on Windows 2003 servers at, www.webwiz.net, and also offer mySQL databases for Web Wiz Forums, with Web Wiz Forums installed for you. We are planning on doubling the bandwidth on all accounts over the next month as well as offering mySQL across all plans as it is much better than Access which often courses issues.
|
|
|
|
