Navision - DB files placement

rixrix

Hello,
can you tell me what you think about this?
We are gonna to buy som new HDDs and will completely reinstall server ....

We will have 66 HDDs.
6 of them will be spares, so I have 60 disks available.
Server is HP DL 380 , 2xXeon (6core),72GB RAM, p812 controller, 16 int. HDDs, 2xD2700 external cages for 50 HDDs

I was thinking of this usage of them:
(no need to think about size, I need advice in placing data in meaning: more spindles=higher speed, better i/o perf)

4x (Raid 10) - OS, swap, export/import data
4x (Raid 10) - Tempdb data (12 files)
4x (Raid 10) - Tempdb log (1 file)
4x (Raid 1) - Backup files
12x (Raid 10) - Working DB LOG (1 file)
32x (Raid 10) - Working DB Data (12 files)

May I know your opinions?


Thank you

rixrix

Please move the topic to SQL Performance. Thank you

bbrown

why 12 data files on the one array?

I'd consider splitting of some drives for a second log file array. At some point someone will want to create a test DB. It would be nice to have a place to put the log.

bbrown

Thought I'd give you the general configuration of a similar size system we work with. By no means is this the only (or maybe not even the best) configuration. It's been in production for about 15 months and performance is quite good. But keep in mind that there has also been the need for DB and app tuning. Hardware can't do it all.

The server is a Dell M905, 4x3.12 GHZ (4 core), 192 GB RAM, 1x1 GB ethernet, 2x 4 GB FC. The server is hosting 2 SQL instances. Eah with max memory of 90 GB. One is for live NAV. The other is used as a NAV testing and training platform.

Disk are host on a EMC CX4-240 SAN. Connections are 4 GB FC. Arrrays are as follow:

2x (RAID 1) - OS, program files
4x (RAID 10) - Paging file
4x (RAID 10) - SQL System databases and logs including temp db and NAV primary data files
14x (RAID 10) - NAV Secondary data files 1
14x (RAID 10) - NAV Secondary data files 2
14x (RAID 10) - NAV Secondary data files 2
8x (RAID 10) - Transaction log - Live DB (dedicated)
4x (RAID 10) - Transaction logs for test/training databases
5x (RAID 0) - Backups
2x (RAID 1) - scratch space

All disk are also duplicated on a second identical server to which the live DB is mirrored.

rixrix

12 files , because it is recomendation (can be seen everywhere on the web) to use 1 file per processor (=core).
So 2 x 6 cores = 12

rixrix

bbrown:

Thank you for your answer.

What does "primary NAV files" contain ?
And why are they together with TempDB and only on 4-disks raid?

+ What number of files do you have alltogether for NAV DB?
+ What filegroups are u using? any special table distribution ?

thank you

P.S. We tried to optimize settings, NAV code, ... everything is pointing to HW, so we are gonny try that ....

David_Singleton

You are going about this completely wrong.

First you need to determine what the SQL database requirements are, what throughput do you need and why the system is slow. Simply saying "I have x number of drives, what should I do with them" is the wrong way.

In any case once you get to this number of spindles, you completely throw out the door the old idea of mixed raids and multiple files on different RAIDs, you need to move to intelligent controllers.

bbrown

rixrix wrote:

bbrown:

Thank you for your answer.

What does "primary NAV files" contain ?
And why are they together with TempDB and only on 4-disks raid?

+ What number of files do you have alltogether for NAV DB?
+ What filegroups are u using? any special table distribution ?

thank you

P.S. We tried to optimize settings, NAV code, ... everything is pointing to HW, so we are gonny try that ....

The primary NAV file contains mainly jsut the system catalog. This is the *.mdf file in the default NAV db configuration. This file. along with the system databases, sees very little activity. So sharing a disk is not an issue. The 4 disk array is hving no problems handlign the demand. I see no need to move to a larger array.

We are not using any special table distribution. "Data Filegroup 1" has 3 even size files with each placed on a separate RAID 10. Nothing overly special here.

Keep in mind that hardware is only a part of the performance package. Even with this oversized system, proper coding, tuning, and maintenance is still very important. While fast hardware can be very nice, it can also have the downside of hiding other problems. Put a bad piece of code on a fast enough box and nobody notices.

David_Singleton

bbrown wrote:

Put a bad piece of code on a fast enough box and nobody notices.

Hardware can typically fix about 20% of performance issues, the other 80% you have to fix the proper way.

rixrix

Hi,

