Performance tuning

From TYPO3Wiki

List Projects	The Performance tuning for TYPO3 and database-project list pages	See Current Project Members, Wishlist
		you can help if you like!

<< Back to Administrators page

[edit]

To get a well working TYPO3 site many things have to be considered.

TYPO3 uses three applications to run - Apache webserver, PHP and MySQL database and of course the OS. Each of these applications and the OS have large sets of options.

To have a fast enviroment it is important to calibrate each of these three applications to run as a strong team.

Beside the information on this page, there's an article about Testing and tuning TYPO3 performance in the article section of typo3.org.

Contents 1 First Steps 1.1 PHP Opcode Caching 1.2 Webserver proxy caching or static file caching 2 Testing the performance 3 OS tuning 3.1 Linux 4 MySQL Tuning 4.1 Tools to analyse your MySQL performance 4.1.1 Build-in tools 4.1.2 3rd party tools 4.2 MySQL query cache 4.2.1 What's the query cache good for? 4.2.2 How to use it? 4.2.3 Benchmarking your query cache 4.3 Optimizing TYPO3 database structures 4.3.1 Some initial thoughts 4.3.2 Using MySQL indexes effectively 5 PHP Tuning 5.1 Output Buffering 5.2 Informal Tests with Eaccelerator 5.3 Testing TYPO3 caching vs. [FE][pageCacheToExternalFiles] 6 Reverse proxies 7 Articles on performance and TYPO3 8 TYPO3 performance Extensions 9 Related discussions in the TYPO3 community 10 Relations 10.1 Links 11 Current Project Members 12 Wishlist

First Steps

Some of the optimization tasks listed in this article might take several days and require fine tuning and testing. Before going that way you might want to try two basic things that improve your website performance regardless of setup. To install them you might need shell access to your server, and in some cases superuser rights.

PHP Opcode Caching

Whenever a php script is run, the script is parsed into opcodes that are later run. eAccelerator is a PHP extension that caches this opcodes and can greatly improve your rendering performance.

Webserver proxy caching or static file caching

No matter how much tuning you do, each request for a typo3 page involves several queries and heavy php logic. If your site has many static pages that are rendered the same way several times, even with typo3's cache you won't get a similar performance than static html files. Thats why you should either use apache's proxy cache, or the static file cache extension - * nc_staticfilecache (nc_staticfilecache) (contact: netcreators)

Testing the performance

In order to understand the performance of your site and to analyze any changes you make you should know "ab", the benchmarking tool from apache. By running "ab -n 1000 -c 100 http://www.mysite.com/" you are asking ab to make 1000 requests, in 10 waves of 100 simultaneous. Checking the processor, memory and hard disk utilization while performing ab tests might help you understand where to continue tuning.

OS tuning

Linux

• Turning off accesstime recording can reduce load on a machine a lot. Kernel.org reduction of load by 50%.

Linux has a special mount option for file systems called noatimethat can be added to each line that addresses one file system in the /etc/fstab file. If a file system has been mounted with this option, reading accesses to the file system will no longer result in an update to the atime information associated with the file like we have explained above. The importance of the noatime setting is that it eliminates the need by the system to make writes to the file system for files which are simply being read. Since writes can be somewhat expensive, this can result in measurable performance gains. Note that the write time information to a file will continue to be updated anytime the file is written to. In our example below, we will set the noatime option to our /chroot file system.

Edit the fstab file vi /etc/fstab and add in the line that refer to /chrootfile system the noatime option after the defaults option as show below:

/dev/sda9 /chroot ext3 defaults,noatime 1 2

You need not reboot your system for the change to take effect, just make the Linux system aware about the modification you have made to the /etc/fstab file. This can be accomplished with the following commands:

[root@typo3server] /#mount -oremount /chroot/

Then test your results with the flowing command: mount

If you see something like: /dev/sda9 on /chroot type ext3 (rw,noatime), congratulations!

• Tune disk performance using hdparm. In some cases this can improve disk throughput by a factor of 6 - 10! HOWTO use hdparm (gentoo-wiki.com) .
• Linux tuning guide

