This project has moved. For the latest updates, please go here.

Db issue... need some help.

Jul 8, 2010 at 6:39 PM
Hi - I'm currently running my photography business website ( http://temptingweddings.com/ ) on graffiti 1.2. When I started it I had it running on Vista db. It seems to have had the problem where the db starts growing out of control. A few months back I shifted the website to MSSQL 2005 and the db went down a little in size (maybe 20%) but it has continued to grow. I looked at the db and it looks like 80% of the data is in the versionStore. I am a web designer by trade, but not a db engineer so my understanding of what's going on is limited. From what I understand versionStore is just where the db keeps a record of what has changed. My ISP offered to help me fix it but they said I'd have to figure out what to do and they could implement it. So, If I want to reduce the size of this db AND prevent it from growing out of control in the future, what do I tell them as far as what to do? thanks! ken
Coordinator
Jul 11, 2010 at 4:28 AM
Edited Jul 11, 2010 at 4:29 AM
When you say, "growing out of control" can you provide some stats behind that? Are we talking about 100's of MB or 10's of MB per day? Per week? Per Month? Along with the growth numbers, what kind of changes are you making to the site in those same periods of time?

How many rows are in your version store? What does your Post_Statistics table look like? Size? Rows?

With some more of this information hopefully we can find a solution for your needs.

Thanks,
-Jeremy
Jul 12, 2010 at 6:04 PM
Edited Jul 12, 2010 at 6:07 PM

Hi Jeremy -

I haven't been watching these stats over a long time so I don't have a lot of history to refer to. My ISP notified me of this because they had to start charging me more for db storage.

Anyways, when i first noticed the issue I was using vista db. The size of the db file was about 34mb. Since vista db was no longer going to be supported in 1.3, I decided to just switch over to MSSQL 2005. After I used the export tool provided by graffiti I got a report from the ISP on the size of the various tables in the db. To answer your specific questions:

1) The size of the db must have been developed during the time I was developing the site and changing the page templates. I added a lot of the blog posts on the site after the db was already large.

2) It doesn't seem to be growing too much now, but I'm also just adding posts and not modifying them or modifying the templates. I don't have exact numbers for this but I could run some tests and get exact numbers if it would help.

3) Here are the number as they currently are from the report the ISP gave me. I left out a few tables that were very small or unrelated (tags, userRoles, etc..

dbo.graffiti_Categories
# of records: 8
Data: 8kb

dbo.graffiti_Comments
# of records: 255
Data: 112kb

dbo.graffiti_Logs
# of records: 48
Data: 40kb

dbo.graffiti_ObjectStore
# of records: 17
Data: 128kb

dbo.graffiti_Post_Statistics
# of records: 13,515
Data: 336kb

dbo.graffiti_Posts
# of records: 41
Data: 304kb

dbo.graffiti_VersionStore
# of records: 1,498
Data: 25,096kb

thanks for your help!
ken

Coordinator
Jul 12, 2010 at 6:16 PM

Well, every time you create or update a post or view file, a copy goes into graffiti_VersionStore as well, which you can see is clearly the biggest table. You should be able to delete any records from that that you no longer need without causing any problems except that you wan't be able to revert to those versions anymore. I (or someone else here, I'm sure) can help you generate SQL scripts to do that if you need it, and as long as you are okay with just cleaning out old versions of everything.

That said - this DB is less than 50MB in space - and that's pushing you past your host's default database settings? Even GoDaddy's minimum plan provides you with a 200MB database (which would take a LOT of posts to fill with just Graffiti data, trust me on that). You may want to reconsider your hosting at one point if you really only get a database that small and want to run a data-driven website.

Jul 12, 2010 at 6:32 PM

Charles -

thanks for replying -

I would be fine with deleting the old versions. I have external methods I use to store archives of posts and so forth so that would be no big loss. Could you send me a script I could run to clear these entries out? That would be very helpful.

As for the ISP issue, I'll take a look around and see other options would work better. It did seem like a pretty small allocation for storage.

thanks!
ken

Coordinator
Jul 12, 2010 at 6:34 PM

Wow, under 50MB--that is a not "growing out of control" at all. I have a Graffiti site that has a DB at about 400MB right now. At 50MB I would just understand that is a very reasonable database size and I would look at other hosting providers that can supply a database to meet your needs.

With that said, as Charles points out, you could run a SQL script to delete old items in the version store, but then you loose the ability to revert back if ever needed.

Jul 12, 2010 at 6:38 PM
Sounds like the ISP is more the problem than the db. Thanks for the help! ken
Coordinator
Jul 12, 2010 at 6:45 PM

If no one else gets it up here before me, I'll put the script together and get it up here tonight sometime.

Coordinator
Jul 12, 2010 at 11:09 PM

A script would be handy. I wonder if there should also be some changes in the future regarding how versions are handled. I havent looked at this in detail but I would guess deleting a file does not delete the past versions of that file from the version store, and considering you could delete the file by some other means as well, there could certainly be some clutter in the VS table over time. Hopefully old post versions are deleted when you permanently delete a post but im not sure on that either. Maybe we could come up with a way to flush out old entries with a built in script, either by checking for the existence of files and posts and then deleting orphaned items, or by simply flushing things out completely. If there are orphaned entries, files could potentially be retrieved by creating a new file with same same name and path as the deleted one and then pulling up past versions, right? Which could be considered a sort of security risk since it becomes difficult to truly get rid of all remnants of a file. Maybe I'm going to far...

Coordinator
Jul 15, 2010 at 4:32 AM

Sorry, I've been swamped and exhausted. I'll get the script together tomorrow night for sure.

Coordinator
Jul 21, 2010 at 4:13 AM

Okay, so I'm even more of a slacker than I pretend to be apparently, but I've got a script together. I set it up as a download, available here: http://graffiticms.codeplex.com/releases/view/49341

This script can be run on your GraffitiCMS SQL Server database to remove all but the most recent entries for each unique item in your version store. Since the version store stores each version of the theme files and any old revisions of posts (does not include the current post data), you will be left with a single revision for each theme file, but the two most recent revisions for each post.

I have the script listed as a "beta" release because I make no claims to it at this point except that I ran it on an old version of a client database and it worked fine with the testing that I did. However, as always when running a script like this on your production database, it is advisable to generate a backup first.