[MDEV-10387] DB Hang - an internal temporary table and aria_pagecahce_buffer_size Created: 2016-07-18  Updated: 2023-07-28  Resolved: 2023-07-28

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Aria
Affects Version/s: 10.0.24, 5.5, 10.0, 10.1
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Yi Yun Jung (Inactive) Assignee: Michael Widenius
Resolution: Won't Fix Votes: 1
Labels: not-10.2, not-10.3, not-10.4
Environment:

CentOS 6.6 x86_64

Attachments: File issue_query.sql     File my.cnf     File test_schema.sql    

 Description   

Our development team had run a query that uses the complex view.
When the query was executed by a single thread, it was no problem. (it took about 90 seconds.)
When the query was executed by multi threads, it had cause DB hang.
It did never-ending and the other threads could not even run a simple query like "desc table_name".
In the processlist, thread status was "Copyint to tmp table"

We performed test by changing parameter value in my.ncf.
We found that the value of aria_pagecahce_buffer_size is problem.
In my.cnf , the value of "aria_pagecahce_buffer_size" was 1M.
We changed the value from 1M to 128M and the query was executed without DB hang.

I think the DB hang occurs when the following conditions combined.

  • A small size of "aria_pagecahce_buffer_size" .
  • At the same time to run a query that uses a large internal temporary table from multiple threads.

What is this cause to have problem?

I attach a script to create a test environment.
Thanks.

 Comments   
Comment by Elena Stepanova [ 2016-07-18 ]

Yunjung,

Thanks for the report and the test case.

As you discovered yourself, the cause of the problem is the rather bad configuration, aria_pagecache_buffer_size=1M is way too low. So, the proper workaround is to set it to a more sensible value.

However, the server shouldn't hang, so I think it's a bug.

Reproducible as described – to make the server hang, it's enough to run the attached query from two client connections in parallel.
In the cnf file, only aria_pagecache_buffer_size is important, other options can be default.

10.2 is also affected.

Below is the thread stack from the running (hanging) server:

10.1 commit 1168c1a3b984fb6b9cdf376392a7fcd301800162

 
Thread 3 (Thread 0x7fc39923bb00 (LWP 26157)):
 
#0  0x00007fc397bbed84 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib/x86_64-linux-gnu/libpthread.so.0
 
#1  0x00007fc39a03f58b in safe_cond_wait (cond=0x7fc37240e188, mp=0x7fc39b2fb0e8 <maria_pagecache_var+200>, file=0x7fc39a204800 "/home/elenst/git/10.1/include/mysql/psi/mysql_thread.h", line=1165) at /home/elenst/git/10.1/mysys/thr_mutex.c:493
 
#2  0x00007fc399bd44e8 in inline_mysql_cond_wait (that=0x7fc37240e188, mutex=0x7fc39b2fb0e8 <maria_pagecache_var+200>, src_file=0x7fc39a204918 "/home/elenst/git/10.1/storage/maria/ma_pagecache.c", src_line=2087) at /home/elenst/git/10.1/include/mysql/psi/mysql_thread.h:1165
 
#3  0x00007fc399bd79c1 in find_block (pagecache=0x7fc39b2fb020 <maria_pagecache_var>, file=0x7fc372769498, pageno=35, init_hits_left=0, wrmode=1 '\001', block_is_copied=0 '\000', reg_req=1 '\001', page_st=0x7fc399237ff0) at /home/elenst/git/10.1/storage/maria/ma_pagecache.c:2086
 
#4  0x00007fc399bdb896 in pagecache_read (pagecache=0x7fc39b2fb020 <maria_pagecache_var>, file=0x7fc372769498, pageno=35, level=0, buff=0x0, type=PAGECACHE_PLAIN_PAGE, lock=PAGECACHE_LOCK_WRITE, page_link=0x7fc3992380e0) at /home/elenst/git/10.1/storage/maria/ma_pagecache.c:3393
 
#5  0x00007fc399c17cbe in delete_head_or_tail (info=0x7fc372769070, page=35, record_number=0, head=0 '\000', from_update=1 '\001') at /home/elenst/git/10.1/storage/maria/ma_blockrec.c:4179
 
