Databases Reference
In-Depth Information
When you need very high throughput, you might be able to improve performance by
changing your operating system's networking configuration. If you don't have many
connections but you have large queries or result sets, you can increase the TCP buffer
size. How you do this varies from system to system, but in most GNU/Linux systems
you can change the values in
/etc/sysctl.conf
and execute
sysctl -p
, or use the
/proc
file-
system by echoing new values into the files found at
/proc/sys/net/
. You can find good
tutorials on this topic online with a search for “TCP tuning guide.”
It's usually more important, though, to adjust your settings to deal efficiently with a
lot of connections and small queries. One of the more common tweaks is to change
your local port range. Here's a system that is configured to default values:
[root@server ~]#
cat /proc/sys/net/ipv4/ip_local_port_range
32768 61000
Sometimes you might need to change these values to a larger range. For example:
[root@server ~]#
echo 1024 65535 > /proc/sys/net/ipv4/ip_local_port_range
You can allow more connections to queue up as follows:
[root@server ~]#
echo 4096 > /proc/sys/net/ipv4/tcp_max_syn_backlog
For database servers that are used only locally, you can shorten the timeout that comes
after closing a socket in the event that the peer is broken and doesn't close its side of
the connection. The default is one minute on most systems, which is rather long:
[root@server ~]#
echo
<value>
> /proc/sys/net/ipv4/tcp_fin_timeout
Most of the time these settings can be left at their defaults. You'll typically need to
change them only when something unusual is happening, such as extremely poor net-
work performance or very large numbers of connections. An Internet search for “TCP
variables” will turn up lots of good reading about these and many more variables.
Choosing an Operating System
GNU/Linux is the most common operating system for high-performance MySQL in-
stallations today, but MySQL will run on many operating systems.
Solaris is the leader on SPARC hardware, and it runs on x86 hardware too. It's fre-
quently used in applications that demand high reliability. Solaris has a reputation for
being more difficult to work with than GNU/Linux in some ways, but it's a solid op-
erating system with many advanced features. In particular, Solaris 10 added the ZFS
filesystem, a lot of advanced troubleshooting tools (such as DTrace), good threading
performance, and a virtualization technology called Solaris Zones that helps with re-
source management.
FreeBSD is another option. It has historically had a number of problems with MySQL,
mostly related to threading support, but newer versions are much better. Today, it's
