| |
Porting to Other Systems
************************
let us
know so that we can list it here and on our web site
(`http://www.mysql.com/'), recommending it to other users.
Note: If you create a new port of MySQL, you are free to copy and
distribute it under the `GPL' license, but it does not make you a
copyright holder of MySQL.
Xavier
Leroy, .
The hard part of porting to a new Unix variant without good native
thread support is probably to port MIT-pthreads. See
`mit-pthreads/README' and Programming POSIX Threads
(`http://www.humanfactor.com/pthreads/').
Up to MySQL 4.0.2, the MySQL distribution included a patched version of
Chris Provenzano's Pthreads from MIT (see the MIT Pthreads web page at
`http://www.mit.edu/afs/sipb/project/pthreads/' and a programming
introduction at `http://www.mit.edu:8001/people/proven/IAP_2000/').
These can be used for some operating systems that do not have POSIX
threads. *Note MIT-pthreads::.
It is also possible to use another user level thread package named FSU
Pthreads (see `http://moss.csc.ncsu.edu/~mueller/pthreads/'). This
implementation is being used for the SCO port.
See the `thr_lock.c' and `thr_alarm.c' programs in the `mysys'
directory for some tests/examples of these problems.
Intel
`ecc' and Compaq `cxx').
To compile only the client use `./configure --without-server'.
There is currently no support for only compiling the server, nor is it
likly to be added unless someone has a good reason for it.
If you want/need to change any `Makefile' or the configure script you
will also need GNU Automake and Autoconf. *Note Installing source
tree::.
All steps needed to remake everything from the most basic files.
/bin/rm */.deps/*.P
/bin/rm -f config.cache
aclocal
autoheader
aclocal
automake
autoconf
./configure --with-debug=full --prefix='your installation directory'
# The makefiles generated above need GNU make 3.75 or newer.
# (called gmake below)
gmake clean all install init-db
If you run into problems with a new port, you may have to do some
debugging of MySQL! *Note Debugging server::.
Debugging a MySQL server
========================
If you are using some functionality that is very new in MySQL, you can
try to run `mysqld' with the `--skip-new' (which will disable all new,
potentially unsafe functionality) or with `--safe-mode' which disables
a lot of optimisation that may cause problems. *Note Crashing::.
If `mysqld' doesn't want to start, you should check that you don't have
any `my.cnf' files that interfere with your setup! You can check your
`my.cnf' arguments with `mysqld --print-defaults' and avoid using them
by starting with `mysqld --no-defaults ...'.
If `mysqld' starts to eat up CPU or memory or if it "hangs", you can
use `mysqladmin processlist status' to find out if someone is executing
a query that takes a long time. It may be a good idea to run
`mysqladmin -i10 processlist status' in some window if you are
experiencing performance problems or problems when new clients can't
connect.
The command `mysqladmin debug' will dump some information about locks
in use, used memory and query usage to the mysql log file. This may
help solve some problems. This command also provides some useful
information even if you haven't compiled MySQL for debugging!
If the problem is that some tables are getting slower and slower you
should try to optimise the table with `OPTIMIZE TABLE' or `myisamchk'.
*Note MySQL Database Administration::. You should also check the slow
queries with `EXPLAIN'.
You should also read the OS-specific section in this manual for
problems that may be unique to your environment. *Note Operating
System Specific Notes::.
Compiling MYSQL for Debugging
-----------------------------
If you have some very specific problem, you can always try to debug
MySQL. To do this you must configure MySQL with the `--with-debug' or
the `--with-debug=full' option. You can check whether MySQL was
compiled with debugging by doing: `mysqld --help'. If the `--debug'
flag is listed with the options then you have debugging enabled.
`mysqladmin ver' also lists the `mysqld' version as `mysql ... --debug'
in this case.
If you are using gcc or egcs, the recommended configure line is:
This will avoid problems with the `libstdc++' library and with C++
exceptions (many compilers have problems with C++ exceptions in threaded
code) and compile a MySQL version with support for all character sets.
If you suspect a memory overrun error, you can configure MySQL with
`--with-debug=full', which will install a memory allocation
(`SAFEMALLOC') checker. However, running with `SAFEMALLOC' is quite
slow, so if you get performance problems you should start `mysqld' with
the `--skip-safemalloc' option. This will disable the memory overrun
checks for each call to `malloc()' and `free()'.
If `mysqld' stops crashing when you compile it with `--with-debug', you
have probably found a compiler bug or a timing bug within MySQL. In
this case you can try to add `-g' to the `CFLAGS' and `CXXFLAGS'
variables above and not use `--with-debug'. If `mysqld' now dies, you
can at least attach to it with `gdb' or use `gdb' on the core file to
find out what happened.
When you configure MySQL for debugging you automatically enable a lot
of extra safety check functions that monitor the health of `mysqld'.
If they find something "unexpected," an entry will be written to
`stderr', which `safe_mysqld' directs to the error log! This also
means that if you are having some unexpected problems with MySQL and
are using a source distribution, the first thing you should do is to
configure MySQL for debugging! (The second thing, of course, is to
send mail to a MySQL mailing list and ask for help. *Note
Mailing-list::. Please use the `mysqlbug' script for all bug reports
or questions regarding the MySQL version you are using!
In the Windows MySQL distribution, `mysqld.exe' is by default compiled
with support for trace files.
Creating Trace Files
--------------------
If the `mysqld' server doesn't start or if you can cause the `mysqld'
server to crash quickly, you can try to create a trace file to find the
problem.
To do this you have to have a `mysqld' that is compiled for debugging.
You can check this by executing `mysqld -V'. If the version number
ends with `-debug', it's compiled with support for trace files.
Start the `mysqld' server with a trace log in `/tmp/mysqld.trace' (or
`C:\mysqld.trace' on Windows):
`mysqld --debug'
On Windows you should also use the `--standalone' flag to not start
`mysqld' as a service:
In a console window do:
mysqld --debug --standalone
After this you can use the `mysql.exe' command-line tool in a second
console window to reproduce the problem. You can take down the above
`mysqld' server with `mysqladmin shutdown'.
Note that the trace file will get *very big*! If you want to have a
smaller trace file, you can use something like:
`mysqld --debug=d,info,error,query,general,where:O,/tmp/mysqld.trace'
which only prints information with the most interesting tags in
`/tmp/mysqld.trace'.
the trace file,
together with a full bug report, to
`ftp://support.mysql.com/pub/mysql/secret/' so that a MySQL developer
can take a look a this.
The trace file is made with the *DBUG* package by Fred Fish. *Note The
DBUG package::.
Debugging mysqld under gdb
--------------------------
On most systems you can also start `mysqld' from `gdb' to get more
information if `mysqld' crashes.
With some older `gdb' versions on Linux you must use `run --one-thread'
if you want to be able to debug `mysqld' threads. In this case you can
only have one thread active at a time. We recommend you to upgrade to
gdb 5.1 ASAP as thread debugging works much better with this version!
When running `mysqld' under gdb, you should disable the stack trace
with `--skip-stack-trace' to be able to catch segfaults within gdb.
In MySQL 4.0.14 and above you should use the `--gdb' option to mysqld.
This will install an interrupt handler for `SIGINT' (needed to stop
mysqld with `^C' to set breakpoints) and disable stack tracing and core
file handling.
It's very hard to debug MySQL under `gdb' if you do a lot of new
connections the whole time as `gdb' doesn't free the memory for old
threads. You can avoid this problem by starting `mysqld' with `-O
thread_cache_size= 'max_connections +1''. In most cases just using `-O
thread_cache_size=5'' will help a lot!
If you want to get a core dump on Linux if `mysqld' dies with a SIGSEGV
signal, you can start `mysqld' with the `--core-file' option. This
core file can be used to make a backtrace that may help you find out
why `mysqld' died:
shell> gdb mysqld core
gdb> backtrace full
gdb> exit
*Note Crashing::.
If you are using gdb 4.17.x or above on Linux, you should install a
`.gdb' file, with the following information, in your current directory:
set print sevenbit off
handle SIGUSR1 nostop noprint
handle SIGUSR2 nostop noprint
handle SIGWAITING nostop noprint
handle SIGLWP nostop noprint
handle SIGPIPE nostop
handle SIGALRM nostop
handle SIGHUP nostop
handle SIGTERM nostop noprint
If you have problems debugging threads with gdb, you should download
gdb 5.x and try this instead. The new gdb version has very improved
thread handling!
Here is an example how to debug mysqld:
shell> gdb /usr/local/libexec/mysqld
gdb> run
...
backtrace full # Do this when mysqld crashes
Include the above output in a mail generated with `mysqlbug' and mail
this to the general MySQL mailing list. *Note Mailing-list::.
If `mysqld' hangs you can try to use some system tools like `strace' or
`/usr/proc/bin/pstack' to examine where `mysqld' has hung.
strace /tmp/log libexec/mysqld
If you are using the Perl `DBI' interface, you can turn on debugging
information by using the `trace' method or by setting the `DBI_TRACE'
environment variable. *Note Perl `DBI' Class: Perl DBI Class.
Using a Stack Trace
-------------------
trace, you
must not compile `mysqld' with the `-fomit-frame-pointer' option to
gcc. *Note Compiling for debugging::.
If the error file contains something like the following:
mysqld got signal 11;
The manual section 'Debugging a MySQL server' tells you how to use a
stack trace and/or the core file to produce a readable backtrace that may
help in finding out why mysqld died
Attemping backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong
stack range sanity check, ok, backtrace follows
0x40077552
0x81281a0
0x8128f47
0x8127be0
0x8127995
0x8104947
0x80ff28f
0x810131b
0x80ee4bc
0x80c3c91
0x80c6b43
0x80c1fd9
0x80c1686
you can find where `mysqld' died by doing the following:
1. Copy the above numbers to a file, for example `mysqld.stack'.
2. Make a symbol file for the `mysqld' server:
nm -n libexec/mysqld > /tmp/mysqld.sym
`mysqld.sym.gz'. In this case you can simply unpack it by doing:
gunzip < bin/mysqld.sym.gz > /tmp/mysqld.sym
3. Execute `resolve_stack_dump -s /tmp/mysqld.sym -n mysqld.stack'.
This will print out where `mysqld' died. If this doesn't help you
find out why `mysqld' died, you should make a bug report and
include the output from the above command with the bug report.
Note however that in most cases it will not help us to just have a
stack trace to find the reason for the problem. To be able to
locate the bug or provide a workaround, we would in most cases
need to know the query that killed `mysqld' and preferable a test
case so that we can repeat the problem! *Note Bug reports::.
Using Log Files to Find Cause of Errors in mysqld
-------------------------------------------------
Note that before starting `mysqld' with `--log' you should check all
your tables with `myisamchk'. *Note MySQL Database Administration::.
If `mysqld' dies or hangs, you should start `mysqld' with `--log'.
When `mysqld' dies again, you can examine the end of the log file for
the query that killed `mysqld'.
should verify
this by restarting `mysqld' and executing the found query from the
`mysql' command-line tools. If this works, you should also test all
complicated queries that didn't complete.
You can also try the command `EXPLAIN' on all `SELECT' statements that
takes a long time to ensure that `mysqld' is using indexes properly.
*Note `EXPLAIN': EXPLAIN.
You can find the queries that take a long time to execute by starting
`mysqld' with `--log-slow-queries'. *Note Slow query log::.
If you find the text `mysqld restarted' in the error log file (normally
named `hostname.err') you have probably found a query that causes
`mysqld' to fail. If this happens you should check all your tables
with `myisamchk' (*note MySQL Database Administration::), and test the
queries in the MySQL log files to see if one doesn't work. If you find
such a query, try first upgrading to the newest MySQL version. If this
doesn't help and you can't find anything in the `mysql' mail archive,
you should report the bug to a MySQL mailing list. The mailing lists
are described at `http://lists.mysql.com/', which also has links to
online list archives.
If you have started `mysqld' with `myisam-recover', MySQL will
automatically check and try to repair `MyISAM' tables if they are
marked as 'not closed properly' or 'crashed'. If this happens, MySQL
will write an entry in the `hostname.err' file `'Warning: Checking
table ...'' which is followed by `Warning: Repairing table' if the
table needs to be repaired. If you get a lot of these errors, without
`mysqld' having died unexpectedly just before, then something is wrong
and needs to be investigated further. *Note Command-line options::.
It's of course not a good sign if `mysqld' did died unexpectedly, but
in this case one shouldn't investigate the `Checking table...' messages
but instead try to find out why `mysqld' died.
Making a Test Case If You Experience Table Corruption
-----------------------------------------------------
If you get corrupted tables or if `mysqld' always fails after some
update commands, you can test if this bug is reproducible by doing the
following:
* Take down the MySQL daemon (with `mysqladmin shutdown').
* Make a backup of the tables (to guard against the very unlikely
case that the repair will do something bad).
* Check all tables with `myisamchk -s database/*.MYI'. Repair any
wrong tables with `myisamchk -r database/table.MYI'.
* Make a second backup of the tables.
* Remove (or move away) any old log files from the MySQL data
directory if you need more space.
* Start `mysqld' with `--log-bin'. *Note Binary log::. If you want
to find a query that crashes `mysqld', you should use `--log
--log-bin'.
* When you have gotten a crashed table, stop the `mysqld server'.
* Restore the backup.
* Restart the `mysqld' server *without* `--log-bin'
* Re-execute the commands with `mysqlbinlog update-log-file | mysql'.
The update log is saved in the MySQL database directory with the
name `hostname-bin.#'.
`ftp://support.mysql.com/pub/mysql/secret/' and enter it into our
bugs system at `http://bugs.mysql.com/'. If you are a support
customer), you can also to alert the MySQL
team about the problem and have it fixed as soon as possible.
You can also use the script `mysql_find_rows' to just execute some of
the update statements if you want to narrow down the problem.
Debugging a MySQL client
========================
To be able to debug a MySQL client with the integrated debug package,
you should configure MySQL with `--with-debug' or `--with-debug=full'.
*Note configure options::.
Before running a client, you should set the `MYSQL_DEBUG' environment
variable:
shell> MYSQL_DEBUG=d:t:O,/tmp/client.trace
shell> export MYSQL_DEBUG
This causes clients to generate a trace file in `/tmp/client.trace'.
you have
compiled MySQL with debugging on):
shell> mysql --debug=d:t:O,/tmp/client.trace
This will provide useful information in case you mail a bug report.
*Note Bug reports::.
old MySQL
installation with new MySQL library.
The DBUG Package
================
of
what the program is debugging. *Note Making trace files::.
One uses the debug package by invoking the program with the
`--debug="..."' or the `-#...' option.
Most MySQL programs has a default debug string that will be used if you
don't specify an option to `--debug'. The default trace file is
usually `/tmp/programname.trace' on Unix and `\programname.trace' on
Windows.
The debug control string is a sequence of colon-separated fields as
follows:
::...:
Each field consists of a mandatory flag character followed by an
optional "," and comma-separated list of modifiers:
flag[,modifier,modifier,...,modifier]
The currently recognised flag characters are:
*Flag**Description*
d Enable output from DBUG_ macros.
D Delay after each debugger output line. The argument is the number
of tenths of seconds to delay, subject to machine capabilities.
That is, `-#D,20' is delay two seconds.
f Limit debugging and/or tracing, and profiling to the list of named
functions. Note that a null list will disable all functions. The
appropriate "d" or "t" flags must still be given, this flag only
limits their actions if they are enabled.
F Identify the source file name for each line of debug or trace
output.
i Identify the process with the PID or thread ID for each line of
debug or trace output.
g Enable profiling. Create a file called 'dbugmon.out' containing
information that can be used to profile the program. May be
followed by a list of keywords that select profiling only for the
functions in that list. A null list implies that all functions are
considered.
L Identify the source file line number for each line of debug or
trace output.
n Print the current function nesting depth for each line of debug or
trace output.
N Number each line of dbug output.
o Redirect the debugger output stream to the specified file. The
default output is stderr.
O As `o' but the file is really flushed between each write. When
needed the file is closed and reopened between each write.
p Limit debugger actions to specified processes. A process must be
identified with the DBUG_PROCESS macro and match one in the list
for debugger actions to occur.
P Print the current process name for each line of debug or trace
output.
r When pushing a new state, do not inherit the previous state's
function nesting level. Useful when the output is to start at the
left margin.
S Do function _sanity(_file_,_line_) at each debugged function until
_sanity() returns something that differs from 0. (Mostly used with
safemalloc to find memory leaks)
t Enable function call/exit trace lines. May be followed by a list
(containing only one modifier) giving a numeric maximum trace
level, beyond which no output will occur for either debugging or
tracing macros. The default is a compile time option.
Some examples of debug control strings which might appear on a shell
command-line (the "-#" is typically used to introduce a control string
to an application program) are:
-#d:t
-#d:f,main,subr1:F:L:t,20
-#d,input,output,files:n
-#d:t:i:O,\\mysqld.trace
In MySQL, common tags to print (with the `d' option) are:
`enter',`exit',`error',`warning',`info' and `loop'.
Locking methods
===============
Currently MySQL only supports table locking for `ISAM'/`MyISAM' and
`HEAP' tables, page-level locking for `BDB' tables and row-level
locking for `InnoDB' tables. *Note Internal locking::. With `MyISAM'
tables one can freely mix `INSERT' and `SELECT' without locks, if the
`INSERT' statements are non-conflicting (that is, whenever they append
to the end of the table file rather than filling freespace from deleted
rows/data).
Starting in version 3.23.33, you can analyse the table lock contention
on your system by checking `Table_locks_waited' and
`Table_locks_immediate' environment variables.
To decide if you want to use a table type with row-level locking, you
will want to look at what the application does and what the
select/update pattern of the data is.
Pros for row locking:
* Fewer lock conflicts when accessing different rows in many threads.
* Fewer changes for rollbacks.
* Makes it possible to lock a single row a long time.
Cons:
* Takes more memory than page level or table locks.
* Is slower than page level or table locks when used on a big part
of the table, because one has to do many more locks.
* Is definitely much worse than other locks if you do often do `GROUP
BY' on a large part of the data or if one has to often scan the
whole table.
* With higher level locks one can also more easily support locks of
different types to tune the application as the lock overhead is
less notable as for row level locks.
Table locks are superior to page level / row level locks in the
following cases:
* Mostly reads
FROM table_name WHERE unique_key=#
* `SELECT' combined with `INSERT' (and very few `UPDATE' and
`DELETE' statements).
* Many scans / `GROUP BY' on the whole table without any writers.
Other options than row / page level locking:
on when
one started to access it. Other names for this are time travel, copy on
write or copy on demand.
Copy on demand is in many case much better than page or row level
locking; the worst case does, however, use much more memory than when
using normal locks.
Instead of using row level locks one can use application level locks
(like get_lock/release_lock in MySQL). This works of course only in
well-behaved applications.
In many cases one can do an educated guess which locking type is best
for the application, but generally it's very hard to say that a given
lock type is better than another; everything depends on the application
and different part of the application may require different lock types.
Here are some tips about locking in MySQL:
Most web applications do lots of selects, very few deletes, updates
mainly on keys, and inserts in some specific tables. The base MySQL
setup is very well tuned for this.
Concurrent users are not a problem if one doesn't mix updates with
selects that need to examine many rows in the same table.
If one mixes inserts and deletes on the same table then `INSERT DELAYED'
may be of great help.
One can also use `LOCK TABLES' to speed up things (many updates within
a single lock is much faster than updates without locks). Splitting
thing to different tables will also help.
If you get speed problems with the table locks in MySQL, you may be
able to solve these by converting some of your tables to `InnoDB' or
`BDB' tables. *Note InnoDB::. *Note BDB::.
The optimisation section in the manual covers a lot of different
aspects of how to tune applications. *Note Tips::.
Comments about RTS threads
==========================
I have tried to use the RTS thread packages with MySQL but stumbled on
the following problems:
They use an old version of a lot of POSIX calls and it is very tedious
to make wrappers for all functions. I am inclined to think that it would
be easier to change the thread libraries to the newest POSIX
specification.
Some wrappers are already written. See `mysys/my_pthread.c' for more
info.
At least the following should be changed:
Now
they return -1 and set `errno'.
easy
to verify it.
The biggest unsolved problem is the following:
To get thread-level alarms I changed `mysys/thr_alarm.c' to wait between
alarms with `pthread_cond_timedwait()', but this aborts with error
`EINTR'. I tried to debug the thread library as to why this happens,
but couldn't find any easy solution.
If someone wants to try MySQL with RTS threads I suggest the following:
* Change functions MySQL uses from the thread library to POSIX.
This shouldn't take that long.
* Compile all libraries with the `-DHAVE_rts_threads'.
* Compile `thr_alarm'.
* If there are some small differences in the implementation, they
may be fixed by changing `my_pthread.h' and `my_pthread.c'.
* Run `thr_alarm'. If it runs without any "warning", "error" or
aborted messages, you are on the right track. Here is a successful
run on Solaris:
Main thread: 1
Thread 0 (5) started
Thread: 5 Waiting
process_alarm
Thread 1 (6) started
Thread: 6 Waiting
process_alarm
process_alarm
thread_alarm
Thread: 6 Slept for 1 (1) sec
Thread: 6 Waiting
process_alarm
process_alarm
thread_alarm
Thread: 6 Slept for 2 (2) sec
Thread: 6 Simulation of no alarm needed
Thread: 6 Slept for 0 (3) sec
Thread: 6 Waiting
process_alarm
process_alarm
thread_alarm
Thread: 6 Slept for 4 (4) sec
Thread: 6 Waiting
process_alarm
thread_alarm
Thread: 5 Slept for 10 (10) sec
Thread: 5 Waiting
process_alarm
process_alarm
thread_alarm
Thread: 6 Slept for 5 (5) sec
Thread: 6 Waiting
process_alarm
process_alarm
...
thread_alarm
Thread: 5 Slept for 0 (1) sec
end
Differences between different thread packages
=============================================
MySQL is very dependent on the thread package used. So when choosing a
good platform for MySQL, the thread package is very important.
There are at least three types of thread packages:
* User threads in a single process. Thread switching is managed with
alarms and the threads library manages all non-thread-safe
functions with locks. Read, write and select operations are
usually managed with a thread-specific select that switches to
another thread if the running threads have to wait for data. If
the user thread packages are integrated in the standard libs
(FreeBSD and BSDI threads) the thread package requires less
overhead than thread packages that have to map all unsafe calls
(MIT-pthreads, FSU Pthreads and RTS threads). In some
environments (for example, SCO), all system calls are thread-safe
so the mapping can be done very easily (FSU Pthreads on SCO).
Downside: All mapped calls take a little time and it's quite
tricky to be able to handle all situations. There are usually also
some system calls that are not handled by the thread package (like
MIT-pthreads and sockets). Thread scheduling isn't always optimal.
* User threads in separate processes. Thread switching is done by the
kernel and all data are shared between threads. The thread package
manages the standard thread calls to allow sharing data between
threads. LinuxThreads is using this method. Downside: Lots of
processes. Thread creating is slow. If one thread dies the rest
are usually left hanging and you must kill them all before
restarting. Thread switching is somewhat expensive.
* Kernel threads. Thread switching is handled by the thread library
or the kernel and is very fast. Everything is done in one process,
but on some systems, `ps' may show the different threads. If one
thread aborts, the whole process aborts. Most system calls are
thread-safe and should require very little overhead. Solaris,
HP-UX, AIX and OSF/1 have kernel threads.
In some systems kernel threads are managed by integrating user level
threads in the system libraries. In such cases, the thread switching
can only be done by the thread library and the kernel isn't really
"thread aware".
[Назад]
[Содержание]
[Вперед]
|
