FreeBSD 4.x or newer is recommended for running MySQL, because
the thread package is much more integrated. To get a secure
and stable system, you should use only FreeBSD kernels that
are marked -RELEASE
.
The easiest (and preferred) way to install MySQL is to use the
mysql-server and
mysql-client
ports available at
http://www.freebsd.org/. Using these ports
gives you the following benefits:
A working MySQL with all optimizations enabled that are known to work on your version of FreeBSD.
Automatic configuration and build.
Startup scripts installed in
/usr/local/etc/rc.d
.
The ability to use pkg_info -L
to see
which files are installed.
The ability to use pkg_delete
to remove
MySQL if you no longer want it on your machine.
It is recommended you use MIT-pthreads on FreeBSD 2.x, and native threads on FreeBSD 3 and up. It is possible to run with native threads on some late 2.2.x versions, but you may encounter problems shutting down mysqld.
Unfortunately, certain function calls on FreeBSD are not yet
fully thread-safe. Most notably, this includes the
gethostbyname()
function, which is used by
MySQL to convert host names into IP addresses. Under certain
circumstances, the mysqld process suddenly
causes 100% CPU load and is unresponsive. If you encounter
this problem, try to start MySQL using the
--skip-name-resolve
option.
Alternatively, you can link MySQL on FreeBSD 4.x against the LinuxThreads library, which avoids a few of the problems that the native FreeBSD thread implementation has. For a very good comparison of LinuxThreads versus native threads, see Jeremy Zawodny's article FreeBSD or Linux for your MySQL Server? at http://jeremy.zawodny.com/blog/archives/000697.html.
Known problem when using LinuxThreads on FreeBSD is:
The connection times
(wait_timeout
,
interactive_timeout
and
net_read_timeout
) values
are not honored. The symptom is that persistent
connections can hang for a very long time without getting
closed down and that a 'kill' for a thread will not take
affect until the thread does it a new command
This is probably a signal handling problem in the thread library where the signal doesn't break a pending read. This is supposed to be fixed in FreeBSD 5.0
The MySQL build process requires GNU make (gmake) to work. If GNU make is not available, you must install it first before compiling MySQL.
The recommended way to compile and install MySQL on FreeBSD with gcc (2.95.2 and up) is:
CC=gcc CFLAGS="-O2 -fno-strength-reduce" \ CXX=gcc CXXFLAGS="-O2 -fno-rtti -fno-exceptions \ -felide-constructors -fno-strength-reduce" \ ./configure --prefix=/usr/local/mysql --enable-assembler gmake gmake install cd /usr/local/mysql bin/mysql_install_db --user=mysql bin/mysqld_safe &
If you notice that configure uses MIT-pthreads, you should read the MIT-pthreads notes. See Section 2.10.5, “MIT-pthreads Notes”.
If you get an error from make install that
it can't find /usr/include/pthreads
,
configure didn't detect that you need
MIT-pthreads. To fix this problem, remove
config.cache
, and then re-run
configure with the
--with-mit-threads
option.
Be sure that your name resolver setup is correct. Otherwise,
you may experience resolver delays or failures when connecting
to mysqld. Also make sure that the
localhost
entry in the
/etc/hosts
file is correct. The file
should start with a line similar to this:
127.0.0.1 localhost localhost.your.domain
FreeBSD is known to have a very low default file handle limit.
See Section B.1.2.18, “'File
' Not Found and
Similar Errors”. Start the
server by using the
--open-files-limit
option
for mysqld_safe, or raise the limits for
the mysqld user in
/etc/login.conf
and rebuild it with
cap_mkdb /etc/login.conf
. Also be sure that
you set the appropriate class for this user in the password
file if you are not using the default (use chpass
mysqld-user-name
). See
Section 4.3.2, “mysqld_safe — MySQL Server Startup Script”.
FreeBSD limits the size of a process to 512MB, even if you have much more RAM available on the system. So you may get an error such as this:
Out of memory (Needed 16391 bytes)
In current versions of FreeBSD (at least 4.x and greater), you
may increase this limit by adding the following entries to the
/boot/loader.conf
file and rebooting the
machine (these are not settings that can be changed at run
time with the sysctl command):
kern.maxdsiz="1073741824" # 1GB kern.dfldsiz="1073741824" # 1GB kern.maxssiz="134217728" # 128MB
For older versions of FreeBSD, you must recompile your kernel
to change the maximum data segment size for a process. In this
case, you should look at the MAXDSIZ
option
in the LINT
config file for more
information.
If you get problems with the current date in MySQL, setting
the TZ
variable should help. See
Section 2.14, “Environment Variables”.
User Comments
If you're going to use MySQL on FreeBSD at 2 or
more CPU's machine, please note, that MySQL would
use only one CPU.
It's because of FreeBSD cannot optimize threads
between multiple CPUs (this feature should appear
in 5.0).
If you want to get full efficiency of
such system, you should compile MySQL with
LinuxThreads, and all optimization, recomended
below. NB: you SHOULD use FreeBSD 4.4 or later,
since older versions work bugly with
LinuxThreads.
On high volume servers using MySQL with FreeBSD
it is also recommended increasing MAXUSERS (i.e.
basically recompiling/rebuilding your kernel). <a
href="http://www.freebsdforums.org/forums">FreeBSD
Forums</a>
For a guide to securing MySQL on FreeBSD, check
out <a
href="http://www.freebsdforums.org/forums/showthre
ad.php?threadid=835">this</a> informative article!
jeremy zawodny's got a heap of interesting info relative to running mysql on freebsd (4.x). check it out here: http://jeremy.zawodny.com/blog/archives/000203.html
When the DB server encounters many query,
I got these error messages when I upgrade from
MySQL 3.23 to MySQL 4.0.12 on a FreeBSD 4.8-PRERELEASE
I query the database via JDBC with the 3.0.6-stable Java API
It seems that there is no problem when using C API
=== cut ===
ERROR 1030: Got error 12 from table handler
ERROR 1135: Can't create a new thread (errno 35). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug
=== cut ===
For those of you using FreeBSD 4.8/4.9 (at least, I don't know about prior to those versions), you can use /usr/ports/databases to install MySQL. If you configure the Makefile with the line
WITH_LINUXTHREADS=yes
MySQL will automatically compile and install LinuxThreads for you, as well as compiling and installing itself in the default directories.
Something rather convenient, after you've been beating your head against the wall for 13 hours because it can't find LinuxThreads when you compile MySQL from source.
freebsd 5.3 (but I believe 4.x aswell)
the MAXDSIZ has been discussed above. You might want to increase MAXSSIZE aswell; I found that a low MAXSSIZ limited the amount of simultaneos connections (and therefore running threads) to the mysql server. (build with linux-thread)
this is if you have several 100 connections at the same time (even if they are idle)
On FreeBSD 5.x, there is a hard limit default compiled into the kernel which limits the amount of memory a process can use to 512MB. You can see this by running the command "limits."
You can, without recompiling the kernel, increase this maximum process size memory limit to something higher, but absolutely below your physical memory. I.e. if you have 1GB of memory, do not set your per process memory limit equal to or higher than that! FreeBSD 5.x will crash and not be able to be recovered without a local change being made if you set that limit higher than the amount of physical memory . (FreeBSD Bug: http://www.freebsd.org/cgi/query-pr.cgi?pr=84656)
In /boot/loader.conf, add these lines (on a 1.5GB box or greater):
kern.maxdsiz="1073741824" # 1GB
kern.dfldsiz="1073741824" # 1GB
kern.maxssiz="134217728" # 128MB
Then reboot, and increase your my.cnf memory configuration values in order to increase mysql memory usage.
The Operating system error number you will see is 12 (ENOMEM) Out of memory:
InnoDB: Error: cannot allocate 797284 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 6299504 bytes. Operating system errno: 12
InnoDB: Check if you should increase the swap file or
InnoDB: ulimits of your operating system.
InnoDB: On FreeBSD check you have compiled the OS with
InnoDB: a big enough maximum process size.
InnoDB: We keep retrying the allocation for 60 seconds...
InnoDB: Fatal error: cannot allocate the memory for the buffer pool
For FreeBSD 6 tests show that using libthr gives the best performance (search on the net for some benchmarks).
To use libthr, just download the precompiled mysql for FreeBSD 6, and add the following lines to your /etc/libmap.conf :
[mysqld]
libpthread.so.2 libthr.so.2
libpthread.so libthr.so
After that restart your mysqld, and from then on it should be using libthr.
Since there are no any "make config" options in mysql51-server port (thanx to the mantainer) we can still set compile options by editing /etc/make.conf
Try adding this to /etc/make.conf:
-----------------
PORTSDIR?=/usr/ports
.if ${.CURDIR} == ${PORTSDIR}/databases/mysql51-server
WITH_CHARSET=utf8
WITH_COLLATION=utf8_bin
WITH_SSL=no
WITH_PROC_SCOPE_PTH=yes
BUILD_OPTIMIZED=yes
BUILD_STATIC=yes
WITH_ARCHIVE=no
.endif
.if ${.CURDIR} == ${PORTSDIR}/databases/mysql51-client
WITH_CHARSET=utf8
WITH_COLLATION=utf8_bin
BUILD_OPTIMIZED=yes
.endif
--------------
Of course you may use any other options you like.
It's still not clear for me is it good idea to use:
WITH_LINUXTHREADS=yes
WITH_PROC_SCOPE_PTH=yes
WITH_FAST_MUTEXES=yes
and it's not clear what they do
Add your own comment.