Informix problem or OS problem?

We have a SQL that executes in 0.30 seconds. However, when we have two users executing the same SQL, our time doubles to 0.60 seconds for both to finish. A third user would add another 0.30 seconds on average. Would anybody know how this could happen? Would it be Informix not scaling properly or the OS not handling DB connections very well? We are using HP UX by the way. -- Nick

I would be checking to see if you are throttling global resources. Perhaps the query is requiring all buffer memory and is thus causing a global buffer flush. When the next query is executed it is causing the engine to have to require the resources. Are you PDQ by any chance? If so, then you might be executing the queries serially because too many resources have been granted to the one query. Nick Tran wrote: > We have a SQL that executes in 0.30 seconds. However, when we have two > users executing the same SQL, our time doubles to 0.60 seconds for both to > finish. A third user would add another 0.30 seconds on average. > > Would anybody know how this could happen? Would it be Informix not scaling > properly or the OS not handling DB connections very well? We are using HP > UX by the way. > > -- Nick

Nick, I just received your email where you stated.... > The problem occurs with SQLs of all complexities. Each additional user > executing the same SQL increases the time for everyone else. Some of these > queries executed by just one user is really really fast. I couldn't imagine > it overflowing buffer memory when a lot of these queries are short and > quick. How can I monitor the global buffer? What is PDQ? I'm not a dbase > expert. > . This indicates that you are throttleing on some common resource, possibably that you are processing serially. You need to ensure that your queries are not locking on the same data as that would cause serialization of all transactions. Also, you might want to check to see if your database is defined as mode ANSI or is using repeatable read isolation. If that is the case, then if scans of the tables are done, you would be locking every row within the table. PDQ is a parameter that can be use to make a limited number of queries run quicker, but it can be deadly if not configured correctly. Here's what you need to examine.... 1) Start collecting and studying the output of onstat -p. You should be seeing the cache read above 97%. 2) Examine the output of onstat -g ath. If the sqlexec threads are not waiting on either smread or netnorm, then you need to examine their stacks (onstat -g stk <thread-id>) 3) examine the output of onstat -K. What threads are waiting on locked resources. From what information you've given, it is really impossible to tell where the problem is. Nick Tran wrote: > We have a SQL that executes in 0.30 seconds. However, when we have two > users executing the same SQL, our time doubles to 0.60 seconds for both to > finish. A third user would add another 0.30 seconds on average. > > Would anybody know how this could happen? Would it be Informix not scaling > properly or the OS not handling DB connections very well? We are using HP > UX by the way. > > -- Nick

A couple of possible causes that come to mind : 1. PDQ "gating". Corrective action : Add "set pdqpriority 0;" to the SQL statement. 2. Locking contention. Corrective action : Add "set isolation to dirty read;" to the SQL statement. Rudy Nick Tran wrote: > We have a SQL that executes in 0.30 seconds. However, when we have two > users executing the same SQL, our time doubles to 0.60 seconds for both to > finish. A third user would add another 0.30 seconds on average. > > Would anybody know how this could happen? Would it be Informix not scaling > properly or the OS not handling DB connections very well? We are using HP > UX by the way. > > -- Nick

What product are you using? You say you're using HP-UX - is that 10.20? Are you using Informix Dynamic Server or SE? What version of the database engine are you using. Rudy Fernandes wrote: > A couple of possible causes that come to mind : > > 1. PDQ "gating". Corrective action : Add "set pdqpriority 0;" to the SQL > statement. > 2. Locking contention. Corrective action : Add "set isolation to dirty read;" > to the SQL statement. > > Rudy > > Nick Tran wrote: > > > We have a SQL that executes in 0.30 seconds. However, when we have two > > users executing the same SQL, our time doubles to 0.60 seconds for both to > > finish. A third user would add another 0.30 seconds on average. > > > > Would anybody know how this could happen? Would it be Informix not scaling > > properly or the OS not handling DB connections very well? We are using HP > > UX by the way. > > > > -- Nick