- 8.0.16 - internal_tmp_disk_storage_engine 
# In MySQL 8.0.16 and later, on-disk internal temporary tables always use the InnoDB storage engine; as of MySQL 8.0.16, this variable has been removed and is thus no longer supported. 

rm -rf 96239/
bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/96239 --log-error-verbosity=3
bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/96239 --core-file --socket=/tmp/mysql_ushastry.sock  --port=3333 --log-error=$PWD/96239/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3  --secure-file-priv=/tmp/ --innodb_buffer_pool_size=128M --temptable_max_ram=2097152 --temptable_use_mmap=OFF 2>&1 &

bin/sysbench share/sysbench/oltp_read_write.lua --threads=100 --tables=2  --table_size=1000000 --db-driver=mysql --mysql-db=test --mysql-user=ushastry --mysql-password=mysql123 --mysql-socket=/tmp/mysql_ushastry.sock prepare

-
 bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

- sysbench

 bin/sysbench share/sysbench/oltp_read_write.lua --threads=100 --tables=2  --table_size=1000000 --db-driver=mysql --mysql-db=test --mysql-user=ushastry --mysql-password=mysql123 --mysql-socket=/tmp/mysql_ushastry.sock prepare
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)

Initializing worker threads...

Creating table 'sbtest2'...Creating table 'sbtest1'...

Inserting 1000000 records into 'sbtest1'
Inserting 1000000 records into 'sbtest2'
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest1'...

- CLI session

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update sbtest1 set pad = '22195207048-70116052123-74140395089-76317954521-98694025897';
Query OK, 999999 rows affected (16.82 sec)
Rows matched: 1000000  Changed: 999999  Warnings: 0

mysql> update sbtest2 set pad = '22195207048-70116052123-74140395089-76317954521-98694025897';
Query OK, 1000000 rows affected (17.45 sec)
Rows matched: 1000000  Changed: 1000000  Warnings: 0

mysql> create view view_sb_2 as select * from sbtest1 union all select * from sbtest2;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from view_sb_2 where pad = '22195207048-70116052123-74140395089-76317954521-98694025897' order by id desc limit 0, 10;

^^ still going on

- CLI2

mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------------------------------------------------------------------------------------------+
| Id | User            | Host      | db   | Command | Time | State                  | Info                                                                                                 |
+----+-----------------+-----------+------+---------+------+------------------------+------------------------------------------------------------------------------------------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  |  366 | Waiting on empty queue | NULL                                                                                                 |
|  7 | root            | localhost | test | Query   |  357 | Creating sort index    | select * from view_sb_2 where pad = '22195207048-70116052123-74140395089-76317954521-98694025897' or |
| 10 | root            | localhost | NULL | Query   |    0 | starting               | show processlist                                                                                     |
+----+-----------------+-----------+------+---------+------+------------------------+------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

.
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------------------------------------------------------------------------------------------+
| Id | User            | Host      | db   | Command | Time | State                  | Info                                                                                                 |
+----+-----------------+-----------+------+---------+------+------------------------+------------------------------------------------------------------------------------------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  |  789 | Waiting on empty queue | NULL                                                                                                 |
|  7 | root            | localhost | test | Query   |  780 | Creating sort index    | select * from view_sb_2 where pad = '22195207048-70116052123-74140395089-76317954521-98694025897' or |
| 10 | root            | localhost | NULL | Query   |    0 | starting               | show processlist                                                                                     |
+----+-----------------+-----------+------+---------+------+------------------------+------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)


mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------------------------------------------------------------------------------------------+
| Id | User            | Host      | db   | Command | Time | State                  | Info                                                                                                 |
+----+-----------------+-----------+------+---------+------+------------------------+------------------------------------------------------------------------------------------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  |  870 | Waiting on empty queue | NULL                                                                                                 |
|  7 | root            | localhost | test | Query   |  861 | Creating sort index    | select * from view_sb_2 where pad = '22195207048-70116052123-74140395089-76317954521-98694025897' or |
.

mysql> use test

^^ hanged


- excerpt from error log


2019-07-18T04:56:20.964333Z 7 [Warning] [MY-011959] [InnoDB] Difficult to find free blocks in the buffer pool (6900 search iterations)! 6900 failed attempts to flush a page! Consider increasing the buffer pool size. It is also possible that in your Unix version fsync is very slow, or completely frozen inside the OS kernel. Then upgrading to a newer version of your operating system may help. Look at the number of fsyncs in diagnostic info below. Pending flushes (fsync) log: 0; buffer pool: 0. 54583 OS file reads, 134312 OS file writes, 48 OS fsyncs. Starting InnoDB Monitor to print further diagnostics to the standard output.

