服務項目:網站建設、仿站、程序開發、APP開發設計、移動網站開發設計、企業網站設計、電子商務網站開發、網站維護、網站推廣、UX/UI 、HTML5、CSS3、JS / Jquery ...
          四川浚浚科技有限公司
          四川浚浚科技有限公司 (開發設計官網)TEL : 15308000360 / QQ : 38585404
          深入挖掘用戶需求
          成就品質用戶體驗

          您的位置:首頁 > 技術經驗 > 數據庫 > 正文

          MariaDB/MySQL配置文件參數優化
          技術支持服務電話:15308000360 【7x24提供運維服務,解決各類系統/軟硬件疑難技術問題】

          mariadb數據庫優化需要根據自己業務需求以及根據硬件配置來進行參數優化,下面是一些關于mariadb數據庫參數優化的配置文件。 如下為128G內存32線程處理器的MySQL/MariaDB配置參數優化:

          [client]
          #password= your_password
          port= 3306         
          socket= /tmp/mysql.sock
          !includedir /opt/local/mysql/wsrep
          # The MySQL server
          [mysqld]
          port= 3306
          socket= /tmp/mysql.sock
          basedir = /opt/local/mysql
          datadir=/opt/local/mysql/data                   #數據庫存放目錄
          relay-log=/opt/local/mysql/relaylog/s74-relay-bin                         
          pid-file = /opt/local/mysql/mysql.pid
          log-error = /opt/local/mysql/logs/mysqld.log
          open_files_limit = 65535                        #
          #skip-locking
          skip-external-locking                           #跳過外部鎖定
          back_log=3000                                   #暫存的連接數量  
          skip-name-resolve                               #關閉mysql的dns反查功能
          memlock                                         #將mysqld 進程鎖定在內存中
          lower_case_table_names = 1
          #query_response_time_stats=1
          #core-file
          #core-file-size = unlimited
          query_cache_type=1                              #查詢緩存  (0 = off、1 = on、2 = demand)
          performance_schema=0                            #收集數據庫服務器性能參數
          net_read_timeout=3600                           #連接繁忙階段(query)起作用
          net_write_timeout=3600                          #連接繁忙階段(query)起作用
          key_buffer_size = 32M                           #設置索引塊緩存大小
          max_allowed_packet = 128M                       #通信緩沖大小
          table_open_cache = 1024                         #table高速緩存的數量
          sort_buffer_size = 12M                          #每個connection(session)第一次需要使用這個buffer的時候,一次性分配設置的內存
          read_buffer_size = 8M                           #順序讀取數據緩沖區使用內存
          #sort_buffer_size = 32M
          #read_buffer_size = 32M
          read_rnd_buffer_size = 32M                      #隨機讀取數據緩沖區使用內存
          myisam_sort_buffer_size = 32M                   #MyISAM表發生變化時重新排序所需的緩沖
          thread_cache_size = 120                         #重新利用保存在緩存中線程的數量
          query_cache_size = 64M
          join_buffer_size = 8M                           #Join操作使用內存
          bulk_insert_buffer_size = 32M                   #批量插入數據緩存大小
          delay_key_write=ON                              #在表關閉之前,將對表的update操作指跟新數據到磁盤,而不更新索引到磁盤,把對索引的更改記錄在內存。這樣MyISAM表可以使索引更新更快。在關閉表的時候一起更新索引到磁盤
          delayed_insert_limit=4000
          delayed_insert_timeout=600
          delayed_queue_size=4000
          # Try number of CPU's*2 for thread_concurrency
          # The variable only affects Solaris!
          thread_concurrency = 64                         #CPU核數 * 2
          max_connections=1500                            #最大連接(用戶)數。每個連接MySQL的用戶均算作一個連接
          max_connect_errors=30                           #最大失敗連接限制
          interactive_timeout=600                         #服務器關閉交互式連接前等待活動的秒數
          wait_timeout=3600                               #服務器關閉非交互連接之前等待活動的秒數
          slow_query_log                                  #慢查詢記錄日志
          long_query_time = 0.1                           #慢查詢記錄時間  0.1秒
          slow_query_log_file=/opt/local/mysql/logs/slow_query.log            #慢查詢日志路徑
          #log_slow_verbosity=full
          log_slow_verbosity=query_plan                   #
          # Replication Master Server (default)
          # binary logging is required for replication
          log-bin=/opt/local/mysql/binlog/mysql-bin                               #binlog 名稱
          log-slave-updates                               #從master取得并執行的二進制日志寫入自己的二進制日志文件中
          replicate-ignore-db=mysql                       #不同步的表
          # binary logging format - mixed recommended
          binlog_format=row                               #binlog 格式 分別為 row=行格式 丶 mixed=混合格式 丶 STATEMENT=SQL語句復制模式
          event_scheduler=1                               #計劃任務 事件調度器
          # required unique id between 1 and 2^32 - 1
          # defaults to 1 if master-host is not set
          # but will not function as a master if omitted
          server-id= 1                                 #
          # Point the following paths to different dedicated disks
          #tmpdir= /tmp/
          #log-update = /path-to-dedicated-directory/hostname
          # Uncomment the following if you are using InnoDB tables
          #innodb_data_home_dir = /data/mysql-5.1.48/mysql-data/
          #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
          #innodb_log_group_home_dir = /data/mysql-5.1.48/mysql-data/
          # You can set .._buffer_pool_size up to 50 - 80 %
          # of RAM but beware of setting memory usage too high
          innodb_file_format=barracuda
          innodb_file_format_max=barracuda
          innodb_file_per_table=1
          innodb_fast_shutdown=0
          innodb_buffer_pool_size = 90000M
          innodb_buffer_pool_instances = 4
          #innodb_additional_mem_pool_size = 20M
          #innodb_use_sys_malloc = 20M
          # Set .._log_file_size to 25 % of buffer pool size
          innodb_log_file_size = 512M
          #innodb_log_buffer_size = 8M
          innodb_log_files_in_group = 3
          innodb_flush_log_at_trx_commit = 2
          innodb_lock_wait_timeout = 3 
          innodb_rollback_on_timeout = on
          innodb_flush_method=O_DIRECT
          transaction-isolation=READ-COMMITTED
          innodb_thread_concurrency=0
          innodb_io_capacity=800
          innodb_purge_threads=1
          innodb_open_files=65535
          #innodb_stats_update_need_lock=0
          #innodb_flush_neighbor_pages=0 
          #innodb_aio_pending_ios_per_thread=256
          #for binlog_format=row
          innodb_autoinc_lock_mode=2
          #innodb_fast_checksum = 1
          innodb_read_io_threads = 8
          innodb_write_io_threads = 12
          innodb_stats_on_metadata = 0
          #使用線程池處理連接
          thread_handling=pool-of-threads
          thread_pool_oversubscribe=30
          thread_pool_size=64
          thread_pool_idle_timeout=7200
          thread_pool_max_threads=2000
          #查詢優化器開關
          #optimizer_switch='index_condition_pushdown=on'
          #optimizer_switch='mrr=on'
          #optimizer_switch='mrr_sort_keys=on'
          #optimizer_switch='mrr_cost_based=off'
          #mrr_buffer_size=32M
          #optimizer_switch='join_cache_incremental=on'
          #optimizer_switch='join_cache_hashed=on'
          #optimizer_switch='join_cache_bka=on'
          #join_cache_level=4
          #join_buffer_size=32M
          #join_buffer_space_limit=32M
          [mysqldump]
          quick
          max_allowed_packet = 16M
          [mysql]
          no-auto-rehash
          # Remove the next comment character if you are not familiar with SQL
          #safe-updates
          [myisamchk]
          key_buffer_size = 256M
          sort_buffer_size = 256M
          read_buffer = 2M
          write_buffer = 2M
          [isamchk]
          key_buffer_size = 256M
          sort_buffer_size = 256M
          read_buffer = 2M
          write_buffer = 2M
          [mysqlhotcopy]
          interactive-timeout
          

          如下為256G內存64線程處理器的MySQL/MariaDB配置參數優化:
          [client]
          #password= your_password
          port= 3306         
          socket= /tmp/mysql.sock
          !includedir /opt/local/mysql/wsrep
          # The MySQL server
          [mysqld]
          port= 3306
          socket= /tmp/mysql.sock
          basedir = /opt/local/mysql
          datadir=/opt/local/mysql/data                   #數據庫存放目錄
          relay-log=/opt/local/mysql/relaylog/s74-relay-bin                         
          pid-file = /opt/local/mysql/mysql.pid
          log-error = /opt/local/mysql/logs/mysqld.log
          open_files_limit = 65535                        #
          #skip-locking
          skip-external-locking                           #跳過外部鎖定
          back_log=3000                                   #暫存的連接數量  
          skip-name-resolve                               #關閉mysql的dns反查功能
          memlock                                         #將mysqld 進程鎖定在內存中
          lower_case_table_names = 1
          #query_response_time_stats=1
          #core-file
          #core-file-size = unlimited
          query_cache_type=1                              #查詢緩存  (0 = off、1 = on、2 = demand)
          performance_schema=0                            #收集數據庫服務器性能參數
          net_read_timeout=3600                           #連接繁忙階段(query)起作用
          net_write_timeout=3600                          #連接繁忙階段(query)起作用
          key_buffer_size = 32M                           #設置索引塊緩存大小
          max_allowed_packet = 128M                       #通信緩沖大小
          table_open_cache = 1024                         #table高速緩存的數量
          sort_buffer_size = 12M                          #每個connection(session)第一次需要使用這個buffer的時候,一次性分配設置的內存
          read_buffer_size = 8M                           #順序讀取數據緩沖區使用內存
          #sort_buffer_size = 32M
          #read_buffer_size = 32M
          read_rnd_buffer_size = 32M                      #隨機讀取數據緩沖區使用內存
          myisam_sort_buffer_size = 32M                   #MyISAM表發生變化時重新排序所需的緩沖
          thread_cache_size = 120                         #重新利用保存在緩存中線程的數量
          query_cache_size = 64M
          join_buffer_size = 8M                           #Join操作使用內存
          bulk_insert_buffer_size = 32M                   #批量插入數據緩存大小
          delay_key_write=ON                              #在表關閉之前,將對表的update操作指跟新數據到磁盤,而不更新索引到磁盤,把對索引的更改記錄在內存。這樣MyISAM表可以使索引更新更快。在關閉表的時候一起更新索引到磁盤
          delayed_insert_limit=4000
          delayed_insert_timeout=600
          delayed_queue_size=4000
          # Try number of CPU's*2 for thread_concurrency
          # The variable only affects Solaris!
          thread_concurrency = 96                          #CPU核數 * 2
          max_connections=1500                            #最大連接(用戶)數。每個連接MySQL的用戶均算作一個連接
          max_connect_errors=30                           #最大失敗連接限制
          interactive_timeout=600                         #服務器關閉交互式連接前等待活動的秒數
          wait_timeout=3600                               #服務器關閉非交互連接之前等待活動的秒數
          slow_query_log                                  #慢查詢記錄日志
          long_query_time = 0.1                           #慢查詢記錄時間  0.1秒
          slow_query_log_file=/opt/local/mysql/logs/slow_query.log            #慢查詢日志路徑
          #log_slow_verbosity=full
          log_slow_verbosity=query_plan                   #
          # Replication Master Server (default)
          # binary logging is required for replication
          log-bin=/opt/local/mysql/binlog/mysql-bin                               #binlog 名稱
          log-slave-updates                               #從master取得并執行的二進制日志寫入自己的二進制日志文件中
          replicate-ignore-db=mysql                       #不同步的表
          # binary logging format - mixed recommended
          binlog_format=row                               #binlog 格式 分別為 row=行格式 丶 mixed=混合格式 丶 STATEMENT=SQL語句復制模式
          event_scheduler=1                               #計劃任務 事件調度器
          # required unique id between 1 and 2^32 - 1
          # defaults to 1 if master-host is not set
          # but will not function as a master if omitted
          server-id= 1                                 #
          # Point the following paths to different dedicated disks
          #tmpdir= /tmp/
          #log-update = /path-to-dedicated-directory/hostname
          # Uncomment the following if you are using InnoDB tables
          #innodb_data_home_dir = /data/mysql-5.1.48/mysql-data/
          #innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
          #innodb_log_group_home_dir = /data/mysql-5.1.48/mysql-data/
          # You can set .._buffer_pool_size up to 50 - 80 %
          # of RAM but beware of setting memory usage too high
          innodb_file_format=barracuda
          innodb_file_format_max=barracuda
          innodb_file_per_table=1
          innodb_fast_shutdown=0
          innodb_buffer_pool_size = 180000M
          innodb_buffer_pool_instances = 4
          #innodb_additional_mem_pool_size = 20M
          #innodb_use_sys_malloc = 20M
          # Set .._log_file_size to 25 % of buffer pool size
          innodb_log_file_size = 512M
          #innodb_log_buffer_size = 8M
          innodb_log_files_in_group = 3
          innodb_flush_log_at_trx_commit = 2
          innodb_lock_wait_timeout = 3 
          innodb_rollback_on_timeout = on
          innodb_flush_method=O_DIRECT
          transaction-isolation=READ-COMMITTED
          innodb_thread_concurrency=0
          innodb_io_capacity=800
          innodb_purge_threads=1
          innodb_open_files=65535
          #innodb_stats_update_need_lock=0
          #innodb_flush_neighbor_pages=0 
          #innodb_aio_pending_ios_per_thread=256
          #for binlog_format=row
          innodb_autoinc_lock_mode=2
          #innodb_fast_checksum = 1
          innodb_read_io_threads = 8
          innodb_write_io_threads = 12
          innodb_stats_on_metadata = 0
          #使用線程池處理連接
          thread_handling=pool-of-threads
          thread_pool_oversubscribe=30
          thread_pool_size=64
          thread_pool_idle_timeout=7200
          thread_pool_max_threads=2000
          #查詢優化器開關
          #optimizer_switch='index_condition_pushdown=on'
          #optimizer_switch='mrr=on'
          #optimizer_switch='mrr_sort_keys=on'
          #optimizer_switch='mrr_cost_based=off'
          #mrr_buffer_size=32M
          #optimizer_switch='join_cache_incremental=on'
          #optimizer_switch='join_cache_hashed=on'
          #optimizer_switch='join_cache_bka=on'
          #join_cache_level=4
          #join_buffer_size=32M
          #join_buffer_space_limit=32M
          [mysqldump]
          quick
          max_allowed_packet = 16M
          [mysql]
          no-auto-rehash
          # Remove the next comment character if you are not familiar with SQL
          #safe-updates
          [myisamchk]
          key_buffer_size = 256M
          sort_buffer_size = 256M
          read_buffer = 2M
          write_buffer = 2M
          [isamchk]
          key_buffer_size = 256M
          sort_buffer_size = 256M
          read_buffer = 2M
          write_buffer = 2M
          [mysqlhotcopy]
          interactive-timeout
          



          上一篇:InnoDB 引擎獨立表空間 innodb_file_per_table
          下一篇:Linux環境CentOS中源碼編譯安裝PostgreSQL

          相關熱詞搜索:數據庫優化 mysql mariadb
          主站蜘蛛池模板: 中文字幕无码不卡一区二区三区| 亲密爱人免费观看完整版| 五月婷婷婷婷婷| 粉色视频在线播放| 国产毛片哪里有| √天堂资源中文官网bt| 日韩国产精品99久久久久久| 亚洲精品无码人妻无码| 老司机天堂影院| 国产片免费在线观看| china同性基友gay勾外卖| 日本一道dvd在线播放| 亚洲国产精品尤物yw在线观看 | 亚洲精品乱码久久久久久不卡| 草莓视频在线免费观看下载| 国产精品二区在线| eeuss影院在线观看| 无码aⅴ精品一区二区三区| 亚洲AV无码乱码国产精品| 欧美重口绿帽video| 全彩acg★无翼乌火影忍者| 青青操免费在线观看| 国产精品v欧美精品∨日韩| 99久久无色码中文字幕| 宝宝你里面好烫很软不想出来| 久久久久久a亚洲欧洲AV冫| 欧美.成人.综合在线| 亚洲精品午夜久久久伊人| 精品久久8X国产免费观看| 国产a级特黄的片子视频| 麻豆视频免费观看| 国产精品一二三区| 91极品在线观看| 天天拍天天干天天操| 一级毛片特级毛片黄毛片| 日本一区二区三区在线视频观看免费| 亚洲av日韩av天堂影片精品| 波多野结衣波多野结衣| 免费欧洲毛片**老妇女| 美女扒开内裤无遮挡网站| 国产伦精品一区二区三区|