Apache2+PHP+MySQL beschleunigen

Apache, Lighttpd, nginx, Cherokee
inchez
Posts: 65
Joined: 2007-10-01 20:17

Apache2+PHP+MySQL beschleunigen

Post by inchez » 2007-10-31 15:35

Guten Tag,

ich betreibe auf einem Server mit 4GHZ CPU Leistung, 2GB Ram unde Debian Etch als OS einen Apache2(worker)+MySQL+PHP(fastcgi).
in Verbindung mit ispCP.

Darauf läuft momentan eine Seite die am Tag etliche Tausend Besucher hat, 50GB Traffic im Schnitt am Tag, denke ihr könnt euch die ausmaße vorstellen.

Nun ist das alles leider sehr langsam, 15-29 sec. bis Seitenaufbau vollendet wurde.

Nun kann das ja eigl nicht sein das der Server nur diese Seite fährt.

Also wollte ich fragen ob ihr mir v.l. mal so Faustregelmässig die besten Einstellungen für den Apache2 und den SQL postet, so das ich dann mal gucken kann wo er gut läuft.

Maximum headers in request
Maximum request header size
Maximum request line size
Maximum concurrent requests
Maximum requests per server process
Maximum spare threads
Minimum spare threads
Initial server processes
Threads per child process

Davon so die Pi-mal-Daumen Optimalwerte z.b. und für den SQL Server wäre echt nice.

Sollen 2 Seiten diesen Ausmaßes später drauf laufen, also ca 100GB am Tag Traffic, Visits ist schwer zu schätzen.

Habe schon varnish installiert, aber hat nur minimal was gebracht.

Momentan sind bloß 200MB Ram in bebrauch und er hat ne Load von 0.18, deswegen wudnert mich dieses langsam sein, wie verteile ich die Last effektiver auf die Hardware.

Gruß :)

thelaw
Posts: 53
Joined: 2002-07-30 18:53
Location: Salzgitter

Re: Apache2+PHP+MySQL beschleunigen

Post by thelaw » 2007-10-31 15:47

Wie hoch ist denn die Load des Servers?
Ein anderer Ansatz und ein häufiges Problem: Ist die Anwendung vernünftig programmiert? Wie viele SQL-Abfragen pro Seitenaufruf, sind Indizes vernünftig gesetzt?

inchez
Posts: 65
Joined: 2007-10-01 20:17

Re: Apache2+PHP+MySQL beschleunigen

Post by inchez » 2007-10-31 16:03

wie ich schon sagte 0.18.

Jop, ist ein vBulletin, denke mal die Programmierer davon haben ihre Kunst im griff.

User avatar
Joe User
Project Manager
Project Manager
Posts: 11599
Joined: 2003-02-27 01:00
Location: Hamburg

Re: Apache2+PHP+MySQL beschleunigen

Post by Joe User » 2007-10-31 18:13

AFAIK ist vB recht SQL intensiv, Indexe sind wohl weitestgehend optimal gesetzt, aber es gibt noch einige unoptimierte Queries.
Wie sieht Deine my.cnf aus und welche MySQL Version setzt Du ein? Schon darüber nachgedacht, den SQL-Server auszulagern?

flo
RSAC
Posts: 2297
Joined: 2002-07-28 13:02
Location: Berlin

Re: Apache2+PHP+MySQL beschleunigen

Post by flo » 2007-10-31 18:16

evtl. fastcgi gg. mod_php tauschen? Wenn der Server letztlich nur eine Website ausliefert und sonst keiner zugreift, wäre das doch möglich!?

Interessant wäre auch, die Cache-Auslastung zu erfahren.

inchez
Posts: 65
Joined: 2007-10-01 20:17

Re: Apache2+PHP+MySQL beschleunigen

Post by inchez » 2007-11-01 01:43

Code: Select all

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port      = 3306
socket      = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice      = 0

[mysqld]
#
# * Basic Settings
#
user      = mysql
pid-file   = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir      = /usr
datadir = /var/lib/mysql
tmpdir      = /tmp
language   = /usr/share/mysql/english
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer      = 1M
max_allowed_packet   = 8M
thread_stack      = 64K
thread_cache_size   = 8
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 8M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
#log      = /var/log/mysql/mysql.log
#
# Error logging goes to syslog. This is a Debian improvement :)
#
# Here you can see queries with especially long duration
#log_slow_queries   = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
#server-id      = 1
log_bin         = /var/log/mysql/mysql-bin.log
# WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian!
expire_logs_days   = 10
max_binlog_size         = 100M
#binlog_do_db      = include_database_name
#binlog_ignore_db   = include_database_name
#
# * BerkeleyDB
#
# Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.
skip-bdb
set-variable = max_connections=1400
set-variable = max_connections=1400
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# You might want to disable InnoDB to shrink the mysqld process by circa 100MB.
#skip-innodb
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet   = 16M

[mysql]
#no-auto-rehash   # faster start of mysql but no tab completition

[isamchk]
key_buffer      = 16M

#
# * NDB Cluster
#
# See /usr/share/doc/mysql-server-*/README.Debian for more information.
#
# The following configuration is read by the NDB Data Nodes (ndbd processes)
# not from the NDB Management Nodes (ndb_mgmd processes).
#
# [MYSQL_CLUSTER]
# ndb-connectstring=127.0.0.1


#
# * IMPORTANT: Additional settings that can override those from this file!
#
!includedir /etc/mysql/conf.d/


das ist die my.cnf



