One Big Database vs Many small Database

[rguerrer]

Hi everyone;

I would like to know the opinion of the forum about this subject; when we have a big database (almost 85 GB). What is the best practice? Mantain this big database or divided in two or three database smaller. With a big database I have some problems to manage backups, transfer data, dump & load because the size, but if I use two or three databases, i dont want to decrease a performance for the limits of the server, because I had tu duplicate parameters.

We have almost 600 concurrent users.

Thanks all.

 

[RealHeavyDude]

To me 85 GB is not a large database when run on a decent server. You don't tell anything about versions and where this is running.

You are mentioning problems backing up and dumping and loading the database. What are exactly your problems?

IMHO there are two reasons as to why I prefer to have one database regardless how large it is:

• Database administration - managing one database is much easier and much more consistent than having that database split up into several chunks. Plus, it is not quite easy in a disaster recovery situation to ensure that these database are in sync.
• Transaction consistency - if you have transactions spawned across multiple databases - unless you use two phase commit, and I never heard of anyone using it - there is no way to guarantee transaction consistency as each of the participating databases is completely oblivious to the others. Meaning if the transaction is rolled back on one database for some reason it could be committed on the other.

Heavy Regards, RealHeavyDude.

 

[Cringer]

Have to agree with RHD. Our main database is over 300GB and we have no issues with backups or dumps and loads etc. Other than time taken of course. So if you're having issues then maybe you need to get to the bottom of those.

 

[cj_brandt]

I wouldn't attempt it unless you are on at least 10.1B03 and are already using Type II Storage Areas.

We were able to combine 10 databases into 1. The single database was around 600gb in size.

As RHD stated, DBA tasks are easier with 1 vs 10 and also 2 Phase Commit isn't needed.

I think the single BIW process vs 1 for each DB is a bottleneck - but others have argued.

Many of the progress utilities are limited to only running 1 at a time. With multiple datatabases, you can run a utility against each database, with 1 database you can only run 1. Progress is improving their utilities with each release though.

With multiple databases, you get multiple buffer pools. A bad query that does a table scan will not impact the buffer pools of the other databases.

We use hardware disk "snaps" to backup databases so we don't run probkup.

Even with the issues of managing a larger database, I still prefer 1 database to many.

 

[rguerrer]

Thanks a lot, my only issue are just the time in some manage tasks, but I am conscient that is because the size of DB, for example when i need to transfer backup or if I need to restore a backup it takes 2 or 3 hours, or when i need to do a full dump & load it takes one day. But the reason for this post was known if have a one database was a good practice and now I feel we are in a good way mantaining only one database.

for information:
We have OpeneEdge 10.0b SP05
Sun Solaris 10 SPARC
600 concurrent REMC

Thanks, have a good day

 

[Rob Fitzpatrick]

I think your life would be much happier, from a DB admin perspective, if you could upgrade your licenses to 10.2B. Better performance, old bugs are fixed, more options available to you, and more work you can do online. In particular, recent service packs in 10.2B make D&L, especially index rebuild, run a lot faster.

I don't know what kind of storage subsystem you have, but IMHO there is no way a D&L of an 85 GB DB should take a full day.

Have you moved to Type II storage areas? Do you have very large and very active tables in their own storage areas? And index areas separate from table areas? Have you moved all your storage objects out of the schema area? Have you tuned your BI configuration? Can you distribute your DB I/O workload across multiple disks, or at least separate BI from DB and both of those from AI and backups?

It sounds like either your configuration or your methodology could be optimized, or both. How are you doing your dumps - ASCII (Data Dictionary) or binary (proutil)? How are you doing your loads - Data Dictionary, proutil bulkload, or proutil binary load? Or are you using a combination of methods?

You may find you will lose your desire to split this single DB into multiple DBs (and thereby increase your administrative workload) if you can complete these maintenance tasks more efficiently and with less downtime.