| Bug #75590 | SHOW VARIABLES WHERE ... materializes all variables into bigass temptable | ||
|---|---|---|---|
| Submitted: | 22 Jan 2015 22:39 | Modified: | 14 Nov 2018 17:40 |
| Reporter: | Domas Mituzas | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Locking | Severity: | S3 (Non-critical) |
| Version: | 5.6 | OS: | Any |
| Assigned to: | Marc ALFF | CPU Architecture: | Any |
[22 Jan 2015 22:39]
Domas Mituzas
[23 Jan 2015 8:32]
MySQL Verification Team
related: http://bugs.mysql.com/bug.php?id=70433
[23 Jan 2015 11:35]
MySQL Verification Team
as far as I can tell, LOCK_global_system_variables should be unlocked just before calling schema_table_store_record. Or do I miss something here? Still amusing that the lock is locked/unlocked for every single variable?
[23 Jan 2015 11:40]
MySQL Verification Team
setting as verified, there is room for improvement. At least I think it's not good to have this mutex flapping crazily if it's not requirement.
[2 Jul 2015 10:24]
Murthy Sidagam
Posted by developer: The query which is mentioned in the bug report(i.e show variables where variable_name='';) is not hitting the schema_table_store_record() function. Hence moving the bug status to '30'.
[20 Jul 2015 9:12]
Murthy Sidagam
Posted by developer: And also in the code it is clear that the global mutex is unlocked before calling schema_table_store_record().
[30 Jul 2015 9:06]
Marc ALFF
This bug is verified, no additional feedback needed.
[17 Sep 2015 9:24]
Murthy Sidagam
We tried to reproduce the problem using the query below provided by the connectors/J team. We saw the max allocation of 127KB. ********************************************* SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'license' OR Variable_name = 'init_connect'; ********************************************** Note that with Bug#75592, the SHOW VARIABLES query generated by connector/J has been simplified and is now much more efficient.
[18 Oct 2015 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[14 Nov 2018 17:42]
MySQL Verification Team
So what we have is one connection doing this SHOW VARIABLES: : ntdll!ZwWaitForSingleObject+0xa : ntdll!RtlpWaitOnCriticalSection+0xe8 : ntdll!RtlEnterCriticalSection+0xd1 : ntdll!RtlpAllocateHeap+0x18a6 : ntdll!RtlAllocateHeap+0x16c : mysqld!malloc+0x5b [f:\dd\vctools\crt_bld\self_64_amd64\crt\src\malloc.c @ 89] : mysqld!my_malloc+0x2f [f:\ade\build\sb_0-14808650-1427302793.22\mysqlcom-pro-5.6.24\mysys\my_malloc.c @ 38] : mysqld!hp_get_new_block+0x52 [f:\ade\build\sb_0-14808650-1427302793.22\mysqlcom-pro-5.6.24\storage\heap\hp_block.c @ 80] : mysqld!next_free_record_pos+0x87 [f:\ade\build\sb_0-14808650-1427302793.22\mysqlcom-pro-5.6.24\storage\heap\hp_write.c @ 157] : mysqld!heap_write+0x1f [f:\ade\build\sb_0-14808650-1427302793.22\mysqlcom-pro-5.6.24\storage\heap\hp_write.c @ 45] : mysqld!ha_heap::write_row+0x49 [f:\ade\build\sb_0-14808650-1427302793.22\mysqlcom-pro-5.6.24\storage\heap\ha_heap.cc @ 236] : mysqld!handler::ha_write_row+0xcb [f:\ade\build\sb_0-14808650-1427302793.22\mysqlcom-pro-5.6.24\sql\handler.cc @ 7274] : mysqld!schema_table_store_record+0x1d [f:\ade\build\sb_0-14808650-1427302793.22\mysqlcom-pro-5.6.24\sql\sql_show.cc @ 2796] : mysqld!show_status_array+0x4db [f:\ade\build\sb_0-14808650-1427302793.22\mysqlcom-pro-5.6.24\sql\sql_show.cc @ 2705] : mysqld!fill_variables+0x1b1 [f:\ade\build\sb_0-14808650-1427302793.22\mysqlcom-pro-5.6.24\sql\sql_show.cc @ 6644] : mysqld!do_fill_table+0xb9 [f:\ade\build\sb_0-14808650-1427302793.22\mysqlcom-pro-5.6.24\sql\sql_show.cc @ 7356] : mysqld!get_schema_tables_result+0x1c4 [f:\ade\build\sb_0-14808650-1427302793.22\mysqlcom-pro-5.6.24\sql\sql_show.cc @ 7457] : mysqld!JOIN::prepare_result+0x6e [f:\ade\build\sb_0-14808650-1427302793.22\mysqlcom-pro-5.6.24\sql\sql_select.cc @ 823] : mysqld!JOIN::exec+0x17d [f:\ade\build\sb_0-14808650-1427302793.22\mysqlcom-pro-5.6.24\sql\sql_executor.cc @ 116] : mysqld!mysql_execute_select+0x89 [f:\ade\build\sb_0-14808650-1427302793.22\mysqlcom-pro-5.6.24\sql\sql_select.cc @ 1103] : mysqld!handle_select+0x11b [f:\ade\build\sb_0-14808650-1427302793.22\mysqlcom-pro-5.6.24\sql\sql_select.cc @ 110] : mysqld!execute_sqlcom_select+0x181 [f:\ade\build\sb_0-14808650-1427302793.22\mysqlcom-pro-5.6.24\sql\sql_parse.cc @ 5139] while several hundred are waiting like this : : Call Site : ntdll!ZwWaitForSingleObject+0xa : ntdll!RtlpWaitOnCriticalSection+0xe8 : ntdll!RtlEnterCriticalSection+0xd1 : mysqld!fill_variables+0xd8 [f:\ade\build\sb_0-14808650-1427302793.22\mysqlcom-pro-5.6.24\sql\sql_show.cc @ 6636] : mysqld!do_fill_table+0xb9 [f:\ade\build\sb_0-14808650-1427302793.22\mysqlcom-pro-5.6.24\sql\sql_show.cc @ 7356] : mysqld!get_schema_tables_result+0x1c4 [f:\ade\build\sb_0-14808650-1427302793.22\mysqlcom-pro-5.6.24\sql\sql_show.cc @ 7457] : mysqld!JOIN::prepare_result+0x6e [f:\ade\build\sb_0-14808650-1427302793.22\mysqlcom-pro-5.6.24\sql\sql_select.cc @ 823] : mysqld!JOIN::exec+0x17d [f:\ade\build\sb_0-14808650-1427302793.22\mysqlcom-pro-5.6.24\sql\sql_executor.cc @ 116] : mysqld!mysql_execute_select+0x89 [f:\ade\build\sb_0-14808650-1427302793.22\mysqlcom-pro-5.6.24\sql\sql_select.cc @ 1103] : mysqld!handle_select+0x11b [f:\ade\build\sb_0-14808650-1427302793.22\mysqlcom-pro-5.6.24\sql\sql_select.cc @ 110] : mysqld!execute_sqlcom_select+0x181 [f:\ade\build\sb_0-14808650-1427302793.22\mysqlcom-pro-5.6.24\sql\sql_parse.cc @ 5139] : mysqld!mysql_execute_command+0x66f [f:\ade\build\sb_0-14808650-1427302793.22\mysqlcom-pro-5.6.24\sql\sql_parse.cc @ 2661] <cut>
[1 Dec 2018 6:12]
MySQL Verification Team
I've lodged a few more bugs related to this. https://bugs.mysql.com/bug.php?id=93429 (mem advisors using SHOW VARIABLES unnecessarily) https://bugs.mysql.com/bug.php?id=93202 (connector/net doesn't need to run SHOW VARIABLES) https://bugs.mysql.com/bug.php?id=93201 (reconsider the number of SHOW [SESSION] VARIABLES commands run by connector/C++)
