System Performance on T520 Informix database server

[Donald Dawson]

I have a T520 with 71 disks - many are 2 GB, some are 4 GB and I have two Jamaica boxes with 8 9 GB drives. It is running on HPUX 11.0 (well patched). Informix is the only application running (7.31.UD6 - latest version). We have the data striped on the Informix volumes. I am not using mirroring yet, but have the disk configured to use Informix mirroring. We have 2 GB of memory, 12 SCSI controllers with 5-10 drives per controller, and 8 CPUs. Glance looks good for disks and memory. The system is slower than a T500 we have running 10.20. We have tried running with KAIO on. Swap is 4.5 GB

dev 512 0 512 0% 0 - 1 /dev/vg00/lvol2
dev 2034 0 2032 0% 0 - 0 /dev/dsk/c0t10d0
dev 2034 0 2032 0% 0 - 0 /dev/dsk/c2t3d0
reserve - 1369 -1369
memory 1608 1255 353 78%
total 6188 2624 3560 42% - 0 -

Basic system info:

HP-UX elitedev B.11.00 U 9000/892 320319321 unlimited-user license
CPU Count: 8
CPU Speed: 120 MHz
CPU HW Support: 32-bit
Kernel Support: 32-bit
RAM Size: 2047 MB
bufpages: 204 MB
maxuprc: 100
maxvgs: 40
maxfiles: 2048
max_thread_proc: 64
nfile: 6010
nflock: 200
nproc: 820
ninode: 2500
shmmax: 1073741824
shmmni: 256

We have had our Informix instance reviewed - checkpoints are fine - see below:

NETTYPE ipcshm,1,100,CPU # Override sqlhosts nettype parameters
NETTYPE soctcp,6,100,CPU # Override sqlhosts nettype parameters

LOCKS 750000 # Maximum number of locks - was 500k 07/23/02
BUFFERS 400000 # Maximum number of shared buffers
NUMAIOVPS 45 # Number of IO vps
#NUMAIOVPS 2 # Number of IO vps
PHYSBUFF 128 # Physical log buffer size (Kbytes)
LOGBUFF 32 # Logical log buffer size (Kbytes)
LOGSMAX 40 # Maximum number of logical log files
CLEANERS 40 # Number of buffer cleaner processes
SHMBASE 0x0 # Shared memory base address
SHMVIRTSIZE 200000 # initial virtual shared memory segment size
SHMADD 32000 # Size of new shared memory segments (Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
CKPTINTVL 600 # Check point interval (in sec)
LRUS 50 # Number of LRU queues
LRU_MAX_DIRTY 10 # LRU percent dirty begin cleaning limit
LRU_MIN_DIRTY 5 # LRU percent dirty end cleaning limit
LTXHWM 50 # Long transaction high water mark percentage
LTXEHWM 60 # Long transaction high water mark (exclusive)
TXTIMEOUT 0x12c # Transaction timeout (in sec)
STACKSIZE 32 # Stack size (Kbytes)

RA_PAGES 64 # Number of pages to attempt to read ahead
RA_THRESHOLD 60 # Number of pages left before next group

DBSPACETEMP tempdbs1,tempdbs2,tempdbs3,tempdbs4

The system is sluggish and the Informix response is poor. Is there a problem with 11.0 on a T520? Should I go back to 10.20?

thanks,
Donald Dawson
Baker Botts L.L.P.

[Zafar A. Mohammed_1]

First, you don't have to go back 10.20.
How many CPUs you are using for Informix, check NUMCPUVPS, this value should be 7 in your case. Check MULTIPROCESSOR value, 0 for single-processor and 1 for multi-processor.

Can you provide some:
onstat -p
onstat -g glo

Also Check :
some onstat command, i mean locks and others
onstat -k, onstat -g seg


Thanks
Zafar

[Rainer von Bongartz]

Donald,

did you enable the wait statistics for your server ??

$ONCONFIG param WSTATS=1 ??

After enabling you can pretty easy see from the system catalog tables
where there might be a performance problem on your INFORMIX server.


If you need more info on this topic let me know


Regards
Rainer

[Steve Lewis]

10.20 is coming right of support / obsolete in a week or two so don't even think about going back to it.

You haven't given us your semaphore kernel values, I assume they are OK.

If you use raw data then look at the wio in sar -u 5 12. onstat -g ioq will show you if there are extra long disk queue stats on a particular chunk. If that is the case then you need to see which tables are in that chunk and the amount of i/o they are taking, so that you can re-distribute some of the data.
db/cockpit will help with that, coupled with the output of oncheck -pe.

Informix mirroring is slower than LVM mirroring. If you have Mirrordisk/UX installed, then use it.

