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 /etc/init.d/mysql
for
automatic MySQL startup and shutdown. See
Section 2.10.2.2, “Starting and Stopping MySQL Automatically”.
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:
shell>cat /proc/sys/fs/file-max
shell>cat /proc/sys/fs/dquot-max
shell>cat /proc/sys/fs/super-max
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
using the sysctl
tool, which is used by
many Linux distributions (SuSE has added it as well, beginning
with SuSE Linux 8.0). Just put the following values into a
file named /etc/sysctl.conf
:
# Increase some values for MySQL fs.file-max = 65536 fs.dquot-max = 8192 fs.super-max = 1024
You should also add the following to
/etc/my.cnf
:
[mysqld_safe] open-files-limit=8192
This should allow the server a limit of 8,192 for the combined number of connections and open files.
The 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
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 “gentlemanly”
behavior of the code that creates threads. The user must take
measures to make sure that the number of running threads at
any 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
the max_connections
variable.
If you build MySQL yourself, you can patch LinuxThreads for
better stack use. See Section 2.12.1.3, “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 3.23.25 or later, you can safely set
max_connections
at 1500,
assuming no large key buffer or heap tables with lots of data.
The more you reduce STACK_SIZE
in
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 manifested 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 made a lot of difference for his site. The 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.
We are currently also trying to collect more information on
how well MySQL performs with a 2.4 kernel on four-way and
eight-way systems. If you have access such a system and have
done some benchmarks, please send an email message to
<benchmarks@mysql.com>
with the results. We will
review them for inclusion in the manual.
If you see a dead mysqld server process with ps, this usually means that you have found a bug in MySQL or that you have a corrupted table. See Section A.5.4.2, “What to Do If MySQL Keeps Crashing”.
To get a core dump on Linux if mysqld dies
with a 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
mysqld_safe with
--core-file-size=1000000
.
See Section 4.3.2, “mysqld_safe — MySQL Server Startup Script”.
User Comments
Rather than editing the mysqld_safe script to change the 'nice' value of mysqld, better to put
[mysqld_safe]
...
nice = -20 # or whatever
into my.cnf
No, "nice = -20" won't do anything; according to both the documentation and --help, there's no "nice" option for my.cnf, at least as of 4.0.24 (which is what I'm running).
You can see if mysql is really niced on a linux box by using top, or by running: ps -eo pid,nice,args | grep -i mysql. The second column there is the nice value, which should be -20.
-Robin
Add your own comment.