优化 MySQLd:硬件以及 MySQLd 配置变量

Tags: database mysql

编译 MySQL

就像赛车手需要在赛道外完成很多工作一样,如果你一开始没有为比赛努力工作,那么一旦比赛开始后你如何努力都不会有好的收获,你会始终落后无法赢得比赛。同样,通过正确的方式开始使用 MySQL 也是非常重要的:正确的编译 MySQL。当然你会根据安装需要使用特定的配置参数,不过下面几个要点对你会有些帮助的:

  • 首先,静态编译(--static)。这需要更多的磁盘空间,不过会运行的更快(linux 13%,MySQL 提供的数据)

  • 可能的情况下使用更好的优化级别(gcc 使用 -O3)

  • 使用 --without-debug 选项,这会提高 20-35% 的运行速度

  • 使用 --fomit-frame-pointer 选项,这会提高 1-4% 的运行速度

  • 编译时只包含你需要使用的字符集,比如:--with-extra- charsets=none

下面是一个示例配置,它工作的很好:

CFLAGS="-mcpu=i686 -march=i686 -O3 -pipe -fomit-frame-pointer" \
./configure \
        --prefix=/usr/local/build/mysql \
        --with-mysqld-user=mysql \
        --without-debug \
        --with-client-ldflags=-all-static \
        --with-mysqld-ldflags=-all-static \
        --disable-shared \
        --localstatedir=/usr/local/build/mysql/var \
        --with-extra-charsets=none \
        --enable-assembler

注意,如果你使用的是奔腾处理器,使用 pgcc 看上去会比 gcc 更好,尽管在 AMD 平台上使用 pgcc 会存在一些问题。要查看所有 gcc 选项,执行:

man gcc

安装前优化编译过程所花费的时间是值得的,毕竟,基本上你仅需要做一次,不过如果你在编译出了错误对你的服务器是非常不好的(在服务器已经安装运行的情况下重新编译它可不是什么好玩的事情)

MySQLD 变量

你已经为获得更优化的速度重新编译了 MySQL,并且你知道它就是为速度而构建的。不过就像需要巨大的努力完成 F1 赛车比赛一样(燃料、轮胎和赛手均要发挥正常),因此你可以为更快的速度调整你的数据库服务器。很多新的 MySQL 用户不知道可以调整服务器参数,他们为业务配置很小的数据库,然后数据库开始越来越忙,最后他们不得不增加越来越多的内存,不过这都是徒劳的,因为服务器 不会使用额外的内存。现在是介绍 mysqld 变量的时候了,你可以使用下面命令查看这些变量:

  1. shell

    mysqladmin variables
  2. 在 MySQL 命令行中

    SHOW VARIABLES

你可以到类似于下面的信息,对于未调优的系统,大部分变量可能与下面的类似:

+---------------------------------+-----------------------------------------------------------------
------------------------------------------------------------------------------------------+
| Variable_name                   | Value
                                                                                          |
+---------------------------------+-----------------------------------------------------------------
------------------------------------------------------------------------------------------+
| ansi_mode                       | OFF
                                                                                          |
| back_log                        | 50
                                                                                          |
| basedir                         | /usr/local/build/mysql/
                                                                                          |
| binlog_cache_size               | 32768
                                                                                          |
| character_set                   | latin1
                                                                                          |
| character_sets                  | latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 dani
sh hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 |
| concurrent_insert               | ON
                                                                                          |
| connect_timeout                 | 5
                                                                                          |
| datadir                         | /usr/local/build/mysql/database/
                                                                                          |
| delay_key_write                 | ON
                                                                                          |
| delayed_insert_limit            | 100
                                                                                          |
| delayed_insert_timeout          | 300
                                                                                          |
| delayed_queue_size              | 1000
                                                                                          |
| flush                           | OFF
                                                                                          |
| flush_time                      | 0
                                                                                          |
| have_bdb                        | NO
                                                                                          |
| have_gemini                     | NO
                                                                                          |
| have_innodb                     | NO
                                                                                          |
| have_isam                       | YES
                                                                                          |
| have_raid                       | NO
                                                                                          |
| have_ssl                        | NO
                                                                                          |
| init_file                       |
                                                                                          |
| interactive_timeout             | 28800
                                                                                          |
| join_buffer_size                | 131072
                                                                                          |
| key_buffer_size                 | 8388600
                                                                                          |
| language                        | /usr/local/build/mysql/share/mysql/english/
                                                                                          |
| large_files_support             | ON
                                                                                          |
| log                             | OFF
                                                                                          |
| log_update                      | OFF
                                                                                          |
| log_bin                         | OFF
                                                                                          |
| log_slave_updates               | OFF
                                                                                          |
| long_query_time                 | 10
                                                                                          |
| low_priority_updates            | OFF
                                                                                          |
| lower_case_table_names          | 0
                                                                                          |
| max_allowed_packet              | 1048576
                                                                                          |
| max_binlog_cache_size           | 4294967295
                                                                                          |
| max_binlog_size                 | 1073741824
                                                                                          |
