July 10th, 2003: More indexes on commonly sorted-by fields, database cleanups
- July 02, 2003: Quick general announcements.
- I made some major changes to the apache config files and was able to trim down about 7 megs of general usage per fork. While this isn't a HUGE amount in the long run, it will keep us from hitting swap earlier than normal. If you see features suddenly not working (ekw schemes are a possibility), let me know, and I'll do what I can to revert the changes. Otherwise, hopefully it'll be a tad more responsive.
- I also added more index keys to the message dbtable, to reflect how it is commonly sorted. This should help make things faster.
- Added a theoretical hack to NodeCache.pm that'd forbid caching writeups. We carry an extra 60-100 writeups around with us in each apache fork, with the likelihood of a good cache hit being somewhat low. I put this on hold for a bit to see how the other changes work out
- Added an index to the nodelock table
- Put index lookup inside the softlinks htmlcode to actually match the way it is indexed inside of mysql
July 20, 2003 - More database slimming
- Added index to orderby field in nodegroup
- Added index to experience field in room
- Added index to parent_e2node field in writeup
- Dropped the vote_temp table after not finding it in any docs or scripts. That's 4,664,247 lines we don't need to keep around, reducing our database footprint
- Dropped the bestnodes table. It wasn't full with very much, but it's one less table to move over to the new db layout.
- Dropped the book_temp table. 75522 lines from our footprint gone.
- Wiped out the original "bug" nodetype. bug maintenance create bug display page bug edit page bug (nodetype) bug (dbtable) and 53 "bug"s are all gone
- Dropped the errorlog table. 623 lines wiped out. It was an old idea by me to have e2 parse it's own errorlogs, but I never really followed through with it.
- Dropped the links_temp table. 5736193 lines gone, cleaning up our db footprint even more.
- Dropped the secretsanta table. Was used for the old secret santa orginization from a couple of years ago.
- Carved out the old clan system and tossed it away. displayclanmotions
clanmotiontype (nodetype and dbtable),clanmotion (nodetype and dbtable),clan display page,clan (nodetype and dbtable), meta-clantype all hit the editing room floor.
- Prevented caching of users because it may not be necessary. It's easily 80% of our nodecache, which really isn't good. Reduced the max caching threshold by half to account for the slack so it doesn't fill up with other junk.
- Compacted the message table so that archived is a TINYINT, or a single bit, allowing possibly smarter optimization, since that's a common query component.
- Dropped the coolwriteups2 table
- Dropped the docrevision table. This was my beta for a feature that will make it into the new site.