MySQL: How to calculate potential wait time

MySQLmysql-5.5

I'm trying understand the relationship between WAIT_MS and COUNT_STAR. Is this saying the average wait time is 3.2s and occurred 11,543 times for a total of approximately 10.5hrs of aggregate wait time?

Your response is greatly appreciated.

| EVENT_NAME                           | WAIT_MS   | COUNT_STAR |
+--------------------------------------+-----------+------------+

| wait/io/file/innodb/innodb_data_file | 3294.3345 | 11543      |

UPDATE: Sorry! It was late. Per Mark Leith's blog: http://www.markleith.co.uk/2010/09/24/tracking-mutex-locks-in-a-process-list-mysql-55s-performance_schema/

SELECT 
   EVENT_NAME, SUM_TIMER_WAIT/1000000000 WAIT_MS, COUNT_STAR 
FROM 
   performance_schema.events_waits_summary_global_by_event_name 
ORDER BY 
   SUM_TIMER_WAIT DESC, 
   COUNT_STAR DESC;

Best Answer

Is this saying the average wait time is 3.2s and occurred 11,543 times for a total of approximately 10.5hrs of aggregate wait time?

No, this is saying the wait occurred 11,543 times for a total aggregated wait of 3.2s. The average wait time is then 3.2s / 11,543, or 0.285 milliseconds.

Note that every file io call, including file open, read, seek, close, read, write, etc is counted, and that what is measured is FILE io (at the OS level), not DISK io (at the hardware level).

Disclosure: I happen to work at MySQL, and I am the author of the performance schema.

SUGGESTION #1

You could use the MONTH() function

mysql> select MONTH(NOW());
+--------------+
| MONTH(NOW()) |
+--------------+
|            3 |
+--------------+
1 row in set (0.03 sec)

mysql> select MONTH('2013-03-17 02:53:47');
+------------------------------+
| MONTH('2013-03-17 02:53:47') |
+------------------------------+
|                            3 |
+------------------------------+
1 row in set (0.04 sec)

mysql>

You would have to put the MONTH function into the SQL command you are running.

SUGGESTION #2

You could also try the PHP Date Function

$month = date("m",$date)

Also DATE_FORMAT(date_column, '%c') to get '3' as result and DATE_FORMAT(date_column, '%m') to get '03' as result.

Best Answer

Related Solutions

Thesql event running at wrong time

Mysql – How to get only month from MySQL date/time

SUGGESTION #1

SUGGESTION #2

Related Question