Re: Best practices for IDS on virtual machine?

Thanks for the quick replies. After a quick look at what the IDS 12.10 admin guide says, I'm thoroughly confused: "Important: While you must use raw disk devices on UNIX to achieve better performance, recent advances in I/O caching for cooked writes can provide similar if not better performance. [...] If optimum performance is unimportant, you can configure the database server to store data in cooked files. Cooked files are easier to set up than raw disk devices." This is a little contradictory in itself. Do raw devices still deliver best IO performance or not? On the other hand, I am not even sure that I would be able to use raw devices in the virtual environment. If I go the route with ext2 filesystem and cooked files, what is the best strategy: Use one big ext2-formated partition and create a file for each chunk, or use several partitions with just one chunk file in each partition? Regards, Richard

Hi Richard , check this practical test about the effect of the cooked file cache (performance focus) : | tablename = my_table | size allocated = 3168 MB | size used = 2974 MB | rows = 33.120.000 I reconfigure my test instance with only 40MB of 4k pages buffer (where the data is allocated) | $ onstat - | IBM Informix Dynamic Server Version 11.70.FC6 -- On-Line -- Up 00:05:43 -- 688536 Kbytes | $ onstat -b | grep buff | 0 modified, 10000 total, 16384 hash buckets, 2048 buffer size | 0 modified, 10000 total, 16384 hash buckets, 4096 buffer size | 0 modified, 10000 total, 16384 hash buckets, 8192 buffer size The machine: Opensuse 12.2 with 4GB memory running over vmware ESXi with 1 SATA disk (this is a desktop, where I use for tests) the chunks are into ext2 partition , but I'm not using DIRECT_IO , where will make all difference here... because with it will behave as RAW . | $ mount | grep ifx | /dev/mapper/vgifxdados-lvifxdisco1 on /ifxdados type ext2 (rw,noatime) Here I clear the Linux cache : | jdivm06:~ # echo 3 > /proc/sys/vm/drop_caches This output is from "dstat -tmd 5" utility (is a SAR improved) | ----system---- ------memory-usage----- -dsk/total- | time | used buff cach free| read writ | 12-04 20:29:36| 429M 28.6M 2384M 1022M| 211k 75k | 12-04 20:29:41| 429M 28.6M 2384M 1022M| 0 0 | 12-04 20:29:46| 429M 28.6M 2384M 1022M| 0 0 | 12-04 20:29:51| 215M 264k 298M 3350M| 16k 0 <<<< drop caches run here | 12-04 20:29:54| 214M 264k 298M 3351M|5461B 0 I run this select forcing a full scan, remember: 2.9 Gb will be read from disk at least and the engine have only 40 MB of buffers. I don't know why the output of my "time" command become all together on the same line, anyway you can see , took 1 minute to run. | $ echo "select {+full (my_table)} count(1) from my_table" | time dbaccess testedb | Database selected. | (count) | 33120929 | 1 row(s) retrieved. | Database closed. | 0.00 user 0.00 system 1:02.78 elapsed 0%CPU (0 avgtext+0avgdata 11248maxresident)k | 0inputs+0outputs (0major+781minor)pagefaults 0swaps dstat output : Check the memory usage and disk read. | ----system---- ------memory-usage----- -dsk/total- | time | used buff cach free| read writ | 12-04 20:34:46| 214M 232k 320M 3329M| 958k 0 | 12-04 20:34:51| 213M 232k 320M 3330M| 0 0 | 12-04 20:34:56| 217M 336k 455M 3192M| 21M 0 <<<<< start the select here | 12-04 20:35:01| 219M 636k 755M 2888M| 60M 0 | 12-04 20:35:06| 217M 852k 970M 2675M| 43M 0 | 12-04 20:35:11| 217M 1032k 1151M 2494M| 36M 0 | 12-04 20:35:16| 217M 1120k 1238M 2407M| 17M 0 | 12-04 20:35:21| 218M 1160k 1284M 2360M|9162k 401k | 12-04 20:35:26| 217M 1440k 1556M 2088M| 54M 345k | 12-04 20:35:31| 217M 1684k 1801M 1843M| 49M 0 | 12-04 20:35:36| 218M 1928k 2044M 1600M| 49M 0 | 12-04 20:35:41| 218M 2264k 2379M 1264M| 67M 0 | 12-04 20:35:46| 219M 2472k 2587M 1054M| 42M 0 | 12-04 20:35:51| 219M 2720k 2839M 803M| 50M 0 | 12-04 20:35:56| 220M 3108k 3224M 416M| 77M 0 <<<<<< finished here | 12-04 20:36:01| 219M 3200k 3319M 322M| 19M 0 | 12-04 20:36:06| 220M 3200k 3319M 322M| 0 0 | 12-04 20:36:11| 220M 3200k 3319M 322M| 0 211k | 12-04 20:36:16| 220M 3200k 3319M 322M| 0 0 You must agree we will not use much buffer pool from engine right. So if you go with RAW devices or DIRECT_IO here and run the same statemente again, will take the same or close time. With cooked file without DIRECT_IO , take only 9 seconds... | $ echo "select {+full (my_table)} count(1) from my_table" | time dbaccess testedb | Database selected. | (count) | 33120929 | 1 row(s) retrieved. | Database closed. | | 0.00user 0.00system 0:09.23elapsed 0%CPU (0avgtext+0avgdata 11248maxresident)k | 0inputs+0outputs (0major+781minor)pagefaults 0swaps dstat output : | ----system---- ------memory-usage----- -dsk/total- | time | used buff cach free| read writ | 12-04 20:37:16| 220M 3200k 3319M 321M| 0 0 | 12-04 20:37:21| 220M 3200k 3319M 322M| 0 0 | 12-04 20:37:26| 220M 3200k 3319M 322M| 0 0 | 12-04 20:37:31| 220M 3200k 3322M 318M| 729k 0 <<< select run here | 12-04 20:37:36| 219M 3200k 3329M 312M|1326k 0 | 12-04 20:37:41| 219M 3200k 3329M 312M| 0 0 | 12-04 20:37:46| 219M 3200k 3329M 312M| 0 0 This occur because the engine read the cooked file which is on Linux cache... Running the test for the third and last time... now clearing the cache again... | jdivm06:~ # echo 3 > /proc/sys/vm/drop_caches | ----system---- ------memory-usage----- -dsk/total- | time | used buff cach free| read writ | 12-04 20:37:56| 219M 3200k 3329M 312M| 0 0 | 12-04 20:38:01| 219M 3200k 3329M 312M| 0 0 | 12-04 20:38:06| 220M 196k 2356M 1287M| 0 0 <<<< drop caches here.... | 12-04 20:38:11| 217M 196k 339M 3307M| 74k 200k we back to 1 minute... | $ echo "select {+full (my_table)} count(1) from my_table" | time dbaccess testedb | Database selected. | (count) | 33120929 | 1 row(s) retrieved. | Database closed. | 0.00user 0.00system 0:56.56elapsed 0%CPU (0avgtext+0avgdata 11232maxresident)k | 9080inputs+0outputs (11major+769minor)pagefaults 0swaps And all was read again from the disk : | ----system---- ------memory-usage----- -dsk/total- | time | used buff cach free| read writ | 12-04 20:38:16| 217M 196k 339M 3307M| 0 0 | 12-04 20:38:21| 217M 196k 339M 3307M| 0 0 | 12-04 20:38:26| 217M 196k 339M 3307M| 10k 0 | 12-04 20:38:31| 217M 260k 420M 3226M| 16M 0 <<< select start here | 12-04 20:38:36| 216M 472k 635M 3012M| 43M 0 | 12-04 20:38:41| 218M 868k 1028M 2617M| 79M 0 | 12-04 20:38:46| 217M 1232k 1389M 2255M| 72M 0 | 12-04 20:38:51| 218M 1584k 1739M 1905M| 70M 0 | 12-04 20:38:56| 218M 1784k 1943M 1701M| 41M 254k | 12-04 20:39:01| 218M 1860k 2019M 1625M| 15M 0 | 12-04 20:39:06| 218M 1980k 2139M 1504M| 24M 0 | 12-04 20:39:11| 218M 2184k 2341M 1302M| 40M 0 | 12-04 20:39:16| 219M 2556k 2714M 928M| 75M 0 | 12-04 20:39:21| 220M 2932k 3088M 552M| 75M 0 | 12-04 20:39:26| 219M 3136k 3323M 318M| 47M 0 <<< finish here | 12-04 20:39:31| 220M 3136k 3323M 317M| 0 0 | 12-04 20:39:36| 220M 3136k 3323M 318M| 0 0 But,don't forget.. if you use OS cache and occur a power off or crash on you OS ,you can loose more information and have more chance to go with a corrupt database. Regards Cesar 2013/4/12 R. Spitz <rspitz.md@googlemail.com> > Thanks for the quick replies. After a quick look at what the IDS 12.10 > admin guide says, I'm thoroughly confused: > > "Important: While you must use raw disk devices on UNIX to achieve better > performance, recent advances in I/O caching for cooked writes can provide > similar if n

Best performance possible = RAW. But RAW are not as easy to use as cooked files, specialy if your environemtn is segregated, meaning, you - the DBA - don't have access to root. But If I'm not confusing posts, you mentioned 3GB of data.... Unlesee you use a very small ammount of memory, there's no way you can get bad performance with that :) Regards On Fri, Apr 12, 2013 at 5:14 PM, R. Spitz <rspitz.md@googlemail.com> wrote: > Thanks for the quick replies. After a quick look at what the IDS 12.10 > admin guide says, I'm thoroughly confused: > > "Important: While you must use raw disk devices on UNIX to achieve better > performance, recent advances in I/O caching for cooked writes can provide > similar if not better performance. [...] If optimum performance is > unimportant, you can configure the database server to store data in cooked > files. Cooked files are easier to set up than raw disk devices." > > This is a little contradictory in itself. Do raw devices still deliver > best IO performance or not? On the other hand, I am not even sure that I > would be able to use raw devices in the virtual environment. > > If I go the route with ext2 filesystem and cooked files, what is the best > strategy: Use one big ext2-formated partition and create a file for each > chunk, or use several partitions with just one chunk file in each partition? > > Regards, Richard > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently...