Skip to content

Instantly share code, notes, and snippets.

@juanrdlo

juanrdlo/my.cnf

Forked from kenelliott/my.cnf
Created October 8, 2020 17:16
Show Gist options
  • Save juanrdlo/bb10e83f995ffa2c43f811b092e997b0 to your computer and use it in GitHub Desktop.
Save juanrdlo/bb10e83f995ffa2c43f811b092e997b0 to your computer and use it in GitHub Desktop.
Download ZIP
16CPU / 16GB My.cnf
Raw
my.cnf
[mysqld_safe]
nice = -15
[mysqld]
max_binlog_size = 256M #max size for binlog before rolling
expire_logs_days = 1 #binlog files older than this will be purged
## Per-Thread Buffers * (max_connections) = total per-thread mem usage
thread_stack = 256K #default: 32bit: 192K, 64bit: 256K
sort_buffer_size = 1M #default: 2M, larger may cause perf issues
read_buffer_size = 1M #default: 128K, change in increments of 4K
read_rnd_buffer_size = 1M #default: 256K
join_buffer_size = 1M #default: 128K
binlog_cache_size = 64K #default: 32K, size of buffer to hold TX queries
## Query Cache
query_cache_size = 32M #global buffer
query_cache_limit = 512K #max query result size to put in cache
## Connections
max_connections = 2000 #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
## Table and TMP settings
max_heap_table_size = 1G #recommend same size as tmp_table_size
bulk_insert_buffer_size = 1G #recommend same size as tmp_table_size
tmp_table_size = 1G #recommend 1G min
# set tmpdir to a ramdisk?
#tmpdir = /data/mysql-tmp0:/data/mysql-tmp1
## Thread settings
thread_concurrency = 0 #recommend 2x CPU cores [0 create as many as needed]
thread_cache_size = 100 #recommend 5% of max_connections
## 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
## InnoDB IO settings - 5.5.x and greater
innodb_write_io_threads = 16
innodb_read_io_threads = 16
## InnoDB Plugin Independent Settings
innodb_file_per_table #enable always
innodb_data_file_path = ibdata1:128M;ibdata2:10M:autoextend
innodb_buffer_pool_size = 12G #global buffer
innodb_additional_mem_pool_size = 4M #global buffer
innodb_flush_log_at_trx_commit = 0 #2/0 = perf, 1 = ACID
innodb_log_file_size = 256M
innodb_log_buffer_size = 128M #global buffer
innodb_lock_wait_timeout = 300
innodb_thread_concurrency = 16 #recommend 2x core quantity
innodb_commit_concurrency = 16 #recommend 4x num disks
innodb_flush_method = O_DSYNC #O_DIRECT = local/DAS, O_DSYNC = SAN/iSCSI
innodb_support_xa = 0 #recommend 0, disable xa to negate extra disk flush
skip-innodb-doublewrite
sync_binlog = 0
Raw
system_info.txt
= SYSTEM INFORMATION
Linux Xen6-WH-Database 2.6.32-262.el6.x86_64 #1 SMP Sun Apr 8 18:38:00 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux
=CPUINFO
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 45
model name : Intel(R) Xeon(R) CPU E5-2640 0 @ 2.50GHz
stepping : 7
cpu MHz : 2500.159
cache size : 15360 KB
fpu : yes
fpu_exception : yes
cpuid level : 13
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat clflush mmx fxsr sse sse2 syscall lm rep_good unfair_spinlock pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 x2apic popcnt aes hypervisor lahf_lm
bogomips : 5000.31
clflush size : 64
cache_alignment : 64
address sizes : 46 bits physical, 48 bits virtual
power management:
= REMOVED IDENTICAL PROCESSORS THERE ARE 16 TOTAL PROCESSORS
processor : 15
vendor_id : GenuineIntel
cpu family : 6
model : 45
model name : Intel(R) Xeon(R) CPU E5-2640 0 @ 2.50GHz
stepping : 7
cpu MHz : 2500.159
cache size : 15360 KB
fpu : yes
fpu_exception : yes
cpuid level : 13
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat clflush mmx fxsr sse sse2 syscall lm rep_good unfair_spinlock pni pclmulqdq ssse3 cx16 sse4_1 sse4_2 x2apic popcnt aes hypervisor lahf_lm
bogomips : 5000.44
clflush size : 64
cache_alignment : 64
address sizes : 46 bits physical, 48 bits virtual
power management:
MEMORYINFO\n
MemTotal: 16313180 kB
MemFree: 6733304 kB
Buffers: 175304 kB
Cached: 5266544 kB
SwapCached: 0 kB
Active: 8468376 kB
Inactive: 662608 kB
Active(anon): 3691820 kB
Inactive(anon): 16 kB
Active(file): 4776556 kB
Inactive(file): 662592 kB
Unevictable: 34760 kB
Mlocked: 10232 kB
SwapTotal: 2064376 kB
SwapFree: 2064376 kB
Dirty: 64 kB
Writeback: 0 kB
AnonPages: 3723912 kB
Mapped: 23256 kB
Shmem: 220 kB
Slab: 202268 kB
SReclaimable: 167716 kB
SUnreclaim: 34552 kB
KernelStack: 4656 kB
PageTables: 15628 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
WritebackTmp: 0 kB
CommitLimit: 10220964 kB
Committed_AS: 14736532 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 55296 kB
VmallocChunk: 34359650952 kB
HardwareCorrupted: 0 kB
AnonHugePages: 3633152 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
DirectMap4k: 6144 kB
DirectMap2M: 16766976 kB
=DRIVEINFO
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/vg_xen6whdatabase-lv_root
33G 2.2G 29G 7% /
tmpfs 7.8G 0 7.8G 0% /dev/shm
/dev/sda1 485M 33M 427M 8% /boot
/dev/sdb 296G 6.1G 275G 3% /var/lib/mysql
192.168.101.15:/var/lib/mysql/DataWareHouse-Outfiles/
591G 530G 32G 95% /var/lib/mysql/DataWareHouse-Outfiles
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment