The approach of offloading historical data into a separate database with
its own DASD, separating operational (real-time) data and processes from
ad-hoc business reporting processes, is a time proven optimization.

Timestamp all rows in the operational database, and migrate them nightly
from the operational database to the ad-hoc database, and remove them
from the operational database on successful completion. Determine what
maximum age records you want to keep in the operational database, and
possibly allow for master record status (for example migrate all rows
more than three days old where the patient's current case is closed).

>From experience, you will want a reverse migration for single sets of

rows (for example, if changes need to be made to a patient's case
record).

To the FB Architects: this provides a scenario where being able to
designate file systems for holding specific tables, indexes, etc could
offer a performance tuning option in a heavily used system. I know that
it is counter to the self-tuning paradigm and intent of the engine to
require this level of intervention by the user, but ... does Firebird
gather sufficient statistical information to identify which tables and
indexes might benefit by separating their file systems?

On Sun, 2005-10-30 at 01:02 +0000, Adam wrote:
> --- In firebird-support@yahoogroups.com, Ali Gökçen <alig@e...> wrote:
> >
> > Hi Adam, Alexandre,
> > thank you very much for your help.
> > I know how CS works, it doesnt help to my requirement,
> > it has much more cost than my delay operation in details.
> > we have no problem with CPU load. it is almost %60 in idle.
> > The problem is on DISK I/O. especially if you have too much
> > table, relations, indexes.
> > in shortly bigger data, more than cachce resources.
> >
> > Let me give you my scenario,
> > We are working for goverment hospitals.
> > As you can guess they are(our users) officer and work only in rush
> > ours.
> > Patients receptions, actions and lab devices communication shouldn't
> > go in a lag.
> > Invoice,statistical and managament related services not so much time-
> > critical.
>
> For invoicing, could you not run the reports on the prior days backup
> on a different machine? This approach can work well, it keeps the real
> database server able to handle real time information better.
>
>
> > it is not important if their reports or browsings fast or
> > not so fast. Also they read nearly whole database to get a report.
>
> Why for invoicing? Does it have any useful index to limit the range? I
> would assume it would only need to look at the last X days on records.
>
> > You know FB works on DISC file.
>
> One possibility (note: untested / theoretical) depending on your
> database size may be something I read about the other day (comment
> posted in firebird-general). It is a little chip that allows you to
> hook up some RAM into a SATA slot. To the OS, the slot looks like
> another hard drive. The chip has an inbuilt battery to keep the RAM
> running for a few hours if there is a power failure etc. So providing
> your database can fit into an affordable amount of memory, it may be
> an option. Of course you would need to test it before putting it into
> production. Unlike normal RAM disks, it is not an OS level trick, so
> if the OS goes wacky, you have not lost data, and the battery backup
> is what really makes it worth considering. You can also put several
> into a RAID. Gigabyte I-RAM, google it. Again, to reiterate, no-one
> has responded to that thread in firebird-general yet, and I haven't
> personally attempted it, but if it works then you know seek time in
> RAM is 1000 times faster than disk.
>
> > At CPU level OS makes a good time sharing for proccess/threads,
> > so i thnk my spin counter doesn't effect other users so much.
> > I made a plan to spread DISC I/O requests in time, with some
> > conditions like, while ( not current_time between '08:00'
> > and '17:00' do spin..
> > You know select doesn't lock any recource in FB.
> > So that, non-time-critical users will get slow respons and understand
> > how heavy their task. At this time our FB works at maximum speed and
> > they like to play with data results.
> > That was all.
>
>
> > Writing a UDF to force the thread to sleep was best idea.
>
> Note: Anns comments regarding releasing of read lock. It is not the
> best idea IMO, but if you are going to intentionally delay a thread,
> it would be better to not waste resources in doing so.
>
> Adam
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>