Uni Tübingen

Hardware
* KVM Virtualisierung (Webserver+DB)
* 2x 4 Prozessoren
* 32 GB RAM
* iSCSI SAN (NetApp)
 
Software
* Applikations-Software: PHP - Installierter PHP Cache: Zend Opcode Cache
* Web-Server: Apache
* Datenbank: MySQL
* Betriebssystem: OpenSuse 12.3
 
Datenbank
* MyISAM / InnoDB Tabellen gemäß Performance Wiki
 
 
###### Mysql config is split into separate files  #########
 
!includedir /etc/mysql/
 
###### server.cnf #########
 
[mysqld]

## Speed up connection. Name resolve isn't needed and can be dissabled.
## Attention: don't use hostnames in GRANTs - they won't work
skip_name_resolve


## Set the max concurrent connection count
max_connections = 450


## How many threads should be keept in cache to handle new connections.
## Observe threads_created for a good estimation.
thread_cache_size = 50


## increase the max connection errors
## should be greater than default (10) to prevent falsely block of LB-Hosts
max_connect_errors = 1000000


## The maximum size of one packet or any generated/intermediate string.
## default: 1M, recommendation: 4-16M
max_allowed_packet    = 8M


## The default of 192KB (256KB for 64-bit systems) is large enough for normal operation.
## If the thread stack size is too small, it limits the complexity of the SQL statements
## that the server can handle, the recursion depth of stored procedures, and other memory-consuming actions.
thread_stack        = 256K


## The number of seconds the server waits for activity on a noninteractive connection before closing it
## default: 28800, recommendation: much smaller number
wait_timeout    = 3600


## BufferPool to sort key/value-pointer for seq. read.
## Reduces random writes and helps contention.
read_rnd_buffer_size = 8M


## If you see many Sort_merge_passes per second in SHOW GLOBAL STATUS output,
## you can consider increasing the sort_buffer_size value to speed up
## ORDER BY or GROUP BY operations that cannot be improved with query
## optimization or improved indexing.
sort_buffer_size = 8M


## The amount of memory allocated for caching query results.
## The default value is 0, which disables the query cache.
## enabled again by mhicke 01.12.2014
query_cache_size= 128M   
query_cache_limit = 4M
query_cache_type = 1      
query_cache_min_res_unit = 8192
low-priority-updates = 1  ## wirkt für Memory Tabellen und daher für query cache


## max open tables
## should be assigned generous - mysql only uses whats needed
## (observe: Status variable Opened_tables)
table_open_cache = 5000


## max open table defs
## should be at least #tables+10%
table_definition_cache = 5000

## max opened files
## important if you use innodb_file_per_table
## sometimes you have to increase your OS limit by adding the following to /etc/security/limits.conf
## mysql hard nofile 102400
## mysql soft nofile 102400
open_files_limit = 102400

# From Freiburg config; mhicke 01.12.2014
large_pages = 1
 
 
 
 
 
###### engine_innodb.cnf #########
 
[mysqld]
 
## Only valid for MYSQL 5.5
## values 2-8 (default 1)
## indicator: numactl --hardware
## (mapping cpu-ram)
innodb_buffer_pool_instances=1


## InnoDB Buffer Pool, should be about 80% of RAM (if only InnoDB is used)
innodb_buffer_pool_size=20000M


## IF log_file_size is changed the old files have to be deleted
## In MySQL 5.6, if innodb_log_file_size is changed between restarts
## then MySQL will automatically resize the logs to match the new desired size
## during the startup process
innodb_log_file_size=400M
innodb_log_files_in_group=2


## Reduces writes and helps contention
## By monitoring log sequence number and value up to which logs have been flushed (SHOW INNODB STATUS)
## you can check if your innodb_log_buffer_size is optimal if you see more than 30% of log buffer size
## being unflushed you may want to increase it.
## values 4M-128M (16) (default 8M)
innodb_log_buffer_size=32M


## Controls the balance between strict ACID compliance for commit operations,
## and higher performance that is possible when commit-related I/O operations
## are rearranged and done in batches.
## 1: Full ACID. Jedes Commit schreibt den log_buffer in das log_file und löst ein flush to disk aus
## 0: Der log_buffer wird im Sekundentakt in das log_file geschrieben und darauf ein flush to disk ausgelöst.
##    Ein Commit löst dabei keine Aktion aus.
##    Im Fehlerfall kann bis zu eine Sekunde Datenverlust auftreten. Hierdurch ist jedoch eine
##    schnellere Antwortzeit möglich.
## 2: Der log_buffer wird nach jedem Commit in das log_file geschrieben. Darauf wird jedoch kein flush to disk ausgelöst.
##    Ein flush to disk wird im Sekundentakt auf dem log_file ausgelöst.
## Empfehlung: 0 oder 2
innodb_flush_log_at_trx_commit=0


## With improvements to the InnoDB engine, it is recommended to allow the engine
## to control the concurrency by keeping it to default value (which is zero).
## If you see concurrency issues, you can tune this variable.
## A recommended value is 2 times the number of CPUs plus the number of disks.
## It’s dynamic variable means it can set without restarting MySQL server.
#innodb_thread_concurrency = 0


