:::

9-1 優化MySQL

新方式

  1. 連至 https://tools.percona.com/wizard 並按照順序填寫






  2. 先備份原來的設定檔
    cd /etc/mysql/mariadb.conf.d/
    cp 50-server.cnf 50-server.cnf.bak

     

  3. 得到最後結果後,將之複製到底下設定檔
    vi /etc/mysql/mariadb.conf.d/50-server.cnf

     

  4. 修改其中的幾個設定儲存後重啟mysql(請自行根據主機狀況來調整):
    socket                         = /var/run/mysqld/mysqld.sock
    pid-file                       = /var/run/mysqld/mysqld.pid
    

    註解掉log-bin(否則會有大量log產生拖慢效能)

    # BINARY LOGGING #
    #log-bin                        = /var/lib/mysql/mysql-bin
    #expire-logs-days               = 14
    #sync-binlog                    = 1
    

    註解掉slow-query-log (否則可能會有超巨型log產生)

    # LOGGING #
    log-error                      = /var/lib/mysql/mysql-error.log
    log-queries-not-using-indexes  = 1
    #slow-query-log                 = 0
    #slow-query-log-file            = /var/lib/mysql/mysql-slow.log
    

     

手動方式:(不太建議)

  1. 下載檢測程式,並賦予執行權限
    cd ~
    wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
    chmod +x mysqltuner.pl
  2. 執行之:

    perl mysqltuner.pl --user root --pass '密碼'
  3. 建議:

    Variables to adjust:
        query_cache_size (=0)
        query_cache_type (=0)
        performance_schema = ON enable PFS
        innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
        innodb_buffer_pool_instances (=1)
    

     

  4. 開啟設定檔,針對紅色[!!] 的結果來做改善與調教:

    vi /etc/mysql/mariadb.conf.d/50-server.cnf
  5. 修改設定如:

    skip-name-resolve=1
    
    max_connections         = 300
    
    query_cache_limit       = 1M
    query_cache_size        = 0
    query_cache_type        = 0
    performance_schema      = ON
    
  6. 重啟資料庫服務即可。

  7. 查詢資料庫最大佔用記憶體語法:

    SELECT ( @@key_buffer_size
    + @@query_cache_size
    + @@innodb_buffer_pool_size
    + @@innodb_additional_mem_pool_size
    + @@innodb_log_buffer_size
    + @@max_connections * ( @@read_buffer_size
    + @@read_rnd_buffer_size
    + @@sort_buffer_size
    + @@join_buffer_size
    + @@binlog_cache_size
    + @@thread_stack
    + @@tmp_table_size )
    ) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;

     


:::

書籍目錄

展開 | 闔起

QR Code 區塊

http%3A%2F%2Fcampus-xoops.tn.edu.tw%2Fmodules%2Ftad_book3%2Fpage.php%3Ftbdsn%3D993

計數器

今天: 707707707
昨天: 2344234423442344
總計: 3297989329798932979893297989329798932979893297989