320 lines
12 KiB
Plaintext
320 lines
12 KiB
Plaintext
#########
|
|
# 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 #
|
|
###########
|