W2K or MySQL Defrag

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My server is running W2K and the primary application utilizes MySQL. I have noticed that my C: drive has been getting more and more fragmented. The D: drive is good but guess on which drive MySQL is on? I am defragmenting on a daily basis but the defrager spends about 2-3minutes and gives up on the C: drive. I'm not sure if this matters but I have a 3-drive Raid-5 system.

Can anyone tell me if this is a W2K or MySQL or other issue and how I might be able to correct the problem?

I appreciate the help.

Surge
 
How much free space is on the drive? As drive space declines fragmentation
will increase. Also, how the drive is formatted will play a big role. The
C drive is almost always formatted as 4k. If you extend a file beyond the
4k, then a new cluster is needed. If the cluster next to the original is
not available, then you will fragment.

If the defragger is giving up, it probably means that there is relatively
little space available on C (which is preventing the defrag). Most likely
MySQL is extending its files a little at a time and inducing the issue.

What can you do?

You could move the MySQL to a volume formatted with a larger cluster size.
This will actually improve perf for 2 reasons. One, fewer fragments and
two, there will be fewer seeks for a given query. I would recommend a 16k
cluster size if possible.

Alternatively, clean up the C drive prior to attempting the defrag. You
could also try a different defrag app.

Pat
 
Thank you for taking the time to respond.
I am posting the defrag analyzed data below:
Volume (C:):
Volume size = 12,291 MB
Cluster size = 512 bytes
Used space = 5,308 MB
Free space = 6,983 MB
Percent free space = 56 %

Volume fragmentation
Total fragmentation = 5 %
File fragmentation = 10 %
Free space fragmentation = 0 %

File fragmentation
Total files = 19,517
Average file size = 355 KB
Total fragmented files = 18
Total excess fragments = 6,614
Average fragments per file = 1.33

Pagefile fragmentation
Pagefile size = 1,536 MB
Total fragments = 1

Directory fragmentation
Total directories = 2,669
Fragmented directories = 1
Excess directory fragments = 1

Master File Table (MFT) fragmentation
Total MFT size = 37,754 KB
MFT record count = 22,635
Percent MFT in use = 59 %
Total MFT fragments = 3

Fragments File Size Most fragmented files
2 56 KB \WINNT\system32\dhcp\backup\DhcpCfg
2 5 KB \Program Files\APC\PowerChute Business Edition\agent\EventLog
5 1,013 KB \WINNT\security\logs\winlogon.log
3 12 KB \WINNT\system32\config\software.LOG
2 1 KB \WINNT\system32\config\default.LOG
4 1 KB \WINNT\system32\config\SECURITY.LOG
2 608 KB \WINNT\security\logs\scepol.log
2 1,083 KB \WINNT\ShellIconCache
2 7 KB \WINNT\system32\dhcp\DhcpSrvLog.Thu
2 19,003 KB \Program Files\TapeWare\database\TW6XXINS.TWD
2 1,984 KB \Program Files\TapeWare\database\TW6XXMED.TWD
2 86 KB \WINNT\tracing\PPP.LOG
11 8 KB \Documents and Settings\xxxxxxx\NTUSER.DAT.LOG
2 25,417 KB \Program Files\xxxx\System Backup\Registry\03_31_04.reg
25 2,064 KB \WINNT\Debug\NtFrs_0005.log
3 32 KB \Documents and Settings\xxxxxxxx\Local Settings\Temp\MMC18.tmp
2 1,032 KB \WINNT\system32\dhcp\backup\Jet\new\dhcp.mdb
 
Thank you for taking the time to help me out.
I am posting the defrag analyzed information below:
Volume (C:):
Volume size = 12,291 MB
Cluster size = 512 bytes
Used space = 5,308 MB
Free space = 6,983 MB
Percent free space = 56 %

Volume fragmentation
Total fragmentation = 5 %
File fragmentation = 10 %
Free space fragmentation = 0 %

File fragmentation
Total files = 19,517
Average file size = 355 KB
Total fragmented files = 18
Total excess fragments = 6,614
Average fragments per file = 1.33

Pagefile fragmentation
Pagefile size = 1,536 MB
Total fragments = 1

Directory fragmentation
Total directories = 2,669
Fragmented directories = 1
Excess directory fragments = 1

Master File Table (MFT) fragmentation
Total MFT size = 37,754 KB
MFT record count = 22,635
Percent MFT in use = 59 %
Total MFT fragments = 3

