The last month we started having delays processing orders in the backend in a production e-Commerce Website.
We adjusted the system to this config and we the website runs at a really good performance now.
We calculated this values using this formula:
key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections = Total Memory
Website Details
Magento E-Commerce Website
Running in a:
Dell Server
CPU cores: Two
Processor(s): 1x Dual Xeon
Clock Speed: >= 2.33GHz
RAM: 2 GBytes
Disks: 1×250 GB SATA
Managing:
13.651 Orders in the last 6 months.
24.403 Customers
1.196 Products
Download mysqltuner.pl
More Info:
The MySQL Performance Blog http://www.mysqlperformanceblog.com/2006/05/17/mysql-server-memory-usage/
Stack Overflow: http://stackoverflow.com/questions/1178736/mysql-maximum-memory-usage
Reports
#
#
# Default Configuration
#
#
/etc/my.cnf
innodb_buffer_pool_size = 128M
mysql> SHOW VARIABLES
+-----------------------------------------+------------------------+ | Variable_name | Value | +-----------------------------------------+------------------------+ | innodb_buffer_pool_size | 134217728 | | key_buffer_size | 8384512 | | max_allowed_packet | 1048576 | | sort_buffer_size | 2097144 | | read_buffer_size | 131072 | | read_rnd_buffer_size | 262144 | | tmp_table_size | 16777216 | | myisam_sort_buffer_size | 8388608 | | query_cache_size | 0 | | query_cache_type | ON | | query_cache_limit | 1048576 | | thread_cache_size | 0 | | max_connections | 500 | | innodb_lock_wait_timeout | 50 | | wait_timeout | 28800 | +-----------------------------------------+------------------------+
./mysqltuner.pl
——– General Statistics ————————————————–
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.37
[OK] Operating on 32-bit architecture with less than 2GB RAM
——– Storage Engine Statistics ——————————————-
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 923M (Tables: 190)
[--] Data in InnoDB tables: 1G (Tables: 549)
[!!] Total fragmented tables: 573
——– Performance Metrics ————————————————-
[--] Up for: 15s (147 q [9.800 qps], 9 conn, TX: 289K, RX: 75K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 154.0M global + 2.7M per thread (500 max threads)
[OK] Maximum possible memory usage: 1.5G (73% of installed RAM)
[OK] Slow queries: 0% (0/147)
[OK] Highest usage of available connections: 0% (2/500)
[OK] Key buffer size / total MyISAM indexes: 8.0M/193.3M
[OK] Key buffer hit rate: 100.0% (52 cached / 0 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 71 sorts)
[OK] Temporary tables created on disk: 1% (1 on disk / 70 total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 88% (55 open / 62 opened)
[OK] Open file limit used: 0% (16/2K)
[OK] Table locks acquired immediately: 100% (366 immediate / 366 locks)
[!!] Connections aborted: 11%
[!!] InnoDB data size / buffer pool: 1.4G/128.0M
——– Recommendations —————————————————–
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours – recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Set thread_cache_size to 4 as a starting value
Your applications are not closing MySQL connections properly
Variables to adjust:
query_cache_size (>= 8M)
thread_cache_size (start at 4)
innodb_buffer_pool_size (>= 1G)
#
#
# New Configuration
#
#
/etc/my.cnf
innodb_buffer_pool_size = 384M
key_buffer = 256M
query_cache_size = 1M
query_cache_limit = 128M
thread_cache_size = 8
max_connections = 400
innodb_lock_wait_timeout = 100
mysql> SHOW VARIABLES
+-----------------------------------------+------------------------+ | Variable_name | Value | +-----------------------------------------+------------------------+ | innodb_buffer_pool_size | 402653184 | | key_buffer_size | 268435456 | | max_allowed_packet | 1048576 | | sort_buffer_size | 2097144 | | read_buffer_size | 131072 | | read_rnd_buffer_size | 262144 | | tmp_table_size | 16777216 | | myisam_sort_buffer_size | 8388608 | | query_cache_size | 1048576 | | query_cache_type | ON | | query_cache_limit | 134217728 | | thread_cache_size | 8 | | max_connections | 400 | | innodb_lock_wait_timeout | 100 | | wait_timeout | 28800 | +-----------------------------------------+------------------------+
./mysqltuner.pl
——– General Statistics ————————————————–
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.37
[OK] Operating on 32-bit architecture with less than 2GB RAM
——– Storage Engine Statistics ——————————————-
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 926M (Tables: 190)
[--] Data in InnoDB tables: 1G (Tables: 549)
[!!] Total fragmented tables: 572
——– Performance Metrics ————————————————-
[--] Up for: 13s (141 q [10.846 qps], 11 conn, TX: 396K, RX: 59K)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 659.0M global + 2.7M per thread (400 max threads)
[OK] Maximum possible memory usage: 1.7G (85% of installed RAM)
[OK] Slow queries: 0% (0/141)
[OK] Highest usage of available connections: 1% (4/400)
[OK] Key buffer size / total MyISAM indexes: 256.0M/193.7M
[OK] Key buffer hit rate: 100.0% (52 cached / 0 reads)
[OK] Query cache efficiency: 23.6% (29 cached / 123 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 53 sorts)
[OK] Temporary tables created on disk: 1% (1 on disk / 54 total)
[OK] Thread cache hit rate: 63% (4 created / 11 connections)
[OK] Table cache hit rate: 86% (46 open / 53 opened)
[OK] Open file limit used: 0% (16/2K)
[OK] Table locks acquired immediately: 100% (265 immediate / 265 locks)
[!!] Connections aborted: 9%
[!!] InnoDB data size / buffer pool: 1.4G/384.0M
Lito Santos
on Apr 4th, 2011
@ 6:27 am:
Hi,
Just curious on how Magento performs in Amazon EC2. Can you send me the url of a sample Magento site running in EC2? Appreciate, thanks.
admin
on Apr 11th, 2011
@ 11:30 pm:
Try look for images named unexpectedit.
There are three different versions on Suse 11