Build a new Informix Server - some questions

Hi Informixer, we want to build a new Informix Database Server. The goal is a high speed system which answeres the database queries especially in OLTP mode in a very short time. We have to use IDS2000 9.2x. The clients are running with Windows NT 4.0. The queries are send to the database engine via Informix-ODBC. In the beginning the DBMS has to serve up to 50 users, later it will be 100 - 150 users. There will be "read or write peaks" if the application asks for reading or writing or analysing the data of an production order. Do you agree that the following configuration is suitable: Processor: Intel Pentium III XEON 700 MHZ SLC : 1 MB Type : 4-way Processor Number : 2 RAM : 1 - 2 GB with ECC Raid 10 Operating System: SuSE Linux 6.x or 7.0 Some people say that it is no possible to use raw chunks with Linux. You have to build your chunks with Cooked Files. Is that really a disadvantage? Some people say that there are problems or limitations in taking advantage of the multiprocessors with Informix and Linux. Does anyone has experience therewith? What can be done force the IDS to spread certain threads to the available processors?. Is that possible and usable for OLTP or is that a feature (PDQ) only meaningful with Decision Support Queries. Any help will be appreciated. TIA Reinhard

"Reinhard Habichtsberg" <Reinhard.Habichtsberg@unilux.de> writes: > Hi Informixer, > > we want to build a new Informix Database Server. > The goal is a high speed system which answeres the database queries > especially in OLTP mode in a very short time. We have to use > IDS2000 9.2x. The clients are running with Windows NT 4.0. > The queries are send to the database engine via Informix-ODBC. > > In the beginning the DBMS has to serve up to 50 users, later it > will be 100 - 150 users. There will be "read or write peaks" > if the application asks for reading or writing or analysing the data > of an production order. > > Do you agree that the following configuration is suitable: > > Processor: Intel Pentium III XEON 700 MHZ > SLC : 1 MB > Type : 4-way Processor > Number : 2 > > RAM : 1 - 2 GB with ECC > > Raid 10 > > Operating System: SuSE Linux 6.x or 7.0 > > Some people say that it is no possible to use raw chunks with Linux. There's a patch for raw-devices, and "RedHat Enterprise Edition" comes with this and other assorted patches. > You have to build your chunks with Cooked Files. Is that really a disadvantage? Yes. It's slower (10-20% according to some), but I haven't seen any tests on Linux. You use the buffering of the OS, spending more memory and time on something the OS shouldn't bother with. And if your OS crash at the wrong time (paging mr. Murphy!) it could have told Informix that the pages was written when they actually was in the cache... > Some people say that there are problems or limitations in taking advantage > of the multiprocessors with Informix and Linux. Does anyone has experience therewith? Not yet;) We run IIF.2000 on a single Xeon 550 with 2MB cache and a Mylex AccelRAID 150, the bottleneck seems to be something else than the processor -but I haven't had the time to find out exactly what. If I find the time, I'll do some tests on my workstation (dual PIII-550 with 384 MB memory). > What can be done force the IDS to spread certain threads to the available processors?. Que? > Is that possible and usable for OLTP or is that a feature (PDQ) only meaningful with > Decision Support Queries. Que? again;) Thomas

Thomas Parsli <thomas.parsli@startsiden.no> schrieb in im Newsbeitrag: 3dk4cj54.fsf@dhcp-d134-76.nextel.no... > "Reinhard Habichtsberg" <Reinhard.Habichtsberg@unilux.de> writes: > [cutting] > > What can be done force the IDS to spread certain threads to the available processors?. > > Que? Maybe it is a very silly question: What do you mean with "Que" ? > > Is that possible and usable for OLTP or is that a feature (PDQ) only meaningful with > > Decision Support Queries. > > Que? again;) > > Thomas Reinhard

"Reinhard Habichtsberg" <Reinhard.Habichtsberg@unilux.de> writes: > Thomas Parsli <thomas.parsli@startsiden.no> schrieb in im Newsbeitrag: 3dk4cj54.fsf@dhcp-d134-76.nextel.no... > > "Reinhard Habichtsberg" <Reinhard.Habichtsberg@unilux.de> writes: > > > [cutting] > > > What can be done force the IDS to spread certain threads to the available processors?. > > > > Que? > > Maybe it is a very silly question: What do you mean with "Que" ? > > > > > Is that possible and usable for OLTP or is that a feature (PDQ) only meaningful with > > > Decision Support Queries. > > > > Que? again;) > > > > Thomas > > Reinhard Forgive me, I'm from Barcelona? OK: What? Thomas

