[TYPO3-dev] Performance optimization Typo3

[Chris Zepernick {SwiftLizard}]

Hi,

>> If you run systems in a normal typo3 scope this might be no problem, but
>> if you have a high traffic installation with several hundert thousand
>> users that have several different usergroups assigned this is a major
>> problem.
>
> This sounds more of a general performance problem with the setup,
> although I'm sure that you've investigated it.

You are right,... we also got a gerneral problem with performance
due to some crapy developed exts, but this was also a problem
we discovered.


> A database, including MySQL, is quite capable of performing a lot of
> queries. Larger databases require correct configuration of course.

sure

> Years ago I worked on a (non-TYPO3) site with half a million registered
> users. Every page view the user profile was updated and some pages (e.g.
> with a chat applet on them) even 'called home' frequently to tell the
> user was still there (and the online timestamp of that user was again
> updated).
> Of course we used a dedicated server just for MySQL with sufficient
> memory, the right indexes in the database and a proper configuration
> (buffers, etc.) for the expected load.

I also think you optimized the code and the way this was handled, in the 
Typo3 way the solution at the moment is to be optimized.

You will get no problem with a straight forward concept for users and 
groups, and a managable amount of users and groups. But in our case this 
is pretty mixed up, and user can switch their group on the fly.

A lot of weird stuff ;-)

We discovered that we save about 8 querys on each page request this
way. 3 Selects, 3 Deletes, 1 Update 1 insert. Those themselfs are no 
problem at all, but within the scope of several thousand users we save
more than 250.000 querys per day.

Querys organized:
SELECT COUNT(ses_id) FROM fe_sessions WHERE 
ses_id='b2d15ea6a256c3d96b56c4dc9fdca3b0'"
SELECT content FROM fe_session_data WHERE 
hash='b2d15ea6a256c3d96b56c4dc9fdca3b0'"
SELECT * FROM fe_session_data WHERE hash='3dd177de35fe2705cdb444ac3ddb4b60'"
DELETE FROM fe_sessions WHERE ses_id = '3dd177de35fe2705cdb444ac3ddb4b60'
DELETE FROM fe_sessions WHERE ses_id = '3dd177de35fe2705cdb444ac3ddb4b60'
DELETE FROM fe_sessions WHERE ses_id = '3dd177de35fe2705cdb444ac3ddb4b60'
INSERT INTO 
fe_sessions(ses_id,ses_name,ses_iplock,ses_hashlock,ses_userid,ses_tstamp,ses_permanent) 
VALUES
UPDATE fe_sessions SET ses_tstamp='1282126916' WHERE 
ses_id='3dd177de35fe2705cdb444ac3ddb4b60'


Real query order:

SELECT COUNT(ses_id) FROM fe_sessions WHERE 
ses_id='b2d15ea6a256c3d96b56c4dc9fdca3b0'"
SELECT content FROM fe_session_data WHERE 
hash='b2d15ea6a256c3d96b56c4dc9fdca3b0'"
DELETE FROM fe_sessions WHERE ses_id = '3dd177de35fe2705cdb444ac3ddb4b60'
SELECT * FROM fe_sessions,fe_users WHERE fe_sessions.ses_id = 
'3dd177de35fe2705cdb444ac3ddb4b60'
DELETE FROM fe_sessions WHERE ses_id = '3dd177de35fe2705cdb444ac3ddb4b60'
SELECT * FROM fe_users WHERE username='chris' AND pid IN (2) AND 
fe_users.disable=0 AND fe_users.deleted=0 AND
DELETE FROM fe_sessions WHERE ses_id = '3dd177de35fe2705cdb444ac3ddb4b60'
INSERT INTO 
fe_sessions(ses_id,ses_name,ses_iplock,ses_hashlock,ses_userid,ses_tstamp,ses_permanent) 
VALUES('3dd177de35fe2705cdb444ac3ddb4b60','fe_typo_user','::1','116999013','1','1282126916','1')"
UPDATE fe_users SET lastlogin='1282126916' WHERE uid='1'"
SELECT * FROM fe_sessions,fe_users WHERE fe_sessions.ses_id = 
'3dd177de35fe2705cdb444ac3ddb4b60'
UPDATE fe_sessions SET ses_tstamp='1282126916' WHERE 
ses_id='3dd177de35fe2705cdb444ac3ddb4b60'
SELECT * FROM fe_session_data WHERE hash='3dd177de35fe2705cdb444ac3ddb4b60'"
UPDATE fe_users SET is_online='1282126916' WHERE uid=1"

>
> Using a cache for storing session data seems like a really bad solution
> to me.
>

If you see the caching framework as cache,... you are totaly right, no 
doubts ;-)

We utilized it more like an API to use several different storage options 
without reimplementing them.

So yes we used caching framework but only to not having to reimplement 
the memcache interfaces, or the other interfaces, and to be able to 
switch the session storage without a huge amount of work.


>> Some of this is due the fact that we are not able to use mysql sepecific
>> features, because other dbs don´t have them. (INSERT ... ON DUBLIKATE
>> KEY UPDATE ...)
>
> That may help a bit, but still it should be able to handle more than you
> describe.

sure it does ... but this is still a performance improvement, because
no doubt this is read and written a lot faster to and from memcache then 
to a DB on a different host.

cheers

chris

>