# database optimization



## omegawolfie (Nov 9, 2007)

ok. I have some beef with the site coder. while I agree that what he's planning will speed things up a bit, it will anger not only me, but alot of other furs I know. From what I understand, we won't be able to favorite anything older than six months? If I'm wrong, please explain in greater detail.


----------



## Nightingalle (Nov 9, 2007)

Not favorites.

I think what he's doing is people who's accounts have just been sitting idle for a long time (IE, 6 months) and have images they need to go through and look at (submissions from other artists) that are older than 6 months, then he's deleting those.  

Does that make sense?


----------



## Infinity (Nov 9, 2007)

Be patient, everything will be revealed in due time... Would you want a laggy and dysfunctional site or a functional site with whatever is being brought in? Besides people can reupload stuff if needed. A favorites system is not needed on an art site anyway...

Edit: I don't know what's going on, but just wait and see what happens.


----------



## Nightingalle (Nov 9, 2007)

Infinity said:
			
		

> Be patient, everything will be revealed in due time... Would you want a laggy and dysfunctional site or a functional site with whatever is being brought in? Besides people can reupload stuff if needed. A favorites system is not needed on an art site anyway...
> 
> Edit: I don't know what's going on, but just wait and see what happens.




I beg to differ, I think favorite system is needed on an art site ;  I mean, I like to keep up with my favorite art without having to save it >_>;


----------



## CottonCandyCabannaBoy (Nov 9, 2007)

kuronekotenshi said:
			
		

> Infinity said:
> 
> 
> 
> ...



And i agree, i mean ive got plenty of hard drive space but honestly id rather use it on music or games.

Then again I sincerely doubt that they are going to remove people's favorites, i thought when they posted it they were talking about maybe pending submissions or something like that...We shall see soon enough though!


----------



## TheShekinah (Nov 9, 2007)

I think the message could have been a bit clearer >_>  I thought it meant that everything older than 6 months will be removed from our favorites list, but it doesn't sound like that's the case.


----------



## thecrypto (Nov 9, 2007)

I have fixed the message, I did not remove any favorites. All I removed was old messages in your user messages box which resulted from people on your watch list. Your watchlist is still there and all your favorites too.


----------



## CottonCandyCabannaBoy (Nov 9, 2007)

Thanks much for the clarification crypto! See everyone nothing to worry about!


----------



## Tensik (Nov 9, 2007)

as long as you are deleting old things, you may want to consider PM's that are 6 months or older.  I know a lot of us let it get out of hand simply because we hold onto them, forget it, then it takes so long to go through them all.


----------



## Dragoneer (Nov 9, 2007)

Tensik said:
			
		

> as long as you are deleting old things, you may want to consider PM's that are 6 months or older.  I know a lot of us let it get out of hand simply because we hold onto them, forget it, then it takes so long to go through them all.


That's sort of the goal, too.


----------



## Mazz (Nov 9, 2007)

Preyfar said:
			
		

> Tensik said:
> 
> 
> 
> ...



Yea, so those of us who have important info saved in PMs will have to lose it because of a lack of warning about removing old PMs. D:


----------



## Epsereth (Nov 9, 2007)

Preyfar said:
			
		

> Tensik said:
> 
> 
> 
> ...




No no no no no!!! You need to give us warning before you do that!!!!


----------



## Dragoneer (Nov 9, 2007)

Mazz said:
			
		

> Yea, so those of us who have important info saved in PMs will have to lose it because of a lack of warning about removing old PMs. D:


When we do it, any notes that are going to be removed will be older than six months, so it shouldn't impact too much. However, we are not removing notes as of right now.


----------



## Nightingalle (Nov 9, 2007)

I'm a procrastinator but if you've got commission info from older than 6 months ago.. D:  Y'know.  >>;  I doubt notes being removed will affect myself.


----------



## omegawolfie (Nov 9, 2007)

Thanks much for the clarification Crypto and sorry for the drama. it's just I recently had lost all my saved art so I was favoriting and I jumped to conclusions. Many apologies.


----------



## Bokracroc (Nov 9, 2007)

Common sense to back-up important information in another location anyway.


----------



## Epsereth (Nov 9, 2007)

kuronekotenshi said:
			
		

> I'm a procrastinator but if you've got commission info from older than 6 months ago.. D:  Y'know.  >>;  I doubt notes being removed will affect myself.




That's fine and dandy for you, but some of us have had things happen, you know? There are also conversations people might want tos ave. A few days warning would save a lot of hassle and general pissiness.


----------



## TheRedRaptor (Nov 9, 2007)

