• 1
  • 2
  • 3
  • 4
  • 5
mysql數據庫問題 首 頁  ?  幫助中心  »  數據庫  »  mysql數據庫問題
mysql的my.cnf
發布日期:2016-4-26 18:4:53

    一般在MySQL在安裝完畢之后,都要對my.cnf進行配置,以得到mysql性能最大化。

  一、關鍵參數

  通過我們的大宗期貨交易平臺性能,此配置整理出來,希望你只做一個參照。如圖1所示的代碼:

  二、innodb_flush_log_at_trx_commit

  Controls the balance between strict ACID compliance for commit operations, and higher performance

  that is possible when commit-related I/O operations are rearranged and done in batches. You can

  achieve better performance by changing the default value, but then you can lose up to a second of

  transactions in a crash.

  ? The default value of 1 is required for full ACID compliance. With this value, the contents of the InnoDB

  log buffer are written out to the log file at each transaction commit and the log file is flushed to disk.

  ? With a value of 0, the contents of the InnoDB log buffer are written to the log file approximately once

  per second and the log file is flushed to disk. No writes from the log buffer to the log file are performed

  at transaction commit. Once-per-second flushing is not 100% guaranteed to happen every second,

  due to process scheduling issues. Because the flush to disk operation only occurs approximately once

  per second, you can lose up to a second of transactions with any mysqld process crash.

  ? With a value of 2, the contents of the InnoDB log buffer are written to the log file after each transaction

  commit and the log file is flushed to disk approximately once per second. Once-per-second flushing

  is not 100% guaranteed to happen every second, due to process scheduling issues. Because the

  flush to disk operation only occurs approximately once per second.

  •   innodb_flush_log_at_trx_commit=1,innodb的緩存會在事務提交或每秒鐘時都會進行磁盤的刷新操作,默認值。
  •   innodb_flush_log_at_trx_commit=2,innodb緩存會在提交事務時寫入到事務日志但是不會刷新磁盤,然后在每秒鐘時進行磁盤刷新操作。
  •   innodb_flush_log_at_trx_commit=0,每秒鐘時緩存寫入日志,同時刷新磁盤。

  據我們的性能測試發現在innodb_flush_log_at_trx_commit=2,數據的讀寫速度和1的時候有明顯的提升。

  三、innodb_buffer_pool_size、innodb_buffer_pool_instances

  innodb_buffer_pool_size、innodb_buffer_pool_instances這兩個參數,你必須得看看這個mysql:提升性能的最關鍵參數

  四、binlog-format = MIXED

  這種二進制日志模式是statement和row模式的結合體,不過我們需要注意的是mysql 5.7中的默認格式是STATEMENT,這影響服務端代碼如Java對事務的處理,非常關鍵,如果沒有調試好的話,要導致事務不回滾。

  In MySQL 5.7, the default format is STATEMENT.

  You must have the SUPER privilege to set either the global or session binlog_format value.

  The rules governing when changes to this variable take effect and how long the effect lasts are the same as for other MySQL server system variables. See Section 13.7.4, “SET Syntax”, for more information.

  When MIXED is specified, statement-based replication is used, except for cases where only row-based replication is guaranteed to lead to proper results. For example, this happens when statements contain user-defined functions (UDF) or the UUID() function. An exception to this rule is that MIXED always uses statement-based replication for stored functions and triggers.

  五、innodb_lock_wait_timeout = 20

  事務回滾的時間間隔,定為20秒,這是我們項目認為比較合理的值,此外可參見Transactional與mysql究竟有什么關系。

  六、transaction-isolation = REPEATABLE-READ

  事務的隔離級別,也對多事務的讀寫也有著關鍵的作用,可參見高性能mysql的札記:事務。

 

什么行业的讲师最赚钱 网上赚钱软件排行榜正式 捕鱼大富翁停服了 股票流通市场 重庆快乐10分定位计划 意甲联赛哪里可以看 长沙麻将技巧十句口 平特一肖多少倍 网上打字赚钱 在线配资公司 广东快乐10分玩法