my.cnf for local MySQL server - several Magento projects with big catalogs

Question

I've read tons of times there is no magical my.cnf file for a Magento project, but... I want to believe there should be some params specifically relevant for Magento catalog pages when dealing with really big catalogs

What I am asking here is some tips to correctly tune those relevant params, according to this specifications:

MySQL server is version 5.6

MySQL server is just a 6GB RAM dedicated server, in our local network

MySQL server should host around 10 Magento databases (although most of them will remain inactive... let's say we'd work with 2 or 3 projects per day)

Max connections param has zero importance here, as we talk about our local network. So we can reduce it to 10 or 20, in order to increase some buffers (if this really helps performance...)

I've been playing after lot of Google reading, trying some tools (such as mysqltuner or percona wizard), and we have get acceptable loading time for any page in:

• Regular Magento project: any kind of page
• Big Magento project: any kind of page except catalog/category, catalog/product & checkout process

I am not asking for a flying navigation, just consider acceptable about 5-10 seconds per catalog page in our local environment (of course, this is not acceptable for live environments, but enough for local). Right now we are really far from this times in catalog pages for the big Magentos

This is our current my.cnf file relevant (I think) params

skip-external-locking
skip-name-resolve

innodb_additional_mem_pool_size         = 20M
innodb_autoinc_lock_mode                = 2
innodb_buffer_pool_size                 = 2G
innodb_data_file_path                   = ibdata1:10M:autoextend
innodb_flush_log_at_trx_commit          = 0 # faster
innodb_log_buffer_size                  = 4M
innodb_log_file_size                    = 512M
innodb_buffer_pool_instances            = 2
innodb_buffer_pool_dump_at_shutdown     = 1
innodb_buffer_pool_load_at_startup      = 1

max_connections                         = 20
max_heap_table_size                     = 128M
tmp_table_size                          = 128M
open_files_limit                        = 65535

key_buffer_size                         = 80M
join_buffer_size                        = 16M
myisam_sort_buffer_size                 = 64M
query_cache_size                        = 64M
query_cache_type                        = 1
read_buffer_size                        = 1M
read_rnd_buffer_size                    = 4M
sort_buffer_size                        = 1M

table_definition_cache                  = 1024
table_open_cache                        = 256

To be clear, each of this projects are working properly in their separated live hostings (both testing and/or production environments), so it doesn't seem a coding issue, we use Profiling tools for that

When I say "big Magento" I am talking about 50k - 100k skus, with several hundreds (maybe some project would have more than 1k) of categories, and several hundreds of attributes. Our "regular Magento" would mean less than 10k skus, less than one hundred categories, etc...

Thanks in advance

Answer 1

just consider acceptable about 5-10 seconds per catalog page hm, if your catalog loads in 15-20 seconds you need to start from aoe_profiler.

there are lots of tools to fine-tune and monitor mysql, my favorite one:

1 - mytop

Mytop is a console-based tool for monitoring queries and the performance of MySQL. It supports version 3.22.x, 3.23.x, 4.x and 5.x servers. It's written in Perl and support connections using TCP/IP and UNIX sockets.

2 - mysqltuner.pl

MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief format along with some basic performance suggestions.

3 - Percona Toolkit for MySQL

Percona Toolkit for MySQL® is a collection of advanced open source command-line tools, developed and used by the Percona technical staff, that are engineered to perform a variety of MySQL and system tasks that are too difficult or complex to perform manually. Percona Toolkit supports Percona Server, MySQL® and MariaDB® and works best with Percona Server and other Percona products.

also we have default mysql config file. you are absolutely right, every database and config very unique. That is why you need to read this page carefully.