PHP/SQL Geeks Click Here For Fun N' Games

Seriously, it would make sense to do a clean rewrite; and potentially it would be faster than trying to understand every nook and cranny of the old code.

(I have not seen the old code, but from what I understand it isn't wonderfully clear)

(i mean, what everyone after is the look and feel, which frankly seems easier to do than the usual fancy horror of most bbs!!!!)

We mentioned Ruby On Rails, but I think that's something that's not gonna fly. LAMP seemed to be the way people wanted to keep it.

(I love theories like that)

xxxpost

SVN server
Bugzilla installation)

have used php and mysql before for small webby projects, nothing anybody but stevem has ever seen though. have some smarty experience too (andrew mentioned it in his rewrite plans but i'm not sure it'll be useful). i also have to take some holiday (29 days left this year...) maybe i can take time off and sit and contribute to this...

would like to see current code before deciding on a complete rewrite though, 'licence' permiting.

PHP5 class and object handling was rewritten for more functionality and optimization. It's a little different from PHP4 though, so better to know right away what you plan on using. I imagine that for this board it would be a minor detail, but better

http://www.alternateinterior.com/2006/06/differences-between-php4-and-5s-object.html

haha FRAMES!

I'm "familiar" with PHP and MySQL, most of my experience is with teh ASP/SQL Server evil empire, tho. No experience with forum apps but plenty with enterprise apps.

A rewrite would make it more extensible in the future, but I think what we want to avoid is having a whole bunch of ILXors pay for hosting and then be plagued by performance problems (or working out the bugs of an untested app). It would be ideal if an interim solution could reduce the poxy fule's of the current codebase while everybody dabbles in an open source rewrite.

What I'm really curious about is the root cause of the poxy fule errors - Is the code not cleaning up its connections to the db? Poor indexing on the tables? I'm wondering if some tweaks to the db could resolve it. There was a very bad period for the past couple of days, which then was alleviated - wha happened? Did the errors cause reduced traffic, or did somebody actually do something?

The original author of the php port didn't want it to be public. Greenspun did his in Tkl/Tk for vastly different reasons.

making it clearer is key, php classes/objects could well play a part in making the code more transparent. just better commenting and sensible variable names and a convention for the important globals would do just as well.

I'd also suggest using PEAR:DB for simplifying/optimizing any code relating to MySQL connections. Works really well.

http://pear.php.net/package/DB

First step seems to me to start an SVN repository, perhaps on the google thing. Upload/edit any relevant schema files, specs, feature lists onto it so every techie that wants too can have a look.

As a person whose job consists of rescuing hopelessly spaghetti coded projects (SCADA systems, not your typical db stuff), I sincerely recommend starting from scratch. use the existing code as a fieldguide, if necessary, to see what the object was, but don't try to patch it.

having the current db schema is paramount to starting any of this, so who has it, and how do we control distribution?

1. keep current ilx running (whatever way)
2. copy a few of the smaller boards from currentilx db into a nu-ilx db
3. create nu-ilx code
4. test out nu-ilx
5. migrate current-ilx db to nu-ilx do whatever final tests necessary (perhaps offically move some smaller boards on nu-ilx as pilots for a couple of weeks or so)
6. turn off rest of old-ilx
7. slag each other off, wave dicks in nu-ilx like the old days.

Oh, agreed, without a doubt.

As an addition to that, we should be aiming for small, tight code (this will benefit both the developers and the server itself).

Schema is urgent and key.

(xpost)

From the impression I've gotten, Andrew or Alan would need to sound off on this.

Unfortunately, a major design flaw in ILX is also its key feature - no pagination of threads, each one opens in its full glory. With small/short threads it's not a problem, but if you've got a popular and very long thread, the poxy fule's may be a result of delivering the full payload on each request. Just to test this I saved off a couple of big threads and here are the file sizes (note this just represents the textual HTML, not any images which are delivered from other people's servers and aren't our goddamned problem):

Steely Dan: "Steely Dan's name has been popping up as a hip musical crush. Remember, this glossy bop-pop was the indifferent aristocracy to punk rock's stone-throwing in the late 70's. People fought and died so our generation could listen to something better. "
347 KB

A New Thread fot the Current Israel/Palestine/Lebanon mess
660 KB

Rolling Teenpop 2006 Thread
1.08 MB (!!!)

