What are the Performance Hits using SQLTRACE?
Dan asked how much overhead SQLTRACE adds on a busy AIX/IDS 11.50 production server at low, medium or high levels. Responses said the impact is minimal: low/medium are near-negligible (high may be noticeable). Practical advice followed: start tracing via the sysadmin task()/admin() functions rather than ONCONFIG, size the buffer (it comes from virtual shared memory) based on syssqltrace_info sql_seen/duration, filter by database or user, dump long-running SQL periodically via cron, and give sysadmin/rootdbs enough space for the trace tables; 11.70 excludes scheduler threads by default. Dan confirmed this helped, but his follow-up about statements showing only as "compiled statement" got no answer in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
AIX 6 IDS11.50.FC7W3 I need to find my query bad guys after a new software release and want to use SQLTRACE. I have done my homework and have played with it in test but I do not have a box that will get hit anywhere near as hard as my production box will. Can nayone give me a good ballpark figure as to how much setting SQLTRACE on to low will affect my performance? ALso what would be the ramifications of upping it to med or high? TIA, Dan
According to a presentation John Miller gave last year LOW and MEDIUM should have little impact on performance. HIGH may be noticeable. I have turned SQLTRACE on to medium on some servers for a time and haven't noticed any problems. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Thu, Mar 21, 2013 at 9:42 AM, DAN MUELLER <ddmueller@intercall.com>wrote: > AIX 6 > IDS11.50.FC7W3 > > I need to find my query bad guys after a new software release and want to > use > SQLTRACE. I have done my homework and have played with it in test but I do > not > have a box that will get hit anywhere near as hard as my production box > will. > > Can nayone give me a good ballpark figure as to how much setting SQLTRACE > on > to low will affect my performance? ALso what would be the ramifications of > upping it to med or high? > > TIA, > Dan > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e01160ddec0ba7d04d86fa735
I use SQLTRACE on production RHEL 5.8 system running IDS 11.50FC8 Frequently. I have a couple hundred, similar installation that sometme need to be searched for slow SQL I have some advice, which may sound strange, but it may help you. 1) Never set up the SQLTRACE through the ONCONFIG setting. This will start a monitoring job in the sysadmin DB that will capture the SQLTRACE output to a table and if you need to capture large amounts of data this will fill space in sysmaster ( or sysadmin, I forget ) Always start SQLTRACE through the sysadmin task stored procedure or admin stored procedure. 2) The number of SQL to keep and the size will be allocated out of SHMVIRT so start small and use the task/admin functions to size it to what you can hold. 3) When you first start it you will probably not have a good idea of how many SQL are run in a minute, or hour, use the sysmaster.syssqltrace_info table information sql_seen and duration, and starttime to figure out the size that will accommodate your needs. 4) If you are looking for just long running single SQL statements you may wish to size your SQLTRACE to hold only a minute of SQL then dump the SQL with long runtimes once a minutes through cron. ( The dump from scanning the couple hundred thousand SQL in SHMVIRT will show up as long running, may want to filter out on sql_database or filter results based on "sqltrace" stuff ) 5) The amount of resources for running the SQLTRACE ( in my experience ) is nominal I start it and stop on production machines without anyone in the user base even noticing. Machine load is unchanged and percentage of CPU is not noticeably changed. The tracing is _very_ light. For what I have I am running DB with a total memory footprint of about 5.5G, Disk of about 200G and 6 CPU VP. The DB is not the only thing on the box and they are not large boxes. mostly the difference in CPU utilization is only 1 percentage point ( but CPU is not an issue on these machines ) For the traces I run, I have run up to 200,000 traces of size 2K or 8K in low and that gives me the info I need for one to two minutes ( Lots more statements in an OLTP system then you might believe on the onset.) These are just suggestions. Post back what works for you after you figure it out. George. From: "DAN MUELLER" <ddmueller@intercall.com> To: ids@iiug.org Date: 03/21/2013 08:43 AM Subject: What are the Performance Hits using SQLTRACE? [29850] Sent by: ids-bounces@iiug.org AIX 6 IDS11.50.FC7W3 I need to find my query bad guys after a new software release and want to use SQLTRACE. I have done my homework and have played with it in test but I do not have a box that will get hit anywhere near as hard as my production box will. Can nayone give me a good ballpark figure as to how much setting SQLTRACE on to low will affect my performance? ALso what would be the ramifications of upping it to med or high? TIA, Dan ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
George: Thanks you for providing this information. I will just make a one additional comments on SQLTRACE. In version 11.70 we realized that tracing the database scheduler threads by default is probably unwanted by our customers so these threads have tracing turned off by default in 11.70. John F. Miller III STSM, Lead Architect miller3@us.ibm.com 503-747-1366 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 03/21/2013 07:13:28 AM: > From: "George_Palmer@aotx.uscourts.gov" <George_Palmer@aotx.uscourts.gov> > To: ids@iiug.org, > Date: 03/21/2013 07:18 AM > Subject: Re: What are the Performance Hits using SQLTRACE? [29853] > Sent by: ids-bounces@iiug.org > > I use SQLTRACE on production RHEL 5.8 system running IDS 11.50FC8 > Frequently. > > I have a couple hundred, similar installation that sometme need to be > searched for slow SQL > > I have some advice, which may sound strange, but it may help you. > > 1) Never set up the SQLTRACE through the ONCONFIG setting. This will start > a monitoring job in the sysadmin DB that will capture the SQLTRACE output > to a table and if you need to capture large amounts of data this will fill > space in sysmaster ( or sysadmin, I forget ) Always start SQLTRACE through > the sysadmin task stored procedure or admin stored procedure. > > 2) The number of SQL to keep and the size will be allocated out of SHMVIRT > so start small and use the task/admin functions to size it to what you can > hold. > > 3) When you first start it you will probably not have a good idea of how > many SQL are run in a minute, or hour, use the sysmaster.syssqltrace_info > table information sql_seen and duration, and starttime to figure out the > size that will accommodate your needs. > > 4) If you are looking for just long running single SQL statements you may > wish to size your SQLTRACE to hold only a minute of SQL then dump the SQL > with long runtimes once a minutes through cron. ( The dump from scanning > the couple hundred thousand SQL in SHMVIRT will show up as long running, > may want to filter out on sql_database or filter results based on > "sqltrace" stuff ) > > 5) The amount of resources for running the SQLTRACE ( in my experience ) is > nominal I start it and stop on production machines without anyone in the > user base even noticing. Machine load is unchanged and percentage of CPU is > not noticeably changed. The tracing is _very_ light. > > For what I have I am running DB with a total memory footprint of about > 5.5G, Disk of about 200G and 6 CPU VP. The DB is not the only thing on the > box and they are not large boxes. mostly the difference in CPU utilization > is only 1 percentage point ( but CPU is not an issue on these machines ) > > For the traces I run, I have run up to 200,000 traces of size 2K or 8K in > low and that gives me the info I need for one to two minutes ( Lots more > statements in an OLTP system then you might believe on the onset.) > > These are just suggestions. Post back what works for you after you figure > it out. > > George. > > From: "DAN MUELLER" <ddmueller@intercall.com> > To: ids@iiug.org > Date: 03/21/2013 08:43 AM > Subject: What are the Performance Hits using SQLTRACE? [29850] > Sent by: ids-bounces@iiug.org > > AIX 6 > IDS11.50.FC7W3 > > I need to find my query bad guys after a new software release and want to > use > SQLTRACE. I have done my homework and have played with it in test but I do > not > have a box that will get hit anywhere near as hard as my production box > will. > > Can nayone give me a good ballpark figure as to how much setting SQLTRACE > on > to low will affect my performance? ALso what would be the ramifications of > upping it to med or high? > > TIA, > Dan > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Dan,
I try and limit the SQLTRACE to only the database and/or users in question.
This is AIX 6.1 IDS 11.50.FC4
dbaccess -e sysadmin <<EOF 2>&1 | grep .execute function task("set sql tracing database clear");
execute function task("set sql tracing database add", "custfilep");
execute function task("set sql tracing database add", "commacc");
execute function task("set sql tracing user clear");
execute function task("set sql tracing user add", "cfile");
execute function task("set sql tracing user add", "commacct");
execute function task ("set sql tracing on", 20000, "4k", "high", "user");
execute function task("set sql tracing database list");
execute function task("set sql tracing user list");
execute function task ("set sql tracing info");
update ph_task set tk_enable = "t"
where tk_name = "Save SQL Trace";EOF
On my server I hit the 20000 trace queue in about 45 seconds. I set the Save
SQL Trace to run every minute to get a good sampling. I let this run for about
6 minutes and then run the following to turn off.
dbaccess -e sysadmin <<EOF 2>&1 | grep .execute function task("set sql tracing off");
execute function task ("set sql tracing info");
update ph_task set tk_enable = "f"
where tk_name = "Save SQL Trace";EOF
Make sure your sysadmin database is located in it's own dbspace with plenty of
disk space. Also these 4 tables are in the sysmaster database as raw tables so
they won't replicate for HDR/RSS. Make sure your rootdbs has enough space to
handle these tables.
sysmaster:
syssqltrace
syssqltrace_hvar
syssqltrace_info
syssqltrace_iter
Make sure your rootdbs has enough space to handle these tables.
I moved the sysadmin:mon_syssqltrace, mon_syssqltrace_hvar,
mon_syssqltrace_info
mon_syssqltrace_iter to a 16K dbspace and changed the first and next size on
the tables. This is where the save sql trace job copies the data from the
sysmaster tables.
These tables could also stand to use a couple of indexes to help with the
performance of some of the OAT queries.
Kernoal
Thanx for all the great responses. I am well on my way to finding out who is eating my lunch.
Related Question - Most of the sql statements just say "compiled statement". Is there any way to get the real statements?