MySQL Tuning

Tools to analyse your MySQL performance

Searching for "mysql optimization", you get about 8.000.000 google hits at the moment. A lot of those sites share good knowledge and experience. But I didn't find a site, which provided some out of the box solutions to solve my personal performance problems. "So what?" you might say, "Go snap your toolbox and search for the bottleneck". Exactly! MySQL optimization is not trivial and demands benchmarking while tuning in almost any case. Keep that in mind, when websites advise you how to tune your server parameters. This section is about tools to analyse MySQL performance.

Build-in tools

MySQL itself comes with some very good tools:

• Slow Query Log

Helpful to find slow and therefore sometimes CPU-consuming queries. To log all queries which take more than two seconds, add the following lines to your MySQL configuration file:

long_query_time  =  2
log-slow-queries = /var/log/mysql-slow.log

Get an overview about the number of slow queries with:

mysql> SHOW STATUS LIKE '%slow_queries%';

• MySQL status query

Find out lots of statistics, counters, ... by simply submitting the following query:

mysql> SHOW STATUS;

You can gather tons of information with the SHOW query. The MySQL Reference Manual is a good starting point to get an overview.

• EXPLAIN

enables you to analyse how MySQL processes a single query. Find out what indizes are used, what kind of optimization MySQL did, ... For example:

mysql> EXPLAIN SELECT DISTINCT tt_content.pid FROM tt_content
    -> WHERE tt_content.deleted=0 ORDER BY tt_content.tstamp DESC LIMIT 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tt_content
         type: index
possible_keys: NULL
          key: parent
      key_len: 4
          ref: NULL
         rows: 3483
        Extra: Using where; Using temporary; Using filesort
1 row in set (0.00 sec)

The chapter Optimizing Queries with EXPLAIN in the MySQL docs will teach you how to read the EXPLAIN output.

3rd party tools

• mytop

mytop is a nice tool to monitor your DB utilization, similar to

mysql> SHOW FULL PROCESSLIST;

but with the ability to constantly refresh the output (like top for unix).

• mysqlreport

Those guys from hackmysql.com provide a set of console tools to analyse MySQL. They have also good case studies, a very detailed documentation and a section about query optimization. Here's two of them:

mysqlreport prompts clearly arranged status values of SHOW STATUS (memory usage, number of queries done, ...)

mysqlsla analyses mysql logfiles, e.g. to easily find slow-queries. (And I bet that 99% of all your slow queries are caused by indexed_search... ;-)

MySQL query cache

What's the query cache good for?

With the query cache turned on, MySQL stores SELECT queries and their results inside the query cache (in the memory). Whenever the same query is requested again, MySQL serves the result directly from the query cache. This cuts down the response time for frequently used queries, because results are directly served from the memory instead of your phlegmatic harddisk.

How to use it?

Set Query cache to a value greater than 16M

To find out if query cache is available for your mysql server try:

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

If you get a return value of "Yes" query caching is available and you may configure it.

Edit your MySQL configuration file (e.g. /etc/mysql/my.conf on Debian, /etc/my.conf on RH types or my.ini on Win) and add the following lines at section [mysqld]:

# Enable caching
query_cache_type = 1
# Set query cache to 32 MB
query_cache_size = 33554432

If you have lots of different queries and lots of memory, choose a higher value.

Benchmarking your query cache

One way to measure the effeciveness of your query cache, is the Hit:Insert ratio. Compare the amount of queries, which have been served from the query cache (Hits) with the amount of queries that have been inserted into the query cache (Inserts). To get this value, use mysqlreport (as mentioned above) or follow the example below (after some warm up time of your database):

mysql> SHOW STATUS LIKE '%Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| ...                     |          |
| Qcache_hits             | 94192865 |
| Qcache_inserts          | 12896776 |
| ...                     |          |
+-------------------------+----------+

mysql> SELECT 94192865 DIV 12896776;
+-----------------------+
| 94192865 DIV 12896776 |
+-----------------------+
|                     7 |
+-----------------------+

The ratio in this example is 7:1, which means seven of eight SQL SELECT statements are served from the cache. The higher this ratio is, the more effective is your query cache.

