1 / 36

Informix Chat with the Lab August 16, 2006

Informix Chat with the Lab August 16, 2006. Stuart Litel, President, International Informix Users Group Jerry Keesee, Director of the Informix Lab John F. Miller III, Senior Technical Staff Member. Keynote Presentation – “ Information as a Service... A New Era in Databases”

ova
Download Presentation

Informix Chat with the Lab August 16, 2006

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. Informix Chat with the LabAugust 16, 2006 Stuart Litel, President, International Informix Users Group Jerry Keesee, Director of the Informix Lab John F. Miller III, Senior Technical Staff Member

  2. Keynote Presentation – “Information as a Service... A New Era in Databases” Bob Picciano, VP of IBM Database Services, Information Management Division • Educational Seminars • Hands on with Advanced features of IDS 10 • Using Perl, DBI and CGI with Databases • Technical Presentations • Database Performance • Disaster Recovery and Data Availability • Application Development • Security • Examples and Solutions • IDS Internals • Informix support • Where do our speakers come from? • IBM US • IBM UK • IBM DE • IBM CZ • Informix users • Products & Services Exhibitions For conference information and registration, go towww.iiug.org/conf * Note: All registration is handled by IDUG – The International DB2 User Group (IDUG).

  3. Announcing IIUG / IDUG North America 2007 May 6 – 10, San Jose California • Call for presentations is now under way! This is YOUR opportunity to share your Informix experience and expertise with an audience of your peers. • FREE conference registration if your presentation is selected. • Single session (60 minutes) or Double (120 minutes) • Abstract deadline is September 1st, 2006 – so start working on it now! • All that is needed for abstract submission is Title, Brief description of the session and an outline. • Entire presentation is not due for a few months after being selected. Over 50 Informix sessions to be presented by fellow Informix users like yourself. • Go to the IIUG Website for more information and a link to the Call for Presentations – http://www.iiug.org/conf/ • See you in San Jose…. The Heart of Silicon Valley.

  4. Overview • Recent Changes to onstat • View I/O through onstat • Disk IO • Network IO • Memory Usage • Cache Sizes • View Action by • User • Table

  5. Recent onstat changes • onstat –m • Displays the message file when the server is offline • onstat –c • Displays the configuration file when the server is offline

  6. New onstat Options • onstat –g env User/System environment • onstat –g stm Statement memory usage • onstat –g rwm Read/Write Mutex • onstat –C Btree Scanner • onstat –g cac Display various caches

  7. Looking at Environmentonstat –g env [sid] Session Environment Server Environment cmd> onstat -g env Server start-up environment: Variable Value [values-list] DBDELIMITER | DBPATH . DBTEMP /tmp cmd> onstat -g env 147 Environment for session 147: Variable Value [values-list] CLIENT_LOCALE en_US.8859-1 DBDELIMITER | DBPATH //jmiller_94 DBTEMP /usr/tmp

  8. Memory by SQL Statementonstat –g stm • Shows all currently active SQL statements • Shows the amount of memory each SQL statement currently using in bytes command> onstat -g stm session 16 -------------------------------------- sdblock heapsz statement ('*' = Open cursor) bca2018 11936 *select * from customer, orders session 14 --------------------------------------- sdblock heapsz statement ('*' = Open cursor) bc4d018 2048 Database 'insdel' bc4d140 8496 select count(*) from informix.systables; bc4d268 6264 *select * from t2

  9. Watching The Inverted Pyramidonstat –g iov AIO I/O vps: class/vp s io/s totalops dskread dskwrite dskcopy wakeups io/wup errors msc 0 i 0.0 26 0 0 0 26 1.0 0 aio 0 s 0.2 1080 883 188 0 849 1.3 0 aio 1 i 0.2 1072 874 189 0 830 1.3 0 aio 2 s 0.2 884 852 28 0 699 1.3 0 aio 3 i 0.2 647 831 13 0 769 1.1 0 aio 4 i 0.2 252 832 16 0 712 1.2 0 aio 5 i 0.2 24 811 12 0 673 1.2 0 pio 0 i 0.0 2 0 2 0 3 0.7 0 lio 0 i 0.0 3 0 3 0 4 0.8 0

  10. Watching The Inverted Pyramidonstat –g iov AIO I/O vps: class/vp s io/s totalops dskread dskwrite dskcopy wakeups io/wup errors msc 0 i 0.0 26 0 0 0 26 1.0 0 aio 0 s 0.2 1080 883 188 0 849 1.3 0 aio 1 i 0.2 1072 874 189 0 830 1.3 0 aio 2 s 0.2 884 852 28 0 699 1.3 0 aio 3 i 0.2 647 831 13 0 769 1.1 0 aio 4 i 0.2 252 832 16 0 712 1.2 0 aio 5 i 0.2 24 811 12 0 673 1.2 0 pio 0 i 0.0 2 0 2 0 3 0.7 0 lio 0 i 0.0 3 0 3 0 4 0.8 0 Errors indicate a lack of resources in the I/O subsystem Especially important if using KAIO

  11. Watching The Inverted Pyramidonstat –g iov AIO I/O vps: class/vp s io/s totalops dskread dskwrite dskcopy wakeups io/wup errors msc 0 i 0.0 26 0 0 0 26 1.0 0 aio 0 s 0.2 1080 883 188 0 849 1.3 0 aio 1 i 0.2 1072 874 189 0 830 1.3 0 aio 2 s 0.2 884 852 28 0 699 1.3 0 aio 3 i 0.2 647 831 13 0 769 1.1 0 aio 4 i 0.2 252 832 16 0 712 1.2 0 aio 5 i 0.2 24 811 12 0 673 1.2 0 pio 0 i 0.0 2 0 2 0 3 0.7 0 lio 0 i 0.0 3 0 3 0 4 0.8 0 S indicates in System call (VP is currently busy) I indicates the VP is currently idle

  12. Monitoring I/O Resources by Fileonstat -g iof AIO global files: gfd pathname totalops dskread dskwrite io/s 3 /dev/tsinfo1 1506 1393 113 0.0 4 /dev/tsinfo2 776 3 773 0.0 5 /dev/tsinfo0 22205 7994 14211 0.4 6 /dev/tsinfo3 55092 54102 990 1.1 • dskread/dskwrite • Number of read/write system calls • io/s • Average I/O operations per second

  13. Network Resources By Client Typeonstat -g ntd Client Type Calls Accepted Rejected Read Write sqlexec yes 4744 26 3911350 3882491 srvinfx yes 92 0 0 0 oncheck yes 410 0 114668 114851 onmonitor yes 4 0 136 137 ontape yes 5 0 66113 66117 • Client Type • The type of client connecting to the IDS • Accepted/Rejected • Number of times a successful/unsuccessful connection occurred • Read/Write • The number of times the engine received/sent data to the clients

  14. Network Resources By Useronstat -g ntu Individual thread network information (basic): netscb type thread name sid fd poll reads writes q-nrm q-exp 3fc97bc soctcp sqlexec 5269 66 5 38 38 0/ 1 0/ 0 3f857bc soctcp sqlexec 5268 64 5 1 1 0/ 1 0/ 0 3fa97bc soctcp sqlexec 5267 62 5 41 41 0/ 1 0/ 0 3e737bc soctcp sqlexec 5264 52 5 1586 1585 0/ 1 0/ 0 3c7f7bc soctcp sqlexec 5262 29 5 357 357 0/ 1 0/ 0 • sid • User session id • type • Indicates the communication protocol used for this thread • reads/writes • The number of OnLine packets received/sent by the OnLine

  15. Show System Memory Usageonstat –g seg • Shows the amount of memory current allocate, used and free • Block of memory are in 4KB units Segment Summary: id key addr size ovhd class blkused blkfree 93001 1386432513 10a000000 13631488 424320 R 3160 168 93002 1386432514 10ad00000 31457280 1696 V 4685 2995 9509 1386432519 10eb00000 8388608 992 V 123 1925 Total: - - 53477376 - - 7968 5088

  16. Useful onstat Options • onstat –P Buffers by Table • onstat –g tpf Actions by Thread • onstat –g ppf Actions by Table

  17. Monitoring Different Caches • Buffer Cache • Stores commonly accessed data/index pages from disk • Data Dictionary • Cache used to store commonly accessed system catalog information • Data Distributions • Cache to store update statistics medium/high data used by the optimizer to build query plans • Stored Procedure Cache • Caches stored procedure code • Extensible Caches

  18. Buffer Cache Sizing (onstat –p) Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 78201 141506 1538858 94.92 14480 23668 298884 95.16 • System Read Cache • Increasing buffers improves read cache rate • 90% or better is a good read cache • System Write Cache • Increasing buffers may not help the write cache • LRU parameters and CKPTINTVL greatly affect the write cache

  19. Buffer Pool Stats by Tableonstat –P • Partnum • Numeric link to objects name table/index/fragment • Total • Number of pages currently buffered for this table/fragment • Data • Number of data page currently buffered for this table/fragment partnum total btree data other dirty 1048707 10 6 2 2 0 1048710 4 1 1 2 0 1048714 3 1 1 1 0 1049081 257 0 255 2 0 1049472 5462 0 5459 3 4635 Totals: 9000 52 5747 3201 4636 Percentages: Data 72.86 Btree 25.58 Other 01.57

  20. Buffer Pool Stats by Tableonstat –P Buffer Pool System Statistics • Data • Percentage of the buffer pool holding data pages • Btree • Percentage of the buffer pool holding index pages • Other • Control pages, such as, reserve pages, bitmap partnum total btree data other dirty 1048707 10 6 2 2 0 1048710 4 1 1 2 0 1048714 3 1 1 1 0 1049081 257 0 255 2 0 1049472 5462 0 5459 3 4635 Totals: 9000 52 5747 3201 4636 Percentages: Data 72.86 Btree 25.58 Other 01.57 Ensure the ratio of System Data/Btree pages looks reasonable for your workload

  21. Data Dictionary Cache onstat -g dic Dictionary Cache: Number of lists: 31, Maximum list size: 10 list# size refcnt dirty? heapptr table name -------------------------------------------------------- 11 1 1 no a2ecf88 d1@olympia:jmiller.t2 25 1 0 no a2ec330 d1@olympia:informix.systables Total number of dictionary entries: 2 • Shows the tables in the data dictionary • Total number of current entries • Currently 2 • Total number of available cache locations • Currently 31 * 10 = 310

  22. Data Distributions Cacheonstat –g dsc Cache used to store update statistics medium or high information Distribution Cache: Number of lists : 31 PC_POOLSIZE : 50 Number of entries : 2 Number of entries in use : 0 Distribution Cache Entries: list# id ref_cnt dropped? heap_ptr distribution name ----------------------------------------------------------------- 15 0 0 0 abe2c20 stores@oly:customer.lname 7 0 0 0 abe3820 stores@oly:customer.company Total number of distribution entries: 2.

  23. Stored Procedure Cacheonstat –g prc Stored Procedure Cache: Number of lists : 31 PC_POOLSIZE : 50 Number of entries : 3 Number of inuse entries : 0 Stored Procedure Cache Entries: list# id ref_cnt dropped? heap_ptr procedure name -------------------------------------------------------------- 4 1 0 0 abe3c20 stores7@olympia:informix.systdist 22 2 0 0 a2f0c58 sysmaster@olympia:informix.bitval 14 3 0 0 a2f0458 sysmaster@olympia:informix.l2date Total number of procedure entries: 3.

  24. Extensible Caches • User Defined Routines • Routine Cache • Type Cache • Cast Cache • OpClass • Access Method Cache • Secondary Access Method Cache • User Defined Aggregate Cache

  25. Actions by Table onstat -g ppf Partition profiles partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd bfwrt seqsc 9437341 11 0 0 0 0 4 0 0 26 14 0 9437394 205 0 0 0 63 11 0 8 318 129 6 10485999 15 0 0 0 0 6 0 0 29 18 0 11534359 5 0 0 0 0 1 0 0 18 8 0 6291561 0 0 0 0 1854 0 0 0 3605 0 103 TableLocksSQL like ActionsBuffers

  26. Actions by Table onstat -g ppf Partition profiles partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd bfwrt seqsc 9437341 11 0 0 0 0 4 0 0 26 14 0 9437394 205 0 0 0 63 11 0 8 318 129 6 10485999 15 0 0 0 0 6 0 0 29 18 0 11534359 5 0 0 0 0 1 0 0 18 8 0 6291561 0 0 0 0 1854 0 0 0 3605 0 103 • partnum Decimal number linked to systables:partnum • lkrqs LocK ReQuestS Number of times a lock has been acquired on the table • lkwts LocK WaiTS Number of times a user had to wait when acquiring a lock on this table • Dlks DeadLocKS

  27. Actions by Table onstat -g ppf Partition profiles partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd bfwrt seqsc 9437341 11 0 0 0 0 4 0 0 26 14 0 9437394 205 0 0 0 63 11 0 8 318 129 6 10485999 15 0 0 0 0 6 0 0 29 18 0 11534359 5 0 0 0 0 1 0 0 18 8 0 6291561 0 0 0 0 1854 0 0 0 3605 0 103 • isrd ISam Reads Number of times a rows has been read from this table • iswrt ISam WriTes Number of calls to insert a row into this table • isrwt ISam ReWriTes Number of calls to update a row in this table • Isdel ISam DELetes Number of calls to delete a row from this table

  28. Actions by Table onstat -g ppf Partition profiles partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd bfwrt seqsc 9437341 11 0 0 0 0 4 0 0 26 14 0 9437394 205 0 0 0 63 11 0 8 318 129 6 10485999 15 0 0 0 0 6 0 0 29 18 0 11534359 5 0 0 0 0 1 0 0 18 8 0 6291561 0 0 0 0 1854 0 0 0 3605 0 103 • bfrd BuFfer ReaDs Number of individual page reads from this table • bfwrt BuFfer WRites Number of times a page in this table has been modified • seqsc SeQuential SCans Number of sequential table scans

  29. Actions by User/Thread onstat -g tpf • User Thread • Locks • Log Records tid lkreqs lkw dl to lgrs isrd iswr isrw isdl isct isrb lx bfr bfw lsus lsmx seq 42 35 0 0 0 408 0 0 0 0 11 0 0 207 128 856 120 0 48 35110 0 0 0 32 0 0 0 0 14 0 0 638 2294 0 208 0 3873 4646 0 0 0 0 1469 89 0 0 0 0 0 6683 1080 0 0 2 3876 498 0 0 0 0 93 25 0 2 0 0 0 535 200 0 0 16 3648 1946 0 0 0 68 6266 42 4 1 5 0 0 4238 495 0 4488 10 • SQL Like Actions • Transactions • Buffers

  30. Actions by User/Thread onstat -g tpf tid lkreqs lkw dl to lgrs isrd iswr isrw isdl isct isrb lx bfr bfw lsus lsmx seq 42 35 0 0 0 408 0 0 0 0 11 0 0 207 128 856 120 0 48 35110 0 0 0 32 0 0 0 0 14 0 0 638 2294 0 208 0 3873 4646 0 0 0 0 1469 89 0 0 0 0 0 6683 1080 0 0 2 3876 498 0 0 0 0 93 25 0 2 0 0 0 535 200 0 0 16 3648 1946 0 0 0 68 6266 42 4 1 5 0 0 4238 495 0 4488 10 • tid Thread ID • Each user can have one or more threads, the TID has a many-to-one relationship to the user's Session ID

  31. Actions by User/Thread onstat -g tpf tid lkreqs lkw dl to lgrs isrd iswr isrw isdl isct isrb lx bfr bfw lsus lsmx seq 42 35 0 0 0 408 0 0 0 0 11 0 0 207 128 856 120 0 48 35110 0 0 0 32 0 0 0 0 14 0 0 638 2294 0 208 0 3873 4646 0 0 0 0 1469 89 0 0 0 0 0 6683 1080 0 0 2 3876 498 0 0 0 0 93 25 0 2 0 0 0 535 200 0 0 16 3648 1946 0 0 0 68 6266 42 4 1 5 0 0 4238 495 0 4488 10 • lgrs LoG Records The number of log records created by this thread • lsus Log Space Used The number of bytes of logical log space used by this thread • lsmx Log Space Maximum • The space used by the thread's largest transaction

  32. Actions by User/Thread onstat -g tpf tid lkreqs lkw dl to lgrs isrd iswr isrw isdl isct isrb lx bfr bfw lsus lsmx seq 42 35 0 0 0 408 0 0 0 0 11 0 0 207 128 856 120 0 48 35110 0 0 0 32 0 0 0 0 14 0 0 638 2294 0 208 0 3873 4646 0 0 0 0 1469 89 0 0 0 0 0 6683 1080 0 0 2 3876 498 0 0 0 0 93 25 0 2 0 0 0 535 200 0 0 16 3648 1946 0 0 0 68 6266 42 4 1 5 0 0 4238 495 0 4488 10 • isct ISam CommiT The number of commits • isrb ISam RollBack The number of times this thread has rolled back a transaction • lx Long Transactions The number of long transactions perpetrated by this thread

  33. Actions by User/Thread onstat -g tpf tid lkreqs lkw dl to lgrs isrd iswr isrw isdl isct isrb lx bfr bfw lsus lsmx seq 42 35 0 0 0 408 0 0 0 0 11 0 0 207 128 856 120 0 48 35110 0 0 0 32 0 0 0 0 14 0 0 638 2294 0 208 0 3873 4646 0 0 0 0 1469 89 0 0 0 0 0 6683 1080 0 0 2 3876 498 0 0 0 0 93 25 0 2 0 0 0 535 200 0 0 16 3648 1946 0 0 0 68 6266 42 4 1 5 0 0 4238 495 0 4488 10 • isrd ISam Reads Number of times a row has been read by this thread • Iswr ISam WRites Number of calls to insert a row by this thread • Isrw ISam ReWrites Number of calls to update a row by this thread • Isdl ISam DeLetes Number of calls to delete a row by this thread

  34. Actions by User/Thread onstat -g tpf tid lkreqs lkw dl to lgrs isrd iswr isrw isdl isct isrb lx bfr bfw lsus lsmx seq 42 35 0 0 0 408 0 0 0 0 11 0 0 207 128 856 120 0 48 35110 0 0 0 32 0 0 0 0 14 0 0 638 2294 0 208 0 3873 4646 0 0 0 0 1469 89 0 0 0 0 0 6683 1080 0 0 2 3876 498 0 0 0 0 93 25 0 2 0 0 0 535 200 0 0 16 3648 1946 0 0 0 68 6266 42 4 1 5 0 0 4238 495 0 4488 10 • lkreqs LocK REQuestS Number of times a lock has been acquired by this thread • lkwts LocK WaiTS Number of times a user had to wait when acquiring a lock • dlks DeadLocKS Number of Deadlock errors encountered by this user • to lock TimeOuts Number of times a user waited on a lock, but failed to acquire the lock in the time allotted

  35. Actions by User/Thread onstat -g tpf tid lkreqs lkw dl to lgrs isrd iswr isrw isdl isct isrb lx bfr bfw lsus lsmx seq 42 35 0 0 0 408 0 0 0 0 11 0 0 207 128 856 120 0 48 35110 0 0 0 32 0 0 0 0 14 0 0 638 2294 0 208 0 3873 4646 0 0 0 0 1469 89 0 0 0 0 0 6683 1080 0 0 2 3876 498 0 0 0 0 93 25 0 2 0 0 0 535 200 0 0 16 3648 1946 0 0 0 68 6266 42 4 1 5 0 0 4238 495 0 4488 10 • Monitor the ratio of all SQL like actions over buffer actions. This gives an indication of how much work is done for each row processed • For OLTP users this ratio will be low. For DW users this ratio should be higher. • A high ratio can mean indexing or optimizer issues (isrd + iswr + isrw + isdl) / (bfr + bfw)

  36. Questions

More Related