WL#441: Use a thread pool to serve user requests

-- Background 
 
The current MySQL design works as follows with respect to how 
threads are used: 
 
- A main thread loops over the socket for incoming connections. 
- When a new connection is made, it's accept():ed to get the  I/O 
socket. 
- A new THD is allocated and initialized with this socket. 
- A new thread is created to serve this socket. (Actually, with a 
thread cache, it might reuse an existing thtread.) 
- This thread then repeatedly serves commands from the client. 
- When the connection is closed, the thread is terminated (or 
turned over to the thread cache for reuse). 
 
This is simple and in effect a way to avoid multiplexing over all 
connections (using select/poll) to serve requests (which would also 
require non-blocking I/O in order to handle high throughput). 
 
However, there are some serious drawbacks: 
 
- There will be as many threads as there are active connections. 
- Creating threads (and killing them) is, in the thread context, an 
expensive operation. 
 
The first cons is the biggest problem since each thread requires a 
certain amount of system (and kernel) resources. Among others, each 
thread has its own stack. To accomodate a large number of 
simultanous connections, the stack size has to be kept small, up to 
a point where either it's too small or the server consumes 
enourmous amounts of memory. Or, at some point we will run out of 
some other (kernel) resource, or the scheduling overhead will start 
to cause problems. (And blaming the kernel is not really fair: An 
improved kernel or scheduler can only push the limit slightly 
further, but not solve the actual problem.) 
 
In short: The above design does not scale. 
 
 
-- How it should be 
 
In general, when designing a threaded application, you do not want 
to have an unlimited number of threads, you want to have the number 
of threads under strict control. 
 
