Re: Still Slow LOAD & SQL
1. And what is the query plan in sqexplain.out for OPTCOMPIND=0 ?
2. What is the read %cached in onstat -p?
3. Can you try to increase the RA_PAGES ?
Best Regards,
Octav
On Tue, Mar 09, 1999 at 04:13:00PM -0200, Sebastian Paul Avarvarei wrote:
> Still me, with the same problem. I got some advices already that helped
> me (thanks to all of you). But I feel that Informix could do more.
>
> The query:
> select customer.company, sum(stock.unit_price*items.quantity) as tot_order
> from orders, items, stock, customer
> where customer.customer_num=orders.customer_num and> orders.order_num=items.order_num and items.stock_num=stock.stock_num
> and items.manu_code=stock.manu_code
> group by customer.company;
>
> It runs now in 2 minutes (comparing to 00:45 for mssql). There are 100,000 records in
> orders and 600,000 in items. The structure of the database is from the classic
> STORES7 sample database.
>
> I tried several versions in ONCONFIG. So far I get best results
> with BUFFERS=1,000 , SHMVIRTSIZE=61,440 , DS_TOTAL_MEMORY=55296, OPTICOMPIND=2
> I'll attach at the end the ONCONFIG.
>
> Also I tried to follow the recomandations for UPDATE STATS:
> HIGH FOR ORDERS(ORDER_NUM)
> HIGH FOR ORDERS(CLIENT_NUM)
> HIGH FOR ITEMS(ORDER_NUM)
> HIGH FOR ITEMS(STOCK_NUM)
> HIGH FOR ITEMS(MANU_CODE)
> MEDIUM FOR ITEMS(STOCK_NUM,MANU_CODE)
> (Probably it's not enogh, but in the documentations I couldn't find any mention
> if there is any order on which the UPDATE STATS must be issued and if one UPDATE
> overwrites another).
>
> I have indexes on:
> orders (order_num)
> orders (customer_num)
> items (item_num,order_num)
> items (order_num)
> items (stock_num, manu_code)
>
> Art suggested increasing the BUFFERS to 14,000. I tried and it didn't work
> because the system is a pure DSS (only read-only SELECTS on big tables). Also
> OPTICOMPIND=0 gave worst results (around 3:30 minutes)
>
> Could you suggest other parameters I should change and some values for them?
>
> Thank you very much and sorry for insisting on this but it is very
> important for me.
>
> Thank you!
> Sebastian Paul A.
> E-mail: proteus@romus.com
>
> #**************************************************************************
> #
> # INFORMIX SOFTWARE, INC.
> #
> # Title: onconfig.std
> # Description: Informix Dynamic Server Configuration Parameters
> #
> #**************************************************************************
>
> # Root Dbspace Configuration
>
> ROOTNAME rootdbs # Root dbspace name
> ROOTPATH D:\\IFMXDATA\\ol_informix_tnt\\rootdbs_dat.000 # Path for device containing root dbspace
> ROOTOFFSET 0 # Offset of root dbspace into device (Kbytes)
> ROOTSIZE 30720 # Size of root dbspace (Kbytes)>
> # Disk Mirroring Configuration Parameters
>
> MIRROR 0 # Mirroring flag (Yes = 1, No = 0)
> MIRRORPATH # Path for device containing mirrored root
> MIRROROFFSET 0 # Offset into mirrored device (Kbytes)>
> # Physical Log Configuration
>
> PHYSDBS rootdbs # Location (dbspace) of physical log
> PHYSFILE 2000 # Physical log file size (Kbytes)>
> # Logical Log Configuration
>
> LOGFILES 10 # Number of logical log files
> LOGSIZE 500 # Logical log size (Kbytes)> LOG_BACKUP_MODE MANUAL # Logical log backup mode (MANUAL, CONT)
>
> # Diagnostics
>
> MSGPATH C:\\INFORMIX\\ol_informix_tnt.log # System message log file path
> CONSOLE C:\\INFORMIX\\conol_informix_tnt.log # System console message path
> ALARMPROGRAM # Alarm program path>
> # System Diagnostic Script.
> # SYSALARMPROGRAM - Full path of the system diagnostic script (e.g.
> # c:\\informix\\etc\\evidence.bat.) Set this parameter
> # if you want a different Diagnostic Script than
> # {INFORMIXDIR}\\etc\\evidence.bat, which is default.
>
> # System Archive Tape Device
>
> TAPEDEV c:\\informix\\tapes\\ltapedev # Tape device path
> TAPEBLK 16 # Tape block size (Kbytes)
> TAPESIZE 1024000 # Maximum amount of data to put on tape (Kbytes)>
> # Log Archive Tape Device
>
> LTAPEDEV c:\\informix\\tapes\\ltapedev # Log tape device path
> LTAPEBLK 16 # Log tape block size (Kbytes)
> LTAPESIZE 1024000 # Max amount of data to put on log tape (Kbytes)>
> # Optical
>
> STAGEBLOB # Informix Dynamic Server/Optical staging area
> OPTICAL_LIB_PATH # Location of Optical Subsystem driver DLL
>
> # System Configuration
>
> SERVERNUM 0 # Unique id corresponding to a server instance> stance
> DBSERVERNAME ol_informix_tnt # Name of default Dynamic Server
> DBSERVERALIASES # List of alternate dbservernames
> NETTYPE onsoctcp,1,,NET # Override sqlhosts nettype parameters
> DEADLOCK_TIMEOUT 60 # Max time to wait of lock in distributed env.
> RESIDENT 1 # Forced residency flag (Yes = 1, No = 0)
>
> MULTIPROCESSOR 0 # 0 for single-processor, 1 for multi-processor
> NUMCPUVPS 1 # Number of user (cpu) vps
> SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one
>
> NOAGE 0 # Process aging
> AFF_SPROC 0 # Affinity start processor
> AFF_NPROCS 0 # Affinity number of processors>
> # Shared Memory Parameters
>
> LOCKS 2000 # Maximum number of locks
> BUFFERS 1000 # Maximum number of shared buffers
> NUMAIOVPS 1 # Number of IO vps
> PHYSBUFF 32 # Physical log buffer size (Kbytes)
> LOGBUFF 32 # Logical log buffer size (Kbytes)> LOGSMAX 20 # Maximum number of logical log files
> CLEANERS 1 # Number of buffer cleaner processes
> SHMBASE 0xC000000L # Shared memory base address
> SHMVIRTSIZE 61440 # initial virtual shared memory segment size
> SHMADD 8192 # Size of new shared memory segments (Kbytes)
> SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
> CKPTINTVL 300 # Check point interval (in sec)
> LRUS 8 # Number of LRU queues
> LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaning limit
> LRU_MIN_DIRTY 50 # LRU percent dirty end cleaning limit
> LTXHWM 50 # Long transaction high water mark percentage
> LTXEHWM 60 # Long transaction high water mark (exclusive)
> TXTIMEOUT 300 # Transaction timeout (in sec)
> STACKSIZE 32 # Stack size (Kbytes)>
> # System Page Size
> # BUFFSIZE - Dynamic Server no longer supports this configuration parameter.
> # To determine the page size used by Dynamic Server on your platform
> # see the last line of output from the command, 'onstat -b'.
>
>
> # Recovery Variables
> # OFF_RECVRY_THREADS:
> # Number of parallel worker threads during fast recovery or an offline restore.
> # ON_RECVRY_THREADS:
> # Number of parallel worker threads during an online restore.
>
> OFF_RECVRY_THREADS 10 # Default number of offline worker threads
> ON_RECVRY_THREADS 1 # Default number of online worker threads>
> # Data Replication Variables
> # DRAUTO: 0 manual, 1 retain type, 2 reverse type
> DRAUTO 0 # DR automatic switchover
> DRINTERVAL 30 # DR max time between DR buffer flushes (in sec)>