mysql.server can be found in the
support-files directory under the MySQL
installation directory or in a MySQL source tree. You can
install it as
automatic MySQL startup and shutdown. See
Section 4.3.3, “mysql.server — MySQL Server Startup Script”.
If MySQL cannot open enough files or connections, it may be that you have not configured Linux to handle enough files.
In Linux 2.2 and onward, you can check the number of allocated file handles as follows:
If you have more than 16MB of memory, you should add something
like the following to your init scripts (for example,
/etc/init.d/boot.local on SuSE Linux):
echo 65536 > /proc/sys/fs/file-max echo 8192 > /proc/sys/fs/dquot-max echo 1024 > /proc/sys/fs/super-max
You can also run the
echo commands from the
command line as
root, but these settings
are lost the next time your computer restarts.
Alternatively, you can set these parameters on startup by
sysctl tool, which is used by
many Linux distributions (including SuSE Linux 8.0 and later).
Put the following values into a file named
# Increase some values for MySQL fs.file-max = 65536 fs.dquot-max = 8192 fs.super-max = 1024
You should also add the following to
This should enable a server limit of 8,192 for the combined number of connections and open files.
STACK_SIZE constant in LinuxThreads
controls the spacing of thread stacks in the address space. It
needs to be large enough so that there is plenty of room for
each individual thread stack, but small enough to keep the
stack of some threads from running into the global
mysqld data. Unfortunately, as we have
experimentally discovered, the Linux implementation of
mmap() successfully unmaps a mapped region
if you ask it to map out an address currently in use, zeroing
out the data on the entire page instead of returning an error.
So, the safety of mysqld or any other
threaded application depends on the “gentlemanly”
behavior of the code that creates threads. The user must take
measures to make sure that the number of running threads at
any given time is sufficiently low for thread stacks to stay
away from the global heap. With mysqld, you
should enforce this behavior by setting a reasonable value for
If you build MySQL yourself, you can patch LinuxThreads for
better stack use. See Section 220.127.116.11, “Linux Source Distribution Notes”. If
you do not want to patch LinuxThreads, you should set
max_connections to a value no
higher than 500. It should be even less if you have a large
key buffer, large heap tables, or some other things that make
mysqld allocate a lot of memory, or if you
are running a 2.2 kernel with a 2GB patch. If you are using
our binary or RPM version, you can safely set
max_connections at 1500,
assuming no large key buffer or heap tables with lots of data.
The more you reduce
LinuxThreads the more threads you can safely create. Values
between 128KB and 256KB are recommended.
If you use a lot of concurrent connections, you may suffer from a “feature” in the 2.2 kernel that attempts to prevent fork bomb attacks by penalizing a process for forking or cloning a child. This causes MySQL not to scale well as you increase the number of concurrent clients. On single-CPU systems, we have seen this manifest as very slow thread creation; it may take a long time to connect to MySQL (as long as one minute), and it may take just as long to shut it down. On multiple-CPU systems, we have observed a gradual drop in query speed as the number of clients increases. In the process of trying to find a solution, we have received a kernel patch from one of our users who claimed it helped for his site. This patch is available at http://dev.mysql.com/Downloads/Patches/linux-fork.patch. We have done rather extensive testing of this patch on both development and production systems. It has significantly improved MySQL performance without causing any problems and is recommended for users who still run high-load servers on 2.2 kernels.
This issue has been fixed in the 2.4 kernel, so if you are not satisfied with the current performance of your system, rather than patching your 2.2 kernel, it might be easier to upgrade to 2.4. On SMP systems, upgrading also gives you a nice SMP boost in addition to fixing the fairness bug.
We have tested MySQL on the 2.4 kernel on a two-CPU machine and found MySQL scales much better. There was virtually no slowdown on query throughput all the way up to 1,000 clients, and the MySQL scaling factor (computed as the ratio of maximum throughput to the throughput for one client) was 180%. We have observed similar results on a four-CPU system: Virtually no slowdown as the number of clients was increased up to 1,000, and a 300% scaling factor. Based on these results, for a high-load SMP server using a 2.2 kernel, it is definitely recommended to upgrade to the 2.4 kernel at this point.
We have discovered that it is essential to run the
mysqld process with the highest possible
priority on the 2.4 kernel to achieve maximum performance.
This can be done by adding a
renice -20 $$
command to mysqld_safe. In our testing on a
four-CPU machine, increasing the priority resulted in a 60%
throughput increase with 400 clients.
If you see a dead mysqld server process with ps, this usually means that you have found a bug in MySQL or you have a corrupted table. See Section B.5.4.2, “What to Do If MySQL Keeps Crashing”.
To get a core dump on Linux if mysqld dies
SIGSEGV signal, you can start
mysqld with the
--core-file option. Note that
you also probably need to raise the core file size by adding
ulimit -c 1000000 to
mysqld_safe or starting
See Section 4.3.2, “mysqld_safe — MySQL Server Startup Script”.