#6  0x00007fc399c181ac in delete_tails (info=0x7fc372769070, tails=0x7fc37276f990) at /home/elenst/git/10.1/storage/maria/ma_blockrec.c:4296
 
#7  0x00007fc399c16dc8 in _ma_update_block_record2 (info=0x7fc372769070, record_pos=302, oldrec=0x7fc3727628b8 "", record=0x7fc372762088 "", undo_lsn=1) at /home/elenst/git/10.1/storage/maria/ma_blockrec.c:3813
 
#8  0x00007fc399c17719 in _ma_update_block_record (info=0x7fc372769070, record_pos=302, orig_rec=0x7fc3727628b8 "", new_rec=0x7fc372762088 "") at /home/elenst/git/10.1/storage/maria/ma_blockrec.c:4020
 
#9  0x00007fc399c26fc5 in maria_update (info=0x7fc372769070, oldrec=0x7fc3727628b8 "", newrec=0x7fc372762088 "") at /home/elenst/git/10.1/storage/maria/ma_update.c:159
 
#10 0x00007fc399bb4d1e in ha_maria::update_row (this=0x7fc372760370, old_data=0x7fc3727628b8 "", new_data=0x7fc372762088 "") at /home/elenst/git/10.1/storage/maria/ha_maria.cc:2309
 
#11 0x00007fc399826f68 in handler::ha_update_tmp_row (this=0x7fc372760370, old_data=0x7fc3727628b8 "", new_data=0x7fc372762088 "") at /home/elenst/git/10.1/sql/sql_class.h:5447
 
#12 0x00007fc399813637 in end_update (join=0x7fc3725ceed8, join_tab=0x7fc372668ed8, end_of_records=false) at /home/elenst/git/10.1/sql/sql_select.cc:19757
 
#13 0x00007fc39991dd11 in JOIN_CACHE::generate_full_extensions (this=0x7fc37268a118, rec_ptr=0x7fc3726e007c "\002") at /home/elenst/git/10.1/sql/sql_join_cache.cc:2405
 
#14 0x00007fc39991da54 in JOIN_CACHE::join_matching_records (this=0x7fc37268a118, skip_last=false) at /home/elenst/git/10.1/sql/sql_join_cache.cc:2297
 
#15 0x00007fc39991d466 in JOIN_CACHE::join_records (this=0x7fc37268a118, skip_last=false) at /home/elenst/git/10.1/sql/sql_join_cache.cc:2092
 
#16 0x00007fc39991d600 in JOIN_CACHE::join_records (this=0x7fc372688050, skip_last=false) at /home/elenst/git/10.1/sql/sql_join_cache.cc:2138
 
#17 0x00007fc39980f99e in sub_select_cache (join=0x7fc3725ceed8, join_tab=0x7fc372668848, end_of_records=true) at /home/elenst/git/10.1/sql/sql_select.cc:18091
 
#18 0x00007fc39980fb88 in sub_select (join=0x7fc3725ceed8, join_tab=0x7fc372668500, end_of_records=true) at /home/elenst/git/10.1/sql/sql_select.cc:18261
 
#19 0x00007fc39980f66f in do_select (join=0x7fc3725ceed8, fields=0x0, table=0x7fc37273c088, procedure=0x0) at /home/elenst/git/10.1/sql/sql_select.cc:17967
 
#20 0x00007fc3997e86a4 in JOIN::exec_inner (this=0x7fc3725ceed8) at /home/elenst/git/10.1/sql/sql_select.cc:2772
 
#21 0x00007fc3997e771b in JOIN::exec (this=0x7fc3725ceed8) at /home/elenst/git/10.1/sql/sql_select.cc:2518
 
#22 0x00007fc3997eacce in mysql_select (thd=0x7fc37b75b070, rref_pointer_array=0x7fc3724a7ac8, tables=0x7fc37250ba70, wild_num=0, fields=..., conds=0x7fc3726661a8, og_num=2, order=0x0, group=0x7fc37250d520, having=0x0, proc_param=0x0, select_options=2416184064, result=0x7fc3725cede0, unit=0x7fc3724a7150, select_lex=0x7fc3724a7850) at /home/elenst/git/10.1/sql/sql_select.cc:3451
 
