[TYPO3-english] Oracle performance issues

[Xavier Perseguers]

Hi Philipp,

"Oracle" in the title catched my eyes ;-)

Note: members active in the core mailing list may find useful to read 
this post too, even if they do not use DBAL.

> installation works fine with all the necssary plugins like templavoila, dam,
> eu_ldap and others - BUT: the performance is very bad. An uncached page

Good to know that DAM works. Did you apply some pending patches? Because 
I wrote long ago a patch to support BINARY LIKE queries which were used 
by DAM but it has not been yet committed (should be committed quite soon 
btw as trunk is open for new features).

> needs about 8 seconds for generation (according to DBAL Log, around 600 to
> 800 queries are generated at one of these requests at uncached pages). I
> think, the number of queries itself isn't that problematic, because on
> MySQL-environments there will be a (nearly) similar number of queries (some
> oracle-weirdness required a little more), but the response times from the
> database server. I had done traces and they show that most of the time the
> database waited for some input.
>   SQL*Net message to client                    3819        0.00          0.00
>   SQL*Net message from client                3819        0.21          6.08
> This shows to me that in one page generating process the database server is
> waiting for 6:08 seconds.
> Accordingly to this, there is very low load on database server and apache.
> Surely, the all-day-frontend-user would not notice this delay (because of
> TYPO3 caching framework and an external reverse proxy), but backend users
> dosen't have this advantage, and also for fe_users this issue is
> problematic.

Well, for fe_users the problem is only when first generating the page, 
then it should be properly cached and quick as well.

But I know this problem. 8 seconds is quite common with your 
configuration and unfortunately this is a known "problem". I don't name 
it a bug but a nuisance, that's true!

If you analyze a bit more the problem you'll quickly notice that most of 
this time is passed by parsing and rewriting the queries to make them 
compatible with Oracle.

This is why I made some initial tests to cache the rewrite process and 
it _dramatically_ speeds up DBAL. Problem is that at the moment TYPO3 
does not support parameterized queries and all queries although often 
"the same" are not recognized as this meaning the rewrite process is 
done again and again by DBAL. Just a quick overview...

When TYPO3 queries a page with time constraints, the query looks like that:

SELECT * FROM pages WHERE starttime <= 1278667762 AND endtime > 1278667762

meaning the current time is hardcoded into the query. Now the rewrite 
process take place and we cache the rewritten query to be able to have 
it from cache next time and not redo the job. However next time the 
query will look slightly differently:

SELECT * FROM pages WHERE starttime <= 1278668756 AND endtime > 1278668756

(see how the timestamp changed).

My wish is to allow prepared queries to be understood by SQL parser (not 
that tricky) and by DBAL (first tests show that it's OK too) and then 
used in Core. The query would then become something like:

SELECT * FROM pages WHERE starttime <= ? AND endtime > ?

and could be properly cached. We could imagine having named parameters 
too, just as PDO does:

SELECT * FROM pages WHERE starttime <= :timestamp AND endtime > :timestamp

Then the execution of the query provides the actual values which do not 
need to be cached as they are not rewritten anyway.

> I know that this is a very specific problem, but I hope to get some answers
> with some of your empirical values and (that would be great) some hints for
> optimizing my setup (this is also the reason for posting in the english
> list, sorry for my bad english).

You should stay tuned to the DBAL bugtracker. I'll more or less soon 
work again on this feature and report progress in the already existing 
thread [1]. I'd be more than happy to have you testing the patches I'll 
provide, that'd be great as testers for other DBMS are very hard to find.

> For speed improvement I use e-accellerator and the ADOdb C extension.

It helps a bit but as said the ADOdb part is not the real bottleneck. 
DBAL is (in its current state) but we cannot avoid it as remapping of 
table and field names cannot be done by some other component (note for 
other readers :D).

To sum it up:

- Plan to add prepared query support into Core
- Then use them
- Add caching support into DBAL (initial tests using a memcached backend 
showed that it's a huge speed improvement)
- At least partly implemented with TYPO3 4.5

You may have a look at [2] to understand how you may run a bleeding-edge 
version of DBAL in an "old" TYPO3. Not officially supported but known to 
work :-)

Regards

[1] http://bugs.typo3.org/view.php?id=12668
[2] http://forge.typo3.org/wiki/extension-dbal/Project_Organization

-- 
Xavier Perseguers
DBAL Leader