Modifying large tables, Native DB

gensmann

Hi,

I'm looking for some advice on how to modify large tables in a Native DB.
I have tables with millions of records and adding new fields or new keys is not trivial.

When stopping the database server and accessing the several db files directly (db1.fdb+db2.fdb+ etc.), internal errors reg. timeouts can mostly be avoided. Another option is to export all data in the table, delete the data in the table, add fields, and then import the data. Any other ways?

Do you have any suggestions for creating new keys? Again accessing the db files as above without the server minimizes the internal errors. Could I use the same trick (import/export) to speed up the process?

All suggestions are welcome, but switching to SQL is not an option.

Thanks,

FDickschat

This all sounds very strange. Adding fields or keys to tables with millions of records does not pose a problem for the CSIDE server. Your internal errors sound like you have network problems and/or the server box is too small.

Which NAV version do you use? How is the server configured? Which error do you actually get?

Alex_Chow

gensmann wrote:

Hi,

I'm looking for some advice on how to modify large tables in a Native DB.
I have tables with millions of records and adding new fields or new keys is not trivial.

When stopping the database server and accessing the several db files directly (db1.fdb+db2.fdb+ etc.), internal errors reg. timeouts can mostly be avoided. Another option is to export all data in the table, delete the data in the table, add fields, and then import the data. Any other ways?

Do you have any suggestions for creating new keys? Again accessing the db files as above without the server minimizes the internal errors. Could I use the same trick (import/export) to speed up the process?

All suggestions are welcome, but switching to SQL is not an option.

Thanks,

The only think you can do is to buy the fastest hardware available. You can't do too much in optimization with the standard C/Side database.

gensmann

Hi Frank,

The error is 1355 in module 19, Err_DB_DBMNotReentrant.

The version is a 5.0SP1 runtime with a 54GB 2.00A database.
The tables contain about 10 mio. records. DB fill is about 70%.
TempFilePath has 70GB free space.
600 MB DBMS cache on the server. 3GHz dual core CPU, 3GB ram. 1 Gbit network.
Db server and the client I use to are on the same box.

The users (ab. 100 concurrent via terminal servers) do not experience any problems nor delays.

In the post http://www.mibuso.com/forum/viewtopic.php?f=23&t=8067 Timo Lässer has some good suggestions, but the only option I see is to try and increase the server cache (or replace the infrastructure and hope for the best).

Any suggestions?

Thanks,

Alex_Chow

gensmann wrote:

In the post http://www.mibuso.com/forum/viewtopic.php?f=23&t=8067 Timo Lässer has some good suggestions, but the only option I see is to try and increase the server cache (or replace the infrastructure and hope for the best).

You've already answered your own question. Again, C/Side database is limited on what can be done.

50GB database is not big. Why not just update the objects overnight?

David_Singleton

how many drives do you have and how are they configured? 10 million records isn't really a lot in C/SIDE database.

gensmann

I do update objects overnight, but unless I shut down the server and open the fdb's directly, I get the 19-1355 error.

There are three drives with one fdb on each. I once heard MS recommended no more that 2GB per fdb but now they have 3 x 18GB. Any thoughts on that?
There is some kind of raid controller in the box, but I'm not sure how it's set up.

Thanks,

FDickschat

Apart from the suggestions from Timo (and in general just get the fastest hardware available) I would try to go from 2.0a to 2.0b. It's a long time ago so I don't remember the details but I had very good experience with 2.0b. If this helps or not in your situation you will have to try.

