V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
Admstor
V2EX  ›  MySQL

一个 my.cnf 请高人看看有没有问题

  •  1
     
  •   Admstor · 2014-05-30 14:14:40 +08:00 · 3500 次点击
    这是一个创建于 3860 天前的主题,其中的信息可能已经有所发展或是发生改变。
    # The following options will be passed to all MySQL clients
    [client]
    #password = your_password
    port = 3306
    socket = /tmp/mysql.sock

    # Here follows entries for some specific programs

    # The MySQL server
    [mysqld]
    port = 3306
    socket = /tmp/mysql.sock
    datadir = /home/mysqldata/
    skip-locking
    key_buffer_size = 768M
    max_allowed_packet = 999M
    table_open_cache = 512
    sort_buffer_size = 4M
    read_buffer_size = 4M
    read_rnd_buffer_size = 8M
    myisam_sort_buffer_size = 64M
    thread_cache_size = 64
    query_cache_size = 64M
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency = 8
    max_connections = 10000

    interactive_timeout = 10000
    wait_timeout = 10000

    log_slow_queries = ON
    log_slow_queries=slow-log
    long_query_time=5

    log-queries-not-using-indexes


    ########
    skip-locking
    skip-name-resolve

    # Don't listen on a TCP/IP port at all. This can be a security enhancement,
    # if all processes that need to connect to mysqld run on the same host.
    # All interaction with mysqld must be made via Unix sockets or named pipes.
    # Note that using this option without enabling named pipes on Windows
    # (via the "enable-named-pipe" option) will render mysqld useless!
    #
    #skip-networking

    # Replication Master Server (default)
    # binary logging is required for replication
    log-bin=mysql-bin

    # required unique id between 1 and 2^32 - 1
    # defaults to 1 if master-host is not set
    # but will not function as a master if omitted
    server-id = 1
    log-bin=mysql-bin


    # Uncomment the following if you are using InnoDB tables
    #innodb_data_home_dir = /usr/local/mysql/var
    #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
    #innodb_log_group_home_dir = /usr/local/mysql/var
    # You can set .._buffer_pool_size up to 50 - 80 %
    # of RAM but beware of setting memory usage too high
    #innodb_buffer_pool_size = 384M
    #innodb_additional_mem_pool_size = 20M
    # Set .._log_file_size to 25 % of buffer pool size
    #innodb_log_file_size = 100M
    #innodb_log_buffer_size = 8M
    #innodb_flush_log_at_trx_commit = 1
    #innodb_lock_wait_timeout = 50

    [mysqldump]
    quick
    max_allowed_packet = 999M

    [mysql]
    no-auto-rehash
    # Remove the next comment character if you are not familiar with SQL
    #safe-updates

    [myisamchk]
    key_buffer_size = 768M
    sort_buffer_size = 256M
    read_buffer = 2M
    write_buffer = 2M

    [mysqlhotcopy]
    interactive-timeout
    12 条回复    2014-05-31 18:31:20 +08:00
    Admstor
        1
    Admstor  
    OP
       2014-05-30 14:25:42 +08:00
    主要是一个门户网站,客户总是反应数据库慢慢慢,有时候查看的确mysql占用CPU高达400%
    目前CPU是4核,内存32G
    系统为centos 6.3 64位

    我是运维方面,代码我管不到也不会去管...
    所以即便从不少结果看,100%是代码的问题,例如,开启后1小时不到
    Slow_queries我设置3秒,结果大概十几分钟就有2.3K记录
    Handler_read_rnd_next 151.8M
    Handler_read_rnd 1.2M

    请教的是,如果尽可能的提高服务器的负载水平,以及尽可能在这种情况下,保证不崩溃
    minbaby
        2
    minbaby  
       2014-05-30 14:39:57 +08:00
    看慢查询,然后分析,然后告诉开发人员,哪些sql查询有问题,让他们改,
    towser
        3
    towser  
       2014-05-30 14:44:52 +08:00
    “Slow_queries我设置3秒,结果大概十几分钟就有2.3K记录”。
    还不改程序更待何时?
    shiny
        4
    shiny  
       2014-05-30 14:46:15 +08:00
    通过 my.cnf 优化提升的空间是有限的,最好还是分析 slow queries 然后检查语句、数据库结构、索引。要不就是硬件层次的改善,提升硬盘 IO 性能、加内存。
    akira
        5
    akira  
       2014-05-30 15:20:49 +08:00
    分析慢查询吧,这种情况优化sql带来的效果更明显
    Admstor
        6
    Admstor  
    OP
       2014-05-30 15:24:53 +08:00
    这个其实我也是这么和客户说的,但是你们懂的......
    加上我对mysql的优化也只是浅浅的了解,所以想请高人看看这个配置文件可有什么不妥当的地方
    Slow_queries其实也挺奇怪,里面有些语句我单独执行的时候也是很快

    很多时候发现CPU占用极高的,用show processlist查看,会有大量的sleep和query
    感觉像是表锁了
    dbow
        7
    dbow  
       2014-05-30 15:52:06 +08:00
    @Admstor myisam 还是innodb?
    Admstor
        8
    Admstor  
    OP
       2014-05-30 16:16:22 +08:00
    @dbow 基本都是myisam
    feuvan
        9
    feuvan  
       2014-05-30 16:29:20 +08:00
    锁表的操作太多了吧。。
    dbow
        10
    dbow  
       2014-05-30 16:34:20 +08:00
    @Admstor explain一下那些慢查询,看看扫描的rows是不是巨大, 看看有没有filesort和tmp table, 看看查询用的什么索引.
    simapple
        11
    simapple  
       2014-05-30 17:15:22 +08:00
    多大的数据量,看索引,索引是多大,能不能再优化索引,服务器内存还可以,把索引做好,全部加载到内存去,你开启了慢查询就按个优化吧
    Admstor
        12
    Admstor  
    OP
       2014-05-31 18:31:20 +08:00
    数据库1G多点...
    谢谢各位我去看看
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2965 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 24ms · UTC 14:18 · PVG 22:18 · LAX 06:18 · JFK 09:18
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.