• MySQL 5.1 Reference Manual :: 7 Optimization :: 7.5 Optimizing the MySQL Server :: 7.5.3 Tuning Server Parameters
  • 7.5.3. Tuning Server Parameters

    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
    auto-increment-increment          1
    auto-increment-offset             1
    automatic-sp-privileges           TRUE
    back_log                          50
    basedir                           /home/jon/bin/mysql-5.1/
    bind-address                      (No default value)
    binlog-row-event-max-size         1024
    binlog_cache_size                 32768
    binlog_format                     (No default value)
    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.1/share/mysql/charsets/
    chroot                            (No default value)
    collation-server                  latin1_swedish_ci
    completion-type                   0
    concurrent-insert                 1
    connect_timeout                   10
    console                           FALSE
    datadir                           .
    datetime_format                   %Y-%m-%d %H:%i:%s
    date_format                       %Y-%m-%d
    default-character-set             latin1
    default-collation                 latin1_swedish_ci
    default-storage-engine            MyISAM
    default-table-type                MyISAM
    default-time-zone                 (No default value)
    default_week_format               0
    delayed_insert_limit              100
    delayed_insert_timeout            300
    delayed_queue_size                1000
    disconnect-slave-event-count      0
    div_precision_increment           4
    enable-locking                    FALSE
    engine-condition-pushdown         TRUE
    expire_logs_days                  0
    external-locking                  FALSE
    flush_time                        0
    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                  (No default value)
    group_concat_max_len              1024
    help                              TRUE
    init-connect                      (No default value)
    init-file                         (No default value)
    init-slave                        (No default value)
    innodb                            TRUE
    innodb-adaptive-hash-index        TRUE
    innodb-additional-mem-pool-size   1048576
    innodb-autoextend-increment       8
    innodb-autoinc-lock-mode          1
    innodb-buffer-pool-size           8388608
    innodb-checksums                  TRUE
    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-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-lock-wait-timeout          50
    innodb-locks-unsafe-for-binlog    FALSE
    innodb-log-buffer-size            1048576
    innodb-log-file-size              5242880
    innodb-log-files-in-group         2
    innodb-log-group-home-dir         (No default value)
    innodb-max-dirty-pages-pct        90
    innodb-max-purge-lag              0
    innodb-mirrored-log-groups        1
    innodb-open-files                 300
    innodb-rollback-on-timeout        FALSE
    innodb-stats-on-metadata          TRUE
    innodb-status-file                FALSE
    innodb-support-xa                 TRUE
    innodb-sync-spin-loops            20
    innodb-table-locks                TRUE
    innodb-thread-concurrency         8
    innodb-thread-sleep-delay         10000
    interactive_timeout               28800
    join_buffer_size                  131072
    keep_files_on_create              FALSE
    key_buffer_size                   8384512
    key_cache_age_threshold           300
    key_cache_block_size              1024
    key_cache_division_limit          100
    language                          /home/jon/bin/mysql-5.1/share/mysql/english/
    large-pages                       FALSE
    lc-time-names                     en_US
    local-infile                      TRUE
    log                               (No default value)
    log-bin                           (No default value)
    log-bin-index                     (No default value)
    log-bin-trust-function-creators   FALSE
    log-bin-trust-routine-creators    FALSE
    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-slave-statements         FALSE
    log-tc                            tc.log
    log-tc-size                       24576
    log-update                        (No default value)
    log-warnings                      1
    log_slow_queries                  (No default value)
    long_query_time                   10
    low-priority-updates              FALSE
    lower_case_table_names            0
    master-connect-retry              60
    master-host                       (No default value)
    master-info-file                  master.info
    master-password                   (No default value)
    master-port                       3306
    master-retry-count                86400
    master-ssl                        FALSE
    master-ssl-ca                     (No default value)
    master-ssl-capath                 (No default value)
    master-ssl-cert                   (No default value)
    master-ssl-cipher                 (No default value)
    master-ssl-key                    (No default value)
    master-user                       test
    max-binlog-dump-events            0
    max_allowed_packet                1048576
    max_binlog_cache_size             18446744073709547520
    max_binlog_size                   1073741824
    max_connections                   151
    max_connect_errors                10
    max_delayed_threads               20
    max_error_count                   64
    max_heap_table_size               16777216
    max_join_size                     18446744073709551615
    max_length_for_sort_data          1024
    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-recover                    OFF
    myisam_block_size                 1024
    myisam_data_pointer_size          6
    myisam_max_extra_sort_file_size   2147483648
    myisam_max_sort_file_size         9223372036853727232
    myisam_repair_threads             1
    myisam_sort_buffer_size           8388608
    myisam_stats_method               nulls_unequal
    myisam_use_mmap                   FALSE
    ndb-autoincrement-prefetch-sz     1
    ndb-cache-check-time              0
    ndb-connectstring                 (No default value)
    ndb-extra-logging                 0
    ndb-force-send                    TRUE
    ndb-index-stat-enable             FALSE
    ndb-mgmd-host                     (No default value)
    ndb-nodeid                        0
    ndb-optimized-node-selection      TRUE
    ndb-report-thresh-binlog-epoch-slip 3
    ndb-report-thresh-binlog-mem-usage 10
    ndb-shm                           FALSE
    ndb-use-copying-alter-table       FALSE
    ndb-use-exact-count               TRUE
    ndb-use-transactions              TRUE
    ndb_force_send                    TRUE
    ndb_use_exact_count               TRUE
    ndb_use_transactions              TRUE
    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
    pid-file                          /home/jon/bin/mysql-5.1/var/tonfisk.pid
    plugin-load                       (No default value)
    plugin_dir                        /home/jon/bin/mysql-5.1/lib/mysql/plugin
    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                  1
    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
    record_buffer                     131072
    relay-log                         (No default value)
    relay-log-index                   (No default value)
    relay-log-info-file               relay-log.info
    relay_log_purge                   TRUE
    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                         0
    show-slave-auth-info              FALSE
    skip-grant-tables                 FALSE
    skip-slave-start                  FALSE
    slave-exec-mode                   STRICT
    slave-load-tmpdir                 /tmp
    slave_compressed_protocol         FALSE
    slave_net_timeout                 3600
    slave_transaction_retries         10
    slow-query-log                    FALSE
    slow_launch_time                  2
    slow_query_log_file               (No default value)
    socket                            /tmp/mysql.sock
    sort_buffer_size                  2097144
    sporadic-binlog-dump-fail         FALSE
    sql-mode                          OFF
    symbolic-links                    TRUE
    sync-binlog                       0
    sync-frm                          TRUE
    sysdate-is-now                    FALSE
    table_definition_cache            256
    table_lock_wait_timeout           50
    table_open_cache                  64
    tc-heuristic-recover              (No default value)
    temp-pool                         TRUE
    thread_cache_size                 0
    thread_concurrency                10
    thread_stack                      262144
    timed_mutexes                     FALSE
    time_format                       %H:%i:%s
    tmpdir                            (No default value)
    tmp_table_size                    16777216
    transaction_alloc_block_size      8192
    transaction_prealloc_size         4096
    updatable_views_with_limit        1
    use-symbolic-links                TRUE
    verbose                           TRUE
    wait_timeout                      28800
    warnings                          1

    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 variables
    shell> 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, you should 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, you should 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, “Preconfigured Option Files”.

    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, “InnoDB Performance Tuning Tips”.

    MySQL Enterprise.  For expert advice on tuning system parameters, subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.