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:

1. A slow page load time and low concurrency support (low clock speed CPU (GHz), few cores)
2. A fast page load time, but low concurrency support (high clock speed CPU (GHz), few cores)
3. A slow page load time, but high concurrency support (low clock speed CPU (GHz), lots of cores)
4. 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.

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

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 -

1. What is the level of daily unique visitors?
2. Number of page views per visitor?
3. What country will visitors predominantly be from?
4. Do you anticipate the site traffic growing over the next 12 months, if so, by how much?
5. Does your site offer digital downloads?
6. Number of store views?
7. Number of products in catalogue?
8. Number of categories in catalogue?
9. Number of attributes in catalogue?
10. Number of attribute sets in catalogue?
11. Current disk space usage?
12. Current bandwidth usage?
13. 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.