The notes below 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 just use our binary. We link our binaries against the best patched version of glibc we can come up with and with the best compiler options, in an attempt to make it suitable for a high-load server.
So if you read the text below, and are in doubt about what you should do, try our binary first to see if it meets your needs, and worry about your own build only after you have discovered that our binary is not good enough. In that case, we would appreciate a note about it, so we can build a better binary next time. For a typical user, even for setups with a lot of concurrent connections and/or tables exceeding 2GB limit, our binary in most cases is the best choice.
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 get
LinuxThreads at http://www.mysql.com/Downloads/Linux/.
Note: We have seen some strange problems with Linux 2.2.14 and MySQL on SMP systems;
If you have a SMP system, we recommend you to upgrade to Linux 2.4 as soon as possible!
Your system will be faster and more stable by doing this!
Note that glibc versions before and including Version 2.1.1 have a fatal bug in pthread_
mutex_timedwait handling, which is used when you do INSERT DELAYED. We recommend
you to not use INSERT DELAYED before upgrading glibc.
If you plan to have 1000+ concurrent connections, you will need to make some changes to
LinuxThreads, recompile it, and relink MySQL against the new ‘libpthread.a’. Increase
PTHREAD_THREADS_MAX in ‘sysdeps/unix/sysv/linux/bits/local_lim.h’ to 4096 and de-
crease STACK_SIZE in ‘linuxthreads/internals.h’ to 256 KB. The paths are relative to
the root of glibc Note that MySQL will not be stable with around 600-1000 connections
if STACK_SIZE is the default of 2 MB.
If you have a problem with that MySQL can’t open enough files, or connections, it may be
that you haven’t configured Linux to handle enough files.
In Linux 2.2 and forwards, you can check the number of allocated file handlers by doing:
cat /proc/sys/fs/file-max
cat /proc/sys/fs/dquot-max
cat /proc/sys/fs/super-max
If you have more than 16M of memory, you should add something like the following in your
boot script (‘/etc/rc/boot.local’ on SuSE):
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 above from the command line as root, but in this case your old limits
will be used next time your computer reboots.
You should also add /etc/my.cnf:
[safe_mysqld]
open-files-limit=8192
The above should allow MySQL to create up to 8192 connections + 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 will be plenty of room for the
stack of each individual thread, but small enough to keep the stack of some thread from
running into the global mysqld data. Unfortunately, the Linux implementation of mmap(),
as we have experimentally discovered, will successfully unmap an already mapped region if
you ask it to map out an address already 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 "gentleman" behavior of the code that creates threads. The user must take
measures to make sure the number of running threads at any time is su ciently low for
hread stacks to stay away from the global heap. With mysqld, you should enforce this
"gentleman" behavior by setting a reasonable value for the max_connections variable.
If you build MySQL yourself and do not want to mess with patching LinuxThreads, you
should set max_connections to a value no higher than 500. It should be even less if you
have a large key bu er, 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 bu er or heap tables with lots of data. The more you reduce STACK_
SIZE in LinuxThreads the more threads you can safely create. We recommend the values
between 128K and 256K.
If you use a lot of concurrent connections, you may su er from a "feature" in the 2.2
kernel that penalises a process for forking or cloning a child in an attempt to prevent a
fork bomb attack. This will cause MySQL not to scale well as you increase the number
of concurrent clients. On single CPU systems, we have seen this manifested in a very
slow thread creation, which means it may take a long time to connect to MySQL (as long
as 1 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. Inthe process of trying to find a solution, we have received a kernel patch from one of ourusers, who claimed it made a lot of di erence for his site. The patch is available here
(http://www.mysql.com/Downloads/Patches/linux-fork.patch). We have now 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 now
recommend it to our users who are still running 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 just upgrade to 2.4,
which will also give you a nice SMP boost in addition to fixing this fairness bug.
We have tested MySQL on the 2.4 kernel on a 2 CPU machine and found MySQL scales
MUCH better - there was virtually no slowdown on query throughput all the way up to
1000 clients, and MySQL scaling factor ( computed as the ratio of maximum throughput to
the throughput with one client) was 180%. We have observed similar results on a 4-CPU
system - virtually no slowdown as the number of clients was increased up to 1000, and 300%
scaling factor.
So for a high-load SMP server we would definitely recommend the 2.4 kernel
at this point. We have discovered that it is essential to run mysqld process with the highest
possible priority on the 2.4 kernel to achieve maximum performance. This can be done
by adding renice -20 $$ command to safe_mysqld. In our testing on a 4-CPU machine,
increasing the priority gave 60% increase in throughput with 400 clients.
We are currently also trying to collect more info on how well MySQL performs on 2.4 kernel on
4-way and 8-way systems. If you have access such a system and have done some benchmarks,
please send a mail to docs@mysql.com with the results - we will include them in the manual.
There is another issue that greatly hurts MySQL performance, especially on SMP systems.
The implementation of mutex in LinuxThreads in glibc-2.1 is very bad for programs with
many threads that only hold the mutex for a short time. On an SMP system, ironic as it is, if
you link MySQL against unmodified LinuxThreads, removing processors from the machine
improves MySQL performance in many cases. We have made a patch available for glibc 2.1.3
to correct this behavior (http://www.mysql.com/Downloads/Linux/linuxthreads-2.1-patch).
MySQL Installation
With glibc-2.2.2 MySQL version 3.23.36 will use 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 chance of this condition can be reduced by renicing mysqld process to the highest priority. We have also been able to correct the overspin behavior with a patch, available at
http://www.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
will need to apply it in the linuxthreads directory with patch -p0 cp support-files/mysql.server /etc/init.d/mysql.server
shell> /usr/sbin/update-rc.d mysql.server defaults 99
mysql.server can be found in the ‘share/mysql’ directory under the MySQL installation
directory or in the ‘support-files’ directory of the MySQL source tree.
If mysqld always core dumps when it starts up, the problem may be that you have an old
‘/lib/libc.a’. Try renaming it, 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
If you are running gcc 3.0 and above, you can’t use the above trick with CXX=gcc, but you
have to install libstd++.
Linux SPARC Notes.
In some implementations, readdir_r() is broken. The symptom is that SHOW DATABASES al-
ways returns an empty set. This can be fixed by removing HAVE_READDIR_R from ‘config.h’
after configuring and before compiling.
Some problems will require patching your Linux installation. The patch can be found at
http://www.mysql.com/Downloads/patches/Linux-sparc-2.0.30.diff. This patch is
against the Linux distribution ‘sparclinux-2.0.30.tar.gz’ that is available at vger.rutgers.edu
(a version of Linux that was never merged with the o cial 2.0.30). You must also install
LinuxThreads Version 0.6 or newer.
Linux Alpha Notes.
MySQL Version 3.23.12 is the first MySQL version that is tested on Linux-Alpha. If you
plan to use MySQL on Linux-Alpha, you should ensure that you have this version or newer.
We have tested MySQL on Alpha with our benchmarks and test suite, and it appears to
work nicely. The main thing we haven’t yet had time to test is how things works with many
concurrent users.
When we compiled the standard MySQL binary we are using SuSE 6.4, kernel 2.2.13-SMP,
Compaq C compiler (V6.2-504) and Compaq C++ compiler (V6.3-005) on a Comaq DS20
machine with an Alpha EV6 processor.
You can find the above compilers at http://www.support.compaq.com/alpha-tools/).
By using these compilers, instead of gcc, we get about 9-14 % better performance with
MySQL.
Note that the configure line optimised the binary for the current CPU; This means you
can only use our binary if you have an Alpha EV6 processor. We also compile statically to
avoid library problems.
CC=ccc CFLAGS="-fast" CXX=cxx CXXFLAGS="-fast -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 will not work with gdb 4.18. You should
download and use gdb 5.0 instead!
• If you try linking mysqld statically when using gcc, the resulting image will core dump
at start. In other words, don’t use --with-mysqld-ldflags=-all-static with gcc.
Linux PowerPC Notes
MySQL should work on MkLinux with the newest glibc package (tested with glibc 2.0.7).
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).
Linux IA64 Notes
To get MySQL to compile on Linux Ia64, we use the following compile line: Using 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 Ia64 the MySQL client binaries are using shared libraries. This means that if you
install our binary distribution in some other place than ‘/usr/local/mysql’ you need
to either modify ‘/etc/ld.so.conf’ or add the path to the directory where you have
‘libmysqlclient.so’ to the LD_LIBRARY_PATH environment variable.
If you liked this article, subscribe to the feed by clicking the image below to keep informed about new contents of the blog:
0 commenti:
Post a Comment