Performance issue with ZFS

Hi, We have shifted our database instance on ZFS file system. After shifting we observed that checkpoint time is increased. We are using Informix Dynamic Server Version 11.50.FC6 on SAN. System Specs are given below System Configuration: Sun Microsystems sun4v Memory size: 32640 Megabytes System Peripherals (Software Nodes): SUNW,SPARC-Enterprise-T5220 #isainfo -v 64-bit sparcv9 applications asi_blk_init vis2 vis 32-bit sparc applications asi_blk_init vis2 vis v8plus div32 mul32 mpstat CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl 0 2 0 42 230 3 54 0 2 14 0 118 0 1 0 99 1 5 0 71 160 12 273 1 8 11 0 670 3 2 0 95 2 0 0 41 101 4 191 2 1 6 0 485 4 1 0 95 3 0 0 75 44 3 77 1 1 5 0 202 5 0 0 94 4 0 0 60 38 3 66 1 1 8 0 152 4 0 0 96 5 0 0 50 32 3 54 1 1 7 0 128 3 0 0 96 6 0 0 49 36 3 62 1 1 7 0 139 3 0 0 97 7 0 0 48 26 2 44 1 1 6 0 111 3 0 0 97 8 0 0 9 21 1 34 0 1 2 0 71 0 0 0 100 9 1 0 26 50 5 78 0 2 4 0 206 0 1 0 99 10 6 0 133 216 26 341 1 11 14 0 1081 2 3 0 95 11 0 0 21 45 6 61 0 2 5 0 141 0 0 0 99 12 1 0 19 39 4 59 0 1 5 0 175 0 0 0 99 13 0 0 16 32 3 52 0 1 4 0 117 0 0 0 99 14 0 0 17 34 3 55 0 1 4 0 146 0 0 0 99 15 1 0 21 42 4 68 0 1 4 0 192 0 0 0 99 16 0 0 33 38 1 74 0 0 2 0 117 1 0 0 99 17 0 0 48 91 1 173 1 0 2 0 400 3 1 0 96 18 1 0 115 96 2 188 3 1 4 0 658 8 1 0 91 19 4 0 169 96 6 163 2 6 9 0 465 10 1 0 89 20 1 0 78 22 2 34 0 1 5 0 124 3 0 0 97 21 0 0 78 17 1 26 0 1 4 0 119 3 0 0 96 22 0 0 20 180 2 347 14 1 4 0 1323 17 2 0 81 23 0 0 3 10 1 13 0 1 2 0 18 0 0 0 100 24 1 0 13 27 2 43 0 1 3 0 83 0 0 0 100 25 0 0 14 42 2 70 0 1 3 0 138 0 0 0 99 26 0 0 12 37 2 60 0 1 3 0 119 0 0 0 99 27 1 0 29 58 5 92 0 2 6 0 169 0 1 0 99 28 7 0 131 211 24 334 1 11 15 0 755 1 3 0 96 29 0 0 19 45 6 61 0 2 6 0 101 0 0 0 99 30 0 0 8 23 2 35 0 1 3 0 59 0 0 0 100 31 0 0 5 15 1 24 0 0 2 0 43 0 0 0 100 32 1 0 13 30 2 46 0 1 4 0 73 0 0 0 100 33 0 0 10 25 2 39 0 1 3 0 63 0 0 0 100 34 0 0 11 26 2 41 0 1 3 0 64 0 0 0 100 35 0 0 11 341 319 38 0 1 4 0 63 0 1 0 99 36 1 0 623 762 724 62 0 2 5 0 114 0 2 0 98 37 5 0 474 575 140 802 2 10 19 0 302 0 3 0 97 38 0 0 303 378 14 654 1 3 15 0 78 0 1 0 99 39 0 0 6 18 1 25 0 1 2 0 39 0 0 0 100 40 1 0 17 35 3 56 0 1 4 0 157 0 0 0 99 41 0 0 21 38 8 53 0 1 4 0 142 0 0 0 99 42 0 0 17 34 3 55 0 1 3 0 157 0 0 0 99 43 0 0 15 30 2 49 0 1 3 0 134 0 0 0 99 44 0 0 5 14 1 22 0 0 1 0 62 0 0 0 100 45 1 0 33 59 5 95 0 2 4 0 290 0 1 0 99 46 7 0 141 220 28 347 1 11 14 0 1190 2 3 0 95 47 0 0 20 44 6 59 0 2 5 0 154 0 0 0 99 48 1 0 22 45 4 74 0 2 6 0 182 0 1 0 99 49 0 0 12 29 2 48 0 1 4 0 120 0 0 0 99 50 0 0 16 34 2 58 0 1 3 0 144 0 0 0 99 51 0 0 11 27 2 44 0 1 3 0 123 0 0 0 99 52 0 0 6 15 1 24 0 0 2 0 63 0 0 0 100 53 0 0 6 16 1 25 0 0 2 0 60 0 0 0 100 54 1 0 35 65 7 103 0 2 5 0 349 1 1 0 98 55 6 0 133 216 26 345 1 10 14 0 1120 2 3 0 95 56 13 0 145 229 30 350 1 13 15 0 867 1 3 0 96 57 1 0 25 56 9 71 0 2 6 0 128 0 0 0 99 58 0 0 10 24 3 33 0 1 3 0 60 0 0 0 100 59 0 0 6 16 1 24 0 0 2 0 45 0 0 0 100 60 1 0 16 31 4 46 0 1 4 0 95 0 0 0 99 61 1 0 17 34 4 52 0 1 4 0 101 0 0 0 99 62 1 0 15 31 3 47 0 1 4 0 93 0 0 0 100 63 1 0 31 58 6 92 0 2 6 0 205 0 1 0 99 #iostat -xnmpztc 2 extended device statistics r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device 485.3 234.2 62090.8 998.7 0.0 13.2 0.0 18.3 0 100 c4t6006016070031B0070CE55132FB7DE11d0 485.3 234.2 62090.8 998.7 0.0 13.2 0.0 18.3 0 100 c4t6006016070031B0070CE55132FB7DE11d0s0 tty cpu tin tout us sy wt id 1 213 3 2 0 96 #zpool iostat -v pool used avail read write read write -------------------------------------- ----- ----- ----- ----- ----- ----- rpool 81.6G 43.4G 0 0 253 0 c1t0d0s0 81.6G 43.4G 0 0 253 0 -------------------------------------- ----- ----- ----- ----- ----- ----- sanpool 681G 391G 645 74 80.7M 559K c4t6006016070031B0070CE55132FB7DE11d0 681G 391G 645 74 80.7M 559K -------------------------------------- ----- ----- ----- ----- ----- -----

