MySQL configuration for Magento 1.9

Question

Our Magento store running on 1.9.x is using Percona MySQL server 5.6.29 and since our site generates loads of MySQL queries, I need caching as much as possible. I have configured my.cnf like this

# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

explicit_defaults_for_timestamp = 1
skip-name-resolve
innodb_file_per_table
innodb_thread_concurrency = 26
query_cache_size = 512M
query_cache_limit = 18M
thread_cache_size = 64
key_buffer_size = 512M
max_allowed_packet = 64M
table_open_cache = 2000
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 64M
max_heap_table_size = 1024M
tmp_table_size = 1024M
query_cache_type = 1
wait_timeout = 300
max_connections = 200
innodb_buffer_pool_size = 64G
innodb_buffer_pool_instances=125
innodb_log_buffer_size=18M

# Disabling symbolic-links is recommended to prevent assorted security risks
# symbolic-links=0

# Recommended in standard MySQL setup
# sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# Slow Query
slow_query_log=1
slow_query_log_file=/var/log/mysql_slow_queries.log

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Our database size is just 6GB and machine has 120GB of RAM, so I need caching as much as possible to make things quicker. So, does the above configuration fits for BEST caching or is there any value I need to tune or add into?

Answer 1

This is an interesting topic and the right configuration depends a lot of your system and how it behaves.

At first I think you could consider reducing your query cache size. MySQL query cache has its some downsides. If you are frequently updating a table, you are then invalidating the query cache for ALL queries cached for that table. So really, anytime you have a “frequently updated table” means you’re probably not going to get any sort of good usage from the MySQL query cache.

It is undesirable to keep the query cache relatively high value at 256 MB as the Qcache invalidation becomes costly. See here for more details.

Additionally check this link.

Finally I like this tool for percona to get an initial configuration and later start fine tunning it after testing: https://tools.percona.com/dashboard

UPDATE

To answer your other question:

Does defining innodb_buffer_pool_size = 64G means that all the time while MySQL running it will occupy 64G out of total RAM?

It will take not only 64G but more since InnoDB reserves additional memory for buffers and control structures, so that the total allocated space is approximately 10% greater than the specified buffer pool size.

About innodb_buffer_pool_size:

The size in bytes of the buffer pool, the memory area where InnoDB caches table and index data. The default value is 128MB. The maximum value depends on the CPU architecture; the maximum is 4294967295 (232-1) on 32-bit systems and 18446744073709551615 (264-1) on 64-bit systems. On 32-bit systems, the CPU architecture and operating system may impose a lower practical maximum size than the stated maximum. When the size of the buffer pool is greater than 1GB, setting innodb_buffer_pool_instances to a value greater than 1 can improve the scalability on a busy server.

A larger buffer pool requires less disk I/O to access the same table data more than once. On a dedicated database server, you might set the buffer pool size to 80% of the machine's physical memory size. Be aware of the following potential issues when configuring buffer pool size, and be prepared to scale back the size of the buffer pool if necessary.

Competition for physical memory can cause paging in the operating system.

Address space for the buffer pool must be contiguous, which can be an issue on Windows systems with DLLs that load at specific addresses.

The time to initialize the buffer pool is roughly proportional to its size. On large installations, initialization time might be significant. For example, on a modern Linux x86_64 server, initialization of a 10GB buffer pool takes approximately 6 seconds. See Section 8.10.1, “The InnoDB Buffer Pool”.

Answer 2

your config is wrong and will quickly over-allocate all your ram, before you change anything you must read this page, every parameter you set.
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html

you need to run mysqltuner.pl to understand