多线程优化mysql我应该使用哪些mysqltuner建议？

vlju58qv 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(310)

下午好。该站点位于vps（10 gb ram、2个cpu内核、65 gb ssd）上。我放了mysqltuner，得到了配置mysql的建议。我想澄清一下这些建议中的哪一个应该应用于获得性能。以及将mysql设置为参数的值是什么？

当前mysql设置

[client]
    port        = 3306
    socket      = /var/run/mysqld/mysqld.sock

    [mysqld_safe]
    log-error=/var/log/mysql.log
    pid-file    = /var/run/mysqld/mysqld.pid
    socket      = /var/run/mysqld/mysqld.sock
    nice        = 0

    [mysqld]
    local-infile=0
    user        = mysql
    pid-file    = /var/run/mysqld/mysqld.pid
    socket      = /var/run/mysqld/mysqld.sock
    port        = 3306
    basedir     = /usr
    datadir     = /var/lib/mysql
    tmpdir      = /tmp
    lc-messages-dir = /usr/share/mysql
    explicit_defaults_for_timestamp

    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    bind-address    = 127.0.0.1

    log-error   = /var/log/mysql/error.log

    # Recommended in standard MySQL setup
    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0

    # * IMPORTANT: Additional settings that can override those from this file!
    #   The files must end with '.cnf', otherwise they'll be ignored.
    #
    !includedir /etc/mysql/conf.d/
    innodb-buffer-pool-instances = 2
    innodb-buffer-pool-size = 2361393152
    innodb-flush-log-at-trx-commit = 2
    innodb-log-file-size = 524288000
    innodb-thread-sleep-delay = 0
    join-buffer-size = 8388608
    max-connections = 100
    max-heap-table-size = 268435456
    query-cache-limit = 2097152
    query-cache-size = 67108864
    query-cache-type = ON
    skip-name-resolve = TRUE
    sort-buffer-size = 8388608
    symbolic-links = FALSE
    tmp-table-size = 268435456

mysqltuner参考

>>  MySQLTuner 1.3.0 - Major Hayden 
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    [OK] Logged in using credentials from debian maintenance account.
    [OK] Currently running supported MySQL version 5.6.35
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in InnoDB tables: 2G (Tables: 326)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
    [--] Data in MyISAM tables: 46M (Tables: 5)
    [!!] Total fragmented tables: 20

    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned

    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 39d 21h 6m 2s (587M q [170.642 qps], 16M conn, TX: 1078B, RX: 358B)
    [--] Reads / Writes: 75% / 25%
    [--] Total buffers: 2.5G global + 16.6M per thread (100 max threads)
    [OK] Maximum possible memory usage: 4.2G (41% of installed RAM)
    [OK] Slow queries: 0% (1K/587M)
    [OK] Highest usage of available connections: 40% (40/100)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/32.6M
    [OK] Key buffer hit rate: 99.5% (136M cached / 677K reads)
    [OK] Query cache efficiency: 81.7% (395M cached / 484M selects)
    [!!] Query cache prunes per day: 1080995
    [OK] Sorts requiring temporary tables: 0% (473 temp sorts / 27M sorts)
    [!!] Joins performed without indexes: 22423559
    [!!] Temporary tables created on disk: 33% (8M on disk / 25M total)
    [OK] Thread cache hit rate: 99% (158K created / 16M connections)
    [OK] Table cache hit rate: 79% (985 open / 1K opened)
    [OK] Open file limit used: 0% (62/16K)
    [OK] Table locks acquired immediately: 99% (390M immediate / 390M locks)
    [OK] InnoDB buffer pool / data size: 2.2G/2.2G
    [!!] InnoDB log waits: 5
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        Temporary table size is already large - reduce result set size
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
        query_cache_size (> 64M)
        join_buffer_size (> 8.0M, or always use indexes with joins)
        innodb_log_buffer_size (>= 8M)

mysql multithreading

来源：https://stackoverflow.com/questions/50680294/tuning-mysql-which-mysqltuner-recommendations-should-i-use

2条答案

按热度按时间

70gysomp1#

初始可用信息中对my.cnf-ini[mysqld]的建议


# sort_buffer_size=8M  # lead with # for default size

# join_buffer_size=8M  # lead with # for default size

thread_cache_size=100  # from autocalc to reduce threads_created of 158K
innodb_buffer_pool_size=3G  # from ~2G  for growth and only 75% available for data
innodb_lru_scan_depth=512  # from 1024 to minimize CPU busy every second
key_buffer_size=64M  # from 8M - you have 32M MyISAM indexes
key_cache_age_threshold=84600  # from 300 seconds to reduce key_reads
query_cache_min_res_unit=512  # from 4096 to increase QC results capacity
innodb_log_buffer_size=16M  # from 8M suggested by Mysqltuner
table_open_cache=2K  # from 1K to reduce tables_opened count
innodb_purge_threads=4  # from 1 to keep up with activity
innodb_flushing_avg_loop=10  # from 30 to keep up with activity

要找到39天内2200万个没有索引的连接的研究太多了，不可忽视。解释选择…将帮助你找到他们。
请在时间允许的情况下公布所取得的进展。
如需更多帮助，请在我的个人资料中找到联系信息。

赞(0）回复(0）举报 2021-06-20

ddrv8njm2#

由于我们对主机上的其他组件对系统的要求一无所知，因此不可能对调整任何内存使用提出建议。
“在没有索引的情况下执行的连接”表明您存在模式问题。而且写的比例非常高，这表明你可能有更基本的问题（比如草率的orm）。你还没有设定好你的查询时间。尽管报告为slow的查询的比例很小，但考虑到机器正在处理的工作负载，这并不是一个非常现实的情况。您应该应用一个长时间的0查询，并收集一段时间的数据，以了解您的性能/容量问题真正喜欢的地方。

赞(0）回复(0）举报 2021-06-20

我来回答

多线程优化mysql我应该使用哪些mysqltuner建议？

2条答案

相关问题

热门标签

最新问答