Want to split database, need help for best practices...

Charger

We're going to split our 3.6 database running on C-Side.....

We've been plagued with performance issues for some time. Using Tivoli as a diagnostic tool, we noticed that running certain processes in Navision yielded significant I/O and a fair # of page faults. Our database is only 25 gig with about 100 users, but as already stated, there seems to be signifact I/O....What's really interesting is that our slave processes almost always exceed their Working Set Peak, consistantly...I don't quite understand how working set peak is determined by Tivoli, but that's another question....

We have tried moving everything to a faster system, no improvement...if anything, degredation....

Then we tried seperating the service and the database, creating a share on a different machine and connecting to the database through that share.

In a real world scenario, we noticed a performance increase. Our next step is to try and split the database into 2 files and run them on seperate disks.....we are spitting the database by taking a backup, creating 2 new 15gig files on a new database, and restoring the database to these 2 files

So scenario would look as follows:
Server1 runs service , connects to DB on server 2 via a share
Server2 runs database, split across 2 files living on seperate physical RAID1 partitions (same controller).

Is this the right way to split the database? Do you guys think I will realize a gain? How does the working set results I've seen come into play? Is there a way to increase my working set?

thanks

Savatage

http://www.mibuso.com/forum/viewtopic.php?t=12497

From
http://www.mergetool.com/PerformanceReport-Compaq.pdf

Even tho it's kinda old - this pdf has some interesting info on benchmark tests. I'm sure it's still in the ballpark.

For example the performance scale

Split database from 1 to 2 disks Performance Increase additional %100
Split database from 2 to 3 disks Performance Increase additional %52
Split database from 3 to 4 disks Performance Increase additional %30.6
Split database from 4 to 5 disks Performance Increase additional %21
Split database from 5 to 6 disks Performance Increase additional %10.8

so I guess if you compare a 1 disk to a 6 disk system there is a %214.4 speed increase.

nunomaia

search in the forums about that issue, there are a lot of topics.

But with more disks you have more speed. Generally DB's consume I/O than cpu or other resources.

Even a Pentium III with 6 disks has a better performance than a new server only with a hard disk

themave

follow-up question on this topic,

I found that for every database part you create windows runs a slave.exe process to manage disk access I am assuming. so is it better to create a lot of database parts, so each gets a percentage of the process memory and thus needs to access the disk less often.

I have four mirrored disk, and a 24 gig database, will I get better performance having one 6 gig database part on each of the four disk, or if I have say for example have 3 - 2 gig database parts on each of the four separate disk mirrors.

kriki

themave wrote:

I have four mirrored disk, and a 24 gig database, will I get better performance having one 6 gig database part on each of the four disk, or if I have say for example have 3 - 2 gig database parts on each of the four separate disk mirrors.

I have also wondered about this one for a lot of time, but never had the time or hardware to test it.

2 others to keep in mind:
-the HIGHER the RPM, the faster.
-the LOWER the capacity, the faster.

DenSter

I have no personal experience with splitting native database files, but I was told it was faster to split up into more smaller files, then fewer bigger files. Instead of using 2x200 GB, it is better to use 4x100GB. The more disks, the more disk heads reading at the same time.

However the added performance gain by adding one disk goes down. So by going from 1 to 2 is twice as fast, and going from 2 to 4 is less than twice as fast.

Get some help from someone who has actually done it is my advice. Don't accept your solution center sending someone who 'will figure it out with you', insist you get help from an experienced person.

Charger

when I'm spreading database files across spindles, can I simply use one logical partition ( 4 mirrored drives in a RAID 1+0 ) or do the logical partitions need to be seperated as well? ( 4 mirrored drives, 4 RAID 1 partitions)

Anyone know why Tivoli continues to warn me about the working set being exceeded?

nunomaia

it's better to use 4 mirrored drives, 4 RAID 1 partitions

Charger

Thanks Nuno, BTW, I'm Portuguese too - well an immigrant anyway 'Borges'

Anyone try mirroring partitions on a SAN?

nunomaia

I never used mirroring partitions.
Microsoft has a document called Microsoft Dynamics NAV 4.0 Hardware Guide; it’s far from perfect but gives some info.

There are other ways to optimize Navision, search this forum for that issue.


Glad to know that in every corner there is a Portuguese.

cunny

Hi Charger,

Once I have read the document about how to split one database to several files and maybe what we should be aware are the things like below,

1) Using commit cache Tools-->Options-->Commit cache=YES
2) Do not share the database files on drives that "share" through the network programme.
3) The value of "Database Used(KB)" field should not eceed 80% (General rule to follow).
4) The size of each part should be same(I mean it's better).

Good Luck,

Cunny