Performance impact of SQLTRACE
Jeff Ross asked about SQLTRACE overhead on Informix 11.10 and whether active queries show. Art Kagel said LOW and MEDIUM cost a few percent of CPU and active queries are shown. Mark Jalkiewicz used high and medium in production with low impact, reserving high for host variable values, and warned that large buffers may add segments. Solved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi all, Pretty much still an informix newbie but I've searched and haven't found an answer. On Informix 11.10, is there a significant performance impact to turning on global SQLTRACE through onconfig? We've got problem queries that need to be addressed and it seems like this might be a way to start. I've been tinkering with SQLTRACE by turning it on through the task API on our development database but that isn't giving me a feel for what it will do to our production server. I've been using 'execute function task("set sql tracing on", 3000, 2, "med", "global");' to test but would really prefer to keep more than 3000 queries if possible, at least until we get a better handle on this. We have 256G of ram on the production server with about 50G usually free. Oh, in testing I haven't answered this next question: Does syssqltrace report queries while they are in process? We'd initially like to look for queries that are taking 10s of seconds or longer to finish so if sql_begintxtime is not null and sql_finishtime is null I can do that. Thank you! Jeff Ross CargoTel, Inc.
The overhead of SQLTRACE for LOW and MEDIUM level is not bad, a few percentage of CPU. Yes it shows active queries. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Dec 8, 2015 at 4:47 PM, JEFF ROSS <rossj@cargotel.com> wrote: > Hi all, > > Pretty much still an informix newbie but I've searched and haven't found an > answer. > > On Informix 11.10, is there a significant performance impact to turning on > global SQLTRACE through onconfig? We've got problem queries that need to be > addressed and it seems like this might be a way to start. > > I've been tinkering with SQLTRACE by turning it on through the task API on > our > development database but that isn't giving me a feel for what it will do to > our production server. I've been using 'execute function task("set sql > tracing > on", 3000, 2, "med", "global");' to test but would really prefer to keep > more > than 3000 queries if possible, at least until we get a better handle on > this. > > We have 256G of ram on the production server with about 50G usually free. > > Oh, in testing I haven't answered this next question: Does syssqltrace > report > queries while they are in process? We'd initially like to look for queries > that are taking 10s of seconds or longer to finish so if sql_begintxtime is > not null and sql_finishtime is null I can do that. > > Thank you! > > Jeff Ross > CargoTel, Inc. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7bd757101b892a05266a02ba
Thank you, Art. Jeff
I used SQLTRACE (both high/med) in a prd env a few months ago to track down costly sql ; I would turn it on for a few mins then toggle it off at various times over a few week period of time using the sql api. I was surprised at what I consider a low impact it had on overall performance therefore I would not be apprehensive of using it in a prd env, reserve using high mode only if you need to see the values of the host variables on the $ sql you identify . As for the buffer size, be careful as the engine may dynamically allocate segments if you set it too high. 11.50.FC7 engine. Mark
Mark, Can you please contact me at ddmueller@west.com re... this subject? Thanx, Dan -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MARK JALKIEWICZ Sent: Wednesday, December 09, 2015 10:27 AM To: ids@iiug.org Subject: Re: Performance impact of SQLTRACE [36193] I used SQLTRACE (both high/med) in a prd env a few months ago to track down costly sql ; I would turn it on for a few mins then toggle it off at various times over a few week period of time using the sql api. I was surprised at what I consider a low impact it had on overall performance therefore I would not be apprehensive of using it in a prd env, reserve using high mode only if you need to see the values of the host variables on the $ sql you identify . As for the buffer size, be careful as the engine may dynamically allocate segments if you set it too high. 11.50.FC7 engine. Mark ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.