Optimal MySQL my.cnf setting for large Magento site on a dedicated server
Solution 1
MySQL is usually the last bottleneck on a Magento store when you are performance testing for an individual user. Nevertheless, your my.cnf
does not look optimal for a Magento store, nor for that server specification. I wrote a detailed answer about MySQL load for Magento here,
https://serverfault.com/a/367856/113375
At present, your best efforts would be to focus on improving your page load times via other means (change host/optimise template etc.) and address the database last; this really only comes into play for concurrency testing and transactional load.
From http://docs.sonassihosting.com/go_dedicated.pdf
Depending on the architecture of your CPU and bus speed - using the figures below, the BEST you would be able to achieve is a 0.8s PHP page render time (excl. images/js/css).
Giving you a rough idea of what hardware to select
There are two figures you need to bear in mind when choosing a server, concurrency and page load time. Concurrency is how many customers your server can support at any time. Individual page load time is how fast the page actual loads for a single customer.
It is possible to have:
- A slow page load time and low concurrency support (low clock speed CPU (GHz), few cores)
- A fast page load time, but low concurrency support (high clock speed CPU (GHz), few cores)
- A slow page load time, but high concurrency support (low clock speed CPU (GHz), lots of cores)
- A fast page load time and high concurrency support (high clock speed CPU (GHz), lots of cores)
You can choose your hardware based on this.
Concurrency
a) A standard Magento demo store is capable of delivering roughly 230 uniques per GHz, per hour.
b) A typical web store, with admin user activity, development activity, product addition/deletion can see this degrade by around 100%, to 115 uniques per GHz, per hour.
c) A store with a poorly built/heavy template can further reduce the figure by another 100-200%, to 50 uniques per GHz, per hour.
When we quote figures, we use option b).
Individual Page load time
A standard Magento demo store (CE or EE – when FPC is disabled) is capable of loading the homepage in:
0.7 seconds 2.4GHz CPU 0.6 seconds 2.8GHz CPU 0.51 seconds 3.3GHz CPU 0.48 seconds 3.46GHz CPU
But again, a badly built/heavy template will cause this figure to increase exponentially. When we quote figures, we use the demo store template with sample data as an example.
Above source extracted from http://docs.sonassihosting.com/go_dedicated.pdf
I would assume that as you've got 5 CPUs - that you are using a VPS (cloud or otherwise), if so, I/O will likely be your bottleneck, both for disk and network. Unfortunately, due to the contended nature of VPS - they are never really ideal for Magento hosting - unless the person/company managing it has a very in-depth knowledge of Magento optimisation and the hardware is not contended.
Downsides of VPS for Magento
Every user has root access. This means that other VPS nodes on your system can be cranking bonnie/iperf/hping and subsequently hammering the resources that can't be partitioned/split off - HDD I/O, network or interrupts. So even if you have guaranteed CPU/RAM - you are subject to the shared subsystem.
You've got limited CPU and RAM. Yes, you can scale up/down - but at the end of the day, a small Magento store benefits from a minimum of 2GB RAM allocated to the MySQL instance alone, in conjunction with a rule of thumb of 1GB RAM/logical CPU core. So at the very least, a VPS should have 3GB RAM and 1 CPU core - which at most (when properly tuned) will be capable of serving around 1,500 unique visitors per day, for a transacting Magento store - assuming no HDD I/O or network I/O bottlenecks.
You have to manage it yourself. Its odd that nowadays an e-commerce website owner and operator also should be responsible for setting up, monitoring and managing their hosting infrastructure. The store owner should focus on what they are good at - running their business, not concerned with trying to resolve bugs, tune performance or administer a command line Linux server. Worst case scenario resulting in blind panic when something goes wrong that they simply don't have the experience to deal with.
Above source extracted from http://www.webhostchat.co.uk/dedicated-servers-vps-colocation/27555-magento-hosting-please-advise-what-i-may-need-2-sites-1-magento-install.html
For a better/more accurate answer, post some more details about your store -
- What is the level of daily unique visitors?
- Number of page views per visitor?
- What country will visitors predominantly be from?
- Do you anticipate the site traffic growing over the next 12 months, if so, by how much?
- Does your site offer digital downloads?
- Number of store views?
- Number of products in catalogue?
- Number of categories in catalogue?
- Number of attributes in catalogue?
- Number of attribute sets in catalogue?
- Current disk space usage?
- Current bandwidth usage?
- Transactions per day?
Solution 2
I'll Skip the large amount of text I could write and get Straight to the point.
Have a look at these for a moment:
max_user_connections = 50
max_connections = 50
Apache runs by default 500 connections. At the moment if 50 request go to MySQL / MySqlI any more will be rejected.
Related videos on Youtube
Larry B
Updated on September 18, 2022Comments
-
Larry B over 1 year
Our dedicated server is running a large Magento store with a MySQL database approximately 250MB in size.
The server spec is listed as having 5 Xeon 2Ghz processors (4MB cache) and 12GB of memory.
I'd have thought the above spec was sufficient to run even Magento very speedily, however although the site isn't slow, it's really not as fast as it should be.
The current MySQL my.cnf file is as follows:
skip-innodb ft_min_word_len=3 query_cache_limit = 4M query_cache_size = 16M ## 32MB for every 1GB of RAM query_cache_type = 1 max_user_connections = 50 max_connections = 50 interactive_timeout = 300 wait_timeout = 200 connect_timeout = 200 thread_cache_size = 32 key_buffer_size = 64M ## 128MB for every 1GB of RAM join_buffer_size = 1M max_connect_errors = 20 max_allowed_packet = 12M table_cache = 1024 record_buffer = 1M sort_buffer_size = 1M ## 1MB for every 1GB of RAM read_buffer_size = 1M ## 1MB for every 1GB of RAM read_rnd_buffer_size = 1M ## 1MB for every 1GB of RAM thread_concurrency = 4 ## Number of CPUs x 2 myisam_sort_buffer_size = 32M tmp_table_size = 16M max_heap_table_size = 12M [safe_mysqld] open_files_limit = 2048 [mysqldump] quick max_allowed_packet = 12M
I've seen a suggested my.cnf for a magento dedcidcated DB server (2GB ram) of:
query_cache_size = 512M
query_cache_limit = 256M
tmp_table_size = 256M
key_buffer_size = 64M
read_buffer_size = 128M
read_rnd_buffer_size = 128M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 128M
myisam_max_extra_sort_file_size = 128M
myisam_repair_threads = 2
myisam_recover
innodb_additional_mem_pool_size = 256M
innodb_log_buffer_size = 128M
innodb_log_file_size = 128M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 0
innodb_buffer_pool_size = 512M
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:256M:autoextend
innodb_autoextend_increment=32
I was wondering if anyone may be able to suggest the best values for the my.cnf parameter given our spec?
Also if there is anything I should be aware of when tinkering with them, such as: set this too high and expect catastrophic failure.
Any thoughts would be much appreciated. Cheers.
-
parhamr over 10 yearsHere’s an annotated MySQL config file I’ve been using in Enterprise 1.12: gist.github.com/parhamr/6177160#file-data-etc-mysql-my-cnf
-