| Bug #105913 | Add table user_variables in performance_schema | ||
|---|---|---|---|
| Submitted: | 16 Dec 2021 4:28 | Modified: | 17 Dec 2021 8:45 |
| Reporter: | chenguang pei | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server: Performance Schema | Severity: | S4 (Feature request) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | user_variables;performance_schema; | ||
[16 Dec 2021 14:55]
MySQL Verification Team
Hi Mr. p, Thank you for your feature request report. We think that it makes sense to have a list of the defined and stored user variable, out of context of the stored routines or triggers. We just do not think that P_S is the correct place for it. Anyway, we are verifying your report as a valid feature request and we shall leave it to our colleagues on how and when to implement it. Since it is a feature request, it can go only into 8.0 version. Regarding the implementation and other info, this report will be updated once that those decisions are made. We do not know when will it happen. Verified.
[17 Dec 2021 8:45]
Marc ALFF
Thanks for the feature request. Instead of writing: "select variable_name, variable_value from performance_schema.user_variables_by_thread where thread_id=(select thread_id from performance_schema.threads where processlist_id=connection_id())". a more efficient query is: select * from user_variables_by_thread where THREAD_ID = ps_current_thread_id(); which makes use of the ps_current_thread_id() native function. This avoids a full scan on table threads, and a join. Also, given the primary key PRIMARY KEY (`THREAD_ID`,`VARIABLE_NAME`) in table performance_schema.user_variables_by_thread, the performance of this query should be acceptable. There is no compelling reason to create another performance schema table just for this.
Description: User_variables_by_thread in perforcemance_schema show all thread_ids' user variables. if we want to get user variables in local connection, maybe we should use a sql like : "select variable_name, variable_value from performance_schema.user_variables_by_thread where thread_id=(select thread_id from performance_schema.threads where processlist_id=connection_id())". Performance of sql above is not perfect.It's better to supply other way to show user variables in local thread, for example: create another table named user_variables in performace_schema which show user variables in local thread. I have a test: prepare: 100 connections(Concurrency) ，set 100 user variables each connection test: method 1: select all user variables use User_variables_by_thread 100000 times method 2: select all user variables use User_variables 10000 times results: method 1: total time is 399s method 2: total time is 7.6s How to repeat: test case: ======================================= #include <stdio.h> #include <string.h> #include <stdlib.h> #include <mysql.h> #include <pthread.h> #include <unistd.h> #include <sys/time.h> #define DBHOSTm "127.0.0.1" #define DBUSERm "test" #define DBPASSm "test" #define DBPORTm 8901 #define DBNAMEm "test" #define DBSOCKm NULL #define DBPCNTm 0 struct timeval select_end; struct timeval select_start; void *set_func(MYSQL *mysql, int pk) { MYSQL_RES *result; MYSQL_ROW row; MYSQL_FIELD *field; unsigned int num_fields; char pStatement[100]; sprintf(pStatement,"set @%d= %d",pk); if (0 != mysql_real_query(mysql, pStatement, strlen(pStatement))) { printf("query failed: %s\n", mysql_error(mysql)); mysql_close(mysql); mysql_thread_end(); return (void *)0; } return (void *)0; } void *select_func(MYSQL *mysql,int pk) { MYSQL_RES *result; MYSQL_ROW row; MYSQL_FIELD *field; unsigned int num_fields; char pStatement[500]; sprintf(pStatement,"select VARIABLE_NAME, VARIABLE_VALUE from performance_schema.user_variables_by_thread where thread_id=( select thread_id from performance_schema.threads where processlist_id = connection_id());"); //sprintf(pStatement,"select VARIABLE_NAME, VARIABLE_VALUE from performance_schema.user_variables"); int try_cnt=0; gettimeofday(&select_start, NULL); while(try_cnt <10000) { if (0 != mysql_real_query(mysql, pStatement, strlen(pStatement))) { printf("query failed: %s\n", mysql_error(mysql)); mysql_close(mysql); mysql_thread_end(); return (void *)0; } result = mysql_store_result(mysql); try_cnt ++; mysql_free_result(result); } gettimeofday(&select_end, NULL); float inval = (select_end.tv_sec - select_start.tv_sec)*1000 + (select_end.tv_usec-select_start.tv_usec)/1000; printf("%.10f \n", inval); return (void *)0; } int main(int argc, char *argv[]) { int loop_num = 100; mysql_thread_init(); MYSQL *mysql_m = mysql_init(NULL); if (mysql_m == NULL) { printf("mysql init failed: %s\n", mysql_error(mysql_m)); return 0; } if (mysql_real_connect(mysql_m,DBHOSTm, DBUSERm, DBPASSm, DBNAMEm, DBPORTm, DBSOCKm, DBPCNTm) == NULL) { printf("connect failed: %s\n", mysql_error(mysql_m)); mysql_close(mysql_m); mysql_thread_end(); return 0; } // prepare int loop,pk; for (pk = 0;pk<=loop_num;pk++) { set_func(mysql_m,pk); } // do select select_func(mysql_m,pk); mysql_close(mysql_m); mysql_thread_end(); return 0; } Suggested fix: add a table like User_variables just show user variables in local connection. add table_uvar_self_thread.cc and table_uvar_self_thread.h in storage/perfschema . Code i will Submit in another way. thank you for your time.