MySQL Performance: MySQL => Profiling stored procedures

Members

Help

Home

Home » Performance » MySQL » Profiling stored procedures

Show: Today's Messages :: Show Polls :: Message Navigator

Profiling stored procedures [message #1092]

Fri, 13 April 2007 11:01

crown
Messages: 2
Registered: April 2007

Junior Member

I'm trying to take some performance measurements of our stored procedures. I've already setup a debug table to log messages but is there any way to get milli- or microsecond precision? The timestamp field doesn't offer this and I don't see a way to get a time with either unix_timestamp() or now() or other functions with microseconds.

Any suggestions?

Report message to a moderator

Re: Profiling stored procedures [message #1094 is a reply to message #1092 ]

Fri, 13 April 2007 13:03

Speeple
Messages: 91
Registered: August 2006

Member

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functio ns.html#function_date-format

You could try building a function to return microseconds using the modifier:

%f - Microseconds (000000..999999)

However I'm not sure if DATE_FORMAT(NOW(), 'you_format') has microsecond precision. On my machine "SELECT DATE_FORMAT(NOW(), '%s %f')" doesn't give ms accuracy.

Martin Gallagher | Speeple: The latest news

Report message to a moderator

Re: Profiling stored procedures [message #1097 is a reply to message #1092 ]

Fri, 13 April 2007 14:28

crown
Messages: 2
Registered: April 2007

Junior Member

Figured it out. Was able to make it work using the UDF function here:

http://bugs.mysql.com/bug.php?id=8523

It gives millisecond precision which is good enough for now. It's simple enough to modify for microsecond precision as well.

Report message to a moderator

Re: Profiling stored procedures [message #1124 is a reply to message #1092 ]

Fri, 20 April 2007 18:18

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

I'm surprised why MySQL for years can't make accurate time counting implemented which is needed for a lot of things starting from internal counting to values in SHOW PROCESSLIST and slow query log.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Previous Topic:	Looking for Suggestions on Improving Performance
Next Topic:	MySQL Performance Concerns

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Tue Jan 6 21:04:15 EST 2009

Total time taken to generate the page: 0.02197 seconds

.:: Contact :: Home :: MySQL Support by Percona.com ::.