| Author |
 Topic  |
|
|
jwashburn
Starting Member
28 Posts |
 Posted - 14 March 2006 : 12:47:44
|
| I had a 20+gb access db, that I just moved to mysql. I didnt have any problems that I noticed, it all went smooth. I did this because my site was going very slow. I was having my host reset the appliction pool once a week. Now that I have moved it seems to be worse. I have been told that my domain has been moved into its own application pool, but still slow. Once the refresh it its fine for a while. I have asked for the Event logs so I can research the issue, but they wont give those up, so any other ideas? |
 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
jwashburn
Starting Member
28 Posts |
Posted - 14 March 2006 : 17:10:36
|
Good question. I dont remember now. I think I did, because I remember when I tried to restore the databse it gave me an error on the create table portion of the script becuase the table were already there.
Is there something different with the way the work |
|
 |
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 14 March 2006 : 19:29:05
|
When you let setup.asp create the table it creates all the indexes too. Without a properly indexed database you'll see a drop in performance.
Might be worth looking at again. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
jwashburn
Starting Member
28 Posts |
Posted - 15 March 2006 : 10:02:51
|
| Is there a way to reindex? |
|
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 15 March 2006 : 15:30:50
|
Check your tables for duplicate records, in particular the archive tables which are a little prone to getting duplicates (the first Snitz version that included the archive mod if I recall correctly never included indexes on those tables).
Also check that there indexs present on those tables and if not, then I'd suggest creating them.
You can create indexes using something like phpmyadmin which your host probably provides, or you can just install a copy under your domain. Those two tables should have the following indexes:
FORUM_A_REPLY
PRIMARY KEY (CAT_ID, FORUM_ID, TOPIC_ID, REPLY_ID),
KEY FORUM_A_REPLY_CATFORTOPREPL(CAT_ID,FORUM_ID,TOPIC_ID, REPLY_ID),
KEY FORUM_A_REPLY_REP_ID(REPLY_ID),
KEY FORUM_A_REPLY_CAT_ID(CAT_ID),
KEY FORUM_A_REPLY_FORUM_ID(FORUM_ID),
KEY FORUM_A_REPLY_TOPIC_ID (TOPIC_ID)
FORUM_A_TOPICS
PRIMARY KEY (CAT_ID, FORUM_ID, TOPIC_ID),
KEY FORUM_A_TOPIC_CATFORTOP(CAT_ID,FORUM_ID,TOPIC_ID),
KEY FORUM_A_TOPIC_CAT_ID(CAT_ID),
KEY FORUM_A_TOPIC_FORUM_ID(FORUM_ID),
KEY FORUM_A_TOPIC_TOPIC_ID (TOPIC_ID)
|
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
jwashburn
Starting Member
28 Posts |
Posted - 15 March 2006 : 16:05:59
|
So I should index the FORUM_A_REPLY, the FORUM_A_TOPICS. What other ones should be indexed. I have never archived.
What should I index off of? |
|
Edited by - jwashburn on 15 March 2006 16:06:58 |
|
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 15 March 2006 : 16:58:48
|
If you've never archived, then those tables should be empty and not an issue.
Do you understand how to create and manage indexes on your database? If not, then it could be a good idea to let someone take a look at it for you, as you can do quite a bit of damage if your unsure what to do.
I note you say your database is 20+GB ? gigabyte? really? By default application pools will reset every 29 (from memory) hours, I'd perhaps be suspecting the slowness was/is a problem on the host side of things rather than Snitz as well. Having said that, running MS Access databases on Server 2003 installations in my experience has been very promlematic especially once you get several busy sites running on them (Microsoft actually have refused to assist helping with several issues I've had stating that access isn't "supported" in that environment). |
Kiwihosting.Net - The Forum Hosting Specialists
|
|
|
|
modifichicci
Average Member
Italy
787 Posts |
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 16 March 2006 : 16:14:11
|
It happens becuase you've got corrupt records with duplicate ID's in them, this is becuase of the lack of indexing originally on this table meaning there was no enforced integrity. If you archive posts and say it fails, then you run it again you often end up duplicating one or more rows. You can find the duplicates with a simple query using the GROUP BY function e.g.
SELECT REPLY_ID, COUNT(REPLY_ID)
FROM FORUM_A_REPLY
GROUP BY REPLY_ID
HAVING (COUNT(REPLY_ID) > 1 )
|
Kiwihosting.Net - The Forum Hosting Specialists
|
Edited by - Gremlin on 16 March 2006 16:14:44 |
|
|
|
jwashburn
Starting Member
28 Posts |
Posted - 17 March 2006 : 19:09:51
|
I have a friend who is pretty well versed in mysql, ill have him take a look.
I know there must be something wrong, because I tried to a simpe search and it just hangs. That tells me some index is screwy |
|
|
|
|
jwashburn
Starting Member
28 Posts |
Posted - 22 March 2006 : 12:19:23
|
| Just in case someone else screws this up like I did. I created a new mysql db and set up another copy of snitz and created all the tables, then I used dbtools to look at the index's and recreated them on my production forum and all was well. |
|
|
|
| |
Topic |
|
Topic Locked
