5d6d网 发表于 2018-10-3 09:36:23

mysql的优化参数

  以下为自己线上服务器my.cnf的配置,大家可以参考一下:
  服务器配置:ubuntu 8核 8G内存
  mysql版本: mysql-5.5
  
  default-character-set=utf8
  port= 3306
  socket= /var/run/mysqld/mysqld.sock
  
  socket= /var/run/mysqld/mysqld.sock
  nice= 0
  
  character-set-server=utf8
  init_connect='SET NAMES utf8'
  max_connections=1500
  max_connect_errors = 6000
  #wait_timeout=31536000
  #interactive_timeout=31536000
  wait_timeout=300
  interactive_timeout=300
  skip-external-locking
  skip-name-resolve
  skip-host-cache
  log-slow-queries=/var/lib/mysql/slowquery.log
  long_query_time=2
  thread_concurrency = 16
  concurrent_insert = 2
  innodb_additional_mem_pool_size = 16M
  innodb_buffer_pool_size = 4G
  innodb_data_file_path = ibdata1:1G:autoextend
  innodb_log_buffer_size = 16M
  innodb_log_file_size = 128M
  innodb_log_files_in_group = 2
  innodb_lock_wait_timeout=120
  innodb_thread_concurrency = 16
  innodb_file_io_threads = 4
  innodb_flush_log_at_trx_commit = 2
  innodb_log_files_in_group = 3
  innodb_max_dirty_pages_pct = 90
  innodb_file_per_table = 0
  transaction_isolation = READ-COMMITTED
  back_log = 600
  open_files_limit = 10240
  table_cache = 614
  max_heap_table_size = 256M
  tmp_table_size = 256M
  sort_buffer_size = 2M
  join_buffer_size = 2M
  read_buffer_size = 1M
  read_rnd_buffer_size = 16M
  bulk_insert_buffer_size = 64M
  key_buffer_size = 128M
  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
  max_allowed_packet = 32M
  thread_stack= 192K
  thread_cache_size = 300
  myisam-recover= BACKUP
  query_cache_limit = 4M
  query_cache_min_res_unit = 2k
  #query_cache_size = 64M
  query_cache_size = 0M
  log_error = /var/log/mysql/error.log
  binlog_cache_size = 4M
  max_binlog_cache_size = 8M
  expire_logs_days = 7
  max_binlog_size= 200M
  
  quick
  quote-names
  max_allowed_packet = 32M
  
  
  key_buffer= 16M
  !includedir /etc/mysql/conf.d/

页: [1]
查看完整版本: mysql的优化参数