--------------------------------------------------------------------------------
Fragments File Size Most fragmented files
2 56 KB \WINNT\system32\dhcp\backup\DhcpCfg
2 5 KB \Program Files\APC\PowerChute Business Edition\agent\EventLog
5 1,013 KB \WINNT\security\logs\winlogon.log
3 12 KB \WINNT\system32\config\software.LOG
2 1 KB \WINNT\system32\config\default.LOG
4 1 KB \WINNT\system32\config\SECURITY.LOG
2 608 KB \WINNT\security\logs\scepol.log
2 1,083 KB \WINNT\ShellIconCache
2 7 KB \WINNT\system32\dhcp\DhcpSrvLog.Thu
2 19,003 KB \Program Files\TapeWare\database\TW6XXINS.TWD
2 1,984 KB \Program Files\TapeWare\database\TW6XXMED.TWD
2 86 KB \WINNT\tracing\PPP.LOG
11 8 KB \Documents and Settings\xxxx\NTUSER.DAT.LOG
2 25,417 KB \Program Files\xxxxx\System Backup\Registry\03_31_04.reg
25 2,064 KB \WINNT\Debug\NtFrs_0005.log
3 32 KB \Documents and Settings\xxxxxx\Local Settings\Temp\MMC18.tmp
2 1,032 KB \WINNT\system32\dhcp\backup\Jet\new\dhcp.mdb
 
Was this an upgrade install? 512byte cluster size is *tiny* and
inefficient. That's why even 1k files are fragmenting. If you had 4k
clusters, it would be impossible to fragment any file <4k (all of those .log
files for example). Basically what is happening is that every time a file
needs to extend 1byte beyond the 512byte cluster, a new cluster is having to
be allocated. The probability that the next cluster is free is relatively
low and you will fragment. This provides very little room for the files to
expand as needed. With 4k clusters, the 1st byte will cause a 4k
allocation, but no new allocations will occur until the 4k is filled up.

The average file size is pretty big (355k) which is partly due to the
pagefile skewing the average high. But even without that, the average file
size is probably close to 256k.

On the good news side, virtually none of the fragmented files are used very
often, if at all (most are only there for troubleshooting or .tmp files).
So, the fact that they are fragmented isn't going to hurt much. If the DB
files are fragmenting, then you will see a perf issue.

Since this is the System drive, there isn't much you can do at this point
other than work to mitigate the problem. If you have another volume
available, move files that are touched a lot (e.g. the DB files) there.
Make sure to format it with a larger cluster size (if it is primarily for
DB, use 16k which will help with lookup times too).

Pat
 
Nope. The cluster size is tied to the volume. So, you would have to
re-format at that level to get anything.

Pat
 
"If you had 4k clusters, it would be impossible to fragment any file "

This is grossly incorrect. Fragmentation is independent of cluster size.
Fragmentation will occur as long as there is insufficient contiguous free
space to create a new file contiguously or if a already contiguous files
"grows" and there is no free space immediately after the file for it to
"grow" into.

- Greg/Raxco Software
Microsoft MVP - Windows File System

Disclaimer: I work for Raxco Software, the maker of PerfectDisk - a
commercial defrag utility, as a systems engineer in the support department.

Want to email me? Delete ntloader.
 
If the files themselves are <4k then they will fit in a single cluster. The
..log files are <4k in size so this would prevent their fragmenting. Files
4k would still be possible to fragment.
Click to expand...


Pat
 
Hi, Greg.

Looks like you were a victim of a copy'n'paste that was one word too short.
;^}

Pat said:
....fragment any file <4k (all of those .log files...

That is, not just "any file", but "any file shorter than 4K" - which would
all fit into a single 4K cluster.

RC
--
R. C. White, CPA
San Marcos, TX
(e-mail address removed)
Microsoft Windows MVP
 
Oops - should teach me to pay attention more attention when I am reading...

- Greg/Raxco Software
Microsoft MVP - Windows File System

Want to email me? Delete ntloader.
 
Pat,
One final question. I've read some threads that indicate that applications such as Paragon Partition might be able to correct this problem. Would you have any experience or words of wisdom concerning such applications?

Thank you and regards,
Surge
 
I don't think that Paragon supports resizing the cluster size (though it can
resize the partition). The latest Partition Magic (v8.0) can, according to
their site, resize the clusters w/out data loss. I have never used Paragon
and have not used Partition Magic in quite a while. My only advice would be
(obviously) to back up anything you could not bear to lose prior to doing
any low level disk operations. Generally speaking, there is no rollback.


Pat
 
Prior to running any sort of utility that can change the cluster size, I
would agree with Pat and strongly suggest that you backup everything (and
verify that you can read the backup).

We have seen instances where the file system looks at little "wierd" after
using a tool to change the cluster size. While the end result is
technically "legal", some of the file system internals end up in a state
that could never be gotten to if the drive was initially formatted with a 4k
cluster size.

- Greg/Raxco Software
Microsoft MVP - Windows File System

Want to email me? Delete ntloader.
 
Back
Top