[my.cnf 값 질문] 가상코어 40 Ram128GB 480GB SSDx4 Raid 10

[my.cnf 값 질문] 가상코어 40 Ram128GB 480GB SSDx4 Raid 10

QA

[my.cnf 값 질문] 가상코어 40 Ram128GB 480GB SSDx4 Raid 10

본문

Nginx + MariaDB 10.3 이고 InnoDB 사용중입니다. 커뮤니티 사이트 운영 예정 제작 중에 있습니다.

 

나중에 모니터링 하더라도 my.cnf 값을 비워 놓을 수는 없을 것 같아서 기본으로 채워놓고

 

차후에 모니터링 하려 합니다. 커뮤니티 시작 단계라 볼때 아래값 정도면 적당한지 궁금합니다.

 

보완해야 할 값이 있다면 조언 좀 부탁드립니다. 미리 감사드립니다.

 

Select, Update, Insert 가 빈번할 것으로 예상하고 있습니다.

 

---------------------------------------------------------------------------------

[mysqld_safe]
  nice = -15
   
  [client]
  socket = /var/lib/mysql/mysql.sock
  default-character-set = utf8
   
  [mysqld]
  ## Charset and Collation
  character-set-server = utf8
  collation-server = utf8_general_ci
   
  ## Files
  back_log = 300
  open-files-limit = 8192
  open-files = 1024
  port = 3306
  socket = /var/lib/mysql/mysql.sock
  pid-file = /var/lib/mysql/mysql.pid
  skip-external-locking
  skip-name-resolve
   
  ## Logging
  datadir = /var/lib/mysql
  relay_log = mysql-relay-bin
  relay_log_index = mysql-relay-index
  #log = mysql-gen.log
  log_error = mysql-error.err
  log_warnings
  log_bin = mysql-bin
  log_slow_queries = mysql-slow.log
  #log_queries_not_using_indexes
  long_query_time = 10 #default: 10
  max_binlog_size = 256M #max size for binlog before rolling
  expire_logs_days = 4 #binlog files older than this will be purged
   
  ## Per-Thread Buffers * (max_connections) = total per-thread mem usage
  thread_stack = 512K #default: 32bit: 192K, 64bit: 256K
  sort_buffer_size = 2M #default: 2M, larger may cause perf issues
  read_buffer_size = 2M #default: 128K, change in increments of 4K
  read_rnd_buffer_size = 2M #default: 256K
  join_buffer_size = 2M #default: 128K
  binlog_cache_size = 128K #default: 32K, size of buffer to hold TX queries
  ## total per-thread buffer memory usage: 44160000K = 43.125GB
   
  ## Query Cache
  query_cache_size = 256M #global buffer
  query_cache_limit = 1M #max query result size to put in cache
   
  ## Connections
  max_connections = 10000 #multiplier for memory usage via per-thread buffers
  max_connect_errors = 100 #default: 10
  concurrent_insert = 2 #default: 1, 2: enable insert for all instances
  connect_timeout = 30 #default -5.1.22: 5, +5.1.22: 10
  max_allowed_packet = 32M #max size of incoming data to allow
   
  ## Default Table Settings
  sql_mode = NO_AUTO_CREATE_USER
   
  ## Table and TMP settings
  max_heap_table_size = 2G #recommend same size as tmp_table_size
  bulk_insert_buffer_size = 2G #recommend same size as tmp_table_size
  tmp_table_size = 2G #recommend 1G min
   
  ## Table cache settings
  #table_cache = 512 #5.0.x <default: 64>
  #table_open_cache = 512 #5.1.x, 5.5.x <default: 64>
  #tmpdir = /data/mysql-tmp0:/data/mysql-tmp1 #Recommend using RAMDISK for tmpdir
   
  ## Thread settings
  thread_concurrency = 16 #recommend 2x CPU cores
  thread_cache_size = 100 #recommend 5% of max_connections
   
  ## Replication
  #read_only
  #skip-slave-start
  #slave-skip-errors = <default: none, recommend:1062>
  #slave-net-timeout = <default: 3600>
  #slave-load-tmpdir = <location of slave tmpdir>
  #slave_transaction_retries = <default: 10>
  #server-id = <unique value>
  #replicate-same-server-id = <default: 0, recommend: 0, !if log_slave_updates=1>
  #auto-increment-increment = <default: none>
  #auto-increment-offset = <default: none>
  #master-connect-retry = <default: 60>
  #log-slave-updates = <default: 0 disable>
  #report-host = <master_server_ip>
  #report-user = <replication_user>
  #report-password = <replication_user_pass>
  #report-port = <default: 3306>
  #replicate-do-db =
  #replicate-ignore-db =
  #replicate-do-table =
  #relicate-ignore-table =
  #replicate-rewrite-db =
  #replicate-wild-do-table =
  #replicate-wild-ignore-table =
   
  ## Replication Semi-Synchronous 5.5.x only, requires dynamic plugin loading ability
  #rpl_semi_sync_master_enabled = 1 #enable = 1, disable = 0
  #rpl_semi_sync_master_timeout = 1000 #in milliseconds <default: 10000>, master only setting
   
  ## 5.1.x and 5.5.x replication related setting.
  #binlog_format = MIXED
   
  ## MyISAM Engine
  key_buffer = 1M #global buffer
  myisam_sort_buffer_size = 128M #index buffer size for creating/altering indexes
  myisam_max_sort_file_size = 256M #max file size for tmp table when creating/alering indexes
  myisam_repair_threads = 4 #thread quantity when running repairs
  myisam_recover = BACKUP #repair mode, recommend BACKUP
   
  ## InnoDB Plugin Dependent Settings
  #ignore-builtin-innodb
  #plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so
   
  ## InnoDB IO Capacity - 5.1.x plugin, 5.5.x
  innodb_io_capacity = 5000
   
  ## InnoDB IO settings - 5.1.x only
  #innodb_file_io_threads = 16
   
  ## InnoDB IO settings - 5.5.x and greater
  #innodb_write_io_threads = 16
  #innodb_read_io_threads = 16
   
  ## InnoDB Plugin Independent Settings
  innodb_data_home_dir = /var/lib/mysql
  innodb_data_file_path = ibdata1:500M;ibdata2:500M:autoextend
  innodb_log_file_size = 768M #64G_RAM+ = 768, 24G_RAM+ = 512, 8G_RAM+ = 256, 2G_RAM+ = 128
  innodb_log_files_in_group = 4 #combined size of all logs <4GB. <2G_RAM = 2, >2G_RAM = 4
  innodb_buffer_pool_size = 112G #global buffer
  innodb_additional_mem_pool_size = 4M #global buffer
  innodb_status_file #extra reporting
  innodb_file_per_table #enable always
  innodb_flush_log_at_trx_commit = 2 #2/0 = perf, 1 = ACID
  innodb_table_locks = 0 #preserve table locks
  innodb_log_buffer_size = 256M #global buffer
  innodb_lock_wait_timeout = 60
  innodb_thread_concurrency = 64 #recommend 2x core quantity
  innodb_commit_concurrency = 16 #recommend 4x num disks
  #innodb_flush_method = O_DIRECT #O_DIRECT = local/DAS, O_DSYNC = SAN/iSCSI
  innodb_support_xa = 0 #recommend 0, disable xa to negate extra disk flush
  skip-innodb-doublewrite
   
  ## Binlog sync settings
  ## XA transactions = 1, otherwise set to 0 for best performance
  sync_binlog = 0
   
  ## TX Isolation
  transaction-isolation = REPEATABLE-READ #REPEATABLE-READ req for ACID, SERIALIZABLE req XA
   
  ## Per-Thread Buffer memory utilization equation:
  #(read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size + binlog_cache_size) * max_connections
   
  ## Global Buffer memory utilization equation:
  # innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size
   
  [mysqldump]
  quick
  quote-names
  max_allowed_packet = 128M

이 질문에 댓글 쓰기 :

답변 1

답변을 작성하시기 전에 로그인 해주세요.
전체 1,543
QA 내용 검색

회원로그인

(주)에스아이알소프트 / 대표:홍석명 / (06211) 서울특별시 강남구 역삼동 707-34 한신인터밸리24 서관 1404호 / E-Mail: admin@sir.kr
사업자등록번호: 217-81-36347 / 통신판매업신고번호:2014-서울강남-02098호 / 개인정보보호책임자:김민섭(minsup@sir.kr)
© SIRSOFT