cubber
03-24-2009, 11:47 AM
I know there are some things you can do to optimize mysql and sysctl to get better performance out of your server. What are some of the optimizations some of you have tried and have they come with good or bad effects? Please note your server specs.
My server is dedicated just for eqemu:
specs are:
Gentoo Linux 2.6.26-hardened-r9 (Headless LAMP Server - NO X)
Processor: AMD Athlon-XP 2800+
RAM: 2 GIG
Hard Drives: 120G 7200 RPM WD IDE, 160G 7200RPM WD IDE, 500G 7200RPM WD IDE
Drives are configured as such:
sda1 - boot - 512M
sda2 - swap - 4096M
sda3 - / - 2048M
sda4 - extended
sda5 - /tmp - 2048M
sda6 - /opt - 20480M
sda7 - /usr - 20480M
sda8 - /var - 10240M
/dev/mapper/vg-home - /home - (LVM the rest of the first drive and the other 2 drives into one large home partion, for backups and storage.)
I have made some minor changes to the default my.cnf file that seemed to have helped out a bit.
my.cnf (RED notes changes from default)
# /etc/mysql/my.cnf: The global mysql configuration file.
# $Header: /var/cvsroot/gentoo-x86/dev-db/mysql/files/my.cnf-4.1,v 1.4 2008/11/14 02:16:25 robbat2 Exp $
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysql]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqladmin]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlcheck]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqldump]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlimport]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlshow]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[myisamchk]
character-sets-dir=/usr/share/mysql/charsets
[myisampack]
character-sets-dir=/usr/share/mysql/charsets
# use [safe_mysqld] with mysql-3
[mysqld_safe]
err-log = /var/log/mysql/mysql.err
# add a section [mysqld-4.1] or [mysqld-5.0] for specific configurations
[mysqld]
character-set-server = utf8
default-character-set = utf8
user = mysql
port = 3306
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
skip-locking
key_buffer = 32M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
language = /usr/share/mysql/english
query_cache_size = 16M
query_cache_limit = 2M
thread_cache = 16
# security:
# using "localhost" in connects uses sockets by default
# skip-networking
#bind-address = 127.0.0.1
log-bin =mysqld-bin
server-id = 1
# point the following paths to different dedicated disks
tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
# you need the debug USE flag enabled to use the following directives,
# if needed, uncomment them, start the server and issue
# #tail -f /tmp/mysqld.sql /tmp/mysqld.trace
# this will show you *exactly* what's happening in your server ;)
#log = /tmp/mysqld.sql
#gdb
#debug = d:t:i:o,/tmp/mysqld.trace
#one-thread
# uncomment the following directives if you are using BDB tables
#bdb_cache_size = 4M
#bdb_max_lock = 10000
# the following is the InnoDB configuration
# if you wish to disable innodb instead
# uncomment just the next line
#skip-innodb
#
# the rest of the innodb config follows:
# don't eat too much memory, we're trying to be safe on 64Mb boxes
# you might want to bump this up a bit on boxes with more RAM
innodb_buffer_pool_size = 64M
# this is the default, increase it if you have lots of tables
innodb_additional_mem_pool_size = 2M
#
# i'd like to use /var/lib/mysql/innodb, but that is seen as a database :-(
# and upstream wants things to be under /var/lib/mysql/, so that's the route
# we have to take for the moment
#innodb_data_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
#innodb_log_group_home_dir = /var/lib/mysql/
# you may wish to change this size to be more suitable for your system
# the max is there to avoid run-away growth on your machine
innodb_data_file_path = ibdata1:10M:autoextend:max:128M
# we keep this at around 25% of of innodb_buffer_pool_size
# sensible values range from 1MB to (1/innodb_log_files_in_group*innodb_buffer_pool_size)
innodb_log_file_size = 128M
# this is the default, increase it if you have very large transactions going on
innodb_log_buffer_size = 8M
# this is the default and won't hurt you
# you shouldn't need to tweak it
set-variable = innodb_log_files_in_group=2
# see the innodb config docs, the other options are not always safe
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_file_per_table
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
# uncomment the next directive if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
and sysctl.conf
# /etc/sysctl.conf
#
# For more information on how this file works, please see
# the manpages sysctl(8) and sysctl.conf(5).
#
# In order for this file to work properly, you must first
# enable 'Sysctl support' in the kernel.
#
# Look in /proc/sys/ for all the things you can setup.
#
# Disables packet forwarding
#net.ipv4.ip_forward = 0
# Disables IP dynaddr
#net.ipv4.ip_dynaddr = 0
# Disable ECN
#net.ipv4.tcp_ecn = 0
# Enables source route verification
net.ipv4.conf.default.rp_filter = 1
# Enable reverse path
net.ipv4.conf.all.rp_filter = 1
# Enable SYN cookies (yum!)
# http://cr.yp.to/syncookies.html
#net.ipv4.tcp_syncookies = 1
# Disable source route
#net.ipv4.conf.all.accept_source_route = 0
#net.ipv4.conf.default.accept_source_route = 0
# Disable redirects
#net.ipv4.conf.all.accept_redirects = 0
#net.ipv4.conf.default.accept_redirects = 0
# Disable secure redirects
#net.ipv4.conf.all.secure_redirects = 0
#net.ipv4.conf.default.secure_redirects = 0
# Ignore ICMP broadcasts
#net.ipv4.icmp_echo_ignore_broadcasts = 1
# Disables the magic-sysrq key
#kernel.sysrq = 0
# When the kernel panics, automatically reboot in 3 seconds
#kernel.panic = 3
# Allow for more PIDs (cool factor!); may break some programs
#kernel.pid_max = 999999
# You should compile nfsd into the kernel or add it
# to modules.autoload for this to work properly
# TCP Port for lock manager
#fs.nfs.nlm_tcpport = 0
# UDP Port for lock manager
#fs.nfs.nlm_udpport = 0
kernel.shmmax = 134217728
I still get that damn shared memory error on first startup of the server even with the kernel.shmmax setting change. Oh well it goes away with a quick ./stop and ./start of the server.
Any suggetions on how to get the most of of a minimal linux server dedicated to eqemu?
My server is dedicated just for eqemu:
specs are:
Gentoo Linux 2.6.26-hardened-r9 (Headless LAMP Server - NO X)
Processor: AMD Athlon-XP 2800+
RAM: 2 GIG
Hard Drives: 120G 7200 RPM WD IDE, 160G 7200RPM WD IDE, 500G 7200RPM WD IDE
Drives are configured as such:
sda1 - boot - 512M
sda2 - swap - 4096M
sda3 - / - 2048M
sda4 - extended
sda5 - /tmp - 2048M
sda6 - /opt - 20480M
sda7 - /usr - 20480M
sda8 - /var - 10240M
/dev/mapper/vg-home - /home - (LVM the rest of the first drive and the other 2 drives into one large home partion, for backups and storage.)
I have made some minor changes to the default my.cnf file that seemed to have helped out a bit.
my.cnf (RED notes changes from default)
# /etc/mysql/my.cnf: The global mysql configuration file.
# $Header: /var/cvsroot/gentoo-x86/dev-db/mysql/files/my.cnf-4.1,v 1.4 2008/11/14 02:16:25 robbat2 Exp $
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysql]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqladmin]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlcheck]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqldump]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlimport]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlshow]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[myisamchk]
character-sets-dir=/usr/share/mysql/charsets
[myisampack]
character-sets-dir=/usr/share/mysql/charsets
# use [safe_mysqld] with mysql-3
[mysqld_safe]
err-log = /var/log/mysql/mysql.err
# add a section [mysqld-4.1] or [mysqld-5.0] for specific configurations
[mysqld]
character-set-server = utf8
default-character-set = utf8
user = mysql
port = 3306
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
skip-locking
key_buffer = 32M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
language = /usr/share/mysql/english
query_cache_size = 16M
query_cache_limit = 2M
thread_cache = 16
# security:
# using "localhost" in connects uses sockets by default
# skip-networking
#bind-address = 127.0.0.1
log-bin =mysqld-bin
server-id = 1
# point the following paths to different dedicated disks
tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
# you need the debug USE flag enabled to use the following directives,
# if needed, uncomment them, start the server and issue
# #tail -f /tmp/mysqld.sql /tmp/mysqld.trace
# this will show you *exactly* what's happening in your server ;)
#log = /tmp/mysqld.sql
#gdb
#debug = d:t:i:o,/tmp/mysqld.trace
#one-thread
# uncomment the following directives if you are using BDB tables
#bdb_cache_size = 4M
#bdb_max_lock = 10000
# the following is the InnoDB configuration
# if you wish to disable innodb instead
# uncomment just the next line
#skip-innodb
#
# the rest of the innodb config follows:
# don't eat too much memory, we're trying to be safe on 64Mb boxes
# you might want to bump this up a bit on boxes with more RAM
innodb_buffer_pool_size = 64M
# this is the default, increase it if you have lots of tables
innodb_additional_mem_pool_size = 2M
#
# i'd like to use /var/lib/mysql/innodb, but that is seen as a database :-(
# and upstream wants things to be under /var/lib/mysql/, so that's the route
# we have to take for the moment
#innodb_data_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
#innodb_log_group_home_dir = /var/lib/mysql/
# you may wish to change this size to be more suitable for your system
# the max is there to avoid run-away growth on your machine
innodb_data_file_path = ibdata1:10M:autoextend:max:128M
# we keep this at around 25% of of innodb_buffer_pool_size
# sensible values range from 1MB to (1/innodb_log_files_in_group*innodb_buffer_pool_size)
innodb_log_file_size = 128M
# this is the default, increase it if you have very large transactions going on
innodb_log_buffer_size = 8M
# this is the default and won't hurt you
# you shouldn't need to tweak it
set-variable = innodb_log_files_in_group=2
# see the innodb config docs, the other options are not always safe
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_file_per_table
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
# uncomment the next directive if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
and sysctl.conf
# /etc/sysctl.conf
#
# For more information on how this file works, please see
# the manpages sysctl(8) and sysctl.conf(5).
#
# In order for this file to work properly, you must first
# enable 'Sysctl support' in the kernel.
#
# Look in /proc/sys/ for all the things you can setup.
#
# Disables packet forwarding
#net.ipv4.ip_forward = 0
# Disables IP dynaddr
#net.ipv4.ip_dynaddr = 0
# Disable ECN
#net.ipv4.tcp_ecn = 0
# Enables source route verification
net.ipv4.conf.default.rp_filter = 1
# Enable reverse path
net.ipv4.conf.all.rp_filter = 1
# Enable SYN cookies (yum!)
# http://cr.yp.to/syncookies.html
#net.ipv4.tcp_syncookies = 1
# Disable source route
#net.ipv4.conf.all.accept_source_route = 0
#net.ipv4.conf.default.accept_source_route = 0
# Disable redirects
#net.ipv4.conf.all.accept_redirects = 0
#net.ipv4.conf.default.accept_redirects = 0
# Disable secure redirects
#net.ipv4.conf.all.secure_redirects = 0
#net.ipv4.conf.default.secure_redirects = 0
# Ignore ICMP broadcasts
#net.ipv4.icmp_echo_ignore_broadcasts = 1
# Disables the magic-sysrq key
#kernel.sysrq = 0
# When the kernel panics, automatically reboot in 3 seconds
#kernel.panic = 3
# Allow for more PIDs (cool factor!); may break some programs
#kernel.pid_max = 999999
# You should compile nfsd into the kernel or add it
# to modules.autoload for this to work properly
# TCP Port for lock manager
#fs.nfs.nlm_tcpport = 0
# UDP Port for lock manager
#fs.nfs.nlm_udpport = 0
kernel.shmmax = 134217728
I still get that damn shared memory error on first startup of the server even with the kernel.shmmax setting change. Oh well it goes away with a quick ./stop and ./start of the server.
Any suggetions on how to get the most of of a minimal linux server dedicated to eqemu?