Re: Thoughts on Logical Log use requested

Colin Dawson wrote:
Ok, here's some information as requested by TBP & OTC,

The onstat -l was taken today (unable to run cmd on Sun), the list of log times for Sunday (yesterday) follow that.

Choosing one of the logs at random (No. 160439) it filled in 10 mins 3 secs and consists of (among others)

49215 HINSERT
51256 BEGIN
50990 COMMIT
56238 HUPDAT
20 BTMERGE
915 BTSPLIT
0 CHALLOC
0 CKPOINT (log filled between checkpoints - CKPTINTVL 900 secs)
266 ROLLBACK

onstat -l
Informix Dynamic Server Version 7.31.FD7 -- On-Line -- Up 5 days 21:17:50 -- 16303104 Kbytes

Physical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-1 1 64 566304 9695 58.41
phybegin physize phypos phyused %used
200035 1020000 1005727 9101 0.89

Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io
L-2 0 64 7276898 892936 838083 8.1 1.1
Subsystem numrecs Log Space used
OLDRSAM 7276898 435137336

address number flags uniqid begin size used %used
10dca21a0 1 U-B---- 160428 1100035 51197 51197 100.00
10dca21c0 2 U-B---- 160429 1200035 51197 51197 100.00
10dca21e0 3 U-B---- 160430 110c832 51197 51197 100.00
10dca2200 4 U-B---- 160431 120c832 51197 51197 100.00
10dca2220 5 U-B---- 160432 111902f 51197 51197 100.00
10dca2240 6 U-B---- 160433 121902f 51197 51197 100.00
10dca2260 7 U-B---- 160434 112582c 51197 51197 100.00
<snip>
Well, just interesting to look at what transaction mix you have :

51,256 Begins
and
51,256 (50,990 commits plus 266 rollbacks)

And it looks like roughly one insert and one update per transaction.

So, you HAVE to have 51,250 minute transactions in an unbuffered logging database i.e. about 100 pretty small transactions a second (100 * 60 * 10 = 60,000).

You are getting about 2 complete transactions per flush (8 records per page - "begin, insert, update, commit" * 2); if there were NO overhead (i.e. 0.9 waste of the 2nd page on each flush), you would consume :

Each transaction is using roughly 1k of log space
60,000 1k log usage transactions in 10 minutes = 60Mb

Seems like you are doing okay, bearing in mind the "absolute necessity to run with unbuffered logging".

Only suggestions really would be to consider :

1. Are these "minute" transactions a must? Are they really business transactions - or just application driven.

2. What is the requirement for "unbuffered logging"?

Food for thought rather than a panacea, but I think that you should look beyond IDS for your solution.
.

Relevant Pages

  • Re: [fw-wiz] RE: IDS (was: FW appliance comparison)
    ... To run the transactions they have a VERY large mainframe. ... logging I'm lucky to have gotten (since they got it for free ... >> With that much data, and 98% of it being useless, you kind have ... > will usually get stupid results. ...
    (Firewall-Wizards)
  • Re: Replication in databases
    ... It's physical logging, writing modified ... hamsterdb is good for embedded devices, ... concurrency and really bad support for transactions. ... months ago i started writing hamsterdb2, which has a high level of ...
    (comp.databases.theory)
  • Re: No Logging for DTS Batch
    ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) www.SQLDTS.com - The site for all your DTS needs. ... I have the import> working in DTS, but I DO NOT WANT IT TO LOG THE> TRANSACTIONS. ... > Alter table to add keys and clean data. ... > Where and how do I put in a commant to not log anything> in this transaction, yet maintain logging on any other> table in the database. ...
    (microsoft.public.sqlserver.dts)
  • Re: [fw-wiz] RE: IDS (was: FW appliance comparison)
    ... >> That's the dumbest argument against logging I've ever heard. ... We're a transactions ... But, on the bright side, our 2k IDS system did ...
    (Firewall-Wizards)
  • Logging
    ... I want to know if it it possible to disable a database from logging ... transactions. ... We have a database with 22 million records and an application that just runs ... but it keeps reporting it runs out of log space. ...
    (microsoft.public.sqlserver.server)