This section discusses issues that have been found to occur on Linux. The first few subsections describe general operating system-related issues, problems that can occur when using binary or source distributions, and post-installation issues. The remaining subsections discuss problems that occur with Linux on specific platforms.
Note that most of these problems occur on older versions of Linux. If you are running a recent version, you may see none of them.
2.4.18.1.1. Linux Operating System Notes
MySQL needs at least Linux version 2.0.
Warning: We have seen some strange problems with Linux 2.2.14 and MySQL on SMP systems. We also have reports from some MySQL users that they have encountered serious stability problems using MySQL with kernel 2.2.14. If you are using this kernel, you should upgrade to 2.2.19 (or newer) or to a 2.4 kernel. If you have a multiple-CPU box, you should seriously consider using 2.4 because it gives you a significant speed boost. Your system should be more stable.
When using LinuxThreads, you should see a minimum of three mysqld processes running. These are in fact threads. There is one thread for the LinuxThreads manager, one thread to handle connections, and one thread to handle alarms and signals.
2.4.18.1.2. Linux Binary Distribution Notes
The Linux-Intel binary and RPM releases of MySQL are configured for the highest possible speed. We are always trying to use the fastest stable compiler available.
The binary release is linked with -static, which means you do not normally need to worry about which version of the system libraries you have. You need not install LinuxThreads, either. A program linked with -static is slightly larger than a dynamically linked program, but also slightly faster (3-5%). However, one problem with a statically linked program is that you can’t use user-defined functions (UDFs). If you are going to write or use UDFs (this is something for C or C++ programmers only), you must compile MySQL yourself using dynamic linking.
A known issue with binary distributions is that on older Linux systems that use libc (such as Red Hat 4.x or Slackware), you get some (non-fatal) issues with hostname resolution. If your system uses libc rather than glibc2, you probably will encounter some difficulties with hostname resolution and getpwnam(). This happens because glibc (unfortunately) depends on some external libraries to implement hostname resolution and getpwent(), even when compiled with -static. These problems manifest themselves in two ways:
- You may see the following error message when you run mysql_install_db:
Sorry, the host ‘xxxx‘ could not be looked up
You can deal with this by executing mysql_install_db –force, which does not execute the resolveip test in mysql_install_db. The downside is that you cannot use hostnames in the grant tables: except for localhost, you must use IP numbers instead. If you are using an old version of MySQL that does not support --force, you must manually remove the resolveip test in mysql_install using a text editor.
- You also may see the following error when you try to run mysqld with the
--user option:
getpwnam: No such file or directory
To work around this problem, start mysqld by using the su command rather than by specifying the --user option. This causes the system itself to change the user ID of the mysqld process so that mysqld need not do so.
Another solution, which solves both problems, is not to use a binary distribution. Obtain a MySQL source distribution (in RPM or tar.gz format) and install that instead.
On some Linux 2.2 versions, you may get the error Resource temporarily unavailable when clients make a great many new connections to a mysqld server over TCP/IP. The problem is that Linux has a delay between the time that you close a TCP/IP socket and the time that the system actually frees it. There is room for only a finite number of TCP/IP slots, so you encounter the resource-unavailable error if clients attempt too many new TCP/IP connections over a short period of time. For example, you may see the error when you run the MySQL test-connect benchmark over TCP/IP.
We have inquired about this problem a few times on different Linux mailing lists but have never been able to find a suitable resolution. The only known “fix” is for clients to use persistent connections, or, if you are running the database server and clients on the same machine, to use Unix socket file connections rather than TCP/IP connections.
2.4.18.1.3. Linux Source Distribution Notes
This section does not apply to MySQL Enterprise Server users.
The following notes regarding glibc apply only to the situation when you build MySQL yourself. If you are running Linux on an x86 machine, in most cases it is much better for you to use our binary. We link our binaries against the best patched version of glibc we can find and with the best compiler options, in an attempt to make it suitable for a high-load server. For a typical user, even for setups with a lot of concurrent connections or tables exceeding the 2GB limit, our binary is the best choice in most cases. After reading the following text, if you are in doubt about what to do, try our binary first to determine whether it meets your needs. If you discover that it is not good enough, you may want to try your own build. In that case, we would appreciate a note about it so that we can build a better binary next time.
MySQL uses LinuxThreads on Linux. If you are using an old Linux version that doesn’t have glibc2, you must install LinuxThreads before trying to compile MySQL. You can obtain LinuxThreads from http://dev.mysql.com/downloads/os-linux.html.
Note that glibc versions before and including version 2.1.1 have a fatal bug in pthread_mutex_timedwait() handling, which is used when INSERT DELAYED statements are issued. We recommend that you not use INSERT DELAYED before upgrading glibc.
Note that Linux kernel and the LinuxThread library can by default handle a maximum of 1,024 threads. If you plan to have more than 1,000 concurrent connections, you need to make some changes to LinuxThreads, as follows:
- Increase
PTHREAD_THREADS_MAX in sysdeps/unix/sysv/linux/bits/local_lim.h to 4096 and decrease STACK_SIZE in linuxthreads/internals.h to 256KB. The paths are relative to the root of glibc. (Note that MySQL is not stable with 600-1000 connections if STACK_SIZE is the default of 2MB.)
- Recompile LinuxThreads to produce a new
libpthread.a library, and relink MySQL against it.
There is another issue that greatly hurts MySQL performance, especially on SMP systems. The mutex implementation in LinuxThreads in glibc 2.1 is very poor for programs with many threads that hold the mutex only for a short time. This produces a paradoxical result: If you link MySQL against an unmodified LinuxThreads, removing processors from an SMP actually improves MySQL performance in many cases. We have made a patch available for glibc 2.1.3 to correct this behavior (http://dev.mysql.com/Downloads/Linux/linuxthreads-2.1-patch).
With glibc 2.2.2, MySQL uses the adaptive mutex, which is much better than even the patched one in glibc 2.1.3. Be warned, however, that under some conditions, the current mutex code in glibc 2.2.2 overspins, which hurts MySQL performance. The likelihood that this condition occurs can be reduced by re-nicing the mysqld process to the highest priority. We have also been able to correct the overspin behavior with a patch, available at http://dev.mysql.com/Downloads/Linux/linuxthreads-2.2.2.patch. It combines the correction of overspin, maximum number of threads, and stack spacing all in one. You need to apply it in the linuxthreads directory with patch -p0 . We hope it is included in some form in future releases of glibc 2.2. In any case, if you link against glibc 2.2.2, you still need to correct STACK_SIZE and PTHREAD_THREADS_MAX. We hope that the defaults is corrected to some more acceptable values for high-load MySQL setup in the future, so that the commands needed to produce your own build can be reduced to ./configure; make; make install.
We recommend that you use these patches to build a special static version of libpthread.a and use it only for statically linking against MySQL. We know that these patches are safe for MySQL and significantly improve its performance, but we cannot say anything about their effects on other applications. If you link other applications that require LinuxThreads against the patched static version of the library, or build a patched shared version and install it on your system, you do so at your own risk.
If you experience any strange problems during the installation of MySQL, or with some common utilities hanging, it is very likely that they are either library or compiler related. If this is the case, using our binary resolves them.
If you link your own MySQL client programs, you may see the following error at runtime:
ld.so.1: fatal: libmysqlclient.so.#:
open failed: No such file or directory
This problem can be avoided by one of the following methods:
- Link clients with the
-Wl,r/full/path/to/libmysqlclient.so flag rather than with -Lpath).
- Copy
libmysqclient.so to /usr/lib.
- Add the pathname of the directory where
libmysqlclient.so is located to the LD_RUN_PATH environment variable before running your client.
If you are using the Fujitsu compiler (fcc/FCC), you may have some problems compiling MySQL because the Linux header files are very gcc oriented. The following configure line should work with fcc/FCC:
CC=fcc CFLAGS="-O -K fast -K lib -K omitfp -Kpreex -D_GNU_SOURCE
-DCONST=const -DNO_STRTOLL_PROTO"
CXX=FCC CXXFLAGS="-O -K fast -K lib
-K omitfp -K preex --no_exceptions --no_rtti -D_GNU_SOURCE
-DCONST=const -Dalloca=__builtin_alloca -DNO_STRTOLL_PROTO
'-D_EXTERN_INLINE=static __inline'"
./configure
--prefix=/usr/local/mysql --enable-assembler
--with-mysqld-ldflags=-all-static --disable-shared
--with-low-memory
2.4.18.1.4. Linux Post-Installation Notes
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.4.15.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 (including SuSE Linux 8.0 and later). 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 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 the max_connections variable.
If you build MySQL yourself, you can patch LinuxThreads for better stack use. See Section 2.4.18.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, 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. We recommend values between 128KB and 256KB.
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 we recommend it to our 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, we definitely recommend upgrading 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 you have a corrupted table. See Section B.1.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 5.3.1, “mysqld_safe — MySQL Server Startup Script”.
2.4.18.1.5. Linux x86 Notes
MySQL requires libc 5.4.12 or newer. It is known to work with libc 5.4.46. glibc 2.0.6 and later should also work. There have been some problems with the glibc RPMs from Red Hat, so if you have problems, check whether there are any updates. The glibc 2.0.7-19 and 2.0.7-29 RPMs are known to work.
If you are using Red Hat 8.0 or a new glibc 2.2.x library, you may see mysqld die in gethostbyaddr(). This happens because the new glibc library requires a stack size greater than 128KB for this call. To fix the problem, start mysqld with the --thread-stack=192K option. (Use -O thread_stack=192K before MySQL 4.) This stack size is the default on MySQL 4.0.10 and above, so you should not see the problem.
If you are using gcc 3.0 and above to compile MySQL, you must install the libstdc++v3 library before compiling MySQL; if you don’t do this, you get an error about a missing __cxa_pure_virtual symbol during linking.
On some older Linux distributions, configure may produce an error like this:
Syntax error in sched.h. Change _P to __P in the
/usr/include/sched.h file.
See the Installation chapter in the Reference Manual.
Just do what the error message says. Add an extra underscore to the _P macro name that has only one underscore, and then try again.
You may get some warnings when compiling. Those shown here can be ignored:
mysqld.cc -o objs-thread/mysqld.o
mysqld.cc: In function `void init_signals()':
mysqld.cc:315: warning: assignment of negative value `-1' to
`long unsigned int'
mysqld.cc: In function `void * signal_hand(void *)':
mysqld.cc:346: warning: assignment of negative value `-1' to
`long unsigned int'
If mysqld always dumps core when it starts, the problem may be that you have an old /lib/libc.a. Try renaming it, and then remove sql/mysqld and do a new make install and try again. This problem has been reported on some Slackware installations.
If you get the following error when linking mysqld, it means that your libg++.a is not installed correctly:
/usr/lib/libc.a(putc.o): In function `_IO_putc':
putc.o(.text+0x0): multiple definition of `_IO_putc'
You can avoid using libg++.a by running configure like this:
shell> CXX=gcc ./configure
2.4.18.1.6. Linux SPARC Notes
In some implementations, readdir_r() is broken. The symptom is that the SHOW DATABASES statement always returns an empty set. This can be fixed by removing HAVE_READDIR_R from config.h after configuring and before compiling.
2.4.18.1.7. Linux Alpha Notes
We have tested MySQL 5.0 on Alpha with our benchmarks and test suite, and it appears to work well.
We currently build the MySQL binary packages on SuSE Linux 7.0 for AXP, kernel 2.4.4-SMP, Compaq C compiler (V6.2-505) and Compaq C++ compiler (V6.3-006) on a Compaq DS20 machine with an Alpha EV6 processor.
You can find the preceding compilers at http://www.support.compaq.com/alpha-tools/. By using these compilers rather than gcc, we get about 9-14% better MySQL performance.
For MySQL on Alpha, we use the -arch generic flag to our compile options, which ensures that the binary runs on all Alpha processors. We also compile statically to avoid library problems. The configure command looks like this:
CC=ccc CFLAGS="-fast -arch generic" CXX=cxx
CXXFLAGS="-fast -arch generic -noexceptions -nortti"
./configure --prefix=/usr/local/mysql --disable-shared
--with-extra-charsets=complex --enable-thread-safe-client
--with-mysqld-ldflags=-non_shared --with-client-ldflags=-non_shared
If you want to use egcs, the following configure line worked for us:
CFLAGS="-O3 -fomit-frame-pointer" CXX=gcc
CXXFLAGS="-O3 -fomit-frame-pointer -felide-constructors
-fno-exceptions -fno-rtti"
./configure --prefix=/usr/local/mysql --disable-shared
Some known problems when running MySQL on Linux-Alpha:
- Debugging threaded applications like MySQL does not work with
gdb 4.18. You should use gdb 5.1 instead.
- If you try linking mysqld statically when using gcc, the resulting image dumps core at startup time. In other words, do not use
--with-mysqld-ldflags=-all-static with gcc.
2.4.18.1.8. Linux PowerPC Notes
MySQL should work on MkLinux with the newest glibc package (tested with glibc 2.0.7).
2.4.18.1.9. Linux MIPS Notes
To get MySQL to work on Qube2 (Linux Mips), you need the newest glibc libraries. glibc-2.0.7-29C2 is known to work. You must also use the egcs C++ compiler (egcs 1.0.2-9, gcc 2.95.2 or newer).
2.4.18.1.10. Linux IA-64 Notes
To get MySQL to compile on Linux IA-64, we use the following configure command for building with gcc 2.96:
CC=gcc
CFLAGS="-O3 -fno-omit-frame-pointer"
CXX=gcc
CXXFLAGS="-O3 -fno-omit-frame-pointer -felide-constructors
-fno-exceptions -fno-rtti"
./configure --prefix=/usr/local/mysql
"--with-comment=Official MySQL binary"
--with-extra-charsets=complex
On IA-64, the MySQL client binaries use shared libraries. This means that if you install our binary distribution at a location other than /usr/local/mysql, you need to add the path of the directory where you have libmysqlclient.so installed either to the /etc/ld.so.conf file or to the value of your LD_LIBRARY_PATH environment variable.
See Section B.1.3.1, “Problems Linking to the MySQL Client Library”.
2.4.18.1.11. SELinux Notes
RHEL4 comes with SELinux, which supports tighter access control for processes. If SELinux is enabled (SELINUX in /etc/selinux/config is set to enforcing, SELINUXTYPE is set to either targeted or strict), you might encounter problems installing MySQL AB RPM packages.
Red Hat has an update that solves this. It involves an update of the “security policy” specification to handle the install structure of the RPMs provided by MySQL AB. For further information, see https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=167551 and http://rhn.redhat.com/errata/RHBA-2006-0049.html.