Re: timed_statistics anyone?

Marc Boden <marc_at_otbbb.demon.co.uk> wrote:
>I would like to get an idea of the affect on performance of switching
>this on. Specifically, I what I want to know, is what is the change
>in system behaviour when ts wre switched on.
>
> [snip ]
>
>On the other hand, if sampling is every few milliseconds and tens of
>thousands of rows are written to some system table somewhere then I'd
>take turning them on as an occasional luxury! :-)

If you switch on timed statistics, the server has to issue two calls to the system timer ( before and after each operation ) for every parse, execute and fetch, including recursive SQL, executed by the server. For even moderately used apps, this will amount to 100's of millions of timer calls per day.

However, in my experience, the overhead is actually very little. I run timed_statistics and sql_trace = true on my production servers all day every day. No one notices when I switch it on, or occasionally switch it off. Note that sql_trace = true probably adds an even larger load, as it is continually generating trace reports and writing them to disc.

From these statistics, I know that on my servers, 52% of all commands take less than 0.0006 secs and 99.64% take less than 0.1 seconds, when these parameters are in force. That's good enough for me. OTOH, I don't know how fast they'd be if I switched timed_statistics and sql_trace off, because then I wouldn't have the timing information to measure them :-)

In my opinion, the advantage of performing the monitoring far outweighs the disadvantage of additional load. When I first start performing this sort of monitoring on a new server, I always find very inefficient SQL which can easily be tweaked to make if efficient and save many hundreds of times more CPU seconds that the additional load applied by the monitoring.

HTH, Dave.
--
Remove the no-spam bit from my email address to reply. Received on Fri Oct 23 1998 - 04:01:22 CDT