The speed increase is astounding

Discuss features as they are added to the new version. Give us your feedback. Don't post bug reports, feature requests, support questions or suggestions here.
Forum rules
Discuss features as they are added to the new version. Give us your feedback. Don't post bug reports, feature requests, support questions or suggestions here. Feature requests are closed.
Post Reply
User avatar
naderman
Consultant
Posts: 1727
Joined: Sun Jan 11, 2004 2:11 am
Location: Karlsruhe, Germany
Contact:

Re: The speed increase is astounding

Post by naderman »

SQL Caching improves performance. You can see that very easily in the Explain mode available when using DEBUG_EXTRA. We wouldn't have implemented it, if it was nonsense. It's one of the main advantages of phpBB3 over phpBB2. The only cases where it might be slower are a) acm_file is being used, and the disk can't handle that many requests (so use e.g. memcached or eaccelerator's caching, etc) or if the result set of a query is empty which doesn't happen very often.

User avatar
Acyd Burn
Posts: 1838
Joined: Tue Oct 08, 2002 5:18 pm
Location: Behind You
Contact:

Re: The speed increase is astounding

Post by Acyd Burn »

Without a simple and easy way to turn sql caching off and on, these questions will remain in the religious realm


If you do not believe us then test for yourself by adding one line to the sql_query call in includes/db/mysql.php or have a closer look at the explain screens. Also, using sql queries also always involves calculations and processing time to get the correct/wanted result whereby a cache only returns a static result set.

Image

CardsFanInChiTown
Registered User
Posts: 28
Joined: Sun Dec 31, 2006 6:21 pm

Re: The speed increase is astounding

Post by CardsFanInChiTown »

Acyd Burn wrote: We already have a memcached version in the works and of course it is faster due to the file i/o not being accessed. Turning off sql caching may increase the performance for some if special conditions are met, but overall it should increase the performance if turned on, especially for mid-sized to large boards. For tiny boards the difference is not noticeable anyway.

What we found out is that, compared to 2.0.x, the cpu load decreased and the file i/o increased (of course) noticeably. But this was to be expected.



So given what I've read it would be fair to say 3.0 will require less CPU but there will be more activity on the HD? Sorry if I'm misunderstanding this.

Gumfuzi
Registered User
Posts: 232
Joined: Wed Apr 26, 2006 7:04 pm

Re: The speed increase is astounding

Post by Gumfuzi »

does this mean, with phpBB3 I now can have (on medium boards) less CPU and less RAM usage (for eg. I have not so much of RAM installed)? or only on systems with more RAM?

CardsFanInChiTown
Registered User
Posts: 28
Joined: Sun Dec 31, 2006 6:21 pm

Re: The speed increase is astounding

Post by CardsFanInChiTown »

Also, what is considered a "medium" and a "large" board? I want to make sure we are all on the same page.


Thanks again!

Cap'n Refsmmat
Registered User
Posts: 219
Joined: Tue Jan 25, 2005 11:31 pm

Re: The speed increase is astounding

Post by Cap'n Refsmmat »

CardsFanInChiTown wrote: So given what I've read it would be fair to say 3.0 will require less CPU but there will be more activity on the HD? Sorry if I'm misunderstanding this.

That's pretty much right.

A good feature for 3.2 would be an eAccelerator backend included by default, as a lot of hosts offer that preinstalled, and it would certainly help performance.

edit: wait... eAccelerator would cache the SQL cache files in memory anyway, after they'd been used once... bonus!

User avatar
Highway of Life
Registered User
Posts: 1399
Joined: Tue Feb 08, 2005 10:18 pm
Location: I'd love to change the World, but they won't give me the Source Code
Contact:

Re: The speed increase is astounding

Post by Highway of Life »

Gumfuzi wrote: does this mean, with phpBB3 I now can have (on medium boards) less CPU and less RAM usage (for eg. I have not so much of RAM installed)? or only on systems with more RAM?
It really depends... even with phpBB2, there was no set CPU speed or RAM size benchmark to go by.
It all depends on your system setup, traffic, and number of hosts on your server and any number of other varying factors.
Obviously, neither phpBB2 or phpBB3 is a Resource Hog like vB or some of the larger board systems, but phpBB3 can manage up to a reasonably medium sized board on a typical shared hosting account, and a large board on a VPS... though once a board gets pretty large, it just really depends on the traffic at that point.
It's not much different than running a static website, except that you are accessing a SQL database in addition to the delivered pages, hopefully that gives you an idea.