#23 0x00007fc39977c5e7 in mysql_derived_fill (thd=0x7fc37b75b070, lex=0x7fc37b75e9c0, derived=0x7fc372497620) at /home/elenst/git/10.1/sql/sql_derived.cc:938
 
#24 0x00007fc39977af02 in mysql_handle_single_derived (lex=0x7fc37b75e9c0, derived=0x7fc372497620, phases=96) at /home/elenst/git/10.1/sql/sql_derived.cc:195
 
#25 0x00007fc39980044d in st_join_table::preread_init (this=0x7fc37268c268) at /home/elenst/git/10.1/sql/sql_select.cc:11695
 
#26 0x00007fc3998173e3 in create_sort_index (thd=0x7fc37b75b070, join=0x7fc37258f328, order=0x7fc372499428, filesort_limit=18446744073709551615, select_limit=18446744073709551615, is_order_by=true) at /home/elenst/git/10.1/sql/sql_select.cc:21437
 
#27 0x00007fc3997ea25f in JOIN::exec_inner (this=0x7fc37258f328) at /home/elenst/git/10.1/sql/sql_select.cc:3198
 
#28 0x00007fc3997e771b in JOIN::exec (this=0x7fc37258f328) at /home/elenst/git/10.1/sql/sql_select.cc:2518
 
#29 0x00007fc3997eacce in mysql_select (thd=0x7fc37b75b070, rref_pointer_array=0x7fc37b75f400, tables=0x7fc372497620, wild_num=0, fields=..., conds=0x7fc3724991f8, og_num=2, order=0x7fc372499428, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fc37258f308, unit=0x7fc37b75ea88, select_lex=0x7fc37b75f188) at /home/elenst/git/10.1/sql/sql_select.cc:3451
 
#30 0x00007fc3997e0773 in handle_select (thd=0x7fc37b75b070, lex=0x7fc37b75e9c0, result=0x7fc37258f308, setup_tables_done_option=0) at /home/elenst/git/10.1/sql/sql_select.cc:384
 
#31 0x00007fc3997b0974 in execute_sqlcom_select (thd=0x7fc37b75b070, all_tables=0x7fc372497620) at /home/elenst/git/10.1/sql/sql_parse.cc:5894
 
#32 0x00007fc3997a685e in mysql_execute_command (thd=0x7fc37b75b070) at /home/elenst/git/10.1/sql/sql_parse.cc:2960
 
#33 0x00007fc3997b408c in mysql_parse (thd=0x7fc37b75b070, rawbuf=0x7fc372422088 "SELECT dtcv.data_table_id AS data_table_id,\n       dtcv.data_type AS test_data_type,\n       dtcv.dtc1 AS attr1,\n       dtcv.dtc2 AS attr2,\n       dtcv.dtc3 AS attr3,\n       dtcv.dtc4 AS attr4,\n       "..., length=6207, parser_state=0x7fc39923a5c0) at /home/elenst/git/10.1/sql/sql_parse.cc:7314
 
#34 0x00007fc3997a2ac5 in dispatch_command (command=COM_QUERY, thd=0x7fc37b75b070, packet=0x7fc37b6c0071 "SELECT dtcv.data_table_id AS data_table_id,\n       dtcv.data_type AS test_data_type,\n       dtcv.dtc1 AS attr1,\n       dtcv.dtc2 AS attr2,\n       dtcv.dtc3 AS attr3,\n       dtcv.dtc4 AS attr4,\n       "..., packet_length=6207) at /home/elenst/git/10.1/sql/sql_parse.cc:1486
 
#35 0x00007fc3997a17f7 in do_command (thd=0x7fc37b75b070) at /home/elenst/git/10.1/sql/sql_parse.cc:1107
 
#36 0x00007fc3998d8127 in do_handle_one_connection (thd_arg=0x7fc37b75b070) at /home/elenst/git/10.1/sql/sql_connect.cc:1350
 
#37 0x00007fc3998d7e8b in handle_one_connection (arg=0x7fc37b75b070) at /home/elenst/git/10.1/sql/sql_connect.cc:1262
 
