| Author |
 Topic  |
|
trevally
Starting Member
46 Posts |
 Posted - 15 March 2006 : 09:25:42
|
The mods I have is the photo album and a chat. I have disconnected the chat today and this thing still happens. I have also discon the photo album mod from showing the top 20 thumbnails in the default.asp page but the high resources still happen.
Any clues I can look for?
Thanks,
Vincent |
 |
|
|
Davio
Development Team Member
Jamaica
12217 Posts |
Posted - 15 March 2006 : 11:42:32
|
| I know I don't have 20/20 vision, maybe close to it as I eat my carrots when possible, but I can't see any information in that screen shot. Got a bigger one? |
Support Snitz Forums
|
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 15 March 2006 : 15:18:09
|
It's the second query down causing the problem, it times out and then locks the DB preventing further queries from executing and eventually just causes things to start failing.
SELECT COUNT( DISTINCT T.TOPIC_ID ) AS PAGECOUNT FROM ((((FORUM_FORUM F LEFT JOIN FORUM_A_TOPICS T ON .........................
It looks identical to the issue here http://forum.snitz.com/forum/topic.asp?TOPIC_ID=59388 which was eventually (touch wood) resolved by rebuilding all the indexes (not too sure this really helped at all) but primarly resolved I believe by locating some topics in the FORUM_A_REPLY table with duplicate REPLY_ID's Since removing the duplicate rows as far as I know the issue reported in that post appears to have been resolved.
|
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
trevally
Starting Member
46 Posts |
Posted - 17 March 2006 : 03:00:06
|
Dear Gremlin,
Thanks for your reply. I received another email from the hosting technical manager just now. I am not sure this new clue from them will help. I am still baffled.
Dear Sir,
May I know have you request your programmer to have a check on your forum sql query?
Kindly be informed that I have monitor on the server for several days and I realise that your forum do use a lot of our server resources.
For this moment, your website forum is the only person who fully utilised the mysql and the exccessive usage on the mysql(fishing75.JPG) causing our server load to be unstable. If we check on the server performance graph, the server load is always in the busy mode and everytime the load is more than 50% and above.
If you check on the server CPU and memory usage provide, you can see that the highest usage in resources is mysql and a process wpw3.exe(PID:440). That particular process ID(fishing72.JPG) is refer to your own application pool 440 and it is fully utilised by your database.
As conclusion on this matter, our whole server CPU resources and memory resources is fully utilised by your hosting account in shared hosting server where by the resources should be shared among our customer.
I believe you may need to take some action on this matter such as do some amendment on your script, or find other open sources forum where it able to handle your current user and at the same time, does not over use the server resources.
Please note that this server is as below:
Model: Dell PowerEdge SC1425
CPU(s): Intel Xeon 3.20Ghz / 800FSB / 1MB Cache
Memory: 1024MB ECC/Registered DDR2
Your co-operation on this matter is greatly appreciated by me and our company staff.
Thank you.
If you have any enquiries, please do not hesitate to contact. Thank you.
Best Regards,
Adeline
Screenshots sent by hosting company:
Does this clue help?
Really appreciate any pointers on this.
Thank you so much.
Regards
Vincent |
|
|
|
trevally
Starting Member
46 Posts |
Posted - 17 March 2006 : 03:17:49
|
Dear Gremlin,
I managed to remove the duplicate Reply_IDs. I just deleted off my archived stuff older than 4 months and it's gone from the forum_a_reply.
So for now the main forum seems to be okay but then another glitch; whenever a user clicks on a member's Profile to view, it will take more than a minute for the page to be generated (as shown in the timer). I asked for the hosting company to test it and send me the log. This is what they got for me:
mysql> show processlist;
+-------+----------+----------------+-------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+----------+----------------+-------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 44593 | angler | localhost:2070 | snitz | Query | 82 | Locked | UPDATE FORUM_TOPICS SET T_VIEW_COUNT = (T_VIEW_COUNT + 1) WHERE (TOPIC_ID = 5732) |
| 44653 | angler | localhost:2497 | snitz | Query | 183 | Copying to tmp table | SELECT F.FORUM_ID, T.TOPIC_ID, T.T_SUBJECT, T.T_STATUS, T.T_LAST_POST, T.T_REPLIES FROM ((FORUM_FOR |
| 44659 | angler | localhost:2520 | snitz | Query | 81 | Locked | SELECT F.FORUM_ID, F.F_STATUS, F.CAT_ID, F.F_SUBJECT, F.F_URL, F.F_TOPICS, F.F_COUNT, F.F_LAST_POST, |
| 44685 | angler | localhost:2700 | snitz | Query | 38 | Locked | SELECT F.FORUM_ID, F.F_STATUS, F.CAT_ID, F.F_SUBJECT, F.F_URL, F.F_TOPICS, F.F_COUNT, F.F_LAST_POST, |
| 44686 | angler | localhost:2708 | snitz | Query | 82 | Locked | SELECT F.FORUM_ID, F.F_STATUS, F.CAT_ID, F.F_SUBJECT, F.F_URL, F.F_TOPICS, F.F_COUNT, F.F_LAST_POST, |
| 44687 | angler | localhost:2716 | snitz | Query | 33 | Locked | SELECT FORUM_ID, T_SUBJECT FROM FORUM_TOPICS WHERE TOPIC_ID=5871 |
| 44688 | angler | localhost:2718 | snitz | Query | 81 | Locked | SELECT F.FORUM_ID, F.F_STATUS, F.CAT_ID, F.F_SUBJECT, F.F_URL, F.F_TOPICS, F.F_COUNT, F.F_LAST_POST, |
| 44689 | angler | localhost:2719 | snitz | Query | 82 | Locked | SELECT F.FORUM_ID, F.F_SUBJECT, F.F_SUBSCRIPTION, F.F_STATUS, C.CAT_ID, C.CAT_NAME, C.CAT_SUBSCRIPTI |
| 44691 | angler | localhost:2737 | snitz | Query | 82 | Locked | SELECT T_SUBJECT, TOPIC_ID FROM FORUM_TOPICS WHERE T_LAST_POST > '20060317170927' AND FORUM_ID = 4 A |
| 44694 | angler | localhost:2760 | snitz | Query | 82 | Locked | SELECT T_SUBJECT, TOPIC_ID FROM FORUM_TOPICS WHERE T_LAST_POST > '20060317213159' AND FORUM_ID = 5 A |
| 44699 | angler | localhost:2801 | snitz | Query | 82 | Locked | SELECT T_SUBJECT, TOPIC_ID FROM FORUM_TOPICS WHERE T_LAST_POST > '20060214081039' AND FORUM_ID = 14 |
| 44709 | angler | localhost:2935 | snitz | Query | 13 | Locked | SELECT COUNT(TOPIC_ID) AS PAGECOUNT FROM FORUM_MEMBERS M, FORUM_TOPICS T, FORUM_MEMBERS AS MEMBERS_ |
| 44710 | angler | localhost:2936 | snitz | Query | 12 | Locked | UPDATE FORUM_MEMBERS SET M_LASTHEREDATE = '20060317235351', M_LAST_IP = '219.95.12.162' WHERE M_ |
| 44712 | angler | localhost:2952 | snitz | Query | 4 | Locked | SELECT MEMBER_ID, M_LEVEL, M_NAME, M_PASSWORD FROM FORUM_MEMBERS WHERE M_NAME = 'LaPala' AND M_PA |
| 44713 | angler | localhost:2953 | snitz | Query | 3 | Locked | SELECT MO.FORUM_ID, ME.MEMBER_ID, ME.M_NAME FROM FORUM_MODERATOR MO , FORUM_MEMBERS ME WHERE (MO.ME |
+-------+----------+----------------+-------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
20 rows in set (0.00 sec)
mysql>
Is there more duplicate records that I should look for?
Thank you all for your advise.
Thanks,
Vincent |
Edited by - trevally on 17 March 2006 11:22:52 |
|
|
|
modifichicci
Average Member
Italy
787 Posts |
|
|
trevally
Starting Member
46 Posts |
Posted - 17 March 2006 : 19:00:05
|
Dear modifichicci,
Yes, you're right! I noticed when I checked my duplicate record in forum_a_reply table, there were reply_id with null. Does it mean in all the tables inside the database I should replace the null with 1 or 0, or is there a specific table you saw from those logs that was pasted I should go to?
I think I am getting closer to solving this...
Thank you
Vincent |
|
|
|
modifichicci
Average Member
Italy
787 Posts |
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 18 March 2006 : 11:36:32
|
| Ditto. This does sound a lot like problems with indexes... or a lack thereof. |
|
|
|
trevally
Starting Member
46 Posts |
Posted - 18 March 2006 : 20:51:23
|
I know I am getting closer to solving this problem. I have actually recreated the database twice with setup.asp and reimported back the records.
Yesterday night I downloaded the table structure from www.ruirib.net/ds.sql.txt to ensure I have no more Null values in wrong locations. I also ensure that I have the Primary Keys necessary.
Well, the main board went very well but then when you click on the the name of a member to pop-up his profile, the problem resurfaced; it takes up to 90 seconds for it to display before it will timeout, and now that causes a problem to the server.
I know I am very near to solving this...I have not ripped any of my hair out yet....
Thanks,
Vincent
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 18 March 2006 : 22:38:42
|
| Just in case I ever move to MySQL, what is it that leads you to question the reliability of their server? |
|
|
|
trevally
Starting Member
46 Posts |
Posted - 19 March 2006 : 00:19:23
|
Hi Ruirib,
Thanks for your help. I will try your suggested code and report back to you.
Cheers,
Vincent
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 19 March 2006 : 06:51:48
|
I find it hard to accept that a newly created MySQL DB locks up when you try to access member info. Never saw anything like that and it makes me suspicious. I am a pessimist by nature  . |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Topic |
|
Topic Locked