>> What can be done force the IDS to spread certain threads to the available processors?. > Que? How can I say it in English: For example: your task is to write a production order to the database. That causes a series of sql-statements: Write this row, read that row, compare next row etc. My impression is that although we have a multi-processor system the sequence of sql statements is handled only by one processor. I understand that from the logical point of view the SQL's has to be handled sequencial. That can all be done by one processor. I look for a way to say Informix DBMS to split a certain query and spread (or should I say "distribute") it to the available processors. I expect an immense increase of speed if that could be done. >> Is that possible and usable for OLTP or is that a feature (PDQ) only meaningful with >> Decision Support Queries. > Que? again;) This sentence refers to Parallel Data Query (PDQ) which I understand as an instrument to distribute queries to more than one processor. The intention of my question is: Is PDQ only useable with Decision Support Queries (great number of rows, sorts etc.) or is PDQ also applicable in Online Transaction Processing (OLTP) and how can I make use of that feature. > Forgive me, I'm from Barcelona? Really? Nice! Reinhard Thomas Parsli <thomas.parsli@startsiden.no> schrieb in im Newsbeitrag: sns43xxc.fsf@dhcp-d134-76.nextel.no... > "Reinhard Habichtsberg" <Reinhard.Habichtsberg@unilux.de> writes: > > > Thomas Parsli <thomas.parsli@startsiden.no> schrieb in im Newsbeitrag: 3dk4cj54.fsf@dhcp-d134-76.nextel.no... > > > "Reinhard Habichtsberg" <Reinhard.Habichtsberg@unilux.de> writes: > > > > > [cutting]

On 17 Aug 2000 11:49:11 +0200, Thomas Parsli <thomas.parsli@startsiden.no> wrote: > You have to build your chunks with Cooked Files. Is that really a disadvantage? >>Yes. >>It's slower (10-20% according to some), but I haven't seen any tests on >>Linux. You use the buffering of the OS, spending more memory and time >>on something the OS shouldn't bother with. And if your OS crash at the >>wrong time (paging mr. Murphy!) it could have told Informix that the >>pages was written when they actually was in the cache... So, What'd happen with data ??!! Could it make the fast recovery correctly ? Regards, Manel Falcó, Semic, S.A. http://www.semic.es LLeida-Catalonia-Spain PS: Thomas Parsli ? Your name sounds not very "catalan", indeed ...

manel@semic.es (Manel Falcó i Aige) writes: > On 17 Aug 2000 11:49:11 +0200, Thomas Parsli > <thomas.parsli@startsiden.no> wrote: > > > You have to build your chunks with Cooked Files. Is that really a disadvantage? > > >>Yes. > >>It's slower (10-20% according to some), but I haven't seen any tests on > >>Linux. You use the buffering of the OS, spending more memory and time > >>on something the OS shouldn't bother with. And if your OS crash at the > >>wrong time (paging mr. Murphy!) it could have told Informix that the > >>pages was written when they actually was in the cache... > So, What'd happen with data ??!! Could it make the fast recovery > correctly ? Maybe, maybe not... Maybe Art has some hands-on experiences with this kind of fun? > Regards, > Manel Falcó, > Semic, S.A. > http://www.semic.es > LLeida-Catalonia-Spain > > PS: Thomas Parsli ? Your name sounds not very "catalan", indeed ... Que? Thomas

Hi, I saw an article recently that said the 2.4 Kernel officially supports raw disks: http://linuxtoday.com/news_story.php3?ltsn=2000-05-13-003-04-NW-LF-KN "One completely new feature in the Linux 2.4 kernel is the implementation of a "raw" I/O device. A raw device is one whose accesses are not handled through the caching layer, instead going right to the low-level device itself. A raw device could be used in cases where a sophisticated application wants complete control over how it does data caching and the expense of the usual cache is not wanted. Alternatively, a raw device could be used in data critical situations where we want to ensure that the data gets written to the disk immediately so that, in the event of a system failure, no data will be lost. Previous incarnations of this support were not fit for inclusion as they required literally doubling the number of device nodes so that every block device would also have a raw device node. (This is the implementation that many commercial UNIXes use.) The current implementation uses a pool of device nodes which can be associated with any arbitrary block device. " Tim Thomas Parsli wrote: > > manel@semic.es (Manel Falcó i Aige) writes: > > > On 17 Aug 2000 11:49:11 +0200, Thomas Parsli > > <thomas.parsli@startsiden.no> wrote: > > > > > You have to build your chunks with Cooked Files. Is that really a disadvantage? > > > > >>Yes. > > >>It's slower (10-20% according to some), but I haven't seen any tests on > > >>Linux. You use the buffering of the OS, spending more memory and time > > >>on something the OS shouldn't bother with. And if your OS crash at the > > >>wrong time (paging mr. Murphy!) it could have told Informix that the > > >>pages was written when they actually was in the cache... > > > So, What'd happen with data ??!! Could it make the fast recovery > > correctly ? > > Maybe, maybe not... > Maybe Art has some hands-on experiences with this kind of fun? > > > Regards, > > Manel Falcó, > > Semic, S.A. > > http://www.semic.es > > LLeida-Catalonia-Spain > > > > PS: Thomas Parsli ? Your name sounds not very "catalan", indeed ... > > Que? > > Thomas -- . .- .-- .--- .---- Tim Schaefer .----- tschaefe@bellsouth.net .---- http://www.inxutil.com .--- .-- .- .