As I said, we (better said, our implementing company) are optimizing (or at least trying to optimize) code "every day".
And we wil continue with that.

Our measurements and tracking problems points to our today's HW.
Especialy Storage subsystem is not able to manage some situations where we need to write/read data when a lot of users are using same tables... for ex. invoicing at the end of month. And it means a lot of blockings. It is normal but system is processing too long so transactions are interrupted and users have to do everything again and again ...

Now we use MSA1000. data,log are on Raid 10 separate spindles (together 18 Disks - others are used for backup and different data)
And it's bloody slow .... not all the time , but very often

So we are gonna to buy better one.

I will have available what I wrote and now I need advice how many LUNs to create, what Raid is best to use on each of them, how many spindles to use for each of them.....
I have some plan, but will be glad if someone with experiences will correct me

Thank you

rixrix

bbrown:

So it is better to distribute files on different LUNs , for eample 3 Raid Arrays , each with 14 disks)
then to put it on one big Raid 10 which consists of 42 disks?

I thought it is better 1 big array (in meaning of many spindles)

P.S. TestDB is on another server, no need to make place for it here ...

bbrown

David Singleton wrote:

bbrown wrote:

Put a bad piece of code on a fast enough box and nobody notices.

Hardware can typically fix about 20% of performance issues, the other 80% you have to fix the proper way.

That's probably a fair number but individual issues depend on the cause of the problem. Systems will fix 100% of some problems and 0% of others. Note that I said "systems" and not "hardware". It's not only the hardware selected and how configured, but also how it is maintained and managed. Not much different from the DB itself.

bbrown

rixrix wrote:

bbrown:

So it is better to distribute files on different LUNs , for eample 3 Raid Arrays , each with 14 disks)
then to put it on one big Raid 10 which consists of 42 disks?

I thought it is better 1 big array (in meaning of many spindles)

P.S. TestDB is on another server, no need to make place for it here ...

(response to your private message)

The 3 arrays vs. 1 is just the configuration I happened to choose. The DB was already split on the old server so I decided to continue it. In terms of which config is faster, well that could be a whole new thread discussion. With the multilple files (same filegroup) SQL will strip data across the files. This helps to balance the load across them

The arrays contain nothing but SQL data files. Besides the live DB there is a testing and devlopment DB. But these see only minimal activity.

rixrix

yes, exactly.
If using multiple files, DB data are split among them. That's good and wanted.

Now I have to decide if to put them on 3 Raids (4+4+4 files) (one raid = all HDDs/3).
Or if to put all files on one big raid which consists of all HDDs.
For me it seems better to use 1 big raid, because the speed will be 3 times faster (theoretically)

I planned to make 12 partitions, each for one file, so when growing, there will be no fragmentation.

bbrown

What do you mean by "12 partitions"? Do you mean filegroups?

To be clear, the config I mention just happens to be the way we went. It seems to be working quite well for us. But I am not claming that this is the best or ultimate config. There could have easily been different options.

rixrix

By partitions I mean partitions :-)

I can make just 1 primary partition and put all files there ...

But when the files will be physically one after one and it happens that 1st file have to grow, physically it gets fragmented and data of 1st file will continue where is the free space (and it is after last file) ... etc


So I was thinking to put file on it's own partition so files will not be influenced by each other and will have enoug space for growing.... And there will be no need for physical defragmentation..

bbrown

The primary advantages of splitting to multiple data files is achieved by placing each on its own physically separate array.

rixrix

Are you sure?

If I have multiple arrays they will consists of SMALLER amount of spindles then one array made from all of them.
So if throughoutput of disk is bottleneck, the more HDDs I will use, the better. (by my opinion)

And if data are split among all files.. then I do not see advantage of more arrys ... System have to read/write almost always from/to all arrays .... and one transaction also affects all of them of course .... so why to split it?

I can see some advantage of multiple arrays only if tables will be placed in different filegroups and these were placed on different Arrays..

bbrown

Whether you have a single 1x42 or 3 1x14 arrays you still have 42 disk servicing your data. So following your train of thought there is no difference.

rixrix

But the data from all of 3 arrays will not be served with the speed they can be served from more spindles (although 1) array.

When I need to read something what is placed on all arrays (because I have files all over of them) I need to read "1st part" from first array , 2nd part from second array ....

And all arrays have smaller speed then the big one.

or not?

bbrown

