Attached script indeed counts writes to tempfile, not tempfile size. If tempfile is rewritten, its size is actually smaller.
Please find attached tempfile-usage3.bt which catches lseek calls, and also tracks read to see how large the file was. Example output.
XX Lseek on fd=57
Lseek on fd=57
^C
@lseek_in_progress[15244]: 57
@tempfile_max_read[58]: 5954293
@tempfile_max_read[57]: 5968974
@tempfile_size[58]: 5954293
@tempfile_size[57]: 11920744
Note that tempfile_max_read[57] < tempfile_size[57]: 11920744, and tempfile_max_read is how large the file actually was.
Sergei Petrunia
added a comment - lstartseva , followup to previous talk:
Attached script indeed counts writes to tempfile, not tempfile size. If tempfile is rewritten, its size is actually smaller.
Please find attached tempfile-usage3.bt which catches lseek calls, and also tracks read to see how large the file was. Example output.
XX Lseek on fd=57
Lseek on fd=57
^C
@lseek_in_progress[15244]: 57
@tempfile_max_read[58]: 5954293
@tempfile_max_read[57]: 5968974
@tempfile_size[58]: 5954293
@tempfile_size[57]: 11920744
Note that tempfile_max_read [57] < tempfile_size [57] : 11920744, and tempfile_max_read is how large the file actually was.
if (buff_sz < full_size * (file_ptrs.elements + 1UL))
res= merge(table, merge_buffer, buff_sz,
buff_sz >= full_size * MERGEBUFF2) ;
if (!res)
{
res= merge_walk(merge_buffer, buff_sz, full_size,
(Merge_chunk *) file_ptrs.buffer,
file_ptrs.elements is "how many unique objects were dumped".
buff_sz is the amount of buffer space we have
full_size is one element size.
This means if the buffer is so so small that we can't have one element from each file in the buffer, then... we use merge_walk() procedure that merges all the buffers at once.
this is an edge case. It would be nice if testing involved this edge case but mostly "normal" case.
One can recognize which case occurred by checking whether lseek call was made (see the trace script).
Sergei Petrunia
added a comment - - edited I've also discovered this piece of code in:
bool Unique::walk(TABLE *table, tree_walk_action action, void *walk_action_arg)
if (buff_sz < full_size * (file_ptrs.elements + 1UL))
res= merge(table, merge_buffer, buff_sz,
buff_sz >= full_size * MERGEBUFF2) ;
if (!res)
{
res= merge_walk(merge_buffer, buff_sz, full_size,
(Merge_chunk *) file_ptrs.buffer,
file_ptrs.elements is "how many unique objects were dumped".
buff_sz is the amount of buffer space we have
full_size is one element size.
This means if the buffer is so so small that we can't have one element from each file in the buffer, then... we use merge_walk() procedure that merges all the buffers at once.
This involves doing a lot of small read calls:
@tempfile_read_size:
[8, 16) 2297 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@|
[16, 32) 495 |@@@@@@@@@@@ |
[32, 64) 458 |@@@@@@@@@@ |
[64, 128) 465 |@@@@@@@@@@ |
[128, 256) 463 |@@@@@@@@@@ |
[256, 512) 491 |@@@@@@@@@@@ |
[512, 1K) 517 |@@@@@@@@@@@ |
[1K, 2K) 511 |@@@@@@@@@@@ |
[2K, 4K) 329 |@@@@@@@ |
[4K, 8K) 1198 |@@@@@@@@@@@@@@@@@@@@@@@@@@@ |
this is an edge case. It would be nice if testing involved this edge case but mostly "normal" case.
One can recognize which case occurred by checking whether lseek call was made (see the trace script).
There are usually two temporary files generated. To understand when it happens see the stacktraces below:
Stacktrace of the creation of the first temporary file:
#1 0x0000556bfb918003 in real_open_cached_file (cache=0x7f998401bdc8) at .../MariaDB/10.6/mysys/mf_cache.c:61
#2 0x0000556bfb91d387 in my_b_flush_io_cache (info=0x7f998401bdc8, need_append_buffer_lock=0) at .../MariaDB/10.6/mysys/mf_iocache.c:1706
#3 0x0000556bfb91aba7 in _my_b_write (info=0x7f998401bdc8, Buffer=0x7f9984ae6774 "p%dJ&zX(^#2p`m&n|F&)=66KXZ:Sre\"uLZaYyv\217\217\217\217\217\217\370\253\235\204\231\177", Count=38)
at .../MariaDB/10.6/mysys/mf_iocache.c:559
#4 0x0000556bfad83099 in my_b_write (info=0x7f998401bdc8, Buffer=0x7f9984ae6700 "\226", Count=154) at .../MariaDB/10.6/include/my_sys.h:522
#5 0x0000556bfad83154 in Unique::unique_write_to_file (key=0x7f9984ae6700 "\226", count=1, unique=0x7f998401bd90) at .../MariaDB/10.6/sql/uniques.cc:45
#9 0x0000556bfb0ce3ef in Aggregator_distinct::endup (this=0x7f998401bbd8) at .../MariaDB/10.6/sql/item_sum.cc:1100
#10 0x0000556bfb0d2022 in Item_sum_count::val_int (this=0x7f99840184f8) at .../MariaDB/10.6/sql/item_sum.cc:1946
#11 0x0000556bfae95a3b in Type_handler::Item_send_longlong (this=0x556bfc6aa7c0 <type_handler_slonglong>, item=0x7f99840184f8, protocol=0x7f99840013e0, buf=0x7f99afefcaa0)
at .../MariaDB/10.6/sql/sql_type.cc:7514
Lena Startseva
added a comment - There are usually two temporary files generated. To understand when it happens see the stacktraces below:
Stacktrace of the creation of the first temporary file:
#0 create_temp_file (to=0x7f99afefc570 "\001", dir=0x556bfef55838 ".../MariaDB/10.6/bld/mysql-test/var/tmp/mysqld.1", prefix=0x7f998401bf00 "MY", mode=0, MyFlags=80)
at .../MariaDB/10.6/mysys/mf_tempfile.c:64
#1 0x0000556bfb918003 in real_open_cached_file (cache=0x7f998401bdc8) at .../MariaDB/10.6/mysys/mf_cache.c:61
#2 0x0000556bfb91d387 in my_b_flush_io_cache (info=0x7f998401bdc8, need_append_buffer_lock=0) at .../MariaDB/10.6/mysys/mf_iocache.c:1706
#3 0x0000556bfb91aba7 in _my_b_write (info=0x7f998401bdc8, Buffer=0x7f9984ae6774 "p%dJ&zX(^#2p`m&n|F&)=66KXZ:Sre\"uLZaYyv\217\217\217\217\217\217\370\253\235\204\231\177", Count=38)
at .../MariaDB/10.6/mysys/mf_iocache.c:559
#4 0x0000556bfad83099 in my_b_write (info=0x7f998401bdc8, Buffer=0x7f9984ae6700 "\226", Count=154) at .../MariaDB/10.6/include/my_sys.h:522
#5 0x0000556bfad83154 in Unique::unique_write_to_file (key=0x7f9984ae6700 "\226", count=1, unique=0x7f998401bd90) at .../MariaDB/10.6/sql/uniques.cc:45
#6 0x0000556bfb94e8a9 in tree_walk_left_root_right (tree=0x7f998401bf38, element=0x7f9984ae66e8, action=0x556bfad830f8 <Unique::unique_write_to_file(unsigned char*, unsigned int, Unique*)>,
argument=0x7f998401bd90) at .../MariaDB/10.6/mysys/tree.c:590
#7 0x0000556bfb94e85a in tree_walk_left_root_right (tree=0x7f998401bf38, element=0x7f9984992a20, action=0x556bfad830f8 <Unique::unique_write_to_file(unsigned char*, unsigned int, Unique*)>,
argument=0x7f998401bd90) at .../MariaDB/10.6/mysys/tree.c:588
#8 0x0000556bfb94e8ce in tree_walk_left_root_right (tree=0x7f998401bf38, element=0x7f9984ad4928, action=0x556bfad830f8 <Unique::unique_write_to_file(unsigned char*, unsigned int, Unique*)>,
argument=0x7f998401bd90) at .../MariaDB/10.6/mysys/tree.c:593
#9 0x0000556bfb94e8ce in tree_walk_left_root_right (tree=0x7f998401bf38, element=0x7f9984a7a838, action=0x556bfad830f8 <Unique::unique_write_to_file(unsigned char*, unsigned int, Unique*)>,
argument=0x7f998401bd90) at .../MariaDB/10.6/mysys/tree.c:593
#10 0x0000556bfb94e85a in tree_walk_left_root_right (tree=0x7f998401bf38, element=0x7f99849d4730, action=0x556bfad830f8 <Unique::unique_write_to_file(unsigned char*, unsigned int, Unique*)>,
argument=0x7f998401bd90) at .../MariaDB/10.6/mysys/tree.c:588
#11 0x0000556bfb94e8ce in tree_walk_left_root_right (tree=0x7f998401bf38, element=0x7f9984a701f0, action=0x556bfad830f8 <Unique::unique_write_to_file(unsigned char*, unsigned int, Unique*)>,
argument=0x7f998401bd90) at .../MariaDB/10.6/mysys/tree.c:593
Stacktrace of the creation of the second temporary file:
#0 create_temp_file (to=0x7f99afefc090 "m\034\357\373kU", dir=0x556bfef55838 ".../MariaDB/10.6/bld/mysql-test/var/tmp/mysqld.1", prefix=0x7f99afefc708 "MY", mode=0, MyFlags=80)
at .../MariaDB/10.6/mysys/mf_tempfile.c:64
#1 0x0000556bfb918003 in real_open_cached_file (cache=0x7f99afefc5d0) at .../MariaDB/10.6/mysys/mf_cache.c:61
#2 0x0000556bfb91d387 in my_b_flush_io_cache (info=0x7f99afefc5d0, need_append_buffer_lock=0) at .../MariaDB/10.6/mysys/mf_iocache.c:1706
#3 0x0000556bfb91aba7 in _my_b_write (info=0x7f99afefc5d0,
Buffer=0x7f9984bc1769 "P_ju12GM0CCdN:vf{Z1$^2x-pnYTvwpNrw{%Eh`'?H,@>6AXyvg#r8`Y|)RA/g9'TS#RW<gr*t/+))4h2\\Y*d:48X7F:2)v:" , '\245' <repeats 104 times>..., Count=45)
at .../MariaDB/10.6/mysys/mf_iocache.c:559
#4 0x0000556bfafa86c2 in my_b_write (info=0x7f99afefc5d0, Buffer=0x7f9984bc172c "f", Count=106) at .../MariaDB/10.6/include/my_sys.h:522
#5 0x0000556bfafaf45a in merge_buffers (param=0x7f99afefc7d0, from_file=0x7f998401bdc8, to_file=0x7f99afefc5d0, sort_buffer=..., lastbuff=0x7f9984a10118, Fb=0x7f9984a10118, Tb=0x7f9984a10268, flag=0)
at .../MariaDB/10.6/sql/filesort.cc:1980
#6 0x0000556bfafae290 in merge_many_buff (param=0x7f99afefc7d0, sort_buffer=..., buffpek=0x7f9984a10118, maxbuffer=0x7f99afefc7a4, t_file=0x7f998401bdc8)
at .../MariaDB/10.6/sql/filesort.cc:1657
#7 0x0000556bfad84d8b in Unique::merge (this=0x7f998401bd90, table=0x7f99849bbf20, buff=0x7f9984ac1f88 "i", buff_size=1046436, without_last_merge=true)
at .../MariaDB/10.6/sql/uniques.cc:769
#8 0x0000556bfad84a06 in Unique::walk (this=0x7f998401bd90, table=0x7f99849bbf20, action=0x556bfb0ccf82 <item_sum_distinct_walk_for_count(void*, element_count, void*)>, walk_action_arg=0x7f998401bbd8)
at .../MariaDB/10.6/sql/uniques.cc:689
#9 0x0000556bfb0ce3ef in Aggregator_distinct::endup (this=0x7f998401bbd8) at .../MariaDB/10.6/sql/item_sum.cc:1100
#10 0x0000556bfb0d2022 in Item_sum_count::val_int (this=0x7f99840184f8) at .../MariaDB/10.6/sql/item_sum.cc:1946
#11 0x0000556bfae95a3b in Type_handler::Item_send_longlong (this=0x556bfc6aa7c0 <type_handler_slonglong>, item=0x7f99840184f8, protocol=0x7f99840013e0, buf=0x7f99afefcaa0)
at .../MariaDB/10.6/sql/sql_type.cc:7514
How to get EITS to always put the same data into Unique when analyze_sample_percentage<100: Set the Rand's Seed.
set @@rand_seed1=N1, @@rand_seed2=N2; analyze table t1 persistent for all;
Sergei Petrunia
added a comment - How to get EITS to always put the same data into Unique when analyze_sample_percentage<100: Set the Rand's Seed.
set @@rand_seed1=N1, @@rand_seed2=N2; analyze table t1 persistent for all;
lstartseva, followup to previous talk:
Attached script indeed counts writes to tempfile, not tempfile size. If tempfile is rewritten, its size is actually smaller.
Please find attached tempfile-usage3.bt which catches lseek calls, and also tracks read to see how large the file was. Example output.
XX Lseek on fd=57
Lseek on fd=57
^C
@lseek_in_progress[15244]: 57
@tempfile_max_read[58]: 5954293
@tempfile_max_read[57]: 5968974
@tempfile_size[58]: 5954293
@tempfile_size[57]: 11920744
Note that tempfile_max_read[57] < tempfile_size[57]: 11920744, and tempfile_max_read is how large the file actually was.