Tuning Server Parameters

You can determine the default buffer sizes used by the mysqld server using this command:

shell> mysqld --verbose --help

This command produces a list of all mysqld options and configurable system variables. The output includes the default variable values and looks something like this:

abort-slave-event-count 0
allow-suspicious-udfs FALSE auto-increment-increment 1
auto-increment-offset 1
automatic-sp-privileges TRUE back_log 50
basedir /home/jon/bin/mysql-5.6/
bind-address (No default value)
binlog-row-event-max-size 1024
binlog_cache_size 32768
binlog_format (No default value)
bulk_insert_buffer_size 8388608
character-set-client-handshake TRUE character-set-filesystem binary character-set-server latin1
character-sets-dir /home/jon/bin/mysql-5.6/share/mysql/charsets/
chroot (No default value)
collation-server latin1_swedish_ci completion-type 0
concurrent-insert 1
connect_timeout 10
console FALSE datadir .
datetime_format %Y-%m-%d %H:%i:%s date_format %Y-%m-%d default-storage-engine MyISAM default-time-zone (No default value)
default_week_format 0
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
disconnect-slave-event-count 0
div_precision_increment 4
engine-condition-pushdown TRUE expire_logs_days 0
external-locking FALSE flush_time 0
ft_max_word_len 84
ft_min_word_len 4
ft_query_expansion_limit 20
ft_stopword_file (No default value)
gdb FALSE general_log FALSE general_log_file (No default value)
group_concat_max_len 1024
help TRUE init-connect (No default value)
init-file (No default value)
init-slave (No default value)
innodb TRUE innodb-adaptive-hash-index TRUE innodb-additional-mem-pool-size 1048576
innodb-autoextend-increment 8
innodb-autoinc-lock-mode 1
innodb-buffer-pool-size 8388608
innodb-checksums TRUE innodb-commit-concurrency 0
innodb-concurrency-tickets 500
innodb-data-file-path (No default value)
innodb-data-home-dir (No default value)
innodb-doublewrite TRUE innodb-fast-shutdown 1
innodb-file-io-threads 4
innodb-file-per-table FALSE innodb-flush-log-at-trx-commit 1
innodb-flush-method (No default value)
innodb-force-recovery 0
innodb-lock-wait-timeout 50
innodb-locks-unsafe-for-binlog FALSE innodb-log-buffer-size 1048576
innodb-log-file-size 5242880
innodb-log-files-in-group 2
innodb-log-group-home-dir (No default value)
innodb-max-dirty-pages-pct 90
innodb-max-purge-lag 0
innodb-mirrored-log-groups 1
innodb-open-files 300
innodb-rollback-on-timeout FALSE innodb-stats-on-metadata TRUE innodb-status-file FALSE innodb-support-xa TRUE innodb-sync-spin-loops 20
innodb-table-locks TRUE innodb-thread-concurrency 8
innodb-thread-sleep-delay 10000
interactive_timeout 28800
join_buffer_size 131072
keep_files_on_create FALSE key_buffer_size 8384512
key_cache_age_threshold 300
key_cache_block_size 1024
key_cache_division_limit 100
language /home/jon/bin/mysql-5.6/share/mysql/english/
large-pages FALSE lc-time-names en_US local-infile TRUE log (No default value)
log-bin (No default value)
log-bin-index (No default value)
log-bin-trust-function-creators FALSE log-error log-isam myisam.log log-output FILE log-queries-not-using-indexes FALSE log-short-format FALSE log-slave-updates FALSE log-slow-admin-statements FALSE log-slow-slave-statements FALSE log-tc tc.log log-tc-size 24576
log-warnings 1
log_slow_queries (No default value)
long_query_time 10
low-priority-updates FALSE lower_case_table_names 0
master-retry-count 86400
max-binlog-dump-events 0
max_allowed_packet 1048576
max_binlog_cache_size 18446744073709547520
max_binlog_size 1073741824
max_connections 151
max_connect_errors 10
max_delayed_threads 20
max_error_count 64
max_heap_table_size 16777216
max_join_size 18446744073709551615
max_length_for_sort_data 1024
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_tmp_tables 32
max_user_connections 0
max_write_lock_count 18446744073709551615
memlock FALSE min_examined_row_limit 0
multi_range_count 256
myisam-recover-options OFF myisam_block_size 1024
myisam_data_pointer_size 6
myisam_max_sort_file_size 9223372036853727232
myisam_repair_threads 1
myisam_sort_buffer_size 8388608
myisam_stats_method nulls_unequal myisam_use_mmap FALSE ndb-autoincrement-prefetch-sz 1
ndb-cache-check-time 0
ndb-connectstring (No default value)
ndb-extra-logging 0
ndb-force-send TRUE ndb-index-stat-enable FALSE ndb-mgmd-host (No default value)
ndb-nodeid 0
ndb-optimized-node-selection TRUE ndb-report-thresh-binlog-epoch-slip 3
ndb-report-thresh-binlog-mem-usage 10
ndb-shm FALSE ndb-use-copying-alter-table FALSE ndb-use-exact-count TRUE ndb-use-transactions TRUE ndb_force_send TRUE ndb_use_exact_count TRUE ndb_use_transactions TRUE net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new FALSE old FALSE old-alter-table FALSE old-passwords FALSE old-style-user-limits FALSE open_files_limit 1024
optimizer_prune_level 1
optimizer_search_depth 62
pid-file /home/jon/bin/mysql-5.6/var/tonfisk.pid plugin_dir /home/jon/bin/mysql-5.6/lib/mysql/plugin port 3306
port-open-timeout 0
preload_buffer_size 32768
profiling_history_size 15
query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_min_res_unit 4096
query_cache_size 0
query_cache_type 1
query_cache_wlock_invalidate FALSE query_prealloc_size 8192
range_alloc_block_size 4096
read_buffer_size 131072
read_only FALSE read_rnd_buffer_size 262144
relay-log (No default value)
relay-log-index (No default value)
relay-log-info-file relay-log.info relay_log_purge TRUE relay_log_space_limit 0
replicate-same-server-id FALSE report-host (No default value)
report-password (No default value)
report-port 3306
report-user (No default value)
safe-user-create FALSE secure-auth TRUE secure-file-priv (No default value)
server-id 0
show-slave-auth-info FALSE skip-grant-tables FALSE skip-slave-start FALSE slave-exec-mode STRICT slave-load-tmpdir /tmp slave_compressed_protocol FALSE slave_net_timeout 3600
slave_transaction_retries 10
slow-query-log FALSE slow_launch_time 2
slow_query_log_file (No default value)
socket /tmp/mysql.sock sort_buffer_size 2097144
sporadic-binlog-dump-fail FALSE sql-mode OFF symbolic-links TRUE sync-binlog 0
sync-frm TRUE sysdate-is-now FALSE table_definition_cache 256
table_open_cache 400
tc-heuristic-recover (No default value)
temp-pool TRUE thread_cache_size 0
thread_concurrency 10
thread_stack 262144
timed_mutexes FALSE time_format %H:%i:%s tmpdir (No default value)
tmp_table_size 16777216
transaction_alloc_block_size 8192
transaction_prealloc_size 4096
updatable_views_with_limit 1
verbose TRUE wait_timeout 28800