I've said this before: Informix chunks should NEVER be placed on any journaled filesystem!!!! Not ZFS, not EXT3, not EXT4, none. It's just a bad idea. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Jul 26, 2010 at 6:48 AM, KHURRAM SHAHZAD <kshahzad02@i2cinc.com>wrote: > Hi, > > We have shifted our database instance on ZFS file system. After shifting we > observed that checkpoint time is increased. > > We are using Informix Dynamic Server Version 11.50.FC6 on SAN. > > System Specs are given below > > System Configuration: Sun Microsystems sun4v > > Memory size: 32640 Megabytes > > System Peripherals (Software Nodes): > SUNW,SPARC-Enterprise-T5220 > > #isainfo -v > 64-bit sparcv9 applications > > asi_blk_init vis2 vis > 32-bit sparc applications > > asi_blk_init vis2 vis v8plus div32 mul32 > > mpstat > CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl > 0 2 0 42 230 3 54 0 2 14 0 118 0 1 0 99 > 1 5 0 71 160 12 273 1 8 11 0 670 3 2 0 95 > 2 0 0 41 101 4 191 2 1 6 0 485 4 1 0 95 > 3 0 0 75 44 3 77 1 1 5 0 202 5 0 0 94 > 4 0 0 60 38 3 66 1 1 8 0 152 4 0 0 96 > 5 0 0 50 32 3 54 1 1 7 0 128 3 0 0 96 > 6 0 0 49 36 3 62 1 1 7 0 139 3 0 0 97 > 7 0 0 48 26 2 44 1 1 6 0 111 3 0 0 97 > 8 0 0 9 21 1 34 0 1 2 0 71 0 0 0 100 > 9 1 0 26 50 5 78 0 2 4 0 206 0 1 0 99 > 10 6 0 133 216 26 341 1 11 14 0 1081 2 3 0 95 > 11 0 0 21 45 6 61 0 2 5 0 141 0 0 0 99 > 12 1 0 19 39 4 59 0 1 5 0 175 0 0 0 99 > 13 0 0 16 32 3 52 0 1 4 0 117 0 0 0 99 > 14 0 0 17 34 3 55 0 1 4 0 146 0 0 0 99 > 15 1 0 21 42 4 68 0 1 4 0 192 0 0 0 99 > 16 0 0 33 38 1 74 0 0 2 0 117 1 0 0 99 > 17 0 0 48 91 1 173 1 0 2 0 400 3 1 0 96 > 18 1 0 115 96 2 188 3 1 4 0 658 8 1 0 91 > 19 4 0 169 96 6 163 2 6 9 0 465 10 1 0 89 > 20 1 0 78 22 2 34 0 1 5 0 124 3 0 0 97 > 21 0 0 78 17 1 26 0 1 4 0 119 3 0 0 96 > 22 0 0 20 180 2 347 14 1 4 0 1323 17 2 0 81 > 23 0 0 3 10 1 13 0 1 2 0 18 0 0 0 100 > 24 1 0 13 27 2 43 0 1 3 0 83 0 0 0 100 > 25 0 0 14 42 2 70 0 1 3 0 138 0 0 0 99 > 26 0 0 12 37 2 60 0 1 3 0 119 0 0 0 99 > 27 1 0 29 58 5 92 0 2 6 0 169 0 1 0 99 > 28 7 0 131 211 24 334 1 11 15 0 755 1 3 0 96 > 29 0 0 19 45 6 61 0 2 6 0 101 0 0 0 99 > 30 0 0 8 23 2 35 0 1 3 0 59 0 0 0 100 > 31 0 0 5 15 1 24 0 0 2 0 43 0 0 0 100 > 32 1 0 13 30 2 46 0 1 4 0 73 0 0 0 100 > 33 0 0 10 25 2 39 0 1 3 0 63 0 0 0 100 > 34 0 0 11 26 2 41 0 1 3 0 64 0 0 0 100 > 35 0 0 11 341 319 38 0 1 4 0 63 0 1 0 99 > 36 1 0 623 762 724 62 0 2 5 0 114 0 2 0 98 > 37 5 0 474 575 140 802 2 10 19 0 302 0 3 0 97 > 38 0 0 303 378 14 654 1 3 15 0 78 0 1 0 99 > 39 0 0 6 18 1 25 0 1 2 0 39 0 0 0 100 > 40 1 0 17 35 3 56 0 1 4 0 157 0 0 0 99 > 41 0 0 21 38 8 53 0 1 4 0 142 0 0 0 99 > 42 0 0 17 34 3 55 0 1 3 0 157 0 0 0 99 > 43 0 0 15 30 2 49 0 1 3 0 134 0 0 0 99 > 44 0 0 5 14 1 22 0 0 1 0 62 0 0 0 100 > 45 1 0 33 59 5 95 0 2 4 0 290 0 1 0 99 > 46 7 0 141 220 28 347 1 11 14 0 1190 2 3 0 95 > 47 0 0 20 44 6 59 0 2 5 0 154 0 0 0 99 > 48 1 0 22 45 4 74 0 2 6 0 182 0 1 0 99 > 49 0 0 12 29 2 48 0 1 4 0 120 0 0 0 99 > 50 0 0 16 34 2 58 0 1 3 0 144 0 0 0 99 > 51 0 0 11 27 2 44 0 1 3 0 123 0 0 0 99 > 52 0 0 6 15 1 24 0 0 2 0 63 0 0 0 100 > 53 0 0 6 16 1 25 0 0 2 0 60 0 0 0 100 > 54 1 0 35 65 7 103 0 2 5 0 349 1 1 0 98 > 55 6 0 133 216 26 345 1 10 14 0 1120 2 3 0 95 > 56 13 0 145 229 30 350 1 13 15 0 867 1 3 0 96 > 57 1 0 25 56 9 71 0 2 6 0 128 0 0 0 99 > 58 0 0 10 24 3 33 0 1 3 0 60 0 0 0 100 > 59 0 0 6 16 1 24 0 0 2 0 45 0 0 0 100 > 60 1 0 16 31 4 46 0 1 4 0 95 0 0 0 99 > 61 1 0 17 34 4 52 0 1 4 0 101 0 0 0 99 > 62 1 0 15 31 3 47 0 1 4 0 93 0 0 0 100 > 63 1 0 31 58 6 92 0 2 6 0 205 0 1 0 99 > > #iostat -xnmpztc 2 > > extended device statistics > > r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device > 485.3 234.2 62090.8 998.7 0.0 13.2 0.0 18.3 0 100 > c4t6006016070031B0070CE55132FB7DE11d0 > 485.3 234.2 62090.8 998.7 0.0 13.2 0.0 18.3 0 100 > c4t6006016070031B0070CE55132FB7DE11d0s0 > > tty cpu > tin tout us sy wt id > > 1 213 3 2 0 96 > > #zpool iostat -v > pool used avail read write read write > -------------------------------------- ----- ----- ----- ----- ----- ----- > rpool 81.6G 43.4G 0 0 253 0 > c1t0d0s0 81.6G 43.4G 0 0 253 0 > -------------------------------------- ----- ----- ----- ----- ----- ----- > sanpool 681G 391G 645 74 80.7M 559K > c4t6006016070031B0070CE55132FB7DE11d0 681G 391G 645 74 80.7M 559K > -------------------------------------- ----- ----- ----- ----- ----- ----- > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00163683395aa5ce96048c48d880

