Fine tune MySQL for Better performance

Few issues I came across when dealing with MySQL performance. Let us go through these problems one by one.

Swap usage:

MySQL loves memory as almost all databases do but MySQL tries to utilise all  the possible available memory. In order to restrict MySQL to use allocated memory resources we should implement following:-

  •  Swapiness

Swappiness is the kernel parameter that defines how much (and how often) your Linux kernel will copy RAM contents to swap. This parameter’s default value is “60” and it can take anything from “0” to “100”. The higher the value of the swappiness parameter, the more aggressively your kernel will swap.

Action: set swapiness to 1 as shown below

bash-4.2$ sysctl vm.swappiness=1

bash-4.2$  cat /proc/sys/vm/swappiness
1
  • innodb_buffer_pool_size

Allocate 60% of the total memory to MySQL instance. It is configured via configuration file my.cnf. For Example if we have 100G of RAM then we will allocate 60G to MySQL. We can reduce or increase percentage of memory allocation as per needs but increase in memory up to certain limit allocation can cause aggressive swap operations.

innodb_buffer_pool_size= 60G

  • Huge Pages

Hugepages is a mechanism that allows the Linux kernel to utilize the multiple page size capabilities of modern hardware architectures. Linux uses pages as the basic unit of memory, where physical memory is partitioned and accessed using the basic page unit. The default page size is 4096 Bytes in the x86 architecture. Hugepages allows large amounts of memory to be utilized with a reduced overhead. Linux uses “Translation Lookaside Buffers” (TLB) in the CPU architecture. These buffers contain mappings of virtual memory to actual physical memory addresses. So utilizing a huge amount of physical memory with the default page size consumes the TLB and adds processing overhead.

Applications that perform a lot of memory accesses may obtain performance improvements by using large pages due to reduced Translation Lookaside Buffer (TLB) misses.

Before large pages can be used on Linux, the kernel must be enabled to support them and it is necessary to configure the HugeTLB memory pool. For reference, the HugeTBL API is documented in the Documentation/vm/hugetlbpage.txt file of your Linux sources.

Please click here to see how to implement huge pages

  • Metadata states persistence

Metadata or database stats should be kept up to date by performing optimize operations and by keeping innodb_persistent_stats =1 / ON . Optimisation of tables can be difficult when table is very large therefor we should implement data partitions for large tables. Optimisation not only updates the stats but it re-organises the indexes as well and helps regain the free storage.

  • I/O capacity

If there are enough cpus and memory available then we should consider to increase number of  innodb_write_io_threads and innodb_read_io_threads from (4 default ) to 8 or 16. This can make huge impact on performance.

  • innodb_flush_log_at_trx_commit

Controls the balance between strict atomicity, consistency, isolation, and durability compliance for commit operations and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. You can achieve better performance by changing the default value but then you can lose up to a second of transactions in a crash.

Keeping value to 2 means we can loose 1 sec worth of data as commits happens every sec regardless of size of transactions / redo but can improve i/o operations.

  • innodb_flush_method  

                      innodb_flush_method=O_DIRECT is recommended for OS caching for storage engine.  This will not use OS file cache.  

  • Partitions

Always use time range partitions to manage data deletions as it helps the big data to keep meta data stats up to date. It also helps indexing and data retrieval very fast.

 

Thanks for reading . Don’t forget to leave your feed backup.

Regards

Raja Naveed

2 thoughts on “Fine tune MySQL for Better performance”

  1. HEY,
    Good to see you online !!!
    I think this innodb_buffer_pool_size 60% formula is obsolete now a days. In you example you considered 100GB ram Box for MYSQL. This essentially means we are not utilizing 40GB of RAM. Buffer allocation should allow active data set to sit in the Buffer. For example I have a database of 1TB and my RAM on the box is 256GB and my active data set is 200Gb. If I allocate 60% formula then I will get 153GB RAM allocated to the buffer. Basically I am forcing MYSQL not to use the RAM that is available and causing innodb contention due to extensive flushing of dirty pages in the buffer to make more space for fresh ones. This 60% or more popular 70% formula was usually for the old days when we had a maximum ram on the box at 32GB. Secondly when defining such a large buffer you may also must consider innodb_buffer_pool_instances and innodb_buffer_pool_chunk_size. Having said that we still need to leave something for background operations, sort buffer, heap tables etc, but certainly not 40Gb!

    1. Thanks for your comments and I agree with you partially . Setting 60% means that MySQL will use 60%+2 to 10% of the total available memory. Again it depends upon the usage of application connected and number for connections to the database. we need to balance out connection threads with MySQL internal Threads. I agree that we should consider innodb_buffer_pool_instances and innodb_buffer_pool_chunk_size. I am considering very busy , application heavy server. In the end we will have to adopt , what fits well.

      Thanks and Regards
      Raja Naveed

Leave a Reply

Your email address will not be published. Required fields are marked *