Closed rubensbarreto closed 3 years ago
schmunk42
commented
5 years ago Did you try to activate Yii's schema caching? https://www.yiiframework.com/doc/guide/2.0/en/tutorial-performance-tuning#enable-schema-caching
I don't think this is a gii specific issue, rather yii2-framework.
rubensbarreto
commented
5 years ago Gii doesn't expect schema caching to be turned on when using development machines, schema caching should be and is enabled on our production server.
This isn't an issue related to schema caching, since it's just the same query when running on different versions of mysql
machour
commented
5 years ago The time reported by the panel is the actual time the query took, and seeing that the queries are unchanged between versions, I'd suspect a problem with your MySQL setup, not with Yii.
Can you run the query in mysql cli and see if it takes the same time?
rubensbarreto
commented
5 years ago Hi Machour, I have the very same issue when using cli. We noticed this issue and downgraded several months ago, I’ve created a brand new mysql installation to check if the issues still persists and created this issue with additional data.
I’ve also tried setting innodb_stats_on_metadata=0 but it had no impact on performance.
The only flags configured on the new mysql installation are sql_mode ser to STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION, but even removing these flags also don’t make any impact.
I’ll add the Explain mysql 8 outputs later.
samdark
commented
5 years ago It could be that in that version of MySQL default settings were changed.
machour
commented
5 years ago @rubensbarreto I happen to be on macOs and using mysql 8.0.16 too.
Just tried on my side, and the average query time is 33ms which is indeed a very slow time. The whole page takes more than a second to generate.
It would be really helpful if you could show us EXPLAIN results for both versions, so we can see what is MySQL doing differently that costs that much.
schmunk42
commented
5 years ago Gii doesn't expect schema caching to be turned on when using development machines, schema caching should be and is enabled on our production server.
Sure, my answer was meant as a (CLI) workaround to reduce the processing time, how many of those slow statements do you have in total?
This isn't an issue related to schema caching, since it's just the same query when running on different versions of mysql
But the slow queries are from AR to retrieve the schema, right?
schmunk42
commented
5 years ago It could be that in that version of MySQL default settings were changed.
@rubensbarreto Can you create a diff of MySQL Variables for both versions.
schmunk42
commented
5 years ago @rubensbarreto Is your MySQL 5 and 8 server running on the same machine and disks?
rubensbarreto
commented
5 years ago Problem is that Mysql 8 has completely changed the output of the Explain result, and I believed they have also changed how the information_schema stores the constraints information, but here is the Explain for MySQL 8:
And the explain for MySQL 5.7:
They were running on different machines (the one running mysql 5 is actually a lower end machine, but both are 2018 macbook pros running apple's SSD), but they were both the same version of yii, mac os and php. The macbook pro running MySQL 8:
The macbook pro running MySQL 5.7:
I get the same behaviour on our Ubuntu Test Server.
Here's the diff for show variables between versions:
This actually worries me since Yii runs this query every time we use an ActiveRecord Model, so this overhead is probably also happening system wide, it's less noticeable on production environment since the connection uses the Schema Cache, but whenever the Cache Expires, the system will feel the extra latency. We've upgraded our Test Server to Mysql 8, but we had to rollback also due to complaints about much slower performance.
schmunk42
commented
5 years ago Two shots in the dark:
It should actually be faster according to: https://mysqlserverteam.com/mysql-8-0-scaling-and-performance-of-information_schema/ and http://mysqlserverteam.com/mysql-8-0-improvements-to-information_schema/
rubensbarreto
commented
5 years ago
- https://www.percona.com/blog/2011/12/23/solving-information_schema-slowness/ (@samdark commented here in 2011 😄)
Yeah that's the "innodb_stats_on_metadata=0" change I mentioned earlier, didn't make any difference. I did set it at execution time, since the MySQL documentation says it's a variable that responds to execution time change.
And I've looked at the debug panel when using our system, and I can see the constraints query is actually making the system slower due to the number of times it queries and how this overhead carries over.
rubensbarreto
commented
5 years ago Okay I believe I have figured it out.
After breaking down the query I noticed the part that takes the longest is the information_schema.key_column_usage join.
Actually just running a select on that table already takes 74 msecs (select * from information_schema.key_column_usage where constraint_schema='ins-gruposanta' and table_name='sdn_aux_bool';) , while running the same query on 5.7 takes less then 1 milisecond, exactly 0.26 msecs
After looking at the MySQL 8.0 documentation, specifically on the Optimizing INFORMATION_SCHEMA section (https://dev.mysql.com/doc/refman/8.0/en/information-schema-optimization.html), it says that certain tables are now implemented as VIEWS on information_schema:
These INFORMATION_SCHEMA tables are implemented as views on data dictionary tables, so queries on them retrieve information from the data dictionary:
CHARACTER_SETS CHECK_CONSTRAINTS COLLATIONS COLLATION_CHARACTER_SET_APPLICABILITY COLUMNS EVENTS FILES INNODB_COLUMNS INNODB_DATAFILES INNODB_FIELDS INNODB_FOREIGN INNODB_FOREIGN_COLS INNODB_INDEXES INNODB_TABLES INNODB_TABLESPACES INNODB_TABLESPACES_BRIEF INNODB_TABLESTATS KEY_COLUMN_USAGE PARAMETERS PARTITIONS REFERENTIAL_CONSTRAINTS RESOURCE_GROUPS ROUTINES SCHEMATA STATISTICS TABLES TABLE_CONSTRAINTS TRIGGERS VIEWS VIEW_ROUTINE_USAGE VIEW_TABLE_USAGE
Some types of values, even for a non-view INFORMATION_SCHEMA table, are retrieved by lookups from the data dictionary. This includes values such as database and table names, table types, and storage engines
That explains why querying this table now takes longer, since it's actually querying a View on mysql.
rubensbarreto
commented
5 years ago Running show create table information_schema.key_column_usage returns:
CREATE
ALGORITHM = UNDEFINED
DEFINER = mysql.infoschema@localhost
SQL SECURITY DEFINER
VIEW information_schema.KEY_COLUMN_USAGE AS
(SELECT
(cat.name COLLATE utf8_tolower_ci) AS CONSTRAINT_CATALOG,
(sch.name COLLATE utf8_tolower_ci) AS CONSTRAINT_SCHEMA,
idx.name AS CONSTRAINT_NAME,
(cat.name COLLATE utf8_tolower_ci) AS TABLE_CATALOG,
(sch.name COLLATE utf8_tolower_ci) AS TABLE_SCHEMA,
(tbl.name COLLATE utf8_tolower_ci) AS TABLE_NAME,
(col.name COLLATE utf8_tolower_ci) AS COLUMN_NAME,
icu.ordinal_position AS ORDINAL_POSITION,
NULL AS POSITION_IN_UNIQUE_CONSTRAINT,
NULL AS REFERENCED_TABLE_SCHEMA,
NULL AS REFERENCED_TABLE_NAME,
NULL AS REFERENCED_COLUMN_NAME
FROM
(((((mysql.indexes idx
JOIN mysql.tables tbl ON ((idx.table_id = tbl.id)))
JOIN mysql.schemata sch ON ((tbl.schema_id = sch.id)))
JOIN mysql.catalogs cat ON ((cat.id = sch.catalog_id)))
JOIN mysql.index_column_usage icu ON ((icu.index_id = idx.id)))
JOIN mysql.columns col ON (((icu.column_id = col.id)
AND (idx.type IN ('PRIMARY' , 'UNIQUE')))))
WHERE
(CAN_ACCESS_COLUMN(sch.name, tbl.name, col.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden,
((col.hidden <> 'Visible')
OR idx.hidden
OR icu.hidden)))) UNION (SELECT
(cat.name COLLATE utf8_tolower_ci) AS CONSTRAINT_CATALOG,
(sch.name COLLATE utf8_tolower_ci) AS CONSTRAINT_SCHEMA,
(fk.name COLLATE utf8_tolower_ci) AS CONSTRAINT_NAME,
(cat.name COLLATE utf8_tolower_ci) AS TABLE_CATALOG,
(sch.name COLLATE utf8_tolower_ci) AS TABLE_SCHEMA,
(tbl.name COLLATE utf8_tolower_ci) AS TABLE_NAME,
(col.name COLLATE utf8_tolower_ci) AS COLUMN_NAME,
fkcu.ordinal_position AS ORDINAL_POSITION,
fkcu.ordinal_position AS POSITION_IN_UNIQUE_CONSTRAINT,
fk.referenced_table_schema AS REFERENCED_TABLE_SCHEMA,
fk.referenced_table_name AS REFERENCED_TABLE_NAME,
fkcu.referenced_column_name AS REFERENCED_COLUMN_NAME
FROM
(((((mysql.foreign_keys fk
JOIN mysql.tables tbl ON ((fk.table_id = tbl.id)))
JOIN mysql.foreign_key_column_usage fkcu ON ((fkcu.foreign_key_id = fk.id)))
JOIN mysql.schemata sch ON ((fk.schema_id = sch.id)))
JOIN mysql.catalogs cat ON ((cat.id = sch.catalog_id)))
JOIN mysql.columns col ON ((fkcu.column_id = col.id)))
WHERE
(CAN_ACCESS_COLUMN(sch.name, tbl.name, col.name)
AND IS_VISIBLE_DD_OBJECT(tbl.hidden,
(col.hidden <> 'Visible'))))
While looking at the definition on Mysql 5.7, its shows its an In-Memory temporary table:
CREATE TEMPORARY TABLE KEY_COLUMN_USAGE (
CONSTRAINT_CATALOG varchar(512) NOT NULL DEFAULT '',
CONSTRAINT_SCHEMA varchar(64) NOT NULL DEFAULT '',
CONSTRAINT_NAME varchar(64) NOT NULL DEFAULT '',
TABLE_CATALOG varchar(512) NOT NULL DEFAULT '',
TABLE_SCHEMA varchar(64) NOT NULL DEFAULT '',
TABLE_NAME varchar(64) NOT NULL DEFAULT '',
COLUMN_NAME varchar(64) NOT NULL DEFAULT '',
ORDINAL_POSITION bigint(10) NOT NULL DEFAULT '0',
POSITION_IN_UNIQUE_CONSTRAINT bigint(10) DEFAULT NULL,
REFERENCED_TABLE_SCHEMA varchar(64) DEFAULT NULL,
REFERENCED_TABLE_NAME varchar(64) DEFAULT NULL,
REFERENCED_COLUMN_NAME varchar(64) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8
rubensbarreto
commented
5 years ago One option would be to query information_schema.innodb_foreign_cols instead of foreign_key_column_usage, but that would impact any MYISAM foreign key.
If we join with innodb_foreign_cols, the execution time falls to half... but it's still quite slower then in mysql 5.7:
SELECT
rc.constraint_name,
kcu. FOR_COL_NAME as column_name,
rc.referenced_table_name,
kcu.REF_COL_NAME as referenced_column_name
FROM information_schema.referential_constraints AS rc
JOIN information_schema.INNODB_FOREIGN_COLS AS kcu ON
kcu.ID=CONCAT(rc.constraint_schema,'/',rc.constraint_name)
WHERE rc.constraint_schema = 'ins-gruposanta'
AND rc.table_name = 'sdn_aux_bool' ;
rubensbarreto
commented
5 years ago There's also one part using gii that I do not understand, why, when choosing an specific table to preview a model, does gii queries information_schema.key_column_usage for all tables in my database?
It runs 6000 queries just to preview the model for one database table, changing this logic would solve most of the problem.
samdark
commented
5 years ago There's also one part using gii that I do not understand, why, when choosing an specific table to preview a model, does gii queries information_schema.key_column_usage for all tables in my database?
Relations, I guess.
samdark
commented
5 years ago We can probably detect engine used and make specific code branch for InnoDB.
rubensbarreto
commented
5 years ago There's also one part using gii that I do not understand, why, when choosing an specific table to preview a model, does gii queries information_schema.key_column_usage for all tables in my database?
Relations, I guess.
I figured it was trying to find the inverse relations, other tables that have foreign key pointing to the table that's being previewed, still, we can get the relations by querying and filtering only the table that's being previewed, there's no need to loop through all the tables in the database. In this way, the bigger the number of tables in the database, the longer it will take.
New projects won't even feel this, but existing ones with a lot of tables will definitely feel.... as longs as they're using MySQL 8.
Since they rewrote all of the information_schema to use the new data dictionary, I don't see this behaviour changing any time soon. Querying information_schema is indeed faster for some types of queries, but unfortunately that is not the case for information_schema.key_column_usage who used to be an in memory table. =\
schmunk42
commented
5 years ago @rubensbarreto How many tables do you have in your project? How much additional time do you need on MySQL 8?
rubensbarreto
commented
5 years ago @rubensbarreto How many tables do you have in your project? How much additional time do you need on MySQL 8?
Right now 965, but the system is still growing.
We've built a HIS using Yii2 and MySQL, so we're still on active development.
rubensbarreto
commented
3 years ago This issue can now be closed, since the team at Mysql have fixed the slow performance on versions 8.0.21 and beyond, i have tested on yii 2.0.40 with mysql 8.0.20 and the issue has ben solved.
This was fixed on: https://bugs.mysql.com/bug.php?id=98238
What steps will reproduce the problem?
Using gii with mysql 8.0.16, I've traced that the constraints query is taking a much bigger time to run when compared to mysql 5.7.
What's expected?
When Gii tries to generate the preview of a model, it runs about 6000 commands, checking constraints and create ddl for several tables, on mysql 5.7, this specific part takes 0.4ms:
SELECT kcu.constraint_name, kcu.column_name, kcu.referenced_table_name, kcu.referenced_column_name FROM information_schema.referential_constraints AS rc JOIN information_schema.key_column_usage AS kcu ON ( kcu.constraint_catalog = rc.constraint_catalog OR (kcu.constraint_catalog IS NULL AND rc.constraint_catalog IS NULL) ) AND kcu.constraint_schema = rc.constraint_schema AND kcu.constraint_name = rc.constraint_name WHERE rc.constraint_schema = database() AND kcu.table_schema = database() AND rc.table_name = 'sdn_aux_bool' AND kcu.table_name = 'sdn_aux_bool';
What do you get instead?
On MySQL 8.0.16 it takes 87.5MS
Additional info
| Yii version 2.0.19 | | PHP version 7.3.5 | | Database version MySQL 8.0.16 | | Operating system Mac OS X 10.14.5 |
MySQL Variables:
'activate_all_roles_on_login','OFF' 'admin_address','' 'admin_port','33062' 'auto_generate_certs','ON' 'auto_increment_increment','1' 'auto_increment_offset','1' 'autocommit','ON' 'automatic_sp_privileges','ON' 'avoid_temporal_upgrade','OFF' 'back_log','151' 'basedir','/usr/local/mysql-8.0.16-macos10.14-x86_64/' 'big_tables','OFF' 'bind_address','' 'binlog_cache_size','32768' 'binlog_checksum','CRC32' 'binlog_direct_non_transactional_updates','OFF' 'binlog_encryption','OFF' 'binlog_error_action','ABORT_SERVER' 'binlog_expire_logs_seconds','2592000' 'binlog_format','ROW' 'binlog_group_commit_sync_delay','0' 'binlog_group_commit_sync_no_delay_count','0' 'binlog_gtid_simple_recovery','ON' 'binlog_max_flush_queue_time','0' 'binlog_order_commits','ON' 'binlog_rotate_encryption_master_key_at_startup','OFF' 'binlog_row_event_max_size','8192' 'binlog_row_image','FULL' 'binlog_row_metadata','MINIMAL' 'binlog_row_value_options','' 'binlog_rows_query_log_events','OFF' 'binlog_stmt_cache_size','32768' 'binlog_transaction_dependency_history_size','25000' 'binlog_transaction_dependency_tracking','COMMIT_ORDER' 'block_encryption_mode','aes-128-ecb' 'bulk_insert_buffer_size','8388608' 'caching_sha2_password_auto_generate_rsa_keys','ON' 'caching_sha2_password_private_key_path','private_key.pem' 'caching_sha2_password_public_key_path','public_key.pem' 'character_set_client','utf8mb4' 'character_set_connection','utf8mb4' 'character_set_database','utf8mb4' 'character_set_filesystem','binary' 'character_set_results','utf8mb4' 'character_set_server','utf8mb4' 'character_set_system','utf8' 'character_sets_dir','/usr/local/mysql-8.0.16-macos10.14-x86_64/share/charsets/' 'check_proxy_users','OFF' 'collation_connection','utf8mb4_0900_ai_ci' 'collation_database','utf8mb4_0900_ai_ci' 'collation_server','utf8mb4_0900_ai_ci' 'completion_type','NO_CHAIN' 'concurrent_insert','AUTO' 'connect_timeout','10' 'core_file','OFF' 'create_admin_listener_thread','OFF' 'cte_max_recursion_depth','1000' 'datadir','/usr/local/mysql/data/' 'default_authentication_plugin','caching_sha2_password' 'default_collation_for_utf8mb4','utf8mb4_0900_ai_ci' 'default_password_lifetime','0' 'default_storage_engine','InnoDB' 'default_table_encryption','OFF' 'default_tmp_storage_engine','InnoDB' 'default_week_format','0' 'delay_key_write','ON' 'delayed_insert_limit','100' 'delayed_insert_timeout','300' 'delayed_queue_size','1000' 'disabled_storage_engines','' 'disconnect_on_expired_password','ON' 'div_precision_increment','4' 'end_markers_in_json','OFF' 'enforce_gtid_consistency','OFF' 'eq_range_index_dive_limit','200' 'error_count','0' 'event_scheduler','ON' 'expire_logs_days','0' 'explicit_defaults_for_timestamp','ON' 'external_user','' 'flush','OFF' 'flush_time','0' 'foreign_key_checks','ON' 'ft_boolean_syntax','+ -><()~:\"\"&|' 'ft_max_word_len','84' 'ft_min_word_len','4' 'ft_query_expansion_limit','20' 'ft_stopword_file','(built-in)' 'general_log','OFF' 'general_log_file','/usr/local/mysql/data/Rubens-MacBook-Pro.log' 'group_concat_max_len','1024' 'group_replication_consistency','EVENTUAL' 'gtid_executed','' 'gtid_executed_compression_period','1000' 'gtid_mode','OFF' 'gtid_next','AUTOMATIC' 'gtid_owned','' 'gtid_purged','' 'have_compress','YES' 'have_dynamic_loading','YES' 'have_geometry','YES' 'have_openssl','YES' 'have_profiling','YES' 'have_query_cache','NO' 'have_rtree_keys','YES' 'have_ssl','YES' 'have_statement_timeout','YES' 'have_symlink','DISABLED' 'histogram_generation_max_mem_size','20000000' 'host_cache_size','279' 'hostname','Rubens-MacBook-Pro.local' 'identity','0' 'immediate_server_version','999999' 'information_schema_stats_expiry','86400' 'init_connect','' 'init_file','' 'init_slave','' 'innodb_adaptive_flushing','ON' 'innodb_adaptive_flushing_lwm','10' 'innodb_adaptive_hash_index','ON' 'innodb_adaptive_hash_index_parts','8' 'innodb_adaptive_max_sleep_delay','150000' 'innodb_api_bk_commit_interval','5' 'innodb_api_disable_rowlock','OFF' 'innodb_api_enable_binlog','OFF' 'innodb_api_enable_mdl','OFF' 'innodb_api_trx_level','0' 'innodb_autoextend_increment','64' 'innodb_autoinc_lock_mode','2' 'innodb_buffer_pool_chunk_size','134217728' 'innodb_buffer_pool_dump_at_shutdown','ON' 'innodb_buffer_pool_dump_now','OFF' 'innodb_buffer_pool_dump_pct','25' 'innodb_buffer_pool_filename','ib_buffer_pool' 'innodb_buffer_pool_in_core_file','ON' 'innodb_buffer_pool_instances','1' 'innodb_buffer_pool_load_abort','OFF' 'innodb_buffer_pool_load_at_startup','ON' 'innodb_buffer_pool_load_now','OFF' 'innodb_buffer_pool_size','134217728' 'innodb_change_buffer_max_size','25' 'innodb_change_buffering','all' 'innodb_checksum_algorithm','crc32' 'innodb_cmp_per_index_enabled','OFF' 'innodb_commit_concurrency','0' 'innodb_compression_failure_threshold_pct','5' 'innodb_compression_level','6' 'innodb_compression_pad_pct_max','50' 'innodb_concurrency_tickets','5000' 'innodb_data_file_path','ibdata1:12M:autoextend' 'innodb_data_home_dir','' 'innodb_deadlock_detect','ON' 'innodb_dedicated_server','OFF' 'innodb_default_row_format','dynamic' 'innodb_directories','' 'innodb_disable_sort_file_cache','OFF' 'innodb_doublewrite','ON' 'innodb_fast_shutdown','1' 'innodb_file_per_table','ON' 'innodb_fill_factor','100' 'innodb_flush_log_at_timeout','1' 'innodb_flush_log_at_trx_commit','1' 'innodb_flush_method','fsync' 'innodb_flush_neighbors','0' 'innodb_flush_sync','ON' 'innodb_flushing_avg_loops','30' 'innodb_force_load_corrupted','OFF' 'innodb_force_recovery','0' 'innodb_fsync_threshold','0' 'innodb_ft_aux_table','' 'innodb_ft_cache_size','8000000' 'innodb_ft_enable_diag_print','OFF' 'innodb_ft_enable_stopword','ON' 'innodb_ft_max_token_size','84' 'innodb_ft_min_token_size','3' 'innodb_ft_num_word_optimize','2000' 'innodb_ft_result_cache_limit','2000000000' 'innodb_ft_server_stopword_table','' 'innodb_ft_sort_pll_degree','2' 'innodb_ft_total_cache_size','640000000' 'innodb_ft_user_stopword_table','' 'innodb_io_capacity','200' 'innodb_io_capacity_max','2000' 'innodb_lock_wait_timeout','50' 'innodb_log_buffer_size','16777216' 'innodb_log_checksums','ON' 'innodb_log_compressed_pages','ON' 'innodb_log_file_size','50331648' 'innodb_log_files_in_group','2' 'innodb_log_group_home_dir','./' 'innodb_log_spin_cpu_abs_lwm','80' 'innodb_log_spin_cpu_pct_hwm','50' 'innodb_log_wait_for_flush_spin_hwm','400' 'innodb_log_write_ahead_size','8192' 'innodb_lru_scan_depth','1024' 'innodb_max_dirty_pages_pct','90.000000' 'innodb_max_dirty_pages_pct_lwm','10.000000' 'innodb_max_purge_lag','0' 'innodb_max_purge_lag_delay','0' 'innodb_max_undo_log_size','1073741824' 'innodb_monitor_disable','' 'innodb_monitor_enable','' 'innodb_monitor_reset','' 'innodb_monitor_reset_all','' 'innodb_old_blocks_pct','37' 'innodb_old_blocks_time','1000' 'innodb_online_alter_log_max_size','134217728' 'innodb_open_files','4000' 'innodb_optimize_fulltext_only','OFF' 'innodb_page_cleaners','1' 'innodb_page_size','16384' 'innodb_parallel_read_threads','4' 'innodb_print_all_deadlocks','OFF' 'innodb_print_ddl_logs','OFF' 'innodb_purge_batch_size','300' 'innodb_purge_rseg_truncate_frequency','128' 'innodb_purge_threads','4' 'innodb_random_read_ahead','OFF' 'innodb_read_ahead_threshold','56' 'innodb_read_io_threads','4' 'innodb_read_only','OFF' 'innodb_redo_log_encrypt','OFF' 'innodb_replication_delay','0' 'innodb_rollback_on_timeout','OFF' 'innodb_rollback_segments','128' 'innodb_sort_buffer_size','1048576' 'innodb_spin_wait_delay','6' 'innodb_spin_wait_pause_multiplier','50' 'innodb_stats_auto_recalc','ON' 'innodb_stats_include_delete_marked','OFF' 'innodb_stats_method','nulls_equal' 'innodb_stats_on_metadata','OFF' 'innodb_stats_persistent','ON' 'innodb_stats_persistent_sample_pages','20' 'innodb_stats_transient_sample_pages','8' 'innodb_status_output','OFF' 'innodb_status_output_locks','OFF' 'innodb_strict_mode','ON' 'innodb_sync_array_size','1' 'innodb_sync_spin_loops','30' 'innodb_table_locks','ON' 'innodb_temp_data_file_path','ibtmp1:12M:autoextend' 'innodb_temp_tablespaces_dir','./#innodb_temp/' 'innodb_thread_concurrency','0' 'innodb_thread_sleep_delay','10000' 'innodb_tmpdir','' 'innodb_undo_directory','./' 'innodb_undo_log_encrypt','OFF' 'innodb_undo_log_truncate','ON' 'innodb_undo_tablespaces','2' 'innodb_use_native_aio','OFF' 'innodb_version','8.0.16' 'innodb_write_io_threads','4' 'insert_id','0' 'interactive_timeout','28800' 'internal_tmp_mem_storage_engine','TempTable' 'join_buffer_size','262144' 'keep_files_on_create','OFF' 'key_buffer_size','8388608' 'key_cache_age_threshold','300' 'key_cache_block_size','1024' 'key_cache_division_limit','100' 'keyring_file_data','/usr/local/mysql/keyring/keyring' 'keyring_operations','ON' 'large_files_support','ON' 'large_page_size','0' 'large_pages','OFF' 'last_insert_id','0' 'lc_messages','en_US' 'lc_messages_dir','/usr/local/mysql-8.0.16-macos10.14-x86_64/share/' 'lc_time_names','en_US' 'license','GPL' 'local_infile','OFF' 'lock_wait_timeout','31536000' 'locked_in_memory','OFF' 'log_bin','ON' 'log_bin_basename','/usr/local/mysql/data/binlog' 'log_bin_index','/usr/local/mysql/data/binlog.index' 'log_bin_trust_function_creators','OFF' 'log_bin_use_v1_row_events','OFF' 'log_error','/usr/local/mysql/data/mysqld.local.err' 'log_error_services','log_filter_internal; log_sink_internal' 'log_error_suppression_list','' 'log_error_verbosity','2' 'log_output','FILE' 'log_queries_not_using_indexes','OFF' 'log_slave_updates','ON' 'log_slow_admin_statements','OFF' 'log_slow_extra','OFF' 'log_slow_slave_statements','OFF' 'log_statements_unsafe_for_binlog','ON' 'log_throttle_queries_not_using_indexes','0' 'log_timestamps','UTC' 'long_query_time','10.000000' 'low_priority_updates','OFF' 'lower_case_file_system','ON' 'lower_case_table_names','2' 'mandatory_roles','' 'master_info_repository','TABLE' 'master_verify_checksum','OFF' 'max_allowed_packet','536870912' 'max_binlog_cache_size','18446744073709547520' 'max_binlog_size','1073741824' 'max_binlog_stmt_cache_size','18446744073709547520' 'max_connect_errors','100' 'max_connections','151' 'max_delayed_threads','20' 'max_digest_length','1024' 'max_error_count','1024' 'max_execution_time','0' 'max_heap_table_size','16777216' 'max_insert_delayed_threads','20' 'max_join_size','18446744073709551615' 'max_length_for_sort_data','4096' 'max_points_in_geometry','65536' 'max_prepared_stmt_count','16382' 'max_relay_log_size','0' 'max_seeks_for_key','18446744073709551615' 'max_sort_length','1024' 'max_sp_recursion_depth','0' 'max_user_connections','0' 'max_write_lock_count','18446744073709551615' 'min_examined_row_limit','0' 'myisam_data_pointer_size','6' 'myisam_max_sort_file_size','9223372036853727232' 'myisam_mmap_size','18446744073709551615' 'myisam_recover_options','OFF' 'myisam_repair_threads','1' 'myisam_sort_buffer_size','8388608' 'myisam_stats_method','nulls_unequal' 'myisam_use_mmap','OFF' 'mysql_native_password_proxy_users','OFF' 'mysqlx_bind_address','*' 'mysqlx_connect_timeout','30' 'mysqlx_document_id_unique_prefix','0' 'mysqlx_enable_hello_notice','ON' 'mysqlx_idle_worker_thread_timeout','60' 'mysqlx_interactive_timeout','28800' 'mysqlx_max_allowed_packet','67108864' 'mysqlx_max_connections','100' 'mysqlx_min_worker_threads','2' 'mysqlx_port','33060' 'mysqlx_port_open_timeout','0' 'mysqlx_read_timeout','30' 'mysqlx_socket','/tmp/mysqlx.sock' 'mysqlx_ssl_ca','' 'mysqlx_ssl_capath','' 'mysqlx_ssl_cert','' 'mysqlx_ssl_cipher','' 'mysqlx_ssl_crl','' 'mysqlx_ssl_crlpath','' 'mysqlx_ssl_key','' 'mysqlx_wait_timeout','28800' 'mysqlx_write_timeout','60' 'net_buffer_length','16384' 'net_read_timeout','30' 'net_retry_count','10' 'net_write_timeout','60' 'new','OFF' 'ngram_token_size','2' 'offline_mode','OFF' 'old','OFF' 'old_alter_table','OFF' 'open_files_limit','8161' 'optimizer_prune_level','1' 'optimizer_search_depth','62' 'optimizer_switch','index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on' 'optimizer_trace','enabled=off,one_line=off' 'optimizer_trace_features','greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on' 'optimizer_trace_limit','1' 'optimizer_trace_max_mem_size','1048576' 'optimizer_trace_offset','-1' 'original_commit_timestamp','36028797018963968' 'original_server_version','999999' 'parser_max_mem_size','18446744073709551615' 'partial_revokes','OFF' 'password_history','0' 'password_require_current','OFF' 'password_reuse_interval','0' 'performance_schema','ON' 'performance_schema_accounts_size','-1' 'performance_schema_digests_size','10000' 'performance_schema_error_size','4486' 'performance_schema_events_stages_history_long_size','10000' 'performance_schema_events_stages_history_size','10' 'performance_schema_events_statements_history_long_size','10000' 'performance_schema_events_statements_history_size','10' 'performance_schema_events_transactions_history_long_size','10000' 'performance_schema_events_transactions_history_size','10' 'performance_schema_events_waits_history_long_size','10000' 'performance_schema_events_waits_history_size','10' 'performance_schema_hosts_size','-1' 'performance_schema_max_cond_classes','100' 'performance_schema_max_cond_instances','-1' 'performance_schema_max_digest_length','1024' 'performance_schema_max_digest_sample_age','60' 'performance_schema_max_file_classes','80' 'performance_schema_max_file_handles','32768' 'performance_schema_max_file_instances','-1' 'performance_schema_max_index_stat','-1' 'performance_schema_max_memory_classes','450' 'performance_schema_max_metadata_locks','-1' 'performance_schema_max_mutex_classes','300' 'performance_schema_max_mutex_instances','-1' 'performance_schema_max_prepared_statements_instances','-1' 'performance_schema_max_program_instances','-1' 'performance_schema_max_rwlock_classes','60' 'performance_schema_max_rwlock_instances','-1' 'performance_schema_max_socket_classes','10' 'performance_schema_max_socket_instances','-1' 'performance_schema_max_sql_text_length','1024' 'performance_schema_max_stage_classes','175' 'performance_schema_max_statement_classes','218' 'performance_schema_max_statement_stack','10' 'performance_schema_max_table_handles','-1' 'performance_schema_max_table_instances','-1' 'performance_schema_max_table_lock_stat','-1' 'performance_schema_max_thread_classes','100' 'performance_schema_max_thread_instances','-1' 'performance_schema_session_connect_attrs_size','512' 'performance_schema_setup_actors_size','-1' 'performance_schema_setup_objects_size','-1' 'performance_schema_users_size','-1' 'persist_only_admin_x509_subject','' 'persisted_globals_load','ON' 'pid_file','/usr/local/mysql/data/mysqld.local.pid' 'plugin_dir','/usr/local/mysql/lib/plugin/' 'port','3306' 'preload_buffer_size','32768' 'profiling','OFF' 'profiling_history_size','15' 'protocol_version','10' 'proxy_user','' 'pseudo_slave_mode','OFF' 'pseudo_thread_id','14' 'query_alloc_block_size','8192' 'query_prealloc_size','8192' 'rand_seed1','0' 'rand_seed2','0' 'range_alloc_block_size','4096' 'range_optimizer_max_mem_size','8388608' 'rbr_exec_mode','STRICT' 'read_buffer_size','131072' 'read_only','OFF' 'read_rnd_buffer_size','262144' 'regexp_stack_limit','8000000' 'regexp_time_limit','32' 'relay_log','Rubens-MacBook-Pro-relay-bin' 'relay_log_basename','/usr/local/mysql/data/Rubens-MacBook-Pro-relay-bin' 'relay_log_index','/usr/local/mysql/data/Rubens-MacBook-Pro-relay-bin.index' 'relay_log_info_file','relay-log.info' 'relay_log_info_repository','TABLE' 'relay_log_purge','ON' 'relay_log_recovery','OFF' 'relay_log_space_limit','0' 'report_host','' 'report_password','' 'report_port','3306' 'report_user','' 'require_secure_transport','OFF' 'resultset_metadata','FULL' 'rpl_read_size','8192' 'rpl_stop_slave_timeout','31536000' 'schema_definition_cache','256' 'secondary_engine_cost_threshold','100000.000000' 'secure_file_priv','NULL' 'server_id','1' 'server_id_bits','32' 'server_uuid','50b7520a-7ca2-11e9-aa84-57f3bcd3ee30' 'session_track_gtids','OFF' 'session_track_schema','ON' 'session_track_state_change','OFF' 'session_track_system_variables','time_zone,autocommit,character_set_client,character_set_results,character_set_connection' 'session_track_transaction_info','OFF' 'sha256_password_auto_generate_rsa_keys','ON' 'sha256_password_private_key_path','private_key.pem' 'sha256_password_proxy_users','OFF' 'sha256_password_public_key_path','public_key.pem' 'show_create_table_verbosity','OFF' 'show_old_temporals','OFF' 'skip_external_locking','ON' 'skip_name_resolve','OFF' 'skip_networking','OFF' 'skip_show_database','OFF' 'slave_allow_batching','OFF' 'slave_checkpoint_group','512' 'slave_checkpoint_period','300' 'slave_compressed_protocol','OFF' 'slave_exec_mode','STRICT' 'slave_load_tmpdir','/var/tmp/' 'slave_max_allowed_packet','1073741824' 'slave_net_timeout','60' 'slave_parallel_type','DATABASE' 'slave_parallel_workers','0' 'slave_pending_jobs_size_max','134217728' 'slave_preserve_commit_order','OFF' 'slave_rows_search_algorithms','INDEX_SCAN,HASH_SCAN' 'slave_skip_errors','OFF' 'slave_sql_verify_checksum','ON' 'slave_transaction_retries','10' 'slave_type_conversions','' 'slow_launch_time','2' 'slow_query_log','OFF' 'slow_query_log_file','/usr/local/mysql/data/Rubens-MacBook-Pro-slow.log' 'socket','/tmp/mysql.sock' 'sort_buffer_size','262144' 'sql_auto_is_null','OFF' 'sql_big_selects','ON' 'sql_buffer_result','OFF' 'sql_log_bin','ON' 'sql_log_off','OFF' 'sql_mode','STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION' 'sql_notes','ON' 'sql_quote_show_create','ON' 'sql_require_primary_key','OFF' 'sql_safe_updates','ON' 'sql_select_limit','18446744073709551615' 'sql_slave_skip_counter','0' 'sql_warnings','OFF' 'ssl_ca','ca.pem' 'ssl_capath','' 'ssl_cert','server-cert.pem' 'ssl_cipher','' 'ssl_crl','' 'ssl_crlpath','' 'ssl_fips_mode','OFF' 'ssl_key','server-key.pem' 'stored_program_cache','256' 'stored_program_definition_cache','256' 'super_read_only','OFF' 'sync_binlog','1' 'sync_master_info','10000' 'sync_relay_log','10000' 'sync_relay_log_info','10000' 'system_time_zone','-03' 'table_definition_cache','2000' 'table_encryption_privilege_check','OFF' 'table_open_cache','4000' 'table_open_cache_instances','16' 'tablespace_definition_cache','256' 'temptable_max_ram','1073741824' 'temptable_use_mmap','ON' 'thread_cache_size','9' 'thread_handling','one-thread-per-connection' 'thread_stack','286720' 'time_zone','SYSTEM' 'timestamp','1558553572.337614' 'tls_ciphersuites','' 'tls_version','TLSv1,TLSv1.1,TLSv1.2' 'tmp_table_size','16777216' 'tmpdir','/var/tmp/' 'transaction_alloc_block_size','8192' 'transaction_allow_batching','OFF' 'transaction_isolation','REPEATABLE-READ' 'transaction_prealloc_size','4096' 'transaction_read_only','OFF' 'transaction_write_set_extraction','XXHASH64' 'unique_checks','ON' 'updatable_views_with_limit','YES' 'use_secondary_engine','ON' 'version','8.0.16' 'version_comment','MySQL Community Server - GPL' 'version_compile_machine','x86_64' 'version_compile_os','macos10.14' 'version_compile_zlib','1.2.11' 'wait_timeout','28800' 'warning_count','0' 'windowing_use_high_precision','ON'