Re: [zfs-discuss] ZFS cache inconsistencies with Oracle
On Fri, 15 Oct 2010, Gerry Bragg wrote: Is it possible for a read to bypass the write cache and fetch from disk before the flush of the cache to disk occurs? No. Zfs is fully coherent in memory. On a server, most accesses are to the data in memory rather than from disk. Bob -- Bob Friesenhahn bfrie...@simple.dallas.tx.us, http://www.simplesystems.org/users/bfriesen/ GraphicsMagick Maintainer,http://www.GraphicsMagick.org/ ___ zfs-discuss mailing list zfs-discuss@opensolaris.org http://mail.opensolaris.org/mailman/listinfo/zfs-discuss
[zfs-discuss] ZFS cache inconsistencies with Oracle
A customer is running ZFS version15 on Solaris SPARC 10/08 supporting Oracle 10.2.0.3 databases in a dev and production test environment. We have come across some cache inconsistencies with one of the Oracle databases where fetching a record displays a 'historical value' (that has been changed and committed many times). This is an isolated occurance and is not always consistent. I can't replicate it to other tables. I'll also be posting a note to the ZFS discussion list. Is it possible for a read to bybpass the write cache and fetch from disk before the flush of the cache to disk occurs? This is a large system that is infrequently busy. The Oracle SGA size is minimized to 1GB per instance and we rely more on the ZFS cache, allowing us to fit 'more instances' (many of which are cloned snapshots). We've been running this setup for 2 years. The filesystems are set with compression on, blocksize 8k for oracle datafiles, 128k for redologs. Here are the details of the scenerio: 1. Update statement re-setting existing value. At this point the previous value was actually set to -643 prior to the update. It was originally set to 3 before today's session: SQL update [name deleted] set status_cd = 1 where id = 65; 1 row updated. SQL commit; Commit complete. SQL select rowid, id, status_cd from [table name deleted] SQL where id = 65; ROWID ID STATUS_CD -- -- -- AAAq/DAAERlAAM 65 3 Note that when retrieved the status_cd reverts to the old original value of 3, not the previous value of -643. 2. Oracle trace file proves that the update was issued and committed: = PARSING IN CURSOR #1 len=70 dep=0 uid=110 oct=6 lid=110 tim=17554807027344 hv=3512595279 ad='fd211878' update [table deleted] set status_cd = 1 where id = 65 END OF STMT PARSE #1:c=0,e=54,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=17554807027340 BINDS #1: EXEC #1:c=0,e=257,p=0,cr=3,cu=3,mis=0,r=1,dep=0,og=2,tim=17554807027737 WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=17554807027803 WAIT #1: nam='SQL*Net message from client' ela= 2999139 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=17554810026992 STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='UPDATE [TABLE DELETED] (cr=3 pr=0 pw=0 time=144 us)' STAT #1 id=2 cnt=1 pid=1 pos=1 obj=177738 op='INDEX UNIQUE SCAN [TABLE_DELETED]_XPK (cr=3 pr=0 pw=0 time=19 us)' PARSE #2:c=0,e=9,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=17554810027367 XCTEND rlbk=0, rd_only=0 EXEC #2:c=0,e=226,p=0,cr=0,cu=1,mis=0,r=0,dep=0,og=0,tim=17554810027630 WAIT #2: nam='log file sync' ela= 833 buffer#=9408 p2=0 p3=0 obj#=-1 tim=17554810028507 WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=17554810028578 WAIT #2: nam='SQL*Net message from client' ela= 1825185 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=17554811853812 = PARSING IN CURSOR #1 len=67 dep=0 uid=110 oct=3 lid=110 tim=17554811854015 hv=1593702413 ad='fd713640' select status_cd from [table_deleted] where id = 65 END OF STMT PARSE #1:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=17554811854010 BINDS #1: EXEC #1:c=0,e=91,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=17554811854273 WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=17554811854327 FETCH #1:c=0,e=64,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=2,tim=17554811854436 WAIT #1: nam='SQL*Net message from client' ela= 780 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=17554811855291 FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=17554811855331 WAIT #1: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=17554811855366 There are no Oracle or Solaris error messages indicating any issue with this update. Haas anyone seen this behavoir? The features of ZFS (snapshots/clones/compression) save us a ton of time on this platform and we have certainly benefited from it. Just want to understand how something like this could occur and determine how we can prevent it in the future. == Gerry Bragg Sr. Developer Altarum Institute (734) 516-0825 gerry.br...@altarum.orgmailto:gerry.br...@altarum.org www.altarum.orghttp://www.altarum.org/ Systems Research For Better Health ___ zfs-discuss mailing list zfs-discuss@opensolaris.org http://mail.opensolaris.org/mailman/listinfo/zfs-discuss
Re: [zfs-discuss] ZFS cache inconsistencies with Oracle
Hi so to be absolutely clear in the same session, you ran an update, commit and select, and the select returned an earlier value than the committed update? Things like ALTER SESSION set ISOLATION_LEVEL = SERIALIZABLE; will cause a session to NOT see commits from other sessions, but in Oracle one always sees one updates in ones transactions. ( assuming no other session makes a change of course ) So are you sure that 1 come other session hasn't mucked with the value between the commit and the select in your session. 2 some DB trigger is doing this perhaps, ie setting some default value? In my experience with DB's, triggers are the root of all evil. Enda On 15/10/2010 14:36, Gerry Bragg wrote: A customer is running ZFS version15 on Solaris SPARC 10/08 supporting Oracle 10.2.0.3 databases in a dev and production test environment. We have come across some cache inconsistencies with one of the Oracle databases where fetching a record displays a 'historical value' (that has been changed and committed many times). This is an isolated occurance and is not always consistent. I can't replicate it to other tables. I'll also be posting a note to the ZFS discussion list. Is it possible for a read to bybpass the write cache and fetch from disk before the flush of the cache to disk occurs? This is a large system that is infrequently busy. The Oracle SGA size is minimized to 1GB per instance and we rely more on the ZFS cache, allowing us to fit ‘more instances’ (many of which are cloned snapshots). We’ve been running this setup for 2 years. The filesystems are set with compression on, blocksize 8k for oracle datafiles, 128k for redologs. Here are the details of the scenerio: 1. Update statement re-setting existing value. At this point the previous value was actually set to -643 prior to the update. It was originally set to 3 before today’s session: SQL update [name deleted] set status_cd = 1 where id = 65; 1 row updated. SQL commit; Commit complete. SQL select rowid, id, status_cd from [table name deleted] SQL where id = 65; ROWID ID STATUS_CD -- -- -- AAAq/DAAERlAAM 65 3 Note that when retrieved the status_cd reverts to the old original value of 3, not the previous value of -643. 2. Oracle trace file proves that the update was issued and committed: = PARSING IN CURSOR #1 len=70 dep=0 uid=110 oct=6 lid=110 tim=17554807027344 hv=3512595279 ad='fd211878' update [table deleted] set status_cd = 1 where id = 65 END OF STMT PARSE #1:c=0,e=54,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=17554807027340 BINDS #1: EXEC #1:c=0,e=257,p=0,cr=3,cu=3,mis=0,r=1,dep=0,og=2,tim=17554807027737 WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=17554807027803 WAIT #1: nam='SQL*Net message from client' ela= 2999139 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=17554810026992 STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='UPDATE [TABLE DELETED] (cr=3 pr=0 pw=0 time=144 us)' STAT #1 id=2 cnt=1 pid=1 pos=1 obj=177738 op='INDEX UNIQUE SCAN [TABLE_DELETED]_XPK (cr=3 pr=0 pw=0 time=19 us)' PARSE #2:c=0,e=9,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=17554810027367 XCTEND rlbk=0, rd_only=0 EXEC #2:c=0,e=226,p=0,cr=0,cu=1,mis=0,r=0,dep=0,og=0,tim=17554810027630 WAIT #2: nam='log file sync' ela= 833 buffer#=9408 p2=0 p3=0 obj#=-1 tim=17554810028507 WAIT #2: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=17554810028578 WAIT #2: nam='SQL*Net message from client' ela= 1825185 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=17554811853812 = PARSING IN CURSOR #1 len=67 dep=0 uid=110 oct=3 lid=110 tim=17554811854015 hv=1593702413 ad='fd713640' select status_cd from [table_deleted] where id = 65 END OF STMT PARSE #1:c=0,e=41,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=17554811854010 BINDS #1: EXEC #1:c=0,e=91,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=17554811854273 WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=17554811854327 FETCH #1:c=0,e=64,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=2,tim=17554811854436 WAIT #1: nam='SQL*Net message from client' ela= 780 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=17554811855291 FETCH #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=17554811855331 WAIT #1: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=17554811855366 There are no Oracle or Solaris error messages indicating any issue with this update. Haas anyone seen this behavoir? The features of ZFS (snapshots/clones/compression) save us a ton of time on this platform and we have certainly benefited from it. Just want to understand how something like this could occur and determine how we can prevent it in the future. == Gerry Bragg Sr. Developer Altarum Institute (734) 516-0825 gerry.br...@altarum.org mailto:gerry.br...@altarum.org www.altarum.org http://www.altarum.org/ Systems Research