I so want to optimize  thecryptos' brain right now


----------



## Bokracroc (Nov 9, 2007)

You're just pissed because you can browse FA right now and we'll more than likely have to wait till tomorrow for it up again.
Find something else to do. I'm playing COD4 on DS and downloading Cooking Mama.


----------



## TheRedRaptor (Nov 9, 2007)

So how are you going to serve your Mama Bokracroc?

I've got no internets at home, and I'm waiting for a commission to be done


----------



## Dragoneer (Nov 9, 2007)

TheRedRaptor said:
			
		

> I so want to optimize  thecryptos' brain right now


Hey now, there's no need to be like that.


----------



## TheRedRaptor (Nov 9, 2007)

Sorry... it is just my nature.

I also want to do the same to Bill Gates


----------



## CottonCandyCabannaBoy (Nov 9, 2007)

Sure it would have been nice for some notice but hey its not that big of a deal.  I cant tell you how many times ive seen journals about "ive lost my commission list please send a note" Or if you cant really remember an entire conversation...then ask "hey what were we talking about?" I mean its easy fixes for something thats really not that big of a deal...

But hey thats just my opinion.


----------



## furryskibum (Nov 9, 2007)

I'm reasonably certain it's people like me who were slowing things down...

Almost hit 30,000 new submissions from people I'm watching.  *shrugs*  Just never got around to saving the old ones from when I had no Internet and was building on my house.


----------



## Ryuu Majin (Nov 9, 2007)

Does this batadase optimization affect FA and make it unaccessible right now? FA is temporarily off line, I have cleaned my cache and cookies but it's still off line.


----------



## CottonCandyCabannaBoy (Nov 9, 2007)

Ryuu Majin said:
			
		

> Does this batadase optimization affect FA and make it unaccessible right now? FA is temporarily off line, I have cleaned my cache and cookies but it's still off line.



yup FA is down untill it is done.


----------



## yak (Nov 9, 2007)

It will be up in a few hours.


----------



## Ryuu Majin (Nov 9, 2007)

Good luck, Yak! We're rootin' for ya! X3


----------



## ANTIcarrot (Nov 9, 2007)

Put another tick in the 'some warning would have been nice' box

My sub list is currently (or should I say used to be) about 2100. The principle reason for this is that it often takes THIRTY SECONDS before FA can be buggered to cough up a picture. You might recall this has been mentioned before? That means saving just 100 pictures takes 50 minutes. It's really hard to make headway against that kind of aggravation. Though I might have tried to make the effort if I'd known this was coming.

Out of optimistic interest, will the DB optimisation fix this problem?

If not a couple of features that I'd really like to see in a next versiopn of FA is more control over watches. Ignoring music for example. And bulk downloads. That way I can highlight up to 64 pictures at a time and tell it to 'delete and save to HDD' instead of just delete. Even if that process takes an hour, it would be less annoying and more user friendly because during that hour I could do other things.


----------



## Lynxia (Nov 9, 2007)

I was wondering about something though...

Is this happening because of the new owner of the FA? Doesn't he/she have enough good running servers or harddrives to keep this site running?
I don't want to be mean or anything, but since the selling of FA, I have seen alot of error pages...

The site coders are doing well with trying to keep this from happening, I surely don't blame them, but... yeah, there are too many things happening concidering FA problems.


----------



## Eustache (Nov 9, 2007)

But this isn't a matter of DB memory capacity, is it? The pictures themselves surely weigh a lot more than any list of messages. I guess it's only the navigational speed which is hampered by having to recapitulate all the past messages, each time we move from one page to another?


----------



## TheShekinah (Nov 9, 2007)

I always thought that the number of accounts registered must affect the database... maybe not connection-wise, but at least in server space.  And since we can't remove old accounts, they just sit there, taking up space...

But I don't really know how any of this stuff works, so maybe unused accounts don't take up enough space to make it worth removing them.

At any rate, this makes me glad that I'm obsessive and clear my new alerts every time I log in


----------



## DragonTrew (Nov 9, 2007)

Database problems are a pain in the ass... really, I can tell... Big databases are painful to keep running smoothly you have to 'tune' the database very often, otherwise it will degrade slowly until unacceptable levels of performance...

By the way what DBMS are you guys using? Oracle? MySql?


----------



## Brooklyn (Nov 9, 2007)

Question: Are you guys saying that whenever we mark a message "as read" it doesn't actually delete it?

