headers
Michael Izioumtchenko | 1 Dec 2008 23:21
Picon
Favicon

Re: more on deadlocks in 5.1bzr MySQL code

I doubt I'll have the time to look at it in detail but in the meantime
I've had another occurrence of it with a reasonably clear stack so I 
filed http://bugs.mysql.com/bug.php?id=41163

SET GLOBAL GENERAL_LOG=ON  does some serious stuff while holding LOCK_global_system_variables
which isn't a good idea since any global variable lookup can result in an attempt to acquire
LOCK_global_system_variables, and there's a lot of global variable lookups in the code.

Thanks,
Michael

MARK CALLAGHAN wrote:
> On Thu, Nov 27, 2008 at 10:58 AM, Michael Izioumtchenko
> <michael.izioumtchenko <at> oracle.com> wrote:
>   
>> Hi,
>>
>> here's another occurrence, see below. I have a few questions:
>>
>> - is the problems about the deadlocks in mysql-stress-test in 5.1.31 known?
>> If so is there a bug number for it?
>> - given a coredump, do you happen to know if there's a way to extract
>> anything useful from thread stack
>> that gdb shows like this:
>>
>> Thread 8 (process 3650):
>> #0  0x007c37a2 in _dl_sysinfo_int80 () from /lib/ld-linux.so.2
>> #1  0x00a3730e in __lll_mutex_lock_wait () from /lib/tls/libpthread.so.0
>> #2  0x00a33f3b in _L_mutex_lock_35 () from /lib/tls/libpthread.so.0
>> #3  0xaa7c0918 in ?? ()
(Continue reading)

Permalink | Reply | 0 comments |
headers
Stefan Hinz | 2 Dec 2008 16:17
Picon

MySQL University session on December 4: What's new in MySQL optimizer

This Thursday (December 4th), Sergey Petrunia will highlight what's new
in MySQL Optimizer, with a focus on MySQL 5.1. Since Sergey is one of
the main developers in charge of MySQL Optimizer, you can expect to get
some in-depth insights that only very few people could provide.

The session will start at 14:00 UTC / 8am CST (Central) / 9am EST
(Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow).

MySQL University is an educational program run virtually on the 'net.
Sessions are open to anyone. MySQL University focuses on MySQL internals
and on Sun technology that can be used in connection with MySQL, but
we're open to hear your suggestions for other topics.

We'll be using the Dimdim conferencing system for this session. Dimdim
has integrated voice streaming and chat. To attend, you need a browser
with a recent Flash plugin. You may register a Dimdim account but you
don't have to. The virtual meeting room will open 15 minutes before the
hour. Point your browser to this URL:
http://webmeeting.dimdim.com:80/portal/JoinForm.action?confKey=stefan <at> stefanhinz.org

Related URLs:
- MySQL University: http://forge.mysql.com/wiki/MySQL_University
- http://forge.mysql.com/wiki/What%27s_New_in_MySQL_Optimizer

Here's the MySQL University schedule for the rest of this year. Only two
more sessions to go in 2008. All sessions start at 14:00 UTC / 8am CST
(Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow):

December 11 - Random Query Generator - Philip Stoev
(Continue reading)

Permalink | Reply | 0 comments |
headers
Eric Jensen | 2 Dec 2008 23:18
Favicon

proposed design for UNION Order By optimization

I'm considering starting work on http://forge.mysql.com/worklog/task.php?id=1762 
   I posted the following understanding of the existing behavior and  
below proposed design to it, but am re-posting it here as I would  
appreciate feedback.  Particularly I'm not 100% sure if one can  
actually have multiple Sensitive_cursors open at the same time for  
several SELECT's within a single THD.  I'm guessing there is some  
issue I am overlooking here if they read from the same tables, etc.

existing behavior
-----------------

(see http://forge.mysql.com/wiki/MySQL_Internals_Selects section
"Non-Subquery UNION Execution" for more general description)

st_select_lex_unit::prepare creates a select_union object that has a
send_data method which actually writes result to temp table

it passes this to each new JOIN object (representing each component
select) as the select_result argument to their constructor,
and calls JOIN::prepare on each.

st_select_lex_unit::exec then does:

/* for each select that is part of the UNION, each of which has their
    own JOIN object as sl->join */
for (SELECT_LEX *sl= select_cursor; sl; sl= sl->next_select())
   JOIN::optimize
     make_join_readinfo
      /* normal select.  (not to be confused with  
st_select_lex::next_select) */
(Continue reading)

