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 archive ON auto-increment-increment 1 auto-increment-offset 1 autocommit TRUE automatic-sp-privileges TRUE back-log 50 basedir /home/jon/bin/mysql-5.5-cluster/ big-tables FALSE bind-address (No default value) binlog-cache-size 32768 binlog-direct-non-transactional-updates FALSE binlog-format STATEMENT binlog-row-event-max-size 1024 binlog-stmt-cache-size 32768 blackhole ON 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.5-cluster/share/charsets/ chroot (No default value) collation-server latin1_swedish_ci completion-type NO_CHAIN concurrent-insert AUTO connect-timeout 10 console FALSE datadir (No default value) date-format %Y-%m-%d datetime-format %Y-%m-%d %H:%i:%s default-storage-engine InnoDB default-time-zone (No default value) default-week-format 0 delay-key-write ON delayed-insert-limit 100 delayed-insert-timeout 300 delayed-queue-size 1000 des-key-file (No default value) disconnect-slave-event-count 0 div-precision-increment 4 engine-condition-pushdown TRUE event-scheduler OFF expire-logs-days 0 external-locking FALSE flush FALSE flush-time 0 ft-boolean-syntax + -><()~*:""&| 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 /home/jon/bin/mysql-5.5-cluster/data/torsk.log group-concat-max-len 1024 help TRUE ignore-builtin-innodb FALSE init-connect init-file (No default value) init-rpl-role MASTER init-slave innodb ON innodb-adaptive-flushing TRUE innodb-adaptive-hash-index TRUE innodb-additional-mem-pool-size 8388608 innodb-autoextend-increment 8 innodb-autoinc-lock-mode 1 innodb-buffer-pool-instances 1 innodb-buffer-pool-size 134217728 innodb-change-buffering all innodb-checksums TRUE innodb-cmp ON innodb-cmp-reset ON innodb-cmpmem ON innodb-cmpmem-reset ON 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-format Antelope innodb-file-format-check TRUE innodb-file-format-max Antelope 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-io-capacity 200 innodb-large-prefix FALSE innodb-lock-wait-timeout 50 innodb-lock-waits ON innodb-locks ON innodb-locks-unsafe-for-binlog FALSE innodb-log-buffer-size 8388608 innodb-log-file-size 5242880 innodb-log-files-in-group 2 innodb-log-group-home-dir (No default value) innodb-max-dirty-pages-pct 75 innodb-max-purge-lag 0 innodb-mirrored-log-groups 1 innodb-old-blocks-pct 37 innodb-old-blocks-time 0 innodb-open-files 300 innodb-purge-batch-size 20 innodb-purge-threads 0 innodb-read-ahead-threshold 56 innodb-read-io-threads 4 innodb-replication-delay 0 innodb-rollback-on-timeout FALSE innodb-rollback-segments 128 innodb-spin-wait-delay 6 innodb-stats-method nulls_equal innodb-stats-on-metadata TRUE innodb-stats-sample-pages 8 innodb-status-file FALSE innodb-strict-mode FALSE innodb-support-xa TRUE innodb-sync-spin-loops 30 innodb-table-locks TRUE innodb-thread-concurrency 0 innodb-thread-sleep-delay 10000 innodb-trx ON innodb-use-native-aio TRUE innodb-use-sys-malloc TRUE innodb-write-io-threads 4 interactive-timeout 28800 join-buffer-size 131072 keep-files-on-create FALSE key-buffer-size 8388608 key-cache-age-threshold 300 key-cache-block-size 1024 key-cache-division-limit 100 language /home/jon/bin/mysql-5.5-cluster/share/ large-pages FALSE lc-messages en_US lc-messages-dir /home/jon/bin/mysql-5.5-cluster/share/ lc-time-names en_US local-infile TRUE lock-wait-timeout 31536000 log /home/jon/bin/mysql-5.5-cluster/data/torsk.log log-bin mister-pibb 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-queries /home/jon/bin/mysql-5.5-cluster/data/torsk-slow.log log-slow-slave-statements FALSE log-tc tc.log log-tc-size 24576 log-warnings 1 long-query-time 10 low-priority-updates FALSE lower-case-table-names 0 master-info-file master.info master-retry-count 86400 max-allowed-packet 1048576 max-binlog-cache-size 18446744073709547520 max-binlog-dump-events 0 max-binlog-size 1073741824 max-binlog-stmt-cache-size 18446744073709547520 max-connect-errors 10 max-connections 151 max-delayed-threads 20 max-error-count 64 max-heap-table-size 16777216 max-join-size 18446744073709551615 max-length-for-sort-data 1024 max-long-data-size 1048576 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-block-size 1024 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 FALSE ndb-autoincrement-prefetch-sz 1 ndb-batch-size 32768 ndb-blob-read-batch-bytes 65536 ndb-blob-write-batch-bytes 65536 ndb-cache-check-time 0 ndb-cluster-connection-pool 1 ndb-connectstring (No default value) ndb-deferred-constraints 0 ndb-distribution KEYHASH ndb-extra-logging 1 ndb-force-send TRUE ndb-index-stat-cache-entries 32 ndb-index-stat-enable TRUE ndb-index-stat-option loop_checkon=1000ms,loop_idle=1000ms,loop_busy=100ms,update_batch=1,read_batch=4,idle_batch=32,check_batch=32,check_delay=1m,delete_batch=8,clean_delay=0,error_batch=4,error_delay=1m,evict_batch=8,evict_delay=1m,cache_limit=32M,cache_lowpct=90 ndb-index-stat-update-freq 20 ndb-join-pushdown TRUE ndb-log-apply-status FALSE ndb-log-bin TRUE ndb-log-binlog-index TRUE ndb-log-empty-epochs FALSE ndb-log-orig FALSE ndb-log-transaction-id FALSE ndb-log-update-as-write TRUE ndb-log-updated-only TRUE ndb-mgmd-host (No default value) ndb-nodeid 0 ndb-optimization-delay 10 ndb-optimized-node-selection 3 ndb-report-thresh-binlog-epoch-slip 3 ndb-report-thresh-binlog-mem-usage 10 ndb-table-no-logging FALSE ndb-table-temporary FALSE ndb-use-copying-alter-table FALSE ndb-use-exact-count FALSE ndb-use-transactions TRUE ndb-wait-connected 0 ndb-wait-setup 15 ndbcluster ON ndbinfo ON ndbinfo-database ndbinfo ndbinfo-max-bytes 0 ndbinfo-max-rows 10 ndbinfo-show-hidden FALSE ndbinfo-table-prefix ndb$ 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 optimizer-switch index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on partition ON performance-schema FALSE performance-schema-events-waits-history-long-size 10000 performance-schema-events-waits-history-size 10 performance-schema-max-cond-classes 80 performance-schema-max-cond-instances 1000 performance-schema-max-file-classes 50 performance-schema-max-file-handles 32768 performance-schema-max-file-instances 10000 performance-schema-max-mutex-classes 200 performance-schema-max-mutex-instances 1000000 performance-schema-max-rwlock-classes 30 performance-schema-max-rwlock-instances 1000000 performance-schema-max-table-handles 100000 performance-schema-max-table-instances 50000 performance-schema-max-thread-classes 50 performance-schema-max-thread-instances 1000 pid-file /home/jon/bin/mysql-5.5-cluster/data/torsk.pid plugin-dir /home/jon/bin/mysql-5.5-cluster/lib/plugin plugin-load (No default value) 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 ON 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-recovery FALSE 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) rpl-recovery-rank 0 safe-user-create FALSE secure-auth FALSE secure-file-priv (No default value) server-id 1 server-id-bits 32 show-slave-auth-info FALSE skip-grant-tables FALSE skip-name-resolve FALSE skip-networking FALSE skip-show-database FALSE skip-slave-start FALSE slave-allow-batching FALSE slave-compressed-protocol FALSE slave-exec-mode STRICT slave-load-tmpdir /tmp slave-net-timeout 3600 slave-skip-errors (No default value) slave-transaction-retries 10 slave-type-conversions slow-launch-time 2 slow-query-log FALSE slow-query-log-file /home/jon/bin/mysql-5.5-cluster/data/torsk-slow.log socket /tmp/mysql.sock sort-buffer-size 2097152 sporadic-binlog-dump-fail FALSE sql-mode ssl FALSE ssl-ca (No default value) ssl-capath (No default value) ssl-cert (No default value) ssl-cipher (No default value) ssl-key (No default value) super-large-pages FALSE symbolic-links TRUE sync-binlog 0 sync-frm TRUE sync-master-info 0 sync-relay-log 0 sync-relay-log-info 0 sysdate-is-now FALSE table-cache 400 table-definition-cache 400 table-open-cache 400 tc-heuristic-recover COMMIT temp-pool TRUE thread-cache-size 0 thread-concurrency 10 thread-handling one-thread-per-connection thread-stack 262144 time-format %H:%i:%s timed-mutexes FALSE tmp-table-size 16777216 tmpdir /tmp transaction-alloc-block-size 8192 transaction-isolation REPEATABLE-READ transaction-prealloc-size 4096 updatable-views-with-limit YES 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 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=1MIf 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.3.14.1, “InnoDB Performance Tuning Tips”.
User Comments
Add your own comment.