| Author |
 Topic  |
|
|
gpspassion
Junior Member
260 Posts |
 Posted - 08 March 2010 : 04:38:09
|
Been getting reports of timeout errors from people registering or changing their email in their profile :
"Timeout expired
/forumsen/pop_profile.asp"
Other than that the forums work fine * so I'm wondering if it might be due to the size of the members table ? We're approaching 500,000 members signed up since day one. I do run some scripts in the query analyzer on a monthly basis so the total number of active (been around in the past 3 months or who posted at least one message) accounts is around 100,000.
Is there any way this can be improved ? Thanks !
* except for the occasional unexplained slowdown that might occur when these timeouts do, now that I think of it. |
Edited by - gpspassion on 08 March 2010 04:52:01 |
|
|
HuwR
Forum Admin
United Kingdom
20600 Posts |
Posted - 08 March 2010 : 05:09:40
|
pop_profile should not really be affected by the number of members (if it is you probably need to look at upgrading hardware), a timeout when registering or changing email addresses may be related to the mail component and not the forum, are they able to change other values in their profile ? (that would rule out number of members as an issue)
Are you on shared hosting or dedicated server?
does SQL run on sam machine as website ?
|
 |
|
|
gpspassion
Junior Member
260 Posts |
Posted - 08 March 2010 : 14:15:33
|
Thanks for the quick reply. Yes separate machines with power to spare and that problem has been getting gradually worse with new members signing up, not with the load on the site.
The activation process (when people click on the link with the code) is really taking a very long time to execute itself, I suppose it looks up the code in a table to make sure it's valid ? Does it maybe scan the table starting at the beginning until it finds the code ? |
|
|
|
HuwR
Forum Admin
United Kingdom
20600 Posts |
Posted - 08 March 2010 : 15:55:33
|
| you could try adding an index on the activation key column (M_KEY) see if that helps |
|
|
|
gpspassion
Junior Member
260 Posts |
Posted - 09 March 2010 : 04:09:07
|
Thanks, any pointers on how to do that ? I looked around EM but I'm not sure how to proceed, would it start with "Full-Text Index Table" (from right-click on the Table) then "Define Full Text Indexing on the Table" ?
Come to think of it, pulling up the profile (pop_profile.asp?mode=display&id=xxxxxx) of a member by clicking on his UserID in a post has become rather slow too, about 6 seconds. |
|
|
|
HuwR
Forum Admin
United Kingdom
20600 Posts |
Posted - 09 March 2010 : 04:58:25
|
no, not full text indexing. you just need to add a normal index to the M_KEY column.
also, you say that just pulling up a normal member by id takes six seconds, how long does it take if you just do a select on FORUM_MEMBER where m_mame=xxxx directly in EM ? if it takes considerably less than 6 seconds then it is not your database that is the problem but could be network traffic between the web server and sql server or IIS/ASP that is slowing it down |
|
|
|
gpspassion
Junior Member
260 Posts |
Posted - 09 March 2010 : 13:03:17
|
Good idea for a test, well this time it took about 15 seconds in both cases clicking on the Username and using
SELECT *, M_NAME AS Expr1
FROM FORUM_MEMBERS
WHERE (M_NAME = N'XYZ') in EM.
Any idea how that could be improved, building an index on M_NAME ?
Is there some "caching" going on when your run a query because it seems that the second time around (hitting F5) it's quite a bit faster. |
Edited by - gpspassion on 09 March 2010 13:12:35 |
|
|
|
HuwR
Forum Admin
United Kingdom
20600 Posts |
Posted - 09 March 2010 : 13:35:11
|
yes it will have cached it.
sorry I should have told you to user WHERE MEMBER_ID = xxxxx as that is how the forum grabs a member, not by it's name |
|
|
|
HuwR
Forum Admin
United Kingdom
20600 Posts |
Posted - 09 March 2010 : 13:37:03
|
to create an index on M_KEY to see if that improves activation, run the following query in EM
CREATE INDEX FORUM_MEMBERS_MEMBER_KEY ON FORUM_MEMBERS(M_KEY) |
|
|
|
gpspassion
Junior Member
260 Posts |
Posted - 09 March 2010 : 15:21:27
|
Thanks ! Based on one email change, that seems to have worked wonders on the activation, it was instantaneous! I ran a CREATE INDEX FORUM_MEMBERS_MEMBER_NAME ON FORUM_MEMBERS(M_NAME) and now searching for a member using member_search.asp is much faster too. Should I run these queries regularly to keep things zippy ?
Clicking on the UserName to pull up .../pop_profile.asp?mode=display&id=xxxxxx remains slow though, any idea how that could be fixed ?
Update : a couple more tries and the search is slow again, the email change remains fast though, which is an improvement. The password change with the same type of activation though, stange!
When the forums slow down I notice thousands of "locks" in EM/Management/Current Activity. Locks in xxx.dbo.FORUM_REPLY go through the roof, 10713 right now and 17900 earlier on, with maybe only 4 or 5 "Process IDs". Can you make any sense of this ? |
Edited by - gpspassion on 09 March 2010 15:45:57 |
|
|
|
HuwR
Forum Admin
United Kingdom
20600 Posts |
|
|
HuwR
Forum Admin
United Kingdom
20600 Posts |
Posted - 09 March 2010 : 18:11:32
|
you could always try archiving some of your posts which would reduce the number of posts in the main topic/reply tables, that would aleviate some of your issues. although you have difficulty running the archiving.
Rui posted some scrips you can run in EM that would help with the archiving though |
|
|
| |
Topic |
|
Topic Locked
