[nycbug-talk] MySQL and FreeBSD 6.0

[Max Gribov]

Trish Lynch wrote:

>I think the main issues with MySQL and FreeBSD are primarily of tuning, even Linux requires OS tuning to run right, but the documentation is all there. Not alway so for FreeBSD. 
>
>It might be nice for those running bigger sites on FreeBSD to document the back-side OS tunings, though for some of us, translating some of the tunings used for Linux to their comparable FreeBSD values (number of threads available, number of open files, depending on thread model used, etc) could also work.
>  
>
seems to work well on gentoo linux. dual core amd64 2.2Ghz, 2G ram,
softraid, default linux scheduler. kernel was stripped to remove
anything not needed of course.

several times a day mysql consumes over 120% of cpu for a period of up
to 10-20 mins, and load ave jumps up to 20, which is the nature of
service machine provides.
app uses innodb tables.

heres a sysctl.conf paste from the box, so far no issues:

# max connections
net.core.somaxconn=4096

# 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!)
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

# Increase TCP
net.ipv4.neigh.default.proxy_qlen = 96
net.ipv4.neigh.default.unres_qlen = 6

# Increase size of socket buffers
net.ipv4.tcp_rmem="4096 98304 349520"
net.ipv4.tcp_wmem="4096 65535 262142"
net.ipv4.tcp_mem="98304 262142 393216"

# Turn on sack
net.ipv4.tcp_dsack = 1
net.ipv4.tcp_reordering = 3
# Turn on sack/fack
net.ipv4.tcp_fack = 1

# Bump up TCP socket queuer to help with syn floods
net.ipv4.tcp_max_syn_backlog = 2048

# Drop it so lack of FIN times out quicker
net.ipv4.tcp_fin_timeout = 30

# Enable a fix for RFC1337 - time-wait assassination hazards in TCP
net.ipv4.tcp_rfc1337 = 1

# Drop keep-alive time
net.ipv4.tcp_keepalive_time = 1800

# Set number of times to retry a SYN-ACK in a half-open new connections
net.ipv4.tcp_synack_retries = 5
# Set number of times to retry SYN in a new connection
net.ipv4.tcp_syn_retries = 5
# Turn off sack
net.ipv4.tcp_sack = 1
# Enable really big (>65kB) TCP window scaling if we want it.
net.ipv4.tcp_window_scaling = 1
# Turn off timestamps
net.ipv4.tcp_timestamps = 0

# Enable bad error message Protection
net.ipv4.icmp_ignore_bogus_error_responses = 1

# Bump optmem_max up
net.core.optmem_max = 20480
# Increase number of incoming connections backlog
net.core.netdev_max_backlog = 1024
net.core.dev_weight = 64
# Bump up default r/wmem to max
net.core.rmem_default = 262141
net.core.wmem_default = 262141
# Bump up max r/wmem
net.core.rmem_max = 262141
net.core.wmem_max = 262141
# Increase size of file handles and inode cache
fs.file-max = 209708

# guaranteed raid rebuild/initialization bandwidth 100mb/s
dev.raid.speed_limit_min = 100000



>Problem being in FreeBSD 6, much of this has changed because of the introduction of new kernel threading models - the same thing that increases native java performance appreciably (almost 200%)
>
>-Trish
>  
>