Spending more memory for the query cache might lead to a lower ratio, because rarely used queries have better chance to stay inside the cache without being hit. Once again: tuning MySQL is not trivial and needs some good reading and time for benchmarking.

More docs on the query cache can be found in the developer zone at mysql.com: http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html http://dev.mysql.com/doc/refman/5.0/en/query-cache.html

or follow the explanations in the mysqlreportguide

Optimizing TYPO3 database structures

Some initial thoughts

MySQL database performance is mainly dependend on

1. Available memory
2. Speed of disc access
3. Size of tables
4. Indexes

As memory and disc access are not really configurable through Typo3 we should focus on table size and indexes.

Using MySQL indexes effectively

There is a thread on the typo3-dev mailinglist discussing usage of indexes. The subject is "Typo3 Database design question (optimization / indices)": http://lists.netfielders.de/pipermail/typo3-dev/2006-March/016782.html

(Temporary annotation: It seems the page has moved from http://lists.netfielders.de/pipermail/typo3-dev/2006-March/016782.html to http://lists.netfielders.de/pipermail/typo3-dev/2006-March/016967.html )

The most obvious columns to use for indexes are hidden and deleted as these are used quite often via tslib_cObj::enableFields() to determine all visible non deleted elements.

Some simple setup of indexes may look like:

(mshigorin: warning, the ALTERs that didn't fall apart would rather hurt my 4.1.5 installation: MySQL 5.0.46 query cache would suddenly lose hit ratio, and ab -n 10 -c 2 response times would *grow* for some 15%)

(ralf@schwedler.com: caution! this is definetely the wrong way to optimize indices. MySQL will use at most one single index per table access (several tables might be accessed by a query containing JOIN, UNION etc.); if the MySQL query optimizer could benefit from several indexed columns, this must be supported by creating a single index on multiple columns - several of these beasts might be necessary to support different queries.)

ALTER TABLE `be_groups` ADD INDEX ( `deleted` ( 1 ) ) ;
ALTER TABLE `be_groups` ADD INDEX ( `hidden` ( 1 ) ) ;

ALTER TABLE `cache_hash` ADD INDEX ( `hash` ( 4 ) ) ;


ALTER TABLE `fe_groups` ADD INDEX ( `deleted` ( 1 ) ) ;
ALTER TABLE `fe_groups` ADD INDEX ( `hidden` ( 1 ) ) ;

ALTER TABLE `fe_users` ADD INDEX ( `deleted` ( 1 ) ) ;
ALTER TABLE `fe_users` ADD INDEX ( `disable` ( 1 ) ) ;


ALTER TABLE `pages` ADD INDEX ( `deleted` ( 1 ) ) ;
ALTER TABLE `pages` ADD INDEX ( `hidden` ( 1 ) ) ;
ALTER TABLE `pages` ADD INDEX ( `doktype` );
ALTER TABLE `pages` ADD INDEX ( `sorting` );

ALTER TABLE `pages_language_overlay` ADD INDEX ( `hidden` ( 1 ) ) ;

ALTER TABLE `sys_domain` ADD INDEX ( `hidden` ( 1 ) ) ;
ALTER TABLE `sys_domain` ADD INDEX ( `sorting` ) ;

ALTER TABLE `sys_filemounts` ADD INDEX ( `hidden` ( 1 ) ) ;
ALTER TABLE `sys_filemounts` ADD INDEX ( `deleted` ( 1 ) ) ;

ALTER TABLE `sys_language` ADD INDEX ( `hidden` ( 1 ) ) ;

ALTER TABLE `sys_note` ADD INDEX ( `deleted` ( 1 ) ) ;

ALTER TABLE `sys_template` ADD INDEX ( `hidden` ( 1 ) ) ;
ALTER TABLE `sys_template` ADD INDEX ( `sorting` ) ;
ALTER TABLE `sys_template` ADD INDEX ( `deleted` ( 1 ) ) ;

ALTER TABLE `tt_content` ADD INDEX ( `deleted` ( 1 ) ) ;
ALTER TABLE `tt_content` ADD INDEX ( `hidden` ( 1 ) ) ;
ALTER TABLE `tt_content` ADD INDEX ( `sorting` );

Tasks:

1. Verify that these changes speed up average db access.
2. Find more (and better) candidates for indexes
3. Where can we benefit from compund indexes?

It appears that this index :

ALTER TABLE `be_groups` ADD INDEX ( `deleted` ( 1 ), `hidden` ( 1 ) ) ;

is more "powerfull" than this two indexes

ALTER TABLE `be_groups` ADD INDEX ( `deleted` ( 1 ) ) ;
ALTER TABLE `be_groups` ADD INDEX ( `hidden` ( 1 ) ) ;

(ralf@schwedler.com: this particular index would be useful fpr something like SELECT * FROM be_users WHERE deleted=0 AND hidden=0 without any additional WHERE-condition. To optimize for additional WHERE-terms, the corresponding columns should be included in the index.)

PHP Tuning

Output Buffering

To increase IIS' performance make sure to set output_puffering=On in php.ini. Changed from output_buffering=4096 to output_buffering=On increased throughput by factor 5.

apache benchmark (ap -n 100 -c10) before and after:

• Requests per second: 0.48 [#/sec] (mean)
• Requests per second: 2.14 [#/sec] (mean)

See http://bugs.php.net/bug.php?id=28524

Informal Tests with Eaccelerator

By Patrick Gaumond

I've done a small test to see the direct influence of few tricks to see their impact. So you see what effect an accelerator has.

	T3 Standard First hit	T3 Standard Average	T3 Cache + EA First hit	T3 Cache +EA Average	T3 Cache + EA + HeaderCaching First hit	T3 Cache + EA + HeaderCaching Average
Page1 Zen2col	1152	332	2309	90	0	0
Page2 NoCache	894	900	408	375	397	400
Page3 tt_news	2598	350	680	88	0	0
Page4 Nomad Template	935	325	439	81	0	0

Time is in milliseconds (ms.).

Pseudo-Server

Windows XP SP2, 1GB RAM
IBM Thinkpad T30
Apache 2.054 Windows, PHP 5.04
Eaccelerator 0.93 (EA in the chart)

Notes

The first 3 pages done with the template Zen2Col
Page 4 is made with the Nomad template
First column is always first hit
The time in millisecond come from TYPO3 debug parsetime
Cache emptied and Apache restarted between each configuration changes. Eaccelerator not restarted from page1 to page2, etc.
Simulatestatic =1
The client asking for pages was on another Windows XP PC running Firefox.

Attention: This was the first time I tried using Cache-Headers from 3.8 and I probably had an Apache misconfiguration. This page will be updated once I'm more experienced with config.sendCacheHeaders=1 ...

Few observations

TYPO3 internal cache permit to save 600 ms. (2/3) of the processing time. See page 1 vs. 2.

Eaccelerator cut the processing time to another 2/3 (330 ms. to 90 ms.) so even the non-T3-cached page2 goes from 900 to 375 ms.

Quick conclusion is that Eaccelerator is a must ! (note: In the case of Linux (debian) its not stable (still beta); at least for PHP4 Turck MMcache is still very robust) (eAccelerator Windows binaries)

---

Testing TYPO3 caching vs. [FE][pageCacheToExternalFiles]

By Patrick Gaumond Using the same hardware as the previous test and ApacheBench with this line:
ab -n 100 -c 10 http://127.0.0.1/typo34rc2/index.php

Note: I've received additional info of the feature by Kasper and it was not meant to enhance individual page rendering time. The feature was created mostly to avoid the gigantic cache of typo3.org That's it: 80 000 records (1GB) MySQl table. In future realease it will be disable by default.

	Request per seconds (mean) Higher is better	Time per request (mean, in ms) Lowest is better
[FE][pageCacheToExternalFiles] = 0 [FE][compressionLevel] = 3 Eaccelerator = Yes	9.54	104.8
[FE][pageCacheToExternalFiles] = 1 [FE][compressionLevel] = 3 Eaccelerator = Yes (same settings as position #4)	9.34	107.1
[FE][pageCacheToExternalFiles] = 1 [FE][compressionLevel] = 0 Eaccelerator = Yes	9.19	108.7
[FE][pageCacheToExternalFiles] = 1 [FE][compressionLevel] = 3 Eaccelerator = Yes	7.07	141.5
No TYPO3 cache (index.php?no_cache=1) [FE][pageCacheToExternalFiles] = 1 [FE][compressionLevel] = 3 Eaccelerator = Yes	2.66	376.6
[FE][pageCacheToExternalFiles] = 1 [FE][compressionLevel] = 3 Eaccelerator = NO	2.34	427.4
[FE][pageCacheToExternalFiles] = 0 [FE][compressionLevel] = 3 Eaccelerator = NO	2.34	426.6
[FE][pageCacheToExternalFiles] = 1 [FE][compressionLevel] = 0 Eaccelerator = NO	2.24	447.1

So it looks that at least on Windows, Apache 2 and TYPO3 4.0RC2, the new [FE][pageCacheToExternalFiles] doesn't have an impact on a small installation like the one used for my test.

If you have any comments or did similar tests with different results, please write to me at patrick[at]typo3quebec.org .

Reverse proxies

Helps to take load from the webserver, which serves dynamic content (TYPO3). Nice example is nginx.

To solve logging inconsistencies, try mod_rpaf for apache 2 or mod_realip for apache 1.3

If you are really poor, you might want to take a look at the poor man's proxy extension: nc_staticfilecache (nc_staticfilecache) (contact: netcreators). It saves TYPO3 cacheable pages to static html files and serves these directly using mod_rewrite.

to be continued...

Articles on performance and TYPO3

• http://typo3.org/fileadmin/t3n/articles/t3n_60_pi_s_with_typo3.pdf
• http://typo3.org/development/articles/testing-and-tuning-typo3-performance/

This article is also available in german: http://www.yeebase.com/fileadmin/t3n/archiv/06-02/t3n_06-02_frisiert_und_aufgebohrt.pdf

• http://typo3.org/development/articles/using-cache-control-headers-in-typo3/

TYPO3 performance Extensions

• dmc_highperformance (dmc_highperformance) (contact: saloon12yrd)
• Static File Cache - Extension Home, fl_staticfilecache (fl_staticfilecache) (contact: spyker)
• nc_staticfilecache (nc_staticfilecache) (contact: netcreators)
• AutoPublish (mda_autopublish) (contact: haeusler)
• kb_quickbe (kb_quickbe) (contact: kraftb)
• kb_quickfe (kb_quickfe) (contact: kraftb)

Related discussions in the TYPO3 community

• [TYPO3-dev] Testing TYPO3 caching vs. [FE][pageCacheToExternalFiles] [1] TYPO3-dev

About: Comparison of DB and filesystem caching within TYPO3. (Date: 3-4/2006)

• [TYPO3-dev] Typo3 Performance [2] TYPO3-dev

About: DB indices for TYPO3 tables, Description of Celkos nested sets for the Pagetree, testing+tuning tips. (Date: 2-3/2007)

• [TYPO3-dev] Indexed Search Performance Improvement [3] TYPO3-dev

About: Using the FULLTEXT feature of (MySQL) MyISAM tables for indexed_search. (Date: 2-3/2007)

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

About: DB indices for TYPO3 tables. (Date: 3/2006)

• [TYPO3-dev] Speeded up TYPO3 by 500% :) [5] TYPO3-dev

About: Bernhard Kraft introducing his extension kb_quickindex, an experiment which adds basic file-caching support to index.php

• ...

Relations

• MySQL configuration - the basics: prepare for installation, settings, rights management
• TYPO3.org - improvements#better performance

Links

• http://www.typo3performance.org/ (unavailable!!)

Current Project Members

Wishlist

• List of typical performance traps, regarding TYPO3 e.g. $TSFE->set_no_cache().
• I would like to see some in-depth studies, how to
  • find different types of bottlenecks and
  • how to get rid of them

For example, find slow PHP statements with profiler, slow queries DB with logs, ...