[TYPO3-dev] Time issues - a proposal

[Christian Kuhn]

Hi,

Martin Kutschker wrote:
> It 
> drops the seconds from the current time as access control doesn't use 
> seconds. The point is to give Mysql the chance to cache the query, which 
>   is nearly impossible with clauses like "endtime > 
> $GLOBALS['SIM_EXEC_TIME']".

I wanted to get a feeling of how much we could win if we drop support
for seconds in the access checks to make better use of mysql_query_cache.

So I have hacked a simple stupid script [1] to benchmark a fe-page and
get stats of the mysql_query_cache hits: Basic parsetime of the page is
around 170 ms (no_cache page!), around 40 mysql queries / request are
executed and 2/3 of them with timestamps.
The benchmark does 100 requests in around 17 seconds, so we have ~6
requests per seconds, so ~5 of them already profit from query_cache,
because the timestamp does not change meanwhile in a second.

Test-Environment: Fresh myql-restart on a non-loaded system to prevent
cache-pruning, t3 trunk, eaccelerator, php 5.2.0, mysql 5.0.32,
non-cached page, apache bench with 5 concurrent requests and 100 total,
only cache_hits to myisam-tables are counted.

Typical output:
real    0m17.480s
user    0m0.008s
sys     0m0.016s
Queries before: 450
Queries after:4584
Number of Queries: 4134
Cache Hits before: 4
Cache Hits after: 3158
Number of Cache Hits: 3154

Next I hacked $EXEC_TIME=time(); in t3lib/config_default.php:
$EXEC_TIME = (int)(time()/60)*60;

With this hack about 2/3 of the queries with timestamps get rounded to
minute timestamps, but there are still some queries in
t3lib/class.t3lib_userauth.php for fe_users and be_users tables that are
not affected. These are also executed for a cached page, so the effect
is not that impressive here.

Typical output with the hack:
real    0m16.830s
user    0m0.008s
sys     0m0.008s
Queries before: 432
Queries after:4549
Number of Queries: 4117
Cache Hits before: 0
Cache Hits after: 3398
Number of Cache Hits: 3398

These are ~250 more cache hits in mysql, and a benefit of half a second
for the benchmark, or about 5ms per request.

Notes:
- This was a bloody hack just to get an impression.
- I have not checked any side effects, some things are likely to break.
- The apache benchmark is not a real life example, but we can at least
state that changing the timestamps makes better use of mysql_query_cache
and that we will get a benefit in the parsetime for loaded sites.
- We might want to look into every direct time()-call for the fe,
especially for those that are used to construct queries that are still
executed for cached pages.
- The cache_hits win would be much greater for sites that only get 1
req/s on a page, adding a sleep 1 to the script between every request
would show this.



Greetings
Christian

[1] http://www.mvwa.de/fileadmin/dl/t3_cache_hit_test.sh