1 / 18

Census Bureau

Census Bureau. System IPT Decennial Systems Database Modernization Support. DRIS Decennial Response Integration System. Date: 03/02/2007. Index. Benchmarking – Hardware level Old Machine New Machine Side By Side comparison-Host Level 2000 Fortran->Oracle Statistical Data

chen
Download Presentation

Census Bureau

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Census Bureau System IPT Decennial Systems Database Modernization Support DRIS Decennial Response Integration System Date: 03/02/2007

  2. Index • Benchmarking – Hardware level • Old Machine • New Machine • Side By Side comparison-Host Level • 2000 Fortran->Oracle • Statistical Data • Old Host – dscmotd1 • New Host – de101 • Performance while indexing • Old Host – dscmotd1 • New Host – de101 • Moving to New Host • Hypothetical Figures • 2010 tables layout • Performance & Tuning (P & T)

  3. Benchmarking-Hardware level Development dscmotd1 Production de101 Sun OS 5.8 CPU 2 1500 GHz 4G Memory 10 G swap Oracle 10g Sun OS 5.10 CPU 8 1500 GHz 64G Memory 64 G swap Oracle 10g

  4. Host name Hardware OS # CPUs Frequency MHz Memory Swap dscmotd1 Sun-Solaris 4800 5.8 2 150 4 G 10 G Database Version Oracle 10g Old Machine

  5. Host name Hardware OS # CPUs Frequency MHz Memory Swap de101 Sun-Solaris E6900 5.10 8 150 64 G 64 G Database Version Oracle 10g New Machine

  6. dscmotd1 de101 Hardware Sun Solaris 4800 Sun-Solaris E6900 OS 5.8 5.10 # CPUs 2 8 Memory 4 G 64 G Swap 10 G 64 G Database Oracle Oracle Database Version 10g 10g Side By Side Comparison-Host level

  7. 2000 Fortran->Oracle address status grfc operation Mapped from Old Fortran Flats to Oracle table layout

  8. Table Name State code State Name Data file Size # Of Records Time to load Time to create index Address 50 VT 95448246 325762 00:01:29.00 Address 06 CA 3852111141 13147137 00:58:53.03 00:28:29 Operation 50 VT 66455448 325762 00:02:06.07 Operation 06 CA 2682015948 13147137 01:23:50.95 00:26:36 Status 50 VT 40720250 325762 00:01:03.64 Status 06 CA 1643392125 13147137 00:46:48.97 00:48:01 Grfc 50 VT 2215722 18014 00:00:04.20 Grfc 06 CA 42119628 342436 00:00:57.58 00:00:32 Statistical Data-dscmotd1 – Old Host

  9. Table Name State code State Name Data file Size # Of Records Time to Load Time to create index Address 50 VT 95448246 325762 00:00:04.21 Address 06 CA 3852111141 13147137 00:02:56.80 00:01:20 Operation 50 VT 66455448 325762 00:00:04.80 Operation 06 CA 2682015948 13147137 00:03:17.03 00:01:16 Status 50 VT 40720250 325762 00:00:03.16 Status 06 CA 1643392125 13147137 00:02:21.78 00:01:39 Grfc 50 VT 2215722 18014 00:00:01.17 Grfc 06 CA 42119628 342436 00:00:03.15 00:00:02 Statistical Data-de101 – New Host

  10. Table Name State code State Name Time to load-Old Time to Load-New Time to create index-Old Time to create index-New Address 50 VT 00:01:29.00 00:00:04.21 Address 06 CA 00:58:53.03 00:02:56.80 00:28:29 00:01:20 Operation 50 VT 00:02:06.07 00:00:04.80 Operation 06 CA 01:23:50.95 00:03:17.03 00:26:36 00:01:16 Status 50 VT 00:01:03.64 00:00:03.16 Status 06 CA 00:46:48.97 00:02:21.78 00:48:01 00:01:39 Grfc 50 VT 00:00:04.20 00:00:01.17 Grfc 06 CA 00:00:57.58 00:00:03.15 00:00:32 00:00:02 Statistical Data-side by side compare

  11. Performance While Indexing Data-OLD TOP At the time of Indexing on address-dscmotd1 ====================================== last pid: 18264; load averages: 0.27, 0.18, 0.18 14:20:02 278 processes: 258 sleeping, 13 zombie, 5 stopped, 2 on cpu CPU states: 46.8% idle, 3.7% user, 2.0% kernel, 47.5% iowait, 0.0% swap Memory: 4096M real, 439M free, 3754M swap in use, 6513M swap free PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND 17694 patki001 11 48 0 842M 752M cpu/19 0:25 6.50% oracle 18182 patki001 1 58 0 2872K 1856K cpu/18 0:00 0.29% top 16904 oracle 11 58 0 361M 272M sleep 0:00 0.17% oracle 837 oracle 17 59 0 132M 42M sleep 60.4H 0.10% emagent 12306 oracle 26 28 10 205M 76M sleep 45:35 0.09% java 4031 oracle 6 0 0 26M 4888K sleep 36:10 0.09% tnslsnr 19855 root 1 58 0 7256K 3456K sleep 0:05 0.08% sshd2 11320 root 22 40 0 49M 11M sleep 106:27 0.07% java 3374 oracle 1 58 0 361M 277M sleep 1:07 0.07% oracle 1255 oracle 1 59 0 780M 693M sleep 2:31 0.05% oracle 18180 oracle 1 38 0 357M 261M sleep 0:00 0.05% oracle 17728 belgr001 1 58 0 5160K 4208K sleep 0:00 0.04% xterm 17824 oracle 1 58 0 7176K 4368K sleep 0:00 0.04% ssh2 911 oracle 15 49 0 781M 686M sleep 4:07 0.03% oracle 1979 oracle 1 58 0 780M 694M sleep 1:52 0.03% oracle

  12. Performance While Indexing Data-New TOP At the time of Indexing on address :de101 ===================================== last pid: 26499; load averages: 0.97, 0.73, 0.60 10:30:22 231 processes: 228 sleeping, 1 stopped, 2 on cpu CPU states: 87.4% idle, 8.3% user, 4.3% kernel, 0.0% iowait, 0.0% swap Memory: 64G real, 44G free, 18G swap in use, 58G swap free PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND 26488 patki001 14 0 0 9750M 2410M cpu/23 1:03 5.83% oracle 9874 oradb1 11 33 0 9739M 3123M sleep 3:01 0.51% oracle 9892 oradb1 11 59 0 9739M 2301M sleep 0:52 0.42% oracle 9894 oradb1 12 59 0 9739M 2302M sleep 0:46 0.30% oracle 9816 oradb1 1 59 0 2144M 819M sleep 35:33 0.05% oracle 10175 oradb1 1 59 0 640M 531M sleep 35:09 0.05% oracle 26489 patki001 1 59 0 3864K 3480K cpu/5 0:00 0.05% top 9886 oradb1 1 59 0 9728M 3124M sleep 40:33 0.04% oracle 10032 oradb1 1 59 0 1152M 595M sleep 36:03 0.04% oracle 26499 oradb1 1 59 0 639M 563M sleep 0:00 0.04% oracle 9956 oradb1 1 59 0 2144M 883M sleep 34:44 0.03% oracle 10171 oradb1 1 59 0 642M 534M sleep 28:54 0.03% oracle 10105 oradb1 1 59 0 1442M 1393M sleep 28:52 0.03% oracle 9952 oradb1 1 59 0 2146M 885M sleep 28:45 0.03% oracle 10028 oradb1 1 59 0 1154M 597M sleep 28:44 0.03% oracle 9882 oradb1 1 59 0 9730M 3125M sleep 28:04 0.03% oracle 10109 oradb1 1 59 0 1440M 1392M sleep 25:43 0.02% oracle

  13. Moving to New host • New Host is nearly 21 times faster for memory related processes • This will help in following operations • Data load • Indexing data • Simple Data Manipulation • Add • Edit/Update • Delete • Will Not help if scripts are not written properly (eg if used aggregate, sum, group by clauses)

  14. Hypothetical Figures • Without any changes, loading of 100% data should run close to following timing • address table • Old host = close to 8 hrs • New Host = close to 23 min • address table index • Old Host = close to 4 hrs • New Host = close to 15 min • All above is just changing from old host to new host.

  15. 2010 Tables layout address status grfc operation Mapped from Old Oracle Tables to New Tables layout

  16. Performance & Tuning • Hardware Level • Adding more power to m/c • Increasing CPUs • Speed • Memory • Database/Server Level • Indexing • Checking proper index usage • Moving indexes to separate table spaces • Partitioning • Partitioning tables • Server level P & T • memory • P & T parameters

  17. Performance & Tuning • Application Level • Changes to user queries/scripts • Usage of cursors • Where conditions • Network Level • Dataflow • Over the network v/s on the host • Jobs and their timings • Usage at the time of job executions

  18. THE END

More Related