RES: ER performance

I would say that my systems reborn with that change. Both the overload and the performance issues simply disappeared. Thank you very much. By the way, this kind of information sharing, for me, is one of greater differentials between Informix and the other databases. Julio -----Mensagem original----- De: Madison Pruet [mailto:mpruet@comcast.net] Enviada em: ter'a-feira, 3 de fevereiro de 2004 04:33 Para: Julio Cesar Cc: informix-list@iiug.org Assunto: Re: ER performance You can probably get a bit of relief by increasing the values for the dictionary. Maybe setting DD_HASHMAX 40 and DD_HASHSIZE to 231 would work. These are in your onconfig file. ----- Original Message ----- From: "Julio Cesar" <julio@prodesmaq.com.br> To: "'Madison Pruet'" <mpruet@comcast.net> Cc: <informix-list@iiug.org> Sent: Monday, February 02, 2004 5:01 AM Subject: RES: ER performance Unfortunately I don't have an open case, 'cause I don't have a support contract anymore. Like I said before, I'm planning on upgrade to version 9.4 and renew a contract for 7.31 went senseless for me. Talking about the stored procedures and the triggers, my conflict resolution works as following: I have triggers on UPDATE and INSERT on all tables that are part of the repl catalog. Every time a row is changed or inserted they're fired, calling a stored procedure that verifies if the rows must be or not be changed to fit on the database. The replicates are configured to allow trigger firing, but they don't have any direct relationship with the triggers. That's why I said that the triggers and procedures are fired even if the ER is down. If it helps, I can send you an onstat -a snapshot. Thanks, Julio -----Mensagem original----- De: Madison Pruet [mailto:mpruet@comcast.net] Enviada em: sexta-feira, 30 de janeiro de 2004 15:33 Para: Julio Cesar Assunto: Re: ER performance Are you working with tech support on this problem? If so, then do you have a case number? I can check into the case notes and advise the support engineer how to proceed. I'm really a bit uncomfortable making tuning advise without seeing the diagnostics of the engine. I'd like to see the following in the case onstat -g dic onstat -g ath Also - I'm a bit confused about the stored procedure. You mention that you are using them to resolve conflict resolution, but then you say that the same number of stored procedures are firing when the replicates are removed. The dictionary is possibably an issue, but only if the reference count of the entry is raised. It may be marked dirty, but that can occur because of distributed queries being done. So we really need to see the output of onstat -g dic in the case notes. The width of the dictionary cache size can be adjusted by using a DD_HASHSIZE entry in the onconfig file. The length can be adjusted from the default 10 by using DD_HASHMAX. Thanks m.P. "Julio Cesar" <julio@prodesmaq.com.br> wrote in message news:bvde9v$77i$1@terabinaries.xmission.com... > > There are 1565 replicates. Triggers that fires 2 another stored procedures > are working to solve conflict problems. That means that I have about 3130 > triggers and 3130 stored procedures on my server, related to the > replication, but I don't that should be the problem 'cause as I said, if I > drop the replicates the system backs to normal, and the triggers and stored > procedures are being fired yet. > > All of my replicates are using select <fields> from <table>; without any > where clauses. > > A onstat -g dic shows that are 1616 entries, and almost all of them has > dirty=yes, is that the problem? > > I also found not that I got a lot of spin locks with number of waits greater > that 10000. I also found that that one of this spin lock is something > related to the dictionary pool, as it follows: > > 225760 1951153679 8642.60 pool po_lock, name = dictpool > > The other spin locks are on the vp procs and some other mutexes. > > How can I upsize the dictionary pool cache? > > PS: Now I have 31 lists on it and the maximum list size is 10. > > Thank you very much. > Julio > > -----Mensagem original----- > De: Madison Pruet [mailto:mpruet@comcast.net] > Enviada em: quinta-feira, 29 de janeiro de 2004 21:02 > Para: Julio Cesar > Assunto: Re: ER performance > > how many replicates? > > The grouper loads all of the information that it needs to do it's work when > the engine is initialized. This means that all of the statements used to > define the replicates are cached. So there should be very little touching > the syscdr catalogue. > > > That said, it may be that since we have prepared all of the statements used > for replication, that the dictionary cache might be a bit tight. You might > want to check onstat -g dic to check that out. > > Are you using any 'where' clauses in the replicate definitions? If you are > attempting to use stored procedures within the where clause, there will be a > huge impact because that means that in effect, you've added triggers to the > tables since the grouper will have to fire the stored procedure for each log > record. > > > M.P. > > > ----- Original Message ----- > From: "Julio Cesar" <julio@prodesmaq.com.br> > Newsgroups: comp.databases.informix > Sent: Thursday, January 29, 2004 11:39 AM > Subject: RES: ER performance > > > > > > The degradation is on the source server. > > I would say it appears that the source is having to work too hard to get > > stuff in. I'm saying that because the tables that are on the repl catalog > > corresponds to just 20% of the system throughtput. The weirdest thing is > > that even if I stop the replicates, the system keep slow. Only if a > > drop/delete the replicates the systems back to normal behavior. > > > > I'll try to increase the queue memory to see what happens. > > > > Thanks again, > > Julio > > > > -----Mensagem original----- > > De: Madison Pruet [mailto:mpruet@comcast.net] > > Enviada em: quinta-feira, 29 de janeiro de 2004 15:18 > > Para: Julio Cesar > > Assunto: Re: ER performance > > > > We would need to understand where the performance hit is occurring. > That's > > going to take a bit of time and would probably be best done by using a bit > > of consulting. > > > > Is the degradation on the source or the target? > > If the apply is having problems, you might be able to split the replicates > > into multiple replicates by using the mod function to partition the table > > and turning on the parallelize option on the replicate definition. > > > > Does it appear that the source is having to work too hard to get stuff > out? > > This could be happening because the queue memory size is a bit too small > and > > causing flushing to/from disk. > > > > > > "Julio Cesar" <julio@prodesmaq.com.br> wrote in message > > news:bvao2e$sid$1@terabinaries.xmission.com... > > > > > > I'm considering it m