Anyway, I think it would be easier to 1) Put a clause in the ToS that messages (New Submission notices, Journal notices, Comment notices, etc) will be deleted after six months (or less, because you rightfully warned them). Then 2) Implement an "expired" function that automatically removes them after the alloted time.


----------



## Hakumei Ookami (Nov 9, 2007)

Hmm. Is it strange that I had hundreds and hundreds of undeleted messages sitting in my FA for 6 months,and on the day I return and delete them all, we're told that the messages sitting in everyone else's accounts are slowing the server down?

I'm not too bothered, all I really used FA for was commenting other people until very recently (I uploaded part of the first chapter of the novel I'm writing) so I'm willing to wait, and wish you luck with your optimisation and hope that you complete it in time to please the impatient masses.


----------



## TakeWalker (Nov 9, 2007)

On the subject of notes, wouldn't it be a better idea to, say, get rid of everything in everyone's deleted folder, since there's no way users can do that themselves? c.c I'm sure you'd clear up a ton of space, and not piss anyone off in the meantime. Just give people a little warning, in case they've been using deleted to store things.


----------



## marymouse (Nov 9, 2007)

I made the suggestion in the suggestion forum a while back... but about the notes...

Would it be possible to make it possible for us to delete our own notes?  I have many many pages of notes that are a mixture of commission notes and random notes from friends.

I am pretty sure you can delete the notes by opening them, but little checky boxes next to them, or a "nuke pms" option would rule.

Anyway the site's moving lightning fast at the moment, so whatever you guys did, it seemed to work.


----------



## yak (Nov 9, 2007)

TheShekinah said:
			
		

> I always thought that the number of accounts registered must affect the database... maybe not connection-wise, but at least in server space.  And since we can't remove old accounts, they just sit there, taking up space...


Server space is the least of the concerns when it comes to speed of the database.
It's the amount of information the server has to process to return a data set  based on the restrictions you put in your original request. Simply put, the more records are in the database the slower it runs.





			
				DragonTrew said:
			
		

> By the way what DBMS are you guys using? Oracle? MySql?


MySQL with InnoDB storage engine. 6Gig buffer_pool, binlogging.



			
				Brooklyn said:
			
		

> Question: Are you guys saying that whenever we mark a message "as read" it doesn't actually delete it?


No, it doesn't. It's too much of a strain on the server to remove the data with every request (rebuid the keys and table data file). It's a lot easier to mark it as 'junk' , and remove in batches later.



			
				Brooklyn said:
			
		

> Anyway, I think it would be easier to 1) Put a clause in the ToS that messages (New Submission notices, Journal notices, Comment notices, etc) will be deleted after six months (or less, because you rightfully warned them). Then 2) Implement an "expired" function that automatically removes them after the alloted time.


My opinion as well.



			
				Hakumei Ookami said:
			
		

> Hmm. Is it strange that I had hundreds and hundreds of undeleted messages sitting in my FA for 6 months,and on the day I return and delete them all, we're told that the messages sitting in everyone else's accounts are slowing the server down?


Straws + camels. Same principal applies here.



			
				TakeWalker said:
			
		

> On the subject of notes, wouldn't it be a better idea to, say, get rid of everything in everyone's deleted folder, since there's no way users can do that themselves? c.c I'm sure you'd clear up a ton of space, and not piss anyone off in the meantime. Just give people a little warning, in case they've been using deleted to store things.


That can easily be done, but with a good warning in advance. People /do/ keep valuable data in there.



			
				marymouse said:
			
		

> Would it be possible to make it possible for us to delete our own notes?  I have many many pages of notes that are a mixture of commission notes and random notes from friends.


It can be done, but the first thing that needs to be done is to rewrite the current PMs code, and that can be done only as a major code update. 
It's not reasonable to do that at this point, because the current code will be scrapped and replaced with something else we are working on called "Ferrox"


----------



## Arshes Nei (Nov 9, 2007)

Yak,

I just wanted to thank you for the time in giving these more informative and constructive replies. I wish more threads would pop up like this than "hey is it down, it's down and it's driving me crazy" they actually help let people know what's going on in the meantime.


----------



## xainy (Nov 9, 2007)

So the Site Status forum says it's up and has been...but it's not for me. Is that my fault? Do I need to clear cookies or something? Or is it just down again and the thread hasn't been updated?

Edit:
Nevermind. I cleared my cookies and that seemed to do the trick. :0


----------



## Nightingalle (Nov 9, 2007)

When in doubt, ctrl+F5.


----------



## Rhainor (Nov 9, 2007)

Lynxia said:
			
		

