Re: ra_pages and io rate

hopehope_123 schrieb: > Hi , > > When i strace the aio vps , no matter what the value i set for ra_pages > , the pread system calls always request 2kb. disk blocks form the disk > subsystem . How can i achive high io rates with informix. (For instance > , in oracle by using db_file_multi_block_read_count , i can achive 1mb. > per read.) > your friends are - parallel scans -> read about fragmentation - light scans -> find more info in the perf tuning guide - if you sorting also: the memory grant manager You will also find useful postings here from jack Parker and useful documents on the IBM web site On the web site search for informix and large database Very sorry that I am not near to my personal link collection to point you there directly :( dic_k -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe

Hi Richard , Lets say that i use pdq . Does pdq allow me to read data by using higher rates? Or does it just make the query run faster because of the parallelism? I can also run this query parallel in oracle . If we compare the sequental scan performance of both databases , oracle suggests to use db_file_multiblock_read_Count parameter which allows up to 1mb. per read and informix has ra_pages parameter still can read page_size blocks , can we say that oracle is better? For an example think about a 1Mb. table . If i use oracle in order to read , with proper setting i can read the whole table by issuing only 1 disk read. In informix , after setting the ra_xx parameter i still read multiple page size blocks. Art says that i had better no to try to optimize an informix query like oracle and i 100% agree with him , but what approach do i need to follow? By the way , The last version i use is informix 9.2 and in order to use parallel query i need to set pdqpriority . What i see with oracle 9.x is , executing parallel query seems much more simpler: select /*+parallel(a,8)*/ count(*) from customer . And informix pdq needs special administration . Is it still same with informix 10? Kind Regards, hope Richard Kofler wrote: > hopehope_123 schrieb: > > Hi , > > > > When i strace the aio vps , no matter what the value i set for ra_pages > > , the pread system calls always request 2kb. disk blocks form the disk > > subsystem . How can i achive high io rates with informix. (For instance > > , in oracle by using db_file_multi_block_read_count , i can achive 1mb. > > per read.) > > > > your friends are > - parallel scans -> read about fragmentation > - light scans -> find more info in the perf tuning guide > - if you sorting also: > the memory grant manager > > You will also find useful postings here from jack Parker > and useful documents on the IBM web site > On the web site search for informix and large database > Very sorry that I am not near to my personal link > collection to point you there directly :( > > dic_k > -- > Richard Kofler > SOLID STATE EDV > Dienstleistungen GmbH > Vienna/Austria/Europe

hopehope_123 schrieb: > Hi Richard , > > Lets say that i use pdq . Does pdq allow me to read data by using > higher rates? Or does it just make the query run faster because of the > parallelism? The answer to this is technical & complicated It is a YES! Depending on your OS ( I cannot speak about MS-DOS and derivates) only about Unices, and dependeing on your LUN configuration, and if you use raw devices, and if you do NOT have 'everything in 1 huge chunk', which is nowadays possible), and depending on your fragmentation strategy - we do NOT speak about fragment avoidance here - and depending on using light scans you will see the I/O rate of the paperware of your I/O subsystem provider -- VERY much to the surprise of their own tech labs ..... More than once I have been told that my benchmarking produces better values than their lab tests. Because You do not use huge blocks to read on the host side Because you do not overstress your LUN buffers, which always and everytime have an internal blocksize between 512 and 8192 Bytes (look into your Unix Kernel asyn I/O package source) but constantly deliver a high load which, much in contrast to Oracle, is highly tuneable by the numbers of fragments alone. Big O delivers a huge blocksize once. Then waits. Behind the curtain is a lot of action to be done to make it happen - but Big O admins are happy if they can read 80 MB / sec or ~ 8k I/Os per sec from an expensive I/O subsystem, whereas I am happy, when I can read 8k 2048 Byte pages per second from a cheap Escalade 9000 controller fed with bits from 16 cheap SATA2 disks :) Or when I can do 150k I/Os of size 2KB per to transfer from the cache of the I/O subsystem - on average, sustained That is - in short - the not so small difference. > I can also run this query parallel in oracle . If we compare the > sequental scan performance of both databases , oracle suggests to use > db_file_multiblock_read_Count parameter which allows up to 1mb. per > read and informix has ra_pages parameter still can read page_size > blocks , can we say that oracle is better? > > For an example think about a 1Mb. table . If i use oracle in order to > read , > with proper setting i can read the whole table by issuing only 1 disk > read. In informix , after setting the ra_xx parameter i still read > multiple page size blocks. Art says that i had better no to try to > optimize an informix query like oracle and i 100% agree with him , but > what approach do i need to follow? > > By the way , The last version i use is informix 9.2 and in order to > use parallel query i need to set pdqpriority . Set PDQPRIORITY to at least 2, if you only want your fragments in parallel. If you also need memory for a biggish incore sort (ORDER BY)then you should look into DS_TOTAL_MEMORY, and read about memory quantum and friends in the performance guide. You will want to set PDQPRIORITY so, that either you get queueing in the gates, or that you be able to run more than 1 parallel data query at the same time ( parallelism of parallelism ;) > What i see with oracle 9.x is , executing parallel query seems much > more simpler: So it looks to me that you are working with vapourware. (no offence intended!) Big O makes it look simple and does it wrong under the hood. Issuing one read I/O for a fragmented table with 10 fragments must lead to 10 I/Os or there cannot be any parallelism, this is a nobrainer. Now if your blocksize matches your total table size, what do you think will happen? The usual term is burst I/O load - and it is a common source of I/O performance degradation down to 20% of what would be posiible, if done the right way. Yes, only one fifth of what you have paid for! This is one of the many reasons why Veritas sells you direct-I/O for another big sack of dollars. Then it still looks simple but is not that wrong anymore under the hood. It is also the reason why one must throttle down the LUN params on Solaris when using some of the well known I/O subsystems. This is no joke! It is in the official prerequisites, much like kernel params in the IBM-INFORMIX machine notes. *sark on* Yeah, another way of flood control that is. Actually it stems from SunOs 4, and dates back to the year 1989 - lol *sark off* If you can, then do some testing on the very same harware config with INFORMIX 9.4 or 10 versus Oracle. You will see, what other have seen, and what I am not allowed to publish in public anymore. And a more general thing: In modern I/O (since the 1990ies) there is not much sequential I/O anymore, if not in very special context. The usual I/O is random. So please do not read figures like xxxx MB per second and calculate with these. Ask for the non published figures of How many start I/Os can I issue per second, without having to queue them up in the host into the queue of 'not immediately startable I/O' Then you can understand why we selling solid state disks, which have an I/O rate of 20k writes or 22K reads per second per device. That means if the HW can do parallel read from 2 devices you get a real 40k I/Os per sec from your disks - not from the cache of the subsystem! Typical for a hot SCSI disk is 500 I/Os per sec Typical for a hot SATA2 disk is 1000 I/Os per sec Typical for a cache of an I/O subsytsem is 12k to 18k per sec, depending on the memory chips used in the device. > > select /*+parallel(a,8)*/ count(*) from customer . > > And informix pdq needs special administration . Is it still same with > informix 10? Yes! And I hope it always will stay like this, because I need to be able to squeeze out the performance my customers dollars have paid for. *crosses fingers* > > > Kind Regards, > hope After this long speak, please consider that if you want performance, you have to know your queries your data the internals of your components If you want to have it easy, you have to spend a lot of money to be able to do your queries by using only a small percentage of what your HW and SW is able to do. It is much like motorcars in a country with speed limits everywhere ...... dic_k -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe

