On our DB servers, the most fragmented files are the log files (DB log, AppServer log). They are also amongst the fastest growing files on the system, even with minimal info logged.
DB files can grow by large chunks (at least with type II storage areas), limiting fragmentation and the number of times the system looks for empty space.
Is there a way to acheive something similar with the log files (i.e. make them grow / reserve space by large chunks)?
No, there is no utility to pre-grow the log files. As they are written to a message at a time I'm sure they become very fragemented based on other allocations happening on the same disk. However, since they are not read as part of any OLTP operation, why do you see this fragmentation as a problem?
If it's filling up every small hole available, I will guess that it is causing fragmentation on other growing files, as well, that do not have any more nearby space to grow to (at the minimum, between all the log files themselves!).
If I have files A, B, C and D (log files or not) all growing by small chunks at the same time, I get a sequence on disk that looks like
ABCDABCDBABDCBDCBACBDBACBDBCADCBDABCABCDABADC
instead of
AAAAA BBBBBBBB AAAA CCCCCCCCC DDDDDDDD CCCC
I'd do an additional guess that while the OS is busy serving and managing the list of available disk fragments to 10 continuously growing log files (make that 15... er... maybe more like 20...), it is less availble to process DB requests.
Just guesses; if you tell me I'm wrong, I'll trust you.
(and we do have automated processes that scan the log files, although they are not writing to it)
Another approach is to locate the log files in locations that don't coexist with your real data. Of course since the database log file must exist with the.db file that means location is not as flexible as you might want but you could dedicate a slice of a disk to the .db and .lg file to avoid fragmentation affecting other parts of the database. I also wonder if there isn't an option at the file system level that might be able to help. In other words, just because the .lg file is growing one message at a time, the file itself could be made by the file system to reserve more space than is actually needed immediately. You'd want to be careful if such a setting exists as it might cause other files to grow at the same rate which could eat up you disk space quickly.
If running on Linux, it may also be possible to make the database.lg a symbolic link to a file in another (possibly dedicated) disk partition.
Mike
How big are your log files?
How fast do they grow?
Ranges from ~1 Mb to 577 Mb
Samples across the size spectrum:
Admin server log is 11Mb for 337 days (33 Kb / day).
Main AppServer log for broker is 28 Mb for 244 days. (117 Kb / day)
Main DB log is 200 Mb for 337 days. (607 Kb / day)
Main AppServer log for server is 577 Mb for 244 days. (2420 Kb / day)
(Logging settings for this largest log: Server logging level: Basic + Server logging entry types: ASPlumbing + -debugalert i.e. no code trace, but if there is an error, it writes the error message and the stack trace at that point)
What benefit do you see in allowing a log file to grow for 200-300+ days? Why not periodically archive it ... if you see value in that ... and truncate?
Wouldn't they still be daily creeping up any available hole still asking the exact same amount of daily resources to the OS for new fragment allocation?
The log files are just text files to which messages are appended as they
occur. They get chunks of space added in units defined by the allocation
fragment size of whatever filesystem you are using. It isn't possible to
preallocate space and overwrite it later, as we do for database files (which
are not text).
For some types of filesystems, you can specify a larger allocation fragment
size than the default (likely 1k). Doing that could lessen the
fragmentation but at cost -- file consume space in units of the allocation
fragment size and if you have many small files, more space will be wasted.
Other than that, the only other things I can think of are running a
defragmenter or periodically copying the files, deleting the original, and
renaming the copy to the original name.
I don't think a few fragmented log files is a serious problem anyhow (except
when the filesystem is close to full). The overall impact they have is
pretty small. You said yourself they are the only files with significant
fragmentation.
If they are regularly truncated, then they will remain small. They might be fragmented, but if they are small, so what.
Of course, I don't think it is a problem for them to be fragmented in the first place, but I question the value of keeping such large files. Chances are, if you need to look at a log, it will be soon after something happened. The circumstances in which you would want to look back for a year are extremely rare. So, why burden yourself with a huge file that you have to open to look at it at all?