What would you suggest which file system is best for informix?

KHURRAM SHAHZAD wrote: > What would you suggest which file system is best for informix? None at all: raw disk. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

other than raw disk?

Best? No filesystem. RAW character devices are BEST. Next best? The simplest non-jounaled filesystem is next best. On Linux that would be EXT2, on Solaris? The old SunOS filesystem. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Jul 26, 2010 at 7:56 AM, KHURRAM SHAHZAD <kshahzad02@i2cinc.com>wrote: > What would you suggest which file system is best for informix? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0022159748fef2f880048c4a5f5c

Why not raw? Jonathon Wyza CX & CBORD System Administrator CX Programmer/Analyst Administrative Computing Bethel College (574)-257-3381 AIM: Iamwyza jonathon.wyza@bethelcollege.edu ============================== SLES 11x64 & IDS 11.50.FC6 "Don't document the problem, fix it." - Atli Björgvin Oddsson -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of KHURRAM SHAHZAD Sent: Monday, July 26, 2010 9:05 AM To: ids@iiug.org Subject: Re: Performance issue with ZFS [20645] other than raw disk? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Use ZFS - Volumes, remember to set the volume block size to the same as your database. OR If you have to use a filesystem with cooked files remember to set the zfs filesystem block size to match your database otherwise you will write 128K for each partial write (copy on write). -- Paul -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of KHURRAM SHAHZAD Sent: 26 July 2010 03:05 PM To: ids@iiug.org Subject: Re: Performance issue with ZFS [20645] other than raw disk? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

