A working Posix thread library is needed for the server. On Solaris 2.5 we use Sun PThreads (the native thread support in 2.4 and earlier versions are not good enough) and on Linux we use LinuxThreads by Xavier Leroy, Xavier.Leroy@inria.fr.
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.
The MySQL distribution includes a patched version of Provenzano's Pthreads from MIT (see MIT Pthreads web page). This can be used for some operating systems that do not have POSIX threads.
It is also possible to use another user level thread package named FSU Pthreads (see FSU Pthreads home page). 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.
Both the server and the client need a working C++ compiler (we use gcc
and have tried SparcWorks). Another compiler that is known to work is the
Irix cc.
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 must
get Automake and Autoconf. We have used the automake-1.2 and
autoconf-2.12 distributions.
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 --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! See section H.1 Debugging a MySQL server.
Note: Before you start debugging mysqld, first get the test
programs mysys/thr_alarm and mysys/thr_lock to work. This
will ensure that your thread installation has even a remote chance to work!
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 optimization that may cause problems.
See section 20.2 What to do if MySQL keeps 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 you have some very specific problem, you can always try to debug
MySQL. To do this you must configure MySQL with the
option --with-debug. You can check whether or not
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:
CC=gcc CFLAGS="-O6" CXX=gcc CXXFLAGS="-O6 -felide-constructors -fno-exceptions -fno-rtti" ./configure --prefix=/usr/local/mysql --with-debug
This will avoid problems with the libstdc++ library and with C++
exceptions (many compilers have problems with C++ exceptions in threaded
code).
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.
If you can cause the mysqld server to crash quickly, you can try to
create a trace file of this:
Start the mysqld server with a trace log in `/tmp/mysql.trace'.
The log file will get very BIG.
mysqld --debug --log
or you can start it with
mysqld --debug=d,info,error,query,general,where:O,/tmp/mysql.trace
which only prints information with the most interesting tags.
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 mysql@lists.mysql.com and ask for help. Please use the
mysqlbug script for all bug reports or questions regarding the
MySQL version you are using!
On most system 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.
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 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 follows an example how to debug mysqld:
shell> gdb /usr/local/libexec/mysqld gdb> run ... back # Do this when mysqld crashes info locals up info locals up ... (until you get some information about local variables) quit
Include the above output in a mail generated with mysqlbug and
mail this to mysql@lists.mysql.com.
If mysqld hangs you can try to use some system tools like
strace or /usr/proc/bin/pstack to examine where
mysqld has hung.
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.
If mysqld dies or hangs, you should start mysqld with
--log. When mysqld dies again, you can check in the log
file for the query that killed mysqld. Note that before starting
mysqld with --log you should check all your tables with
myisamchk. See section 15 Maintaining a MySQL installation.
If you are using a log file, mysqld --log, you should check the
'hostname' log files, that you can find in the database directory, for
any queries that could cause a problem. Try the command EXPLAIN
on all SELECT statements that takes a long time to ensure that
mysqld are using indexes properly. See section 7.24 EXPLAIN syntax (Get information about a SELECT). You
should also test complicated queries that didn't complete within the
mysql command line tool.
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 (see section 15 Maintaining a MySQL installation), 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 mysql@lists.mysql.com. Links to mail archives are
available online at the MySQL
documentation page.
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:
mysqladmin shutdown)
myisamchk -s database/*.MYI. Repair any
wrong tables with myisamchk -r database/table.MYI.
mysqld with --log-update. See section 21.3 The update log.
mysqld server.
mysqld server without --log-update
mysql < update-log. The update log
is saved in the MySQL database directory with the name
your-hostname.#.
ISAM code! FTP the tables and the update log to
ftp://www.mysql.com/pub/mysql/secret and we will fix this as soon as
possible!
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 optimize the table with OPTIMIZE TABLE or
myisamchk. See section 15 Maintaining a MySQL installation. 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. See section 4.11 System-specific issues.
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.
See section 22.5.2 The DBI interface.
To be able to debug a MySQL client with the integrated debug package,
you should configure MySQL with --with-debug.
See section 4.7.3 Typical 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'.
If you have problems with your own client code, you should attempt to
connect to the server and run your query using a client that is known to
work. Do this by running mysql in debugging mode (assuming 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. See section 2.3 How to report bugs or problems.
If your client crashes at some 'legal' looking code, you should check that your `mysql.h' include file matches your mysql library file. A very common mistake is to use an old `mysql.h' file from an old MySQL installation with new MySQL library.
The MySQL server and most MySQL clients are compiled with the DBUG package originally made by Fred Fish. When one has configured MySQL for debugging, this package makes it possible to get a trace file of what the program is debugging.
One uses the debug package by invoking the program with the
--debug="..." or the -#... option.
The debug control string is a sequence of colon separated fields as follows:
<field_1>:<field_2>:...:<field_N>
Each field consists of a mandatory flag character followed by an optional "," and comma separated list of modifiers:
flag[,modifier,modifier,...,modifier]
The currently recognized flag characters are:
| d | Enable output from DBUG_<N> macros for for the current state. May be followed by a list of keywords which selects output only for the DBUG macros with that keyword. A empty list of keywords implies output for all macros. |
| D | Delay after each debugger output line. The argument is the number of tenths of seconds to delay, subject to machine capabilities. I.E. -#D,20 is delay two seconds.
|
| f tab 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 neaded 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.
Currently MySQL only supports table locking for
ISAM/MyISAM and HEAP tables and page level locking
for BDB tables. See section 12.2.8 How MySQL locks tables. With MyISAM
tables one can freely mix INSERT and SELECT without locks
(Versioning).
Some database users claim that MySQL cannot support near the number of concurrent users because it lacks row-level locking. This is a may be true for some specific applications, but is' not generally true. As always this depends totally on what the application does and what is the access/update pattern of the data.
Pros for row locking:
Cons:
GROUP
BY on a large part of the data or if one has to often scan the whole table.
Table locks are superior to page level / row level locks in the following cases:
UPDATE table_name SET column=value WHERE unique_key# DELETE FROM table_name WHERE unique_key=#
SELECT combined with INSERT (and very few UPDATE's
and DELETE's.
GROUP BY on the whole table without any writers.
Other options than row / page level locking:
Versioning (like we use in MySQL for concurrent inserts) where you can have one writer at the same time as many readers. This means that the database/table supports different views for the data depending 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 follows some tips about locking in MySQL:
On web application most applications do lots of selects, very few deletes, updates mainly on keys and inserts in some specific tables. The base MySQL setup is VERY tuned for this.
Concurrent users is not a problem if one doesn't mix updates and selects that needs to examine many rows in the same table.
If one mix a insert and deletes on the same table then INSERT DELAY
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 helps.
If you get speed problems with the table locks in MySQL, you
may be able to solve these to convert some of your tables to BDB tables.
See section 8.4 BDB or Berkeley_db tables.
The optimization section in the manual covers a lot of different aspects of how to tune ones application. See section 12.6 Other optimization tips.
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:
pthread_get_specific should use one argument.
sigwait should take two arguments.
A lot of functions (at least pthread_cond_wait,
pthread_cond_timedwait)
should return the error code on error. Now they return -1 and set errno.
Another problem is that user-level threads use the ALRM signal and this
aborts a lot of functions (read, write, open...).
MySQL should do a retry on interrupt on all of these but it is
not that 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:
-DHAVE_rts_threads.
thr_alarm.
thr_alarm. If it runs without any ``warning'', ``error'' or aborted
messages, you are on the right track. Here follows a successful run on
Solaris:
Main thread: 1 Tread 0 (5) started Thread: 5 Waiting process_alarm Tread 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
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:
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 OSF1 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''.
Go to the first, previous, next, last section, table of contents.