IQ_SYSTEM_MAIN usage

[Former Member]

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

[tayeb_hadjou · Accepted solution]

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.

[Former Member · Accepted solution]

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

[tayeb_hadjou · Accepted solution]

Hi Johan,

Another imporatant point to check is allocation consistency (leaked blocks), using sp_iqcheckdb with allocation mode.

Regards,

Tayeb.

[Former Member]

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

[Former Member]

Hi Oleg

Thanks but I now how to find the size and I can monitor it. The problem is that I want to troubleshoot the root cause of the problem

Johan

[Former Member]

TotalSize - 100%

X              - Usage

X = TotalSize * Usage / 100.0

?

in my db:

TotalSize = 15G

Usage = 21%

15 * 21 / 100 = 3.15 GB Used SIZE

[markmumy]

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

[Former Member]

Hi Mark

I am sure there are no user objects written to or in IQ_SYSTEM_MAIN. Unfortunately there were no monitoring of IQ_SYSTEM_MAIN in place to see how it grew over time

Hence my original question is what can I put in place to monitor and troubleshoot this problem.

regards

Johan

[c_baker]

Are you running any backups while loading data?  A backup will hold versions until it is complete.

Chris

[Former Member]

Hi Chris

Nope , The full backup runs on a Sunday and this happened on a Saturday

regards

Johan

[c_baker]

Any chance a specific load process (perhaps a newly implemented ETL job) is not using LOAD TABLE but row-by-row inserts and not committing until the end of the job?

What ETL jobs have been implemented/changed recently?

Chris

[jitendra_kumar01]

Hi Mark and all,

Can anyone help me to find the location of sp_iqdbspace, sp_iqdbspaceinfo, and sp_iqdbspaceobject.

I have installed SYbase IQ 16.0 but not able to find the correct location.

Mant thanks

Best regards

jitendra

[c_baker]

System and catalog sp's are installed in a database when created and are available for execution by the DBA.  Have you created a database?

Chris

[markmumy]

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