BUFFERPOOL Configuration

Hi, I saw an excellent video post from AdvancedDataTools https://www.youtube.com/watch?v=Wgw1bAWl_Hg regarding sample BUFFERPOOL configurations 1.3 GB Memory for Buffer - Linux OLTP System - BUFFERPOOL size=2k,buffers=1500000,lrus=32,lru_min_dirty=10,lru_max_dirty=20 2. 12 GB Memory for Buffers - AIX OLTP System - BUFFERPOOL size=4k,buffers=3000000,lrus=128,lru_min_dirty=1,lru_max_dirty=2 3. 48 GB Memory for Buffers - Solaris Data Warehouse - BUFFERPOOL size=2k,buffers=24000000,lrus=128,lru_min_dirty=60,lru_max_dirty=70 4. 15 GB Memory for 4K Buffers and 12.8 GB for 16K Buffers - BUFFERPOOL size=4k,buffers=60000000,lrus=256,lru_min_dirty=0.1,lru_max_dirty=0.2 - BUFFERPOOL size=16k,buffers=800000,lrus=256,lru_min_dirty=20,lru_max_dirty=30 Questions : 1. Coincidentally we are running on AIX with OLTP System so item #2 would be a great fit for us. But why is the min and max lru set to 1 and 2 respectively? does it mean 1% and 2%? Currently ours are set to 50 min and 60 max. 2. If I will be adapting #2 how can I check if the 50/60 we have is sufficient or if we need to change to 1/2? 3. What are the PROS/CONS of adding another BUFFERPOOL (instead of the default 1)

It is a great video. Obviously these are just examples. 1. The idea is to flush dirty pages to disk as fast as possible using the LRU mechanism. "1" and "2" are fairly aggressive values so the LRUs will be working hard. I did my own testing on this over 10+ years ago and the constant LRU activity does have a performance impact. However, data has to be flushed to disk somehow and there is a trade-off. The benefit of an aggressive LRU set up is shorter checkpoint interval. At checkpoint all dirty pages are written to disk. Keeping checkpoints very short used to be extremely important in the old days (version 10.00 and earlier) when checkpoints blocked server activity. Lester in the video recommends (for an OLTP system) keeping chunk writes low to avoid writing a lot of data out at the checkpoint, which has the potential to block (although rare) and dump a lot of writes to disk in a short space of time. If you have a modern SAN with a big write cache this won't be a problem and in this case I'd go against Lester's recommendation even for an OLTP system and allow a lot of writing at checkpoint time. If you don't have such a set up and writing a large amount of data quickly could impact read performance on your OLTP system, you'll follow what Lester says and trickle it through to disk using the LRU mechanism. 2. 50/60 is fine as long as you don't see performance any degradation during checkpoints. As discussed above this will depend on your throughput and storage. 3. On AIX the default page size is 4 kB so you must have a 4 kB bufferpool. Optionally you have 8, 12 and 16 kB ones as well. They will only be used if you have dbspaces using these page sizes. I would say in general larger page sizes are better especially for indices. They also permit larger sized tables which could be especially important if you have workgroup edition (no partitioning). Exceptions to this are if you a compress table or your table has a small row size: in these cases you may reach the 255 rows/page limit without filling the page completely, wasting space. Ben.

