Details
Major
Description
Ref MDEV-6308 which was my previous ticket
When increasing optimizer_use_condition_selectivity above 2, my server crashes completely - I'm not sure what query causes the crash, as its a live production environment with many many qps.
A list of my variables can be found here: https://gist.githubusercontent.com/jippi/81c2b80087308e11c767/raw/9f02e5fde3062925e98fd4996b6a9b30667160c2/gistfile1.txt
The list is with a working copy, it crashes if optimizer_use_condition_selectivity is increased above "2"
The stack trace is as below:
Server version: 10.0.15-MariaDB-1~wheezy-log key_buffer_size=25165824 read_buffer_size=131072 max_used_connections=522 max_threads=1511 thread_count=522 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 3343761 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x0x7f97b2260008 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0x7f97417c2e10 thread_stack 0x40000 /usr/sbin/mysqld(my_print_stacktrace+0x2b)[0x7fc89d5cab9b] /usr/sbin/mysqld(handle_fatal_signal+0x422)[0x7fc89d15ae92] /lib/x86_64-linux-gnu/libpthread.so.0(+0xf0a0)[0x7fc89c7ad0a0] /usr/sbin/mysqld(_Z45fix_semijoin_strategies_for_picked_join_orderP4JOIN+0x68)[0x7fc89d0f3788] /usr/sbin/mysqld(_Z20get_best_combinationP4JOIN+0x60)[0x7fc89d0302e0] /usr/sbin/mysqld(+0x374104)[0x7fc89cf50104] /usr/sbin/mysqld(_ZN4JOIN14optimize_innerEv+0x84a)[0x7fc89d04bdba] /usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xd8)[0x7fc89d04e418] /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x28d)[0x7fc89d051c0d] /usr/sbin/mysqld(+0x41b491)[0x7fc89cff7491] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x4b8a)[0x7fc89d001a1a] /usr/sbin/mysqld(+0x4275ba)[0x7fc89d0035ba] /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x14df)[0x7fc89d004b7f] /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x47b)[0x7fc89d0c673b] /usr/sbin/mysqld(handle_one_connection+0x47)[0x7fc89d0c6817] /lib/x86_64-linux-gnu/libpthread.so.0(+0x6b50)[0x7fc89c7a4b50] /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fc89aeb17bd] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x7f97ab53b020): is an invalid pointer Connection ID (thread ID): 591 Status: NOT_KILLED Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=off,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
I've uploaded the following files to the FTP for thats statistics:
MDEV-7413-stats-column-2015-01-08.csv
MDEV-7413-stats-index-2015-01-08.csv
MDEV-7413-stats-table-2015-01-08.csv
MDEV-7413-mysql-output.txt
with the stats tables and explain + query execution for each level of optimizer_use_condition_selectivity
the fix didn't make it to 10.0.16 ? that's sad 
Ok the fix for MDEV-7316 that was pushed into bzr tree, fixes this bug, too (checked on Elena's, simplified testcase). I've committed Elena's testcase into 10.0-bzr.
Fix for MDEV-7316 is pushed, so closing this one too. The fix will be in 10.0.17 release.
Thanks for your patience.
If you can afford crashing the server once more, it should be fairly easy to find the exact query that causes the problem. I'll describe the algorithm just in case you can do it (I realize that it's a production server, so you probably can't).
Note that number in the 'Connection ID' string, lets call it NNNN (above NNNN = 591).
Open the general log which was created at the path that you chose before; go to the end of it; search backwards for Query NNNN. Make sure it's the last query with this connection ID in the log.
This should be the query that caused the crash.
If you happen to do it, please paste the query and output of SHOW CREATE TABLE ..., SHOW TABLE STATUS LIKE ... and SHOW INDEX IN ... for all tables involved in the query (or upload the information to our ftp.askmonty.org/private if it's confidential).