Asynchronous I/O on AIX and DB performance Post: 302514790

Sponsored Content

Operating Systems AIX Asynchronous I/O on AIX and DB performance Post 302514790 by zaxxon on Monday 18th of April 2011 05:15:00 AM

04-18-2011

Registered User

Before increasing the maxservers, you could try if the behaviour is getting better when increasing the maxreqs 1st. On AIX 5.3 it is per default 4096. In AIX 6.1 they start with 16384 for example. If you had hit the max number of async I/O requests, you will most probably have gotten a Oracle DB error with a crash (see this: What is maxreqs parameter in AIX?)

What you see with iostat -AQ is just the average since boot. If you want an actual size of the ascync I/O queue, you might want to add some interval parameters like iostat -AQ 2 which will report every 2 seconds.

The number of active AIO servers processes you see with a ps output for example, is just the number of servers, that had been started at some time of a peak. It is not necessary the number of servers that are actually doing some work. To see these, you could use topas -i2. Here an example from a busy Oracle box:

Code:

┌─topas_nmon──S=WLMsubclasses────Host=somehost──────Refresh=2 secs───11:08.03─────────────────────────────────────────────────────────┐
│ Asynchronous-I/O-Processes ──────────────────────────────────────────────────────────────────────────────────────────────────────────│
│ Total AIO processes=1100 Actually in use=  59  CPU used=  20.9%    [Use zero                                                         │
│       All time peak=1100     Recent peak=  59      Peak=  20.9%     to reset]                                                        │

There are currently 1100 servers started, but now at production times, there are in this 2 seconds just 59 of them really active.
When aioserver processes are not doing any work anymore, they stay started and so their number does not decrease. Their number stays at their peak value since boot, which is here 1100. We have maxservers=50, so 50 for every logical cpu (lcpu) in the box, maxreqs=32768 (just in case) and minservers=5 to have a buffer for initial workload. If more are needed, the box just starts more of them.
It could be that in some special job or night job etc. there is much more demand of async IO, so that could be the reason, the box started all of the 1100.

