RE: Questionable IDS 9.21 performance on HP-UX v11.11

Hi, Jason, Please, see my comments below jasondinsdale@bigpond.com (Jason) wrote in message > > Alexey, > > Thanks for your observations. Reading the Informix Handbook, it says: > > "Foreground Writes. These occur when IDS needs a buffer and must > interrupt processing to flush buffers to disk to free a buffer. These > are the least desirable types of writes. If a foreground writes > occurs, you should seriously consider tuning your instance. The goal > is to have zero FG writes." > > This leads me to a couple of questions: > > - Why am I getting FG writes? With 1.6Gb BUFFERS + a SAN disk array I > find it hard to understand why I'm getting FG writes and poor disk > performance as you say. Do I have the 'right' number of LRU queues > for 800,000 buffers? > > - How do I get rid of the FG writes? Is it possible just by tuning, > or is it more of an application-level change? > The fact that You have FG writes (which by itself are very small and do not cause significant performance degradation) indicates that performance of the database server I/O subsystem is not enough for the application it is running Even with huge database server cache, disk array cache it is unable to serve all I/O requests (both read and write), this is why the write queue (which has less priority then read queue) is getting so big between checkpoints that the database server has to stop all disk activity to perform the FG write. This is what I'd like to suggest: 1. Disk array level - Get Rid off the AUTO RAID!!! Only RAID 10 for high-demanding applications! - Carefully verify disk array cache configuration: it should be configured as write-back; - No disk write verification; - Most RAID memory should be given to write buffers, and only minimal should be left to data caching (Informix caching is more efficient for the database, then hardware caching) 2. Database server level - decrease checkpoint interval to 300 or even 100 seconds. Your goal is to make almost all database writes to be chunk writes - that is, checkpoint writes - to reduce contention between reads and writes between checkpoints. Please, note, that even LRU writes cause performance degradation, because they dramatically decrease database write caching. With LRU writes, modified page tends to be written to the disk after being modified only once, while with checkpoint writes, database server page can be modified several times before it actually goes to the disk. With 'pure' chunk writes, one can dedicate almost all disk resources to database reads between checkpoints. Checkpoint duration can be optimized by properly configuring disk array write-back cache (buffers). - Increase LOGBUF and PHYSBUF to 256 or 512 - Consider moving logical and/or physical logs to another location - may be, even to local disks (Informix writes to both of them sequentially, so single disk should give enough performance) 3. Database level - properly run 'update statistics': without statistics, database server might choose sequential scan to access very big table, even though proper index exists - carefully analyze indexes created on largest tables. Consider, what additional indexes might improve performance of your queries, and what indexes might be very expensive for inserts, deletes and updates - consider keeping data and indexes in different dbspaces located on different I/O devices 4. Application level - Consider using 'online aggregation' (that is, OLTP functions should also update 'aggregate' tables along with 'pure' OLTP tables), so that part of reporting can be switched to smaller aggregate tables from original huge tables > As for which db objects are being hit hardest, I have tried the > queries you suggest and it does tend to be a few large tables that are > constantly hit by multiple users and app modules. How can I increase > parallelisation of access to these few objects? Fragmentation? In my opinion, fragmentation is not very good for OLTP systems. Just keep data and indexes in different dbspaces. Also, consider keeping some huge tables in separate dbspaces > > Re: Logical log activity, how do you measure it? onstat -l > We use BUFFERED logging. This is good - from performance standpoint > > As you can probably tell I'm a reluctant DBA who's really a sys admin, > and so I have limited experience in this area of database design. > However, you've come to the same conclusion as I have ... the > application is poorly designed! > > Thanks, > > Jason > > > > Jason, > > > > I think You are having a pure disk bottleneck. > > > > For some reason, your server is having HUGE database > > read and write activity more then 50 disk operations/sec > > average (I think, including calm night hours) > > > > Most of your writes are LRU writes; that means, there is > > contention between database server reads and writes > > > > You even have FG writes!!! > > This means, that disk performance is REALLY poor. > > > > Most probably, your application is poorly designed > > > > Try to analyze using > > SELECT * from SYSMASTER:SYSPTPROF ORDER BY PAGREADS DESC; > > SELECT * from SYSMASTER:SYSPTPROF ORDER BY PAGWRITES DESC;> > what tables/indexes are consuming most of the server's I/O > > resources - and, then, try to identify application modules > > that are accessing these objects for read and write > > > > It is also interesting to see how much LOG activity > > your system is having. Are you using BUFFERED or UNBUFFERED > > logging? > > > > > > ------------------------------------------ > > Alexey Sonkin sending to informix-list