On a T, 10 disks per controller is too many. PB Bus controllers in particular don't work well with more than 5 disks each, although they do support more.

You say you have had the informix parameters tuned, but your RA_PAGES and RA_THRESHOLD are very big. Ours are 32 and 24, which works as well as I have ever seen. I would only set RA_PAGES to a higher value when I had large sequential table scans. I would never set RA_THREASHOLD that high unless I had huge i/o waiting times.

6 soctcp VPs is an interesting piece of tuning. I have never seen that many. If you have multiple lan interfaces, then try setting different INFORMIXSERVER aliases in sqlhosts to different IPs for different interfaces. I have seen TCP socket saturation, which was relieved by separating the batch processes from the interactive through that method.
Have you already tried setting your soctcp VPs to type NET? Many people reckon that is better than CPU, even though the manual says otherwise.

Another thing that could be causing poor response is the network. Double check the duplex/speed settings.

Some parts of 11.00 are improved in 11i, particulary if you use cooked files through the VXFS filesystem. A lot of people went to 11 coupled with a new faster server, so didn't notice degredation.

11.00 is a bigger o/s than 10.20. The libraries are bigger, the kernel is bigger, the EMS is a bigger suite and you get Java apps popping up and consuming cpu which never helps on such old processors. Look at what is running and chop the unnecessary bits.

IDS 9.30/9.40 have some good features, such as the long/awaited SQL statement cache, so you could consider upgrading to that. It isn't just good for datablades and objects.

[Steve Lewis]

10.20 is coming right of support / obsolete in a week or two so don't even think about going back to it.

You haven't given us your semaphore kernel values, I assume they are OK.

If you use raw data then look at the wio in sar -u 5 12. onstat -g ioq will show you if there are extra long disk queue stats on a particular chunk. If that is the case then you need to see which tables are in that chunk and the amount of i/o they are taking, so that you can re-distribute some of the data.
db/cockpit will help with that, coupled with the output of oncheck -pe.

Informix mirroring is slower than LVM mirroring. If you have Mirrordisk/UX installed, then use it.

On a T, 10 disks per controller is too many. PB Bus controllers in particular don't work well with more than 5 disks each, although they do support more.

You say you have had the informix parameters tuned, but your RA_PAGES and RA_THRESHOLD are very big. Ours are 32 and 24, which works as well as I have ever seen. I would only set RA_PAGES to a higher value when I had large sequential table scans. I would never set RA_THREASHOLD that high unless I had huge i/o waiting times.

6 soctcp VPs is an interesting piece of tuning. I have never seen that many. If you have multiple lan interfaces, then try setting different INFORMIXSERVER aliases in sqlhosts to different IPs for different interfaces. I have seen TCP socket saturation, which was relieved by separating the batch processes from the interactive through that method.
Have you already tried setting your soctcp VPs to type NET? Many people reckon that is better than CPU, even though the manual says otherwise.

Another thing that could be causing poor response is the network. Double check the duplex/speed settings.

Some parts of 11.00 are improved in 11i, particulary if you use cooked files through the VXFS filesystem. A lot of people went to 11 coupled with a new faster server, so didn't notice degredation.

11.00 is a bigger o/s than 10.20. The libraries are bigger, the kernel is bigger, the EMS is a bigger suite and you get Java apps popping up and consuming cpu which never helps on such old processors. Look at what is running and chop the unnecessary bits.

IDS 9.30/9.40 have some good features, such as the long-awaited SQL statement cache, so you could consider upgrading to that. It isn't just good for datablades and objects.

[Michael Steele_2]

Please post the results of the following:

sar -m 5 5
sar -v 5 5
sar -b 5 5
sar -d 5 5
vmstat 5 5
dbc_min
dbc_max
swapinfo -tam

[Donald Dawson]

Rainer,

I do not have the WSTATS variable set, nor is it in the onconfig.std file. As you know, it isn't documented. I found the entry in the Informix DBA Survival Guide by Joe Lumbley - I'll give it a try. Does it add significant overhead?

Steve,

The system is a test system, so the load is inconsistent. It is slow with 1 user. sar -u checking for wio is okay. I'l lcheck into onstat -g ioq and how to check for problems. Currently, its output shows no load compared to our production system.

I do not have db/cockpit nor do I have any references to the product, although I remember seeing it a few years back. Google has info, but the product appears to have been dropped. Does ISA Admin replace this product?

With the T520, I can mirror the disks, but it is apparently not straight-forward. The striping is differrent with HP mirroring set. I currently have the mirror disk on another volume group. Regardless, does mirroring provide any performance boost? I am not real concerned with redundancy on this system.

