0

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.

1

2 Answers 2

6

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?
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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .