database network performance requirements
Ryan_Hoffman
database applications. I was wondering if anyone in this group would
be willing to share their opinions regarding specific values that
should be used as targets (latency/delay, jitter, packet loss).
Various vendor documentation provides some guidelines (latency: Oracle
SYNC = ≤ 10 ms, SQL Clustering ≤ 100 ms; loss: Oracle On Demand ≤
0.1%). I was hoping to hear what database administrators look for
when purchasing wide area network services.
Thanks!
Frank van Bortel
Never heard any DBA being consulted before LAN and/or WAN
purchases.
DBA's usually learn to live with the "It's the database that's
performing badly!" accusations afterwards. By then, the seagull
known as interim manager or sales consultant is flown.
--
Regards, Frank van Bortel
Topposting in Usenet groups I regard as offensive - I will not reply
vsevolod afanassiev
application cache static data.
There are several network-related statistics, this is from Statspack
report for 9.2.0.8 database:
Statistic Total per Second
per Trans
--------------------------------- ------------------ --------------
------------
SQL*Net roundtrips to/from client 15,714,040
436.5 22.2
bytes received via SQL*Net from c 5,056,985,913
140,475.7 7,144.4
bytes sent via SQL*Net to client 3,173,523,378
88,155.9 4,483.5
joel garry
wrote:
A single application is the wrong level to determine this. The dba
presumably is responsible for allocuting all Oracle related network
usage. So, at a minimum, it would need to be statistics as above for
all applications current and contemplated, projected forward, plus the
likelihood of disaster or distributed usage. The latter means
archiving transport, and rman usage over the net (among other
possibilities). For example, to build a standby database you have to
figure how much data there is uncompressed (in case there happens to
be some bug that disallows the compression option), and how fast you
will need to rebuild such a beast from scratch should the archive gap
become untenable. If management's view is "no, we will never lose our
computer room due to the upstairs crapper overflowing," get an
experienced consultant in there.
Another way to get around penny-pinching network resources is to get a
video conferencing project, that means big pipes for everyone.
jg
--
@home.com is bogus.
That darn internet! http://www.signonsandiego.com/news/2010/feb/04/man-pleads-guilty-in-la-to-posting-film-online/
Tim X
This is very critical. Estimating growth is almost always udner
estimated. come up with a figure and then multiply by PI and you may be
getting closer to what reality is. I've frequently seen analysis that
has focused on the app performance and completely overlooked network
requirements for backups. If you expect to have, lets say, one full
backup per week and incremental backups each day, then you need to be
able to perform them within at least one 24 hour period - this means all
your databases AND other critical data, such as file servers, mail
servers etc. While many SANs and other products, including Oracle,
provide means to assist in getting a consistent snapshot of data for
backup, you usually need to transfer that data to a backup machine where
it is put onto tape etc. Often, that backup is either off site or the
tapes are stored off site for DR reasons. The critical point is that you
need to be able to do this all within a single backup period (often this
is 1 day, but for some sites/appps, it cold be more frequent). If you
cannot transfer all your data in a backup period, then things get really
complicated fast. The point to note is that this backup process usually
involves mroe than just your Oracle data AND it normally takes place at
the same time your other activities are occuring i.e. it is a mistake to
estimate network loads based solely on the requirements of the 'public'
applications. You need to add in the overheads from admin/maintenance
activities, such as backups.
then, be careful regarding how network vendors spin their
specifications. I came across one of the larger vendors who claimed a
switch they sell was capable of 10 Gbit sec speeds. However, on further
investigating it, it turned out this speed could only be achieved by
multiplexing 10 1Gbit connections and that the switch limited individual
connections to 1Gbit. So, if you needed 10Gbit between two machines that
passed through this switch, you had to have multiple NICs in each
machine! Even the reseller was not aware of this limitation and thought
the switch could do up to 10Gbit on a single connection.
I personally would strongly suggest getting in a network specialist. The
network vendors have really been into a lot of value adding in the last
few years. Switches are no longer just switches. They come with all
sorts of features, such as built-in anti-virus protection, switch level
authentication, deep and shallow packet inspection, etc. Its very easy
to shoot yourself in the foot, especially if you have a heterogeneious
network with equipment from different vendors.
> Another way to get around penny-pinching network resources is to get a
> video conferencing project, that means big pipes for everyone.
hehehe! Tell them they need not just video conferencing, but the full
video grid stuff (aka VC inspired by Star Trek's holodeck).
--
tcross (at) rapttech dot com dot au
Ryan_Hoffman
<snip>
> hehehe! Tell them they need not just video conferencing, but the full
> video grid stuff (aka VC inspired by Star Trek's holodeck).
>
> --
> tcross (at) rapttech dot com dot au- Hide quoted text -
>
> - Show quoted text -
Thanks for the response. Agreed, a latency figure for example is
dependant on how large of a pipe you've purchased specific to the
issue of getting your backup completed in the time you have allotted.
The larger the latency (typically distance between source and backup),
the larger the pipe you'll need to compensate. Loss plays a factor
here too, since TCP can provide recovery for a lossy connection; but
only so far before throughput is significantly degraded.
Can you comment on SYNC backup (realtime)? Is there a particular
latency and loss threshold you wouldn't want to exceed; since these
directly impact the application response for the client?
I've found a variety of marketing names the db vendors use for SYNC
and ASYNC backups. I'm curious if there are generic names DBA's use
for these functions? I was thinking 'hot standby' for SYNC and
perhaps 'WAN replication' for ASYNC.
Thanks!
hpuxrac
snip
> Can you comment on SYNC backup (realtime)? Is there a particular
> latency and loss threshold you wouldn't want to exceed; since these
> directly impact the application response for the client?
>
> I've found a variety of marketing names the db vendors use for SYNC
> and ASYNC backups. I'm curious if there are generic names DBA's use
> for these functions? I was thinking 'hot standby' for SYNC and
> perhaps 'WAN replication' for ASYNC.
You lost me completely here.
You backup an Oracle database using rman. Best backups are done to
disk since they go much faster than backups to tape and if you have to
use the backup recovery from disk versus tape is much faster.
Are you talking about replicating a database?
Synchronous replication can carry a severe performance penalty
especially for commit happy applications. Asynchronous replication
carries the risk of data loss but reduces the performance hit. Log
file sync is the oracle wait event that is especially useful when
looking at the relative performance hit of synch versus asynch
replication.
Tim X
> On Feb 6, 12:31 pm, Ryan_Hoffman <hoffman.r...@gmail.com> wrote:
>
> snip
>
>> Can you comment on SYNC backup (realtime)? Is there a particular
>> latency and loss threshold you wouldn't want to exceed; since these
>> directly impact the application response for the client?
>>
>> I've found a variety of marketing names the db vendors use for SYNC
>> and ASYNC backups. I'm curious if there are generic names DBA's use
>> for these functions? I was thinking 'hot standby' for SYNC and
>> perhaps 'WAN replication' for ASYNC.
>
> You lost me completely here.
>
> You backup an Oracle database using rman. Best backups are done to
> disk since they go much faster than backups to tape and if you have to
> use the backup recovery from disk versus tape is much faster.
>
Rman is ONE way of backing up an Oracle database, but its not the only
way and it may not necessarily be the best way, depending on Oracle
version. It would generally be the preferred way on nearly all current
Oracle versions.
I think what the OP was asking about was database agnostic/neutral
terminology rather than Oracle specific. If you need to work with a
bunch of network engineers, more than likely talking about RMAN is going
to be meaningless to them.
With respect to the OPs other questions, I'm not sure I can give an
answer. This is partly due to much of what your asking being dependent
on site specific variables. What latency is acceptable where you are may
not be acceptable where I am. The size of the databases, the type of
applicaiton and how fast your data changes, the costs of your local and
remote storage, types of storage available and its support for tiered
storage, snapshots etc all have a bearing on what you can do, what you
should do and what you can afford to do. there is no standard formula
that can be provided. Its a difficult question and one where I know
enough to know I don't know enough!
Based on experiences where I am at present, we have been seeing around
60% increase in data storage requirements per year (not just Oracle
DBs). We have had to implement tiered storage solution and differentiate
between the backup frequencies based on the storage tier i.e. more
frequent backups for the very dynamic data and less frequent backups for
the data on the slower more static storage tiers (actually, its not that
simple - the formula to determine backup frequency has to take factors
like risk impact, business impact, data value etc into account as well).
The backup system we implemented in 2004 only lasted half the projected
lifetime because it wasn't fast enough to handle the amount of data
being kbacked up. We could have extended this slightly by using more
incremental backups and compressed backups, but of course the downside
with doing tis is longer restores, which you need to consider from a DR
and business continuity perspective. Our failure was in adequately
predicting data growth. We estimated about a 30% growth per year, which
turned out to be about half what it really was. In our defense, much of
the growth was due to changes in business processes and new services
that we were not aware of at the time of estimate. The real failure was
in the projects that implemented the new services and changes not
incorporating backup into their planning.
To deal with things like latencies, temporary network outages etc and to
handle both backup and restore requirements, we rman backup to fairly
fast SAN storage, do snapshots of that backup onto slower local storage.
Move the snapshots to our remote backup system (10Bgit network
connection) onto local storage attached to a tape bac,backup system with
a tape jukebox with 8 high speed tape backups. Each week, tapes are
moved to a third off site location with a fireproof safe. The tape
jukebox can be increased in size and I expect, given current data
growth, will need to be before the system is totally replaced with
whatever is the better technology at the time.
More than likely, your best solution will not be found just working with
DBAs or just with network engineers. You need to find a solution working
with all thre areas, the dBAs, the network engineeers and someone whose
area of expertise is the low level management and administration of
backups. It is a rare individual who can be up to speed on all three
areas if the requirements are at all demanding/complex. the two things
I'd keep in mind is that backup is only half the story - a backup
solution is only as good as the restoration process it supports. No
point backing up f you cnnot restore. The second most important point
and the one which is ignored/overlooked more often than any other is
that you cannot be certain about your backups unless you do regular bare
metal restores. This is rarely done because of the time it takes and the
fact you need the necessary duplicated envrionments to do this without
impacting on core business, but it has to be the one most common point
of failure with backup systems.
Tim