Oninit cdi / Archive
Talk to an expert →

BUFFERPOOL

Posted in 2018

Summary

A DBA on IDS 12.10.FC8 worried his buffer pools were "full" (onstat -P showing all buffers used) and had poor performance. Respondents explained full buffers are normal (they're a cache); what matters is the buffer turnover ratio and hit rates. His BTR was ~400 (ideally under 10), and seqscans were very high. Advice: enlarge both the 4K and 8K bufferpools (his 4K pool was tiny), then find and tune the offending queries/table scans using sysptprof, onstat -u/-g ses/-g buf, SQLTRACE and update statistics. Raising the 4K pool to 768MB didn't help, and no final resolution is recorded.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: Server Administration, Versions, Editions & End-of-Life

MUKESH TANUKU — — source: IIUG Forums & Mailing Lists

Hello friends, Please look into my environment. My buffers are full it looks like. My onconfig parameter for BUFFERPOOL is BUFFERPOOL default,memory=auto BUFFERPOOL size=4K,memory=512000 BUFFERPOOL size=8K,memory=auto and the output for "onstat -P": IBM Informix Dynamic Server Version 12.10.FC8 -- On-Line (Prim) -- Up 9 days 22:10:45 -- 6755072 Kbytes Buffer pool page size: 4096 partnum total btree data other dirty 0 9915 0 9906 9 36 1048577 3 0 3 0 3 1048578 2 1 1 0 0 1048580 16 6 10 0 0 1048581 12 7 5 0 0 1048582 3 1 2 0 0 1048583 6 3 3 0 0 1048584 1 1 0 0 0 1048585 3 1 2 0 0 1048590 1 1 0 0 0 1048593 1 1 0 0 0 1048599 1 1 0 0 0 1048600 1 1 0 0 0 1048601 1 1 0 0 0 1048603 1 1 0 0 0 1048604 2 1 1 0 0 1048624 4 2 2 0 0 1048625 3 3 0 0 0 1048694 6 3 3 0 0 1048695 3 2 1 0 0 1048696 2 1 1 0 0 1048697 4 2 2 0 0 1048698 1 1 0 0 0 1048704 2 1 1 0 0 1048707 2 1 1 0 0 1048708 2 1 1 0 0 1048711 6 2 4 0 0 1048713 1 1 0 0 0 1048714 1 1 0 0 0 1048715 1 1 0 0 0 1048718 2 1 1 0 0 1048720 1 1 0 0 0 1048724 1 1 0 0 0 1048738 4 3 1 0 0 1048739 3 3 0 0 0 1048759 2 0 1 1 1 1048760 2 2 0 0 1 1048761 3 3 0 0 1 1048762 2 2 0 0 1 1048766 13 0 12 1 3 1048768 1 1 0 0 0 1048769 1 1 0 0 0 1048770 1 1 0 0 1 1048771 13 0 12 1 7 1048772 3 3 0 0 2 1048773 3 3 0 0 2 1048774 4 4 0 0 3 1048860 4 0 3 1 4 1048864 2 0 2 0 0 1048865 3 3 0 0 0 4194305 44 0 44 0 44 4194306 104 40 63 1 0 4194307 123 41 82 0 0 4194308 172 9 163 0 0 4194309 39 16 23 0 0 4194310 1 1 0 0 0 4194316 100 15 85 0 0 4194317 22 12 10 0 0 4194319 54 10 44 0 0 4194320 13 8 5 0 0 4194323 14 6 7 1 4 4194325 1 1 0 0 0 4194326 1 1 0 0 0 4194327 2 1 1 0 0 4194328 10 3 7 0 0 4194329 10 10 0 0 0 4194330 165 29 136 0 0 4194336 1 1 0 0 0 4194350 217 137 80 0 0 4194351 3 3 0 0 0 4194352 6 3 3 0 0 4194388 19 0 19 0 0 4194527 1 0 1 0 0 4194529 3 0 3 0 0 4194546 1 0 1 0 0 4194593 1 0 1 0 0 4194594 1 1 0 0 0 4194603 1 0 1 0 0 4194604 1 1 0 0 0 4194617 1 0 1 0 0 4194621 1 0 1 0 0 4194629 1 0 1 0 0 4194637 1 0 1 0 0 4194645 1 0 1 0 0 4194646 1 1 0 0 0 4194651 1 0 1 0 0 4194665 1 0 1 0 0 4194666 1 1 0 0 0 4194673 2 0 2 0 0 4194674 3 3 0 0 0 4194696 1 0 1 0 0 4194724 2 0 2 0 0 4194725 2 2 0 0 0 4194736 1 0 1 0 0 4194737 1 1 0 0 0 4194740 3 0 3 0 0 4194741 3 3 0 0 0 4194750 6 0 6 0 0 4194751 4 4 0 0 0 4194784 1 0 1 0 0 4194785 1 1 0 0 0 4194794 1 0 1 0 0 4194795 1 1 0 0 0 4194808 1 0 1 0 0 4194809 3 3 0 0 0 4194850 1 0 1 0 0 4194851 1 1 0 0 0 4194860 1 0 1 0 0 4194861 1 1 0 0 0 4194864 1 0 1 0 0 4194865 1 1 0 0 0 4194872 1 0 1 0 0 4194873 1 1 0 0 0 4194881 10 0 10 0 0 4194883 1 0 1 0 0 4194884 1 1 0 0 0 4194895 2 0 2 0 0 4194896 1 1 0 0 0 4194897 1 0 1 0 0 4194898 1 1 0 0 0 4194907 1 0 1 0 0 4194908 1 1 0 0 0 4194919 1 0 1 0 0 4194920 1 1 0 0 0 4194950 1 0 1 0 0 4194951 1 1 0 0 0 4194970 2 0 2 0 0 4194971 1 1 0 0 0 4194974 1 0 1 0 0 4194975 1 1 0 0 0 4194976 1 0 1 0 0 4194977 1 1 0 0 0 4194988 1 0 1 0 0 4194989 1 1 0 0 0 4194992 3 0 2 1 0 4194999 1 0 1 0 0 4195000 2 2 0 0 0 4195012 1 0 1 0 0 4195013 1 1 0 0 0 4195022 32 0 31 1 0 4195031 1 0 1 0 0 4195032 1 1 0 0 0 4195037 1 0 1 0 0 4195038 1 1 0 0 0 4195067 36 0 35 1 10 4195068 6 6 0 0 1 4195069 2 0 2 0 0 4195070 1 1 0 0 0 4195075 5 0 4 1 0 4195079 42 0 42 0 0 4195095 12 0 12 0 0 4195140 1 0 1 0 0 4195160 10 0 10 0 0 4195166 1 0 1 0 0 4195167 1 1 0 0 0 4195246 2 0 2 0 0 4195371 1 0 1 0 0 4195372 1 1 0 0 0 4195373 1 0 1 0 0 4195377 1 0 1 0 0 4195378 1 1 0 0 0 4195381 2 0 2 0 0 4195382 2 2 0 0 0 4195383 1 0 1 0 0 4195384 1 1 0 0 0 4195393 63 0 62 1 0 4195394 8 8 0 0 0 4195419 1 0 1 0 0 4195421 3 0 3 0 1 4195422 4 4 0 0 1 4195423 175 0 175 0 0 4195425 1062 0 1062 0 1 4195426 2 2 0 0 0 4195437 4 0 4 0 0 4195438 3 3 0 0 0 4195445 14227 0 14224 3 17 4195459 1 0 1 0 0 4195467 79 0 79 0 0 4195468 52 52 0 0 0 4195481 2 0 2 0 0 4195507 388 0 387 1 4 4195508 5 5 0 0 1 4195511 2910 0 2910 0 1 4195512 743 743 0 0 0 4195513 3 0 2 1 3 4195514 3 3 0 0 1 4195534 1 0 1 0 0 4195535 2 2 0 0 0 4195545 2667 0 2666 1 8 4195546 7 7 0 0 0 4195559 13 0 13 0 0 4195560 8 8 0 0 0 4195561 8 0 8 0 0 4195562 5 5 0 0 0 4195564 6 0 6 0 0 4195565 6 6 0 0 0 4195569 1 0 1 0 1 4195570 2 2 0 0 0 4195573 1 1 0 0 0 4195574 2 0 1 1 1 4195575 3 3 0 0 0 4195576 2 0 2 0 0 4195588 1 0 1 0 0 4195589 2 2 0 0 0 4195594 4557 0 4556 1 43 4195595 153 153 0 0 0 4195650 2 0 2 0 0 4195704 5282 0 5281 1 4 4195705 4 4 0 0 1 4195706 1 0 1 0 0 4195707 1 1 0 0 0 4195708 4 0 4 0 0 4195709 1 1 0 0 0 4195725 9 0 9 0 0 4195745 11 0 10 1 0 4195753 9 0 9 0 4 4195804 1 0 1 0 1 4195805 1 1 0 0 1 4195818 1 0 1 0 0 4195819 1 1 0 0 0 4195820 3 0 3 0 0 4195821 1 1 0 0 0 4195822 7505 0 7504 1 4 4195823 3 3 0 0 1 4195833 7 0 6 1 7 4195834 1128 0 1127 1 61 4195835 25 25 0 0 1 4195836 21865 0 21864 1 4 4195837 6 6 0 0 1 4195861 20 0 20 0 1 4195881 5 0 4 1 2 4195882 3 3 0 0 1 4195883 21 0 20 1 4 4195884 7 7 0 0 3 4195885 4 0 4 0 0 4195886 6 6 0 0 0 4195887 2 0 1 1 2 4195888 1 1 0 0 1 4195895 3 0 2 1 3 4195896 1 1 0 0 1 4195901 62 0 62 0 4 4195902 8 8 0 0 0 4195947 43 0 43 0 1 4195948 9 9 0 0 0 4195951 52 0 51 1 1 4195952 58 58 0 0 2 4195959 4077 0 4077 0 3 4195960 8 8 0 0 0 4195970 1 0 1 0 0 4195984 3010 0 3010 0 0 4196004 25 0 24 1 4 4196005 24 23 0 1 7 4196012 1 0 1 0 1 4196013 1 1 0 0 1 4196022 8 8 0 0 0 4196023 3 0 2 1 3 4196024 1 1 0 0 1 4196041 1 0 1 0 0 4196042 1 1 0 0 0 4196059 23 0 23 0 0 4196060 4 4 0 0 0 4196067 13220 0 13219 1 84 4196068 326 324 0 2 4 4196069 43 0 42 1 16 4196070 11 10 0 1 7 4196073 49 0 48 1 7 4196074 3 3 0 0 1 4196079 7 0 6 1 0 4196080 1 1 0 0 0 4196081 7 0 6 1 0 4196082 1 1 0 0 0 4196083 12 0 12 0 0 4196091 4 0 3 1 0 4196092 1 1 0 0 0 4196097 18 0 17 1 0 4196098 1 1 0 0 0 4196101 10 0 10 0 0 4196111 4 0 4 0 0 4196112 3 3 0 0 0 4196119 1 0 1 0 0 4196120 1 1 0 0 0 4196127 123 0 122 1 0 4196128 3 3 0 0 0 4196141 70 0 69 1 7 4196142 9 9 0 0 0 4196143 1 0 1 0 0 4196144 1 1 0 0 0 4196161 14 0 14 0 0 4196162 5 5 0 0 0 4196163 2 0 2 0 0 4196164 1 1 0 0 0 4196169 1 0 1 0 0 4196170 1 1 0 0 0 4196171 31 0 31 0 0 4196172 9 9 0 0 0 4196175 1 0 1 0 0 4196176 1 1 0 0 0 4196183 2 0 2 0 0 4196184 1 1 0 0 0 4196187 3 0 2 1 0 4196188 1 1 0 0 0 4196197 2 0 2 0 0 4196205 1 0 1 0 0 4196206 1 1 0 0 0 4196207 1254 0 1254 0 0 4196208 61 61 0 0 0 4196209 80 0 80 0 0 4196210 15 15 0 0 0 4196215 9 0 9 0 0 4196216 3 3 0 0 0 4196217 45 0 45 0 0@@NL

↪ replying to MUKESH TANUKU
MUKESH TANUKU — — source: IIUG Forums & Mailing Lists

onconfig parameters for my instance SHMVIRTSIZE 105472 SHMTOTAL 0 # there is no limit for memory (Server RAM is 64GB) AUTO_LRU_TUNNING 1BUFFERPOOL default,memory=auto BUFFERPOOL size=4K,memory=512000 BUFFERPOOL size=8K,memory=auto onstat -p: IBM Informix Dynamic Server Version 12.10.FC8 -- On-Line (Prim) -- Up 10 days 01:26:40 -- 6755072 Kbytes Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 1170287491 12916762996 89052972052 98.75 127782668 291013389 20497121843 99.98 isamtot open start read write rewrite delete commit rollbk 67531926041 555657446 128007848 17619325283 10154268036 1118746 285864 1697460 24 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 12054801 69327 814440 1 0 0 56 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 1139210.45 195582.77 280 950 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 134390765 354017 5087230156 0 0 401 10457174 15469125 ixda-RA idx-RA da-RA logrec-RA RA-pgsused lchwaits 661438005 5787399 33121242 0 442462987 51962783

↪ replying to MUKESH TANUKU
Mike Walker — — source: IIUG Forums & Mailing Lists

Buffers should be full - think of them as a cache. What is relevant is how often queries find what they need in the buffers, versus how often they require more pages to be brought in from disk, and how often the bufferpool is turned over. I would suggest doing a google search for "buffer turnover ratio" for a calculation that is a rough guide as to whether you need to increase the size of your bufferpool. Mike Walker -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MUKESH TANUKU Sent: Wednesday, January 31, 2018 10:52 AM To: ids@iiug.org Subject: BUFFERPOOL [40612] Hello friends, Please look into my environment. My buffers are full it looks like. My onconfig parameter for BUFFERPOOL is BUFFERPOOL default,memory=auto BUFFERPOOL size=4K,memory=512000 BUFFERPOOL size=8K,memory=auto and the output for "onstat -P": IBM Informix Dynamic Server Version 12.10.FC8 -- On-Line (Prim) -- Up 9 days 22:10:45 -- 6755072 Kbytes Buffer pool page size: 4096 partnum total btree data other dirty 0 9915 0 9906 9 36 1048577 3 0 3 0 3 1048578 2 1 1 0 0 1048580 16 6 10 0 0 1048581 12 7 5 0 0 1048582 3 1 2 0 0 1048583 6 3 3 0 0 1048584 1 1 0 0 0 1048585 3 1 2 0 0 1048590 1 1 0 0 0 1048593 1 1 0 0 0 1048599 1 1 0 0 0 1048600 1 1 0 0 0 1048601 1 1 0 0 0 1048603 1 1 0 0 0 1048604 2 1 1 0 0 1048624 4 2 2 0 0 1048625 3 3 0 0 0 1048694 6 3 3 0 0 1048695 3 2 1 0 0 1048696 2 1 1 0 0 1048697 4 2 2 0 0 1048698 1 1 0 0 0 1048704 2 1 1 0 0 1048707 2 1 1 0 0 1048708 2 1 1 0 0 1048711 6 2 4 0 0 1048713 1 1 0 0 0 1048714 1 1 0 0 0 1048715 1 1 0 0 0 1048718 2 1 1 0 0 1048720 1 1 0 0 0 1048724 1 1 0 0 0 1048738 4 3 1 0 0 1048739 3 3 0 0 0 1048759 2 0 1 1 1 1048760 2 2 0 0 1 1048761 3 3 0 0 1 1048762 2 2 0 0 1 1048766 13 0 12 1 3 1048768 1 1 0 0 0 1048769 1 1 0 0 0 1048770 1 1 0 0 1 1048771 13 0 12 1 7 1048772 3 3 0 0 2 1048773 3 3 0 0 2 1048774 4 4 0 0 3 1048860 4 0 3 1 4 1048864 2 0 2 0 0 1048865 3 3 0 0 0 4194305 44 0 44 0 44 4194306 104 40 63 1 0 4194307 123 41 82 0 0 4194308 172 9 163 0 0 4194309 39 16 23 0 0 4194310 1 1 0 0 0 4194316 100 15 85 0 0 4194317 22 12 10 0 0 4194319 54 10 44 0 0 4194320 13 8 5 0 0 4194323 14 6 7 1 4 4194325 1 1 0 0 0 4194326 1 1 0 0 0 4194327 2 1 1 0 0 4194328 10 3 7 0 0 4194329 10 10 0 0 0 4194330 165 29 136 0 0 4194336 1 1 0 0 0 4194350 217 137 80 0 0 4194351 3 3 0 0 0 4194352 6 3 3 0 0 4194388 19 0 19 0 0 4194527 1 0 1 0 0 4194529 3 0 3 0 0 4194546 1 0 1 0 0 4194593 1 0 1 0 0 4194594 1 1 0 0 0 4194603 1 0 1 0 0 4194604 1 1 0 0 0 4194617 1 0 1 0 0 4194621 1 0 1 0 0 4194629 1 0 1 0 0 4194637 1 0 1 0 0 4194645 1 0 1 0 0 4194646 1 1 0 0 0 4194651 1 0 1 0 0 4194665 1 0 1 0 0 4194666 1 1 0 0 0 4194673 2 0 2 0 0 4194674 3 3 0 0 0 4194696 1 0 1 0 0 4194724 2 0 2 0 0 4194725 2 2 0 0 0 4194736 1 0 1 0 0 4194737 1 1 0 0 0 4194740 3 0 3 0 0 4194741 3 3 0 0 0 4194750 6 0 6 0 0 4194751 4 4 0 0 0 4194784 1 0 1 0 0 4194785 1 1 0 0 0 4194794 1 0 1 0 0 4194795 1 1 0 0 0 4194808 1 0 1 0 0 4194809 3 3 0 0 0 4194850 1 0 1 0 0 4194851 1 1 0 0 0 4194860 1 0 1 0 0 4194861 1 1 0 0 0 4194864 1 0 1 0 0 4194865 1 1 0 0 0 4194872 1 0 1 0 0 4194873 1 1 0 0 0 4194881 10 0 10 0 0 4194883 1 0 1 0 0 4194884 1 1 0 0 0 4194895 2 0 2 0 0 4194896 1 1 0 0 0 4194897 1 0 1 0 0 4194898 1 1 0 0 0 4194907 1 0 1 0 0 4194908 1 1 0 0 0 4194919 1 0 1 0 0 4194920 1 1 0 0 0 4194950 1 0 1 0 0 4194951 1 1 0 0 0 4194970 2 0 2 0 0 4194971 1 1 0 0 0 4194974 1 0 1 0 0 4194975 1 1 0 0 0 4194976 1 0 1 0 0 4194977 1 1 0 0 0 4194988 1 0 1 0 0 4194989 1 1 0 0 0 4194992 3 0 2 1 0 4194999 1 0 1 0 0 4195000 2 2 0 0 0 4195012 1 0 1 0 0 4195013 1 1 0 0 0 4195022 32 0 31 1 0 4195031 1 0 1 0 0 4195032 1 1 0 0 0 4195037 1 0 1 0 0 4195038 1 1 0 0 0 4195067 36 0 35 1 10 4195068 6 6 0 0 1 4195069 2 0 2 0 0 4195070 1 1 0 0 0 4195075 5 0 4 1 0 4195079 42 0 42 0 0 4195095 12 0 12 0 0 4195140 1 0 1 0 0 4195160 10 0 10 0 0 4195166 1 0 1 0 0 4195167 1 1 0 0 0 4195246 2 0 2 0 0 4195371 1 0 1 0 0 4195372 1 1 0 0 0 4195373 1 0 1 0 0 4195377 1 0 1 0 0 4195378 1 1 0 0 0 4195381 2 0 2 0 0 4195382 2 2 0 0 0 4195383 1 0 1 0 0 4195384 1 1 0 0 0 4195393 63 0 62 1 0 4195394 8 8 0 0 0 4195419 1 0 1 0 0 4195421 3 0 3 0 1 4195422 4 4 0 0 1 4195423 175 0 175 0 0 4195425 1062 0 1062 0 1 4195426 2 2 0 0 0 4195437 4 0 4 0 0 4195438 3 3 0 0 0 4195445 14227 0 14224 3 17 4195459 1 0 1 0 0 4195467 79 0 79 0 0 4195468 52 52 0 0 0 4195481 2 0 2 0 0 4195507 388 0 387 1 4 4195508 5 5 0 0 1 4195511 2910 0 2910 0 1 4195512 743 743 0 0 0 4195513 3 0 2 1 3 4195514 3 3 0 0 1 4195534 1 0 1 0 0 4195535 2 2 0 0 0 4195545 2667 0 2666 1 8 4195546 7 7 0 0 0 4195559 13 0 13 0 0 4195560 8 8 0 0 0 4195561 8 0 8 0 0 4195562 5 5 0 0 0 4195564 6 0 6 0 0 4195565 6 6 0 0 0 4195569 1 0 1 0 1 4195570 2 2 0 0 0 4195573 1 1 0 0 0 4195574 2 0 1 1 1 4195575 3 3 0 0 0 4195576 2 0 2 0 0 4195588 1 0 1 0 0 4195589 2 2 0 0 0 4195594 4557 0 4556 1 43 4195595 153 153 0 0 0 4195650 2 0 2 0 0 4195704 5282 0 5281 1 4 4195705 4 4 0 0 1 4195706 1 0 1 0 0 4195707 1 1 0 0 0 4195708 4 0 4 0 0 4195709 1 1 0 0 0 4195725 9 0 9 0 0 4195745 11 0 10 1 0 4195753 9 0 9 0 4 4195804 1 0 1 0 1 4195805 1 1 0 0 1 4195818 1 0 1 0 0 4195819 1 1 0 0 0 4195820 3 0 3 0 0 4195821 1 1 0 0 0 4195822 7505 0 7504 1 4 4195823 3 3 0 0 1 4195833 7 0 6 1 7 4195834 1128 0 1127 1 61 4195835 25 25 0 0 1 4195836 21865 0 21864 1 4 4195837 6 6 0 0 1 4195861 20 0 20 0 1 4195881 5 0 4 1 2 4195882 3 3 0 0 1 4195883 21 0 20 1 4 4195884 7 7 0 0 3 4195885 4 0 4 0 0 4195886 6 6 0 0 0 4195887 2 0 1 1 2 4195888 1 1 0 0 1 4195895 3 0 2 1 3 4195896 1 1 0 0 1 4195901 62 0 62 0 4 4195902 8 8 0 0 0 4195947 43 0 43 0 1 4195948 9 9 0 0 0 4195951 52 0 51 1 1 4195952 58 58 0 0 2 4195959 4077 0 4077 0 3 4195960 8 8 0 0 0 4195970 1 0 1 0 0 4195984 3010 0 3010 0 0 4196004 25 0 24 1 4 4196005 24 23 0 1 7 4196012 1 0 1 0 1 4196013 1 1 0 0 1 4196022 8 8 0 0 0 4196023 3 0 2 1 3 4196024 1 1 0 0 1 4196041 1 0 1 0 0 4196042 1 1 0 0 0 4196059 23 0 23 0 0 4196060 4 4 0 0 0 4196067 13220 0 13219 1 84 4196068 326 324 0 2 4 4196069 43 0 42 1 16 4196070 11 10 0 1 7 4196073 49 0 48 1 7 4196074 3 3 0 0 1 4196079 7 0 6 1 0 4196080 1 1 0 0 0 4196081 7 0 6 1 0 4196082 1 1 0 0 0 4196083 12 0 12 0 0 4196091 4 0 3 1 0 4196092 1 1 0 0 0 4196097 18 0 17 1 0 4196098 1 1 0 0 0 4196101 10 0 10 0 0 4196111 4 0 4 0 0 4196112 3 3 0 0 0 4196119 1 0 1 0 0 4196120 1 1

↪ replying to MUKESH TANUKU
Jack Parker — — source: IIUG Forums & Mailing Lists

That=E2=80=99s a pretty high number of seq scans. That will have a = nasty effect on your buffers. Run this query against the sys master database to see where they are: select * from sysptprof order by seqscans desc cheers j. > On Jan 31, 2018, at 1:04 PM, MUKESH TANUKU <mukeshbt1328@gmail.com> = wrote: >=20 > onconfig parameters for my instance=20 >=20 > SHMVIRTSIZE 105472=20 > SHMTOTAL 0 # there is no limit for memory (Server RAM is 64GB)=20 > AUTO_LRU_TUNNING 1=20> BUFFERPOOL default,memory=3Dauto=20 > BUFFERPOOL size=3D4K,memory=3D512000=20 > BUFFERPOOL size=3D8K,memory=3Dauto=20 >=20> onstat -p:=20 > IBM Informix Dynamic Server Version 12.10.FC8 -- On-Line (Prim) -- Up = 10 days=20 > 01:26:40 -- 6755072 Kbytes=20 >=20 > Profile=20> dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached=20= > 1170287491 12916762996 89052972052 98.75 127782668 291013389 = 20497121843 99.98=20 >=20 > isamtot open start read write rewrite delete commit rollbk=20 > 67531926041 555657446 128007848 17619325283 10154268036 1118746 285864 = 1697460=20 > 24=20 >=20 > gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs=20 > 12054801 69327 814440 1 0 0 56=20 >=20 > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes=20 > 0 0 0 1139210.45 195582.77 280 950=20 >=20 > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans=20= > 134390765 354017 5087230156 0 0 401 10457174 15469125=20 >=20 > ixda-RA idx-RA da-RA logrec-RA RA-pgsused lchwaits=20 > 661438005 5787399 33121242 0 442462987 51962783=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

↪ replying to Mike Walker
MUKESH TANUKU — — source: IIUG Forums & Mailing Lists

Hello Mike I got the BTR for my database. bufsize pagreads bufwrites nbuffs btr ------- ----------- ----------- ------ ---- 4096 13085208125 14482959 126057 427.6493472 8192 812811552 21356077543 224950 405.5567738

↪ replying to MUKESH TANUKU
Mike Walker — — source: IIUG Forums & Mailing Lists

Those values are high - indicating that the contents of the bufferpools are being overwritten frequently. The lower the number the better, and below 10 is a good value to aim for. Are you actually experiencing any performance problems, or is this more of an academic exercise? From these numbers, I would suggest increasing the size of both bufferpools (maybe doubling if you can), and then checking the BTR again after the system has been up for a while to see if it has improved. You must also undertake some tuning of the queries, focusing on which ones are reading more records than are actually needed. The tuning can get quite involved, but a good place to start is to look for full table scans of large tables. A scan of a big table can overwrite everything in the bufferpool. If you have frequent scans of the same table (assuming that it doesn't all fit in the bufferpool), or scans of multiple tables, then this can easily result in the bufferpool contents continually being overwritten - resulting in poor caching. Here's a query that I use to identify the 5 tables with the highest KB scanned per hour. It might be a good start to find out which tables may be responsible for overwriting the cache: select first 5 p.dbsname, p.tabname, p.seqscans, i.ti_nrows, round((i.ti_npused * i.ti_pagesize/1024),0) size_kb, round(p.seqscans / (select (((sh_curtime - sh_pfclrtime)/60)/60) from sysshmvals),0) scans_hr, round(p.seqscans * i.ti_nrows / (select (((sh_curtime - sh_pfclrtime)/60)/60) from sysshmvals),0) rows_scn_hr, round(p.seqscans * (i.ti_npused * i.ti_pagesize/1024) / (select (((sh_curtime - sh_pfclrtime)/60)/60) from sysshmvals),0) kb_scn_hr, dbinfo("DBSPACE", partnum) dbspace, (select ROUND ((((sh_curtime - sh_pfclrtime)/60)/60),2) from sysshmvals) hr_since_reset from sysptprof p, systabinfo i where p.partnum = i.ti_partnum order by kb_scn_hr desc; Once you identify these, you will need to look at the queries that are running against these tables to see which ones can be tuned. You may also want to start by looking at the queries that are simply taking a long time to complete, or doing many reads/writes. You can monitor with onstats (-u, -g ntt, -ses), or try using SQLTRACE to capture them. Mike Walker Advanced DataTools Corporation -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MUKESH TANUKU Sent: Thursday, February 01, 2018 1:58 AM To: ids@iiug.org Subject: Re: RE: BUFFERPOOL [40619] Hello Mike I got the BTR for my database. bufsize pagreads bufwrites nbuffs btr ------- ----------- ----------- ------ ---- 4096 13085208125 14482959 126057 427.6493472 8192 812811552 21356077543 224950 405.5567738 **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Mike Walker
Mike Walker — — source: IIUG Forums & Mailing Lists

And of course....the old favorite...are you running UPDATE STATISTICS? -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Mike Walker Sent: Thursday, February 01, 2018 6:23 AM To: ids@iiug.org Subject: RE: RE: BUFFERPOOL [40620] Those values are high - indicating that the contents of the bufferpools are being overwritten frequently. The lower the number the better, and below 10 is a good value to aim for. Are you actually experiencing any performance problems, or is this more of an academic exercise? >From these numbers, I would suggest increasing the size of both >bufferpools (maybe doubling if you can), and then checking the BTR again after the system has been up for a while to see if it has improved. You must also undertake some tuning of the queries, focusing on which ones are reading more records than are actually needed. The tuning can get quite involved, but a good place to start is to look for full table scans of large tables. A scan of a big table can overwrite everything in the bufferpool. If you have frequent scans of the same table (assuming that it doesn't all fit in the bufferpool), or scans of multiple tables, then this can easily result in the bufferpool contents continually being overwritten - resulting in poor caching. Here's a query that I use to identify the 5 tables with the highest KB scanned per hour. It might be a good start to find out which tables may be responsible for overwriting the cache: select first 5 p.dbsname, p.tabname, p.seqscans, i.ti_nrows, round((i.ti_npused * i.ti_pagesize/1024),0) size_kb, round(p.seqscans / (select (((sh_curtime - sh_pfclrtime)/60)/60) from sysshmvals),0) scans_hr, round(p.seqscans * i.ti_nrows / (select (((sh_curtime - sh_pfclrtime)/60)/60) from sysshmvals),0) rows_scn_hr, round(p.seqscans * (i.ti_npused * i.ti_pagesize/1024) / (select (((sh_curtime - sh_pfclrtime)/60)/60) from sysshmvals),0) kb_scn_hr, dbinfo("DBSPACE", partnum) dbspace, (select ROUND ((((sh_curtime - sh_pfclrtime)/60)/60),2) from sysshmvals) hr_since_reset from sysptprof p, systabinfo i where p.partnum = i.ti_partnum order by kb_scn_hr desc; Once you identify these, you will need to look at the queries that are running against these tables to see which ones can be tuned. You may also want to start by looking at the queries that are simply taking a long time to complete, or doing many reads/writes. You can monitor with onstats (-u, -g ntt, -ses), or try using SQLTRACE to capture them. Mike Walker Advanced DataTools Corporation -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MUKESH TANUKU Sent: Thursday, February 01, 2018 1:58 AM To: ids@iiug.org Subject: Re: RE: BUFFERPOOL [40619] Hello Mike I got the BTR for my database. bufsize pagreads bufwrites nbuffs btr ------- ----------- ----------- ------ ---- 4096 13085208125 14482959 126057 427.6493472 8192 812811552 21356077543 224950 405.5567738 **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Mike Walker
MUKESH TANUKU — — source: IIUG Forums & Mailing Lists

Thanks Mike for your valuable answer, and yes im running UPDATE STATISTICS HIGH on table with indexed columns

↪ replying to Mike Walker
MUKESH TANUKU — — source: IIUG Forums & Mailing Lists

Hello Mike, I have increased the bufferpool in my onconfig file, see parameters bellow, BUFFERPOOL default,memory=auto #BUFFERPOOL size=4K,memory=512000 BUFFERPOOL size=4K,memory=768000 BUFFERPOOL size=8K,memory=auto I have changed from 512000 to 768000. Previous BTR is arround 400 after changing the bufferpool now the BTR is bufsize pagreads bufwrites nbuffs btr ------- --------- ---------- ------ ---------- 4096 866572740 831511 94526 1310.90803588430696316357404312 8192 56718076 2360957834 220932 1563.2967286637646748450072549857 What to do now to decrease the BTR

↪ replying to MUKESH TANUKU
Mike Walker — — source: IIUG Forums & Mailing Lists

That really isn't a lot of memory for the 4K bufferpool. Can you not give Informix a lot more? Remember that the bufferpool is your cache - and you don't want to be miserly with it if you can avoid it. What does "onstat -g buf" show you? Not knowing your system, it is hard to make recommendations. Have you looked for queries that run long and run frequently? Tuning a database is more than increasing a single parameter, and you'll need to start digging in and seeing what the pain points are. Are you actually experiencing poor performance, and is it with specific queries? If so, then start with those. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MUKESH TANUKU Sent: Friday, February 09, 2018 5:01 AM To: ids@iiug.org Subject: Re: RE: RE: BUFFERPOOL [40665] Hello Mike, I have increased the bufferpool in my onconfig file, see parameters bellow, BUFFERPOOL default,memory=auto #BUFFERPOOL size=4K,memory=512000 BUFFERPOOL size=4K,memory=768000 BUFFERPOOL size=8K,memory=auto I have changed from 512000 to 768000. Previous BTR is arround 400 after changing the bufferpool now the BTR is bufsize pagreads bufwrites nbuffs btr ------- --------- ---------- ------ ---------- 4096 866572740 831511 94526 1310.90803588430696316357404312 8192 56718076 2360957834 220932 1563.2967286637646748450072549857 What to do now to decrease the BTR **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Mike Walker
MUKESH TANUKU — — source: IIUG Forums & Mailing Lists

Dear Mike, I had run the Update statistics on High mode for indexed columns. We are facing poor performance We are trying to find the queries to tune. onstat -g buf: IBM Informix Dynamic Server Version 12.10.FC8 -- On-Line (Prim) -- Up 01:12:21 -- 4400896 Kbytes Profile Buffer pool page size: 4096 dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 18280639 144434782 361412388 94.94 29480 41536 234139 87.41 bufwrits_sinceckpt bufwaits ovbuff flushes 40422 2112656 0 5 Fg Writes LRU Writes Avg. LRU Time Chunk Writes Total Mem 0 0 -1.#IO 16136 266Mb cache # extends max memory next memory hit ratio last 3 768Mb 128Mb 90 10:42:24 Bufferpool Segments id segment size # buffs 0 0000000086FC0000 74Mb 15706 1 00000001009C0000 64Mb 15763 2 00000001049C0000 64Mb 15763 3 00000001089C0000 64Mb 15763 ---------------------------------- Buffer pool page size: 8192 dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 6907 110738 106078502 99.99 887283 1920126 47215305 98.12 bufwrits_sinceckpt bufwaits ovbuff flushes 13231275 37 0 5 Fg Writes LRU Writes Avg. LRU Time Chunk Writes Total Mem 0 0 -1.#IO 7274 1872Mb cache # extends max memory next memory hit ratio last 0 21216Mb 1760Mb 90 10:07:11 Bufferpool Segments id segment size # buffs 0 000000008B9C0000 1872Mb 224950 ---------------------------------- Fast Cache Stats gets hits %hits puts 79434073 79309180 99.84 140512943

↪ replying to MUKESH TANUKU
Mike Walker — — source: IIUG Forums & Mailing Lists

Your 4K bufferpool is very small - consider making it bigger. You need to find your slower queries and determine whether you are missing indexes or if there are other changes that may improve performance. There is no magic bullet for this, but start with "onstat -u" and check sessions that consistently have the first flag that is NOT a "Y". Do you see many sessions where that first flag is a "L"? That would indicate that you are running into locked records. If you consistently see sessions where the flag is a "B" then they are waiting on buffers - increase those bufferpools as a starting point. If you see sessions that have the first flag as a "-" and they stay that way for a while (keep running onstat -u) then that may be a query which is running for a while. Run onstat -g ses <sid> and take a look at the SQL. Keep doing this until you identify some queries that you see run frequently and the business folks may agree are likely candidates for tuning. Get an explain plan and determine if the query can be improved. Also look at the sysmaster:sysptprof table. If you have TBLSPACE_STATS enabled in your config, then you can use this table to see which tables have the most table scans, or are reading most pages from disk versus the buffers. Use this table to determine the top troublemakers. Of course there are also onconfig settings that can impact performance. There's quite a bit to the whole tuning thing, and every system is different. There's seldom just a single quick fix. The best thing is to get familiar with what is running and work with the users to know where to start. Mike Walker Advanced DataTools Corporation -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MUKESH TANUKU Sent: Friday, February 09, 2018 10:52 PM To: ids@iiug.org Subject: Re: RE: RE: RE: BUFFERPOOL [40668] Dear Mike, I had run the Update statistics on High mode for indexed columns. We are facing poor performance We are trying to find the queries to tune. onstat -g buf: IBM Informix Dynamic Server Version 12.10.FC8 -- On-Line (Prim) -- Up 01:12:21 -- 4400896 Kbytes Profile Buffer pool page size: 4096 dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 18280639 144434782 361412388 94.94 29480 41536 234139 87.41 bufwrits_sinceckpt bufwaits ovbuff flushes 40422 2112656 0 5 Fg Writes LRU Writes Avg. LRU Time Chunk Writes Total Mem 0 0 -1.#IO 16136 266Mb cache # extends max memory next memory hit ratio last 3 768Mb 128Mb 90 10:42:24 Bufferpool Segments id segment size # buffs 0 0000000086FC0000 74Mb 15706 1 00000001009C0000 64Mb 15763 2 00000001049C0000 64Mb 15763 3 00000001089C0000 64Mb 15763 ---------------------------------- Buffer pool page size: 8192 dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 6907 110738 106078502 99.99 887283 1920126 47215305 98.12 bufwrits_sinceckpt bufwaits ovbuff flushes 13231275 37 0 5 Fg Writes LRU Writes Avg. LRU Time Chunk Writes Total Mem 0 0 -1.#IO 7274 1872Mb cache # extends max memory next memory hit ratio last 0 21216Mb 1760Mb 90 10:07:11 Bufferpool Segments id segment size # buffs 0 000000008B9C0000 1872Mb 224950 ---------------------------------- Fast Cache Stats gets hits %hits puts 79434073 79309180 99.84 140512943 **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Related threads

Glossary