Kann mir jemand v.l. die default einstllungen des apache 2 posten von den parametern die oben gelistet sind. habd a dummerweise rumgespielt
und jetzt ists noch instabiler.


mfg

User avatar
Joe User
Project Manager
Project Manager
Posts: 11599
Joined: 2003-02-27 01:00
Location: Hamburg

Re: Apache2+PHP+MySQL beschleunigen

Post by Joe User » 2007-11-01 09:48

Apache:

Code: Select all

Timeout 300
KeepAlive On
KeepAliveTimeout 15
MaxKeepAliveRequests 100
<IfModule mpm_prefork_module>
    StartServers          5
    MinSpareServers       5
    MaxSpareServers      10
    MaxClients          150
    MaxRequestsPerChild 500
</IfModule>
<IfModule mpm_worker_module>
    StartServers          2
    MaxClients          150
    MinSpareThreads      25
    MaxSpareThreads      75
    ThreadsPerChild      25
    MaxRequestsPerChild 500
</IfModule>

MySQL:

Code: Select all

[client]
character_sets_dir        = /usr/share/mysql/charsets
default_character_set     = latin1
port                      = 3306
socket                    = /var/run/mysqld/mysqld.sock

[mysql]
character_sets_dir        = /usr/share/mysql/charsets
default_character_set     = latin1
prompt                    = u@h [d]>_
no_auto_rehash

[mysqladmin]
character_sets_dir        = /usr/share/mysql/charsets
default_character_set     = latin1

[mysqlcheck]
character_sets_dir        = /usr/share/mysql/charsets
default_character_set     = latin1

[mysqldump]
character_sets_dir        = /usr/share/mysql/charsets
default_character_set     = latin1
max_allowed_packet        = 32M
quote_names
quick

[mysqlimport]
character_sets_dir        = /usr/share/mysql/charsets
default_character_set     = latin1

[mysqlshow]
character_sets_dir        = /usr/share/mysql/charsets
default_character_set     = latin1

[isamchk]
character_sets_dir        = /usr/share/mysql/charsets
key_buffer_size           = 256M

[myisamchk]
character_sets_dir        = /usr/share/mysql/charsets
key_buffer_size           = 256M

[myisampack]
character_sets_dir        = /usr/share/mysql/charsets

[mysqld_safe]
err_log                   = /var/log/mysql/mysql.err
open_files_limit          = 32768

[mysqld]
character_sets_dir        = /usr/share/mysql/charsets
character_set_server      = latin1
default_character_set     = latin1
user                      = mysql
port                      = 3306
bind_address              = 127.0.0.1
socket                    = /var/run/mysqld/mysqld.sock
pid_file                  = /var/run/mysqld/mysqld.pid
log_error                 = /var/log/mysql/mysqld.err
basedir                   = /usr
datadir                   = /var/lib/mysql
tmpdir                    = /var/tmp
slave_load_tmpdir         = /var/tmp
language                  = /usr/share/mysql/english
#master_host               = <hostname>
#master_user               = <username>
#master_password           = <password>
#master_port               = 3306
log_bin                   = mysql-bin
server_id                 = 1
back_log                  = 50
sync_binlog               = 0
binlog_cache_size         = 1M
max_binlog_size           = 100M
expire_logs_days          = 7
slave_compressed_protocol = 1
lower_case_table_names    = 1
delay_key_write           = ALL
safe_user_create          = 1
myisam_repair_threads     = 1
skip_locking
skip_name_resolve
skip_external_locking
skip_show_database
key_buffer_size           = 512M
max_allowed_packet        = 32M
max_heap_table_size       = 64M
tmp_table_size            = 64M
table_cache               = 1024
query_cache_type          = 1
query_cache_size          = 64M
query_cache_limit         = 4M
thread_concurrency        = 8
thread_cache_size         = 12
max_connections           = 1200
max_connect_errors        = 20
ft_max_word_len           = 30
ft_min_word_len           = 3
local_infile              = 0
log_warnings              = 2
long_query_time           = 3
log_slow_queries          = slow-queries.log
#log_queries_not_using_indexes
#log_slave_updates
log_long_format
myisam_recover
skip-bdb
#skip_innodb
innodb_thread_concurrency       = 8
innodb_buffer_pool_size         = 1024M
innodb_additional_mem_pool_size = 16M
innodb_data_home_dir            = /var/lib/mysql
innodb_log_arch_dir             = /var/lib/mysql
innodb_log_group_home_dir       = /var/lib/mysql
innodb_data_file_path           = ibdata1:2000M;ibdata2:10M:autoextend
innodb_flush_method             = O_DIRECT
innodb_log_file_size            = 100M
innodb_log_buffer_size          = 8M
innodb_log_files_in_group       = 2
innodb_flush_log_at_trx_commit  = 1
innodb_max_dirty_pages_pct      = 90
innodb_lock_wait_timeout        = 120

[mysqlhotcopy]
interactive_timeout

!includedir /etc/mysql/conf.d/

Nach 48 Stunden lässt Du das tuning-primer.sh von http://day32.com/MySQL/ laufen und meldest Dich nochmal.

inchez
Posts: 65
Joined: 2007-10-01 20:17

Re: Apache2+PHP+MySQL beschleunigen

Post by inchez » 2007-11-01 15:34

hi,

danke für die configs, allerdings ist er unter denen auch total langsam.

ist echt unzumutbar für die surfer auf der seite.

hat jemand noch eine idee.

für 48h kann ich das nicht so lassen.

mfg