mirror of
https://github.com/MariaDB/server.git
synced 2025-09-23 17:39:15 +02:00
ca168a7a9a
Keep concurrent_connections updated when user stats are enabled. When a user connects, the count in concurrent_connections increases by one; when they disconnect, it decreases by one. Setting the global userstat begins counting new concurrent sessions from that point forward; pre-existing concurrent sessions are not counted until they are restarted. Ideally this configuration is set in the my.cnf file so as to apply from server startup, ensuring that the counter is consistent. If it is set during the server's lifetime (via SET GLOBAL userstat=1) it is recommended to do so before concurrent user sessions exist for accurate counts.
257 lines
8.2 KiB
Text
257 lines
8.2 KiB
Text
select variable_value from information_schema.global_status where variable_name="handler_read_key" into @global_read_key;
|
|
Warnings:
|
|
Warning 1287 '<select expression> INTO <destination>;' is deprecated and will be removed in a future release. Please use 'SELECT <select list> INTO <destination> FROM...' instead
|
|
show columns from information_schema.client_statistics;
|
|
Field Type Null Key Default Extra
|
|
CLIENT varchar(64) NO NULL
|
|
TOTAL_CONNECTIONS bigint(21) NO NULL
|
|
CONCURRENT_CONNECTIONS bigint(21) NO NULL
|
|
CONNECTED_TIME bigint(21) NO NULL
|
|
BUSY_TIME double NO NULL
|
|
CPU_TIME double NO NULL
|
|
BYTES_RECEIVED bigint(21) NO NULL
|
|
BYTES_SENT bigint(21) NO NULL
|
|
BINLOG_BYTES_WRITTEN bigint(21) NO NULL
|
|
ROWS_READ bigint(21) NO NULL
|
|
ROWS_SENT bigint(21) NO NULL
|
|
ROWS_DELETED bigint(21) NO NULL
|
|
ROWS_INSERTED bigint(21) NO NULL
|
|
ROWS_UPDATED bigint(21) NO NULL
|
|
SELECT_COMMANDS bigint(21) NO NULL
|
|
UPDATE_COMMANDS bigint(21) NO NULL
|
|
OTHER_COMMANDS bigint(21) NO NULL
|
|
COMMIT_TRANSACTIONS bigint(21) NO NULL
|
|
ROLLBACK_TRANSACTIONS bigint(21) NO NULL
|
|
DENIED_CONNECTIONS bigint(21) NO NULL
|
|
LOST_CONNECTIONS bigint(21) NO NULL
|
|
ACCESS_DENIED bigint(21) NO NULL
|
|
EMPTY_QUERIES bigint(21) NO NULL
|
|
TOTAL_SSL_CONNECTIONS bigint(21) unsigned NO NULL
|
|
MAX_STATEMENT_TIME_EXCEEDED bigint(21) NO NULL
|
|
show columns from information_schema.user_statistics;
|
|
Field Type Null Key Default Extra
|
|
USER varchar(128) NO NULL
|
|
TOTAL_CONNECTIONS int(11) NO NULL
|
|
CONCURRENT_CONNECTIONS int(11) NO NULL
|
|
CONNECTED_TIME int(11) NO NULL
|
|
BUSY_TIME double NO NULL
|
|
CPU_TIME double NO NULL
|
|
BYTES_RECEIVED bigint(21) NO NULL
|
|
BYTES_SENT bigint(21) NO NULL
|
|
BINLOG_BYTES_WRITTEN bigint(21) NO NULL
|
|
ROWS_READ bigint(21) NO NULL
|
|
ROWS_SENT bigint(21) NO NULL
|
|
ROWS_DELETED bigint(21) NO NULL
|
|
ROWS_INSERTED bigint(21) NO NULL
|
|
ROWS_UPDATED bigint(21) NO NULL
|
|
SELECT_COMMANDS bigint(21) NO NULL
|
|
UPDATE_COMMANDS bigint(21) NO NULL
|
|
OTHER_COMMANDS bigint(21) NO NULL
|
|
COMMIT_TRANSACTIONS bigint(21) NO NULL
|
|
ROLLBACK_TRANSACTIONS bigint(21) NO NULL
|
|
DENIED_CONNECTIONS bigint(21) NO NULL
|
|
LOST_CONNECTIONS bigint(21) NO NULL
|
|
ACCESS_DENIED bigint(21) NO NULL
|
|
EMPTY_QUERIES bigint(21) NO NULL
|
|
TOTAL_SSL_CONNECTIONS bigint(21) unsigned NO NULL
|
|
MAX_STATEMENT_TIME_EXCEEDED bigint(21) NO NULL
|
|
show columns from information_schema.index_statistics;
|
|
Field Type Null Key Default Extra
|
|
TABLE_SCHEMA varchar(192) NO NULL
|
|
TABLE_NAME varchar(192) NO NULL
|
|
INDEX_NAME varchar(192) NO NULL
|
|
ROWS_READ bigint(21) NO NULL
|
|
show columns from information_schema.table_statistics;
|
|
Field Type Null Key Default Extra
|
|
TABLE_SCHEMA varchar(192) NO NULL
|
|
TABLE_NAME varchar(192) NO NULL
|
|
ROWS_READ bigint(21) NO NULL
|
|
ROWS_CHANGED bigint(21) NO NULL
|
|
ROWS_CHANGED_X_INDEXES bigint(21) NO NULL
|
|
set @save_general_log=@@global.general_log;
|
|
set @@global.general_log=0;
|
|
set @@global.userstat=1;
|
|
flush status;
|
|
create table t1 (a int, primary key (a), b int default 0) engine=innodb;
|
|
insert into t1 (a) values (1),(2),(3),(4);
|
|
update t1 set b=1;
|
|
update t1 set b=5 where a=2;
|
|
delete from t1 where a=3;
|
|
/* Empty query */
|
|
select * from t1 where a=999;
|
|
a b
|
|
drop table t1;
|
|
connect ssl_con,localhost,root,,,,,SSL;
|
|
SELECT (VARIABLE_VALUE <> '') AS have_ssl FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='Ssl_cipher';
|
|
have_ssl
|
|
1
|
|
connection default;
|
|
create table t1 (a int, primary key (a), b int default 0) engine=innodb;
|
|
begin;
|
|
insert into t1 values(1,1);
|
|
commit;
|
|
begin;
|
|
insert into t1 values(2,2);
|
|
commit;
|
|
begin;
|
|
insert into t1 values(3,3);
|
|
rollback;
|
|
drop table t1;
|
|
select sleep(1);
|
|
sleep(1)
|
|
0
|
|
show status like "rows%";
|
|
Variable_name Value
|
|
Rows_read 6
|
|
Rows_sent 1
|
|
Rows_tmp_read 0
|
|
show status like "ha%";
|
|
Variable_name Value
|
|
Handler_commit 19
|
|
Handler_delete 1
|
|
Handler_discover 0
|
|
Handler_external_lock 0
|
|
Handler_icp_attempts 0
|
|
Handler_icp_match 0
|
|
Handler_mrr_init 0
|
|
Handler_mrr_key_refills 0
|
|
Handler_mrr_rowid_refills 0
|
|
Handler_prepare 18
|
|
Handler_read_first 0
|
|
Handler_read_key 17
|
|
Handler_read_last 0
|
|
Handler_read_next 0
|
|
Handler_read_prev 0
|
|
Handler_read_retry 0
|
|
Handler_read_rnd 0
|
|
Handler_read_rnd_deleted 0
|
|
Handler_read_rnd_next 5
|
|
Handler_rollback 2
|
|
Handler_savepoint 0
|
|
Handler_savepoint_rollback 0
|
|
Handler_tmp_delete 0
|
|
Handler_tmp_update 0
|
|
Handler_tmp_write 0
|
|
Handler_update 5
|
|
Handler_write 7
|
|
select variable_value - @global_read_key as "handler_read_key" from information_schema.global_status where variable_name="handler_read_key";
|
|
handler_read_key
|
|
17
|
|
disconnect ssl_con;
|
|
set @@global.userstat=0;
|
|
select * from information_schema.index_statistics;
|
|
TABLE_SCHEMA TABLE_NAME INDEX_NAME ROWS_READ
|
|
select * from information_schema.table_statistics;
|
|
TABLE_SCHEMA TABLE_NAME ROWS_READ ROWS_CHANGED ROWS_CHANGED_X_INDEXES
|
|
show table_statistics;
|
|
Table_schema Table_name Rows_read Rows_changed Rows_changed_x_#indexes
|
|
show index_statistics;
|
|
Table_schema Table_name Index_name Rows_read
|
|
select TOTAL_CONNECTIONS, TOTAL_SSL_CONNECTIONS, CONCURRENT_CONNECTIONS, ROWS_READ, ROWS_SENT, ROWS_DELETED, ROWS_INSERTED, ROWS_UPDATED, SELECT_COMMANDS, UPDATE_COMMANDS, COMMIT_TRANSACTIONS, ROLLBACK_TRANSACTIONS, DENIED_CONNECTIONS, LOST_CONNECTIONS, ACCESS_DENIED, EMPTY_QUERIES from information_schema.client_statistics;;
|
|
TOTAL_CONNECTIONS 2
|
|
TOTAL_SSL_CONNECTIONS 1
|
|
CONCURRENT_CONNECTIONS 1
|
|
ROWS_READ 6
|
|
ROWS_SENT 3
|
|
ROWS_DELETED 1
|
|
ROWS_INSERTED 7
|
|
ROWS_UPDATED 5
|
|
SELECT_COMMANDS 4
|
|
UPDATE_COMMANDS 11
|
|
COMMIT_TRANSACTIONS 19
|
|
ROLLBACK_TRANSACTIONS 2
|
|
DENIED_CONNECTIONS 0
|
|
LOST_CONNECTIONS 0
|
|
ACCESS_DENIED 0
|
|
EMPTY_QUERIES 1
|
|
select TOTAL_CONNECTIONS, TOTAL_SSL_CONNECTIONS, CONCURRENT_CONNECTIONS, ROWS_READ, ROWS_SENT, ROWS_DELETED, ROWS_INSERTED, ROWS_UPDATED, SELECT_COMMANDS, UPDATE_COMMANDS, COMMIT_TRANSACTIONS, ROLLBACK_TRANSACTIONS, DENIED_CONNECTIONS, LOST_CONNECTIONS, ACCESS_DENIED, EMPTY_QUERIES from information_schema.user_statistics;;
|
|
TOTAL_CONNECTIONS 2
|
|
TOTAL_SSL_CONNECTIONS 1
|
|
CONCURRENT_CONNECTIONS 1
|
|
ROWS_READ 6
|
|
ROWS_SENT 3
|
|
ROWS_DELETED 1
|
|
ROWS_INSERTED 7
|
|
ROWS_UPDATED 5
|
|
SELECT_COMMANDS 4
|
|
UPDATE_COMMANDS 11
|
|
COMMIT_TRANSACTIONS 19
|
|
ROLLBACK_TRANSACTIONS 2
|
|
DENIED_CONNECTIONS 0
|
|
LOST_CONNECTIONS 0
|
|
ACCESS_DENIED 0
|
|
EMPTY_QUERIES 1
|
|
select OTHER_COMMANDS IN (7,8) from information_schema.client_statistics;
|
|
OTHER_COMMANDS IN (7,8)
|
|
1
|
|
select OTHER_COMMANDS IN (7,8) from information_schema.user_statistics;
|
|
OTHER_COMMANDS IN (7,8)
|
|
1
|
|
flush table_statistics;
|
|
flush index_statistics;
|
|
select * from information_schema.index_statistics;
|
|
TABLE_SCHEMA TABLE_NAME INDEX_NAME ROWS_READ
|
|
select * from information_schema.table_statistics;
|
|
TABLE_SCHEMA TABLE_NAME ROWS_READ ROWS_CHANGED ROWS_CHANGED_X_INDEXES
|
|
show status like "%generic%";
|
|
Variable_name Value
|
|
Com_show_generic 2
|
|
select connected_time <> 0, busy_time <> 0, bytes_received <> 0,
|
|
bytes_sent <> 0, binlog_bytes_written <> 0
|
|
from information_schema.user_statistics;
|
|
connected_time <> 0 busy_time <> 0 bytes_received <> 0 bytes_sent <> 0 binlog_bytes_written <> 0
|
|
1 1 1 1 1
|
|
select connected_time <> 0, busy_time <> 0, bytes_received <> 0,
|
|
bytes_sent <> 0, binlog_bytes_written <> 0
|
|
from information_schema.client_statistics;
|
|
connected_time <> 0 busy_time <> 0 bytes_received <> 0 bytes_sent <> 0 binlog_bytes_written <> 0
|
|
1 1 1 1 1
|
|
create table t1 (a int) engine=innodb;
|
|
select @@in_transaction;
|
|
@@in_transaction
|
|
0
|
|
begin;
|
|
select @@in_transaction;
|
|
@@in_transaction
|
|
1
|
|
insert into t1 values (1);
|
|
select @@in_transaction;
|
|
@@in_transaction
|
|
1
|
|
commit;
|
|
select @@in_transaction;
|
|
@@in_transaction
|
|
0
|
|
set @@autocommit=0;
|
|
select @@in_transaction;
|
|
@@in_transaction
|
|
0
|
|
insert into t1 values (2);
|
|
select @@in_transaction;
|
|
@@in_transaction
|
|
1
|
|
set @@autocommit=1;
|
|
select @@in_transaction;
|
|
@@in_transaction
|
|
0
|
|
drop table t1;
|
|
set @@global.general_log=@save_general_log;
|
|
#
|
|
# MDEV-25242 Server crashes in check_grant upon invoking function with userstat enabled
|
|
#
|
|
create function f() returns int return (select 1 from performance_schema.threads);
|
|
set global userstat= 1;
|
|
select f() from information_schema.table_statistics;
|
|
ERROR 21000: Subquery returns more than 1 row
|
|
select f() from information_schema.index_statistics;
|
|
ERROR 21000: Subquery returns more than 1 row
|
|
set global userstat= 0;
|
|
drop function f;
|
|
# End of 10.2 tests
|
|
#
|
|
# MDEV-36586 USER_STATISTICS.BUSY_TIME is in microseconds
|
|
#
|
|
select distinct busy_time>1e5, cpu_time>1e5 from information_schema.user_statistics;
|
|
busy_time>1e5 cpu_time>1e5
|
|
0 0
|
|
# End of 10.11 tests
|