Depending on what your current values for AIO are (post a lsattr -El aio0, it could no bad idea to increase them and monitor the box. For long term monitoring, write a script or maybe use something like nmon.

zaxxon

View Public Profile for zaxxon

Find all posts by zaxxon

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

AIX server performance problem!

Hello, I have a performance problem on an AIX box. I'm not sure what is causing this and hoping someone may have suggestions. Currently I'm noticing that cpu's are waiting while the box is in a idle state. I checked the disks and none of them are at 100%. If they were then I would understand...

2. UNIX for Dummies Questions & Answers

AIX performance

Hiya all, I am a newbie sysadmin to AIX, i have worked on HPUX for 3 years. I have started a new role with in an IBM house and because there is me and one other there are a couple of issues I cannot work out: We havehad a production server slowing down processing batch jbs over the past...

3. AIX

Performance testing on AIX

I'm doing performance testing for one application which works on AIX. But I don't know which performance parameters of memory need to be collected. Now, I just know very few: 1. page in 2. page out 3. fre They are all collected by "vmstat" command. I want to know, except for above...

4. AIX

AIX 5.2 performance question

I am trying to analyze the performance of an AIX system. I think I may have a disk I/O issue, but I am asking for help to validate or invalidate this assumption. I ran the commands below during a period of peak load. Please help me to find any performance bottlenecks. Thanks in advance for your...

5. AIX

Performance issue in AIX 5.3

Is there is any way to increase the CPU utilization of a Embedded SQL program in AIX 5.3 .. for performance purpose.

6. AIX

performance issue in AIX

Gurus, i have process that runs 5 times a day. it runs normally (takes about 1 hour) to complete in 3 runs but it is takes about ( 3 hrs to complete) two times So i need to figure out why it takes significanlty high time during those 2 runs. The process is a shell script that connect to...

7. AIX

AIX performance issue

Hi Guys, This is the situation I am in. Provide your views and input where should I start? I have one P7 test server and a p520 production server. the job is taking pretty long on the P7 test server when compared to the P5 production server. below is the full detail. Informix...

8. AIX

Need some help for AIX performance monitoring

Hello I am new user of AIX; I have only basic knowledge of the UNIX commands, and I want to create script that will monitor the performance and resources usage on AIX 6.1 machine. Basically I wan to start a loop that will grab, every 10 seconds, the CPU usage, the memory usage, the disk usage,...

9. AIX

AIX 5.3 performance problems

Hello, I encounter some performance issues on my AIX 5.3 server running in a LPAR on a P520. How do I investigate performance issues in AIX. Is there any kind of procedure that takes me to the steps to investigate my server and find the sub systems that is causing the issues? The performance...

10. AIX

Performance stats from AIX 6

Hi, I'm supposed to capture many performance stats on AIX 6 and stuck up with below: Priority queue Disk cache hit% Page out rate Swap out rate Memory queue I see vmstatis helpful for "page out" but not sure how to get the "rate". Could anyone please let me know how to get these...

LEARN ABOUT PHP

db2_foreign_keys

DB2_FOREIGN_KEYS(3)							 1						       DB2_FOREIGN_KEYS(3)

db2_foreign_keys - Returns a result set listing the foreign keys for a table

SYNOPSIS

       resource db2_foreign_keys (resource  $connection, string  $qualifier, string  $schema, string  $table-name)

DESCRIPTION

	Returns a result set listing the foreign keys for a table.

PARAMETERS

	      o $connection
		- A valid connection to an IBM DB2, Cloudscape, or Apache Derby database.

	      o $qualifier
		- A qualifier for DB2 databases running on OS/390 or z/OS servers. For other databases, pass NULL or an empty string.

	      o $schema
		- The schema which contains the tables. If $schema is NULL, db2_foreign_keys(3) matches the schema for the current connection.

	      o $table-name
		- The name of the table.

RETURN VALUES

	Returns a statement resource with a result set containing rows describing the foreign keys for the specified table. The result set is com-
       posed of the following columns:

       +--------------+---------------------------------------------------+
       | Column name  | 						  |
       |	      | 						  |
       |	      | 		   Description			  |
       |	      | 						  |
       +--------------+---------------------------------------------------+
       | PKTABLE_CAT  | 						  |
       |	      | 						  |
       |	      |  Name of the catalog for the table containing the |
       |	      | primary key. The value is NULL if this table does |
       |	      | not have catalogs.				  |
       |	      | 						  |
       |PKTABLE_SCHEM | 						  |
       |	      | 						  |
       |	      |  Name of the schema for the table containing  the |
       |	      | primary key.					  |
       |	      | 						  |
       |PKTABLE_NAME  | 						  |
       |	      | 						  |
       |	      |   Name of the table containing the primary key.   |
       |	      | 						  |
       |PKCOLUMN_NAME | 						  |
       |	      | 						  |
       |	      |  Name of the column containing the primary key.   |
       |	      | 						  |
       | FKTABLE_CAT  | 						  |
       |	      | 						  |
       |	      |  Name of the catalog for the table containing the |
       |	      | foreign key. The value is NULL if this table does |
       |	      | not have catalogs.				  |
       |	      | 						  |
       |FKTABLE_SCHEM | 						  |
       |	      | 						  |
       |	      |  Name  of the schema for the table containing the |
       |	      | foreign key.					  |
       |	      | 						  |
       |FKTABLE_NAME  | 						  |
       |	      | 						  |
       |	      |   Name of the table containing the foreign key.   |
       |	      | 						  |
       |FKCOLUMN_NAME | 						  |
       |	      | 						  |
       |	      |  Name of the column containing the foreign key.   |
       |	      | 						  |
       |   KEY_SEQ    | 						  |
       |	      | 						  |
       |	      |   1-indexed position of the column in the key.	  |
       |	      | 						  |
       | UPDATE_RULE  | 						  |
       |	      | 						  |
       |	      |  Integer value representing the action applied to |
       |	      | the foreign key when the SQL operation is UPDATE. |
       |	      | 						  |
       | DELETE_RULE  | 						  |
       |	      | 						  |
       |	      |  Integer value representing the action applied to |
       |	      | the foreign key when the SQL operation is DELETE. |
       |	      | 						  |
       |   FK_NAME    | 						  |
       |	      | 						  |
       |	      | 	  The name of the foreign key.		  |
       |	      | 						  |
       |   PK_NAME    | 						  |
       |	      | 						  |
       |	      | 	  The name of the primary key.		  |
       |	      | 						  |
       |DEFERRABILITY | 						  |
       |	      | 						  |
       |	      |  An integer value representing whether	the  for- |
       |	      | eign key deferrability is SQL_INITIALLY_DEFERRED, |
       |	      | SQL_INITIALLY_IMMEDIATE, or SQL_NOT_DEFERRABLE.   |
       |	      | 						  |
       +--------------+---------------------------------------------------+
SEE ALSO

       db2_column_privileges(3),  db2_columns(3),  db2_primary_keys(3),   db2_procedure_columns(3),   db2_procedures(3),   db2_special_columns(3),
       db2_statistics(3), db2_table_privileges(3), db2_tables(3).

PHP Documentation Group 												       DB2_FOREIGN_KEYS(3)

10 More Discussions You Might Find Interesting

1. UNIX for Advanced & Expert Users

AIX server performance problem!

Discussion started by: ctcuser

2. UNIX for Dummies Questions & Answers

AIX performance

Discussion started by: chlawren

3. AIX

Performance testing on AIX

Discussion started by: adasong

4. AIX

AIX 5.2 performance question

Discussion started by: jhall