#38 0x00007fc397bbae9a in start_thread () from /lib/x86_64-linux-gnu/libpthread.so.0
 
#39 0x00007fc3972e7cbd in clone () from /lib/x86_64-linux-gnu/libc.so.6
 
#40 0x0000000000000000 in ?? ()
 
 
 
Thread 2 (Thread 0x7fc3991f1b00 (LWP 26160)):
 
#0  0x00007fc397bbed84 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib/x86_64-linux-gnu/libpthread.so.0
 
#1  0x00007fc39a03f58b in safe_cond_wait (cond=0x7fc37280e188, mp=0x7fc39b2fb0e8 <maria_pagecache_var+200>, file=0x7fc39a204800 "/home/elenst/git/10.1/include/mysql/psi/mysql_thread.h", line=1165) at /home/elenst/git/10.1/mysys/thr_mutex.c:493
 
#2  0x00007fc399bd44e8 in inline_mysql_cond_wait (that=0x7fc37280e188, mutex=0x7fc39b2fb0e8 <maria_pagecache_var+200>, src_file=0x7fc39a204918 "/home/elenst/git/10.1/storage/maria/ma_pagecache.c", src_line=2087) at /home/elenst/git/10.1/include/mysql/psi/mysql_thread.h:1165
 
#3  0x00007fc399bd79c1 in find_block (pagecache=0x7fc39b2fb020 <maria_pagecache_var>, file=0x7fc36f8e7498, pageno=89, init_hits_left=0, wrmode=1 '\001', block_is_copied=0 '\000', reg_req=1 '\001', page_st=0x7fc3991ed8c0) at /home/elenst/git/10.1/storage/maria/ma_pagecache.c:2086
 
#4  0x00007fc399bdb896 in pagecache_read (pagecache=0x7fc39b2fb020 <maria_pagecache_var>, file=0x7fc36f8e7498, pageno=89, level=0, buff=0x0, type=PAGECACHE_PLAIN_PAGE, lock=PAGECACHE_LOCK_WRITE, page_link=0x7fc3991ed9a0) at /home/elenst/git/10.1/storage/maria/ma_pagecache.c:3393
 
#5  0x00007fc399c11ab3 in get_head_or_tail_page (info=0x7fc36f8e7070, block=0x7fc3728c7eb8, buff=0x7fc36f8eaa40 "", length=816, page_type=2, lock=PAGECACHE_LOCK_WRITE, res=0x7fc3991edac0) at /home/elenst/git/10.1/storage/maria/ma_blockrec.c:1781
 
#6  0x00007fc399c121ad in write_tail (info=0x7fc36f8e7070, block=0x7fc3728c7eb8, row_part=0x7fc372908470 "\002", org_length=816) at /home/elenst/git/10.1/storage/maria/ma_blockrec.c:1974
 
#7  0x00007fc399c151de in write_block_record (info=0x7fc36f8e7070, old_record=0x7fc36f8e08b8 "", record=0x7fc36f8e0088 "", row=0x7fc36f8e7188, bitmap_blocks=0x7fc36f8e70c8, head_block_is_read=1 '\001', row_pos=0x7fc3991ee2e0, undo_lsn=1, old_record_checksum=0) at /home/elenst/git/10.1/storage/maria/ma_blockrec.c:3090
 
#8  0x00007fc399c16fda in _ma_update_block_record2 (info=0x7fc36f8e7070, record_pos=28430, oldrec=0x7fc36f8e08b8 "", record=0x7fc36f8e0088 "", undo_lsn=1) at /home/elenst/git/10.1/storage/maria/ma_blockrec.c:3856
 
#9  0x00007fc399c17719 in _ma_update_block_record (info=0x7fc36f8e7070, record_pos=28430, orig_rec=0x7fc36f8e08b8 "", new_rec=0x7fc36f8e0088 "") at /home/elenst/git/10.1/storage/maria/ma_blockrec.c:4020
 
#10 0x00007fc399c26fc5 in maria_update (info=0x7fc36f8e7070, oldrec=0x7fc36f8e08b8 "", newrec=0x7fc36f8e0088 "") at /home/elenst/git/10.1/storage/maria/ma_update.c:159
 
