KEEP IN MIND: OVERLOAD OF YOUR WEBSERVER MIGHT BE FROM BRUTEFORCE OR DDoS!!!

10k-connections-to-apach2

sudden spikes of (botnet) traffic might deplete your RAM via apache2 forking too many instances, forcing MySQL to quit. 10.000 connections at a time might be too much.

You can protect yourself against such attacks via fail2ban.

tested on: Linux 3.X #1 SMP Debian 3.X i686 GNU/Linux

Before start optimizing your server, let’s review memory using on it.
You can use the following command to display memory:

free -m

             total       used       free     shared    buffers     cached
Mem:           502        481         21          0         47        181
-/+ buffers/cache:        252        249
Swap:            0          0          0  

To see a list of your running processes sorted by memory use:

ps -eo pmem,pcpu,rss,vsize,args | sort -k 1 -r | less

%MEM %CPU RSS VSZ COMMAND
5.9 0.1 30384 85140 /usr/sbin/apache2 -k start 5.8 0.2 30120 85084 /usr/sbin/apache2 -k start
5.7 0.2 29632 84640 /usr/sbin/apache2 -k start
5.7 0.2 29464 84508 /usr/sbin/apache2 -k start
5.7 0.2 29324 85384 /usr/sbin/apache2 -k start
5.6 0.2 29240 85408 /usr/sbin/apache2 -k start
5.6 0.2 29200 84248 /usr/sbin/apache2 -k start
5.6 0.2 29132 85272 /usr/sbin/apache2 -k start
5.6 0.2 28920 84200 /usr/sbin/apache2 -k start
5.5 0.2 28544 84716 /usr/sbin/apache2 -k start
5.4 0.2 28012 83076 /usr/sbin/apache2 -k start
5.2 0.2 26916 81712 /usr/sbin/apache2 -k start
19.0 0.1 97812 314408 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
1.8 0.0 9648 67936 /usr/sbin/apache2 -k start
1.5 0.0 8012 41628 /usr/sbin/named -u bind
0.6 0.0 3556 17328 php-fpm: pool www
0.6 0.0 3364 17300 php-fpm: pool www
0.6 0.0 3340 10608 dovecot/imap
0.6 0.0 3152 8160 dovecot/imap

so we clearly see that a lot of apache2 instances are using most of the precious RAM.

also we see here

vim /var/log/apache2/error.log; # open up apache2 error log file

[Fri May 01 20:15:01 2015] [error] (12)Cannot allocate memory: fork: Unable to fork new process
[Sat May 02 01:13:52 2015] [notice] suEXEC mechanism enabled (wrapper: /usr/lib/apache2/suexec)

tweak em to not to do so:
You might NOT need the LockFile config file anymore see: https://httpd.apache.org/docs/2.4/upgrading.html

vim /etc/apache2/apache2.conf; # open up apache2 main config file

# Global configuration
LockFile ${APACHE_LOCK_DIR}/accept.lock # is probably only valid for x <= Apache v2.2
PidFile ${APACHE_PID_FILE}
Timeout 30
KeepAlive Off
MaxKeepAliveRequests 100
KeepAliveTimeout 10
StartServers 1
MinSpareServers 1
MaxSpareServers 3
MaxClients 20
MaxRequestsPerChild 3000
StartServers 1
MinSpareThreads 5
MaxSpareThreads 15
ThreadLimit 25
ThreadsPerChild 5
MaxClients 50
MaxRequestsPerChild 200

src: http://www.narga.net/optimizing-apachephpmysql-low-memory-server/

http://lowendbox.com/blog/reducing-mysql-memory-usage-for-low-end-boxes/

for low RAM servers you should tweak:

vim /etc/mysql/my.cnf; # open this file

# add that
open_files_limit=65535

to check out the current value of the parameter go like that:

mysql -u root
mysql> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024  |
+------------------+-------+
1 row in set (0.00 sec)
# open this file
vim /etc/security/limits.conf

# add that
root soft nofile 65535
root hard nofile 65535
* soft nofile 65535
* hard nofile 65535

if you ever want to change the mysql-root for vesta cp password, it’s good to know that the mysql-root password vesta cp is relying on is in a text file located in:

vim /usr/local/vesta/conf/mysql.conf

if

mysql -u root

won’t ask you for a password, it is because it’s also stored here:

vim /root/.my.cnf

i try to detect what is causing mysql to crash frequently… like every 2 days.

server is vps@hetzner with 512MB of RAM running Linux 3.2.0-4-686-pae #1 SMP Debian 3.X i686 GNU/Linux

admin