Difference between revisions of "MySQL tuning"
From ATI public wiki
| (8 intermediate revisions by the same user not shown) | |||
| Line 27: | Line 27: | ||
==Filesystem stuff== | ==Filesystem stuff== | ||
| − | * Failisüsteem peaks olema XFS | + | * Failisüsteem peaks olema '''XFS''' |
| − | * Disk IO tune | + | : 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 | ||
<pre> | <pre> | ||
| − | multipath -ll /dev/mapper/fc_db | grep ready | cut -c 13-18 | awk '{print $1}' | + | # multipath -ll /dev/mapper/fc_db | grep ready | cut -c 13-18 | awk '{print $1}' |
| + | sdr | ||
| + | sdz | ||
| + | sdj | ||
| + | sdah | ||
</pre> | </pre> | ||
| − | + | :jooksvat ''Disk Queuing Algorithm'' sätet saab chekkida nii | |
<pre> | <pre> | ||
# cat /sys/block/sdd/queue/scheduler | # cat /sys/block/sdd/queue/scheduler | ||
noop deadline [cfq] | noop deadline [cfq] | ||
| + | </pre> | ||
| + | :kui on nii nagu üleval näha siis on aktiivne '''CFQ''' scheduler | ||
| + | :selle peaks muutma '''DEADLINE''' scheduleriks | ||
| + | <pre> | ||
| + | # echo deadline > /sys/block/sdd/queue/scheduler | ||
| + | </pre> | ||
| + | :multipathi puhul siis | ||
| + | <pre> | ||
| + | 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 | ||
| + | </pre> | ||
| + | :multipath check | ||
| + | <pre> | ||
| + | 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 | ||
| + | </pre> | ||
| + | <pre> | ||
| + | # 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 | ||
| + | </pre> | ||
| + | ==Müsli stuff== | ||
| + | |||
| + | '''''/etc/my.cnf''''' | ||
| + | <pre> | ||
| + | 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 | ||
</pre> | </pre> | ||
Latest revision as of 23:01, 12 June 2017
Contents
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'
- sealt võtta System
- sealt Power Management
- 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