## useful for table shrinking options, among other things
## ON by default starting with MySQL 5.6
innodb_file_per_table = 1

# default 300, would need to open files every time; mhicke 01.12.2014
innodb_open_files = 40000


## Needed for Tables with more than 8-10 TEXT/BLOB (Pointer) Cols
innodb_file_format=barracuda


## This is analogous to MySQL’s sql_mode, which controls what SQL syntax MySQL will accept,
## and determines whether it will silently ignore errors, or validate input syntax and data values.
## Note that there is no strict mode with the built-in InnoDB,
## so some commands that execute without errors with the built-in InnoDB will generate errors with the InnoDB Plugin,
## unless you disable strict mode.
innodb_strict_mode=1


## The InnoDB shutdown mode. By default, the value is 1,
## which causes a "fast" shutdown (the normal type of shutdown).
## If the value is 0, InnoDB does a full purge and an insert
## buffer merge before a shutdown.
## These operations can take minutes, or even hours in extreme cases.
innodb_fast_shutdown=0


## Number of read/write threads
## On systems with more disk a slight increase could result in better performance
## default: 4 threads
innodb_write_io_threads = 6
innodb_read_io_threads = 6


## IO Capacity of HDD - sould be adjusted for SSDs
## How many IOPs can be handled per sec.
## 15k rpm: 180-210 IOPS
## 10k rpm: 130-150 IOPS
## 7200 rpm: 80-100 IOPS
## 5400 rpm: 50-80 IOPS
## (default: 200)
innodb_io_capacity=800


## bypasses the OS caching systems and hits the storage direct
## should be benchmarked for every system. Could slow down the system (especially on SANs)
# innodb_flush_method = O_DIRECT
 
 
 
###### engine_myisam.cnf #########
 
[mysqld]
 
## Accommodate indexes for temporary tables which are created on disk
## Otherwise used for MyISAM Tables
## 20-40% of RAM
key_buffer_size        = 500M


## The size of the buffer that is allocated when sorting MyISAM indexes
## during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.
myisam_sort_buffer_size = 16M


## Allow MySQL to insert new rows at the end of a table while select statements are
## executed on the table.
##  0/never: allow never
##  1/auto: allow if the table has no holes in the data file (deleted rows)
##  2/always: allow even if there are holes
concurrent_insert = 2


## This replaces the startup script and checks MyISAM tables if needed
## the first time they are touched
myisam-recover         = BACKUP


## Do not use external locking (system locking). This affects only MyISAM table access.
## External locking is the use of file system locking to manage contention for MyISAM database
## tables by multiple processes. External locking has been disabled by default since MySQL 4.0.
skip-external-locking
 
 
 
###### engines.cnf #########
 
[mysqld]
 
## Disable not needed storage engines
#skip-blackhole
#skip-archive
#skip-federated
 
 
###### encoding.cnf #########
 
[mysqld]

## UTF-8 sollte in dieser Version bereits default sein
## just in case

collation_server = utf8_unicode_ci
character_set_server = utf8
 
 
###### logging.cnf #########
 
[mysqld]

log-error=/var/log/mysql/mysqld.log

## Enable binary logging. The server logs all statements that change data to the binary log,
## which is used for backup and replication.
##log-bin = /db-backup/binlog/mysql-bin


## This variable sets the binary logging format, and can be any one of STATEMENT, ROW, or MIXED.
#binlog-format = ROW


## Normally a slave does not log to its own binary log any updates that are received from a master server.
## This option tells the slave to log the updates performed by its SQL thread to its own binary log.
##
## NEEDED FOR gtid-mode=ON: log-slave-updates = TRUE
#log-slave-updates = TRUE


## If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk
## (using fdatasync()) after every sync_binlog writes to the binary log.
## There is one write to the binary log per statement if autocommit is enabled, and one write per transaction otherwise.
sync_binlog = 0

## Attention: Binlogs could raise fast. Because of this the hold-back time shouldn't be to long.
## With daily backups one day should be enough
##expire_logs_days        = 2


## Split the binlog in smaller pieces
max_binlog_size         = 500M


####### FILTER #######################################

#binlog_do_db        = include_database_name
#binlog_ignore_db    = include_database_name
 
 
.htaccess
-----------------------------
AddOutputFilter DEFLATE js css htm html xml php
ExpiresActive On
ExpiresByType image/gif "access plus 48 hours"
ExpiresByType image/jpg "access plus 48 hours"
ExpiresByType image/jpeg "access plus 48 hours"
ExpiresByType image/png "access plus 48 hours"
ExpiresByType text/js "access plus 3 hours"
ExpiresByType text/x-js "access plus 3 hours"
ExpiresByType text/css "access plus 3 hours"
 
 
server-tuning.conf
----------------------
KeepAlive On
MaxKeepAliveRequests 200
KeepAliveTimeout 15
<IfModule prefork.c>
[...]
ServerLimit 500
MaxClients 500
[...]
</IfModule>
<IfModule worker.c>
MaxClients 500
</IfModule>
 
 
Anzahl Benutzerkonten
 
Davon eingeloggt in den letzten … (Stand: 24.06.2010)
 
… 7 Tagen: 6.857
… 30 Tagen: 10.450
… 90 Tagen: 15.948
… 365 Tagen: 22.279