For a mysqld server that is currently running, you can see the current values of its system variables by connecting to it and issuing this statement:

mysql> SHOW VARIABLES;

You can also see some statistical and status indicators for a running server by issuing this statement:

mysql> SHOW STATUS;

System variable and status information also can be obtained using mysqladmin:

shell> mysqladmin variables
shell> mysqladmin extended-status

For a full description of all system and status variables, see , "Server System Variables", and , "Server Status Variables".

MySQL uses algorithms that are very scalable, so you can usually run with very little memory. However, normally you get better performance by giving MariaDB more memory.

When tuning a MariaDB server, the two most important variables to configure are key_buffer_size and table_open_cache. You should first feel confident that you have these set appropriately before trying to change any other variables.

The following examples indicate some typical variable values for different runtime configurations.

• If you have at least 256MB of memory and many tables and want maximum performance with a moderate number of clients, use something like this:
  
  

  shell> mysqld_safe --key_buffer_size=64M --table_open_cache=256 \
   --sort_buffer_size=4M --read_buffer_size=1M &
  

• If you have only 128MB of memory and only a few tables, but you still do a lot of sorting, you can use something like this:
  
  

  shell> mysqld_safe --key_buffer_size=16M --sort_buffer_size=1M
  

  If there are very many simultaneous connections, swapping problems may occur unless mysqld has been configured to use very little memory for each connection. mysqld performs better if you have enough memory for all connections.

• With little memory and lots of connections, use something like this:
  
  

  shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=100K \
   --read_buffer_size=100K &
  

  Or even this:

  shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K \
   --table_open_cache=32 --read_buffer_size=8K \
   --net_buffer_length=1K &
  

If you are performing GROUP BY or ORDER BY operations on tables that are much larger than your available memory, increase the value of read_rnd_buffer_size to speed up the reading of rows following sorting operations.

You can make use of the example option files included with your MariaDB distribution; see , "Preconfigured Option Files".

If you specify an option on the command line for mysqld or mysqld_safe, it remains in effect only for that invocation of the server. To use the option every time the server runs, put it in an option file.

To see the effects of a parameter change, do something like this:

shell> mysqld --key_buffer_size=32M --verbose --help

The variable values are listed near the end of the output. Make sure that the --verbose and --help options are last. Otherwise, the effect of any options listed after them on the command line are not reflected in the output.

For information on tuning the InnoDB storage engine, see , "InnoDB Performance Tuning Tips".

Retornar