So each time the Rolling Teenpop thread gets hit, the codebase and db have to pull together over a meg of text and deliver it through the pipe. You also have the problem of people hitting refresh refresh refresh, and if the refresh occurs before their last request was completed the server may still be occupied threading their last one. It's not difficult to see how the environment could get stressed out under these conditions.

The question is, what's the best way to optimize when the mandate is to not reduce the payload?

Keep a pregenerated cache of each page in New Answers? That way the most popular threads would only get generated once per post, not once per view.

Would it be worth it? It seems silly just to use one single thread for every potential issue.

Xpost - we're assuming that pre-caching isn't being performed... perhaps another question for Andrew and Alan. Maybe someone should review the thread and compile a list of questions for them?

hmmm

Starting from scratch, it wouldn't be that hard. The basic operations would be fairly simple:

1) when loading a thread, look for a thread-cache file and serve that if possible
2) when inserting a post, generate a thread-cache file too
3) when deleting or editing a post, either delete any thread-cache file, or generate a replacement.
4) run the following shell command, or something like it, once per day or whenever:

find $ilx_threadcache_dir -mtime +2 -print0 | xargs -0 rm

(which would delete any cached threads that haven't been updated in a couple of days, so you don't end up with a cached copy of the entire database)

(xpost)

I thought it was user locking which caused the *worst* outages, but I could be wrong. I was also under the impression that some of the outages are inexplicable; or have been happening for reasons that haven't been disclosed.

x-post, I don't think post/thread deletion has been causing the issue - I've deleted numerous threads from the moderator request board, without any noticeable degradation. The slow-down usually seems to hit around the end of the afternoon, uk time.

xpost: I'd forgotten about that feature, which would add an extra level of complexity to caching stuff.

that's quite easily solved.

the hardest bit is at the moment how each post displays is different depending on 1) whether you're logged in and 2) what user settings you have (show all details etc)

problem with cached pages is all the optional bits that users like, like the bracketted bits in the signature (most can be replaced by a link to user details page i guess, maybe one that passes message id as a param).

'show all posts' is also very useful on dialup when you hit 'load', wait for page to download and then disconnect and read all posts at your leisure. so suggest caching an 'all posts' version too

xposts, maybe.

1. An admin who is able to see all ILX functionality should compile a current_features.txt file and make another file feature_requests.txt.

2. Schema is urgent and key.

3. we're assuming that pre-caching isn't being performed

4. Anyone know if MySQL query cache is enabled?

Thanks, Tom.

-- Jaq (js...), August 10th, 2006.

I'll make this point again - if the move occurs, it's going to happen on September 7th. If a bunch of part-timers can re-code the entire app, enter a test/revision cycle, perform a stress test, and migrate the existing database into a new structure in less than a month's time, I'd be very impressed. It's not impossible, but keep in mind that forum members will be paying out of pocket to keep it going post 9/7. I'd hate to rollout a halfbaked app to somebody who just laid out cold hard cash, and as I noted on the other thread, I've seen bad migrations kill good forums.

This is a bit like changing the tire on a moving car, so I'd advise to err on the side of deliberation and a phased transition. If necessary, the dev team could be split into an optimization crew and a new app crew. That way there's three possible outcomes (1) move to new server with existing codebase, 2) move to new server with optimized codebase, 3) move to new server with new app) and maximum flexibility in case contingencies arise.

that's me out then 8)

is there any guarantee that all the code isn't going to be indexed and made searchable by googly?

4) not have to move, rewrite at leisure.

Because the code as it exists is currently closed and I think it's wise to operate under the assumption that we probably can't or won't get new coder accounts in the next two weeks, replace "optimization" with "babysit" and that's what's happening already.

-httpd.conf
-my.cnf
-machine_specs.txt

Of course any sensitive bits should be edited out. Maybe add a directory just to hold non-code relevant configs and feature documentation. Would the admins be willing to do this?

-- TOMBOT (tombo...), August 10th, 2006.

That's assuming the optimization team would be working on code changes - however, there are plenty of things external to the code that could possibly provide relief (e.g. turning on MySQL cache).

If it were me, similar to what Forest Pines is suggesting, I would cache the whole of the new answers page's threads in memory (this assumes that this is what most people look at, but the principle would apply to other places) - hash table of linked lists. It's not so big. Reading files is expensive compared to reading from memory. You could compress if it's an issue trading off CPU for memory. I don't think the presentation is too much of an issue Deano, that's just post-processing isn't it? Key to scalability is to use as few resources (connections, memory, disk CPU etc.) as possible for as little time as possible. This approach trades of memory for servicing a request much more quickly (memory access being 1000s of times faster than disk and likely 10,000 times faster than DB access). It saves you using a database connection at all, and releases an HTTP connection much more quickly due to shortening the length of the transaction. This frees those up for other users to make use of. Given the amount of info on the new answers page's threads, it seems feasible (can't be more than a few megs I expect, but even if it's 100MB, that's still only about 6 quid of memory).

For updates, first update the cache, then ideally stick the update on a persistent message queue (like MQseries or MSMQ; there are open source versions of these) to which there is a single thread (possibly more than one depending on volume) servicing the updates in series (no idea if you can do this in PHP, I'm afraid). This lets the system record the information to the database ultimately, without placing load on the server that's directly proportional to the number of users currently accessing the site (effectively, spreads updates out over a longer time, smoothing the profile). Obviously need to check that the queue isn't just getting longer and longer. Persistent queues will protect against the server crashing. It won't affect users, since they'll get all the data from the cache.

Of course, all this depends on my guesswork as to how people use ILX. I would say that the most important thing to do now is to find out how people actually do use it, but in a typical situation, 95% of access is read and 5% update. I would suspect ILX has a lower proportion of updates than this. With this in mind, you would optimise towards reading, which is kind of what myself and others are suggesting.

Keef OTM. And there's no reason to do all of these modifications really. The only concern I can think of regarding queuing page caches would be the potential for really wild xposts under heavy load. I'm probably missing a detail in the technical process though.

Optimizing reads is definitely key in the new ILX engine, codenamed Excelsior.

It would actually require a slight change to ensure something didn't get removed from the cache before it was written to the DB, so cache entries would need to be have a counter incremented when inserted and the update would need to decrement the counter it was flushed to disk. Once it's at zero, it OK to remove from the cache.

At a guess of an average 100K per thread, and 200 threads on the new answers page, that would need a cache of 20M, assuming no compression.

If you're currently running out of connections, minimizing the amount of time those connections are open will allow them to return to the pool more quickly. This is helpful if long-running queries are hogging connections. Of course, this is all theory until someone can look at the MySQL logs on the server to verify where the poxy fules are coming from.

The rest of your suggestions are helpful, but that's new app talk. I'm suggesting that someone investigate small environment tweaks that might relieve some of the current lockouts, since we may not be able to mod the existing codebase. I had an ETL job that was running for 90 minutes - by building one index over a single column it dropped to 5 minutes. Sometimes the simplest acts can fix these issues, the real bear is figuring out exactly where the bottleneck is.

So by contrast, if updates are much more frequent than reads, then indexing will make it slower for high volumes. As you say, working out where the bottlenecks are is highly important.

The key would seem to be analysing the usage patterns of the current version of ILX.

boardfunctions.php on line 127 seems like a good place to start

The easy remedy is to simply remove the function to search on thread content, rather than title, or if that's not palatable, make it blindingly obvious that one shouldn't use it unless it's absolutely necessary.

unfortunately current job sitch doesn't let me offer to volunteer time this time (not that i ever ended up doing much in the past).

as i recall, there's no caching now, and also there's some very nice simple php caching libraries that i've used (and whose names are not on the tip of my tongue).

i'm all for the moving over as is and then doing work on the side (if the database schema is preserved, we could even have the old codebase and the beta one talking to it simultaneously! [assuming we trust the coders not to make dangerous beta code / trust [ahem] people not to go and try to break the database with hacks)

-- TOMBOT (tombo...), August 10th, 2006.

Haha, where in the world did you ever come up with that idea?

If it is (as I suspect) a generic OpenDBConnection on that line then we're back to the database logs.

KeefW does have a good point about the searching; if the search doesn't utilize a basic word index it will have some deleterious effects on performance. The search function here is quite pokey, and it could be commandeering db connections that eventually time-out, all the while poxy fule'ing the rest of the universe...

this would prevent the chicagoans or teenpoppers from creating 4000+ post memory drain threads...

It also might have a nice effect on bandwidth requirements.

-- Pashmina (vietgrov...), August 10th, 2006.

I made the change Pashmina notes above and remeasured those threads - quite a difference:

Rolling Teenpop 2006 Thread
347 KB -> 11 KB

A New Thread fot the Current Israel/Palestine/Lebanon mess
660 KB -> 25 KB

Rolling Teenpop 2006 Thread
1.08 MB -> 29 KB

If this default setting for users is easily configurable, I'd suggest globally changing it from "ALL" to "50" or "20". The nice thing is you get a link to view all comments on the truncated thread, so if you really want to see the whole thing you can get it.

I am too. And actually TOMBOT, if you could add me to the Google Code project page I'd be obliged (googlename = quartzcity)

Steely Dan: "Steely Dan's name has been popping up as a hip musical crush. Remember, this glossy bop-pop was the indifferent aristocracy to punk rock's stone-throwing in the late 70's. People fought and died so our generation could listen to something better. "
347 KB -> 11 KB

but i can give you some pointers on the general architecture. and yes the sql requests are very flabby - too much stuff is requested on generating threads i can remember that much.

the main approach with the very large table of posts is to have a cache of "new answers", that is some of the fields from posts from the last week, with oldest threads culled regularly.

then a cache of recently updated threads is generated from the newanswers_cache.

the cache of threads is refreshed every couple of minutes, so that when a thread off the list is "bumped" with a new answer/post, it doesn't appear on new answers page until that cache is refreshed.

the list of threads (on the new answers page) is ordered by the most recent post in the newanswers_cache, which is why a thread in the new answers cache is bumped to the top on that page immediately.

clear?

I strongly suggest core integration of memcached in the design of nu-ILX. That would solve most problems almost immediately. A rewrite should be possible within, say, a month (minus testing) if a good spec is written. There aren't that many features, after all.

http://nf.wh3rd.net/ilxor/

It is password protected. If Tombot or whoever's coordinating this wants access to it, my email address is andrewdg @ gmail.

is there any way to improve the TZ calculations? all posts have a UTC timestamp, and then if a user is logged in, the time is adjusted to reflect their own zone, which going back to a post X years ago, means knowing what the offset was for every time zone going back X years. which is what that table is.

the last useful thing i remember coding was a brain-dead caching of those offsets.

There's a load for PHP, quite a few influenced by Rails. CakePHP was a bit of a documentation nightmare when I last looked at it, but it might've improved since. I stuck with CodeIgniter because of the quality of the docs. CI has some caching classes built in, could be useful.

cough cough. You forgetting someone Alan?

Site seems more responsive in general...

I'm not trying to sell this mind you, its just an offer to run some scenarios in my free time that would simulate a ton of people all hitting the system at once, and the reporting back the relevant details.

agree v. much on the memcached thing.

why not enable "show unread" by default? if people were only loading the last twenty messages of hastings or israel/palestine/lebanon FAP instead of 800 messages, that would take a huge load off the db.

anonymous posting. just a checkbox that allows a logged-in, registered user to be completely anonymous on the website (though not necessarily in the db - gotta be some accountability)

will register with gmail later (don't need invite anymore i guess?) and post address here.

links in the right hand bar of google page don't work btw.

> Site seems more responsive in general...
> Edward III, August 11th, 2006 2:27 PM

ha ha. 8)

Although others will probably have their own choice, irc.gimp.net #ilxor could work.

well ilx>board at least so that urls are like

http://nuilx.org/ilm/ -> ilm newanswers
http://nuilx.org/thread/722857/ -> this thread, or possibly
http://nuilx.org/ile/thread/722857/ -> this thread
http://nuilx.org/ile/faq/ -> ile's specific faq

you get the idea

obv need to stay back compatible for old links to come in, but a little pre-processing of the URI wouldn't go amiss.

<IfModule mod_rewrite.c>
RewriteEngine On
RewriteBase /
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /index.php [L]
</IfModule>

leaving index.php to interpret the $_SERVER['REQUEST_URI'] into variables

i think even just http://nuilx.org/722857 should be possible.

That's fine, but please please keep the old URL format alive whatever you do.

doesn't seem to be much going on there tbh. read the mvc / php framework thing last night. have long weekend here, what with bank holiday and tomorrow off, and thought i could take the time to at least look through the existing code / schema, maybe attack the image url parser thing.

(oh, and new aliases will map to the old ones, be rewritten by apache to map exactly to old format so the old ones would work as always. it's just that when we script any new urls we'd use the new, shorter format)