> I was wondering about something though...
> 
> Is this happening because of the new owner of the FA? Doesn't he/she have enough good running servers or harddrives to keep this site running?
> I don't want to be mean or anything, but since the selling of FA, I have seen alot of error pages...



FA is using the same servers in the same location as before the change of ownership.  The only thing that changed then was who owned the name "Fur Affinity", the associated Internet domain names, and some of the stuff *on* those servers (like parts of the back-end software).



			
				kuronekotenshi said:
			
		

> When in doubt, ctrl+F5.



QFFT.


----------



## yak (Nov 9, 2007)

What Rhainor said. The only thing that has changed over the ownership transfer, website-functionality-wise, is the content on the staff page.

ArshesNei, thanks, i appreciate every byte of that. It takes a whole 15 seconds to reply to a direct question, so it's not really a hard thing to do. It's just that most of the time people go with their assumptions, usually of the worst, rather then doing the obvious thing and just asking.


----------



## Brooklyn (Nov 9, 2007)

Something I just thought of, maybe have daily planned downtimes to remove unwanted/junked records during the most convenient times, in addition to the six-month limit? Say, 12-2am EST?


----------



## yak (Nov 9, 2007)

A downtime that will take away from my browsing time, as myself and ~20% of FA's users are not situated in the US. 
Neat, but that will not work.


----------



## Brooklyn (Nov 9, 2007)

yak said:
			
		

> A downtime that will take away from my browsing time, as myself and ~20% of FA's users are not situated in the US.
> Neat, but that will not work.



Well, no matter what time you pick, /someone/ is going to get short-changed. (That's why I suggest a /short/ period of time. Chances are that you'll interrupt less people that way.) It's better to just go with it and let them get used to it.

Even 12-2am EST cuts into my own browsing time. (I live in Central. that's 11-1am)


----------



## Hakumei Ookami (Nov 9, 2007)

Maybe once a week or a month it would be easier?

Don't always use EST time because I live in the UK and that would be unfair on me =P


----------



## yak (Nov 10, 2007)

If it was in my ability, i'd opt for a complete notification system rewrite.
But at this time, it's not a reasonable thing to do. Current code will be scrapped and replaced by Ferrox. The best thing to do now, i believe, would be to devise some kind of patch to take care of the problem for the remaining time.

We'll think of something


----------



## ferinoch (Nov 11, 2007)

Perhaps in place of a fixed downtime that will effect everybody in a specific region, a rolling down time could be used? Not sure if somebody has to watch what goes on during the actual operation, but perhaps it could be written into a program to do it automatically? 

Just a thought.


----------



## Stratelier (Nov 11, 2007)

You mean relative to each person's time zone?  There's no such thing.


----------



## Brooklyn (Nov 11, 2007)

Stratadrake said:
			
		

> You mean relative to each person's time zone?  There's no such thing.



He means that every time the event fires, it increases/decreases the next fire event by an hour.


----------



## ferinoch (Nov 11, 2007)

Brooklyn said:
			
		

> Stratadrake said:
> 
> 
> 
> ...



Yeah. If you keep it rolling faster or slower relative to the advance of actual time, then different people are effected each time, so it's not the same subset of users always being screwed. it means _everybody_ gets effected, but in theory we all share in a small burden, rather than one group taking the full load.


----------



## AndyFox (Nov 12, 2007)

Tensik said:
			
		

> as long as you are deleting old things, you may want to consider PM's that are 6 months or older.  I know a lot of us let it get out of hand simply because we hold onto them, forget it, then it takes so long to go through them all.



Yeah. I made that same comment last month. Old PMs use to automatically be deleted if over 90 days old. I asked why this was no longer functioning. My question was met with dead air.


----------



## Bokracroc (Nov 13, 2007)

Brooklyn said:
			
		

> yak said:
> 
> 
> 
> ...


Kingdom Of Loathing has a 'rollover' thing. At a certain time everyday, the game goes down for a while to just check everything is alright/to update stuff.

Taking the site down once a week/fortnight at a certain time for an hour or two for maintenance isn't going to kill everyone. It's better than semi-constant 'random' outages.


----------



## Stratelier (Nov 13, 2007)

> Taking the site down once a week/fortnight at a certain time for an hour or two for maintenance isn't going to kill everyone.


Except maybe the sleep schedules of the coders who actually have to _perform_ the maintenance... working on a 23-hour day?


----------



## Brooklyn (Nov 13, 2007)

Stratadrake said:
			
		

> Except maybe the sleep schedules of the coders who actually have to _perform_ the maintenance... working on a 23-hour day?



Code, eat, sleep. Such is the life of a programmer.


----------