The old SunOS filesystem? is it UFS?

how can we set this parameter . Is there need to restart my machine or it will be effective on run time. What is the block size of the informix database,is it the page size of the dbspace or any thing else . Please give me some details.

First do an onstat -d to see where your data is stored ! Then as a privileged user (root) zfs list it will return all your ZFS file systems you are interested in fs prefixed by the sanpool poolname A volume blocksize can only be set when you create it For a filesystem you can change it at will with the following command. ** The blocksize of existing files on the filesystem will not change ** (you can change them by copying to a new file and renaming) zfs set recordsize=2k sanpool/ifxfilesys -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of KHURRAM SHAHZAD Sent: 26 July 2010 04:19 PM To: ids@iiug.org Subject: Re: RE: Performance issue with ZFS [20650] how can we set this parameter . Is there need to restart my machine or it will be effective on run time. What is the block size of the informix database,is it the page size of the dbspace or any thing else . Please give me some details. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Yes, sorry senior moment. UFS is simple. The primary problem with ZFS for databases that I know of (unlike EXT4 is is safe) is that ZFS, like most journaling filesystems, relocates disk blocks when they are written to. That has two implications for databases that don't affect flat files as severely: 1- since the FS block size is usually substantial (128K or larger) and the database's block size is small (for Informix 2K to 16K max) that means that large disk blocks will be completely rewritten many times as data is saved and updated by the database and each time moved to somewhere other than where it was moments ago which brings to the second problem 2- Informix assumes the contiguous pages within a chunk are physically contiguous, journaling filesystems break that relationship. IDS 11.50, because non-blocking checkpoints allow us to tune the engine to use more chunks writes which are sequential block writes, is less affected by the first problem, but more seriously affected by the second problem than earlier releases which had to be tuned to favor the smaller, more random, LRU writes. That means that 11.50 will have more long transactions under ZFS than earlier releases, but in either case the additional overhead of rewriting and moving disk blocks repeatedly robs the system of IO bandwidth. Simple filesystems do not experience any of these overheads. RAW is definitely superior, and considerably faster, than COOKED devices which are considerably faster than any filesystem, but simple filesystems are faster, for the IO patterns of databases, than the more advanced filesystems. Note that the main advantage of journaling filesystems is the added security of the journal to prevent data loss in case of a system crash. Note that ALL modern databases, and Informix is one of the most advanced in this way alone, have their own logging systems. Informix's logical logs perform the same function at the level of a single transaction and do it more securely and recover much faster than any journaled filesystem since their operations are optimized for the kinds of transactions that databases make. The filesystem journaling is not only redundant, but will slow down recovery if there is a crash. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Jul 26, 2010 at 10:10 AM, KHURRAM SHAHZAD <kshahzad02@i2cinc.com>wrote: > The old SunOS filesystem? is it UFS? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016e65a03e876663e048c4c1dae

