Optimum Configuration for Page Size, NTFS Allocation Unit size and RAID Stripe size

Optimum Configuration for Page Size, NTFS Allocation Unit size and RAID Stripe size

Post by Scheldon Perei » Sat, 06 Apr 2002 15:03:47



Hello,
     I am currently building a PC that will serve primarily as a
database server. This is to replace an older machine. I have been
searching using Google to arrive at the optimum database/hardware
configuration.

I am using SQL Anywhere 5.5 running on Windows NT. The size of the
database is greater than 4G in size. Currently the page size is 1024
which was the default size. I plan on changing this to 4096.

The relevant specs of the PC are:

1G DDR-RAM
Adaptec 2100S SCSI RAID Card
Seagate Cheetah 18G 15K RPM SCSI HDD 8M Cache x 5
Windows NT 4.0 with SP6

The first thing I have realised is that I need to change the Database
page size to 4096. This is due to the size of the database.

Secondly, in regards to the NTFS partition, the allocation unit size
will be 4096. This is to match the database page size. My assumption
is that 1 to 1 ratio will be ideal.

One of the SCSI disks is the Boot disk. This leaves four SCSI disks to
setup in a RAID 0/1 array for the database & log file. Adaptec
recommends a 64KB Stripe size for the RAID 0 which means 32KB for each
disk in the array.

Therefore my optimum configuration is looking like this:

- Database Page size:        4096
- NTFS Allocation Unit size: 4096
- RAID 0 Stripe size:        64KB

Can anyone who has had experience in setting up a Sybase SQL Anywhere
database on a RAID array comment on my configuration?

This database server has regular inserts done during the day. The main
reason for optimising it is for the reports, i.e. retrieval of data
for daily reports. I have already optimised the SQL in the reports and
added indexes to the databases where appropriate. Now that I am
building a new PC server, I have the opportunity to optimise the
database page size/NTFS Cluster/RAID.

I almost forgot, I plan on allocating 512MB of RAM for the Database
cache.

Any comments are appreciated.

Thanks.

Scheldon Pereira

 
 
 

Optimum Configuration for Page Size, NTFS Allocation Unit size and RAID Stripe size

Post by Bechev » Sat, 06 Apr 2002 22:43:55


Scheldon

You may want to consider log mirroring onto one of your other disks for
recoverability.   Another option to consider is upgrading from SQL Anywhere 5.5
to ASA 8.  ASA 8 provides a number of optimizer enhancements as follow:
-Improved query processing and optimization can make many complex queries up to
six times faster
-New advanced index type for efficient large key indexing
-Bitmap table page lists enable faster table scans

 
 
 

Optimum Configuration for Page Size, NTFS Allocation Unit size and RAID Stripe size

Post by Robert Gl?ckne » Sun, 07 Apr 2002 15:42:07


Hi,

Quote:

> I am using SQL Anywhere 5.5 running on Windows NT. The size of the
> database is greater than 4G in size. Currently the page size is 1024
> which was the default size. I plan on changing this to 4096.

maybe you should consider to move to the 'big' server Adaptive Server
Enterprise?
there is an evaluation version available at Sybase - only 350MB to download

Quote:

> The relevant specs of the PC are:

> 1G DDR-RAM

unfortunately useless for ASA

Quote:> Adaptec 2100S SCSI RAID Card

For high end RAID there are better alternatives: DPT or - what I prefer -
ICP Vortex

Quote:> Seagate Cheetah 18G 15K RPM SCSI HDD 8M Cache x 5

nice disks ;-)

Quote:> Windows NT 4.0 with SP6

is end of life at July 1, 2002 - you should go with at least W2K. BTW, ASA
5.5 is dead too.

Quote:

> The first thing I have realised is that I need to change the Database
> page size to 4096. This is due to the size of the database.

good decision

Quote:

> Secondly, in regards to the NTFS partition, the allocation unit size
> will be 4096. This is to match the database page size. My assumption
> is that 1 to 1 ratio will be ideal.

I don't think that matters. 4096 is the default, but internally WNT/W2K
works with 32K chunks and all data will be cached though

Quote:

> One of the SCSI disks is the Boot disk. This leaves four SCSI disks to
> setup in a RAID 0/1 array for the database & log file. Adaptec
> recommends a 64KB Stripe size for the RAID 0 which means 32KB for each
> disk in the array.

that should be ok.

Quote:

> Can anyone who has had experience in setting up a Sybase SQL Anywhere
> database on a RAID array comment on my configuration?

to optimize write performance you will need to enable write caching. This is
risky without a battery backed cache. You shouldn't buy a controller without
that - and don't forget a good UPS.

Quote:> I almost forgot, I plan on allocating 512MB of RAM for the Database
> cache.

ASA at least up to V6 sees RAM as a expensive resource and seldom doesn't
use more than 64MB in the Windows versions. Sometimes it will run slower if
you configure too much memory...

If your reports make use of ORDER BY and GROUP BY statements, which utilizes
the TEMPDB, it may be interesting to install a big ramdrive (there are some
good ramdrives around which supports up to 2GB) and change the ASA temppath
to that drive. OTOH, ASA will crash badly if it requests more space than
available.

Greetings from Frth/Germany
R.Gl?ckner

 
 
 

Optimum Configuration for Page Size, NTFS Allocation Unit size and RAID Stripe size

Post by Anthony Mandi » Sun, 07 Apr 2002 18:36:06



> One of the SCSI disks is the Boot disk. This leaves four SCSI disks to
> setup in a RAID 0/1 array for the database & log file. Adaptec
> recommends a 64KB Stripe size for the RAID 0 which means 32KB for each
> disk in the array.

        Not quite. The stripe size is on a per disk basis, so its a
        stripe width of 64KB per disk.

-am     ? 2002

 
 
 

Optimum Configuration for Page Size, NTFS Allocation Unit size and RAID Stripe size

Post by Anthony Mandi » Sun, 07 Apr 2002 18:37:47



> > Windows NT 4.0 with SP6
> is end of life at July 1, 2002 - you should go with at least W2K.

        You mean its not dead yet? Looks like I was wrong about that
        smell then.

Quote:> BTW, ASA 5.5 is dead too.

        Nah, I'm certain the smell isn't coming from there.

-am     ? 2002

 
 
 

1. NTFS cluster size & RAID stripe size

Does anyone know of utilities that can be used on NT to find out (after the
fact) disk details like NTFS cluster size and RAID stripe size etc.
I would prefer things that do not require rebooting the system.

Dell PowerEdge 6300 with PERC RAID controller.

I would like this info to help me figure out what Oracle block sizes I can
use.
Thanks,
John

2. Any Linux HA solution? HowTo?

3. Optimum NTFS Allocation Size for SQL 2000

4. Where is INGRES Report Writer mannul?

5. optimum allocation unit size ?

6. issue with triggers and identitys. VERY STRANGE

7. RAID stride size (stripe) and Oracle block size

8. configure publishing and distribution

9. Allocation Unit Size for 25 Gb Raid Disk

10. RAID5: Optimal stripe size and cluster size?

11. DB Block Size Revisited + VxVm Stripe Size(newbie)

12. Best disk allocation unit size for SQL Server?

13. Allocation Unit Size on Disk