Sstrutt: Initial creation

2020-07-06T18:15:59Z

Initial creation

New page

== OS Changes ==
=== OS Swapiness ===
Adjusts OS's tendency to swap memory to disk. The OS will swap memory to disk when the % usage of RAM is reached, so for 60%, the OS will start swapping when there is 60% of available RAM remaining.

* To change permanently
*# Edit <code>/etc/sysctl.conf</code>
*# Update <code>vm.swappiness = 0</code>
*# '''Reboot''' to apply
* To change on the fly
** <code> sysctl vm.swappiness=10 </code>
* To check current
** <code> cat /proc/sys/vm/swappiness </code>

=== Disk IO Scheduler ===
Adjusts the disk IO queuing algorithm in use. Deadline is recommended for MySQL.

* To change permanently (requires reboot)
*# Edit the <code> /etc/grub.conf</code>
*# Append <code>elevator=deadline</code> to the <code> kernel </code> line (see example below)
* To change <code> sda </code> and <code> sdb </code> devices on the fly
*# <code> echo deadline > /sys/block/sda/queue/scheduler </code>
*# <code> echo deadline > /sys/block/sdb/queue/scheduler </code>
* To check current
** <code>cat /sys/block/sda/queue/scheduler</code>
** Current scheduler is surrounded by <code>[ ... ]</code>

title Red Hat Enterprise Linux Server (2.6.18-8.el5)
root (hd0,0)
kernel /vmlinuz-2.6.18-8.el5 ro root=/dev/sda2 elevator=deadline
initrd /initrd-2.6.18-8.el5.img

=== Disable Last Access Timestamps ===
Stops the filesystem tracking the last time a file was accessed.

* To change
*# Edit <code> /etc/fstab </code>
*# Add <code>noatime</code> option to relevant filesystems (see example below
*# '''Reboot''' to apply
* To check current
** Run <code> mount </code> command
<pre>
/dev/mapper/VolGroup00-LogVol01 / ext4 noatime,defaults 1 1
UUID=264a246c-1823-4ef5-87f2-1a976d272a74 /boot ext4 defaults 1 2
/dev/mapper/VolGroup00-LogVol02 /home ext4 noatime,defaults 1 2
/dev/mapper/VolGroup00-LogVol04 /tmp ext4 noatime,defaults 1 2
/dev/mapper/VolGroup00-LogVol03 /usr ext4 noatime,defaults 1 2
/dev/mapper/VolGroup00-LogVol05 /var ext4 noatime,defaults 1 2
/dev/mapper/VolGroup00-LogVol00 swap swap defaults 0 0
tmpfs /dev/shm tmpfs defaults 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
</pre>

== MySQL Server Changes ==
The MySQL config file is normally found at <code>/etc/my.cnf</code>

=== InnoDB Buffer Size ===
Amount of memory that can be used by InnoDB to store data and indexes, should be as high as possible whilst leaving enough for other system processes (EG 12GB for a 16GB system)

innodb_buffer_pool_size = 10G

Requires restart to apply.

=== InnoDB Buffer Pools ===
Number of pools to split the buffer space into. Should equal the number of CPUs.

innodb_buffer_pool_instances = 4

Requires restart to apply.

=== InnoDB Redo Log Size ===
Needs to be sufficiently large enough to allow good write performance.

innodb_log_file_size = 64M

To apply change...
# Update config file
# Shutdown MySQL
#* <code> service mysql stop </code>
# Delete the old logfiles
#* EG <code> mv /var/lib/mysql/ib_logfile* /var/tmp/. </code>
# Start MySQL
#* <code> service mysql start </code>

=== InnoDB Statistics ===
Setting this option to OFF is recommended to avoid that some queries on the information_schema database become very slow.

innodb_stats_on_metadata = off

=== Sync Binary Log ===
Forces writes to the binary log to be written to disk immediately (can't be write-cached by OS).

sync_binlog = 1

=== Query Cache ===
Disable the query cache completely, this entire cache is governed by a single mutex which hurts performance, in later versions of MySQL is it disabled by default.

query_cache_limit = 0
query_cache_size = 0

=== Maximum Connections ===
Maximum number of concurrent client connections

max_connections = 400

Requires restart to apply.

=== Disable DNS Resolution ===

skip-name-resolve

Requires restart to apply.

[[Category:MySQL]]
[[Category:Linux]]

Performance Tuning (MySQL) - Revision history

Sstrutt: Initial creation