Mysql
300 GB Innodb 的 MySQL 5.7 性能問題
我們有一個在 MysSQL 5.7 上執行的數據庫。數據庫大小約為 300 GB，執行在具有 144 GB RAM、16 個 CPU 和 15 GB 交換大小的專用 Linux 伺服器 (RHEL 6) 上。
伺服器一整天都很忙，至少有 50 個連接。大多數查詢都有索引並且每週優化一次。但是我們仍然面臨性能問題。
因此請求審查
my.cnf配置，並請提出任何更改。
my.cnf[mysqld] basedir=/usr datadir=/sql/mysql/data57 socket=/sql/mysql/data/mysql.sock skip-external-locking key_buffer_size = 4000M max_allowed_packet = 5120M table_open_cache = 4000 sort_buffer_size = 128M read_buffer_size =8M join_buffer_size=128M read_rnd_buffer_size = 16M myisam_sort_buffer_size = 128M thread_cache_size = 100 query_cache_size=0 query_cache_limit=4M query_cache_type="ON" query_cache_min_res_unit=20K query_prealloc_size=40K query_alloc_block_size=40K max_connections=300 sql_mode = "" interactive_timeout = 28800 wait_timeout = 7200 connect_timeout = 60 default_password_lifetime=0 old_passwords=2 lower_case_table_names=1 tmpdir=/tmpfs tmp_table_size=20G max_heap_table_size=170M innodb_buffer_pool_size=100G innodb_buffer_pool_instances=16 innodb_buffer_pool_chunk_size=6562M innodb_read_io_threads=64 innodb_write_io_threads=64 innodb_log_file_size=2G innodb_lock_wait_timeout=180 net_buffer_length=8K transaction-isolation=READ-COMMITTED innodb_lock_wait_timeout=180 federated log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [client] port=3306 socket=/sql/mysql/data/mysql.sock [mysql.server] user=mysql basedir=/usr log-bin=mysql-bin binlog_format=mixed server-id=1 [mysqldump] quick max_allowed_packet=1G [mysql] no-auto-rehash [myisamchk] key_buffer_size=1024M sort_buffer_size=256K read_buffer=8M write_buffer=8M [mysqlhotcopy] interactive-timeout詢問
SELECT year_no AS "Year", month_no AS "Month", area AS "Area", hq AS "Headquarters", region AS "Region", depot AS "Depot Name", aw AS "Dist Name", ss_channel AS "Channel", sales.classification AS "Classification", sales.beat AS "Beat", sales.salesman AS "Salesman", sales.cust AS "Retailer UID", Sum(qty) AS "Qty", Sum(amt) AS "Amount" FROM sales JOIN date_tbl ON dt = sales.dt JOIN or_areas ON area = sales.area JOIN hq ON hq = sales.hq JOIN or_regions ON region = sales.region JOIN depot ON depot = sales.depot JOIN or_aws ON aw = sales.aw JOIN prods ON sku = sales.sku JOIN prod_grp ON product_group = sales.product_group JOIN brand ON brand = sales.brand JOIN dsm ON ( salesman = sales.salesman ) JOIN cust ON retailer = sales.retailer WHERE ss_inv_status IN ( 1, 3, -3 ) AND ss_cust_name IS NOT NULL AND cal_date BETWEEN ? AND ? AND sales.zone IN ( ? ) AND sales.region IN ( ? ) AND sales.depot IN ( ? ) AND sales.zone IN ( ? ) AND sales.distributor_division = ? AND sales.brand IN ( ? ) GROUP BY year_no, month_no, aw, sales.cust, prod, cal_date;是
?佔位符，值作為輸入輸入。解釋
更多資訊
- 性能問題是非常大的查詢，需要 30 分鐘才能執行。
- 這些是每天生成的報告查詢。
- 它不是 DW 系統。
我認為您已經找到了一個“過度規範化”的範例，以及為什麼“規範化連續值”（例如日期）是不好的。
我建議您放棄
date_tbl並將日期拉入sales. 也擺脫單獨的列year_no和month_no; 而是根據需要計算它們。完成此操作後，其中一些可能有用：
INDEX(cal_date) INDEX(brand, cal_date) INDEX(zone, cal_date)這取決於
?可能是什麼。例如，如果brand經常是單個項目，那麼(brand, cal_date)很可能是最有用的。你測量後有任何改善
OPTIMIZE TABLE嗎？我對此表示懷疑。
my.cnf 值會加劇許多性能問題。考慮加入
$$ mysqld $$部分
innodb_change_buffer_size_max = 5 # from 25 to reduce set aside for I, D, U ops有了 100G 的 IBPoolSize，這個“預留”從 25G 下降到 5G，可用於插入、刪除、更新維護。其他 20% 將可用於池中的數據行。
考慮刪除此列表以允許預設設置為您工作：
key_buffer_size = 4000M max_allowed_packet = 5120M # with 144G RAM, /5G = 28 active connections = OOM sort_buffer_size = 128M read_buffer_size = 8M join_buffer_size = 128M read_rnd_buffer_size = 16M query_cache_min_res_unit=20K net_buffer_length=8K此外，由於您無意使用查詢記憶體，請添加
have_query_cache = 0 # to eliminate CPU overhead when this is Yes使用帶有目前 my.cfg 值的 mysqlcalculator.com 表明您需要大約 210G 的記憶體來支持您的 300 個計劃的 max_connections。
@veerendra kumar，你能做出這些改變嗎？有什麼改善嗎？