Is it better to have multiple databases or a single database.
Suppose if you have more that 1500 tables, what would you go for and why?
It depends.
Personally I prefer a single database whenever possible. I find it simpler to deal with.
1,500 tables is not, in itself, a reason why I would consider multiple databases.
I might split things up if I had a clear reason to -- perhaps there are significant functional differences between distinct groups of tables (for instance online processing vs archive data or maybe a subset of tables that is really all about data warehousing).
I find multiple without 2phase commit it OK except that you have so much more to manage, run etc. You also end up with lots more batch jobs (e.g. 1 * BIW per DB). Adding 2phase commit to mutil-DBs will massively slug your performance.
Do we also consider the size of DB.
One DB may grow to 1TB but two DB will 500 GB each.
Will it make any diffrence in
1) Time to start/stop.
2) Recovery time
3) Will it effect the time to query?
Since now -B will be shared by all types of queries.
4) Management of AI files.
Do we also consider the size of DB.
One DB may grow to 1TB but two DB will 500 GB each.
It depends on how well designed the database and the application are. In general if size makes a noticable difference then someone has done something wrong and splitting the database into two parts isn't going to improve that.
Most of these things can be expected to get slightly worse if you have two instead of one because all of the "overhead" and static resource consumption is being doubled.
(However, as has been noted above, 2-phase commit may have serious negative consequences if needed and implemented.)
Will it make any diffrence in
1) Time to start/stop.
No.
2) Recovery time
No, that is related to the transaction volume which won't change just because you have two databases instead of one.
3) Will it effect the time to query?
No. Your queries will still be processing the same amount of data.
Since now -B will be shared by all types of queries.
The effectiveness of -B will depend a great deal on how well designed the database is and on what sort of choices you make in regards to block size, storage areas assignments, rows per block and cluster size. Some of which depends on the version of Progress that you are running. But having one -B or two -B buffers probably isn't going to affect that in any significant way.
If you believe that you are suffering from "cache thrashing" you can always use private buffers to limit a processes impact (I've done that with great success in some circumstances).
) Management of AI files.
Gets more complicated.
IMHO the main indicator that your performance might benefit from splitting a database into two parts is if you have hard evidence of latch contention and you can show that the objects being contended for are in distinct tables that have already been properly configured in terms of block size and rows per block and isolated in either a type 2 storage area or in a dedicated type 1 storage area.
If you need to attempt to determine if this is a problem that you might suffer from you could try simulating your expected load take a look at this Load Simulation and this OE10 Benchmark
Working URLs:
http://www.greenfieldtech.com/articles/load_simulation.shtml
http://www.psdn.com/library/entry.jspa?externalID=207&categoryID=182
Splitting one database into two without such evidence (or without a functional reason as described previously) is, IMHO, premature, counter-productive and quite likely to be a big mistake that will be hard to undo.
Message was edited to insert working URLs by:
Tom Bascom
That really answers most of my queries.
But we are not planning to split the database.
We are planning to combine two databases into one single database.
Ok, good. The simulation stuff might still be useful.
in that case i would use multiple database. becouse i dont want to make the schema file mega! and there is no reason to keep 1500 tables in 1 Database. much better to use multiple wording!