Wednesday, March 2, 2011

When did that statement finish?

A friend wanted to go to lunch but need to time (roughly) how long a UPDATE took on one of his instances.
The friend had a co-worker who said the statement would take X time and the friend was pretty sure it was X/2. Me? I was hungry.

I edited the ˜.my.cnf and added the following:

[mysql]
prompt="\\r:\\m:\\s\\P>"

This changes the mysql client prompt from mysql> to 11:42:22am>. The friend started the mysql client, entered the query, and we went to lunch. We when returned, there on the prompt line was the time the command finished.

An old simple trick but it did what was needed. The documentation on the prompt string details more options. I probably would not want to use this trick for more than the crudest of benchmarks. I do use something similar to display the hostname when dealing with slaves in replication.

8 comments:

Garp said...

Am I missing something here? It tells you when the query completes exactly how long it took (alongside how many records were returned)

Shlomi N. said...

Hi,

But, the MySQL client explicitly tell you how long a query took:


root@mysql-5.1.51> SELECT 1 + SLEEP(3.5);
+----------------+
| 1 + SLEEP(3.5) |
+----------------+
| 1 |
+----------------+
1 row in set (3.50 sec)

Dave Stokes said...

Aha! I should have said wall clock time and not execution time. And yes you can add the two together but in this case we wanted to know if the job finished before noon or not.

Benjamin said...

Hi, you don't need to edit your config file, that should do the trick:
root@mysql-5.1.51> select NOW();YOUR_STATEMENT;select NOW();

Benjamin said...

Hi, you don't need to edit your config file, that should do the trick:
root@mysql-5.1.51> select NOW();YOUR_STATEMENT;select NOW();

Dave Stokes said...

Depending on the statement, the first time may scroll off. But i have used that in the past.

Darren Cassar said...

Personally I use `prompt mysql \D > ` which helps me know when certain queries were executed and at what time (if you store the new line after the query finished. The reason? I like to be able to know compare queries when testing and knowing what changes were done when you can easily go back in time to remember what you had done.

Sheeri K. Cabral said...

This is a great illustration of the prompt command. (in fact Sarah and I put neat tips like this every week in our podcast, and "prompt" was covered in episode 33 - http://bit.ly/oursql033 - and I wrote about not being able to change the 2nd line of the command prompt at http://www.pythian.com/news/1798/the-little-things/ )

---

But as a fix to that specific issue....If you're like me, you've already started the statement and waited 10 minutes...

So in the same command window that you have open, type:

SELECT NOW();

(and make sure to hit enter)

And after the query finishes, the current time will be displayed. I do this ALL the time.