Let me answer your questions inline below: Questions : 1. Coincidentally we are running on AIX with OLTP System so item #2 would be a great fit for us. But why is the min and max lru set to 1 and 2 respectively? does it mean 1% and 2%? Currently ours are set to 50 min and 60 max. Ans: Benjamin's response is a good one. I would add: The trade off between LRU writes between checkpoints and checkpoint writes (aka chunk writes) is that even with the later releases (11.10+) that have non-blocking checkpoints, the massive IOs during a checkpoint can cause slowdowns in transaction performance on a busy OLTP system. My recommendation, based on benchmarking and client experiences, is to balance LRU Writes and Chunk Writes (see onstat -F) such that between 30% and 70% of writes are LRU writes. In the old days we wanted 70% or more LRU writes because of checkpoint blocking which is rarely a concern (see onstat -g ckp output to see if you are getting any sessions blocked and for how long). However, the severity of the checkpoint and transaction slowdown caused by too many Chunk Writes will depend on your system, so set to 50 & 60 and adjust it down until you are happy with the balance and performance effects. 2. If I will be adapting #2 how can I check if the 50/60 we have is sufficient or if we need to change to 1/2? Ans:See #1 Basically watch onstat -F and onstat -g ckp 3. What are the PROS/CONS of adding another BUFFERPOOL (instead of the default 1) Ans: I assume you are not talking about the "BUFFERPOOL default,..." entry which is ONLY used when you create a new dbspace with a pagesize for which you do not already have a size specific BUFFERPOOL entry. So, why use page sizes other than the default for your system (4K for AIX, Mac, & Windows; 2K everywhere else)? Several reasons: - If the pagesize is too small for the length of the rows in some tables causing those rows to extend out to remainder pages or even to multiple full pages. - If the number of pages in the table is approaching the partition page limit of 2^24 pages (16,777,216) and you do not want to partition the table. - If the size of the row is such that there is significant wasted space on every page. Example: a 1500 byte row on a 2K page waste 520 bytes per page (and also per row in this example). Put that row in a 6K page and it wastes only 423 bytes per 6K page or only 106 bytes per row. On a 14K page that row size wastes 811 bytes but divided over the 9 rows that fit on a page that's only 90 bytes per row. - Indexes do indeed perform better on wider pages with 16K pages performing best for all but the smallest indexes.

Oh, one more reason to use multiple page size buffer pools: - To be able to isolate the IOs against some objects from those of other objects so that, for example, index IO activity does not flush data pages from the cache.

