[TYPO3-v4] Core performance for 4.6 and beyond

[Jigal van Hemert]

Hi,

On 8-2-2011 11:05, Christian Kuhn wrote:
> Sure! Don't get me wrong, Jigal: I love mysql for its performance and
> I'm not a bloody beginner in optimizing mysql. In fact the core queries
> are already optimized pretty well, in usual operation there are no wrong
> indexes or evil queries. We're running sites with up to 10k queries per
> second at peak time on mysql and it takes quite some time to get the
> configuration right after launch, to locate and fix slow queries of
> extensions and to optimize mysql to specific hardware (mostly memory)
> capabilities.

You're a pretty rare breed in this area! :-) Most people just want to 
run things "out of the box". Configuring MySQL to the specific needs of 
an installation can be quite a task and obviously you have what it takes 
to squeeze the last bit of performance out of a server.

Judging by the things one comes across in several installations it's 
sometimes a miracle that the website actually runs at all.

> Still, mysql tends to have problems with
> bigger text/blob fields: We occasionally see slow inserts in those
> bigger cache tables which handle page tables with ~0,5MB of html data
> per row and I've not found a way to change that on query, index or mysql
> config level.

You probably know all about how the different engines in MySQL handle 
their storage space and how it should be maintained. It can't be that 
these slow inserts happen when InnoDB has to expand the storage space or 
when it has to rotate logs?

> Additionally mysql (actually not mysql but more the
> underlying disks / storage systems) really gets problems with very high
> insert load (even if innodb is configured to speed up there at the cost
> of integrity) and in general mysql slows down if table space doesn't fit
> into memory anymore.

MySQL simply runs best if the database fits in memory. A disk bound 
situation is quite a problem. It may help a bit to use InnoDB storage 
space on a per table basis. As InnoDB stores the indexes inside the same 
file as the data it needs to have both in memory for fast processing. A 
single storage space which cannot fit in memory will make the whole 
database disk bound. It doesn't help either that you can't reduce the 
InnoDB storage space anymore like you can with MyISAM tables.

As you probably saw in different installations, table configurations 
which work quite well for small tables don't work well for large table, 
and vice versa. It's hard to make a situation where performance is 
optimal for small sites and really large sites.

-- 
Kind regards / met vriendelijke groet,

Jigal van Hemert
skype:jigal.van.hemert
msn: jigal at xs4all.nl
http://twitter.com/jigalvh