Permalink | Reply | 10 comments |
headers
Rick James | 3 Dec 2008 00:43
Picon
Favicon

RE: proposed design for UNION Order By optimization

Your example implies two distinct tables; does it also work for one?

I am forever suggesting to coworkers to turn

SELECT ... FROM a
    WHERE (expr1) OR (expr2)
    ORDER BY date DESC
    LIMIT 10

into

( SELECT ... FROM a
    WHERE (expr1)
    ORDER BY date DESC  LIMIT 10 )
UNION
( SELECT ... FROM a
    WHERE (expr2)
    ORDER BY date DESC  LIMIT 10 )
ORDER BY date DESC  LIMIT 10

Variants include
 * JOIN of multiple tables,
 * LIMIT M,N -- requiring LIMIT M+N inside and LIMIT M,N outside,
 * IN instead of OR
 * To deal with non-deterministic order of dup dates:  "ORDER BY id
DESC", or "ORDER BY date DESC, id DESC"

 
Rick James
MySQL Geeks - Consulting & Review
(Continue reading)

Permalink | Reply | 1 comment |
headers
Eric Jensen | 3 Dec 2008 00:50
Favicon

Re: proposed design for UNION Order By optimization

The example on the worklog was there before I came along, but this  
would theoretically work for any union that has the same order by in  
its selects as it does on the union itself.  There should be nothing  
query-specific about my proposal except that the order by column(s)  
must be consistent.  Now that I review that worklog example, it is not  
even what I proposed supporting, as it uses two separate tables and we  
can have no guarantee they have an identical indexed_column.

eric

On Dec 2, 2008, at 3:43 PM, Rick James wrote:

> Your example implies two distinct tables; does it also work for one?
>
> I am forever suggesting to coworkers to turn
>
> SELECT ... FROM a
>    WHERE (expr1) OR (expr2)
>    ORDER BY date DESC
>    LIMIT 10
>
> into
>
> ( SELECT ... FROM a
>    WHERE (expr1)
>    ORDER BY date DESC  LIMIT 10 )
> UNION
> ( SELECT ... FROM a
>    WHERE (expr2)
>    ORDER BY date DESC  LIMIT 10 )
(Continue reading)

Permalink | Reply | 0 comments |
headers
Konstantin Osipov | 3 Dec 2008 13:42
Picon

Re: proposed design for UNION Order By optimization

* Eric Jensen <ej <at> ir.iit.edu> [08/12/03 01:28]:
> 
> k-way merge design
> ------------------
> * when we determine all selects within the union have the same order
> by field as the outer one
> 
> * create temporary table with a btree index on that field and an extra
> int select_num field specifying which select the result came from
> 
> * replace st_select_lex_unit::exec logic so that instead of
> JOIN::exec's that read every result from each of those queries,
> figure out how to run them for just one result at a time
> 
> (if we converted it somehow to a normal cursored select, do_select
> would return NESTED_LOOP_CURSOR_LIMIT inside of
> st_select_lex_unit::exec saved_error=sl->join->error would be equal to
> NESTED_LOOP_CURSOR_LIMIT after processing one)
> 
> NESTED_LOOP_CURSOR_LIMIT is only handled by a Sensitive_cursor which
> is only created by mysql_open_cursor which is called by
> sp_cursor::open which is in the sp_rcontext for stored procedures, OR
> is called by Prepared_statement::execute, doesn't seem like either is
> available for a normal query.

No need to convert it to a cursored SELECT. Make do_select() a
method of each JOIN, and pull the call to it (the last one, the
one that does the final sweep) out of JOIN::exec.. Call JOIN::exec
for all joins. Then call do_select() repeatedly for different
joins involved, with different cursor limits. This won't work
(Continue reading)

Permalink | Reply | 7 comments |
headers
Eric Jensen | 3 Dec 2008 21:40
Favicon

Re: proposed design for UNION Order By optimization

Thanks!  Can you help me understand your suggestion a bit more?

When you suggest "Make do_select() a method of each JOIN" do you  
actually mean "Make sub_select() a method of each join"?

According to my understanding outlined under "existing behavior" in my  
initial email, st_select_lex_unit::prepare creates a top-level JOIN  
object for each select that is a component of the UNION and stores it  
in sl->join.  Each of those JOIN objects has a chain of component JOIN  
objects within them representing the various joins happening within  
that select.

