Optimizating the settings of MySQL - Tweak

[ThinkFast]

First, sorry if I’m spaming.

I have a few months to find a stable VPS provider and I want to make an in depth research. Of course, I will need your help.

I want to ask you about the 256MB ones. Are they enough? On the one I use now only the mysql service uses 120MB of the RAM. There’s no space left for the other service. Now I use 512RAM, but the MYSQL is not optimized and the WebMin panel is not good at all. I’m not satisfied with its RAM consumption. It eats RAM even when I’m not using it. What’s the real deal.

 

[subn3t]

Optimizing the settings of MySQL

WebMin should not use any of you RAM when you don’t use it. In my opinion this is the web instrument with the highest performance when we talk about managing UNIX/LINUX OSs. Well, we can talk a lot about the security, but that goes with every piece of computer software. Tell me what you need to run on the VPS. What service you will provide and how much people you will server it on. I will give you an average of the resources that you will need.

 

[ktomov]

MySQL - Database optimization

When using centos + lxadmin, the default config file of Mysql does not feature some of the buffer parameters which are allowed. This leads to the over usage of RAM by MySQL, well it even does not use the RAM it shows. It just makes it busy. In the moment I’m using this config file. It works perfect for my needs. The RAM used by the service now is 75 mb. With some more tweaks it can get lower.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=**********

log-slow-queries = /var/log/mysql-slow.log
long_query_time = 5
skip-innodb
skip-locking
skip-bdb

[mysqld]
local-infile=0
skip-locking
query_cache_limit=4M
query_cache_size=32M
query_cache_type=1
max_connections=300
interactive_timeout=100
#wait_timeout=100
connect_timeout=10
thread_cache_size=64
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
open_files_limit=5120
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=4
myisam_sort_buffer_size=64M
tmp_table_size=64M
max_heap_table_size=64M

[safe_mysqld]
err-log=/var/log/mysqld.log
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Change the name of the user in the beginning of the file if you decide to use this file. This is the for the standard version of Mysql.

P.S. You can use the tool below to check how you config file works. You can tweak it for your needs based on the resources you have: http://mysqltuner.com/

P.S.2: I’ve almost forgotten. You have to change this line too: thread_concurrency=4, because the setup now is for 2 cores. If you use only one core the value should be 2 not 4. If you don’t know how much cores you have access to use this: grep ^processor /proc/cpuinfo | wc –l

 

[ThinkFast]

When using centos + lxadmin, the default config file of Mysql does not feature some of the buffer parameters which are allowed. This leads to the over usage of RAM by MySQL, well it even does not use the RAM it shows. It just makes it busy. In the moment I’m using this config file. It works perfect for my needs. The RAM used by the service now is 75 mb. With some more tweaks it can get lower.

Change the name of the user in the beginning of the file if you decide to use this file. This is the for the standard version of Mysql.

P.S. You can use the tool below to check how you config file works. You can tweak it for your needs based on the resources you have: http://mysqltuner.com/

P.S.2: I’ve almost forgotten. You have to change this line too: thread_concurrency=4, because the setup now is for 2 cores. If you use only one core the value should be 2 not 4. If you don’t know how much cores you have access to use this: grep ^processor /proc/cpuinfo | wc –l

Thanks. I saved 70MB with this service. Those MySQL configurations are hated by me since mankind was created. I have to learn how to do it by myself.
P.S. You are fast at building up your reputation. Great job, you’re great.

 

[delta_force]

Requesting help optimizing MySQL settings

Here is an addition to the thread. It is for Apache, but still it greatly affects the optimization of the memory, especially if we talk about 256MB VPSs or even lower.

In the file httpd.conf (CentOs path: /etc/httpd/conf/httpd.conf) you have to find and reconfigure the listed fields:

KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 1
# prefork MPM
StartServers 2
MinSpareServers 2
MaxSpareServers 5
ServerLimit 100
MaxClients 100
MaxRequestsPerChild 500
<IfModule prefork.c>
StartServers 2
MinSpareServers 2
MaxSpareServers 5
ServerLimit 100
MaxClients 100
MaxRequestsPerChild 500
</IfModule>
<IfModule worker.c>
StartServers 2
MaxClients 150
MinSpareThreads 15
MaxSpareThreads 50
ThreadsPerChild 15
MaxRequestsPerChild 0
</IfModule>

Before doing this on one of my VPSs the Apache service was eating the entire RAM when it has to, but after that there were no release of it and it still was using the swap file even when there were no visitors on the site. After I made this modification when loading random pages the used memory shown in the WP admin panel was 180MB. After watching a movie and going back the used RAM was only 72 MB.

Server - CentOs 5.7 x64, PHP 5.3.8, number of sites - 1 (wordpress blog)

 

[airstand]

Put nginx before the apache. After it put fastcgi + spawn-fastcgi and listen to the love song.