Closed mdcallag closed 1 month ago
From PMP, hierarchical is here, flat is here
I saved the RocksDB LOG file but it is 1.6M compressed and I don't know where to put it. It is saved on my laptop.
Nothing useful in the MySQL error log
2023-03-31T03:55:40.158936Z mysqld_safe Logging to '/data/m/fbmy/data/c2-60-3.err'.
2023-03-31T03:55:40.179300Z mysqld_safe Starting mysqld daemon with databases from /data/m/fbmy/data
2023-03-31T03:55:40.575208Z 0 [Warning] [MY-011068] [Server] The syntax '--ssl=off' is deprecated and will be removed in a future release. Please use --tls-version=invalid instead.
2023-03-31T03:55:40.575243Z 0 [Warning] [MY-010080] [Server] The use of InnoDB is mandatory since MySQL 5.7. The former options like '--innodb=0/1/OFF/ON' or '--skip-innodb' are ignored.
2023-03-31T03:55:40.575402Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2023-03-31T03:55:40.575414Z 0 [System] [MY-010116] [Server] /home/mcallaghan/d/fbmy80/bin/mysqld (mysqld 8.0.28) starting as process 1093467
2023-03-31T03:55:40.584687Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-03-31T03:55:40.672863Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-03-31T03:55:40.798897Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2023-03-31T03:55:40.810645Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2023-03-31T03:55:40.900373Z 0 [System] [MY-010931] [Server] /home/mcallaghan/d/fbmy80/bin/mysqld: ready for connections. Version: '8.0.28' socket: '/tmp/mysql.sock' port: 3306 Source distribution.
From PMP, and starting with hierarchical output after removing lines with abstimed_wait (output is here)
Many threads stuck in RocksDB, maybe trying to write the WAL
9 __futex_abstimed_wait_common64,__futex_abstimed_wait_common,__GI___futex_abstimed_wait_cancelable64,__pthread_cond_wait_common,___pthread_cond_wait,wait<rocksdb::WriteThread::BlockingAwaitState(rocksdb::WriteThread::Writer*,,rocksdb::WriteThread::BlockingAwaitState,rocksdb::WriteThread::AwaitState,rocksdb::DBImpl::WriteImplWALOnly,rocksdb::DBImpl::WriteImpl,rocksdb::WriteCommittedTxn::PrepareInternal,rocksdb::PessimisticTransaction::Prepare,myrocks::Rdb_transaction_impl::prepare,rocksdb_prepare,ha_prepare_low,ha_commit_trans,trans_commit_stmt,mysql_execute_command,dispatch_sql_command,dispatch_command,do_command,handle_connection,pfs_spawn_thread,start_thread,clone3
Compaction threads are stuck
3 futex_wait,__GI___lll_lock_wait,lll_mutex_lock_optimized,___pthread_mutex_lock,rocksdb::port::Mutex::Lock,rocksdb::InstrumentedMutex::LockInternal,rocksdb::InstrumentedMutex::Lock,rocksdb::DBImpl::BackgroundCompaction,rocksdb::DBImpl::BackgroundCallCompaction,rocksdb::DBImpl::BGWorkCompaction,rocksdb::ThreadPoolImpl::Impl::BGThread,rocksdb::ThreadPoolImpl::Impl::BGThreadWrapper,??,start_thread,clone3
RocksDB memory monitoring thread is stuck
1 futex_wait,__GI___lll_lock_wait,lll_mutex_lock_optimized,___pthread_mutex_lock,rocksdb::port::Mutex::Lock,rocksdb::InstrumentedMutex::LockInternal,rocksdb::InstrumentedMutex::Lock,rocksdb::InstrumentedMutexLock::InstrumentedMutexLock,rocksdb::DBImpl::GetAggregatedIntProperty,rocksdb::MemoryUtil::GetApproximateMemoryUsageByType,myrocks_update_memory_status,show_myrocks_vars,PFS_status_variable_cache::manifest,PFS_status_variable_cache::do_materialize_global,PFS_variable_cache<Status_variable>::materialize_global,PFS_variable_cache<Status_variable>::materialize_global,table_global_status::rnd_init,ha_perfschema::rnd_init,handler::ha_rnd_init,TableScanIterator::Init,MaterializeIterator::MaterializeQueryBlock,MaterializeIterator::Init,Query_expression::ExecuteIteratorQuery,Query_expression::execute,Sql_cmd_dml::execute_inner,Sql_cmd_dml::execute,Sql_cmd_show::execute,Sql_cmd_show_status::execute,mysql_execute_command,dispatch_sql_command,dispatch_command,do_command,handle_connection,pfs_spawn_thread,start_thread,clone3
Memtable flush is stuck
1 futex_wait,__GI___lll_lock_wait,lll_mutex_lock_optimized,___pthread_mutex_lock,rocksdb::port::Mutex::Lock,rocksdb::InstrumentedMutex::LockInternal,rocksdb::InstrumentedMutex::Lock,rocksdb::FlushJob::WriteLevel0Table,rocksdb::FlushJob::Run,rocksdb::DBImpl::FlushMemTableToOutputFile,rocksdb::DBImpl::FlushMemTablesToOutputFiles,rocksdb::DBImpl::BackgroundFlush,rocksdb::DBImpl::BackgroundCallFlush,rocksdb::ThreadPoolImpl::Impl::BGThread,rocksdb::ThreadPoolImpl::Impl::BGThreadWrapper,??,start_thread,clone3
Another hang is here This one from a build that uses CMAKE_BUILD_TYPE=RelWithDebInfo. The previous hang uses a build with CMAKE_BUILD_TYPE=Relase.
@mdcallag
It happened again. This is very intermittent. I will soon switch to a more recent build and hope it is gone. After kill -9 the database seems to be OK.
The world has moved on to 8.0.32
I have run it many times (maybe 30) and there was a hang once. From PMP this looks like a hang in RocksDB
I compiled 8.0.28 from source at git hash f33578867
RocksDB is at
The server is a c2-standard-60 on GCP with 240G RAM and 30 cores (hyperthreading disabled). More details on the setup are here and this is with the rel_o2_lto build.
The benchmark is run with 20 clients (threads) and the hang occurs during a phase where all clients are doing inserts. It has been stuck for ~5 days.
SHOW PROCESSLIST is
my.cnf is