Re: Help! Poor performance

Jeff Craig wrote: > > I am getting very bad performance on an unloaded system and > need some more ideas on what might be wrong. Platform: > Silicon Graphics Challenge XL > 8 250-Mhz CPUs > 2048 megabytes memory > Lots of disks > Informix Online DSA v7.12.uc3x2 > > If I use dbaccess to do the following: > unix> dbaccess db1 <<EOF > output to /dev/null > select * from consumer;> EOF > > I can sequentially scan 7.8 million rows in 45 minutes - not good!Depends on the rowsize of consumer: 100 Bytes * 7,8 million -> more than 743 MB -> 0,27 K per second 1000 Bytes * 7,8 million -> more than 7,2 GB -> 2,75 K per second the bigger your rowsize -> the better your performance :-) > I have look for waiters, locks, I/O, everything. The only thing > that I see when this query is executing is a single aio vp reading > big buffers (onstat -g iob). Since I have lots of memory, I have > made all memory-related parameters large in the hopes of gaining > good throughput for such sequential scans. This is a data warehouse > application, so sequential scans are not out of the ordinary.I think consumer is one of your "smaller" tables and even if it is only 100 Bytes p. row it is much more than your BUFFERS (app. 120 MB - PAGESIZE 2048 assumed). As you mentioned above sequential scans are not out of ordinary in a DW-App, so You can't run this from memory anyway. You should instead try to use parallelism in io to speed up your queries. Fragment your consumer table, if it isn't already done, and increase PDQPRIORITY step by step. After that you should see all AIOVP's working. In a next step You can examine the optimal number of AIOVP's ( saw you had some experiments on this already, but imho this doesnt work on single-query-tests whith PDQPRIORITY=0). Also try to get a newer version of your OS and INFORMIX, which does Asynchronous Kernel-IO. I don't know SGI's UNIX very well, but I think there should be a such a newer version. hope that helps martin - Dr. Materna GmbH | EMail. Martin.Berns@Materna.De Martin Berns | Tel.: +49 231/5599-231 Vosskuhle 37 | FAX: +49 231/5599-574 D-44141 Dortmund | X.400: S=BERNS;G=MARTIN;P=MATERNA;A=UMI-DE;C=DE