| max_connections                 | 100
                                                                                          |
| max_connect_errors              | 10
                                                                                          |
| max_delayed_threads             | 20
                                                                                          |
| max_heap_table_size             | 16777216
                                                                                          |
| max_join_size                   | 4294967295
                                                                                          |
| max_sort_length                 | 1024
                                                                                          |
| max_user_connections            | 0
                                                                                          |
| max_tmp_tables                  | 32
                                                                                          |
| max_write_lock_count            | 4294967295
                                                                                          |
| myisam_recover_options          | OFF
                                                                                          |
| myisam_max_extra_sort_file_size | 256
                                                                                          |
| myisam_max_sort_file_size       | 2047
                                                                                          |
| myisam_sort_buffer_size         | 8388608
                                                                                          |
| net_buffer_length               | 16384
                                                                                          |
| net_read_timeout                | 30
                                                                                          |
| net_retry_count                 | 1000000
                                                                                          |
| net_write_timeout               | 60
                                                                                          |
| open_files_limit                | 0
                                                                                          |
| pid_file                        | /usr/local/build/mysql/database/newdev.domainname.co.za.pid
                                                                                          |
| port                            | 3306
                                                                                          |
| protocol_version                | 10
                                                                                          |
| record_buffer                   | 131072
                                                                                          |
| query_buffer_size               | 0
                                                                                          |
| safe_show_database              | OFF
                                                                                          |
| server_id                       | 0
                                                                                          |
| skip_locking                    | OFF
                                                                                          |
| skip_networking                 | OFF
                                                                                          |
| skip_show_database              | OFF
                                                                                          |
| slow_launch_time                | 2
                                                                                          |
| socket                          | /tmp/mysql.sock
                                                                                          |
| sort_buffer                     | 2097144
                                                                                          |
| table_cache                     | 64
                                                                                          |
| table_type                      | MYISAM
                                                                                          |
| thread_cache_size               | 0
                                                                                          |
| thread_stack                    | 65536
                                                                                          |
| transaction_isolation           | READ-COMMITTED
                                                                                          |
| timezone                        | SAST
                                                                                          |
| tmp_table_size                  | 33554432
                                                                                          |
| tmpdir                          | /var/tmp/
                                                                                          |
| version                         | 3.23.39
                                                                                          |
| wait_timeout                    | 28800
                                                                                          |
+---------------------------------+-----------------------------------------------------------------
    ------------------------------------------------------------------------------------------+

结合查看变量,你可能需要查看这些变量在系统中当前的值,可以使用下面命令:

  1. shell

    mysqladmin extended-status
  2. MySQL 命令行

    SHOW STATUS

输出类似于下面列表:

+--------------------------+----------+
| Aborted_clients          | 142      |
| Aborted_connects         | 5        |
| Bytes_received           | 9005619  |
| Bytes_sent               | 15444786 |
| Connections              | 794      |
| Created_tmp_disk_tables  | 1        |
| Created_tmp_tables       | 716      |
| Created_tmp_files        | 0        |
| Delayed_insert_threads   | 0        |
| Delayed_writes           | 0        |
| Delayed_errors           | 0        |
| Flush_commands           | 1        |
| Handler_delete           | 27       |
| Handler_read_first       | 1534     |
| Handler_read_key         | 608840   |
| Handler_read_next        | 652228   |
| Handler_read_prev        | 164      |
| Handler_read_rnd         | 14143    |
| Handler_read_rnd_next    | 1133372  |
| Handler_update           | 90       |
| Handler_write            | 131624   |
| Key_blocks_used          | 6682     |
| Key_read_requests        | 2745899  |
| Key_reads                | 6026     |
| Key_write_requests       | 63925    |
| Key_writes               | 63790    |
| Max_used_connections     | 20       |
| Not_flushed_key_blocks   | 0        |
| Not_flushed_delayed_rows | 0        |
| Open_tables              | 64       |
| Open_files               | 128      |
| Open_streams             | 0        |
| Opened_tables            | 517      |
| Questions                | 118245   |
| Select_full_join         | 0        |
| Select_full_range_join   | 0        |
| Select_range             | 2300     |
| Select_range_check       | 0        |
| Select_scan              | 642      |
| Slave_running            | OFF      |
| Slave_open_temp_tables   | 0        |
| Slow_launch_threads      | 0        |
| Slow_queries             | 8        |
| Sort_merge_passes        | 0        |
| Sort_range               | 3582     |
| Sort_rows                | 16287    |
| Sort_scan                | 806      |
| Table_locks_immediate    | 82957    |
| Table_locks_waited       | 2        |
| Threads_cached           | 0        |
| Threads_created          | 793      |
| Threads_connected        | 1        |
| Threads_running          | 1        |
| Uptime                   | 1662790  |
+--------------------------+----------+

my.cnf

幸运的是,MySQL 为这些变量提供给了适合大部分系

本文链接:http://www.4byte.cn/learning/37783/you-hua-mysqld-ying-jian-yi-ji-mysqld-pei-zhi-bian-liang.html