MaxDB – Space oddities
Recently a customer came up with the assumption that there is space somehow missing from the database.
Although he deleted a whole lot of data via archiving the data area was still quite filled up.
To investigate he decided to check whether the information visible in DBM GUI are correct.
As some of you may know, DBM GUI simply runs dbm- and SQL-commands in the background to gather the information.
Via “Instance -> Show Console” these commands can be revealed.
Getting the data
To gather the filling level of the database the command in charge is “info_state“.
Running this command manually in dbmcli brings up the following information:
dbmcli on 760c>info state OK END Name | Value Data (KB) = 3248 Perm Data (KB) = 3136 Temp Data (KB) = 112 Data (Pages) = 406 Perm Data (Pages) = 392 Temp Data (Pages) = 14 Data (%) = 6 Perm Data (%) = 6 Temp Data (%) = 0 Log (KB) = 8 Log (Pages) = 1 Log (%) = 0 Sessions = 2 Sessions (%) = 10 Data Cache (%) = 96 Converter Cache (%) = 0 Data Max (KB) = 51184 Log Max (KB) = 22448 Data Max (Pages) = 6398 Log Max (Pages) = 2806 Sessions Max = 20 Database Full = No Connect Possible = Yes Command Monitoring = Off Database Monitoring = On Kernel Trace = Off Autosave = Off Bad Indexes = 0 Log Full = No Bad Devspaces = 0 Data Cache OMS (%) = 0 Data Cache OMS Log (%) = 0
If we investigate a bit deeper, we’ll find that this command actually is a predefined SQL command that queries a view called “SYSDBM”.”INFO_STATE” which is defined in the DBMVIEWS.py file as:
createReplaceView (session, 'INFO_STATE',""" ( "Data (KB)", "Perm Data (KB)", "Temp Data (KB)", "Data (Pages)", "Perm Data (Pages)", "Temp Data (Pages)", "Data (%)", "Perm Data (%)", "Temp Data (%)", "Log (KB)", "Log (Pages)", "Log (%)", "Sessions", "Sessions (%)", "Data Cache (%)", "Converter Cache (%)", "Data Max (KB)", "Log Max (KB)", "Data Max (Pages)", "Log Max (Pages)", "Sessions Max", "Database Full", "Connect Possible", "Command Monitoring", "Database Monitoring", "Kernel Trace", "Autosave", "Bad Indexes", "Log Full", "Bad Devspaces", "Data Cache OMS (%)", "Data Cache OMS Log (%)" ) AS SELECT A.USEDSIZE, A.USEDSIZE - A.USEDTEMPORARYSIZE, A.USEDTEMPORARYSIZE, FIXED(A.USEDSIZE/D.PAGESIZE_IN_KB), FIXED((A.USEDSIZE - A.USEDTEMPORARYSIZE)/D.PAGESIZE_IN_KB), FIXED(A.USEDTEMPORARYSIZE/D.PAGESIZE_IN_KB), FIXED(A.USEDSIZE/A.USABLESIZE*100, 3), FIXED((A.USEDSIZE - A.USEDTEMPORARYSIZE)/A.USABLESIZE*100, 3), FIXED(A.USEDTEMPORARYSIZE/A.USABLESIZE*100, 3), S.USED_LOG_PAGES*D.PAGESIZE_IN_KB, S.USED_LOG_PAGES, S.PCT_LOGUSED, D.ACTIVE_SESSIONS, FIXED(D.ACTIVE_SESSIONS/D.MAXUSERS*100, 3), D.DATACACHE_HITRATE, 0, A.USABLESIZE, S.LOG_PAGES*D.PAGESIZE_IN_KB, FIXED(A.USABLESIZE/D.PAGESIZE_IN_KB), S.LOG_PAGES, D.MAXUSERS, D.DATABASEFULL, D.CONNECTPOSSIBLE, D.DIAGMONITOR, D.MONITOR, D.KNLTRACE, D.AUTOSAVESTANDBY, D.BADINDEXES, D.LOGFULL, D.BADVOLUMES, D.DATA_OMS_HITRATE, D.LOG_OMS_HITRATE FROM SYSDD.DATASTATISTICS A, SYSDD.SERVERDB_STATS S, SYSDD.DBM_STATE D INTERNAL""")
Easy to see: the storage-related data are taken out of “SYSDD”.”DATASTATISTICS“.
As the SYSDD tables are reserved for internal access we can simply use the view “SYSINFO“.”DATASTATISTICS” which is available in the database catalogue.
(note: there are some internal schemas in MaxDB that are usually hidden from users. “SYSDD” and “SYSDBM” are just two of them.).
So let’s check the “DATASTATISTICS” on my test system:
select 'STAT' as source, USEDSIZE used_kb, round(usedsize/1024, 2) used_MB from datastatistics
SOURCE USED_KB USED_MB ------------------------ STAT 467208 456.26 =======================
So far nothing impressing – there are about 452 MB used up in the data area.
Check the free space accounting
Now the customer had his doubts and wanted to check against this value.
So the idea was (obviously):
Count all tables and indexes in the database and what is allocated for them and this should match the information from “DATASTATISTICS”.
The statement used looked like this:
select 'FILES' as source, sum(treeleavessize+treeindexsize+lobsize) as used_kb ,round(sum(treeleavessize+treeindexsize+lobsize)/1024,2) as used_MB from files
SOURCE USED_KB USED_MB -----------------------
FILES 469672 458.66
Comparing the two results shows quite some differences:
SOURCE USED_KB USED_MB ----------------------- STAT 467208 456.26 FILES 469672 458.66 ----------------------- DELTA -4224 -4,13 =======================
So where are the 4,13 MB in my system?
In the case of the customer, the difference was in the range of several hundred MB.
Where is my free space?
The answer is simple but a bit surprising.
It’s about how the FILES system table works.
For each internal FILE (or SEGMENTS for the Oracle folks that read this) there is one row in this table.
Anyhow, the column LOBSIZE is kind of special.
MaxDB stores the data of LONG/LOB columns in separate FILES.
So for each table with LONG/LOB columns we find one row for the table and one row for each LONG/LOB column in the FILES system table.
If the FILES.TYPE is ‘TABLE’ then the LOBSIZE is the sum of allocated space for all the dependent LONG/LOB columns of this table.
Basically, the value is internally created like this:
select f.fileid, sum(lf.treeindexsize+lf.treeleavessize) LOBSIZE from files f, files lf where f.fileid=lf.primaryfileid and f.type='TABLE' and lf.type ='SHORT COLUMN FILE' group by f.fileid
Getting the data the right way
So with the initial statement to check the used size, we actually counted the LONG/LOB files twice.
Therefore a good approximation of the used space would be this adapted version of the statement where we just exclude the FILE-entries for those LONG/LOB columns:
select 'STAT' as source, USEDSIZE used_kb, round(usedsize/1024, 2) used_MB from datastatistics UNION ALL select 'FILES' as source, sum(treeleavessize+treeindexsize+lobsize) as used_kb ,round(sum(treeleavessize+treeindexsize+lobsize)/1024,2) as used_MB from files where type != 'SHORT COLUMN FILE'
SOURCE USED_KB USED_MB ------------------------ STAT 467200 456.25 FILES 466728 455.79 ------------------------ DELTA -472 -0.47 ========================
As we see, much less deviation from the “DATASTATISTICS” now.
But still, there is half an MB off for which we can assume that these belong to the CONVERTER.
By checking “IOBUFFERCACHES” we can at least get a rough estimation of the lower limit for the size of the CONVERTER:
SELECT CONVERTERUSEDSIZE FROM "SYSINFO"."IOBUFFERCACHES"
CONVERTERUSEDSIZE (Pages used by the converter (KB)) ------------------
With this information, we can be pretty sure that there is no “deadwood” in this database.
‘Deadwood’ issues in MaxDB?
Should the deviation between the values from “DATASTATISTICS” and “FILES” be much larger, say several 100 MB as it looked initially like for the customer, then it may be time to run a CHECK DATA WITH UPDATE.
This will rebuild the converter and the free block management and release pages that had been left over by the garbage collector.
You may find messages like this one in the KNLDIAG after the CHECK DATA WITH UPDATE run:
[...] Converte The pageno perm (111553) on block 4/8251 is deleted Converte The pageno perm (111565) on block 2/4608 is deleted Converte The pageno perm (111568) on block 4/8211 is deleted Converte The pageno perm (111574) on block 5/1264 is deleted Converte The pageno perm (111576) on block 4/8207 is deleted Converte The pageno perm (111587) on block 4/8335 is deleted Converte The pageno perm (111602) on block 6/5702 is deleted Converte The pageno perm (111605) on block 5/1503 is deleted Converte The pageno perm (111617) on block 6/5791 is deleted Converte The pageno perm (111643) on block 4/8329 is deleted CHECK Check data finished successfully DATACACH Begin data cache shutdown DATACACH Released cache frames: 2392 DATACACH Finish data cache shutdown [...]
Of course, you may now ask: “Why does the garbage collector forget about those pages?”.
The garbage collector is a forgetful bastard…
The reasons for that can be various.
One of them is that currently, the garbage collector is not able to resume the cleanup of a file when it has been interrupted, say by a restart of the database.
This is of obviously not so nice and I’m sure that this will be changed in the future, but usually, it’s not such a big issue, as the database instance are not restarted every now and then.
I hope you liked this rather long and technical blog.