MySQL tuning

From ATI public wiki
Jump to: navigation, search

CPU stuff

  • check stuff:
#grep -E '^model name|^cpu MHz' /proc/cpuinfo

kui gigahertsid erinevad, on CPU governor paigast ära ja tõenäoliselt ondemand mis on süsteemide default säte

governori saab chekkida nii:

cat /sys/devices/system/cpu/cpu0/cpufreq/scaling_governor

et SLES 11SP4 see süsteemiüleselt performance peale ajada, tuleb vaadata et

  • pm-profiler pakk võiks olla installitud
  • siis lüüa lahti 'yast'
    1. sealt võtta System
    2. sealt Power Management
    3. sealt valida profiil Low Latency Computing

pärast võib kontrolliks teha 'rcpm-profiler status' ja vastus peaks olema

Active power management profile: low_latency                                 running

Filesystem stuff

  • Failisüsteem peaks olema XFS
täiendavaid mount argumente pole vaja, defaults on suht perfect juba niigi
  • Disk IO tune - DEADLINE
kui on multipath siis tuleb osalevad kettad üles otsida, ketta fc_db puhul nii
# multipath -ll /dev/mapper/fc_db | grep ready | cut -c 13-18 | awk '{print $1}'
sdr
sdz
sdj
sdah
jooksvat Disk Queuing Algorithm sätet saab chekkida nii
# cat /sys/block/sdd/queue/scheduler 
noop deadline [cfq] 
kui on nii nagu üleval näha siis on aktiivne CFQ scheduler
selle peaks muutma DEADLINE scheduleriks
# echo deadline > /sys/block/sdd/queue/scheduler
multipathi puhul siis
for i in `multipath -ll /dev/mapper/fc_db | grep ready | cut -c 13-18 | awk '{print $1}'`
 do
  echo deadline > /sys/block/$i/queue/scheduler
 done
multipath check
for i in `multipath -ll /dev/mapper/fc_db | grep ready | cut -c 13-18 | awk '{print $1}'`
 do
  echo -n "$i : "
  cat /sys/block/$i/queue/scheduler
 done
# for i in `multipath -d -ll /dev/mapper/fc_db | grep ready | cut -c 13-18 | awk '{print $1}'`; do echo -n "$i : "; cat /sys/block/$i/queue/scheduler; done
sdr : noop [deadline] cfq 
sdz : noop [deadline] cfq 
sdj : noop [deadline] cfq 
sdah : noop [deadline] cfq 

Müsli stuff

/etc/my.cnf

max_connect_errors = 25000
max_connections = 505
query_cache_size = 0
innodb_buffer_pool_size = 8G
innodb_additional_mem_pool_size = 2G
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
innodb_flush_method = O_DIRECT
innodb_io_capacity = 500
innodb_buffer_pool_instances = 8
innodb_thread_concurrency = 20