on 08-22-2014 9:14 AM
Hi
We had a problem with IQ_SYSTEM_MAIN getting full and crashing the the coordinator/writer.
The dbspace is sized at 80GB for a 3Tb multiplex and normally runs at 21% utilization.
Is there any way I monitor the content of this space to determine why it would suddenly fill up
BTW to user data gets writtten to this space
Johan
Hi Johan,
Using Events could help. In manuals a code sample for event BSpaceLogger is provided.
You can customize it to monitor only IQ_SYSTEM_MAIN with 40% or 50% utilization criteria, since usual utilization in your case is only 21 %.
Also as Mark said , double check that Default_Dbspace option is set to a user dbspace.
You can add query below to the event code to check/record if user objects are stored there :
select top 5 name, nblocks, dbspacename from sp_iqspaceinfo() where dbspacename='IQ_SYSTEM_MAIN' order by nblocks desc ;
By the way, did you filter the iqmsg lines for the IQ connID involved in the out of dbspace message? It may give you info about the statements involved.
Regards,
Tayeb.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Tayeb
Thanks , I will put and event in place.
This is the filtering of the user and the last command run. This was the only user on teh IQ server at the time. So it looks like the delete cause the problem, strange
I. 08/16 12:16:58. 0000880800 [20917]: Delete of 7190 rows started for table:
I. 08/16 12:16:58. 0000880800 [20919]:
I. 08/16 12:16:58. 0000880800 Cmt 1463702075
I. 08/16 12:16:58. 0000880800 PostCmt 0
I. 08/16 12:16:58. 0000880800 Txn 1463702076 0 1463702076
I. 08/16 12:16:58. 0000880800 Cmt 1463702077
I. 08/16 12:16:58. 0000880800 PostCmt 0
;
;
;
;
I. 08/16 12:36:18. 0000880800 Cmt 1463706777
I. 08/16 12:36:18. 0000880800 PostCmt 0
I. 08/16 12:36:18. 0000880800 Txn 1463706779 0 1463706779
I. 08/16 12:36:18. 0000880800 Allocation failed. Dbspace IQ_SYSTEM_MAIN is OUT OF SPACE
I. 08/16 12:36:19. 0000880800 Exception Thrown from s_blockmap.cxx:4195, Err# 0, tid 25 origtid 25
I. 08/16 12:36:19. 0000880800 O/S Err#: 0, ErrID: 2096 (s_nodbspaceexception); SQLCode: -1009170, SQLState: 'QSB66', Severity: 14
I. 08/16 12:36:19. 0000880800 [20223]: You have run out of space in IQ_SYSTEM_MAIN DBSpace.
I. 08/16 12:36:19. 0000880800 Rbck
I. 08/16 12:36:26. 0000880800
===== Thread Number 871852352 (IQ connID: 0000880800) =====
I. 08/16 12:36:26. 0000880800 pc: 0x2aab3c5f7406 pcstkwalk(stk_trace*, int, db_log*, hos_fd*)+0x36
I. 08/16 12:36:26. 0000880800 pc: 0x2aab3c5f7641 ucstkgentrace(int, int)+0x111
I. 08/16 12:36:26. 0000880800 pc: 0x3ea440eca0
I. 08/16 12:36:26. 0000880800 pc: 0x3ea440b019
I. 08/16 12:36:26. 0000880800 pc: 0x2aab3c3d74cf hos_condvar::Wait() const+0x2f
I. 08/16 12:36:26. 0000880800 pc: 0x2aab3c5e10fe hos_RecursRwLock::WrLock() const+0x6e
I. 08/16 12:36:26. 0000880800 pc: 0x2aab3bf58e55 db_CatalogTxnList::RollBack(unsigned int)+0x3c5
I. 08/16 12:36:26. 0000880800 pc: 0x2aab3bf5f3eb db_Catalog::RollBack(unsigned long long, unsigned int)+0x5b
I. 08/16 12:36:26. 0000880800 pc: 0x2aab3ce5ca52 st_txncb::Rollback()+0x1b2
I. 08/16 12:36:26. 0000880800 pc: 0x2aab3ce738fd st_txnMgr::Rollback()+0x12d
I. 08/16 12:36:26. 0000880800 pc: 0x2aab3cd87ae2 st_command::DoCmdThroughResourceGate()+0x82
I. 08/16 12:36:26. 0000880800 pc: 0x2aab3ce1e413 st_iqtxn::RollbackTxn(UIConnection*)+0x23
I. 08/16 12:36:26. 0000880800 pc: 0x2aab3c65c625 UIQTxn_RollbackTxn+0x15
I. 08/16 12:36:26. 0000880800 pc: 0x2aab3ce46fb6 st_SAIQInterfaceInfo::callFunction()+0x26
I. 08/16 12:36:26. 0000880800 pc: 0x2aab3ce46694 st_SAIQInterface::RunIQFunc(st_SAIQInterfaceInfo*)+0x184
I. 08/16 12:36:26. 0000880800 pc: 0x2aab3ce4795d st_SAIQInterface::Execute(int, void*, unsigned int (*)(void*, void*), void*, unsigned int)+0x20d
I. 08/16 12:36:26. 0000880800 pc: 0x2aab3c745aa8 saint_iqthresholdtxn::RollbackTxn(IConnection*)+0xb8
I. 08/16 12:36:26. 0000880800 pc: 0x2aaaab2a60c1 DB_Rollback_Chgs(Connection*)+0x1f1
I. 08/16 12:36:26. 0000880800 pc: 0x2aaaaadabd05 Connection::rollback_work_internal(unsigned int)+0x75
I. 08/16 12:36:26. 0000880800 pc: 0x2aaaaadac34b Connection::rollback_work(unsigned int)+0x4b
I. 08/16 12:36:26. 0000880800 pc: 0x2aaaaadac677 ri_commit(Connection*)+0x197
I. 08/16 12:36:26. 0000880800 pc: 0x2aaaaadac998 Connection::commit_work()+0x48
I. 08/16 12:36:26. 0000880800 pc: 0x2aaaaae67237 DoExecuteStmt(Connection*, a_stmt*, a_statement*, a_stmt_identifier*, unsigned short, unsigned int)+0x1547
I. 08/16 12:36:26. 0000880800 pc: 0x2aaaaae0210a exec_procedure_stmt(Connection*, a_context_ref*, a_statement*, unsigned int, unsigned int)+0x17a
I. 08/16 12:36:26. 0000880800 pc: 0x2aaaaae0385f run_procedure(a_context_ref*, dfm_CallInfo*, unsigned int, Connection*, unsigned int)+0x32f
I. 08/16 12:36:26. 0000880800 pc: 0x2aaaaae04447 call_procedure(Connection*, a_proc_def*, a_trigger_def*, an_expr_node*, a_procedure_arg*, unsigned int, a_db_cursor*, bool)+0xd7
I. 08/16 12:36:26. 0000880800 pc: 0x2aaaaae04a4e dbi_callprocedure(Connection*, a_call*, bool, unsigned int)+0x12e
I. 08/16 12:36:26. 0000880800 pc: 0x2aaaaae683d8 DoExecuteStmt(Connection*, a_stmt*, a_statement*, a_stmt_identifier*, unsigned short, unsigned int)+0x26e8
I. 08/16 12:36:26. 0000880800 pc: 0x2aaaaae6a01c db__execute(Connection*, an_sqlpres_receive*)+0x12c
I. 08/16 12:36:26. 0000880800 pc: 0x2aaaaae73df6 RequestProcedure::call()+0x946
I. 08/16 12:36:26. 0000880800 pc: 0x2aaaaad158ee Context::call(Procedure*, Context**)+0x4e
I. 08/16 12:36:26. 0000880800 pc: 0x2aaaaad159d6 Worker::call_on_stack(Procedure*)+0x56
I. 08/16 12:36:26. 0000880800 pc: 0x2aaaaae70fcd TopProcedure::call()+0x3d
I. 08/16 12:36:26. 0000880800 pc: 0x2aaaaad17689 Worker::spawn(Procedure*)+0x49
I. 08/16 12:36:26. 0000880800 pc: 0x2aaaaae71f3e EngStream::handle_ind(unsigned char, unsigned int)+0x13e
I. 08/16 12:36:26. 0000880800 pc: 0x2aaaaae75994 EngStream::execute()+0xcb4
I. 08/16 12:36:26. 0000880800 pc: 0x2aaaaae70ada RQBaseItem::do_work(Worker*)+0xa
I. 08/16 12:36:26. 0000880800 pc: 0x2aaaaae90a16 RequestQueue::worker_body()+0x66
I. 08/16 12:36:26. 0000880800 pc: 0x2aaaaae71356 request_task(void*)+0x46
I. 08/16 12:36:26. 0000880800 pc: 0x2aaaab2987bd run_task_body+0x2d
I. 08/16 12:36:26. 0000880800 pc: 0x2aaaaae91607 UnixTask::pre_body(void*)+0x77
I. 08/16 12:36:26. 0000880800 pc: 0x3ea440683d
I. 08/16 12:36:26. 0000880800 pc: 0x3ea3cd526d
sp_iqdbspace
fiale Usage is percent of TotalSize
if Usage >= 90 add new file to IQ_SYSTEM_MAIN
Central / Dbspaces / IQ_SYSTEM_MAIN / Contents - should be no user tables
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Johan,
I would make sure that you have no objects in IQ_SYSTEM_MAIN. A 20% utilization is normal. When you create IQ_SYSTEM_MAIN 20% of storage is reserved for internal structures like the freelist, TLV, etc.
There is no reason why IQ_SYSTEM_MAIN would grow that significantly from 21% to 100%. Or from 16gb to 80gb in absolute space terms.
System main can grow, but I've not seen it grow that much over a short period of time. Do you know how long it to for it to go from 21% to 100% full?
Can you also confirm whether or not there are objects in system main? And make sure that the default dbspace option for all users does not point to IQ_SYSTEM_MAIN, which is the default.
Mark
If they are not there, you can always run "ALTER DATABASE UPGRADE". This will put the procedures back in place.
The procedures, as Chris points out, are always there and visible to everyone. Depending on the procedure, you may not have rights to execute, but that is much different than the procedure not being found.
Mark
User | Count |
---|---|
85 | |
10 | |
10 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.