RES: ER performance

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 more than ever now. I was planning to do such thing to > > avoid the limitation of use HDR + ER. > > While I'm planning that, is there something I could do to at least improve > > performance a little? > > > > Thanks, > > Julio > > > > -----Mensagem original----- > > De: Madison Pruet [mailto:mpruet@comcast.net] > > Enviada em: quarta-feira, 28 de janeiro de 2004 20:02 > > Para: Julio Cesar > > Assunto: Re: ER performance > > > > There's been a huge amount of work done on ER since the 7.31 days. And a > > huge amount of the work has been focused on performance. > > > > 7.31 does not have nearly the parallelism with ER as 9.3+ and the queue > > spooling treatment has been significantly improved. Both of these changes > > will impact performance as well as use current resources much more > > efficiently. Have you considered migration to 9.4? > > > > M.P. > > > > > > "Julio Cesar" <julio@prodesmaq.com.br> wrote in message > > news:bv96qe$2q9$1@terabinaries.xmission.com... > > > > > > This is a multi-part message in MIME format. > > > > > > ------=_NextPart_000_0001_01C3E5CA.48A6EEC0 > > > Content-Type: text/plain; > > > charset="Windows-1252" > > > Content-Transfer-Encoding: quoted-printable > > > > > > Informix 7.31UD1 > > > Linux (Redhat 8.0) > > > > > > On two servers:=20 > > > DELL Poweredge 2650 Intel Xeon 2,8 (w/ 2 CPUs each), 2GB RAM each. > > > > > > My onconfig is attached to this message. > > > > > > Thanks > > > > > > -----Mensagem original----- > > > De: Madison Pruet [mailto:mpruet@comcast.net]=20 > > > Enviada em: quarta-feira, 28 de janeiro de 2004 17:49 > > > Para: Julio Cesar > > > Assunto: Re: ER performance > > > > > > Version??? > > > > > > Configuration??? > > > > > > > > > "Julio Cesar" <julio@prodesmaq.com.br> wrote in message > > > news:bv939u$10l$1@terabinaries.xmission.com... > > > > > > > > Hi guys, > > > > A few weeks ago I got ER working on two servers between my companies. > > > Since > > > > that, I'm experiencing a lot of performance issues that I'd never = > > > passed > > > > through before. > > > > The replication is working pretty fine but the server is completely > > > > overloaded. > > > > > > > > I think I've checked out everything, although I couldn't find out what > > > might > > > > be happening. I just notice that cpu vps are getting overloaded = > > > according > > > a > > > > "onstat -g glo" output. Also, locks on tables are taking longer than = > > > ever > > > to > > > > be released. > > > > > > > > What should I check to solve that? > > > > > > > > ---- > > > > By the way, I have DELL Servers with 2 Intel XEON processors, with > > > > Hyperthreading enabled. On Linux, the system recognize 4 processors > > > ('cause > > > > of hyperthreading). With that, should I use 2 or 4 CPU Vps? What's = > > > better? > > > > --- > > > > > > > > PS: I did a test, turning off all the 1500 replicates and the systems > = > > > got > > > > faster as it they were before. > > > > > > > > Thanks in advance, > > > > Julio > > > > > > > > --- > > > > Outgoing mail is certified Virus Free. > > > > Checked by AVG anti-virus system (http://www.grisoft.com). > > > > Ve

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 more than ever now. I was planning to do such thing > to > > > avoid the limitation of use HDR + ER. > > > While I'm planning that, is there something I could do to at least > improve > > > performance a little? > > > > > > Thanks, > > > Julio > > > > > > -----Mensagem original----- > > > De: Madison Pruet [mailto:mpruet@comcast.net] > > > Enviada em: quarta-feira, 28 de janeiro de 2004 20:02 > > > Para: Julio Cesar > > > Assunto: Re: ER performance > > > > > > There's been a huge amount of work done on ER since the 7.31 days. And > a > > > huge amount of the work has been focused on performance. > > > > > > 7.31 does not have nearly the parallelism with ER as 9.3+ and the queue > > > spooling treatment has been significantly improved. Both of these > changes > > > will impact performance as well as use current resources much more > > > efficiently. Have you considered migration to 9.4? > > > > > > M.P. > > > > > > > > > "Julio Cesar" <julio@prodesmaq.com.br> wrote in message > > > news:bv96qe$2q9$1@terabinaries.xmission.com... > > > > > > > > This is a multi-part message in MIME format. > > > > > > > > ------=_NextPart_000_0001_01C3E5CA.48A6EEC0 > > > > Content-Type: text/plain; > > > > charset="Windows-1252" > > > > Content-Transfer-Encoding: quoted-printable > > > > > > > > Informix 7.31UD1 > > > > Linux (Redhat 8.0) > > > > > > > > On two servers:=20 > > > > DELL Poweredge 2650 Intel Xeon 2,8 (w/ 2 CPUs each), 2GB RAM each. > > > > > > > > My onconfig is attached to this message. > > > > > > > > Thanks > > > > > > > > -----Mensagem original----- > > > > De: Madison Pruet [mailto:mpruet@comcast.net]=20 > > > > Enviada em: quarta-feira, 28 de janeiro de 2004 17:49 > > > > Para: Julio Cesar > > > > Assunto: Re: ER performance > > > > > > > > Version??? > > > > > > > > Configuration??? > > > >@@NL