Tim Schaefer <tschaefe@bellsouth.net> writes: > Hi, > > I saw an article recently that said the 2.4 Kernel > officially supports raw disks: > > http://linuxtoday.com/news_story.php3?ltsn=2000-05-13-003-04-NW-LF-KN Yes it does, but there's still some issues in the pre/test versions. Thomas

"Manel Falcó i Aige" wrote: > > On 17 Aug 2000 11:49:11 +0200, Thomas Parsli > <thomas.parsli@startsiden.no> wrote: > > > You have to build your chunks with Cooked Files. Is that really a disadvantage? > > >>Yes. > >>It's slower (10-20% according to some), but I haven't seen any tests on > >>Linux. You use the buffering of the OS, spending more memory and time > >>on something the OS shouldn't bother with. And if your OS crash at the > >>wrong time (paging mr. Murphy!) it could have told Informix that the > >>pages was written when they actually was in the cache... > So, What'd happen with data ??!! Could it make the fast recovery > correctly ? Actually this cannot happen unless the OS's implementation of O_SYNC mode files is damaged. When informix sets up to open a chunk it stats the file to determine whether it is COOKED or RAW. If COOKED it opens the file in O_SYNC mode so that ALL writes a fully synchronous and committed to disk before the write system call returns. Your data is ALWAYS safe with Informix, that is not the issue between COOKED files, COOKED devices, and RAW devices it is performance. COOKED devices are 15-25% slower than RAW devices and COOKED filesystem files are an additional 10-15% slower than COOKED devices. This has held in my testing on many OSes and many FS implementations over the years including some of the newly touted high speed filesystems that are "as fast as RAW". Art S. Kagel

"Art S. Kagel" <kagel@bloomberg.net> writes: > "Manel Falc' i Aige" wrote: > > > > On 17 Aug 2000 11:49:11 +0200, Thomas Parsli > > <thomas.parsli@startsiden.no> wrote: > > > > > You have to build your chunks with Cooked Files. Is that really a disadvantage? > > > > >>Yes. > > >>It's slower (10-20% according to some), but I haven't seen any tests on > > >>Linux. You use the buffering of the OS, spending more memory and time > > >>on something the OS shouldn't bother with. And if your OS crash at the > > >>wrong time (paging mr. Murphy!) it could have told Informix that the > > >>pages was written when they actually was in the cache... > > So, What'd happen with data ??!! Could it make the fast recovery > > correctly ? > > Actually this cannot happen unless the OS's implementation of O_SYNC > mode files is damaged. When informix sets up to open a chunk it stats > the file to determine whether it is COOKED or RAW. If COOKED it opens > the file in O_SYNC mode so that ALL writes a fully synchronous and > committed to disk before the write system call returns. Thank's Art, insightfull as ever:) I believe there's been some discussion on O_SYNC in the 2.4 kernels, but don't remember the problem. > Your data is > ALWAYS safe with Informix, that is not the issue between COOKED files, > COOKED devices, and RAW devices it is performance. COOKED devices are > 15-25% slower than RAW devices and COOKED filesystem files are an > additional 10-15% slower than COOKED devices. This has held in my > testing on many OSes and many FS implementations over the years > including some of the newly touted high speed filesystems that are "as > fast as RAW". Have you done any comparisons between Windows NT and ie. Solaris? Thomas

Thomas Parsli wrote: > > "Art S. Kagel" <kagel@bloomberg.net> writes: > > > "Manel Falcó i Aige" wrote: > > > > > > On 17 Aug 2000 11:49:11 +0200, Thomas Parsli > > > <thomas.parsli@startsiden.no> wrote: [SNIP] > > Your data is > > ALWAYS safe with Informix, that is not the issue between COOKED files, > > COOKED devices, and RAW devices it is performance. COOKED devices are > > 15-25% slower than RAW devices and COOKED filesystem files are an > > additional 10-15% slower than COOKED devices. This has held in my > > testing on many OSes and many FS implementations over the years > > including some of the newly touted high speed filesystems that are "as > > fast as RAW". > > Have you done any comparisons between Windows NT and ie. Solaris? Except for this workstation, which I have to have to run the mandated office suite and Open Bloomberg software, I avoid NT like plague, thanks. I have tested RAW -vs- COOKED DEV -vs- COOKED FILE on Solaris with Solaris' high speed FS and with EMC's high speed FS. The differences were on the low end but not substantially better than good UNIX filesystems I'd tested over the years. When you do these kinds of test, you have to make the test as much like Informix's accesses are likely to be as possible. Use 2K or 4K and 8K or 16K writes and 2K or 4K reads, intermix reads and writes and random reads and writes with long sequential blocks of reads as well as long sequential writes to simulate checkpoints. Be sure to make all COOKED I/O in O_SYNC mode. Art S. Kagel