From: Christopher Powers Date: August 19 2011 7:04am Subject: bzr push into mysql-trunk branch (chris.powers:3383 to 3384) WL#4896 List-Archive: http://lists.mysql.com/commits/140714 Message-Id: <201108190704.p7J74nP0021397@acsmt358.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3384 Christopher Powers 2011-08-19 WL#4896 "Performance Schema Net IO" New test: socket_summary_by_event_name_func.test added: mysql-test/suite/perfschema/r/socket_summary_by_event_name_func.result mysql-test/suite/perfschema/t/socket_summary_by_event_name_func.test 3383 Christopher Powers 2011-08-19 WL#4896 "Performance Schema Net IO" Final version of socket_summary_by_instance_func.test modified: mysql-test/suite/perfschema/include/socket_check1.inc mysql-test/suite/perfschema/include/socket_event.inc mysql-test/suite/perfschema/include/socket_summary_check.inc mysql-test/suite/perfschema/r/socket_summary_by_instance_func.result* mysql-test/suite/perfschema/t/socket_summary_by_instance_func.test === added file 'mysql-test/suite/perfschema/r/socket_summary_by_event_name_func.result' --- a/mysql-test/suite/perfschema/r/socket_summary_by_event_name_func.result 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/perfschema/r/socket_summary_by_event_name_func.result 2011-08-19 07:04:02 +0000 @@ -0,0 +1,121 @@ +#============================================================================== +# Establish the level of IPV6 support +#============================================================================== +#============================================================================== +# Get hostname, port number +#============================================================================== +SELECT @@hostname INTO @MY_HOSTNAME; +SELECT @@port INTO @MY_MASTER_PORT; +#============================================================================== +# 1.0 TEST INITIALIZATION +#============================================================================== + +# 1.1 Disable instrumentation of the default (this) connection + +UPDATE performance_schema.threads +SET INSTRUMENTED='NO' WHERE PROCESSLIST_ID = CONNECTION_ID(); + +# 1.2 Get the default THREAD_ID; + +SELECT THREAD_ID INTO @my_thread_id +FROM performance_schema.threads +WHERE PROCESSLIST_ID = CONNECTION_ID(); + +# 1.3 Get the default OBJECT_INSTANCE_BEGIN + +SELECT OBJECT_INSTANCE_BEGIN INTO @my_object_instance_begin +FROM performance_schema.socket_instances +WHERE THREAD_ID = @my_thread_id; + +# 1.4 Create a test database + +CREATE SCHEMA mysqltest; + +# 1.5 Create a table to store summary values from socket_summary_by_instance +and socket_summary_by_event_name + +CREATE TABLE mysqltest.my_socket_summary AS +SELECT * FROM performance_schema.socket_summary_by_instance +WHERE 1 = 0; + +# 1.6 Drop object_instance_begin from my_socket_summary + +ALTER TABLE mysqltest.my_socket_summary +DROP COLUMN OBJECT_INSTANCE_BEGIN; + +# 1.7 Add an auto_inc column to my_socket_summary + +ALTER TABLE mysqltest.my_socket_summary +ADD COLUMN (n INT AUTO_INCREMENT, PRIMARY KEY(n)); + +# 1.8 Create test tables + +CREATE TABLE mysqltest.t1 (n INT AUTO_INCREMENT, s1 VARCHAR(1024), PRIMARY KEY(n)); +CREATE TABLE mysqltest.t2 (n INT AUTO_INCREMENT, s1 VARCHAR(1024), PRIMARY KEY(n)); +#============================================================================== +# 2.0 ESTABLISH CLIENT CONNECTIONS +#============================================================================== + +# 2.1 Connection 1 (tcp/ip, 127.0.0.1 or ::1) + + +# 2.2 Connection 2 (localhost or unix domain socket) + +#============================================================================== +# 3.0 RUN THE TESTS +#============================================================================== + +# 3.1 Clear performance schema tables + +TRUNCATE performance_schema.socket_summary_by_instance; +TRUNCATE performance_schema.socket_summary_by_event_name; + +# 3.2 Get the 'before' sum of bytes written from socket_summary_by_instance +for later comparison to the 'after' byte count as a simple confirmation +that the table was updated. + +SELECT sum(SUM_NUMBER_OF_BYTES_WRITE) INTO @my_write_count +FROM performance_schema.socket_summary_by_instance; + +# 3.3 From connection 1, insert one a 1K row of data into t1 + +USE mysqltest; +INSERT INTO t1 (s1) VALUES (REPEAT('a', 1024)); +INSERT INTO t1 (s1) SELECT s1 FROM t1; + +# 3.4 From connection 2, insert one a 1K row of data into t2 + +USE mysqltest; +INSERT INTO t2 (s1) VALUES (REPEAT('a', 1024)); +INSERT INTO t2 (s1) SELECT s1 FROM t2; + +# 3.5 Get the 'after' sum of bytes written from socket_summary_by_instance + +SELECT sum(SUM_NUMBER_OF_BYTES_WRITE) INTO @my_write_count +FROM performance_schema.socket_summary_by_instance; + +# 3.6 Verify that SUM_NUMBER_OF_BYTES_WRITE increased + + +# socket_summary_by_instance was updated + +#============================================================================== +# 4.0 VERIFY RESULTS +#============================================================================== + +# 4.1 Verify that the totals in socket_summary_by_event_name are +consistent with totals in socket_summary_by_instance + +#============================================================================== +# 5.0 Clean up +#============================================================================== + +# 5.1 Disconnect con1 + + +# 5.2 Disconnect con2 + + +# 5.3 Drop mysqltest + +DROP DATABASE mysqltest; === added file 'mysql-test/suite/perfschema/t/socket_summary_by_event_name_func.test' --- a/mysql-test/suite/perfschema/t/socket_summary_by_event_name_func.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/perfschema/t/socket_summary_by_event_name_func.test 2011-08-19 07:04:02 +0000 @@ -0,0 +1,348 @@ +# Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved. +# +# This program is free software; you can redistribute it and/or +# modify it under the terms of the GNU General Public License +# as published by the Free Software Foundation; version 2 of +# the License. +# +# This program is distributed in the hope that it will be useful, +# but WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. +# +# You should have received a copy of the GNU General Public License +# along with this program; if not, write to the Free Software +# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA +# 02110-1301 USA +# +# Some functional checks for the content of the performance_schema table +# socket_summary_by_event_name. +# +# Created: cpowers 2011-08-18 +#============================================================================== +# +# Performance schema table socket_summary_by_event_name holds statistics +# aggreagated by event name. Table socket_summary_by_instances maintains statistics +# per socket instance. +# +# This test verifies that the statistics in socket_summary_by_event_name +# are consistent with those in socket_summary_by_instance. +# +#============================================================================== +# Embedded server does not support the performance_schema. +--source include/not_embedded.inc + +# Not much can happen without the performance schema +--source include/have_perfschema.inc + +# Wait for any clients from previous tests to disconnect +--source ../include/wait_for_pfs_thread_count.inc + +# The values in the performance_schema tables depend on how much communication +# happens per SQL statement within our MTR tests. And there is a significant +# difference between standard statement execution and execution via +# prepared statement. +--source ../include/no_protocol.inc + +# Set this to enable debugging output +let $my_socket_debug=0; + +--echo #============================================================================== +--echo # Establish the level of IPV6 support +--echo #============================================================================== +--source ../include/socket_ipv6.inc + +--echo #============================================================================== +--echo # Get hostname, port number +--echo #============================================================================== +SELECT @@hostname INTO @MY_HOSTNAME; +SELECT @@port INTO @MY_MASTER_PORT; + +if ($my_socket_debug) +{ + SELECT @MY_HOSTNAME AS 'Hostname'; + SELECT @MY_MASTER_PORT AS 'Master Port'; +} +#============================================================================== +# Utility queries +#============================================================================== +let $count_client_connections= + SELECT COUNT(*) INTO @my_client_connections + FROM performance_schema.socket_instances + WHERE EVENT_NAME LIKE "%client_connection%"; + +let $get_thread_id= + SELECT THREAD_ID INTO @my_thread_id + FROM performance_schema.threads + WHERE PROCESSLIST_ID = CONNECTION_ID(); + +let $get_object_instance_begin= + SELECT OBJECT_INSTANCE_BEGIN INTO @my_object_instance_begin + FROM performance_schema.socket_instances + WHERE THREAD_ID = @my_thread_id; + +let $get_port= + SELECT PORT INTO @my_port + FROM performance_schema.socket_instances + WHERE THREAD_ID = @my_thread_id; + +let $get_write_count= +SELECT sum(SUM_NUMBER_OF_BYTES_WRITE) INTO @my_write_count + FROM performance_schema.socket_summary_by_instance; +#============================================================================== +# Testcase queries +#============================================================================== +let $insert_totals= + INSERT INTO mysqltest.my_socket_summary SELECT EVENT_NAME, + SUM(t1.COUNT_STAR), SUM(t1.SUM_TIMER_WAIT), SUM(t1.MIN_TIMER_WAIT), SUM(t1.AVG_TIMER_WAIT), SUM(t1.MAX_TIMER_WAIT), + SUM(t1.COUNT_READ), SUM(t1.SUM_TIMER_READ), SUM(t1.MIN_TIMER_READ), SUM(t1.AVG_TIMER_READ), SUM(t1.MAX_TIMER_READ), SUM(t1.SUM_NUMBER_OF_BYTES_READ), + SUM(t1.COUNT_WRITE), SUM(t1.SUM_TIMER_WRITE), SUM(t1.MIN_TIMER_WRITE), SUM(t1.AVG_TIMER_WRITE), SUM(t1.MAX_TIMER_WRITE), SUM(t1.SUM_NUMBER_OF_BYTES_WRITE), + SUM(t1.COUNT_MISC), SUM(t1.SUM_TIMER_MISC), SUM(t1.MIN_TIMER_MISC), SUM(t1.AVG_TIMER_MISC), SUM(t1.MAX_TIMER_MISC), NULL + FROM performance_schema.socket_summary_by_instance AS t1 + WHERE EVENT_NAME LIKE '%client_connection%' GROUP BY EVENT_NAME; + +let $compare_tables_and_print= +SELECT EVENT_NAME, + (SUM(t_inst.COUNT_STAR) = t_name.COUNT_STAR) AS CNT_STAR, + (SUM(t_inst.SUM_TIMER_WAIT) = t_name.SUM_TIMER_WAIT) AS SUM_WAIT, + (MAX(t_inst.MAX_TIMER_WAIT) = t_name.MAX_TIMER_WAIT) AS MAX_WAIT, +# (MIN(t_inst.MIN_TIMER_WAIT) = t_name.MIN_TIMER_WAIT) AS MIN_WAIT, + (SUM(t_inst.COUNT_READ) = t_name.COUNT_READ) AS CNT_READ, + (SUM(t_inst.SUM_TIMER_READ) = t_name.SUM_TIMER_READ) AS SUM_READ, + (MAX(t_inst.MAX_TIMER_READ) = t_name.MAX_TIMER_READ) AS MAX_READ, +# (MIN(t_inst.MIN_TIMER_READ) = t_name.MIN_TIMER_READ) AS MIN_READ, + (SUM(t_inst.SUM_NUMBER_OF_BYTES_READ) = t_name.SUM_NUMBER_OF_BYTES_READ) AS BYTES_READ, + (SUM(t_inst.COUNT_WRITE) = t_name.COUNT_WRITE) AS CNT_WRITE, + (SUM(t_inst.SUM_TIMER_WRITE) = t_name.SUM_TIMER_WRITE) AS SUM_WRITE, + (MAX(t_inst.MAX_TIMER_WRITE) = t_name.MAX_TIMER_WRITE) AS MAX_WRITE, +# (MIN(t_inst.MIN_TIMER_WRITE) = t_name.MIN_TIMER_WRITE) AS MIN_WRITE, + (SUM(t_inst.SUM_NUMBER_OF_BYTES_WRITE) = t_name.SUM_NUMBER_OF_BYTES_WRITE) AS BYTES_WRITE, + (SUM(t_inst.COUNT_MISC) = t_name.COUNT_MISC) AS CNT_MISC, + (SUM(t_inst.SUM_TIMER_MISC) = t_name.SUM_TIMER_MISC) AS SUM_MISC +FROM performance_schema.socket_summary_by_instance t_inst +JOIN performance_schema.socket_summary_by_event_name t_name +USING (EVENT_NAME) +WHERE t_inst.event_name like '%client%' + AND t_inst.object_instance_begin <> @default_object_instance_begin; + +let $compare_tables_and_verify= +SELECT ( + (SUM(t_inst.COUNT_STAR) = t_name.COUNT_STAR) AND + (SUM(t_inst.SUM_TIMER_WAIT) = t_name.SUM_TIMER_WAIT) AND + (MAX(t_inst.MAX_TIMER_WAIT) = t_name.MAX_TIMER_WAIT) AND +# (MIN(t_inst.MIN_TIMER_WAIT) = t_name.MIN_TIMER_WAIT) AND + (SUM(t_inst.COUNT_READ) = t_name.COUNT_READ) AND + (SUM(t_inst.SUM_TIMER_READ) = t_name.SUM_TIMER_READ) AND + (MAX(t_inst.MAX_TIMER_READ) = t_name.MAX_TIMER_READ) AND +# (MIN(t_inst.MIN_TIMER_READ) = t_name.MIN_TIMER_READ) AND + (SUM(t_inst.SUM_NUMBER_OF_BYTES_READ) = t_name.SUM_NUMBER_OF_BYTES_READ) AND + (SUM(t_inst.COUNT_WRITE) = t_name.COUNT_WRITE) AND + (SUM(t_inst.SUM_TIMER_WRITE) = t_name.SUM_TIMER_WRITE) AND + (MAX(t_inst.MAX_TIMER_WRITE) = t_name.MAX_TIMER_WRITE) AND +# (MIN(t_inst.MIN_TIMER_WRITE) = t_name.MIN_TIMER_WRITE) AND + (SUM(t_inst.SUM_NUMBER_OF_BYTES_WRITE) = t_name.SUM_NUMBER_OF_BYTES_WRITE) AND + (SUM(t_inst.COUNT_MISC) = t_name.COUNT_MISC) AND + (SUM(t_inst.SUM_TIMER_MISC) = t_name.SUM_TIMER_MISC) ) = 1 INTO @tables_match +FROM performance_schema.socket_summary_by_instance t_inst +JOIN performance_schema.socket_summary_by_event_name t_name +USING (EVENT_NAME) +WHERE t_inst.event_name like '%client%' + AND t_inst.object_instance_begin <> @default_object_instance_begin; + +--echo #============================================================================== +--echo # 1.0 TEST INITIALIZATION +--echo #============================================================================== +--echo +--echo # 1.1 Disable instrumentation of the default (this) connection +--echo +--connection default +UPDATE performance_schema.threads + SET INSTRUMENTED='NO' WHERE PROCESSLIST_ID = CONNECTION_ID(); + +--echo +--echo # 1.2 Get the default THREAD_ID; +--echo +eval $get_thread_id; +let $default_thread_id= `SELECT @my_thread_id`; + +--echo +--echo # 1.3 Get the default OBJECT_INSTANCE_BEGIN +--echo +eval $get_object_instance_begin; +let $default_object_instance_begin= `SELECT @my_object_instance_begin`; + +--disable_query_log +SELECT @my_object_instance_begin INTO @default_object_instance_begin; +--enable_query_log + +if ($my_socket_debug) +{ + --echo # Default object instance begin = $default_object_instance_begin + --echo # Default thread id = $default_thread_id +} + +--echo +--echo # 1.4 Create a test database +--echo +CREATE SCHEMA mysqltest; + +--echo +--echo # 1.5 Create a table to store summary values from socket_summary_by_instance +--echo and socket_summary_by_event_name +--echo +CREATE TABLE mysqltest.my_socket_summary AS + SELECT * FROM performance_schema.socket_summary_by_instance + WHERE 1 = 0; + +--echo +--echo # 1.6 Drop object_instance_begin from my_socket_summary +--echo +ALTER TABLE mysqltest.my_socket_summary + DROP COLUMN OBJECT_INSTANCE_BEGIN; + +--echo +--echo # 1.7 Add an auto_inc column to my_socket_summary +--echo +ALTER TABLE mysqltest.my_socket_summary + ADD COLUMN (n INT AUTO_INCREMENT, PRIMARY KEY(n)); + +--echo +--echo # 1.8 Create test tables +--echo +CREATE TABLE mysqltest.t1 (n INT AUTO_INCREMENT, s1 VARCHAR(1024), PRIMARY KEY(n)); +CREATE TABLE mysqltest.t2 (n INT AUTO_INCREMENT, s1 VARCHAR(1024), PRIMARY KEY(n)); + +--echo #============================================================================== +--echo # 2.0 ESTABLISH CLIENT CONNECTIONS +--echo #============================================================================== +--echo +--echo # 2.1 Connection 1 (tcp/ip, 127.0.0.1 or ::1) +--echo +--disable_query_log +--connect (con1,$my_localhost,root,,test,,$MASTER_MYPORT) +--enable_query_log + +--echo +--echo # 2.2 Connection 2 (localhost or unix domain socket) +--echo +--disable_query_log +--connect (con2,localhost,root,,test,,$MASTER_MYPORT) +--enable_query_log + +--echo #============================================================================== +--echo # 3.0 RUN THE TESTS +--echo #============================================================================== +--echo +--echo # 3.1 Clear performance schema tables +--echo +TRUNCATE performance_schema.socket_summary_by_instance; +TRUNCATE performance_schema.socket_summary_by_event_name; + +--echo +--echo # 3.2 Get the 'before' sum of bytes written from socket_summary_by_instance +--echo for later comparison to the 'after' byte count as a simple confirmation +--echo that the table was updated. +--echo +--connection default +eval $get_write_count; +let $my_count_before= `SELECT @my_write_count`; + +--echo +--echo # 3.3 From connection 1, insert one a 1K row of data into t1 +--echo +--connection con1 +USE mysqltest; +INSERT INTO t1 (s1) VALUES (REPEAT('a', 1024)); +INSERT INTO t1 (s1) SELECT s1 FROM t1; + +--echo +--echo # 3.4 From connection 2, insert one a 1K row of data into t2 +--echo +--connection con2 +USE mysqltest; +INSERT INTO t2 (s1) VALUES (REPEAT('a', 1024)); +INSERT INTO t2 (s1) SELECT s1 FROM t2; + +--echo +--echo # 3.5 Get the 'after' sum of bytes written from socket_summary_by_instance +--echo +--connection default +eval $get_write_count; +let $my_count_after= `SELECT @my_write_count`; + +--echo +--echo # 3.6 Verify that SUM_NUMBER_OF_BYTES_WRITE increased +--echo + +if ($my_count_before >= $my_count_after) +{ + --echo + --echo # ERROR: Write count did not increaase + --echo # Before = $my_count_before + --echo # After = $my_count_after + --echo + SELECT * FROM performance_schema.socket_summary_by_instance ORDER BY EVENT_NAME; + --echo + --echo # Abort + exit; +} + +--echo +--echo # socket_summary_by_instance was updated +--echo + +--echo #============================================================================== +--echo # 4.0 VERIFY RESULTS +--echo #============================================================================== +--echo +--echo # 4.1 Verify that the totals in socket_summary_by_event_name are +--echo consistent with totals in socket_summary_by_instance +--echo + +--disable_query_log +eval $compare_tables_and_verify; + +if (`SELECT @tables_match = 0`) +{ + --echo + --echo # ERROR: Socket summary tables do not match + --echo # Column comparison results: + --echo + eval $compare_tables_and_print; + --enable_query_log + --echo + SELECT * from performance_schema.socket_summary_by_instance ORDER BY EVENT_NAME; + --echo + SELECT * from performance_schema.socket_summary_by_event_name ORDER BY EVENT_NAME; + --echo + --echo # Abort + exit; +} +--enable_query_log + +--echo #============================================================================== +--echo # 5.0 Clean up +--echo #============================================================================== + +--echo +--echo # 5.1 Disconnect con1 +--echo +--connection con1 +--disconnect con1 +--source include/wait_until_disconnected.inc + +--echo +--echo # 5.2 Disconnect con2 +--echo +--connection con2 +--disconnect con2 +--source include/wait_until_disconnected.inc + +--echo +--echo # 5.3 Drop mysqltest +--echo +--connection default +DROP DATABASE mysqltest; + +exit; + No bundle (reason: useless for push emails).