MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Marc Alff Date:July 10 2009 5:07pm
Subject:Re: bzr commit into mysql-6.0-perfschema branch (marc.alff:3150)
View as plain text  
Hi Guilhem

Guilhem Bichot wrote:
> Hello Marc,
> 
> 
> A PSI_thread is created by create_thread(), via one of two routes:
> a) pfs_spawn_thread(), which happens when creating an OS thread, so in
> this case a PSI_thread seems to represent an OS thread
> b) PSI_server->new_thread(), which happens in libevent_add_connection(),
> so in this case a PSI_thread seems to represent a THD (as that moment we
> have a THD but no OS thread bound to it).
> 
> I try hard, but find some inconsistency between a) and b), and it's
> probably what makes me not understand explanations.
> Big question for you: what does a PSI_thread represent? What is it?
> I guess that the Performance Schema PROCESSLIST (to be named THREADS)
> table will be a list of PSI_thread's, and WL#2360 says
> "The original idea was that this would correspond to what SHOW
> PROCESSLIST shows. However, we monitor more threads, and must supply
> different data. Therefore the implementor will add a new
> PERFORMANCE_SCHEMA.PROCESSLIST table which shows all threads."
> So it seems to be about "threads". OS threads? or THD-like jobs?
> If about OS threads, it means the table really lists OS threads, one per
> row (THREAD_ID is the primary key somehow), and gives the SELECTer, via
> an informative column (ID), a hint of what job (more or less THD) this
> OS thread is handling now.
> But if it's about OS threads, (b) above does not make a lot of sense:
> PSI_server->new_thread() should not be called in
> libevent_add_connection(): a PSI_thread has already been created for the
> OS thread (of the threads pool), it is already a row in PROCESSLIST, and
> we should just adjust ID when the OS thread picks up a THD for processing.
> On the other hand, if PSI_thread is about a THD-like job (or similar
> jobs done by the system threads which don't have a THD), then it should
> not be created in pfs_spawn_thread(), but rather when we create the job
> object (THD::THD() or equivalent for system threads which don't have a
> THD).
> 
> Could you please shed some light on my confusion, after which I could
> hopefully make more useful comments?
> 
> Defining PSI_thread clearly is worth doing it now, independently of the
> fact that pool-of-threads is not in the tree which Performance Schema is
> now based on.
> 

Ok, let me try again with a few examples.

Please note that the explanation I am offering here:
- is not implemented
- is not documented in WL
- has not been fully discussed and approved yet,
since work on the THREADS table was stalled.
This is only where I would like personally the performance schema to go,
so having a picture of the long term goal helps understanding, but you
may *not* use that information to claim that the current code is
incomplete, because this is not supposed to be in the code yet.
I am sure that by the time THREADS is fully discussed, some details will
change.

performance_schema.THREADS will show "jobs" running in the server.
A job is either real or virtual.
A real job is an OS thread (pthread_t)
A virtual job is a THD like object, picked up by real threads in a
thread pool.
A job is either a bound to a connection or connectionless
A job bound to a connection is a THD
A connectionless job is typically a background job

Example 1
=========

Assume a server with 1 thread per connection, and 10 user connections.

There are:
- 10 user connections
- 10 THD
- 1 main thread
- 11 OS threads
- 11 PSI_thread
- 10 THD --> 10 PSI_thread --> 10 pthread_t
- main() --> 1 PSI_thread --> 1 pthread_t

THD::ID are numbered 1..10
PSI_thread::thread_id are numbered 100..110
pthead_t are numbered in the OS maybe 1000..1010

For the 10 connections,
a line in performance_schema.THREAD will look like:
- ID=1, THREAD_ID=101, OS_THREAD_ID=1001
meaning:
- user connection ID=1
- is instrumented with PSI_thread::thread_id=101
- and executed in the thread library by pthread_t 1001

EVENTS_WAITS for these 10 threads will show what a user connection is doing.

For the main thread,
a line in performance_schema.THREAD will look like:
- ID=0 or NULL, THREAD_ID=100, OS_THREAD_ID=1000
meaning:
- there is no user connection for main()
- main() is instrumented with PSI_thread::thread_id=100
- and executed in the thread library by pthread_t 1000

EVENTS_WAITS for this thread will show that it is most of the time stuck
waiting for a new connection on a socket (accept()).


Example 2
=========

Assume a server with a thread pool of size 2, and 10 user connections.

There are:
- 10 user connections
- 10 THD
- 1 main thread
- 2 threads in libevent
- 3 OS threads
- 13 PSI_thread
- 10 THD --> 10 PSI_thread --> no dedicated pthread_t
- main() --> 1 PSI_thread --> 1 pthread_t
- 2 libevent() --> 2 PSI_thread --> 2 pthread_t


THD::ID are numbered 1..10
PSI_thread::thread_id are numbered 101..113
pthead_t are numbered in the OS maybe 1001..1003


For the main thread,
a line in performance_schema.THREAD will look like:
- ID=0 or NULL, THREAD_ID=111, OS_THREAD_ID=1001
meaning:
- there is no user connection for main()
- main() is instrumented with PSI_thread::thread_id=111
- and executed in the thread library by pthread_t 1001

EVENTS_WAITS for this thread will show that it is most of the time stuck
waiting for a new connection on a socket (accept()).

For the 10 connections,
a line in performance_schema.THREAD will look like:
(a) - ID=1, THREAD_ID=101, OS_THREAD_ID=1002
(b) - ID=2, THREAD_ID=102, OS_THREAD_ID=NULL
(c) - ID=3, THREAD_ID=103, OS_THREAD_ID=1003
meaning:
(a)
- connection THD::ID=1
- is instrumented with PSI_thread::thread_id=101
- and executed in the thread library by pthread_t 1002
(b)
- connection THD::ID=2
- is instrumented with PSI_thread::thread_id=102
- and is not running
(c)
- connection THD::ID=3
- is instrumented with PSI_thread::thread_id=103
- and executed in the thread library by pthread_t 1003

THD::ID=2 is not running because the 2 threads in the thread pool are
busy (1002, 1003), so this connection is basically stuck in the libevent
queue, waiting for libevent to pick it up.

Note that the performance schema will then display that kind of data, so
the DBA can see how the pool is performing and if it is sized properly.

For the 2 libevent threads,
a line in performance_schema.THREAD will look like:
(a) - ID=O or NULL, THREAD_ID=112, OS_THREAD_ID=1002
(b) - ID=O or NULL, THREAD_ID=113, OS_THREAD_ID=1003
meaning:
(a)
- there is no user connection for libevent()
- this libevent() thread is instrumented with PSI_thread::thread_id=112
- and executed in the thread library by pthread_t 1002
(b)
- there is no user connection for libevent()
- this libevent() thread is instrumented with PSI_thread::thread_id=113
- and executed in the thread library by pthread_t 1003

Now, let's look at how the data changes over time.

Assuming that libevent pthread_t 1002:
(a) - picks up THD::ID 1 and executes it
(b) - returns THD::ID 1 to the pool
(c) - picks up THD::ID 2 and executes it
(d) - returns THD::ID 2 to the pool
(e) - picks up THD::ID 3 and executes it
(f) - returns THD::ID 3 to the pool

Monitoring the THREAD table (where OS_THREAD_ID=1002)
will show these rows:
(a) - ID=1, THREAD_ID=101, OS_THREAD_ID=1002
(b) - ID=0 or NULL, THREAD_ID=112, OS_THREAD_ID=1002
(c) - ID=2, THREAD_ID=102, OS_THREAD_ID=1002
(d) - ID=0 or NULL, THREAD_ID=112, OS_THREAD_ID=1002
(e) - ID=3, THREAD_ID=103, OS_THREAD_ID=1002
(f) - ID=0 or NULL, THREAD_ID=112, OS_THREAD_ID=1002

Assuming that connection THD::ID=5 :
(a) - is picked up by libevent thread 112 / 1002
(b) - is returned to the thread pool
(c) - is picked up by libevent thread 113 / 1003
(d) - is returned to the thread pool
(e) - is picked up by libevent thread 112 / 1002
(f) - is returned to the thread pool

Monitoring the THREAD table (where ID=5)
will show these rows:
(a) - ID=5, THREAD_ID=105, OS_THREAD_ID=1002
(b) - ID=5, THREAD_ID=105, OS_THREAD_ID=NULL
(a) - ID=5, THREAD_ID=105, OS_THREAD_ID=1003
(b) - ID=5, THREAD_ID=105, OS_THREAD_ID=NULL
(a) - ID=5, THREAD_ID=105, OS_THREAD_ID=1002
(b) - ID=5, THREAD_ID=105, OS_THREAD_ID=NULL

So, to summarize, in case of thread pools:

The THD::ID <==> PSI_thread::thread_id association never changes,
a given PSI_thread is bound to a given THD for the life of THD.
Note the '<==>' notation.

The PSI_thread::thread_id <--> pthread_t association always changes,
each time a job is picked from or returned to the pool.
Note the '<-->' notation.

THD::ID <=(permanent)=> PSI_thread::thread_id <-(temporary)-> pthread_t

That is why THD::store_global() does not call mysql_thread_set_psi_id().

With this design, the performance schema can be used:
(1) - to monitor what a THD is doing, or see that THD is stuck waiting
to be picked up
(2) - to monitor what a libevent thread is doing between jobs, and
possibly see if it is stuck on the internal mutexes used to protect the
job queue itself
(3) - keep events related to a *logical job* (THD) together, by always
recording events with the same THREAD_ID, even when different physical
threads technically generate this events.

(1) and (2) are critical to tune the performances of a thread pool

(3) is critical for the thread history, which would otherwise just be
random useless noise.


-- Marc

Thread
bzr commit into mysql-6.0-perfschema branch (marc.alff:3150) Marc Alff21 May
  • Re: bzr commit into mysql-6.0-perfschema branch (marc.alff:3150)Guilhem Bichot26 May
    • Re: bzr commit into mysql-6.0-perfschema branch (marc.alff:3150)Marc Alff26 May
      • Re: bzr commit into mysql-6.0-perfschema branch (marc.alff:3150)Guilhem Bichot28 May
        • Re: bzr commit into mysql-6.0-perfschema branch (marc.alff:3150)Marc Alff1 Jul
          • Re: bzr commit into mysql-6.0-perfschema branch (marc.alff:3150)Guilhem Bichot10 Jul
            • Re: bzr commit into mysql-6.0-perfschema branch (marc.alff:3150)Marc Alff10 Jul
              • Re: bzr commit into mysql-6.0-perfschema branch (marc.alff:3150)Guilhem Bichot27 Jul
                • Re: bzr commit into mysql-6.0-perfschema branch (marc.alff:3150)Marc Alff28 Jul
                  • Re: bzr commit into mysql-6.0-perfschema branch (marc.alff:3150)Guilhem Bichot28 Jul