Hi all, Does anybody use Oracle on ZFS in production (but not as a background/testing database but as a front line) ? I am interesting especially in: - how does it behave after a long time of using it. Becasue COW nature of ZFS I don''t know how it influences performance of queries. - general opinion of such pair (ZFS + Oracle) - which version of Oracle przemol
Hello przemolicc,
Friday, August 25, 2006, 3:51:53 PM, you wrote:
ppf> Hi all,
ppf> Does anybody use Oracle on ZFS in production (but not as a
ppf> background/testing database but as a front line) ?
Not exactly front-line but devel Oracle instances and warehouse.
It works. However no performance comparisons were done.
Remember to set recordsize.
ppf> I am interesting especially in:
ppf> - how does it behave after a long time of using it. Becasue COW nature
ppf> of ZFS I don''t know how it influences performance of queries.
ppf> - general opinion of such pair (ZFS + Oracle)
ppf> - which version of Oracle
Oracle 10 in a zones.
Also remember that some performance patches aren''t yet in S10.
Especially fix for not reading block is full overwrite is issued which
can hurt performance. But once patch is released (someone stated it
should be available at the end of September with other fixes).
I doubt you will find many (if any) fron-line Oracle on ZFS running
for a long time...
--
Best regards,
Robert mailto:rmilkowski at task.gda.pl
http://milek.blogspot.com
przemolicc at poczta.fm schrieb:> Hi all, > > Does anybody use Oracle on ZFS in production (but not as a > background/testing database but as a front line) ?I haven''t used in production yet - but I''m planning by the end of the year. I did some performance stress tests on ZFS vs. UFS+SVM. For testing I used the TPC-H benchmark as base and tested on SAN (Symmetrix DMX1000 storage). The test consists of four parts: 1. creation of database (CREATE DATABASE ...; @catalog.sql; @catproc.sql) 2. Importing Test data with sqlldr (TPC-H target size of 2 GB) 3. Run a single instance of the TPC-H benchmark 4. Run 4 (2 * NCPU) instances of the TPC-H benchmark Test 1: UFS performed slightly better Test 2: performance of UFS vs. ZFS was similar Test 3: ZFS was somewhat faster (<10%) Test 4: ZFS was much faster (>20%) Summing up the runtime of all 4 tests ZFS was ~12% faster (248 mins to 280 mins).> I am interesting especially in: > - how does it behave after a long time of using it. Becasue COW nature > of ZFS I don''t know how it influences performance of queries.I filled up the pool with random data so that the pool reached 100% usage during the benchmark (The benchmark needs a huge TEMP tablespace (with above 2GB data TEMP grew up to 20GB), so you can expect a lot of write I/O) The total performance dropped just by 1-2%> - general opinion of such pair (ZFS + Oracle)Use compression and save a lot of space. Expect a space reduction of >50% by using compression even with filled up tablespaces. Oracle just worked on ZFS, no "strange" results. I had one panic, but this was related to the Emulex FC device driver, not to ZFS (in the meantime a Emulex patch has been released (120222/120223), don''t know if it fixes this panic) The total runtime didn''t change during my tests (<1%) if compression was turned on compared to compression off. The CPU usage increased by ~15%. So if you have fast CPUs give the CPUs something to do instead of just fooling around.> - which version of OracleThis was Oracle 10g R2 on a FSC PrimePower (2 x 1350 MHz, 4 GB RAM) with Solaris 10 06/06. Currently I have only an older Xeon box with SAN connectivity for Solaris/x86 but Oracle 10gR2 still isn''t released on 32bit x86 platforms[1]. 10gR1 needed much more space in the TEMP tablespace so I couldn''t complete the tests. [1] http://www.oracle.com/technology/software/products/database/oracle10g/index.html Daniel
Daniel, This is cool. I''ve convinced my DBA to attempt the same stunt. We are just starting with the testing so I''ll post results as I get them. Will appreciate if you can share your zpool layout. -- Just me, Wire ... On 8/26/06, Daniel Rock <solaris at deadcafe.de> wrote:> przemolicc at poczta.fm schrieb: > > Hi all, > > > > Does anybody use Oracle on ZFS in production (but not as a > > background/testing database but as a front line) ? > > I haven''t used in production yet - but I''m planning by the end of the year. I > did some performance stress tests on ZFS vs. UFS+SVM. For testing I used the > TPC-H benchmark as base and tested on SAN (Symmetrix DMX1000 storage). > > The test consists of four parts: > > 1. creation of database (CREATE DATABASE ...; @catalog.sql; @catproc.sql) > 2. Importing Test data with sqlldr (TPC-H target size of 2 GB) > 3. Run a single instance of the TPC-H benchmark > 4. Run 4 (2 * NCPU) instances of the TPC-H benchmark > > Test 1: UFS performed slightly better > Test 2: performance of UFS vs. ZFS was similar > Test 3: ZFS was somewhat faster (<10%) > Test 4: ZFS was much faster (>20%) > > Summing up the runtime of all 4 tests ZFS was ~12% faster (248 mins to 280 mins). > > > > I am interesting especially in: > > - how does it behave after a long time of using it. Becasue COW nature > > of ZFS I don''t know how it influences performance of queries. > > I filled up the pool with random data so that the pool reached 100% usage > during the benchmark (The benchmark needs a huge TEMP tablespace (with above > 2GB data TEMP grew up to 20GB), so you can expect a lot of write I/O) The > total performance dropped just by 1-2% > > > > - general opinion of such pair (ZFS + Oracle) > > Use compression and save a lot of space. Expect a space reduction of >50% by > using compression even with filled up tablespaces. Oracle just worked on ZFS, > no "strange" results. > > I had one panic, but this was related to the Emulex FC device driver, not to > ZFS (in the meantime a Emulex patch has been released (120222/120223), don''t > know if it fixes this panic) > > The total runtime didn''t change during my tests (<1%) if compression was > turned on compared to compression off. The CPU usage increased by ~15%. So if > you have fast CPUs give the CPUs something to do instead of just fooling around. > > > > - which version of Oracle > > This was Oracle 10g R2 on a FSC PrimePower (2 x 1350 MHz, 4 GB RAM) with > Solaris 10 06/06. > Currently I have only an older Xeon box with SAN connectivity for Solaris/x86 > but Oracle 10gR2 still isn''t released on 32bit x86 platforms[1]. 10gR1 needed > much more space in the TEMP tablespace so I couldn''t complete the tests. > > > [1] > http://www.oracle.com/technology/software/products/database/oracle10g/index.html > > > Daniel > _______________________________________________ > zfs-discuss mailing list > zfs-discuss at opensolaris.org > http://mail.opensolaris.org/mailman/listinfo/zfs-discuss >
Good start, I''m now motivated to run the same test on my server. My h/w config for the test will be: - E2900 (24 way x 96GB) - 2 2Gbps QLogic cards - 40 x 64GB EMC LUNs I''ll run the AOL deidentified clickstream database. It''ll primarily be a write test. I intend to use the following scenarios: - SVM/UFS (nologging, atimeoff, directio), data striped across all LUNs - ZFS (compress=OFF, atime=OFF) - ZFS (compress=ON, atime=OFF) - Oracle 10g Automatic Storage Management (ASM) I''ll keep the same Oracle 10g settings for all tests. I''m really interested in the comparison between ASM and ZFS, specially with the compress=ON option. In a DW environment like ours this could lead to HUGE savings. This message posted from opensolaris.org
On Fri, Aug 25, 2006 at 06:52:17PM +0200, Daniel Rock wrote:> przemolicc at poczta.fm schrieb: > >Hi all, > > > >Does anybody use Oracle on ZFS in production (but not as a > >background/testing database but as a front line) ?[ ... ] Robert and Daniel, How did you put oracle on ZFS: - one zpool + one filesystem - one zpool + many filesystems - a few zpools + one filesystem on each - a few zpools + many filesystem on each przemol
I have seen the best oracle performance on ZFS by
1. match the zfs record size to oracle db_block_size
2. use the default 128k record size for oracle logs.
3. If possible use a separate zpool for the oracle logs.
This is especially true if your workload has a high
write component to it.
Two other knobs help (vdev cache/stb and vq_max_pending)
but i do not recommend them right now. I believe work is
already underway to have better values out-of-the-box.
hth,
-neel
ps:If you want to know about vq_max_pending, please read
Eric Kustarz''s blog @
http://blogs.sun.com/erickustarz/entry/vq_max_pending
przemolicc at poczta.fm wrote:> On Fri, Aug 25, 2006 at 06:52:17PM +0200, Daniel Rock wrote:
>> przemolicc at poczta.fm schrieb:
>>> Hi all,
>>>
>>> Does anybody use Oracle on ZFS in production (but not as a
>>> background/testing database but as a front line) ?
>
> [ ... ]
>
> Robert and Daniel,
>
> How did you put oracle on ZFS:
> - one zpool + one filesystem
> - one zpool + many filesystems
> - a few zpools + one filesystem on each
> - a few zpools + many filesystem on each
>
> przemol
> _______________________________________________
> zfs-discuss mailing list
> zfs-discuss at opensolaris.org
> http://mail.opensolaris.org/mailman/listinfo/zfs-discuss
Hello przemolicc, Wednesday, August 30, 2006, 9:49:33 AM, you wrote: ppf> On Fri, Aug 25, 2006 at 06:52:17PM +0200, Daniel Rock wrote:>> przemolicc at poczta.fm schrieb: >> >Hi all, >> > >> >Does anybody use Oracle on ZFS in production (but not as a >> >background/testing database but as a front line) ?ppf> [ ... ] ppf> Robert and Daniel, ppf> How did you put oracle on ZFS: ppf> - one zpool + one filesystem ppf> - one zpool + many filesystems ppf> - a few zpools + one filesystem on each ppf> - a few zpools + many filesystem on each I used one pool + many file systems, with recordsize set for some of them. -- Best regards, Robert mailto:rmilkowski at task.gda.pl http://milek.blogspot.com
przemolicc at poczta.fm schrieb:> Robert and Daniel, > > How did you put oracle on ZFS: > - one zpool + one filesystem > - one zpool + many filesystems > - a few zpools + one filesystem on each > - a few zpools + many filesystem on eachMy goal was not to maximize tuning for ZFS but just compare ZFS vs. UFS for average setup. I just setup ZFS with default parameters (beside the tested ones: compression on/off; checksum off/on/sha256). So my ZFS setup was simply one zpool + one filesystem; no specific tuning on the record size. I did some preliminary tests on different ZFS recordsize and Oracle db_block_size/db_file_multiblock_read_count but had mixed results so I kept the defaults. If I have some time (and my test machine back) I might try different ZFS/Oracle performance parameters and their impact. Daniel
I finally got around to running a ''benchmark'' using the AOL clickstream data (2GB of text files and approximately 36 million rows). Here are the Oracle settings during the test. - Same Oracle settings for all tests - All disks in question are 32GB EMC hypers - I had the standard Oracle tablespaces on one ASM group consisting of 1disk - I created a tablespace using ASM on 10 disks - I created a tablespace using ZFS on 10 disks - I created a tablespace using ZFS with compression on 10 disks Test 1 (loading to ASM) I loaded the text file into Oracle using external table feature. Time 1m20s, system loads were in the 1-1.35 range. Test 2 (loading to ZFS) I loaded the text file into Oracle using external table feature. Time 1m16s, system loads were in the 1.13 range. Test 3 (loading from ASM to ASM) I loaded a new table from the just loaded Oracle table. Time 1m21s, system loads were in the 1-1.3 range. Test 4 (loading from ZFS to ZFS) I loaded a new table from the just loaded Oracle table. Time 1m20s, system loads were in the 1-1.3 range Test 5 (loading from ZFS to ZFS compress=ON) I loaded a new table from the just loaded Oracle table. Time 1m18s, system loads were in the 1-1.45 range, saw a compression in the 3.5-4x range. Throughout the tests I had other stuff running on the machine as well (1 additional database and 10g GridControl Repository). [b]All the tests yielded same results in my opinion.[/b] We''ll probably go with Oracle ASM because of its integration with other Oracle products/features. I''m not comfortable with ZFS enough to bet on it yet (I''ve only played with it for less than 2 months) while ASM has been around for 3 years. The other contributing factor is ASMs ability to rebalance the data when disks are added/removed. ZFS at this time doesn''t give a facility to remove drives when I''m not using mirrors (my problem is that all our disks are provisioned from EMC that are already protected), ASM does. While performing these tests I came across another (severe?) problem with ZFS that I''ll post as a separate entry. -Anantha- This message posted from opensolaris.org
One correction in the interest of full disclosure, tests were conducted on a machine that is different from my original post indicated a server configuration. Here''s the server config used in tests: - E25K domain (1 board: 4P/8Way x 32GB) - 2 2Gbps FC - MPxIO - Solaris 10 Update 2 (06/06); no other patches This message posted from opensolaris.org