[TYPO3-dev] Typo3 Database design question (optimization / indices)

Dimitri Tarassenko mitka at mitka.us
Sat Mar 25 18:32:45 CET 2006


I has some spare time on my hands today, so instead of cutting grass
and other exciting yard work, I actually did some benchmarking with
the proposed indexes.Here are my results:

Hardware: 2xIntel Xeon 3Ghz (total 23,920.64 BogoMIPS), 2G RAM,
5-drive RAID5 SATA (LSI).

Software: RHEL 4 64bit, kernel 2.6.9-5-smp, php 4.3.9-3.9, mysql
4.1.12, apache 2.0.52, TYPO3 3.7.1, Zend Optimizer.

Site statistics: 243 pages, 320 content elements, 10 templates.

Testing method: ApacheBench 100 requests in 5 concurrent connections,
run from the same server (i.e. no network latency/bandwidth
bottleneck). QueryCache=OFF means query_cache_type=0 in MySQL config,
QueryCache=ON means it's 1 (40MB size). TYPO3Cache=OFF means adding
no_cache=1 parameter to the URL.

Here are the total times for with and without the new indexes proposed by Marc:

Test: <No Indexes>/<With Indexes>

QueryCache=OFF, TYPO3Cache=OFF: 19.46s / 19.28s  (1% speed gain)
QueryCache=ON, TYPO3Cache=OFF: 18.23s / 18.30s (0.4% speed loss)
QueryCache=OFF, TYPO3Cache=ON: 11.15s / 10.47s  (6% speed gain)
QueryCache=ON, TYPO3Cache=ON: 11.09s / 10.29s (7% speed gain)

Overall, there is measurable performance gain on cached pages, but
nothing I would open a bottle of champaigne for ;)

Your mileage may vary, especially on slower iron with less memory -
would love to see somebody else's numbers.

Next time my grass is too wet to mow, I'll try it all with compound
indices and let you know;)

--
Dimitri Tarassenko


More information about the TYPO3-dev mailing list