Re: Tuning Online ?
In article <CJMLqt.DxM@ibg1.gtn.com>, ado@ibg1.gtn.com (Christoph Adomeit) writes: |> |> Hi there, |> maybe there is someone out here who has an idea what can be tuned here. |> It's a Sco-Unix 486/50 Machine with 64 MB of RAM. It's a dedicated |> database server and serves for about 30 Users, but they use it heavily in |> a large business Application. |> From my point of view it's unbelieveable fast, but the customer would like |> it to be faster in some special selects, but these selects are complicated |> and absolutely optimized using indices and no temporary files:-(. |> |> How large would be the effect of unloading the database and recreating it |> with optimized extent sizes ? Some tables meanwhile have more than 30 |> extents ? Small BUT important! Performance of the engine goes down greatly when extents go higher than the Informix magic number of 8. Also, there is a maximum number of extents that the engine can handle. This number is high (213 on a Sun) but is machine and port dependent (according to Informix). If you reach it the engine WILL crash and NOT be easily recoverable! |> |> Would it make sense to install 1 more SCSI Hard-Disk for the DBTEMP |> Directory and the Informix stuff ? In the Moment thats on the same drive as |> the database. Yes. Splitting the RootDB from the data is a good idea (putting on different spindles I mean). |> |> Some info from tbstat: |> |> RSAM Version 5.00.UD1 -- On-Line -- Up 15 days 23:46:40 -- 9152 Kbytes |> |> # Shared Memory Parameters |> |> RESIDENT 1 # Forced residency flag (Yes = 1, No = 0) |> USERS 100 # Maximum number of concurrent user processes |> TRANSACTIONS 100 # Maximum number of concurrent transactions |> LOCKS 30000 # Maximum number of locks |> BUFFERS 2900 # Maximum number of shared memory buffers |> TBLSPACES 1000 # Maximum number of active tblspaces |> CHUNKS 10 # Maximum number of chunks |> DBSPACES 10 # Maximum number of dbspaces and blobspaces |> PHYSBUFF 256 # Size of physical log buffers (Kbytes) |> LOGBUFF 256 # Size of logical log buffers (Kbytes) |> LOGSMAX 18 # Maximum number of logical log files |> CLEANERS 2 # Number of page-cleaner processes |> SHMBASE 0x0 # Shared memory base address |> CKPTINTVL 300 # Checkpoint interval (in seconds) |> LRUS 8 # Number of LRU queues |> LRU_MAX_DIRTY 60 # LRU modified begin-cleaning limit (percent) |> LRU_MIN_DIRTY 50 # LRU modified end-cleaning limit (percent) |> LTXHWM 80 # Long TX high-water mark (percent) |> LTXEHWM 90 # Long TX exclusive high-water mark (percent) |> |> Tblspaces |> n address flgs ucnt tblnum physaddr npages nused npdata nrows nextns |> 0 80500b20 1 1 1000001 10000e 8063 384 0 0 1 |> 1 80500c20 1 15 10000e0 1000ed 16 15 8 135 2 |> 15 80501a20 1 1 1000065 100072 8 2 1 3 1 |> 19 80501e20 1 1 1000123 100130 360 360 317 1585 2 |> 22 80502120 1 1 100012a 100137 1984 1949 1823 10933 55 |> 25 80502420 1 1 1000060 10006d 8 3 1 1 1 |> 30 80502920 1 1 1000062 10006f 9 3 1 2 1 |> 31 80502a20 1 1 100010d 10011a 64 59 50 589 7 |> 34 80502d20 1 1 1000127 100134 3360 3357 2945 14724 13 |> 9 active, 1000 total, 256 hash buckets |> |> Logical Logging |> Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io |> L-1 0 128 33946835 408487 9960 83.1 41.0 |> |> |> Profile |> dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached |> 3207255 6541885 265424126 98.79 162798 853670 63242360 99.74 |> |> isamtot open start read write rewrite delete commit rollbk |> 208229415 7520241 19076712 72478392 7778086 181175 106739 11143053 12510 |> |> ovtbls ovlock ovuser ovbuff usercpu syscpu numckpts flushes |> 0 3 0 0 53074.87 23701.55 2016 8947 |> |> bufwaits lokwaits lockreqs deadlks dltouts lchwaits ckpwaits compress |> 801 135 279766467 1 0 13012 1173 6101582 |> The %cached for reads and writes looks real good. You should bump up the number of locks so you don't have the "ovlocks". Otherwise it looks pretty good. -- ------------------------------------------------------------------------------- Mike Reetz reetz@ncar.ucar.edu 3450 Mitchell Lane UNIX(tm) Operations Manager (303)497-8881(V) 497-8501(Fax) Building 1 UCAR - University Corporation For Atmospheric Research Boulder, CO 80301