######### # SETUP # ######### INSTALL PLUGIN pfs_example_plugin_employee SONAME 'pfs_example_plugin_employee.xxx'; show tables in performance_schema where Tables_in_performance_schema like "%pfs_example%"; Tables_in_performance_schema pfs_example_employee_name pfs_example_employee_salary pfs_example_machine pfs_example_machine_by_employee_by_type select TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE from information_schema.tables where TABLE_NAME like "pfs_example%" AND TABLE_SCHEMA= "performance_schema"; TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE performance_schema pfs_example_employee_name BASE TABLE PERFORMANCE_SCHEMA performance_schema pfs_example_employee_salary BASE TABLE PERFORMANCE_SCHEMA performance_schema pfs_example_machine BASE TABLE PERFORMANCE_SCHEMA performance_schema pfs_example_machine_by_employee_by_type BASE TABLE PERFORMANCE_SCHEMA ############## # Operations # ############## ------------------------------------------------------------- Displaying records inserted from plugin/component code itself ------------------------------------------------------------- select * from performance_schema.pfs_example_employee_name; EMPLOYEE_NUMBER FIRST_NAME LAST_NAME 1 foo1 bar1 2 foo2 bar2 3 foo3 bar3 select * from performance_schema.pfs_example_employee_salary; EMPLOYEE_NUMBER EMPLOYEE_SALARY DATE_OF_BIRTH TIME_OF_BIRTH 1 1000 2013-11-12 12:02:34 2 2000 2016-02-29 12:12:30 3 3000 2017-03-24 11:12:50 select * from performance_schema.pfs_example_machine; MACHINE_SL_NUMBER MACHINE_TYPE MACHINE_MADE EMPLOYEE_NUMBER 1 DESKTOP Lenovo 1 2 LAPTOP Dell 2 3 MOBILE Apple 1 4 MOBILE Samsung 1 5 LAPTOP Lenovo 2 6 MOBILE Nokia 2 7 LAPTOP Apple 1 8 LAPTOP HP 3 9 DESKTOP Apple 3 select * from performance_schema.pfs_example_machine_by_employee_by_type; FIRST_NAME LAST_NAME MACHINE_TYPE COUNT foo1 bar1 LAPTOP 1 foo1 bar1 DESKTOP 1 foo1 bar1 MOBILE 2 foo2 bar2 LAPTOP 2 foo2 bar2 MOBILE 1 foo3 bar3 LAPTOP 1 foo3 bar3 DESKTOP 1 ------------------------------- Inserting rows in plugin tables ------------------------------- insert into performance_schema.pfs_example_employee_name values (4, "foo4", "bar4"); insert into performance_schema.pfs_example_employee_salary values (4, 4000, "2013-12-12", "10:10:10"); insert into performance_schema.pfs_example_machine values (10, 'LAPTOP', "IBM", 4); insert into performance_schema.pfs_example_employee_salary values (NULL,1000000000,NULL,NULL); insert into performance_schema.pfs_example_employee_salary (employee_salary,date_of_birth) values (10000000,'1988-04-17'); insert into performance_schema.pfs_example_machine_by_employee_by_type values ("a", "b", LAPTOP, 101); ERROR 42000: INSERT command denied to user 'root'@'localhost' for table 'pfs_example_machine_by_employee_by_type' select * from performance_schema.pfs_example_employee_name; EMPLOYEE_NUMBER FIRST_NAME LAST_NAME 1 foo1 bar1 2 foo2 bar2 3 foo3 bar3 4 foo4 bar4 select * from performance_schema.pfs_example_employee_salary; EMPLOYEE_NUMBER EMPLOYEE_SALARY DATE_OF_BIRTH TIME_OF_BIRTH 1 1000 2013-11-12 12:02:34 2 2000 2016-02-29 12:12:30 3 3000 2017-03-24 11:12:50 4 4000 2013-12-12 10:10:10 NULL 1000000000 NULL NULL NULL 10000000 1988-04-17 NULL select * from performance_schema.pfs_example_machine; MACHINE_SL_NUMBER MACHINE_TYPE MACHINE_MADE EMPLOYEE_NUMBER 1 DESKTOP Lenovo 1 2 LAPTOP Dell 2 3 MOBILE Apple 1 4 MOBILE Samsung 1 5 LAPTOP Lenovo 2 6 MOBILE Nokia 2 7 LAPTOP Apple 1 8 LAPTOP HP 3 9 DESKTOP Apple 3 10 LAPTOP IBM 4 select * from performance_schema.pfs_example_machine_by_employee_by_type; FIRST_NAME LAST_NAME MACHINE_TYPE COUNT foo1 bar1 LAPTOP 1 foo1 bar1 DESKTOP 1 foo1 bar1 MOBILE 2 foo2 bar2 LAPTOP 2 foo2 bar2 MOBILE 1 foo3 bar3 LAPTOP 1 foo3 bar3 DESKTOP 1 foo4 bar4 LAPTOP 1 ----------------------------------- Fetching records from plugin tables ----------------------------------- select FIRST_NAME, LAST_NAME, EMPLOYEE_SALARY from performance_schema.pfs_example_employee_name, performance_schema.pfs_example_employee_salary where performance_schema.pfs_example_employee_name.EMPLOYEE_NUMBER= performance_schema.pfs_example_employee_salary.EMPLOYEE_NUMBER; FIRST_NAME LAST_NAME EMPLOYEE_SALARY foo1 bar1 1000 foo2 bar2 2000 foo3 bar3 3000 foo4 bar4 4000 ------------------------------ Updating rows in plugin tables ------------------------------ update performance_schema.pfs_example_employee_name set FIRST_NAME="foo42", LAST_NAME="bar42" where LAST_NAME="bar4"; update performance_schema.pfs_example_employee_salary set EMPLOYEE_SALARY=8000 where EMPLOYEE_NUMBER=2; update performance_schema.pfs_example_machine set MACHINE_TYPE = 'DESKTOP' where MACHINE_SL_NUMBER=10; update performance_schema.pfs_example_machine_by_employee_by_type set count=0 where MACHINE_SL_NUMBER=10; ERROR 42000: UPDATE command denied to user 'root'@'localhost' for table 'pfs_example_machine_by_employee_by_type' select * from performance_schema.pfs_example_employee_name; EMPLOYEE_NUMBER FIRST_NAME LAST_NAME 1 foo1 bar1 2 foo2 bar2 3 foo3 bar3 4 foo42 bar42 select * from performance_schema.pfs_example_employee_salary; EMPLOYEE_NUMBER EMPLOYEE_SALARY DATE_OF_BIRTH TIME_OF_BIRTH 1 1000 2013-11-12 12:02:34 2 8000 2016-02-29 12:12:30 3 3000 2017-03-24 11:12:50 4 4000 2013-12-12 10:10:10 NULL 1000000000 NULL NULL NULL 10000000 1988-04-17 NULL select * from performance_schema.pfs_example_machine; MACHINE_SL_NUMBER MACHINE_TYPE MACHINE_MADE EMPLOYEE_NUMBER 1 DESKTOP Lenovo 1 2 LAPTOP Dell 2 3 MOBILE Apple 1 4 MOBILE Samsung 1 5 LAPTOP Lenovo 2 6 MOBILE Nokia 2 7 LAPTOP Apple 1 8 LAPTOP HP 3 9 DESKTOP Apple 3 10 DESKTOP IBM 4 select * from performance_schema.pfs_example_machine_by_employee_by_type; FIRST_NAME LAST_NAME MACHINE_TYPE COUNT foo1 bar1 LAPTOP 1 foo1 bar1 DESKTOP 1 foo1 bar1 MOBILE 2 foo2 bar2 LAPTOP 2 foo2 bar2 MOBILE 1 foo3 bar3 LAPTOP 1 foo3 bar3 DESKTOP 1 foo42 bar42 DESKTOP 1 ----------------------------------- Fetching records from plugin tables ----------------------------------- select FIRST_NAME, LAST_NAME, EMPLOYEE_SALARY from performance_schema.pfs_example_employee_name, performance_schema.pfs_example_employee_salary where performance_schema.pfs_example_employee_name.EMPLOYEE_NUMBER= performance_schema.pfs_example_employee_salary.EMPLOYEE_NUMBER; FIRST_NAME LAST_NAME EMPLOYEE_SALARY foo1 bar1 1000 foo2 bar2 8000 foo3 bar3 3000 foo42 bar42 4000 ----------------------------------- Verify indexes usage ----------------------------------- explain select * from performance_schema.pfs_example_employee_name where EMPLOYEE_NUMBER=2; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE pfs_example_employee_name NULL const PRIMARY PRIMARY 4 const 1 100.00 NULL insert into performance_schema.pfs_example_employee_name values (5,"foo","bar"), (6,"foofoo","barbar") , (7,"foo","bar"), (8,"foofoo","barbar") , (9,"foo","bar"), (10,"foofoo","barbar") , (11,"foo","bar"), (12,"foofoo","barbar") , (13,"foo","bar"), (14,"foofoo","barbar") , (15,"foo","bar"), (16,"foofoo","barbar") , (17,"foo","bar"), (18,"foofoo","barbar") , (19,"foo","bar"), (20,"foofoo","barbar") , (21,"foo","bar"), (22,"foofoo","barbar") , (23,"foo","bar"), (24,"foofoo","barbar") , (25,"foo","bar"), (26,"foofoo","barbar") , (27,"foo","bar"), (28,"foofoo","barbar") , (29,"foo","bar"), (30,"foofoo","barbar") , (31,"foo","bar"), (32,"foofoo","barbar") , (33,"foo","bar"), (34,"foofoo","barbar") , (35,"foo","bar"), (36,"foofoo","barbar") , (37,"foo","bar"), (38,"foofoo","barbar") , (39,"foo","bar"), (40,"foofoo","barbar") , (41,"foo","bar"), (42,"foofoo","barbar") , (43,"foo","bar"), (44,"foofoo","barbar") , (45,"foo","bar"), (46,"foofoo","barbar") , (47,"foo","bar"), (48,"foofoo","barbar") , (49,"foo","bar"), (50,"foofoo","barbar") , (51,"foo","bar"), (52,"foofoo","barbar") , (53,"foo","bar"), (54,"foofoo","barbar") , (55,"foo","bar"), (56,"foofoo","barbar") , (57,"foo","bar"), (58,"foofoo","barbar") , (59,"foo","bar"), (60,"foofoo","barbar"); explain select * from performance_schema.pfs_example_employee_name where FIRST_NAME='foo'; id select_type table partitions type possible_keys key key_len ref rows filtered Extra 1 SIMPLE pfs_example_employee_name NULL ref FIRST_NAME FIRST_NAME 81 const 10 100.00 Using where ------------------------------------------------- Verify stats collection in PFS stats tables ------------------------------------------------- update performance_schema.setup_consumers set ENABLED='YES'; update performance_schema.setup_instruments set ENABLED='YES', TIMED='YES'; insert into performance_schema.setup_objects values ('TABLE', "performance_schema", "pfs_example_employee_name", 'YES', 'YES'); truncate table performance_schema.table_io_waits_summary_by_table; truncate table performance_schema.table_io_waits_summary_by_index_usage; ---------- insert query ---------- insert into performance_schema.pfs_example_employee_name values (61, "foo5", "bar5"); ---------- table I/O stat by table name ---------- select COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where OBJECT_NAME="pfs_example_employee_name"; COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE 1 0 1 0 0 ---------- table I/O stat by index usage ---------- select INDEX_NAME, COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE from performance_schema.table_io_waits_summary_by_index_usage where OBJECT_NAME="pfs_example_employee_name"; INDEX_NAME COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE PRIMARY 0 0 0 0 0 FIRST_NAME 0 0 0 0 0 NULL 1 0 1 0 0 ---------- update query with EMPLOYEE_NUMBER ---------- update performance_schema.pfs_example_employee_name set LAST_NAME="bar55" where EMPLOYEE_NUMBER=61; ---------- table I/O stat by table name ---------- select COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where OBJECT_NAME="pfs_example_employee_name"; COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE 3 1 1 1 0 ---------- table I/O stat by index usage ---------- select INDEX_NAME, COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE from performance_schema.table_io_waits_summary_by_index_usage where OBJECT_NAME="pfs_example_employee_name"; INDEX_NAME COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE PRIMARY 2 1 0 1 0 FIRST_NAME 0 0 0 0 0 NULL 1 0 1 0 0 ---------- update query with FIRST_NAME ---------- update performance_schema.pfs_example_employee_name set LAST_NAME="bar56" where FIRST_NAME="foo5"; ---------- table I/O stat by table name ---------- select COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where OBJECT_NAME="pfs_example_employee_name"; COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE 6 3 1 2 0 ---------- table I/O stat by index usage ---------- select INDEX_NAME, COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE from performance_schema.table_io_waits_summary_by_index_usage where OBJECT_NAME="pfs_example_employee_name"; INDEX_NAME COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE PRIMARY 2 1 0 1 0 FIRST_NAME 3 2 0 1 0 NULL 1 0 1 0 0 ---------- delete query ---------- delete from performance_schema.pfs_example_employee_name where EMPLOYEE_NUMBER=61; ---------- table I/O stat by table name ---------- select COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE from performance_schema.table_io_waits_summary_by_table where OBJECT_NAME="pfs_example_employee_name"; COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE 8 4 1 2 1 ---------- table I/O stat by index usage ---------- select INDEX_NAME, COUNT_STAR, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE from performance_schema.table_io_waits_summary_by_index_usage where OBJECT_NAME="pfs_example_employee_name"; INDEX_NAME COUNT_STAR COUNT_FETCH COUNT_INSERT COUNT_UPDATE COUNT_DELETE PRIMARY 4 2 0 1 1 FIRST_NAME 3 2 0 1 0 NULL 1 0 1 0 0 delete from performance_schema.setup_objects where OBJECT_NAME="pfs_example_employee_name"; ------------------------------------------------- Fetching from plugin table after plugin uninstall ------------------------------------------------- UNINSTALL PLUGIN pfs_example_plugin_employee; select * from performance_schema.pfs_example_employee_name; ERROR 42S02: Table 'performance_schema.pfs_example_employee_name' doesn't exist select * from performance_schema.pfs_example_employee_salary; ERROR 42S02: Table 'performance_schema.pfs_example_employee_salary' doesn't exist show tables in performance_schema where Tables_in_performance_schema like "%pfs_example%"; Tables_in_performance_schema ########### # CLEANUP # ###########