#11 0x00007fc399bb4d1e in ha_maria::update_row (this=0x7fc36f8de370, old_data=0x7fc36f8e08b8 "", new_data=0x7fc36f8e0088 "") at /home/elenst/git/10.1/storage/maria/ha_maria.cc:2309
 
#12 0x00007fc399826f68 in handler::ha_update_tmp_row (this=0x7fc36f8de370, old_data=0x7fc36f8e08b8 "", new_data=0x7fc36f8e0088 "") at /home/elenst/git/10.1/sql/sql_class.h:5447
 
#13 0x00007fc399813637 in end_update (join=0x7fc372af9ed8, join_tab=0x7fc36f81ded8, end_of_records=false) at /home/elenst/git/10.1/sql/sql_select.cc:19757
 
#14 0x00007fc39991dd11 in JOIN_CACHE::generate_full_extensions (this=0x7fc36f821118, rec_ptr=0x7fc36f877094 "\005") at /home/elenst/git/10.1/sql/sql_join_cache.cc:2405
 
#15 0x00007fc39991da54 in JOIN_CACHE::join_matching_records (this=0x7fc36f821118, skip_last=false) at /home/elenst/git/10.1/sql/sql_join_cache.cc:2297
 
#16 0x00007fc39991d466 in JOIN_CACHE::join_records (this=0x7fc36f821118, skip_last=false) at /home/elenst/git/10.1/sql/sql_join_cache.cc:2092
 
#17 0x00007fc39991d600 in JOIN_CACHE::join_records (this=0x7fc36f81f050, skip_last=false) at /home/elenst/git/10.1/sql/sql_join_cache.cc:2138
 
#18 0x00007fc39980f99e in sub_select_cache (join=0x7fc372af9ed8, join_tab=0x7fc36f81d848, end_of_records=true) at /home/elenst/git/10.1/sql/sql_select.cc:18091
 
#19 0x00007fc39980fb88 in sub_select (join=0x7fc372af9ed8, join_tab=0x7fc36f81d500, end_of_records=true) at /home/elenst/git/10.1/sql/sql_select.cc:18261
 
#20 0x00007fc39980f66f in do_select (join=0x7fc372af9ed8, fields=0x0, table=0x7fc36f8be088, procedure=0x0) at /home/elenst/git/10.1/sql/sql_select.cc:17967
 
#21 0x00007fc3997e86a4 in JOIN::exec_inner (this=0x7fc372af9ed8) at /home/elenst/git/10.1/sql/sql_select.cc:2772
 
#22 0x00007fc3997e771b in JOIN::exec (this=0x7fc372af9ed8) at /home/elenst/git/10.1/sql/sql_select.cc:2518
 
#23 0x00007fc3997eacce in mysql_select (thd=0x7fc37b761070, rref_pointer_array=0x7fc37296aac8, tables=0x7fc372be3a70, wild_num=0, fields=..., conds=0x7fc36f81b1a8, og_num=2, order=0x0, group=0x7fc372be5520, having=0x0, proc_param=0x0, select_options=2416184064, result=0x7fc372af9de0, unit=0x7fc37296a150, select_lex=0x7fc37296a850) at /home/elenst/git/10.1/sql/sql_select.cc:3451
 
#24 0x00007fc39977c5e7 in mysql_derived_fill (thd=0x7fc37b761070, lex=0x7fc37b7649c0, derived=0x7fc37292e620) at /home/elenst/git/10.1/sql/sql_derived.cc:938
 
#25 0x00007fc39977af02 in mysql_handle_single_derived (lex=0x7fc37b7649c0, derived=0x7fc37292e620, phases=96) at /home/elenst/git/10.1/sql/sql_derived.cc:195
 
#26 0x00007fc39980044d in st_join_table::preread_init (this=0x7fc36f823268) at /home/elenst/git/10.1/sql/sql_select.cc:11695
 
#27 0x00007fc3998173e3 in create_sort_index (thd=0x7fc37b761070, join=0x7fc372ae6328, order=0x7fc372926428, filesort_limit=18446744073709551615, select_limit=18446744073709551615, is_order_by=true) at /home/elenst/git/10.1/sql/sql_select.cc:21437
 