What I would really like to see in your situation is whether a FusionIO Board helps (no David, no, don't shoot )

FDickschat

gensmann wrote:

I do update objects overnight, but unless I shut down the server and open the fdb's directly, I get the 19-1355 error.

There are three drives with one fdb on each. I once heard MS recommended no more that 2GB per fdb but now they have 3 x 18GB. Any thoughts on that?
There is some kind of raid controller in the box, but I'm not sure how it's set up.

Thanks,

Ahhh, we are getting to it. First thing: Find out how the Raid Controller is configured. Is this 3 drives? Or 1 drive with 3 LUNs? Maybe even a 3 drive Raid 5? Or maybe 6 drives Raid 10?
This is really the essential part. A good configured Raid system can support your system but if its configured wrong you will get into trouble.

FusionIO: This is the best performance hardware I had ever in my hands but verrryyyy expensive. Less expensive hardware will give you enough firepower for a NavDB.

kriki

Alex Chow wrote:

50GB database is not big.

For a DB on SQL it is small. For a DB on native it is quite big and it is getting uncomfortably close to 64GB. So I would also advice planning a migration to SQL Server.
Considering you still have 2.0 objects, you better consider a project from scratch and not just an upgrade+migration.

You should check which RAID the machine is using. If it is using RAID5 (why do I have a gut feeling you have?), you should implement a RAID1 for each db-file. (the more db-files and thus RAID1 disks you have, the better performance you will have.

Also increase the cache to as much as you can (I got 950MB once and even saw once a 1GB [the limit], both working without problems; in case the service doesn't start, try to restart the server to clean the memory:Seems NAV needs 1 big chunk of memory and doesn't like fragmented memory).

Also check if you have the commit-cache enabled.

David_Singleton

gensmann, unless you are willing to tell us what your hardware configuration is, its impossible really to help you.

gensmann

Thanks Frank, Alain, David,

I've asked the guys who set up the box for raid information, I will get back to you as soon as he gets back to me.

I'll try increasing the cache (commit cache is enabled).

I would love to build them a new system or even just migrate to SQL, but as they are awaiting the roll-out of the HQ's ERP it's a really hard sell.

Cheers,

gensmann

Hi again,

Finally got the info I needed on the RAID setup:

Six 136Gb disks setup in 3 x RAID 1 with one fdb on each.

Disk 0:
C: (System) Cap. 30Gb Free Space 8Gb
(DB1) Cap. 107Gb Free Space 72Gb
Disk 1:
E: (DB2) Cap. 136Gb Free Space 80Gb
Disk 2:
F: (DB3) Cap. 136Gb Free Space 68Gb

This should be OK, no? Then there's just the cache to increase.

Thanks,

kriki

That looks good.

BTW: I just remembered that I've had the error a few times when doing a lot of data changes like a modifyall. The trick I used to solve the problem was:removing keys in the offending tables. On a native DB, this is not so easy. You must check all the code to see where a key is used and maybe you need to change the code.

If it is something you need to do very few times (during off-hours!), you can save the object, remove all the keys you don't need. Do your thing, re-import the original object.

David_Singleton

(DB1) Cap. 107Gb Free Space 72Gb = 35gig DB
E: (DB2) Cap. 136Gb Free Space 80Gb = 56gig db
F: (DB3) Cap. 136Gb Free Space 68Gb = 68gig db

Total = 159 Gig

Doesn't sound good at all. From comments above I gather that 50 gig is used. So 17gig per disk, that's too much you want that number down to about half that, so 6 RAID arrays would be better. More importantly though you MUST have the same size db parts for each part, otherwise Navision will not stripe correctly and the effect is the same as having less spindles.

My bigger fear is that actually the db parts are the same size, and you are putting other stuff on the raids.

bbrown

gensmann wrote:

Disk 0:
C: (System) Cap. 30Gb Free Space 8Gb
(DB1) Cap. 107Gb Free Space 72Gb

Also note that drive is sharing the same physical disk as drive C:. Another configuration issue.

David_Singleton

bbrown wrote:

Also note that drive is sharing the same physical disk as drive C:. Another configuration issue.

I missed that!

So far this is not looking like a well configured server.

kriki

David Singleton wrote:

bbrown wrote:

Also note that drive is sharing the same physical disk as drive C:. Another configuration issue.

I missed that!

So far this is not looking like a well configured server.

I missed those too.
So: not good. I supposed it was 3 spindles with ONLY the NAV-DB on it.

gensmann

Thank you Alain, David, bbrown,

I'll investigate if more disks can be added and the 3 equal size fdbs replaced with 6 or 8 equal size fdbs while keeping the system files and other files away from any fdb-disks.

Cheers,

David_Singleton

gensmann wrote:

Thank you Alain, David, bbrown,

I'll investigate if more disks can be added and the 3 equal size fdbs replaced with 6 or 8 equal size fdbs while keeping the system files and other files away from any fdb-disks.

Cheers,

I am also curious as to why you have such different sized db files on each of the drives.

FDickschat

David Singleton wrote:

I am also curious as to why you have such different sized db files on each of the drives

I think there are just some additional files on the disks. The DB seems to be 3 x 18GB

gensmann wrote:

There are three drives with one fdb on each. I once heard MS recommended no more that 2GB per fdb but now they have 3 x 18GB.

Disk 0:
C: (System) Cap. 30Gb Free Space 8Gb
(DB1) Cap. 107Gb Free Space 72Gb => DB 18GB, Other Stuff 17GB
Disk 1:
E: (DB2) Cap. 136Gb Free Space 80Gb => DB 18GB, Other Stuff 38GB
Disk 2:
F: (DB3) Cap. 136Gb Free Space 68Gb => DB 18GB, Other Stuff 50GB

bbrown

Get rid of that other stuff. You want each array dedicated to each DB file. Dedicated means NOTHING else on those drives.

gensmann

Yes it is 3 equally sized files and yes, as I wrote, I'll see what can be done reg. hw and placement of files. Thanks.

JFGcanes

bbrown wrote:

Get rid of that other stuff. You want each array dedicated to each DB file. Dedicated means NOTHING else on those drives.

Why would it matter if there are other files on these drives? Suppose they are old backups or other rarely accessed files?

DenSter

Why would it be a big deal to dedicate a couple of drives or a couple of arrays for a specific purpose?

JFGcanes

DenSter wrote:

Why would it be a big deal to dedicate a couple of drives or a couple of arrays for a specific purpose?

Not trying to pick a fight. I just wanted to know why it would cause a performance issue.