tunning performance optimal mysql
show variables;
show status
mysqladmin variables
mysqladmin extended-status
my.cnf
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
沒有留言:
張貼留言