E.6 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''.

User Comments

something that they never get into in this
documentation is how to effectively use it
(period) in a SQL query with, oh, tables, and
columns. :)

select users from table where first_name REGEXP
\"^[RBrb]ob\";

will select all users that have names that start
with rob/bob (first character case insensitive)

Those of us who use DBI + Perl or php can
certainly make effective use of this.

erik, this is described here:
http://www.mysql.com/doc/en/Pattern_matching.html

If you are searching for literal
parentheses, you
have to enclose each parenthesis in brackets;
otherwise, mySQL thinks they're part of the
regular expression syntax. For instance:

WHERE phone REGEXP '(435)';

would return any phone numbers that have the
sequence 435 in any part of the string, such as
"1(801)555-4351". However:

WHERE phone REGEXP '[(]435[)]';

would return only phone numbers with (435), such
as "1(435)555-5555".

I've a question concerning negations of regular
expressions - e.g. I want the sentence "this is
nice" to match, while the sentence "this is not
nice" should not match. I only found possibilities
for the negation of single characters in this
tutorial, but what about whole words?!

Thanks for the reference to http://www.mysql.com/doc/en/Pattern_matching.html, fragking (there
really should be a "see also" link to it
somewhere on this doc page.) I couldn't get
REGEXP to work right for me till I found a VERY
IMPORTANT note on the Pattern Matching page
regarding case-sensitivity:

"Prior to MySQL Version 3.23.4, REGEXP is case
sensitive.... From MySQL 3.23.4 on, to force a
REGEXP comparison to be case sensitive, use the
BINARY keyword to make one of the strings a
binary string."

So I finally got my REGEXP (which searches for
any uppercase letters in a user id) to work
correctly:

mysql> SELECT * FROM tblTimePunch WHERE eid
REGEXP BINARY "[A-Z]";

Putting the column last is handy too:
select * from table where "Joe Bob Jim" regexp
name

Add your own comment.