I would view area51 to be a "Medium" board... while phpBB.com is a "large" board.
Image

BondGamer
Registered User
Posts: 112
Joined: Mon Dec 15, 2003 8:20 pm
Contact:

Re: The speed increase is astounding

Post by BondGamer »

If you just examine the different processes you will realize how caching SQL queries is a huge benefit.

No Caching
Form Query --> Send Request to Database --> Database collects data --> Sends results back to server

Caching
Form Query --> Read Cached Data

There are probably more steps, but that is how I imagine the process to be. Web servers are optimized for file reading and sending, that is the primary job. So reading a cache file is always preferable to querying the database. There is almost no instance where it would not be preferable. If you can think of one, then post it.
but with mysql 5.x with query-caching turned on it is actually a hindrance?

I am just taking a guess, but bypassing the database altogether would be faster then any database-side caching that could be delivered. Even if data is cached, you still have to query the database and receive the data back. With SQL file caching, you have the data immediately or faster if it is in memory.

code reader
Registered User
Posts: 653
Joined: Wed Sep 21, 2005 3:01 pm

Re: The speed increase is astounding

Post by code reader »

BondGamer wrote: If you just examine the different processes you will realize how caching SQL queries is a huge benefit.

No Caching
Form Query --> Send Request to Database --> Database collects data --> Sends results back to server

Caching
Form Query --> Read Cached Data
unfortunately, things are not that simple.
if you look at how caching works you'll discover that there are many more to it than "read cached data".
-- you have to save the results of each cached query (this costs some, even if the cached data will nver be used).
-- with every query you have to look in the cache and see if it's there. this cost some for queries which are not in cache.
-- with memcache you pay with memory, regardless of how well the cache performs. it is possible that this memory would contribute more to performance if the system could use it for other things.
-- even when query results are found in the cache, it is still not 100% free: the cache data has to be read and then unserialized. this may be cheaper than db query (although one needs to actually measure to be sure), but it's still not completely free.
-- and finally, a point which relate to integrity rather than performance:
if i understand the code correctly, the current cache mechanism does not invalidate cached results even when the actual data is modified. there is a "ttl" (time to live) parameters, so the out-of-date data will not be used indefinitely, but it is still not perfect: with cache, you sometime get out of date data.

it is very much possible that in spite of all the above, the sql cache is still a huge performance booster in all circumstances.
it is also possible (though not certain) that under certain circumstances it is exactly the opposite.
my point is/was that by supplying a simple switch to turn sql caching off and on, those of us who are interested will be able to actually test the effect of sql caching for our own unique environment, and after a while, providing enough people will report their findings, there will be a better understanding when does it actually help.

the whole discussion started when someone asked about the very impressive performance improvements of phpbb 3 vs. 2.
i mentioned the reduction of number of queries and query complexity, and described the improvements to the templating system. both these (huge) improvements have no down side, and they are both a net gain.
then i mentioned that the sql-caching is not as clear cut as the other issues, and it would be nice to have some actual comparative data to verify that it actually improves performance.
since then, acyd burn, naderman, and now bondgamer have made claims that it is a net gain in all cases.
unfortunately, none of these claims (which may well be all true) is backed up by any real data.

User avatar
jojobarjo32
Registered User
Posts: 164
Joined: Wed Jun 22, 2005 7:38 pm
Location: France

Re: The speed increase is astounding

Post by jojobarjo32 »

code reader wrote: and finally, a point which relate to integrity rather than performance:
if i understand the code correctly, the current cache mechanism does not invalidate cached results even when the actual data is modified. there is a "ttl" (time to live) parameters, so the out-of-date data will not be used indefinitely, but it is still not perfect: with cache, you sometime get out of date data.

AFAIK, cached data are removed when the "actual data" are modified. For example, if you add, remove or mofify a disallowed username, the file which contains these data is removed. For the template data, if the relevant option is set to "on" in the Load Settings page, the template system checks if the template file has been modified before loading the cached file.
You only get old data if you have manually modify the database.
code reader wrote: by supplying a simple switch to turn sql caching off and on

I think it's not very difficult to do so :)
Simply comment the lines where the cached files are created (in includes/acm/acm_file.php) and you'll have the board working without cache (only the template caching is necessary and may need some adjustments to work).

Post Reply