| Author |
 Topic  |
|
trevally
Starting Member
46 Posts |
 Posted - 10 March 2006 : 19:03:04
|
Hi all,
I have been running Snitz successfully since 2003 with an Access database. I migrated to MySQL yesterday and managed to import everything in correctly following the instructions in this forum. However, when I connect it and went live with it for the first time, the system was excruciatingly slow for the first few minutes. An hour later I received a message from the hosting company that the forum is taking up 50% of their CPU resources and they are shutting me down (shared hosting).
I have now reverted back to the Access connection again but I am still adamant on getting back to mysql.
For the connection string, I have even changed the Option to 16387 as per recommended to get the Type Mismatch problem gone.
Appreciate any pointers on this. What could cause this when everything as running smoothly in Access. The only recent mod I had in was the Photo Album last month but that was also working fine with Access.
Thank you from hot and tropical Malaysia.
Vincent
www.fishing.net.my/forum
Malaysian Fishing Net |
Edited by - trevally on 10 March 2006 19:05:07 |
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 10 March 2006 : 19:12:46
|
I am by no means an expert with MySQL, but did you let setup.asp create the tables? Just as a wild guess, I'd think improperly indexed tables would be near the top of my list of suspects...
but then again I'd also ask someone who knew a little more about MySQL. =/ |
 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 10 March 2006 : 19:31:11