A common design is to have a pool of threads serving requests. This 
pool can be tuned to a suitable number proportional to the number 
of CPUs you have available. (Exactly how many per CPU depends on 
the applications; how much internal serialization and wait states 
you have among the threads, but usually it's a quite small number.) 
In servers where each connection has a very short lifetime, say in 
a web server, this is simple. The threads in the pool simply loop 
over a mutex wrapped accept(), each accepting a new connection, 
handle it, and then return to the mutex. 
 
 
-- A proposal 
 
In the MySQL case, however, we have the problem that connections 
might last for a very long (or indefinite) time, so the above 
solution obviously doesn't work. Instead I propose the following 
solution: 
 
- One (main) thread loops over a select/poll where one (or more) of 
the sockets is the one for new connections. 
- If a new connection was made, accept it and add the new socket to 
the set of sockets. 
- Input on the other sockets is read and the incomming messages 
each assembled. (Since we should use non-blocking I/O for 
performance, we can have partial requests in the making here.) 
- Each complete request is passed on to a pthread condition 
variable protected queue. 
 
- A (fixed) pool of service threads waits for new requests on the 
queue. 
- When a new requests is received in the queue, one of the threads 
will grab it and handle it, and then return to grab new requests 
from the queue. 
 
Note that what identifies a particular connection is the THD 
struct, so this what we pass through the queue to the worker 
threads in the pool. 
 
There are some variations on this theme: 
 
- The thread pool is created at boot and never changed; the number 
is of course configurable. But it's possible to make this tunable 
in run-time if we want. (Being able to grow the thread pool without 
having to shut down could be desirable.) 
 
- It's possible to have more than one "main" thread doing the I/O, 
if the I/O is believed to be a bottleneck in large, high-load 
installations. However, this also causes some problems, with load 
balancing among other things. A first step should be limited to 
just having one main thread. 
 
 
Issues: 
- There might be a problem with limitations of the FD set size in 
select and/or poll on some systems. 
- This is all under the assumption that the I/O can be done 
non-blocking. 
- It's always possible that I overlooked something in the current 
mysqld structure that makes all this difficult or impossible... 
 
 

We should create a general interface that can be used to have many
different thread handling designs. All code in MySQL and mysys that
are hard coupled with threads (like thr_alarm and thr_lock) should be
made losely coupled so we can change physical threads between calls.

Initially we have should have implementations for Solaris (using
port_create, from the event completion framework) and windows (using
IOCP)..

Later we can enhance it to other areas (like epoll under Linux?)

Create general 'scheduler' object that is the main interface to the
different different thread handling designs. It will be a global object,
setup once at at start, that mainly is used as dispatch functions:

class scheduler_functions
{
public:
  uint max_threads;
  /*
    To initialize the scheduler object. This is called once.
    If the return value is 1, we need to abort mysqld
  */
  bool (*init)(void);
  /*
    Called to setup the thread.  Normally this calles the global function
    init_new_connection_handler_thread().
  */
  bool (*init_new_connection_thread)(void);
  /* Tell scheduler that we have a new connection */
  void (*add_connection)(THD *thd);
  /* Tell scheduler that we have want to kill a connection */
  void (*post_kill_notification)(THD *thd);
  /* Called by handle_one_connection() when we want to end a connection */
  bool (*end_thread)(THD *thd, bool cache_thread);
  /* Free all resources */
  void (*end)(void);
};


THD is extended with a contained class:

class thd_scheduler, that different scheduling implementations can use
to store states associated with one connection or statement.  (This is
to free the scheduler functions to have to maintain memory for holding
states)


- Added 'my_thread_id' as a typedef for our internal thread identifier to be
  used when identifying an internal working thread. In other words, a physical
  thread may over time do the work for many 'internal threads'.

- Changed some mysys functions to make them independent of the physical thread.
  (This allows us to switch physical thread more eaasily)

  - Make thr_alarm_kill() to depend on thread_id instead of thread
    (Safer as we can't accidently abort the wrong thread)

  - Renamed 'my_thread_id()' function to my_thead_dbug_id()
    (to not conflict with the new 'my_thread_id' typedef)

  - Make thr_abort_locks_for_thread() depend on thread_id instead of thread.
    (Safer as we can't accidently abort the wrong thread)

- MySQL SQL functions GET_LOCK and RELEASE_LOCK now works on
  my_thread_id instead of pthread_t

- my_thread_dbug_id() (former my_thread_id()), now always returns our own
  thread id (makes it easier to debug thing as things are now the more the
  same over different platforms).


Other cleanups done at the same time:

- Moved --one-thread to be handled trough the scheduler infrastructure.
  (Less code, faster code and fewer #ifdefs than before)

- Move all connection setup code from sql_parse.cc to sql_connect.cc
  (Should have done it a long time ago)

- Ensure that 'pseudo_thread_id' is setup the same way in every place.

- Removed thd->block_signals, as these where never used.


Implementation details:

mysqld.cc:
- Add global variable 'thread_scheduler' that contains dispatch functions
  for thread scheduling.  By using this for creating threads,
  initialization new connections etc we can easily have many different way
  to schedule query execution.

- Split create_new_thread() into reusable sub functions:
   - create_thread_to_handle_connection() and
     one_thread_per_connection_end() to handle the old
     one-thread-per-connection scheduling.

- Split 'end_thread()' into 'unlink_thd()' and 'cache_thread()'

- Added option --thread-handling=# that can take options:
  one-thread-per-connection, no-threads or pool-of-threads

- Added option --pool-of-threads, to specify how many threads should be
  created if we are using --thread-handling=pool-of-threads.


sql_class.cc:
- In THD::store_globals(), we set my_thread_var->id to be thd->thread_id.
  'thd->thread_id' is the 'connection specific identifier' that is unique
   over the whole connection.

  We are in effect renaming the physical thread to be associcated with the
  connection until the next call to store_globals().
  This allows us, in a pool-of-threads environment, to use different
  physical threads to execute different statements for the same connection.

  During one statement one thread is associated with one THD and one
  mysys_var object.

- Use new interfaces to thr_alarm_kill().

- Send thread-kill signal to scheduler (if not slave threads, which are not
  handled through the scheduler)

sql_class.h:

- Remove thd->dbug_thread_id
- thd->real_id is now only usable for debugging; One should not use this
  for anything else as this may change value during the lifetime of the
  connection.
- 'thd->thread_id' is now a connection identifier.
- Added 'thd->scheduler' as an element to THD, to allow different scheduler
  implementation to use THD to store it's states.


sql_connect.cc:
- Split handle_one_connection() into multiple functions that can be called
  separately by the scheduler functions.


sql_show.cc:
- Don't send pthread_kill() to threads to detect if they exists.
  (Not that useful and causes problems with future thread_handling code)


scheduler.h:

- Declarations needed for scheduler.cc and also for the thd_scheduler, which
  is different for each schduler implementation.

scheduler.cc:

- Implementations of the following schedulers:

- one-thread-per-connection
- no-threads
- pool-of-threads ; Implemented with event completion framework (Solaris)
- pool-of-threads ; Implemented with IOCP (windows)