Spec-Zone .ru
спецификации, руководства, описания, API
|
|
Spec-Zone .ru
спецификации, руководства, описания, API
|
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 0allow-suspicious-udfs FALSEarchive ONauto-increment-increment 1auto-increment-offset 1autocommit TRUEautomatic-sp-privileges TRUEback-log 80basedir /home/jon/bin/mysql-5.6/big-tables FALSEbind-address *binlog-cache-size 32768binlog-checksum CRC32binlog-direct-non-transactional-updates FALSEbinlog-format STATEMENTbinlog-max-flush-queue-time 0binlog-order-commits TRUEbinlog-row-event-max-size 8192binlog-row-image FULLbinlog-rows-query-log-events FALSEbinlog-stmt-cache-size 32768blackhole ONbulk-insert-buffer-size 8388608character-set-client-handshake TRUEcharacter-set-filesystem binarycharacter-set-server latin1character-sets-dir /home/jon/bin/mysql-5.6/share/charsets/chroot (No default value)collation-server latin1_swedish_cicompletion-type NO_CHAINconcurrent-insert AUTOconnect-timeout 10console FALSEdatadir (No default value)date-format %Y-%m-%ddatetime-format %Y-%m-%d %H:%i:%sdefault-storage-engine InnoDBdefault-time-zone (No default value)default-tmp-storage-engine InnoDBdefault-week-format 0delay-key-write ONdelayed-insert-limit 100delayed-insert-timeout 300delayed-queue-size 1000des-key-file (No default value)disconnect-on-expired-password TRUEdisconnect-slave-event-count 0div-precision-increment 4end-markers-in-json FALSEenforce-gtid-consistency FALSEeq-range-index-dive-limit 10event-scheduler OFFexpire-logs-days 0explicit-defaults-for-timestamp FALSEexternal-locking FALSEflush FALSEflush-time 0ft-boolean-syntax + -><()~*:""&|ft-max-word-len 84ft-min-word-len 4ft-query-expansion-limit 20ft-stopword-file (No default value)gdb FALSEgeneral-log FALSEgeneral-log-file /home/jon/bin/mysql-5.6/data/havskatt.loggroup-concat-max-len 1024gtid-mode OFFhelp TRUEhost-cache-size 279ignore-builtin-innodb FALSEinit-connect init-file (No default value)init-slave innodb ONinnodb-adaptive-flushing TRUEinnodb-adaptive-flushing-lwm 10innodb-adaptive-hash-index TRUEinnodb-adaptive-max-sleep-delay 150000innodb-additional-mem-pool-size 8388608innodb-api-bk-commit-interval 5innodb-api-disable-rowlock FALSEinnodb-api-enable-binlog FALSEinnodb-api-enable-mdl FALSEinnodb-api-trx-level 0innodb-autoextend-increment 64innodb-autoinc-lock-mode 1innodb-buffer-page ONinnodb-buffer-page-lru ONinnodb-buffer-pool-dump-at-shutdown FALSEinnodb-buffer-pool-dump-now FALSEinnodb-buffer-pool-filename ib_buffer_poolinnodb-buffer-pool-instances 0innodb-buffer-pool-load-abort FALSEinnodb-buffer-pool-load-at-startup FALSEinnodb-buffer-pool-load-now FALSEinnodb-buffer-pool-size 134217728innodb-buffer-pool-stats ONinnodb-change-buffer-max-size 25innodb-change-buffering allinnodb-checksum-algorithm innodbinnodb-checksums TRUEinnodb-cmp ONinnodb-cmp-per-index ONinnodb-cmp-per-index-enabled FALSEinnodb-cmp-per-index-reset ONinnodb-cmp-reset ONinnodb-cmpmem ONinnodb-cmpmem-reset ONinnodb-commit-concurrency 0innodb-compression-failure-threshold-pct 5innodb-compression-level 6innodb-compression-pad-pct-max 50innodb-concurrency-tickets 5000innodb-data-file-path (No default value)innodb-data-home-dir (No default value)innodb-disable-sort-file-cache FALSEinnodb-doublewrite TRUEinnodb-fast-shutdown 1innodb-file-format Antelopeinnodb-file-format-check TRUEinnodb-file-format-max Antelopeinnodb-file-io-threads 4innodb-file-per-table TRUEinnodb-flush-log-at-timeout 1innodb-flush-log-at-trx-commit 1innodb-flush-method (No default value)innodb-flush-neighbors 1innodb-flushing-avg-loops 30innodb-force-load-corrupted FALSEinnodb-force-recovery 0innodb-ft-aux-table (No default value)innodb-ft-being-deleted ONinnodb-ft-cache-size 8000000innodb-ft-config ONinnodb-ft-default-stopword ONinnodb-ft-deleted ONinnodb-ft-enable-diag-print FALSEinnodb-ft-enable-stopword TRUEinnodb-ft-index-cache ONinnodb-ft-index-table ONinnodb-ft-inserted ONinnodb-ft-max-token-size 84innodb-ft-min-token-size 3innodb-ft-num-word-optimize 2000innodb-ft-server-stopword-table (No default value)innodb-ft-sort-pll-degree 2innodb-ft-user-stopword-table (No default value)innodb-io-capacity 200innodb-io-capacity-max 18446744073709551615innodb-large-prefix FALSEinnodb-lock-wait-timeout 50innodb-lock-waits ONinnodb-locks ONinnodb-locks-unsafe-for-binlog FALSEinnodb-log-buffer-size 8388608innodb-log-compressed-pages TRUEinnodb-log-file-size 50331648innodb-log-files-in-group 2innodb-log-group-home-dir (No default value)innodb-lru-scan-depth 1024innodb-max-dirty-pages-pct 75innodb-max-dirty-pages-pct-lwm 0innodb-max-purge-lag 0innodb-max-purge-lag-delay 0innodb-metrics ONinnodb-mirrored-log-groups 1innodb-monitor-disable (No default value)innodb-monitor-enable (No default value)innodb-monitor-reset (No default value)innodb-monitor-reset-all (No default value)innodb-old-blocks-pct 37innodb-old-blocks-time 1000innodb-online-alter-log-max-size 134217728innodb-open-files 0innodb-optimize-fulltext-only FALSEinnodb-page-size 16384innodb-print-all-deadlocks FALSEinnodb-purge-batch-size 300innodb-purge-threads 1innodb-random-read-ahead FALSEinnodb-read-ahead-threshold 56innodb-read-io-threads 4innodb-read-only FALSEinnodb-replication-delay 0innodb-rollback-on-timeout FALSEinnodb-rollback-segments 128innodb-sort-buffer-size 1048576innodb-spin-wait-delay 6innodb-stats-auto-recalc TRUEinnodb-stats-method nulls_equalinnodb-stats-on-metadata FALSEinnodb-stats-persistent TRUEinnodb-stats-persistent-sample-pages 20innodb-stats-sample-pages 8innodb-stats-transient-sample-pages 8innodb-status-file FALSEinnodb-strict-mode FALSEinnodb-support-xa TRUEinnodb-sync-array-size 1innodb-sync-spin-loops 30innodb-sys-columns ONinnodb-sys-datafiles ONinnodb-sys-fields ONinnodb-sys-foreign ONinnodb-sys-foreign-cols ONinnodb-sys-indexes ONinnodb-sys-tables ONinnodb-sys-tablespaces ONinnodb-sys-tablestats ONinnodb-table-locks TRUEinnodb-thread-concurrency 0innodb-thread-sleep-delay 10000innodb-trx ONinnodb-undo-directory .innodb-undo-logs 128innodb-undo-tablespaces 0innodb-use-native-aio TRUEinnodb-use-sys-malloc TRUEinnodb-write-io-threads 4interactive-timeout 28800join-buffer-size 262144keep-files-on-create FALSEkey-buffer-size 8388608key-cache-age-threshold 300key-cache-block-size 1024key-cache-division-limit 100language /home/jon/bin/mysql-5.6/share/large-pages FALSElc-messages en_USlc-messages-dir /home/jon/bin/mysql-5.6/share/lc-time-names en_USlocal-infile TRUElock-wait-timeout 31536000log-bin (No default value)log-bin-index (No default value)log-bin-trust-function-creators FALSElog-bin-use-v1-row-events FALSElog-error log-isam myisam.loglog-output FILElog-queries-not-using-indexes FALSElog-raw FALSElog-short-format FALSElog-slave-updates FALSElog-slow-admin-statements FALSElog-slow-slave-statements FALSElog-tc tc.loglog-tc-size 24576log-throttle-queries-not-using-indexes 0log-warnings 1long-query-time 10low-priority-updates FALSElower-case-table-names 0master-info-file master.infomaster-info-repository FILEmaster-retry-count 86400master-verify-checksum FALSEmax-allowed-packet 4194304max-binlog-cache-size 18446744073709547520max-binlog-dump-events 0max-binlog-size 1073741824max-binlog-stmt-cache-size 18446744073709547520max-connect-errors 100max-connections 151max-delayed-threads 20max-error-count 64max-heap-table-size 16777216max-join-size 18446744073709551615max-length-for-sort-data 1024max-prepared-stmt-count 16382max-relay-log-size 0max-seeks-for-key 18446744073709551615max-sort-length 1024max-sp-recursion-depth 0max-tmp-tables 32max-user-connections 0max-write-lock-count 18446744073709551615memlock FALSEmetadata-locks-cache-size 1024metadata-locks-hash-instances 8min-examined-row-limit 0multi-range-count 256myisam-block-size 1024myisam-data-pointer-size 6myisam-max-sort-file-size 9223372036853727232myisam-mmap-size 18446744073709551615myisam-recover-options OFFmyisam-repair-threads 1myisam-sort-buffer-size 8388608myisam-stats-method nulls_unequalmyisam-use-mmap FALSEnet-buffer-length 16384net-read-timeout 30net-retry-count 10net-write-timeout 60new FALSEold FALSEold-alter-table FALSEold-passwords 0old-style-user-limits FALSEopen-files-limit 1024optimizer-prune-level 1optimizer-search-depth 62optimizer-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,subquery_materialization_cost_based=on,use_index_extensions=onoptimizer-trace optimizer-trace-features greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=onoptimizer-trace-limit 1optimizer-trace-max-mem-size 16384optimizer-trace-offset -1partition ONperformance-schema TRUEperformance-schema-accounts-size -1performance-schema-consumer-events-stages-current FALSEperformance-schema-consumer-events-stages-history FALSEperformance-schema-consumer-events-stages-history-long FALSEperformance-schema-consumer-events-statements-current TRUEperformance-schema-consumer-events-statements-history FALSEperformance-schema-consumer-events-statements-history-long FALSEperformance-schema-consumer-events-waits-current FALSEperformance-schema-consumer-events-waits-history FALSEperformance-schema-consumer-events-waits-history-long FALSEperformance-schema-consumer-global-instrumentation TRUEperformance-schema-consumer-statements-digest TRUEperformance-schema-consumer-thread-instrumentation TRUEperformance-schema-digests-size -1performance-schema-events-stages-history-long-size -1performance-schema-events-stages-history-size -1performance-schema-events-statements-history-long-size -1performance-schema-events-statements-history-size -1performance-schema-events-waits-history-long-size -1performance-schema-events-waits-history-size -1performance-schema-hosts-size -1performance-schema-instrument performance-schema-max-cond-classes 80performance-schema-max-cond-instances -1performance-schema-max-file-classes 50performance-schema-max-file-handles 32768performance-schema-max-file-instances -1performance-schema-max-mutex-classes 200performance-schema-max-mutex-instances -1performance-schema-max-rwlock-classes 30performance-schema-max-rwlock-instances -1performance-schema-max-socket-classes 10performance-schema-max-socket-instances -1performance-schema-max-stage-classes 150performance-schema-max-statement-classes 167performance-schema-max-table-handles -1performance-schema-max-table-instances -1performance-schema-max-thread-classes 50performance-schema-max-thread-instances -1performance-schema-session-connect-attrs-size -1performance-schema-setup-actors-size 100performance-schema-setup-objects-size 100performance-schema-users-size -1pid-file /home/jon/bin/mysql-5.6/data/havskatt.pidplugin-dir /home/jon/bin/mysql-5.6/lib/plugin/port 3306port-open-timeout 0preload-buffer-size 32768profiling-history-size 15query-alloc-block-size 8192query-cache-limit 1048576query-cache-min-res-unit 4096query-cache-size 1048576query-cache-type OFFquery-cache-wlock-invalidate FALSEquery-prealloc-size 8192range-alloc-block-size 4096read-buffer-size 131072read-only FALSEread-rnd-buffer-size 262144relay-log (No default value)relay-log-index (No default value)relay-log-info-file relay-log.inforelay-log-info-repository FILErelay-log-purge TRUErelay-log-recovery FALSErelay-log-space-limit 0replicate-same-server-id FALSEreport-host (No default value)report-password (No default value)report-port 0report-user (No default value)safe-user-create FALSEsecure-auth TRUEsecure-file-priv (No default value)server-id 0server-id-bits 32sha256-password-private-key-path private_key.pemsha256-password-public-key-path public_key.pemshow-slave-auth-info FALSEskip-grant-tables FALSEskip-name-resolve FALSEskip-networking FALSEskip-show-database FALSEskip-slave-start FALSEslave-allow-batching FALSEslave-checkpoint-group 512slave-checkpoint-period 300slave-compressed-protocol FALSEslave-exec-mode STRICTslave-load-tmpdir /tmpslave-max-allowed-packet 1073741824slave-net-timeout 3600slave-parallel-workers 0slave-pending-jobs-size-max 16777216slave-rows-search-algorithms TABLE_SCAN,INDEX_SCANslave-skip-errors (No default value)slave-sql-verify-checksum TRUEslave-transaction-retries 10slave-type-conversions slow-launch-time 2slow-query-log FALSEslow-query-log-file /home/jon/bin/mysql-5.6/data/havskatt-slow.logsocket /tmp/mysql.socksort-buffer-size 262144sporadic-binlog-dump-fail FALSEsql-mode NO_ENGINE_SUBSTITUTIONssl FALSEssl-ca (No default value)ssl-capath (No default value)ssl-cert (No default value)ssl-cipher (No default value)ssl-crl (No default value)ssl-crlpath (No default value)ssl-key (No default value)stored-program-cache 256super-large-pages FALSEsymbolic-links TRUEsync-binlog 0sync-frm TRUEsync-master-info 10000sync-relay-log 10000sync-relay-log-info 10000sysdate-is-now FALSEtable-definition-cache 615table-open-cache 431table-open-cache-instances 1tc-heuristic-recover COMMITtemp-pool TRUEthread-cache-size 9thread-concurrency 10thread-handling one-thread-per-connectionthread-stack 262144time-format %H:%i:%stimed-mutexes FALSEtmp-table-size 16777216tmpdir /tmptransaction-alloc-block-size 8192transaction-isolation REPEATABLE-READtransaction-prealloc-size 4096transaction-read-only FALSEupdatable-views-with-limit YESverbose TRUEwait-timeout
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 variablesshell>mysqladmin extended-status
For a full description of all system and status variables, see Section 5.1.4, "Server System Variables", and Section 5.1.6, "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 MySQL more memory.
When tuning a MySQL 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 MySQL distribution; see Section 5.1.2, "Server Configuration Defaults".
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 Section
14.2.4.1, "InnoDB Performance Tuning Tips".