|
| That's a bit weird situation. Is your database big? Did you create the tables properly, as AnonJr asked? What type of traffic do you have? How many users per day, how many concurrent users? |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
trevally
Starting Member
46 Posts |
Posted - 10 March 2006 : 19:47:50
|
The weird part is that when I ran setup.asp, snitz said everything a-ok.
Daily, I get an average of 800 to 900 visits on my server with a hit rate of 100,000 + and -.
My current Access database size is 200MB.
It's kinda baffling to me now. It's one of those type of problems that keeps you awake through the night and disrupts your shower time.
Vincent |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 10 March 2006 : 19:54:28
|
| Probably the reason lies with MySQL. Either it's not properly configured, of the server is underpowered or similar. How does Access behave? Does it cope with that traffic with low page generation times? |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
trevally
Starting Member
46 Posts |
Posted - 10 March 2006 : 21:19:47
|
I think this is what I plan to do from your tip (on improper index). Even though that the setup.asp said that the table is okay, I will create new tables and to the re-import to see how it affects the performance.
I will report this back again.
Thanks ruirib.
Regards,
Vincent |
|
|
|
Davio
Development Team Member
Jamaica
12217 Posts |
Posted - 10 March 2006 : 22:57:16
|
Not sure what you think the setup.asp file checks, before it tells you all is ok.
It only checks the presence of the FORUM_CONFIG table, as my memory recollects. So if you are thinking it checks each table and it's indeces, it doesn't.
What is suggested is that you allow setup.asp create the database tables, then you import the data into those tables. That's the safest way to avoid any problems with the forum and the database. |
Support Snitz Forums
|
|
|
|
modifichicci
Average Member
Italy
787 Posts |
|
|
trevally
Starting Member
46 Posts |
Posted - 11 March 2006 : 22:53:59
|
Ok thanks everyone for the tips. I have done whatever I could and have the forum running in the same server but in two separate folders. One is accessing the Access database (current) and the other is in Mysql under a development folder.
Access: www.fishing.net.my/forum/
Mysql: www.fishing.net.my/testsite0912/
I have also enabled the Timer function for both. Now, here's the interesting part :) -- in the Access forum the default page is generated in a split second but in the Mysql defaultp page it is generated around 20 secs.
HOWEVER, in the mysql forum when you enter any of the rooms, the pages are generated in seconds.
Why is it that only the default page in the Mysql database takes a longer time?
Intriguing. The web hosting side all is running in normal condition.
Regards,
Vincent
PS - Actually I have reading the snitz forum for years but never participated as I never had any problems. You guys are all fantastic in your response. I noticed also that you are all from countries which have qualified for the World Cup. Co-incidence huh.
|
Edited by - trevally on 12 March 2006 00:15:03 |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 12 March 2006 : 01:01:46
|
It's not unusual for Access to seem faster than mySQL, chances are your mySQL Server isn't running on the webserver, so it has to deal with local network traffic as well, Access on the other hand, only has to load the data from disk which is a lot faster than pulling it over the network.
MySQL, once it's got your queries/tables/indexes in it's cache though should perform pretty well.
Other factors could be the version of myODBC your host is running, and just how they've got the mySQL installation configured, ie caching on/off etc. It's extremely unusual though for mySQL to be taking 50% of CPU, even on very very busy mySQL boxes it's CPU usage normally seems to be extremely low usually.
I would expect though that you'll find your problem lies somewhere in your table indexes, or you have corrupt data eg duplicate reply or topic id's in your database somewhere. |
Kiwihosting.Net - The Forum Hosting Specialists
|
Edited by - Gremlin on 12 March 2006 01:02:51 |
|
|
|
trevally
Starting Member
46 Posts |
Posted - 12 March 2006 : 08:39:18
|
Dear all,
I have connection to Mysql live already to my website www.fishing.net.my/forum.
Yes, Gremlin, you're correct; the first time it's slow then after that it's quite okay.
I even tried connection to my Nokia 9300 and it worked fabulously.
This is a good experience for me. I think the setup.asp is quite deceptive in this case to say everything is okay.
I overcame this whole thing by having snitz create the table again in setup.asp even though it said all fine. Then used the proposed tools here to import it into mysql.
Thanks everyone. You are all very gracious in sharing knowledge.
So it is normal then to have the default.asp page to be slow then the pages inside faster?
Regards,
Vincent |
Edited by - trevally on 12 March 2006 10:36:35 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
modifichicci
Average Member
Italy
787 Posts |
|
|
trevally
Starting Member
46 Posts |
Posted - 12 March 2006 : 22:12:56
|
Actually I removed the Photo Album mod from the default.asp (the mod that shows the top 20 photos in thumbnail). That was causing the problem. The code that was causing the problem was emailed by my hosting company back to me. I don't know how to decipher it. This is from the mod. It was working fine in Access but somehow now it is not working:
Your MySQL connection id is 111 to server version: 4.1.16-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
mysql>
mysql> shoq processlist;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'shoq
processlist' at line 1
mysql> show processlist;
+-----+----------+----------------+-------+---------+------+--------------------
--+-----------------------------------------------------------------------------
-------------------------+
| Id | User | Host | db | Command | Time | State
| Info
|
+-----+----------+----------------+-------+---------+------+--------------------
--+-----------------------------------------------------------------------------
-------------------------+
| 70 | trevally | localhost:2250 | snitz | Query | 29 | Sorting result
| SELECT M.M_NAME, M.M_RECEIVE_EMAIL, M.M_AIM, M.M_ICQ, M.M_MSN, M.M_YAHOO, M.
M_TITLE, M.MEMBER_ID, M. |
| 75 | trevally | localhost:2287 | snitz | Query | 28 | Copying to tmp tabl
e | SELECT FA1.Photo_id,FA1.Photo_Name,FAU1.Member_id,FAU1.M_NAME FROM FORUM_ALB
UM_USERS AS FAU1 INNER J |
| 77 | trevally | localhost:2292 | snitz | Query | 139 | Copying to tmp tabl
e | SELECT FA1.Photo_id,FA1.Photo_Name,FAU1.Member_id,FAU1.M_NAME FROM FORUM_ALB
UM_USERS AS FAU1 INNER J |
| 82 | trevally | localhost:2310 | snitz | Query | 81 | Copying to tmp tabl
e | SELECT FA1.Photo_id,FA1.Photo_Name,FAU1.Member_id,FAU1.M_NAME FROM FORUM_ALB
UM_USERS AS FAU1 INNER J |
| 83 | trevally | localhost:2311 | snitz | Query | 28 | Copying to tmp tabl
e | SELECT FA1.Photo_id,FA1.Photo_Name,FAU1.Member_id,FAU1.M_NAME FROM FORUM_ALB
UM_USERS AS FAU1 INNER J |
| 85 | trevally | localhost:2313 | snitz | Query | 151 | Copying to tmp tabl
e | SELECT FA1.Photo_id,FA1.Photo_Name,FAU1.Member_id,FAU1.M_NAME FROM FORUM_ALB
UM_USERS AS FAU1 INNER J |
| 84 | trevally | localhost:2314 | snitz | Query | 87 | Copying to tmp tabl
e | SELECT FA1.Photo_id,FA1.Photo_Name,FAU1.Member_id,FAU1.M_NAME FROM FORUM_ALB
UM_USERS AS FAU1 INNER J |
| 86 | trevally | localhost:2315 | snitz | Query | 7 | freeing items
| SELECT FORUM_ID FROM FORUM_ALLOWED_MEMBERS WHERE MEMBER_ID = 1000
|
| 95 | trevally | localhost:2343 | snitz | Query | 19 | Copying to tmp tabl
e | SELECT FA1.Photo_id,FA1.Photo_Name,FAU1.Member_id,FAU1.M_NAME FROM FORUM_ALB
UM_USERS AS FAU1 INNER J |
| 94 | trevally | localhost:2342 | snitz | Query | 142 | Copying to tmp tabl
e | SELECT FA1.Photo_id,FA1.Photo_Name,FAU1.Member_id,FAU1.M_NAME FROM FORUM_ALB
UM_USERS AS FAU1 INNER J |
| 100 | trevally | localhost:2355 | snitz | Sleep | 0 |
| NULL
|
| 107 | trevally | localhost:2378 | snitz | Query | 29 | Sorting result
| SELECT M.M_NAME, M.M_RECEIVE_EMAIL, M.M_AIM, M.M_ICQ, M.M_MSN, M.M_YAHOO, M.
M_TITLE, M.MEMBER_ID, M. |
| 108 | trevally | localhost:2381 | snitz | Query | 3 | Sending data
| SELECT M.M_NAME, M.M_RECEIVE_EMAIL, M.M_AIM, M.M_ICQ, M.M_MSN, M.M_YAHOO, M.
M_TITLE, M.M_HOMEPAGE, M |
| 112 | trevally | localhost:2387 | snitz | Query | 16 | Copying to tmp tabl
e | SELECT FA1.Photo_id,FA1.Photo_Name,FAU1.Member_id,FAU1.M_NAME FROM FORUM_ALB
UM_USERS AS FAU1 INNER J |
| 113 | trevally | localhost:2399 | snitz | Sleep | 1 |
| NULL
|
| 114 | trevally | localhost:2398 | snitz | Sleep | 0 |
| NULL
|
+-----+----------+----------------+-------+---------+------+--------------------
--+-----------------------------------------------------------------------------
-------------------------+
17 rows in set (0.00 sec)
mysql> show processlist;
+-----+----------------------+-------------------+-----------+---------+------+----------------------+--------------------------------------------------------------------------------------------------
----+
| Id | User | Host | db | Command | Time | State | Info
|
+-----+----------------------+-------------------+-----------+---------+------+----------------------+--------------------------------------------------------------------------------------------------
----+
| 75 | trevally | localhost:2287 | snitz | Query | 124 | Sorting result | SELECT FA1.Photo_id,FA1.Photo_Name,FAU1.Member_id,FAU1.M_NAME FROM FORUM_ALBUM_USERS AS FAU1 INNE
R J |
| 77 | trevally | localhost:2292 | snitz | Query | 19 | Sorting result | SELECT M.M_NAME, M.M_RECEIVE_EMAIL, M.M_AIM, M.M_ICQ, M.M_MSN, M.M_YAHOO, M.M_TITLE, M.MEMBER_ID,
M. |
| 82 | trevally | localhost:2310 | snitz | Query | 14 | Sorting result | SELECT FA1.Photo_id,FA1.Photo_Name,FAU1.Member_id,FAU1.M_NAME FROM FORUM_ALBUM_USERS AS FAU1 INNE
R J |
| 83 | trevally | localhost:2311 | snitz | Query | 124 | Copying to tmp table | SELECT FA1.Photo_id,FA1.Photo_Name,FAU1.Member_id,FAU1.M_NAME FROM FORUM_ALBUM_USERS AS FAU1 INNE
R J |
| 85 | trevally | localhost:2313 | snitz | Query | 13 | Sorting result | SELECT FA1.Photo_id,FA1.Photo_Name,FAU1.Member_id,FAU1.M_NAME FROM FORUM_ALBUM_USERS AS FAU1 INNE
R J |
| 84 | trevally | localhost:2314 | snitz | Query | 15 | Sorting result | SELECT M.M_NAME, M.M_RECEIVE_EMAIL, M.M_AIM, M.M_ICQ, M.M_MSN, M.M_YAHOO, M.M_TITLE, M.MEMBER_ID,
M. |
| 86 | trevally | localhost:2315 | snitz | Query | 83 | Copying to tmp table | SELECT FA1.Photo_id,FA1.Photo_Name,FAU1.Member_id,FAU1.M_NAME FROM FORUM_ALBUM_USERS AS FAU1 INNE
R J |
| 95 | trevally | localhost:2343 | snitz | Query | 115 | Copying to tmp table | SELECT FA1.Photo_id,FA1.Photo_Name,FAU1.Member_id,FAU1.M_NAME FROM FORUM_ALBUM_USERS AS FAU1 INNE
R J |
| 94 | trevally | localhost:2342 | snitz | Sleep | 26 | | NULL
|
| 100 | trevally | localhost:2355 | snitz | Query | 74 | Sorting result | SELECT FA1.Photo_id,FA1.Photo_Name,FAU1.Member_id,FAU1.M_NAME FROM FORUM_ALBUM_USERS AS FAU1 INNE
R J |
| 107 | trevally | localhost:2378 | snitz | Query | 19 | Sorting result | SELECT M.M_NAME, M.M_RECEIVE_EMAIL, M.M_AIM, M.M_ICQ, M.M_MSN, M.M_YAHOO, M.M_TITLE, M.MEMBER_ID,
M. |
| 108 | trevally | localhost:2381 | snitz | Query | 2 | freeing items | SELECT MEMBER_ID FROM FORUM_ALBUM_USERS WHERE M_Name = 'Gekko'
|
| 112 | trevally | localhost:2387 | snitz | Query | 112 | Copying to tmp table | SELECT FA1.Photo_id,FA1.Photo_Name,FAU1.Member_id,FAU1.M_NAME FROM FORUM_ALBUM_USERS AS FAU1 INNE
R J |
| 113 | trevally | localhost:2399 | snitz | Query | 31 | Copying to tmp table | SELECT FA1.Photo_id,FA1.Photo_Name,FAU1.Member_id,FAU1.M_NAME FROM FORUM_ALBUM_USERS AS FAU1 INNE
R J |
| 114 | trevally | localhost:2398 | snitz | Query | 2 | freeing items | SELECT MEMBER_ID FROM FORUM_ALBUM_USERS WHERE M_Name = 'thomas lee'
|
| 116 | trevally | localhost:2401 | snitz | Query | 19 | Sorting result | SELECT M.M_NAME, M.M_RECEIVE_EMAIL, M.M_AIM, M.M_ICQ, M.M_MSN, M.M_YAHOO, M.M_TITLE, M.MEMBER_ID,
M. |
| 115 | trevally | localhost:2400 | snitz | Query | 16 | Sorting result | SELECT M.M_NAME, M.M_RECEIVE_EMAIL, M.M_AIM, M.M_ICQ, M.M_MSN, M.M_YAHOO, M.M_TITLE, M.MEMBER_ID,
M. |
| 117 | trevally | localhost:2402 | snitz | Query | 29 | Copying to tmp table | SELECT FA1.Photo_id,FA1.Photo_Name,FAU1.Member_id,FAU1.M_NAME FROM FORUM_ALBUM_USERS AS FAU1 INNE
R J |
| 119 | trevally | localhost:2403 | snitz | Sleep | 56 | | NULL
|
| 120 | trevally | localhost:2404 | snitz | Query | 16 | Sorting result | SELECT M.M_NAME, M.M_RECEIVE_EMAIL, M.M_AIM, M.M_ICQ, M.M_MSN, M.M_YAHOO, M.M_TITLE, M.MEMBER_ID,
M. |
|
+-----+----------------------+-------------------+-----------+---------+------+----------------------+--------------------------------------------------------------------------------------------------
----+
27 rows in set (0.00 sec)
mysql> quit
Bye
C:\mysql\bin>
|
Edited by - trevally on 13 March 2006 08:25:35 |
|
|
|
trevally
Starting Member
46 Posts |
Posted - 15 March 2006 : 02:48:51
|
Hi everyone,
The hosting company still pointed out that the Snitz forum still takes up resources and have also gave me a screenshot of where they guess is the problem:
They sent me this email:
Dear Vincent,
May I know does your mysql query in your script do close or have close on the connection or query?
It is because based on the mysql query, it seem that it keeps on holding the session whereby it ask for the username and password from the database.
Can you please check?
If you have any enquiries, please do not hesitate to contact. Thank you.
Best Regards,
I am just a fisherman, not a developer so I am not sure what it means. I am more like a cut-and-paste guy. Appreciate any ideas on this.
Thank you so much.
Regards,
Vincent |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Topic |
|
|
|
Topic Locked
