Re: Online 7.11 Select Performance - Odd Results

In <49iarh$sst@panix.com> clay@panix.com (Clay Irving) writes: >I seem to remember reading something a week or so ago about some >strange performance problems in Online 7.1x select. Supposedly, >the problems were fixed in subsequent releases. Maybe so, but it looks >like we found another problem. So far, Informix Tech Support is stumped >(even though the problem is easily and dependably reproduceable). >This is the problem: >We have INFORMIX-Online Dynamic Server 7.11.UC1 running on >HP-UX v.10.01. >table_x (30 columns, 22000 rows), index on (column1,field2,field3) >SELECT * FROM table_x ORDER BY 1,2,3 (works fine) >SELECT * FROM table_x ORDER BY 1 (works fine) >SELECT * FROM table_x ORDER BY 3 (*EXTREMELY* long time) >SELECT * FROM table_x ORDER BY 15 (*EXTREMELY* long time) >SELECT column1,column, column3 ORDER BY 3 (works fine) >What do I mean by "*EXTREMELY* long time? On a dedicated 4-processor >HP K400, the query completes in approximately 10 minutes! >Even wierder: Move these queries into a distributed environment and >the *EXTREMELY* long query completely shuts down the TCP/IP pipe >between two systems -- Users can rlogin to the database server... >Has anyone seen anything like this, and most importantly, does anyone >have any suggestions? We determined what the problem is... Those two queries taking forever to run are not using the index, and a temp table is built for the sequential scan. The temp dbspace we were using was linked to block-based raw devices. We changed the link to character-based raw devices and look what happened: 1) DBSPACETEMP= SELECT * FROM table_x ORDER BY 1,2,3,4,5,6,7 [29 seconds][sequential scan, used root dbspace for creation of temporary tables] 2) DBSPACETEMP=tmpspace1 ( linked to character-based raw device ) SELECT * FROM table_x ORDER BY 1,2,3,4,5,6,7 [41 seconds][sequential scan, used tmpspace1 dbspace for creation of temporary tables] 3) DBSPACETEMP=tmpspace2 ( linked to block-based raw device ) SELECT * FROM table_x ORDER BY 1,2,3,4,5,6,7 [5 minutes 32 seconds][sequential scan, used tmpspace2 dbspace for creation of temporary tables] After this, one of our DBAs realized what the problem was: After I realized that what the problem was. I have found the technical information below in a third-party book ( Informix DBA's Survival Guide-Joe Lumbley) which should have been given us by Informix Technical Support. "... Notice also that the UNIX /dev/rdisk*** devices are used. These are the character based devices, not the block-based devices such as the /dev/disk** devices. If you have accidentally created your raw devices on block devices, you find that initial process of disk initialization take a long time. Subsequent accesses to the disk will take much longer than usual. This is because of the additional overhead of using the UNIX kernel services. Using the block devices will use the UNIX kernel buffer cache while using the character device will not.To guarantee that writes are flushed to disk in timely manner, you must use the character device. ..." Thanks to all who helped... -- - o - o Clay Irving (clay@panix.com), N2VKG o - o o AEC NYC ARES, New York County o - Deputy Radio Operator, NYC RACES, New York County - o - - Start --> http://www.panix.com/clay