"Richard Kofler" <richard.kofler@chello.at> wrote > If you can, then do some testing on the very same > harware config with INFORMIX 9.4 or 10 versus > Oracle. > You will see, what other have seen, and what I am > not allowed to publish in public anymore. What do you mean by 'anymore' ? Is it because Oracle does not allow benchmark results to be published without their approval. It was always true. So why 'anymore'. what has changed recently. thanks. You can email me, if you want. rk-

rkusenet schrieb: > "Richard Kofler" <richard.kofler@chello.at> wrote > > >>If you can, then do some testing on the very same >>harware config with INFORMIX 9.4 or 10 versus >>Oracle. >>You will see, what other have seen, and what I am >>not allowed to publish in public anymore. > > > What do you mean by 'anymore' ? > Is it because Oracle does not allow benchmark results to be > published without their approval. It was always true. So > why 'anymore'. what has changed recently. one of the rather big I/O subsystem manufacturing companies threatened one of my small companies (not the one of this signature) to take them to court, should they post benchmark results ever again. We had to agreed, their attourneys beeing real professionals. They, in contrast to Big O, you will not give approval, but you will be bought as a company ..... dic_k -- Richard Kofler SOLID STATE EDV Dienstleistungen GmbH Vienna/Austria/Europe

Hi , Thanks for your replies , your postings are very helpful . Richard , i have more questions : -Because You do not use huge blocks to read on the host side -Because you do not overstress your LUN buffers, -which always and everytime have an internal blocksize -between 512 and 8192 Bytes (look into your Unix Kernel -asyn I/O package source) but constantly deliver a high load -which, much in contrast to Oracle, is highly tuneable -by the numbers of fragments alone. -Big O delivers a huge blocksize once. Then waits. -Behind the curtain is a lot of action to be done to make -it happen - but Big O admins are happy if they can read -80 MB / sec or ~ 8k I/Os per sec from an expensive -I/O subsystem, whereas I am happy, when I can read -8k 2048 Byte pages per second from a cheap -Escalade 9000 controller fed with bits from -16 cheap SATA2 disks :) -Or when I can do 150k I/Os of size 2KB per to transfer from -the cache of the I/O subsystem - on average, sustained -That is - in short - the not so small -difference. I need a clarification here. Two of the oracle systems which i am responsible for are datawarehouse systems .Since these are datawarehouse systems , my major io is 'random sequental io' . I mean majority of the users queries need to read the whole data in order to analyze , etc... On both of these systems i use emc , raid 10 .I want to give you more details about these systems and io rates for the queries. System 1: redhat linux advanced server , 4 cpu , 8gb ram , 1 hba , fibre channel. I can see 100MB. reads from this fibre channel. The oracle database server multiblock read count parameter is set to 1mb , so oracle requests 1mb. data from the os. (When i strace - truss the process , i see the pread system call requests 1mb. from the io subsystem.) The file system which oracle files reside is OCFS - oracle clustered file system and it uses Direct -io , no os buffering , (no page cache usage) , no kaio . After running the query for a big table ( for instance for an 10GB. table) , the below is what iostat shows : Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sdn 46835.00 0.00 963.60 1.40 53652.60 1.20 55.60 838159.62 122.32 1.01 100.00 The displayed values are in 512bytes sectors so according to the this output , the system has 963 read operations per second , reads about 26 mb. per second and each read operation reads about 22KB. data . This shows that although oracle requests 1mb. data from the disks , the system can only read 22kb. , and the device is 100% utilized with 963 read operations per second. System 2: This is sun solaris , has 1hba , fibre channel 2 cpus, 4gb. ram . Oracle files reside in UFS file system . Force directio is on , and i have tuned the solaris kernel cluster size in order to read higher rates. Same table (10gb) , same sql , and this is what io stat shows: r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device 103.7 0.0 93451.8 0.0 0.0 4.3 0.1 41.5 1 100 c2t16d65 Here i see 903kb. per read , only 103.7 read operaitons per second. The redhat system issues 963 read io operations ( 22kb. per read) whereas the sun system only requests 103 io operations (903 kb. per read) . Which is better than ? -Then you can understand why we selling -solid state disks, which have an I/O rate of -20k writes or 22K reads per second per device. -That means if the HW can do parallel read from -2 devices you get a real 40k I/Os per sec from -your disks - not from the cache of the subsystem! -Typical for a hot SCSI disk is 500 I/Os per sec -Typical for a hot SATA2 disk is 1000 I/Os per sec -Typical for a cache of an I/O subsytsem is -12k to 18k per sec, depending on the memory chips -used in the device. 963 small io against 103 big io . Which one is better? ( 103 big io runs much more faster than the 963 small io) -Now if your blocksize matches your total table size, what -do you think will happen? -The usual term is burst I/O load - and it is a common source -of I/O performance degradation down to 20% of what would -be posiible, if done the right way. Yes, only one fifth of -what you have paid for! -This is one of the many reasons why Veritas sells you -direct-I/O for another big sack of dollars. -Then it still looks simple but is not that wrong -anymore under the hood. -It is also the reason why one must throttle down -the LUN params on Solaris when using some of the -well known I/O subsystems. This is no joke! It is in -the official prerequisites, much like kernel params -in the IBM-INFORMIX machine notes. -*sark on* -Yeah, another way of flood control that is. -Actually it stems from SunOs 4, and dates back -to the year 1989 - lol -*sark off* Can you explain this little bit further? I could not see the performance degration on my sun system . ( May be i dont know how to see it ) Obnoxio , -No. They way that the two products approach their disk access is -completely different.. What kind of difference exist?( For os level io calls ,when i strace the oninit and oracle processes , i see they use pread calls .I think you mention other differences.) -Why would you want to read the entire table in one shot? Since this is a datawarehouse , the majority of accesses are full table scans , hash joins etc. Tables are huge (10gb. 15gb. etc..) Kind Regards, hope