I have been working very hard over the past few months to review each and every query on my phpBB boards and try to optimize it. I run a board that is now getting (on a fairly regular basis) over 250 concurrent users now, and this is on a server that I share with a few other boards. So it’s important that things perform at their best. I will be writing up some of the query tuning tips in later posts. As it turns out, my query tuning efforts probably helped, but that’s not where the bulk of my page generation times were coming from.
Not even close.
I was testing my queries outside of the php page and getting query times that were not measurable. When using the MySQL command line interface the query times only go out to two decimal places. Many (most) of my queries run in less than that. Yet I would still see page generation times of half a second (0.5 seconds) or more. Even, on occasion, a 14-15 second generation time. Why?
In a later post I will go into more details about how I track the time, so you can determine if you agree with my methods or not. But for now, the main point: the part of generating a page that consistenly takes between 70-95% of the total time is the pparse() line at the end of every page. That one step (granted it does a lot) is where the time goes.
For example, here are some numbers from the index page:
Generated in 0.6231 seconds using 13 queries. (SQL 0.0083 Parse 0.5572 Other 0.0577)
Generated in 0.4889 seconds using 13 queries. (SQL 0.0060 Parse 0.4456 Other 0.0373)
Generated in 0.1411 seconds using 13 queries. (SQL 0.0048 Parse 0.1012 Other 0.0350)
Generated in 0.1655 seconds using 13 queries. (SQL 0.0054 Parse 0.1253 Other 0.0348)
For these scenarios, the parsing time was 89%, 91%, 72%, and 76% of the total page processing time. The total query time was on average 0.003, or generally a very minimal percentage of the page processing time.
So all of that time I’ve spent optimizing queries has been useful, but to a very minimal extent. It seems that the most time consuming portion of the page generation is the template parsing step.
Why? I have no clue. I have never even bothered to crack open the code for template.php because for all of these years it has simply been a black box. Data goes in, formatting is applied, and html is returned. What’s to worry about?
Quite a bit, it now seems.
So what I have done is secured four different template systems and I’m working my way through a fairly thorough benchmark. I ran over 5,000 page refreshes, using index, viewforum, viewtopic, and memberlist as sample pages. I used the stock phpBB template system, a phpBB template system modified to include caching, the eXtreme Styles MOD from Cyber Alien, and a modified version of the template system from the Category Hierarchies MOD. (It has also been suggested that I try to back-port the template system from phpBB3, but I’m not up to that yet.)
I am not quite prepared to share numbers yet as I want to repeat my tests on a stand-alone system so there is no question of advantage / disadvantage of one template system over the other due to overall system activity.
I am prepared to say that the SQL time was extremely consistent from one system to the next, which reinforces my idea that the SQL execution impact on the page load is the same no matter which template system is in use. That was not surprising.
I am also prepared to share that the four pages I selected all seem to put a different amount of load on the system. As you might expect, the memberlist is the least load. Of the remaining three pages (index, viewforum, and viewtopic) it probably should not be a surprise that viewtopic has the highest load. If I use relative numbers and assign index.php a load factor of 1.0, then viewforum is about 5 and viewtopic is about 8. So viewing a topic puts 8 times more load on a system than viewing the index page. That’s probably not surprising.
What was surprising was that there is no clear-cut winner in the template race. One template system scored very well on the index and memberlist pages, but came in last on the viewtopic page. Since the viewtopic page has the higher load, maybe I would be better off picking the template system that performed the best on that specific page, and take the lesser load on the less expensive pages?
It should be noted that these pages have all been customized to some degree, so numbers for a standard (unmodified) phpBB board might be somewhat different. The bottom line is that while my query optimizations are probably helping, reducing the query load from 0.004 seconds down to 0.003 seconds has less of an impact on the overall page generation times than I would like. So now it’s time to really dig into and try to understand the template engines available for phpBB and see what I can learn.