#28 0x00007fc3997ea25f in JOIN::exec_inner (this=0x7fc372ae6328) at /home/elenst/git/10.1/sql/sql_select.cc:3198
 
#29 0x00007fc3997e771b in JOIN::exec (this=0x7fc372ae6328) at /home/elenst/git/10.1/sql/sql_select.cc:2518
 
#30 0x00007fc3997eacce in mysql_select (thd=0x7fc37b761070, rref_pointer_array=0x7fc37b765400, tables=0x7fc37292e620, wild_num=0, fields=..., conds=0x7fc3729261f8, og_num=2, order=0x7fc372926428, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fc372ae6308, unit=0x7fc37b764a88, select_lex=0x7fc37b765188) at /home/elenst/git/10.1/sql/sql_select.cc:3451
 
#31 0x00007fc3997e0773 in handle_select (thd=0x7fc37b761070, lex=0x7fc37b7649c0, result=0x7fc372ae6308, setup_tables_done_option=0) at /home/elenst/git/10.1/sql/sql_select.cc:384
 
#32 0x00007fc3997b0974 in execute_sqlcom_select (thd=0x7fc37b761070, all_tables=0x7fc37292e620) at /home/elenst/git/10.1/sql/sql_parse.cc:5894
 
#33 0x00007fc3997a685e in mysql_execute_command (thd=0x7fc37b761070) at /home/elenst/git/10.1/sql/sql_parse.cc:2960
 
#34 0x00007fc3997b408c in mysql_parse (thd=0x7fc37b761070, rawbuf=0x7fc372822088 "SELECT dtcv.data_table_id AS data_table_id,\n       dtcv.data_type AS test_data_type,\n       dtcv.dtc1 AS attr1,\n       dtcv.dtc2 AS attr2,\n       dtcv.dtc3 AS attr3,\n       dtcv.dtc4 AS attr4,\n       "..., length=6207, parser_state=0x7fc3991f05c0) at /home/elenst/git/10.1/sql/sql_parse.cc:7314
 
#35 0x00007fc3997a2ac5 in dispatch_command (command=COM_QUERY, thd=0x7fc37b761070, packet=0x7fc3729d7071 "SELECT dtcv.data_table_id AS data_table_id,\n       dtcv.data_type AS test_data_type,\n       dtcv.dtc1 AS attr1,\n       dtcv.dtc2 AS attr2,\n       dtcv.dtc3 AS attr3,\n       dtcv.dtc4 AS attr4,\n       "..., packet_length=6207) at /home/elenst/git/10.1/sql/sql_parse.cc:1486
 
#36 0x00007fc3997a17f7 in do_command (thd=0x7fc37b761070) at /home/elenst/git/10.1/sql/sql_parse.cc:1107
 
#37 0x00007fc3998d8127 in do_handle_one_connection (thd_arg=0x7fc37b761070) at /home/elenst/git/10.1/sql/sql_connect.cc:1350
 
#38 0x00007fc3998d7e8b in handle_one_connection (arg=0x7fc37b761070) at /home/elenst/git/10.1/sql/sql_connect.cc:1262
 
#39 0x00007fc397bbae9a in start_thread () from /lib/x86_64-linux-gnu/libpthread.so.0
 
#40 0x00007fc3972e7cbd in clone () from /lib/x86_64-linux-gnu/libc.so.6
 
#41 0x0000000000000000 in ?? ()

Comment by Elena Stepanova [ 2019-05-01 ]

Still reproducible on 10.1, but gone from 10.2. Possibly the underlying issue with the small size of aria_pagecache_buffer_size, but on 10.2 the query now is almost instantaneous (even with {{ IGNORE INDEX (CM_BA_DATA_TABLE_COLUMNS_U01)}} in cm_ba_data_table_columns_seq_v to make execution plans seemingly identical), so there is no race condition, even with 10 connections running the query in parallel.

Comment by Elena Stepanova [ 2023-07-28 ]

10.1 is EOL

Generated at Thu Feb 08 07:41:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.