We only have 1 controller with 10 2 GB drives, the others have 5, except for two that have 8 9 GB drvies each (Jamaica boxes).

I learned about having high RA values in an Informix performance class. I will drop the values to 32/24.

My recent consultant said to use all of the CPU VPs since they are more efficient. I was using NET before. Since I have 8 CPUS, and have allocated 7 to Informix, should I leave the ipcshm net type as 1,100,CPU and change the onsoctcp net type from 6,100,CPU to 2,100,NET? I was told the onsoctcp type would allocate more net types if they were needed. With this config, am I underutilizing my CPUs?

Checking our 1 lan card, it was set to auto-negotiation. Checking the current settings, it was 100 MB half duplex. I turned off auto-neg and set the etnry at full duplex - thanks.

We are using raw disks for Informix and they are striped.

We'll stay with 11.0, but at some point I'll check 11i to see if that helps. Informix 9+ may or may not happen - it will cost us about $25k to upgrade our production and test server - expensive.

I will attach our kmtune info - not sure about the semaphore values you are referring to.

Thanks,
Donald

[Donald Dawson]

Michael,

I am including the sar / vmstat / swapinfo information you requested.

Donald

[Michael Steele_2]

Not a lot going on here. Do have one suggestion to make about your swap priorities.

dev 512 0 512 0% 0 - 1 /dev/vg00/lvol2
dev 2034 0 2032 0% 0 - 0 /dev/dsk/c0t10d0
dev 2034 0 2032 0% 0 - 0 /dev/dsk/c2t3d0


Make them all 1, the same as your primary. You'll have to vi /etc/fstab and reboot to do this. Right now your using secondary swap before primary because of the '0'. (* ooops *)

Isn't there an asynchronous write issue with Informix? Something to set within the database? Could you be in synchronous mode?

[Donald Dawson]

Michael,

HP support just recommended setting the two matching swap spaces to 1, to have priority over the primary 512 MB space. He said when they are used, the two matching spaces (2 GB) will be efficiently and equally written to.

We are not using kernel i/o by setting KAIOON to 1.

dd

[Bill Hassell]

Setting the swap priorities won't do anything--you're not swapping which is very good thing. Swap priorities, spreading swap among different disks and controllers, having swap space 2xRAM size, all of these are obsolete recommendations for today's servers. They were made when big RAM was 768megs, and shared memory would be found in very few applications. Without question, swapping (paging) is very bad for performance, but your stats show that you're fine (no swap usage) so that's fine.

The T-series was a really good box many years ago, but today, the whole system can be replaced with new hardware that is easily 1/3 the original price of the T-series+disks, and will run 3x-5x faster. 11.11 will run faster than 11.0, espeically for LAN intensive and filesystem intensive operations due to major code changes. As always, install 11.11, then add both SupportPlus patch sets (HWE and QPK), then add the applications and configs. Make sure the buffer cache is no larger than 200 megs on this slow machine (set bufpages, don't use dbc_max_pct)

[Steve Lewis]

Sounds like the half-duplex issue could have been the problem to me.

The thing you have to realise about CPU VPs is the HP-UX scheduler. Giving them the TCP/IP listening can give each CPU VP less of a timeslice to work on SQL statements. If you put the listening onto NET VPs gives the CPU VPs the full timeslice to work on SQL. The downside to this is the required communication between VP types through IPC. There is an IBM Informix product to take the handling of client communications right off the server, so it must have been noticed as an issue in the past. I have noticed that it greatly helps to have at least one listener process / thread per active lan interface.

If you can use ipcshm for on-server processes then do so judiciously, its by far the quickest means of access to the database.
However it also depends on how many users you have and it can effictively slow down access from remote clients.

You sound pretty switched on and I think its probably going to be up to you whether you are under-utilising your processors. Just be sure to test your changes objectively.

[Rainer von Bongartz]

Donald,

sure there is a overhead enabling wait statistics with WSTATS=1.

You should enable it during a specific time and measure the database servers wait states.

This wait states will give you the direction to go on, so you can disable WSTATS afterwards.

During enabled WSTATS measure with:

exec $INFORMIXDIR/bin/dbaccess sysmaster - </dev/null
select sum(cumtime)/1000000 time, reason[1,25] reason from sysseswts
where reason != "condition" and cumtime > 1000
and sid != ( select sid from syssessions where pid = $$ )
group by reason
order by 1 desc
eof


You will get something like:

time reason

49,64480400000 aio
22,102884 buffer
19,66827700000 running
0,691903 mt ready
0,489298 mt yield 0
0,102884 buffer



In this case he databas server is waiting for IO and buffers.
You need to tune AIO and add more BUFFERS.


Regards
Rainer