st_select_lex_unit::exec goes through and calls JOIN::exec on each of  
those, which calls do_select on that top-level JOIN, which first sets  
up the final join_tab.next_select function pointer to be end_send for  
the final component JOIN  within that JOIN.  It then calls sub_select  
which loops while (rc == NESTED_LOOP_OK) for each row to return, which  
calls the top-level evaluate_join_record on that JOIN and then  
recurses down calling sub_select (via the next_select function  
pointer) for each of the component joins until it hits the last one  
which has next_select = end_send.

Is end_send the "last one, the one that does the final sweep" to which  
you refer?  It is the only function that pays attention to the cursor  
limit, by returning NESTED_LOOP_CURSOR_LIMIT which breaks the  
sub_select recursion and the top-level evaluate_join_record loop  
around it.

It seems like what you might be suggesting is to pull an init_select  
and end_select out of do_select so that i can call them and in-between
(Continue reading)

Permalink | Reply | 6 comments |
headers
Konstantin Osipov | 3 Dec 2008 22:32
Picon

Re: proposed design for UNION Order By optimization

* Eric Jensen <ej <at> ir.iit.edu> [08/12/03 23:49]:
> When you suggest "Make do_select() a method of each JOIN" do you actually 
> mean "Make sub_select() a method of each join"?

No, I actually mean make function do_select() a method of class
JOIN, JOIN::do_select(). Come to think of it, when you resume join
iteration you indeed need to be calling sub_select(), not
do_select(), so perhaps it's not a directly relevant suggestion.

sub_select() can not be made a method of JOIN in a straightforward
manner, since it operates on JOIN_TAB, and got to be cast to
Next_select_func function pointer type.

> According to my understanding outlined under "existing behavior" in my 
> initial email, st_select_lex_unit::prepare creates a top-level JOIN object 
> for each select that is a component of the UNION and stores it in sl->join. 
>  Each of those JOIN objects has a chain of component JOIN objects within 
> them representing the various joins happening within that select.

Right.

> st_select_lex_unit::exec goes through and calls JOIN::exec on each of 
> those, which calls do_select on that top-level JOIN, which first sets up 
> the final join_tab.next_select function pointer to be end_send for the 
> final component JOIN  within that JOIN.  It then calls sub_select which 
> loops while (rc == NESTED_LOOP_OK) for each row to return, which calls the 
> top-level evaluate_join_record on that JOIN and then recurses down calling 
> sub_select (via the next_select function pointer) for each of the component 
> joins until it hits the last one which has next_select = end_send.
(Continue reading)

Permalink | Reply | 5 comments |
headers
Shawn Green | 3 Dec 2008 22:59
Picon

MyISAM deadlocking (was: proposed design for UNION Order By optimization)

Konstantin Osipov wrote:
> ...
> Right. THD is a connection. Besides, you may have trivial
> deadlocks with myisam if you try to run multiple sensitive cursors
> for unions.
>  ...

I can see where this could happen if there were different tables in each 
part of the union. For example

(SELECT FROM A ...)
UNION
(SELECT FROM B ...)

run at the same time as

(SELECT FROM B ...)
UNION
(SELECT FROM A ...)

Could easily deadlock unless all of the locks are granted at the START 
of the full query instead of when each sub-SELECT is executed.

How is this handled today?

--

-- 
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN
(Continue reading)

Permalink | Reply | 2 comments |
headers
Rick James | 3 Dec 2008 23:34
Picon
Favicon

RE: MyISAM deadlocking (was: proposed design for UNION Order By optimization)

A guess:  MyISAM:  The two SELECTs in a UNION are run serially, so no
chance of deadlock?  InnoDB will rollback one of them?

This issue could be a nasty toward Eric's proposed fix?

But we are talking about READ locks -- perhaps you can't really get a
deadlock?

 
Rick James
MySQL Geeks - Consulting & Review

> -----Original Message-----
> From: Shawn Green [mailto:shawn.green <at> sun.com] 
> Sent: Wednesday, December 03, 2008 1:59 PM
> To: Konstantin Osipov; internals <at> lists.mysql.com
> Subject: MyISAM deadlocking (was: proposed design for UNION 
> Order By optimization)
> 
> Konstantin Osipov wrote:
> > ...
> > Right. THD is a connection. Besides, you may have trivial
> > deadlocks with myisam if you try to run multiple sensitive cursors
> > for unions.
> >  ...
> 
> I can see where this could happen if there were different 
> tables in each 
> part of the union. For example
>
(Continue reading)

Permalink | Reply | 0 comments |

Gmane