- pmp

     10 __io_getevents_0_4(libaio.so.1),LinuxAIOHandler::collect,LinuxAIOHandler::poll,os_aio_linux_handler,os_aio_handler(unsigned,fil_aio_wait,io_handler_thread,void,__invoke<void,__call<void,,operator()<>,operator()<void,__invoke_impl<void,,__invoke<Runnable,,_M_invoke<0,,operator(),std::thread::_State_impl<std::thread::_Invoker<std::tuple<Runnable,,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
      3 pthread_cond_wait,wait,os_event::wait_low,os_event_wait_low,srv_worker_thread,void,__invoke<void,__call<void>,operator()<>,operator()<void,__invoke_impl<void,,__invoke<Runnable,,_M_invoke<0,,operator(),std::thread::_State_impl<std::thread::_Invoker<std::tuple<Runnable,,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
      2 pthread_cond_timedwait,native_cond_timedwait,my_cond_timedwait,inline_mysql_cond_timedwait,xpl::Cond::timed_wait,ngs::Scheduler_dynamic::wait_if_idle_then_delete_worker(atomic_base.h:400),ngs::Scheduler_dynamic::worker,pfs_spawn_thread,start_thread(libpthread.so.0),clone(libc.so.6)
      1 syscall(libc.so.6),epoll_wait,epoll_dispatch,event_base_loop,ngs::Server::run_task,ngs::Server::start(atomicity.h:96),xpl::Server::net_thread,operator(std_function.h:682),ngs::Scheduler_dynamic::worker()(std_function.h:682),pfs_spawn_thread,start_thread(libpthread.so.0),clone(libc.so.6)
      1 sigwaitinfo(libc.so.6),signal_hand,pfs_spawn_thread,start_thread(libpthread.so.0),clone(libc.so.6)
      1 pthread_cond_wait,wait,os_event::wait_low,os_event_wait_low,srv_purge_coordinator_suspend,srv_purge_coordinator_thread,void,__invoke<void,__call<void>,operator()<>,operator()<void,__invoke_impl<void,,__invoke<Runnable,,_M_invoke<0,,operator(),std::thread::_State_impl<std::thread::_Invoker<std::tuple<Runnable,,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
      1 pthread_cond_wait,wait,os_event::wait_low,os_event_wait_low,buf_resize_thread,void,__invoke<void,__call<void>,operator()<>,operator()<void,__invoke_impl<void,,__invoke<Runnable,,_M_invoke<0,,operator(),std::thread::_State_impl<std::thread::_Invoker<std::tuple<Runnable,,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
      1 pthread_cond_wait,wait,os_event::wait_low,os_event_wait_low,buf_dump_thread,void,__invoke<void,__call<void>,operator()<>,operator()<void,__invoke_impl<void,,__invoke<Runnable,,_M_invoke<0,,operator(),std::thread::_State_impl<std::thread::_Invoker<std::tuple<Runnable,,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
      1 pthread_cond_wait,native_cond_wait,my_cond_wait,inline_mysql_cond_wait,xpl::Cond::wait,wait,xpl::Broker_task::wait_for_data_and_swap_queues(std::queue<ngs::Notice_descriptor,,xpl::Broker_task::loop,ngs::Server::run_task,operator(atomicity.h:96),std::_Function_handler<void(atomicity.h:96),operator(std_function.h:682),ngs::Scheduler_dynamic::worker()(std_function.h:682),pfs_spawn_thread,start_thread(libpthread.so.0),clone(libc.so.6)
      1 pthread_cond_wait,native_cond_wait,my_cond_wait,inline_mysql_cond_wait,Per_thread_connection_handler::block_until_new_connection,handle_connection,pfs_spawn_thread,start_thread(libpthread.so.0),clone(libc.so.6)
      1 pthread_cond_wait,native_cond_wait,my_cond_wait,inline_mysql_cond_wait,Event_queue::cond_wait,Event_queue::get_top_for_execution_if_time,Event_scheduler::run,event_scheduler_thread,pfs_spawn_thread,start_thread(libpthread.so.0),clone(libc.so.6)
      1 pthread_cond_wait,native_cond_wait,my_cond_wait,inline_mysql_cond_wait,compress_gtid_table,pfs_spawn_thread,start_thread(libpthread.so.0),clone(libc.so.6)
      1 pthread_cond_timedwait,os_event::timed_wait,os_event::wait_time_low,os_event_wait_time_low,srv_monitor_thread,void,__invoke<void,__call<void>,operator()<>,operator()<void,__invoke_impl<void,,__invoke<Runnable,,_M_invoke<0,,operator(),std::thread::_State_impl<std::thread::_Invoker<std::tuple<Runnable,,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
      1 pthread_cond_timedwait,os_event::timed_wait,os_event::wait_time_low,os_event_wait_time_low,srv_error_monitor_thread,void,__invoke<void,__call<void>,operator()<>,operator()<void,__invoke_impl<void,,__invoke<Runnable,,_M_invoke<0,,operator(),std::thread::_State_impl<std::thread::_Invoker<std::tuple<Runnable,,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
      1 pthread_cond_timedwait,os_event::timed_wait,os_event::wait_time_low,os_event_wait_time_low,pc_sleep_if_needed,buf_flush_page_coordinator_thread(unsigned,void,__invoke<void,__call<void,,operator()<>,operator()<void,__invoke_impl<void,,__invoke<Runnable,,_M_invoke<0,,operator(),std::thread::_State_impl<std::thread::_Invoker<std::tuple<Runnable,,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
      1 pthread_cond_timedwait,os_event::timed_wait,os_event::wait_time_low,os_event_wait_time_low,os_event_wait_for<log_writer::<lambda>>,wait<log_writer(log_t*)::<lambda(bool)>,log_writer(log_t*),void,__invoke<void,__call<void,,operator()<>,operator()<void,__invoke_impl<void,,__invoke<Runnable,,_M_invoke<0,,operator(),std::thread::_State_impl<std::thread::_Invoker<std::tuple<Runnable,,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
      1 pthread_cond_timedwait,os_event::timed_wait,os_event::wait_time_low,os_event_wait_time_low,os_event_wait_for<log_write_notifier::<lambda>>(unique_ptr.h:712),wait<log_write_notifier(log_t*)::<lambda(bool)>(unique_ptr.h:712),log_write_notifier(log_t*)(unique_ptr.h:712),void(unique_ptr.h:712),__invoke<void(unique_ptr.h:712),__call<void,(unique_ptr.h:712),operator()<>(unique_ptr.h:712),operator()<void(unique_ptr.h:712),__invoke_impl<void,(unique_ptr.h:712),__invoke<Runnable,(unique_ptr.h:712),_M_invoke<0,(unique_ptr.h:712),operator()(unique_ptr.h:712),std::thread::_State_impl<std::thread::_Invoker<std::tuple<Runnable,(unique_ptr.h:712),execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
      1 pthread_cond_timedwait,os_event::timed_wait,os_event::wait_time_low,os_event_wait_time_low,os_event_wait_for<log_flush_notifier::<lambda>>(unique_ptr.h:712),wait<log_flush_notifier(log_t*)::<lambda(bool)>(unique_ptr.h:712),log_flush_notifier(log_t*)(unique_ptr.h:712),void(unique_ptr.h:712),__invoke<void(unique_ptr.h:712),__call<void,(unique_ptr.h:712),operator()<>(unique_ptr.h:712),operator()<void(unique_ptr.h:712),__invoke_impl<void,(unique_ptr.h:712),__invoke<Runnable,(unique_ptr.h:712),_M_invoke<0,(unique_ptr.h:712),operator()(unique_ptr.h:712),std::thread::_State_impl<std::thread::_Invoker<std::tuple<Runnable,(unique_ptr.h:712),execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
      1 pthread_cond_timedwait,os_event::timed_wait,os_event::wait_time_low,os_event_wait_time_low,os_event_wait_for<log_flusher::<lambda>>,wait<log_flusher(log_t*)::<lambda(bool)>,log_flusher(log_t*),void,__invoke<void,__call<void,,operator()<>,operator()<void,__invoke_impl<void,,__invoke<Runnable,,_M_invoke<0,,operator(),std::thread::_State_impl<std::thread::_Invoker<std::tuple<Runnable,,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
      1 pthread_cond_timedwait,os_event::timed_wait,os_event::wait_time_low,os_event_wait_time_low,os_event_wait_for<log_closer::<lambda>>,wait<log_closer(log_t*)::<lambda(bool)>,log_closer,void,__invoke<void,__call<void,,operator()<>,operator()<void,__invoke_impl<void,,__invoke<Runnable,,_M_invoke<0,,operator(),std::thread::_State_impl<std::thread::_Invoker<std::tuple<Runnable,,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
      1 pthread_cond_timedwait,os_event::timed_wait,os_event::wait_time_low,os_event_wait_time_low,log_checkpointer,void,__invoke<void,__call<void,,operator()<>,operator()<void,__invoke_impl<void,,__invoke<Runnable,,_M_invoke<0,,operator(),std::thread::_State_impl<std::thread::_Invoker<std::tuple<Runnable,,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
      1 pthread_cond_timedwait,os_event::timed_wait,os_event::wait_time_low,os_event_wait_time_low,lock_wait_timeout_thread,void,__invoke<void,__call<void>,operator()<>,operator()<void,__invoke_impl<void,,__invoke<Runnable,,_M_invoke<0,,operator(),std::thread::_State_impl<std::thread::_Invoker<std::tuple<Runnable,,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
      1 pthread_cond_timedwait,os_event::timed_wait,os_event::wait_time_low,os_event_wait_time_low,ib_wqueue_timedwait,fts_optimize_thread,void,__invoke<void,__call<void,,operator()<>,operator()<void,__invoke_impl<void,,__invoke<Runnable,,_M_invoke<0,,operator(),std::thread::_State_impl<std::thread::_Invoker<std::tuple<Runnable,,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
      1 pthread_cond_timedwait,os_event::timed_wait,os_event::wait_time_low,os_event_wait_time_low,dict_stats_thread,void,__invoke<void,__call<void>,operator()<>,operator()<void,__invoke_impl<void,,__invoke<Runnable,,_M_invoke<0,,operator(),std::thread::_State_impl<std::thread::_Invoker<std::tuple<Runnable,,execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
      1 ppoll(libc.so.6),vio_io_wait,vio_socket_io_wait,vio_read,net_read_raw_loop,net_read_packet_header,net_read_packet,my_net_read,Protocol_classic::read_packet,Protocol_classic::get_command,do_command,handle_connection,pfs_spawn_thread,start_thread(libpthread.so.0),clone(libc.so.6)
      1 poll(libc.so.6),Mysqld_socket_listener::listen_for_connection_event(stl_map.h:468),connection_event_loop,mysqld_main(int,,__libc_start_main(libc.so.6),_start
      1 nanosleep(libpthread.so.0),sleep_for<long,std::ratio<1,1000000>>(chrono:465),srv_master_sleep(chrono:465),srv_master_thread()(chrono:465),void(chrono:465),__invoke<void(chrono:465),__call<void>(chrono:465),operator()<>(chrono:465),operator()<void(chrono:465),__invoke_impl<void,(chrono:465),__invoke<Runnable,(chrono:465),_M_invoke<0,(chrono:465),operator()(chrono:465),std::thread::_State_impl<std::thread::_Invoker<std::tuple<Runnable,(chrono:465),execute_native_thread_routine,start_thread(libpthread.so.0),clone(libc.so.6)
      1 nanosleep(libpthread.so.0),sleep_for<long,std::ratio<1,1000000>>(chrono:465),buf_LRU_get_free_block(buf_pool_t*)(chrono:465),buf_page_init_for_read,buf_read_page_low,buf_read_page,Buf_fetch::read_page,Buf_fetch_other::get,Buf_fetch::single_page,buf_page_get_gen,btr_cur_search_to_nth_level_with_no_latch,open_no_init,Row_sel_get_clust_rec_for_mysql::operator()(row_prebuilt_t*,,row_search_no_mvcc,ha_innobase::general_fetch,handler::ha_index_next_same,RefIterator::Read,FilterIterator::Read(unique_ptr.h:342),read_all_rows,filesort(THD*,,SortingIterator::DoSort,SortingIterator::Init,sub_select,do_select,JOIN::exec(),Sql_cmd_dml::execute_inner,Sql_cmd_dml::execute,mysql_execute_command,mysql_parse,dispatch_command,do_command,handle_connection,pfs_spawn_thread,start_thread(libpthread.so.0),clone(libc.so.6)