Ingres Q & A
I have the following problem with my ingres log file:
I need to load 6000 rows per 1020 bytes of data from an ascii file
using the "COPY FROM " command.
Our ingres log file is configured as 32 Mbytes.
What can I do to avoid LOG FULL ABORT ?
I think that log file isn't small...
Here is our logstat:
======================Wed Mar 1 14:52:44 1995 Logging System Summary======================
Database add 792 Database removes 784
Transaction begins 46253 Transaction ends 46244
Log read i/o's 33610 Log write i/o's 33521
Log writes 153485 Log forces 32182
Log waits 33885 Log split waits 1105
Log free waits 54 Log stall waits 11
Log BCP waits 11 Logfull stall waits 0
Log group commit 26218 Log group count 26261
Check commit timer 503 Timer write 436
Inconsistent db 0 Kbytes written 120362
----Current log file header---------------------------------------------------------------
Block size: 8192 Block count: 4000 Buffer count: 4
CP interval: 800 Logfull interval: 3800 Abort interval: 3200
Last Transaction Id: 00002F2F75F124F9
Begin: <791639547:2751:72> CP: <791639547:2751:72> End: <791639547:2811:72>
Percentage of log file in use: 1
Journal Window: <0,0,0>..<0,0,0>
Dump Window: <0,0,0>..<0,0,0>
Status: ONLINE,ECPDONE
----List of active processes--------------------------------------------------------------
ID PID TYPE OPEN_DB WRITE FORCE WAIT BEGIN END
--------------------------------------------------------------------------------------
0001000A 000040C6 FCT 6 152595 11590 29377 46102 46095
0001000D 000040A0 ARCHIV 1 457 137 2272 139 138
00010010 00004096 MASTER 1 421 0 2225 1 0
----List of active databases--------------------------------------------------------------
Id: FFFF0001 Database: ($recovery,$ingres) Status: NOTDB
Tx_cnt: 7 Begin: 145 End: 138 Read: 0 Write: 878 Force: 287 Wait: 6455
Location: None
Journal Window: <0,0,0>..<0,0,0>
Start Backup Location: <0,0,0>
Id: 0115000E Database: (online,halkova) Status: JOURNAL,FAST_COMMIT
Tx_cnt: 2 Begin: 24 End: 22 Read: 0 Write: 3 Force: 0 Wait: 3
Location: /data/ingres/data/default/online
Journal Window: <0,0,0>..<0,0,0>
Start Backup Location: <0,0,0>
----List of active transactions-----------------------------------------------------------
Tx_id: 00010002 Tran_id: 00002F2F75F07053 Database: FFFF0001
Process: 0001000A Dis_tran_id: 0000000000000000 Session: 1009EB00
First: <0,0,0> Last: <0,0,0> Cp: <791639539,1451,84>
Write: 0 Split: 0 Force: 1 Wait: 0
Status: INACTIVE
User: <$write_behind>
Tx_id: 00010004 Tran_id: 00002F2F75F07052 Database: FFFF0001
Process: 0001000A Dis_tran_id: 0000000000000000 Session: 1009D580
First: <0,0,0> Last: <0,0,0> Cp: <791639539,1451,84>
Write: 0 Split: 0 Force: 4 Wait: 2
Status: INACTIVE
User: <$write_behind>
Tx_id: 00010006 Tran_id: 00002F2F75F07051 Database: FFFF0001
Process: 0001000A Dis_tran_id: 0000000000000000 Session: 1009C000
First: <0,0,0> Last: <0,0,0> Cp: <791639539,1451,84>
Write: 0 Split: 0 Force: 1 Wait: 0
Status: INACTIVE
User: <$write_behind>
Tx_id: 00010008 Tran_id: 00002F2F75F07050 Database: FFFF0001
Process: 0001000A Dis_tran_id: 0000000000000000 Session: 100993A0
First: <0,0,0> Last: <0,0,0> Cp: <791639539,1451,84>
Write: 0 Split: 0 Force: 141 Wait: 1955
Status: INACTIVE,WAIT
User: <$fast_commit_thread>
Tx_id: 0002000B Tran_id: 00002F2F75F0704F Database: FFFF0001
Process: 0001000D Dis_tran_id: 0000000000000000 Session: 10074040
First: <0,0,0> Last: <0,0,0> Cp: <791639539,1451,84>
Write: 0 Split: 0 Force: 137 Wait: 1951
Status: INACTIVE,WAIT
User: <$archiver>
Tx_id: 0001000E Tran_id: 00002F2F75F0704D Database: FFFF0001
Process: 00010010 Dis_tran_id: 0000000000000000 Session: 10074040
First: <791639547,2751,784> Last: <791639547,2752,84> Cp: <791639547,2684,72>
Write: 421 Split: 0 Force: 0 Wait: 2225
Status: INACTIVE,WAIT
User: <$recovery>
Tx_id: 16C80017 Tran_id: 00002F2F75F1240E Database: 0115000E
Process: 0001000A Dis_tran_id: 0000000000000000 Session: 102BA040
First: <0,0,0> Last: <0,0,0> Cp: <791639547,2751,72>
Write: 0 Split: 0 Force: 0 Wait: 0
Status: INACTIVE,PROTECT,JOURNAL
User:
Tx_id: 051A0019 Tran_id: 00002F2F75F12406 Database: 0115000E
Process: 0001000A Dis_tran_id: 0000000000000000 Session: 102BA040
First: <791639547,2775,72> Last: <791639547,2775,304> Cp: <791639547,2751,72>
Write: 1 Split: 0 Force: 0 Wait: 2
Status: INACTIVE
User: <$opendb>
Tx_id: 00010020 Tran_id: 00002F2F75F07054 Database: FFFF0001
Process: 0001000A Dis_tran_id: 0000000000000000 Session: 100A0080
First: <0,0,0> Last: <0,0,0> Cp: <791639539,1451,84>
Write: 0 Split: 0 Force: 3 Wait: 1
Status: INACTIVE
User: <$write_behind>
==========================================================================================
I hope some helps.
Regards,
--------------------------------------------------------------------------
RNDr. Katarina Novakova
Computing Center
University of P. J. Safarik
Park Angelinum 9
041 54 Kosice
Slovakia e-mail: novakova@kosice.upjs.sk
---------------------------------------------------------------------------
Katarina Novakova writes:
>I have the following problem with my ingres log file:
>I need to load 6000 rows per 1020 bytes of data from an ascii file
>using the "COPY FROM " command.
>Our ingres log file is configured as 32 Mbytes.
>What can I do to avoid LOG FULL ABORT ?
>I think that log file isn't small...
If this is an empty table, it will not get logged if you create the table as a
heap with no journaling etc, then do the copy. You can also simply say "SET
NOLOGGING" before you issue the COPY statement. This will prevent logging also.
Be aware that if there is any sort of system failure during the copy, you will
not be able to rollback the copy statements work. But if this is a new empty
table, all you have to do is drop the table and try again.
The log file may be filling on you because data is written to the log file from
the log buffers which are always transferred in amounts equal to the size of the
log buffer, regardless of how much actual data is in the log buffers. Your log
buffers are being transferred at an average of 28.7% full. So, over 2/3 of the
8K log buffer is being written to the log file empty, but it takes up space in
the log file. 28.7% is pretty low, but 8k is the smallest you can make the log
buffers. Are there a lot of concurrent transactions that are doing a lot commits
all the time (a commit will cause the log buffers to get flushed.)?
FYI:
log buffer % full = 100 * (Kbytes written / (log write i/os * block size in KB)
Incidentally, the log file here is over 1Gb, but we have a fairly large system
and lots of data. You may want to say, double your log file if you have to
process the amounts of data you indicated above.
Hope this helps!
=========================================================================
Monte Malenke Phone 303/770-7200
Raymond James Consulting, Inc. Fax 303/770-5452
Stanford Building #3 Email mmalenke@raymondjames.com
4582 S. Ulster St. Parkway, Suite 100
Denver, CO 80237
=========================================================================
Ingres Q & A
To William's Home Page
© William Yuan 2000
Email William