Tuning Server Parameters
You can determine the default buffer sizes used by the mysqld server using this command:
shell> mysqld --verbose --help
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
No comments:
Post a Comment