Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
Home » Performance » MySQL » Profiling stored procedures
Profiling stored procedures [message #1092] Fri, 13 April 2007 11:01 Go to next message
crown is currently offline 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 Go to previous messageGo to next message
Speeple is currently offline 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 Go to previous messageGo to next message
crown is currently offline 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 Go to previous message
Peter is currently offline 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:
  

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

Current Time: Sun Jul 5 15:44:40 EDT 2009

Total time taken to generate the page: 0.01901 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.