diff options
Diffstat (limited to 'mysql-test/suite/perfschema/t/func_file_io.test')
| -rw-r--r-- | mysql-test/suite/perfschema/t/func_file_io.test | 193 |
1 files changed, 193 insertions, 0 deletions
diff --git a/mysql-test/suite/perfschema/t/func_file_io.test b/mysql-test/suite/perfschema/t/func_file_io.test new file mode 100644 index 00000000..e29b05b8 --- /dev/null +++ b/mysql-test/suite/perfschema/t/func_file_io.test @@ -0,0 +1,193 @@ +## +## WL#4814, 4.1.4 FILE IO +## +## Functional testing of File IO +## + +--source include/not_embedded.inc +--source include/have_perfschema.inc + +UPDATE performance_schema.setup_instruments SET enabled = 'NO', timed = 'YES'; + +UPDATE performance_schema.setup_instruments SET enabled = 'YES' +WHERE name LIKE 'wait/io/file/%'; + +update performance_schema.threads SET instrumented = 'YES' + WHERE PROCESSLIST_ID=connection_id(); + +# reset lost counters +truncate table performance_schema.events_statements_summary_by_digest; +flush status; + +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +# +# TODO: Change to InnoDB when it gets instrumentation +# + +CREATE TABLE t1 (id INT PRIMARY KEY, b CHAR(100) DEFAULT 'initial value') +ENGINE=MyISAM; + +INSERT INTO t1 (id) VALUES (1), (2), (3), (4), (5), (6), (7), (8); + +TRUNCATE TABLE performance_schema.events_waits_history_long; +TRUNCATE TABLE performance_schema.events_waits_history; +TRUNCATE TABLE performance_schema.events_waits_current; + +# +# FF1: Count for file should increase with instrumentation enabled and +# FF2: Count for file should not increase with instrumentation disabled +# + +SELECT * FROM t1 WHERE id = 1; + +SET @before_count = (SELECT SUM(TIMER_WAIT) + FROM performance_schema.events_waits_history_long + WHERE (EVENT_NAME = 'wait/io/file/myisam/dfile') + AND (OBJECT_NAME LIKE '%t1.MYD')); + +SELECT (@before_count >= 0) as have_before_count; +SELECT IF(@before_count > 0, 'Success', 'Failure') has_instrumentation; + +SELECT * FROM t1 WHERE id < 4; + +SET @after_count = (SELECT SUM(TIMER_WAIT) + FROM performance_schema.events_waits_history_long + WHERE (EVENT_NAME = 'wait/io/file/myisam/dfile') + AND (OBJECT_NAME LIKE '%t1.MYD') AND (1 = 1)); + +SELECT (@after_count >= 0) as have_after_count; +SELECT IF((@after_count - @before_count) > 0, 'Success', 'Failure') test_ff1_timed; + +UPDATE performance_schema.setup_instruments SET enabled='NO'; + +SET @before_count = (SELECT SUM(TIMER_WAIT) + FROM performance_schema.events_waits_history_long + WHERE (EVENT_NAME = 'wait/io/file/myisam/dfile') + AND (OBJECT_NAME LIKE '%t1.MYD') AND (2 = 2)); + +SELECT (@before_count >= 0) as have_before_count; +SELECT * FROM t1 WHERE id < 6; + +SET @after_count = (SELECT SUM(TIMER_WAIT) + FROM performance_schema.events_waits_history_long + WHERE (EVENT_NAME = 'wait/io/file/myisam/dfile') + AND (OBJECT_NAME LIKE '%t1.MYD') AND (3 = 3)); + +SELECT (@after_count >= 0) as have_after_count; +SELECT IF((COALESCE(@after_count, 0) - COALESCE(@before_count, 0)) = 0, 'Success', 'Failure') test_ff2_timed; + +# +# Check not timed measurements +# + +UPDATE performance_schema.setup_instruments SET enabled = 'YES' +WHERE name LIKE 'wait/io/file/%'; + +UPDATE performance_schema.setup_instruments SET timed = 'NO'; + +TRUNCATE TABLE performance_schema.events_waits_history_long; +TRUNCATE TABLE performance_schema.events_waits_history; +TRUNCATE TABLE performance_schema.events_waits_current; + +SELECT * FROM t1 WHERE id > 4; + +SELECT * FROM performance_schema.events_waits_history_long +WHERE TIMER_WAIT != NULL + OR TIMER_START != NULL + OR TIMER_END != NULL; + +SELECT * FROM performance_schema.events_waits_history +WHERE TIMER_WAIT != NULL + OR TIMER_START != NULL + OR TIMER_END != NULL; + +SELECT * FROM performance_schema.events_waits_current +WHERE TIMER_WAIT != NULL + OR TIMER_START != NULL + OR TIMER_END != NULL; + +UPDATE performance_schema.setup_instruments SET timed = 'YES'; + +SELECT * FROM t1 WHERE id < 4; + +DROP TABLE t1; + +# +# FF4: Use-case from Enterprise Monitor +# + +--disable_result_log +SELECT SUM(COUNT_READ) AS sum_count_read, + SUM(COUNT_WRITE) AS sum_count_write, + SUM(SUM_NUMBER_OF_BYTES_READ) AS sum_num_bytes_read, + SUM(SUM_NUMBER_OF_BYTES_WRITE) AS sum_num_bytes_write +FROM performance_schema.file_summary_by_instance +WHERE FILE_NAME LIKE CONCAT('%', @@tmpdir, '%') ORDER BY NULL; +--enable_result_log + +# +# FF5: Troubleshooting tasks +# +# These queries will give different results based on timing, +# but at least they should not crash. +# + +# +# Total and average wait time for different events on system level +# +--disable_result_log +SELECT EVENT_NAME, COUNT_STAR, AVG_TIMER_WAIT, SUM_TIMER_WAIT +FROM performance_schema.events_waits_summary_global_by_event_name +WHERE COUNT_STAR > 0 +ORDER BY SUM_TIMER_WAIT DESC +LIMIT 10; +--enable_result_log + +# +# Total and average wait time for different users +# + +--disable_result_log +SELECT p.processlist_user, SUM(TIMER_WAIT) SUM_WAIT +# ((TIME_TO_SEC(TIMEDIFF(NOW(), i.startup_time)) * 1000) / SUM(TIMER_WAIT)) * 100 WAIT_PERCENTAGE +FROM performance_schema.events_waits_history_long h +INNER JOIN performance_schema.threads p USING (THREAD_ID) +GROUP BY p.processlist_user +ORDER BY SUM_WAIT DESC +LIMIT 20; +--enable_result_log + +# +# Total and average wait times for different events for a session +# +--disable_result_log +SELECT h.EVENT_NAME, SUM(h.TIMER_WAIT) TOTAL_WAIT +FROM performance_schema.events_waits_history_long h +INNER JOIN performance_schema.threads p USING (THREAD_ID) +WHERE p.PROCESSLIST_ID = 1 +GROUP BY h.EVENT_NAME +HAVING TOTAL_WAIT > 0; +--enable_result_log + +# +# Which user reads and writes data +# + +--disable_result_log +SELECT p.processlist_user, h.operation, SUM(NUMBER_OF_BYTES) bytes +FROM performance_schema.events_waits_history_long h +INNER JOIN performance_schema.threads p USING (THREAD_ID) +GROUP BY p.processlist_user, h.operation +HAVING BYTES > 0 +ORDER BY p.processlist_user, h.operation; +--enable_result_log + +# Clean-up. +UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES'; + +# In case of failure, will indicate the root cause +show global status like "performance_schema%"; + |