MySQL server parameter description and view setting method
- 2020-05-07 20:31:47
- OfStack
View parameters: SHOW VARIABLES;
Setting parameters: SET GLOBAL parameter name =value; For example, set the maximum number of connections in the database to SET GLOBAL max_connections=1000.
Use shell > The command mysqld-help produces a table of all mysql options and configurable variables.
possible variables for option--set-variable(-o) are:
back_log current value:5 // requires the number of connections that mysql can have
connect_timeout current value:5 //mysql server time to wait for 1 connection before replying with bad handshake
delayed_insert_timeout current value:200 // the time for one insert delayed to wait for insert before terminating
delayed_insert_limit current value:50 //insert delayed processor will check to see if any select statements have not been executed, and if so, proceed before executing them
delayed_queue_size current value:1000 // assign the size of the team for insert delayed
flush_time current value:0 // if set to non-0, all tables are closed for each flush_time time
interactive_timeout current value:28800 // time the server waits on the interconnect before shutting it down
join_buffer_size current value:131072 // with the buffer size for all connections
key_buffer_size current value:1048540 // increase the size of the buffer of the index block to better handle the index
lower_case_table_names current value:0 //
long_query_time current value:10 // if a query takes longer than that, the slow_queried count increases
max_allowed_packet current value: size of 1048576 //1 package
max_connections current es1064en :300 // number of simultaneous connections allowed
max_connect_errors current value:10 // if there are more than this number of interrupted connections, the next step will be blocked. This can be resolved with flush hosts
max_delayed_threads current value:15 // the number of processing insert delayed that can be started
max_heap_table_size current value:16777216 //
max_join_size current value:4294967295 // number of connections allowed to be read
max_sort_length current value:1024 // the number of bytes used to sort blob or text
max_tmp_tables current value:32 // the number of temporary tables open for one connection
max_write_lock_count value:4294967295 // specify a value (usually small) to enable mysqld so that an read lock occurs after a fixed number of write locks
net_buffer_length current value:16384 // size of the communication buffer - reset to this size on query
query_buffer_size current value:0 // buffer size at query time
record_buffer current value:131072 // the size of the buffer allocated by each sequential scan connection for each table it scans
sort_buffer current value:2097116 // size of buffer allocated for each sorted connection
table_cache current value:64 // number of tables open for all connections
thread_concurrency current value:10 //
tmp_table_size current value:1048576 // size of temporary table
thread_stack current value:131072 // size per thread
wait_timeout current value:28800 // time the server waited on a connection before shutting it down 3
Setting parameters: SET GLOBAL parameter name =value; For example, set the maximum number of connections in the database to SET GLOBAL max_connections=1000.
Use shell > The command mysqld-help produces a table of all mysql options and configurable variables.
possible variables for option--set-variable(-o) are:
back_log current value:5 // requires the number of connections that mysql can have
connect_timeout current value:5 //mysql server time to wait for 1 connection before replying with bad handshake
delayed_insert_timeout current value:200 // the time for one insert delayed to wait for insert before terminating
delayed_insert_limit current value:50 //insert delayed processor will check to see if any select statements have not been executed, and if so, proceed before executing them
delayed_queue_size current value:1000 // assign the size of the team for insert delayed
flush_time current value:0 // if set to non-0, all tables are closed for each flush_time time
interactive_timeout current value:28800 // time the server waits on the interconnect before shutting it down
join_buffer_size current value:131072 // with the buffer size for all connections
key_buffer_size current value:1048540 // increase the size of the buffer of the index block to better handle the index
lower_case_table_names current value:0 //
long_query_time current value:10 // if a query takes longer than that, the slow_queried count increases
max_allowed_packet current value: size of 1048576 //1 package
max_connections current es1064en :300 // number of simultaneous connections allowed
max_connect_errors current value:10 // if there are more than this number of interrupted connections, the next step will be blocked. This can be resolved with flush hosts
max_delayed_threads current value:15 // the number of processing insert delayed that can be started
max_heap_table_size current value:16777216 //
max_join_size current value:4294967295 // number of connections allowed to be read
max_sort_length current value:1024 // the number of bytes used to sort blob or text
max_tmp_tables current value:32 // the number of temporary tables open for one connection
max_write_lock_count value:4294967295 // specify a value (usually small) to enable mysqld so that an read lock occurs after a fixed number of write locks
net_buffer_length current value:16384 // size of the communication buffer - reset to this size on query
query_buffer_size current value:0 // buffer size at query time
record_buffer current value:131072 // the size of the buffer allocated by each sequential scan connection for each table it scans
sort_buffer current value:2097116 // size of buffer allocated for each sorted connection
table_cache current value:64 // number of tables open for all connections
thread_concurrency current value:10 //
tmp_table_size current value:1048576 // size of temporary table
thread_stack current value:131072 // size per thread
wait_timeout current value:28800 // time the server waited on a connection before shutting it down 3