ecs50145@zach.fit.edu ( COLDENHOFF) (03/28/91)
I have a few questions about Informix On-Line and Informix ESQL/C
that I hope somebody can help me with...
My system is two Dec 5400 RISCs running Ultrix 4.1. I have set up On-Line to
use one 400M disk as a primary dbspace, and a separate 400M drive to
be a mirror. I have another disk, approx. 200M of which is used as
a different space, which is also mirrored to another drive. When I
try to get the status of either of these spaces, tbmonitor tells me
that a fraction of the primary is used (which I expect) and that ALL of
the mirror is used (which I am confused about). After going over the
docs many times, I cannot find any reference to this particular situation.
Does Informix actually go out and grab the entire mirror space? Or have
I made some error in configuration? By the way, these two spaces are
specified as two separate raw devices. The idea here is when CPU 1 fails,
CPU 2 takes over and has access to the mirrored data (both buses are wired
to the drives - they are not clustered).
Second question: I need to perform a large number of selects on a fairly
large table in a very short period of time. The very basic numbers here
are 5000 selects on a table with 250,000 rows, each row having 60 bytes.
One unique composite index set up on three columns. Each select returns
exactly one tuple. Essentially, I have the following:
...cut...
$ select *
into $host_var
from table_x
where ((col1 = $hv1) and (col2 = $hv2) and (col3 = $hv3));
...cut...
which is done 5000 times, getting a different row each time. This needs
to be done within two minutes. I haven't been able to do it in less than
three. I have set up shared memory to no swapping, with 4500 buffers
which forces a large amount of the data to be cached.
So, are there any weird quirks, kludges, or even simple advice to eek
out a few milliseconds? That time constraint is critical, and any and all
suggestions, no matter how simple are appreciated. I am contemplating
going into the pre-processed code and trying to trim that down also...
would I be wasting my time?
Final question: Are you still with me? There is very little technical
information provided in the Informix manuals. For instance, how are
shared memory buffers managed? Why is it that 45,000 buffered reads
are required to perform 5000 selects (ref. above question)? If these
are B-tree overheads, can they be trimmed down? Are there books that
can help me with these questions?? Is there by any chance any ftp sites
or other Internet sources of Informix information (tech specs, examples,
kludges, etc. )?
Thank you in advance for any and all information and/or pointers.
Please respond to: ecs50145@zach.fit.edu
--------------------------------------------------------------------------
Timity Coldenhoff ecs50145@zach.fit.edu
via Florida Institute of Technology Academic Computing Services
--------------------------------------------------------------------------markj@informix.com (Mark Jeske(Chicago Consultan)t) (03/29/91)
In article <2225@winnie.fit.edu> ecs50145@zach.UUCP ( COLDENHOFF) writes: > > >My system is two Dec 5400 RISCs running Ultrix 4.1. I have set up On-Line to >use one 400M disk as a primary dbspace, and a separate 400M drive to >be a mirror. I have another disk, approx. 200M of which is used as >a different space, which is also mirrored to another drive. When I >try to get the status of either of these spaces, tbmonitor tells me >that a fraction of the primary is used (which I expect) and that ALL of >the mirror is used (which I am confused about). After going over the >docs many times, I cannot find any reference to this particular situation. >Does Informix actually go out and grab the entire mirror space? Or have >I made some error in configuration? By the way, these two spaces are >specified as two separate raw devices. The idea here is when CPU 1 fails, >CPU 2 takes over and has access to the mirrored data (both buses are wired >to the drives - they are not clustered). > That is ok. tbstat -d will show free as 0 for mirrored chunks. It is simply intended to indicate that you can't save data here. Data is placed in the primary chunk and the system mirrors it to here. For instance, we had many users that developed shell scripts to total the free column output from tbstat with our earlier turbo product. Turbo of course didn't suport mirroring. If we displayed a free value for mirror chunks these scripts would be displaying incorrect totals. I'm not exactly clear what you are doing with the two machines. It sounds like they are accessing the same disks?. Be very careful in situations like this. Could you provide a better explanation? >Second question: I need to perform a large number of selects on a fairly >large table in a very short period of time. The very basic numbers here >are 5000 selects on a table with 250,000 rows, each row having 60 bytes. >One unique composite index set up on three columns. Each select returns >exactly one tuple. Essentially, I have the following: >... > >which is done 5000 times, getting a different row each time. This needs >to be done within two minutes. I haven't been able to do it in less than >three. I have set up shared memory to no swapping, with 4500 buffers >which forces a large amount of the data to be cached. Use the prepare statement here. If you are executing the select directly many times the optimizer is doing way to much work. By preparing the statement once and then executing into multiple times you should be able to get under 2 minutes. You should also try the set explain option to make sure the optimizer is using an index. If you're already under 3 minutes I'm sure it is, but it can't hurt to check. >Final question: Are you still with me? There is very little technical >information provided in the Informix manuals. For instance, how are >shared memory buffers managed? Why is it that 45,000 buffered reads >are required to perform 5000 selects (ref. above question)? If these >are B-tree overheads, can they be trimmed down? Are there books that >can help me with these questions?? Is there by any chance any ftp sites >or other Internet sources of Informix information (tech specs, examples, >kludges, etc. )? Well, we simply setup an array of buffers in shared memory with the number being what you have configured. We manage buffers just like you think we would. If we need to read a page from disk we first check the buffer pool. If it's there, simply return that. If not, then get an empty buffer, possibly flushing ones to disk that haven't been used for awhile, and read the page into it. If we need to write a page to disk simply do the oposite. First check to see if it is in the buffer pool and just update it. Changes in the buffer pool then get flushed to disk during a checkpoint. It's actually a little more complicated with the physical log being involved, but that is the basic idea. Sounds like you are a good candidate for our 'Online for DBA' class. Check with your local sales rep. Mark Jeske (Informix Consulting) 708-699-5850 markj@infmx.com