We have set as per your recommendation but still we are facing checkpoint took time . Can you help me further?

Get rid of ZFS. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Jul 26, 2010 at 11:53 AM, KHURRAM SHAHZAD <kshahzad02@i2cinc.com>wrote: > We have set as per your recommendation but still we are facing checkpoint > took > time . Can you help me further? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016e65aeaa29595ee048c4c7dae

Art Kagel wrote: > Get rid of ZFS. I agree with Art. Journalled file systems absolutely kill database performance. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

The setting will only come into effect for new files that are created (hence the copy). Your logs will be written to a dbspace that already exists. I suggest that you add a new dbspace and move your logs there (with the smaller block size). -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of KHURRAM SHAHZAD Sent: 26 July 2010 05:54 PM To: ids@iiug.org Subject: Re: RE: RE: Performance issue with ZFS [20653] We have set as per your recommendation but still we are facing checkpoint took time . Can you help me further? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

You may also want to check if your SAN is honouring the SYNC_NV if it has battery backed up cache -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of KHURRAM SHAHZAD Sent: 26 July 2010 05:54 PM To: ids@iiug.org Subject: Re: RE: RE: Performance issue with ZFS [20653] We have set as per your recommendation but still we are facing checkpoint took time . Can you help me further? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Thanks all our problem has been solved after shifting to UFS

;-) Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Jul 26, 2010 at 12:57 PM, KHURRAM SHAHZAD <kshahzad02@i2cinc.com>wrote: > Thanks all our problem has been solved after shifting to UFS > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0016e6de0432e41c2d048c4f4246