IQ 15, iq_system_main full - stops clearing itself at 00:00 daily

[Former Member]

Hey everyone,

We have a strange issue on test system where our iq_system_main gets full every 24-48h, this timeframe depends when we do restart of the database.

Behavior:

• After restart database runs 100% normally which means that iq_system_main usage stays around 21% until day changes at 00:00.
• When clock hits 00:00, iq_system_main is not being cleared anymore no matter when we did the restart.

Actions taken:

• sp_iqconnections has been monitored and logged - we couldn't see anything special here
• sp_iqdbspace logged and monitored - here we can see when it stops to clear the iq_system_main
• sp_iqtransactions logged and monitored - after day changes we can see that many transactions stay in applied state for very long time.

Example from our iq_system_main logging - behavior is same if even if we restart at 5 am, it runs fine until 00:00:

16-09-28 08:13 40

16-09-28 07:43 39

16-09-28 07:12 38

16-09-28 06:42 37

16-09-28 06:12 36

16-09-28 05:42 35

16-09-28 05:12 33

16-09-28 04:42 32

16-09-28 04:12 30

16-09-28 03:42 29

16-09-28 03:12 28

16-09-28 02:42 26

16-09-28 02:12 25

16-09-28 01:42 24

16-09-28 01:12 23

16-09-28 00:42 23

16-09-28 00:12 22

16-09-27 23:42 21

16-09-27 23:12 21

16-09-27 22:42 21

16-09-27 22:12 21

16-09-27 21:42 21

16-09-27 21:12 21

16-09-27 20:42 21

16-09-27 20:12 22

16-09-27 19:42 21

16-09-27 19:12 21

-Restart happened here-

Any idea what is happening? Additional logs are available if needed.

Regards,

Nag

[markmumy]

This is certainly an odd one.

What does sp_iqdbspace show in the BlkTypes column?  When you say space is increase, what space type?  Is it user data?  System data?  Checkpoints?  Versions?  Having that output would be helpful.

What jobs get run at midnight, or close to that, every night?  If versions are staying applied, there is likely something that has an open transaction and thus holding versions.  Backups?  Checkdb?

Is this simplex or multiplex?

Can you post the files you collected?  That will certainly help track things down.

Mark

[Former Member]

Thanks for the response Mark.

With more monitoring we noticed that at 23:55, it tries to access the below files and then the problem starts to happen (i.e. increase in iq_system_main space).

$HOME/.sqlanywhere11/diagnostics/sadiags.xml

$HOME/.sqlanywhere11/diagnostics/iqdiags.xml

$HOME/.sqlanywhere11/sasrv.ini

and also for some reason we cannot read these files when database is running or during shutdown.

#: cat sadiags.xml

^C

#: file sadiags.xml

^C

<Observe that this cat command or file command simply hangs here. Only way to come out of this is to press Control C to kill it>

Database processes seem to be hanging and struck when accessing these files.

This is causing (a) increase in iq_system_main space while the database is running (b) database process to hang during shutdown “only”.

Please note that when database processes are offline then I am able to read these files and proper data is visible; so these files are not corrupt!

It is multiplex config but running on single server instance only.

Versioning is working OK and being cleaned up + there is no additional objects in iq_system_main.

sp_iqdbspace output before midnight (i.e. before it starts to grow). Running at 16-10-02 22:12,

DBSpaceName                                                                                                                      DBSpaceType Writable Online Usage TotalSize Reserve NumFiles    NumRWFiles  StripingON StripeSize

         BlkTypes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

         OkToDrop

-------------------------------------------------------------------------------------------------------------------------------- ----------- -------- ------

IQ_SYSTEM_MAIN                                                                                                                   MAIN        T        T      21    200G      0B                1           1 T          1K

         1H,1310656F,32D,128M,668B                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

         N

sp_iqdbspace output after midnight (i.e. after it starts to grow). Running at 16-10-03 01:42,

DBSpaceName                                                                                                                      DBSpaceType Writable Online Usage TotalSize Reserve NumFiles    NumRWFiles  StripingON StripeSize

         BlkTypes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    

         OkToDrop

-------------------------------------------------------------------------------------------------------------------------------- ----------- -------- ------

IQ_SYSTEM_MAIN                                                                                                                   MAIN        T        T      26    200G      0B                1           1 T          1K

         1H,1310656F,32D,370573X,128M,540B                                                                                                                                                                                                                                                                                                                                                                                                                                                                           

         N

sp_iqversioninuse output before midnight, Running at 16-10-02 22:12,

VersionID            Server                                                                                                                           IQConnID             WasReported          MinKBRelease            MaxKBRelease

-------------------- --------------------------------------------------------------------------------------------------------------------------------

                    0 dwhdb                                                                                                                                          172619                    0                       0                       0

(1 row affected)

(return status = 0)

sp_iqversioninuse output after midnight. Running at 16-10-03 01:42,

VersionID            Server                                                                                                                           IQConnID             WasReported          MinKBRelease            MaxKBRelease

-------------------- --------------------------------------------------------------------------------------------------------------------------------

                    0 dwhdb                                                                                                                                          203343                    0                       0                       0

(1 row affected)

(return status = 0)

Regards,

Nag