Best Apache2 + MySQL config for VPS Server [closed]

Question

I've bought a VPS Server with 1v core and 1GB ram, but yesterday I tried to host a website using Apache2 + MySQL... Everything went wrong!

I had around 400 unique vists and the server was dying, full CPU usage and full RAM usage I couldn't even open a top command to see what went wrong, so I moved the website to a normal shared hosting and everything is fine... But I want to host my PHP apps on my VPS server, because I have full control over it unlike a shared hosting...

My apache2, mysql, php etc conf is what it comes by default so maybe there's something wrong because 400 visitors isnt' a huge thing and I'm expecting to have around 6000 or even more...

Thanks!

my.cnf

#
# 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
lc-messages-dir = /usr/share/mysql
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        = 176.31.189.3
#
# * Fine Tuning
#
key_buffer      = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# 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.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size         = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * 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!
#
# * 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
innodb_use_native_aio = 0


[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

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

[isamchk]
key_buffer      = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

apache2.conf

# This is the main Apache server configuration file.  It contains the
# configuration directives that give the server its instructions.
# See http://httpd.apache.org/docs/2.2/ for detailed information about
# the directives and /usr/share/doc/apache2-common/README.Debian.gz about
# Debian specific hints.
#
#
# Summary of how the Apache 2 configuration works in Debian:
# The Apache 2 web server configuration in Debian is quite different to
# upstream's suggested way to configure the web server. This is because Debian's
# default Apache2 installation attempts to make adding and removing modules,
# virtual hosts, and extra configuration directives as flexible as possible, in
# order to make automating the changes and administering the server as easy as
# possible.

# It is split into several files forming the configuration hierarchy outlined
# below, all located in the /etc/apache2/ directory:
#
#   /etc/apache2/
#   |-- apache2.conf
#   |   `--  ports.conf
#   |-- mods-enabled
#   |   |-- *.load
#   |   `-- *.conf
#   |-- conf.d
#   |   `-- *
#   `-- sites-enabled
#       `-- *
#
#
# * apache2.conf is the main configuration file (this file). It puts the pieces
#   together by including all remaining configuration files when starting up the
#   web server.
#
#   In order to avoid conflicts with backup files, the Include directive is
#   adapted to ignore files that:
#   - do not begin with a letter or number
#   - contain a character that is neither letter nor number nor _-:.
#   - contain .dpkg
#
#   Yet we strongly suggest that all configuration files either end with a
#   .conf or .load suffix in the file name. The next Debian release will
#   ignore files not ending with .conf (or .load for mods-enabled).
#
# * ports.conf is always included from the main configuration file. It is
#   supposed to determine listening ports for incoming connections, and which
#   of these ports are used for name based virtual hosts.
#
# * Configuration files in the mods-enabled/ and sites-enabled/ directories
#   contain particular configuration snippets which manage modules or virtual
#   host configurations, respectively.
#
#   They are activated by symlinking available configuration files from their
#   respective *-available/ counterparts. These should be managed by using our
#   helpers a2enmod/a2dismod, a2ensite/a2dissite. See
#   their respective man pages for detailed information.
#
# * Configuration files in the conf.d directory are either provided by other
#   packages or may be added by the local administrator. Local additions
#   should start with local- or end with .local.conf to avoid name clashes. All
#   files in conf.d are considered (excluding the exceptions noted above) by
#   the Apache 2 web server.
#
# * The binary is called apache2. Due to the use of environment variables, in
#   the default configuration, apache2 needs to be started/stopped with
#   /etc/init.d/apache2 or apache2ctl. Calling /usr/bin/apache2 directly will not
#   work with the default configuration.


# Global configuration
#

#
# ServerRoot: The top of the directory tree under which the server's
# configuration, error, and log files are kept.
#
# NOTE!  If you intend to place this on an NFS (or otherwise network)
# mounted filesystem then please read the LockFile documentation (available
# at <URL:http://httpd.apache.org/docs/2.2/mod/mpm_common.html#lockfile>);
# you will save yourself a lot of trouble.
#
# Do NOT add a slash at the end of the directory path.
#
#ServerRoot "/etc/apache2"

#
# The accept serialization lock file MUST BE STORED ON A LOCAL DISK.
#
LockFile ${APACHE_LOCK_DIR}/accept.lock

#
# PidFile: The file in which the server should record its process
# identification number when it starts.
# This needs to be set in /etc/apache2/envvars
#
PidFile ${APACHE_PID_FILE}

#
# Timeout: The number of seconds before receives and sends time out.
#
Timeout 300

#
# KeepAlive: Whether or not to allow persistent connections (more than
# one request per connection). Set to "Off" to deactivate.
#
KeepAlive On

#
# MaxKeepAliveRequests: The maximum number of requests to allow
# during a persistent connection. Set to 0 to allow an unlimited amount.
# We recommend you leave this number high, for maximum performance.
#
MaxKeepAliveRequests 100

#
# KeepAliveTimeout: Number of seconds to wait for the next request from the
# same client on the same connection.
#
KeepAliveTimeout 5

##
## Server-Pool Size Regulation (MPM specific)
## 

# prefork MPM
# StartServers: number of server processes to start
# MinSpareServers: minimum number of server processes which are kept spare
# MaxSpareServers: maximum number of server processes which are kept spare
# MaxClients: maximum number of server processes allowed to start
# MaxRequestsPerChild: maximum number of requests a server process serves
<IfModule mpm_prefork_module>
    StartServers          1
    MinSpareServers       1
    MaxSpareServers       3
    MaxClients           10
    MaxRequestsPerChild 3000
</IfModule>

# worker MPM
# StartServers: initial number of server processes to start
# MinSpareThreads: minimum number of worker threads which are kept spare
# MaxSpareThreads: maximum number of worker threads which are kept spare
# ThreadLimit: ThreadsPerChild can be changed to this maximum value during a
#              graceful restart. ThreadLimit can only be changed by stopping
#              and starting Apache.
# ThreadsPerChild: constant number of worker threads in each server process
# MaxClients: maximum number of simultaneous client connections
# MaxRequestsPerChild: maximum number of requests a server process serves
<IfModule mpm_worker_module>
    StartServers          1
    MinSpareThreads       5
    MaxSpareThreads      15 
    ThreadLimit          25
    ThreadsPerChild       5
    MaxClients           25
    MaxRequestsPerChild 200
</IfModule>

# event MPM
# StartServers: initial number of server processes to start
# MinSpareThreads: minimum number of worker threads which are kept spare
# MaxSpareThreads: maximum number of worker threads which are kept spare
# ThreadsPerChild: constant number of worker threads in each server process
# MaxClients: maximum number of simultaneous client connections
# MaxRequestsPerChild: maximum number of requests a server process serves
<IfModule mpm_event_module>
    StartServers          1
    MinSpareThreads       5
    MaxSpareThreads      15 
    ThreadLimit          25
    ThreadsPerChild       5
    MaxClients           25
    MaxRequestsPerChild 200
</IfModule>

# These need to be set in /etc/apache2/envvars
User ${APACHE_RUN_USER}
Group ${APACHE_RUN_GROUP}

#
# AccessFileName: The name of the file to look for in each directory
# for additional configuration directives.  See also the AllowOverride
# directive.
#

AccessFileName .htaccess

#
# The following lines prevent .htaccess and .htpasswd files from being 
# viewed by Web clients. 
#
<Files ~ "^\.ht">
    Order allow,deny
    Deny from all
    Satisfy all
</Files>

#
# DefaultType is the default MIME type the server will use for a document
# if it cannot otherwise determine one, such as from filename extensions.
# If your server contains mostly text or HTML documents, "text/plain" is
# a good value.  If most of your content is binary, such as applications
# or images, you may want to use "application/octet-stream" instead to
# keep browsers from trying to display binary files as though they are
# text.
#
# It is also possible to omit any default MIME type and let the
# client's browser guess an appropriate action instead. Typically the
# browser will decide based on the file's extension then. In cases
# where no good assumption can be made, letting the default MIME type
# unset is suggested  instead of forcing the browser to accept
# incorrect  metadata.
#
DefaultType None


#
# HostnameLookups: Log the names of clients or just their IP addresses
# e.g., www.apache.org (on) or 204.62.129.132 (off).
# The default is off because it'd be overall better for the net if people
# had to knowingly turn this feature on, since enabling it means that
# each client request will result in AT LEAST one lookup request to the
# nameserver.
#
HostnameLookups Off

# ErrorLog: The location of the error log file.
# If you do not specify an ErrorLog directive within a <VirtualHost>
# container, error messages relating to that virtual host will be
# logged here.  If you *do* define an error logfile for a <VirtualHost>
# container, that host's errors will be logged there and not here.
#
ErrorLog ${APACHE_LOG_DIR}/error.log

#
# LogLevel: Control the number of messages logged to the error_log.
# Possible values include: debug, info, notice, warn, error, crit,
# alert, emerg.
#
LogLevel error

# Include module configuration:
Include mods-enabled/*.load
Include mods-enabled/*.conf

# Include list of ports to listen on and which to use for name based vhosts
Include ports.conf

#
# The following directives define some format nicknames for use with
# a CustomLog directive (see below).
# If you are behind a reverse proxy, you might want to change %h into %{X-Forwarded-For}i
#
LogFormat "%v:%p %h %l %u %t \"%r\" %>s %O \"%{Referer}i\" \"%{User-Agent}i\"" vhost_combined
LogFormat "%h %l %u %t \"%r\" %>s %O \"%{Referer}i\" \"%{User-Agent}i\"" combined
LogFormat "%h %l %u %t \"%r\" %>s %O" common
LogFormat "%{Referer}i -> %U" referer
LogFormat "%{User-agent}i" agent

# Include of directories ignores editors' and dpkg's backup files,
# see the comments above for details.

# Include generic snippets of statements
Include conf.d/

# Include the virtual host configurations:
Include sites-enabled/

Answer 1

I have a few suggestions:

1. Have you considered nginx as a front-end for all the static files, and something like php-fpm to handle PHP requests? nginx is a lot more efficient than Apache, and most popular PHP apps support it nowadays. PHP-FPM is also a lot more tweakable than mod_php on Apache, IMHO. This is more work, sure, but the benefit is that you can watch all components (nginx, php-fpm and php threads themselves) separately, things are easier to debug once you have the logging set up the way you want, etc, etc.

2. Consider throwing Cloudflare on top of your app. This may require some tweaking, but in general Cloudflare provides both caching/offloading and security on top of a pretty kickass DNS management control panel; one of the best I've seen. The free plan should suit your needs, but you can always sign up for a paid plan if you need fancier features.

3. How about a middleground in terms of hosting? If you just want a certain level of control, and you are hosting LAMP applications (i.e. don't really need root access), check out webfaction. I've used them for several years now and am extremely satisfied. They let you tweak the config quite extensively, despite being a shared host. You can even dynamically allocate your own MySQL instances if you want. It's quite literally like having a VPS without the need to manage it. :) If you do choose to go with them, I'd appreciate it if you used me as a reference: http://www.webfaction.com?affiliate=rouben A word of caution re: webfaction - read their docs to understand how their service is structured before you write them off. It's quite literally like setting up the config I outlined in point (1) above, and if you're coming from a cPanel (or similar) world, their control panel will make little sense to you, but if you'd ever set up anything like option (1) above, it will actually make a lot more sense.

Answer 2

It's perfectly possible to run a LAMP server on a 1Gb machine - depending on the nature of the traffic. There's not nearly enough information to say why why you are reaching the limits of the system - indeed "Everything went wrong" is not a very helpful diagnostic.

I had around 400 unique vists

This is a totally meaningless metric.

You've not told us what the memory usage was before starting the servers, nor when the performance went bad, nor the load average, not which apache this is, nor how your PHP is configured, nor what the memory profile of the application is, large chunks of the config are missing.....

There's about 3 hours work for someone who knows what they are doing to start baselining the system and have some clue about what's happening with the performance. For now you don't know the questions to ask. You are not going to find your answer here.

If you can't afford professional advice, then go read some books - High Performance MySQL, Professional website performance, and the online resources such as the Apache docs there are also some good links here. You should also learn how to use the tools available in the OS for analysing performance.

Answer 3

use threadded MPM(worker) in apache, or, if you don't need .htaccess files - use nginx+php-fpm. This configuration will be most lightweight.

You should use maximum number of threads(64) per process and, 2*CPUs processes in your MPM config.