Hi Benjamin/Art, Let me start by thanking both of you for your brilliant answers and inputs regarding the subject. Let me do some back story on our server to fully explain our case : We have an informix server running on IBM AIX which starts I think from version 9.7 (or earlier). This server was constantly updated (software and hardware) but still using the old oncofig file. Recently we migrated this server from 11.7 to 12.10 as well as upgraded the memory from 16 to 32 Gig and disk to SSD. Upon searching articles and videos on how to optimize the server I found out that we barely scratch the surface of it's capability. Upon doing an onstat - informix is only using 5 Gig of memory and BUFFERPOOL is at 1 gig only, also the server's # of processors is 16 but onstat -g sch displays only 1 cpu vp. Can you kindly check if I can tweak the lru min/max provided the onstat -F and onstat -g ckp respectively. Also if there are any parameters you need to seeso I can check and optimize (Willing and very happy to provide). I know there are still A LOT of tuning we need to do in the server and I want to start by configuring the onconfig the right way (one step at a time). Fg Writes LRU Writes Chunk Writes 0 0 3860865 address flusher state data # LRU Chunk Wakeups Idle Tim 70000002327a8d0 0 I 0 0 1293 389892 388804.195 70000002327b178 1 I 0 0 1286 389884 388794.436 70000002327ba20 2 I 0 0 1282 389877 388777.579 70000002327c2c8 3 I 0 0 1279 389877 388777.838 70000002327cb70 4 I 0 0 1272 389863 388762.618 70000002327d418 5 I 0 0 1269 389852 388751.045 70000002327dcc0 6 I 0 0 1268 389861 388770.145 70000002327e568 7 I 0 0 1266 389847 388740.853 70000002327ee10 8 I 0 0 1256 389858 388785.236 70000002327f6b8 9 I 0 0 1254 389813 388685.145 70000002327ff60 10 I 0 0 1252 389830 388757.763 700000023280808 11 I 0 0 1244 389812 388743.940 7000000232810b0 12 I 0 0 1240 389815 388724.718 700000023281958 13 I 0 0 1237 389800 388702.179 700000023282200 14 I 0 0 1235 389806 388713.892 700000023282aa8 15 I 0 0 1232 389803 388731.057 700000023283350 16 I 0 0 1226 389797 388746.082 700000023283bf8 17 I 0 0 1220 389793 388755.835 7000000232844a0 18 I 0 0 1214 389786 388750.004 700000023284d48 19 I 0 0 1209 389800 388766.367 7000000232855f0 20 I 0 0 1204 389762 388727.653 700000023285e98 21 I 0 0 1202 389776 388735.403 700000023286740 22 I 0 0 1194 389770 388745.575 700000023286fe8 23 I 0 0 1191 389776 388764.740 700000023287890 24 I 0 0 1178 389767 388777.971 700000023288138 25 I 0 0 1163 389744 388770.057 7000000232889e0 26 I 0 0 1149 389726 388754.098 700000023289288 27 I 0 0 1126 389715 388765.613 700000023289b30 28 I 0 0 1113 389707 388763.434 70000002328a3d8 29 I 0 0 1098 389680 388762.234 70000002328ac80 30 I 0 0 1070 389665 388774.429 70000002328b528 31 I 0 0 1048 389643 388786.975 70000002328bdd0 32 I 0 0 1023 389617 388789.788 70000002328c678 33 I 0 0 989 389587 388797.498 70000002328cf20 34 I 0 0 955 389550 388797.574 70000002328d7c8 35 I 0 0 912 389517 388810.118 70000002328e070 36 I 0 0 877 389472 388802.781 70000002328e918 37 I 0 0 835 389439 388807.584 70000002328f1c0 38 I 0 0 769 389366 388802.679 70000002328fa68 39 I 0 0 707 389307 388806.900 700000023290310 40 I 0 0 649 389245 388799.600 700000023290bb8 41 I 0 0 581 389181 388800.756 700000023291460 42 I 0 0 540 389139 388793.412 700000023291d08 43 I 0 0 496 389096 388807.081 7000000232925b0 44 I 0 0 467 389070 388815.880 700000023292e58 45 I 0 0 428 389034 388816.418 700000023293700 46 I 0 0 399 388995 388806.251 700000023293fa8 47 I 0 0 372 388968 388792.176 700000023294850 48 I 0 0 352 388943 388780.675 7000000232950f8 49 I 0 0 330 388922 388791.109 7000000232959a0 50 I 0 0 319 388919 388809.784 700000023296248 51 I 0 0 303 388902 388810.015 700000023296af0 52 I 0 0 291 388891 388803.111 700000023297398 53 I 0 0 282 388880 388798.683 700000023297c40 54 I 0 0 276 388870 388804.506 7000000232984e8 55 I 0 0 269 388862 388795.392 700000023298d90 56 I 0 0 266 388859 388789.904 700000023299638 57 I 0 0 260 388851 388786.595 700000023299ee0 58 I 0 0 254 388845 388797.945 70000002329a788 59 I 0 0 246 388838 388795.978 70000002329b030 60 I 0 0 212 388808 388798.634 70000002329b8d8 61 I 0 0 156 388748 388797.553 70000002329c180 62 I 0 0 67 388665 388828.192 70000002329ca28 63 I 0 0 18 388621 388849.211 70000002329d2d0 64 I 0 0 1 388609 388859.840 70000002329db78 65 I 0 0 0 388608 388860.248 70000002329e420 66 I 0 0 0 388608 388860.463 70000002329ecc8 67 I 0 0 0 388608 388860.463 70000002329f570 68 I 0 0 0 388608 388860.456 70000002329fe18 69 I 0 0 0 388608 388860.469 7000000232a06c0 70 I 0 0 0 388608 388860.462 7000000232a0f68 71 I 0 0 0 388608 388860.469 7000000232a1810 72 I 0 0 0 388608 388860.469 7000000232a20b8 73 I 0 0 0 388608 388860.470 7000000232a2960 74 I 0 0 0 388608 388860.468 7000000232a3208 75 I 0 0 0 388608 388860.468 7000000232a3ab0 76 I 0 0 0 388608 388860.468 7000000232a4358 77 I 0 0 0 388608 388860.469 7000000232a4c00 78 I 0 0 0 388608 388860.468 7000000232a54a8 79 I 0 0 0 388608 388860.466 7000000232a5d50 80 I 0 0 0 388608 388860.469 7000000232a65f8 81 I 0 0 0 388608 388860.472 7000000232a6ea0 82 I 0 0 0 388608 388860.471 7000000232a7748 83 I 0 0 0 388608 388860.471 7000000232a7ff0 84 I 0 0 0 388608 388860.460 7000000232a8898 85 I 0 0 0 388608 388860.467 7000000232a9140 86 I 0 0 0 388608 388860.470 7000000232a99e8 87 I 0 0 0 388608 388860.465 7000000232aa290 88 I 0 0 0 388608 388860.470 7000000232aab38 89 I 0 0 0 388608 388860.470 7000000232ab3e0 90 I 0 0 0 388608 388860.469 7000000232abc88 91 I 0 0 0 388608 388860.468 7000000232ac530 92 I 0 0 0 388608 388860.468 7000000232acdd8 93 I 0 0 0 388608 388860.469 7000000232ad680 94 I 0 0 0 388608 388860.469 7000000232adf28 95 I 0 0 0 388608 388860.468 7000000232ae7d0 96 I 0 0 0 388608 388860.461 7000000232af078 97 I 0 0 0 388608 388860.469 7000000232af920 98 I 0 0 0 388608 388860.468 7000000232b01c8 99 I 0 0 0 388608 388860.470 7000000232b0a70 100 I 0 0 0 388608 388860.465 7000000232b1318 101 I 0 0 0 388608 388860.467 7000000232b1bc0 102 I 0 0 0 388608 388860.469 7000000232b2468 103 I 0 0 0 388608 388860.470 7000000232b2d10 104 I 0 0 0 388608 388860.470 7000000232b35b8 105 I 0 0 0 388608 388860.469 7000000232b3e60 106 I 0 0 0 388608 388860.467 7000000232b4708 107 I 0 0 0 388608 388860.467 7000000232b4fb0 108 I 0 0 0 388608 388860.467 7000000232b5858 109 I 0 0 0 388608 388860.469 7000000232b6100 110 I 0 0 0 388608 388860.470 7000000232b69a8 111 I 0 0 0 388608 388860.469 7000000232b7250 112 I 0 0 0 388608 388860.470 7000000232b7af8 113 I 0 0 0 388608 388860.469 7000000232b83a0 114 I 0 0 0 388608 388860.469 7000000232b8c48 115 I 0 0 0 388608 388860.467 7000000232b94f0 116 I 0 0 0 388608 388860.470 7000000232b9d98 117 I 0 0 0 388608 388860.470 7000000232ba640 118 I 0 0 0 388608 388860.464 7000000232baee8 119 I 0 0 0 388608 388860.469 7000000232bb790 120 I 0 0 0 388608 388860.469 7000000232bc038 121 I 0 0 0 388608 388860.467 7000000232bc8e0 122 I 0 0 0 388608 388860.460 7000000232bd188 123 I 0 0 0 388608 388860.469 7000000232bda30 124 I 0 0 0 388608 388860.470 7000000232be2d8 125 I 0 0 0 388608 388860.469 70000

Hi again, Fg Writes LRU Writes Chunk Writes 0 0 3860865 Looking at the above, you are entirely doing chunk writes probably due to high lru min/max dirty settings. As such, the number of LRU pairs you have per buffer pool is not coming into play. If this is working for you as it seems to be (you have no long checkpoints and, much more importantly, no block time) you probably don't need to do anything. As I mentioned before, writing all data out in a checkpoint is more efficient than using LRUs. It looks like you should concentrate on using the server memory effectively first and then look at CPU VP configuration. Ben.