Problem(Abstract)
The database server's performance becomes bad when there are many sessions accessing the server simultaneously.
Symptom
Query response time becomes longer and longer for all queries.
Cause
Overuse of PDQ feature.
Diagnosing the problem
Find there are many sessions were accumulated in PDQ queue.
onstat -g mgm
IBM Informix Dynamic Server Version 11.50.FC4 -- on-Line -- Up 18:04:23 -- 16821136 Kbytes
Memory Grant Manager (MGM)
--------------------------
MAX_PDQPRIORITY: 50
DS_MAX_QUERIES: 32000
DS_MAX_SCANS: 1048576
DS_NONPDQ_QUERY_MEM: 1024 KB
DS_TOTAL_MEMORY: 4096000 KB
Queries: Active Ready Maximum
205 42 32000
Memory: Total Free Quantum
(KB) 4096000 0 128
Scans: Total Free Quantum
1048576 1048374 1
Load Control: (Memory) (Scans) (Priority) (Max Queries) (Reinit)
Gate 1 Gate 2 Gate 3 Gate 4 Gate 5
(Queue Length) 1 0 41 0 0
Active Queries:
---------------
Session Query Priority Thread Memory Scans Gate
22 700000228709438 25 700000227c24028 0/0 1/1 -
42 70000022804fbf8 25 700000227ff5970 0/0 1/1 -
43 700000228a9fbf8 25 7000002280a7a30 0/0 1/1 -
44 700000228b53bf8 25 700000228b09028 0/0 1/1 -
45 700000228c08bf8 25 700000228bbd028 0/0 1/1 -
46 700000228cbcbf8 25 700000228c60310 0/0 1/1 -
47 700000228d70bf8 25 700000228d145b0 0/0 1/1 -
48 700000228e24bf8 25 700000228dc8850 0/0 1/1 -
49 700000228ed8bf8 25 700000228e7cb38 0/0 1/1 -
50 700000228f8cbf8 25 700000228eebd70 0/0 1/1 -
51 700000229041bf8 25 700000228ff6028 0/0 1/1 -
52 7000002290f5bf8 25 700000229099280 0/0 1/1 -
53 7000002291a9bf8 25 700000229151610 0/0 1/1 -
54 700000229205bf8 25 7000002291bb028 0/0 1/1 -
55 70000022930dbf8 25 70000022926f028 0/0 1/1 -
57 700000229422bf8 25 7000002293c6280 0/0 1/1 -
58 7000002294d6bf8 25 70000022947a460 0/0 1/1 -
59 70000022958abf8 25 70000022952e658 0/0 1/1 -
60 70000022963ebf8 25 7000002295e2850 0/0 1/1 -
......
Resolving the problem
1. Set PDQPRIORITY lower in application, especially in OLTP environment.
2. If no time to change the application, we can turn off the PDQ feature by setting MAX_PDQPRIORITY to 0 in onconfig or execute onmode -D 0 without need to restart the server.
Related information
'Informix > informix troubleshooting' 카테고리의 다른 글
| Prevent and prepare for assertion failures (0) | 2012.06.06 |
|---|---|
| Correcting assertion failures (AF) (0) | 2012.06.06 |
| Insert into a table is slow (0) | 2012.04.27 |
| Locked record errors -244 and -107 or -245 and -144 when updating a record (0) | 2012.03.30 |
| IC56169: REQUEST JDBC DRIVER TO PARSE SERVICENAME WHEN USING SQLHOSTS LOOKUP IMPLEMENTATION (0) | 2012.03.21 |