database network performance requirements
in [Oracle]
|
Prev: Advice on running Oracle with SGA > 20 GB
Next: Problem with 10G Physical Standby Database (archive gap). HELP :-)
From: Ryan_Hoffman on 1 Feb 2010 23:09 I'm doing some research on network performance requirements for 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!
From: Frank van Bortel on 3 Feb 2010 04:00 Ryan_Hoffman wrote: > I'm doing some research on network performance requirements for > 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! 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
From: vsevolod afanassiev on 3 Feb 2010 17:28 Requirements vary from application to application. Well-written 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
From: joel garry on 4 Feb 2010 12:07 On Feb 3, 2:28 pm, vsevolod afanassiev <vsevolod.afanass...(a)gmail.com> wrote: > Requirements vary from application to application. Well-written > 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 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/
From: Tim X on 4 Feb 2010 16:47
joel garry <joel-garry(a)home.com> writes: > On Feb 3, 2:28 pm, vsevolod afanassiev <vsevolod.afanass...(a)gmail.com> > wrote: >> Requirements vary from application to application. Well-written >> 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 > > 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. > 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 |