[Typo3-dev] Trivial scalability test

[Mathias Schreiber [wmdb >]]

Kasper Skårhøj wrote:
> First; What is the penalty we pay by adding indexes? They are not for
> free. Each time we make UPDATE/INSERT or DELETE all indexes are
> modified by MySQL as well, so we have to analyse what balance is
> right for us! I have NO clue how great the penalty is!?

This depends on how many records you have any how big the amount of data
stored within the index is.
Basically you only index fields the you use in WHERE and GROUP BY clauses.
We did so in a extension which handled request over 12 different tables
changing parsetime from 14 seconds to 84 ms.
We also manually index all cache, pags and tt_content tables so improve
overall performance.
If we want to monitor how big the penalty is we would have to gather
infomartion on how often data is requested over how often data is
manipulated.
In typo3 this is mainly 100:1 in live environments (development is cut out
in here because this is not really representative).

Since this touches DBAL as well I wanted to offer 1 machine for DBAL
purposes where we install mysql, MSSQL, Oracle, Postgres etc.
This way we could benchmark the different systems.
Oracle and MSSQL offer a really cool feature where you set up a daemon that
monitors all queries teking place on the server and after a week or so you
get a logfile saying "ok, change this query to this'n'that and set up
indicies here, here and here".
This might reduce the "load" of the profiling team.

> Second; This task belongs under the profiling project. Is it possible
> that this mail could animate anyone to start systematic analysis of
> the benefits and drawbacks of adding indexes on tables in TYPO3
> (including what indexes on what tables).

I'd like two out of several reasons:
a) I work together with a bunch of guys doing nothing else than tuning
databases for Vodafone all day long and thus could "leech" some infos from
them.
b) I am generally interested in making typo3 more perfomant since I got grip
of several "enterprise" clients who need high performance.

greetings
Mattes