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
pppguest3962
V2EX  ›  MySQL

请大家评点一下这个老物理机在虚拟机上跑 MySQL 优化的配置参数,谢谢~

  •  
  •   pppguest3962 · 2020-10-07 10:25:59 +08:00 · 3020 次点击
    这是一个创建于 1500 天前的主题,其中的信息可能已经有所发展或是发生改变。

    物理机器性能:
    机器的物理 CPU 是 E5,专门指定分了 2 个核给这台虚拟机
    专门分配一个物理网口给虚拟机独享
    内存:4G 独享
    系统 CentOS 6.8
    MySQL 版本是 5.1.73
    磁盘 I/O 是 120mb/s 读写的水平吧

    虚拟机只是拿来跑 MySQL 的,
    大概 40 多张表,最大的表有 4 千万,都建有“合理的”索引

    内网只有几台 PC 机使用它的服务,以前并发量平时都是 100 左右( MySQL 默认是 151,所以一点儿事都没有),
    最近更新了本地程序,现在 1 秒同时并发最大量到了 2000,有提示:Too many Connection 的字样,
    于是根据网上的知识点,和我这里的实际情况改了一下 CentOS 的内核参数和 MySQL 配置,

    # sysctl 配置
    net.ipv4.tcp_tw_reuse = 1
    net.ipv4.tcp_tw_recycle = 1
    fs.file-max=65535
    net.ipv4.ip_local_port_range = 1024 65535
    net.ipv4.tcp_fin_timeout = 30
    net.ipv4.tcp_max_tw_buckets = 800
    net.ipv4.tcp_max_syn_backlog = 819200
    net.ipv4.tcp_keepalive_time = 120
    net.ipv4.tcp_keepalive_intvl = 15
    net.ipv4.tcp_keepalive_probes = 3
    net.core.netdev_max_backlog = 500000
    net.core.somaxconn = 65536
    
    # my.ini 配置
    max_tmp_tables = 64
    max_allowed_packet = 32M
    max_connect_errors = 8000 
    table_cache = 614
    wait_timeout = 10
    interactive_timeout = 10
    max_connections=4096
    back_log=600
    wait_timeout=13
    key_buffer_size = 128M
    query_cache_size = 32M
    read_buffer_size =16M
    read_rnd_buffer_size = 16M
    sort_buffer_size = 16M
    read_rnd_buffer_size = 16M
    thread_cache_size = 16
    thread_concurrency = 8
    open-files-limit = 10240
    

    全部表的引擎都是 Myisam,程序操作没有事务的操作,对数据库的操作就是 INSERT/SELECT/UPDATE,
    语句连 mysql 的函数运算都没有用到

    程序上的所有连接都是单独的短连接,程序里没有长连接

    优化的的期望是,除了增大接受的并发量,对于大量同时的 INSERT (同时 600 个短链接的过来),MySQL 是否可以先缓冲,然后慢慢写到表里?
    老机器,I/O 确实不怎么样

    不知道适合吗?

    7 条回复    2020-10-07 13:34:14 +08:00
    opengps
        1
    opengps  
       2020-10-07 10:44:04 +08:00 via Android
    虚拟机的硬盘 io 损失很严重的,我的 gps 表单行 1k,物理硬盘可以 3000 ~ 4000,物理硬盘的虚拟机里只能 300 ~ 400
    wangritian
        2
    wangritian  
       2020-10-07 10:51:32 +08:00
    1.wait_timeout=13 如果应用代码的短连接没有正确关闭的话,mysql 会保留 13 秒,此时支持的最大并发数是 max_connections/(wait_timeout+1),急病乱投医可以改成 1 试试,但我觉得不健康
    2.并发量不小,继续用短连接,你的机器会变成握手狂魔
    3.myisam 插入数据似乎是表锁,读写并发性能会被 innodb 吊打吧,而且你的业务写入量大,索引多了反而会降低写入性能
    594duck
        3
    594duck  
       2020-10-07 11:29:56 +08:00
    写多还是读多,二个优化方向不一样

    短链接不好,用长链接,我们游戏服务器都是用的长连接,一般一个前端往后面开 10~30 个连接。长链接在有规划的高峰的时候更节省时间
    pppguest3962
        4
    pppguest3962  
    OP
       2020-10-07 11:50:29 +08:00
    @opengps 明白,我知道关于这个知识点的原因是,虽然同样是损耗,这个是 RAID 和随机读写等一串问题造成的,用 SSD 会好很多,不知道是不是正确的理解了。。。

    @wangritian 13 这个值也是逐步减下来到这个数的,并发写的时候,有时候会延迟好几秒才写完,10 秒的话,多数情况都写完了,所以才用 13 的。。。
    握手狂魔也没办法了,程序的逻辑没有办法共用一个长连接的对象。。。除非重构,但没啥动力去做这个事情。。。
    至于 myisam 还是 innodb,我回头把 SQL 全导出来,反正也就 10 几 GB 的量,改成 innodb 再倒进库里,跑几天看看。。。


    @594duck 写得多呢,90%是写,10%是读,INSERT/SELECT/UPDATE 之比应该是 80%/10%/10%
    这个 mysql 就像是数据仓库,说它冷数据呢,全是冷数据,要用的时候呢,全是热数据,唉
    594duck
        5
    594duck  
       2020-10-07 12:09:38 +08:00
    @pppguest3962

    写的多的话如果一直是增加写不做任何删改,是可以考虑主库不要索引的,二是关注 IO WAIT,三是一定要考虑读写分离,毕竟你到时候要读起来也凶。另外要考虑分表(减少数据被抽取和使用的时候压力)。

    不要索引 可以加快写,Zabbix 就是为写优化的(所以这鬼东西读起来要疯掉)
    aru
        6
    aru  
       2020-10-07 12:47:14 +08:00
    可以尝试 innodb,给 mysql 更多的内存
    换 ssd 应该就立竿见影了,不过涉及到采购不好说
    wangritian
        7
    wangritian  
       2020-10-07 13:34:14 +08:00
    @pppguest3962 wait_timeout 应该是指一个连接的任务完成且未主动关闭时,mysql 等待的超时时间,不是一句 sql 最大的执行时间,所以跟 insert 本身的时间无关,可以设置为 1 。我觉得应该先从写入性能优化了,然后才是参数和长连接。短连接不好改的话,建议找一个队列产品丢进去,然后用 go/py 写个消费者批量入库。引擎还是强烈建议 innodb,只有超大量读、极少写入的项目适合 myisam
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   5432 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 23ms · UTC 05:57 · PVG 13:57 · LAX 21:57 · JFK 00:57
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.