Thursday 12 July 2012

MySQL Server Tuning


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