[PPP] Alas, friends, we look toward uncertain times
| Author | Topic: [PPP] Alas, friends, we look toward uncertain times |
|---|---|
|
Law Bringer
Member # 2984
|
written Monday, February 11 2008 09:14
Profile
Homepage
I received a polite message from my webhost an hour ago calling my attention to the ppp database, especially the table ppp_posts. This table, as an aside, contains all the content of the past 7 years that we have been able to save. Unfortunately, it turns out that a shared MySQL server is not quite adequate to support the extreme load that the PPP puts on this vast table. I was given the choice to work to cut down the load, or upgrade my plan for a lot of money that I don't have. The search feature, which was the main culprit (caused in part by some idiot spammer who thought putting his crappy URL into the search field would somehow make it publically available somewhere, but also just through general usage), has been permanently disabled. The Google site search is still there, so use that. Long-term, I fear this may not be enough. I'm waiting for an update from my host on whether the load is reduced sufficiently, but if it hasn't, we have a problem. If that happens, I basically only have two choices. I can suspend the site until I have time to optimize it properly. When I cobbled it together in 2006, I paid absolutely no attention to scale (no need back then), and I have no real experience with optimizing a database. Until around May, I have no time to work that heavily on the Ermarian Network. The other choice is to make the site donation-, ad-, or (if that doesn't work) subscription-supported. I hope it doesn't have to come to any of these, and I will do my best to keep access uninterrupted and free. -Aran  [ Monday, February 11, 2008 09:15: Message edited by: Arancaytar ] -------------------- The Noble and Ancient Order of Polaris - We're Not Yet Dead. Encyclopedia • Blades Forge • Archives • Stats • RSS (This Topic / Forum) • Blog • NaNoWriMo Did-chat thentagoespyet jumund fori is jus, hat onlime gly nertan ne gethen Firyoubbit 'obio.' Decorum deserves a whole line of my signature, and an entry in your bookmarks. Posts: 8752 | Registered: Wednesday, May 14 2003 07:00 |
|
By Committee
Member # 4233
|
written Monday, February 11 2008 09:18
Profile
Has anything that's been posted in recent days really merited preservation to begin with? I certainly think it's great that you've been doing this, Aran, but I'm pretty sure we can part with what most assuredly has largely been dross. [ Monday, February 11, 2008 09:19: Message edited by: Drew ] Posts: 2242 | Registered: Saturday, April 10 2004 07:00 |
|
Law Bringer
Member # 4153
|
written Monday, February 11 2008 09:21
Profile
Homepage
quote:Revisionism FTW. -------------------- TM: "I want BoA to grow. Evolve where the food ladder has rungs to be reached." Gamble with Gaea, and she eats your dice. Posts: 4130 | Registered: Friday, March 26 2004 08:00 |
|
Infiltrator
Member # 5576
|
written Monday, February 11 2008 09:33
Profile
Homepage
As a stop-gap solution, could you export a large chunk of the data out of the SQL database? Then it could be stored elsewhere, allowing an immediate reduction of the size of the database and reintroduction of the data sometime later when it can be handled more efficiently, at the cost of having a large chunk of the data unavailable for some period of time. Unfortunately, off the top of my head I can't think of any general solution to the sheer volume of data, especially since I have no idea what gains could be had from database optimization, even should you be able to devote time to such a thing. -------------------- Überraschung des Dosenöffners! "On guard, you musty sofa!" Posts: 627 | Registered: Monday, March 7 2005 08:00 |
|
Shaper
Member # 7472
|
written Monday, February 11 2008 10:39
Profile
Homepage
Is it possible to make it so that, in certain forums (General, for one), it only archives a topic if it gets past a certain number of posts? -------------------- Hz'ii'zt a'iiencf coxnen a'bn'z'p pahuen yzpa'zuhb be'tt'phukh'kn az'ii'ova mxn't bhcizvi'fl? Nioca's Citadel - A resource for BoA graphics and scripts, as well as my scenarios. In Last Hope's Light RP - The end is near... Posts: 2686 | Registered: Friday, September 8 2006 07:00 |
|
Law Bringer
Member # 335
|
written Monday, February 11 2008 11:10
Profile
Homepage
How much is the shortfall between what you have and what you'd have to scrounge together to keep the current unwieldy database online? I think ads might be a necessary and acceptable inconvenience, but they rarely bring in much income. What order of magnitude of donations would be required to keep the PPP piping away? —Alorael, who wonders if perhaps these forums should be replaced by forums that allow interstitial advertising. Pay for the PPP and give the rest of the revenue to Spiderweb Software. Everyone wins! Posts: 14579 | Registered: Saturday, December 1 2001 08:00 |
|
Electric Sheep One
Member # 3431
|
written Monday, February 11 2008 11:15
Profile
Conversely, how much space are you needing? Is it an option just to zip everything for the time being, or is it already efficiently stored? -------------------- Listen carefully because some of your options may have changed. Posts: 3335 | Registered: Thursday, September 4 2003 07:00 |
|
Law Bringer
Member # 2984
|
written Monday, February 11 2008 12:24
Profile
Homepage
Neither space nor transfer volume are the issue. The problem is solely related to the database server and how much load it takes to process the queries my site sends to it - this is usually the first weak point of any dynamic website. I have been asking around and getting some expert help on optimizing my database queries - improving JOIN statements, adding indexes, and so on - which I hope will alleviate the problem for now. Perhaps my first post was exceedingly alarmist, but I didn't know how much I could do to solve the problem. We'll just see how much I can do with this. -------------------- The Noble and Ancient Order of Polaris - We're Not Yet Dead. Encyclopedia • Blades Forge • Archives • Stats • RSS (This Topic / Forum) • Blog • NaNoWriMo Did-chat thentagoespyet jumund fori is jus, hat onlime gly nertan ne gethen Firyoubbit 'obio.' Decorum deserves a whole line of my signature, and an entry in your bookmarks. Posts: 8752 | Registered: Wednesday, May 14 2003 07:00 |
|
? Man, ? Amazing
Member # 5755
|
written Monday, February 11 2008 12:57
Profile
Timely. I was going to approach you about help on integrating a sql database with a web interface in order to distribute information within a wifi district. So, what exactly is causing that high load? How many queries/hour is too much? -------------------- Synergy, et al - "I don't get it." Argon - "I'm at a loss for words..." Posts: 4114 | Registered: Monday, April 25 2005 07:00 |
|
Agent
Member # 4574
|
written Monday, February 11 2008 13:06
Profile
Perhaps you could split websites? Have, for example, the game forums on a different host then the rest of everything, and leave the links as redirects? I'm not sure which website would be willing to do this, maybe one of the Spidweb satellites. Other than that, purge. There are plenty of things that could be done away with, if need be, like post-count and birthday celebrations, introductions, redundant game questions, and anything else that doesn't add to anything. Ads wouldn't be the greatest pitfall in the world, and they might work if you maximize the space you can work put them at, margins, headers, bottom space, et cetera. You're bright though. You'll come up with something. -------------------- "I'm happy I'm the mentally disturbed person I am." -Nioca "Yes, Iffy is a demon." -Iffy "To fly, throw yourself at the ground and miss." -The Hitchhiker's Guide to the Galaxy Posts: 1186 | Registered: Friday, June 18 2004 07:00 |
|
Law Bringer
Member # 2984
|
written Monday, February 11 2008 13:15
Profile
Homepage
With some ingenuity and analysis, I've cut down the query time of the feed from 6 seconds to .2 seconds, on the front page from 2-3 seconds to .4 seconds, and that on the General forum from 2 seconds to .2 seconds. I think I'm getting somewhere here. The full search feature will have to remain offline for the time being, unfortunately. It's just too much of a weight. Perhaps I can implement a different kind of keyword-based search, using the mechanism Drupal employs very efficiently. Drupal keeps its keywords in a separate table complete with scores, and then just has to go through that table on exact matches rather than search the entire text. -------------------- The Noble and Ancient Order of Polaris - We're Not Yet Dead. Encyclopedia • Blades Forge • Archives • Stats • RSS (This Topic / Forum) • Blog • NaNoWriMo Did-chat thentagoespyet jumund fori is jus, hat onlime gly nertan ne gethen Firyoubbit 'obio.' Decorum deserves a whole line of my signature, and an entry in your bookmarks. Posts: 8752 | Registered: Wednesday, May 14 2003 07:00 |
|
Nuke and Pave
Member # 24
|
written Monday, February 11 2008 14:11
Profile
Homepage
If you turn off the search function, you can get rid of the database completely, returning to storing threads in text files. Posting statistics can still be calculated by parsing these files and storing results in the database for easy access. Some things (like old forum threads in an archive) are naturally static and don't need to be regenerated from the database every time somebody wants to view them. If only overuse of search is a problem, you could make search feature available only to those who log in, requiring users to create account for using search. Then you can place rescrictions like "no more than 20 full-text searches per user per day". [ Monday, February 11, 2008 14:15: Message edited by: Zeviz ] -------------------- Be careful with a word, as you would with a sword, For it too has the power to kill. However well placed word, unlike a well placed sword, Can also have the power to heal. Posts: 2649 | Registered: Wednesday, October 3 2001 07:00 |
|
Agent
Member # 8030
|
written Monday, February 11 2008 19:32
Profile
Homepage
This is unfortunate, but I lack the knowledge to offer any advice. -------------------- Drew - "Coincidence, correlation, or causation, I wonder?" Posts: 1384 | Registered: Tuesday, February 6 2007 08:00 |
|
Shaper
Member # 7420
|
written Monday, February 11 2008 19:43
Profile
Homepage
I have some advice. Let the archives burn. With luck, perhaps some on the lame inside jokes and other nonsense will die with them. That way, new people can come to these boards without having to worry about brushing up on worthless arcana before older members take them seriously. [ Monday, February 11, 2008 19:43: Message edited by: Emperor Tullegolar ] -------------------- You lose. Posts: 2156 | Registered: Thursday, August 24 2006 07:00 |
|
Guardian
Member # 6670
|
written Monday, February 11 2008 19:52
Profile
Homepage
Huh, I didn't know PPP was used that much. Usually when I use it (which is very rarely), I know in advance what I'm looking for. Another possibility is to make a whitelist for archiving, instead of trying to archive the entire board. That way, PPP users could nominate worthy topics to be archived, instead of saving the whole bloody mess. -------------------- "One poor design Emacs has is the use of chords. It's impossible to type with one hand." "What do vi users do with their other hand?" Posts: 1509 | Registered: Tuesday, January 10 2006 08:00 |
|
Law Bringer
Member # 6785
|
written Monday, February 11 2008 20:43
Profile
It looks like your best bet is an index of common keywords and searches can be limited to that list. Posts: 4643 | Registered: Friday, February 10 2006 08:00 |
|
Infiltrator
Member # 4784
|
written Tuesday, February 12 2008 18:47
Profile
Homepage
fwiw I haven't been able to use the search feature for weeks. Kept giving me line errors. Should have copied them... -------------------- Forever Always on Past the End TrueSite for Blades - Blades Walkthroughs Pixle Profusion - BoE Graphics Archive Posts: 563 | Registered: Tuesday, July 27 2004 07:00 |
|
Agent
Member # 8030
|
written Tuesday, February 12 2008 20:52
Profile
Homepage
quote:I've never been able to use the search feature. However, I suspect my laziness to be at fault. Forgive me if I sound stupid, but is it possible to distribute a year of archiving among different respectable members of the forum, who in turn host the topics on separate sites. The project could be centralized by providing hyperlinks at the actual PiedPiper Project. This is somewhat echoing Goldenking's suggestion nonetheless. -------------------- Drew - "Coincidence, correlation, or causation, I wonder?" Posts: 1384 | Registered: Tuesday, February 6 2007 08:00 |
|
Law Bringer
Member # 6489
|
written Wednesday, February 13 2008 18:17
Profile
Homepage
I'd offer to let you use my webspace, Aran, but sense we use the same host, I doubt that would help. :P -------------------- "Dumbledore returns from the dead and declares it to be hammertime, Harry proceeds to break it down, Voldemort is unable to touch this." —Dintiradan Spiderweb Chat Room Avernum RP • Summaries • OoC • Roster Shadow Vale - My site, home of the Spiderweb Chat Database, BoA Scenario Database, & the A1 Quest List, among other things. Posts: 1556 | Registered: Sunday, November 20 2005 08:00 |