Actually it's about the same. You either have 100% of the I/O serviced by 42 disk. Or 1/3rd of the I/O serviced by 14 disk. Which equals 100% of the I/O serviced by 42 disk (3x14=42).

Anyways with this many disk thsi issue is insignicant in a typical size NAV database. If you're having performance issues it's highly likely that the disk system is not the underlying problem.

BTW - how large is this DB? And what version?

rixrix

Hi, ... If Storage would not be a problem, then NAV is not suitable SW for us :-) (everything points to storage anyway)

We are on 5.01, but "just technically upgraded" from v4
DB is about 500 GB
150-200 concurent users

bbrown

The database I've been referring to is around the same size. It is version 5.00 SP1 using NAV 2009 SP1 clients. The user count is lower (about 100) but it is a fairly high transaction level. System generates aboot 1 million GL Entries per week.

What makes you say the problem is storage?

rixrix

1. We have about 250000 GL Entries per week (average of 3 years)
2. MS Experts and people from implementing company said the problem is there... + of course I was observing perf.monitor and other monit. SW for disk queues and other things... and when problems showed up, the dis wasn't ablu to write/read enough fast... every other parameters ws ok ...+ processor usage was 90-100% which is not normal.

Besides that we tried to optimize indexes (also with mr.Stryk and MS help) , we optimized the code (i m sure here can be a lot of done yet), etc

And also because we are out of 3y warranty we decided to invest into new server with new storage...

3. JUst for your info, now we are on:
HP DL 380 G5: 2xXeon 2 GHZ, 32GB RAM, MSSQL2005. The Working DB was on MSA1000 (8 disk Raid10) + Log on it's own disks .... But it was terrible, so we moved the db to just 4 Raid 10 internal disks.... And it's better.... but I am sure it is not enough It was just short time solution

As you can see it is very lowend config and can't be compared to yours

bbrown

I'm not saying don't upgrade your hardware. Many of the reasons you mention are not much different from why our site upgraded their hardware. Just don't go forward thinking this will solve all your issues. You will be dissappointed.

rixrix

I do not think it will solve everything , I hope :-)

Of course, it will not solve everything (it is still low end config) but we will at least know that the the problems which lasted are caused by something else.

But I hope , main problems will be gone

And every storage config will be better then todays one... I am jus trying to do my best now, because when installed, it will be hard to move with anything ...

bbrown

I'd have to agree with David's number here. I think you'll be lucky of this resolves more then 20% of your problems. it's not so much your expectations but rather the expectations of senior management (the ones writing the checks) unless that is you of course. Not clear on your position in this whole thing.

rixrix

Of course this wasn't only my decision :-) And I do not write checks ...

Solution was brought by implementing company.. I just confirmed that I agree , because it really showed to storage/server problems ... trust me :-) Better said.. At least one of the problems is storage/server ....

And .... In few weeks nobody will have right to say that problem is in HW .... you know ... now programmers like to say, problem is in HW, we can't do anything with that ...blah, blah,.... And I do not know where the truth is... I learned some basics of NAV programming, but I do not know structure of our heavy modifications (and do not have devel. lic) so I can't do anything by myself ....

And 20%? That would be wonderful :-)

bbrown

I fully understand the "Hardware folks said this" vs. "Software folks said this" situation. Over my career I have been on both sides of that fence. Much of it at the same time.

DenSter

Some food for thought:
You might conclude that because you are seeing high numbers of reads, it must be a storage problem. The root cause, however, might just be that the system is not using the right index, and reverts to an index scan for high frequency queries. Instead of neatly retrieving a low number of records, now it is reading through an entire table. Although the high number of reads causes a lot of traffic, which in turn causes i/o issues, implementing the right key could give you much better results.

You say you've done some index tuning, and I wonder what exactly has been done. On 4.0 SP3 it was a very common approach to implement SQLIndex values that were different from the field order in the key itself, but on 5.0 SP1 and up that actually causes performance problems. If you have implemented a lot of SQLIndex values in your keys (especially in larger tables), you may have a big problem with keys.

The trick is finding where this happens of course. I just wanted to point out that it's not always very clear what is the problem.

davmac1

Your post is missing so much useful information, you will get only very generic responses.
What version of SQL Server?
What version of NAV executables and objects
32 bit or 64 bit SQL Server
RAM on the SQL Server
All direct connect clients or are you using remote desktop
Locking problems.
Navision locks tables when you post to the G/L Entry table - how much posting is going on? Has it been optimized? Any customizations to streamline posting?