Optimizing MySQL database for Magento on Amazon EC2 server

For KOOLSKOOL we use Amazon EC2 server. Magento is a very powerful ecommerce platform but like any real world application, it needs real server bandwidth.One of the simple ways to optimize the speed is to optimize the MySQL server. The configuration I have used on my EC2 large server is as follows. (This is not a dedicated MySQL server and I am also running apache on this)

old_passwords=1
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 32M
sort_buffer_size = 10M
read_buffer_size = 10M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 16
query_cache_type = 1
query_cache_size = 48M
max_connections=500
wait_timeout=120
tmp_table_size = 64M
max_heap_table_size = 64M
innodb_log_file_size  = 100M 
innodb_log_buffer_size = 4M
innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 20M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method=O_DIRECT
innodb_thread_concurrency=8
[mysqld_safe]
#log-error=/var/log/mysqld.log
log-error=/mnt/mysql/err-log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#log_slow_queries=/var/log/mysqld.slowquery.log


However by doing this I got an error "General error: 1033 Incorrect information in file: './xxx_db/table.frm"
This got fixed immediately by reverting the innodb configuration back. The problem was in the innodb_log_file_size. As I had changed this from 10M to 100M, when the MySQL restarted it would start failing as the existing log were much smaller than what it expects. To fix this you would need
1. Stop MySQL (#/etc/init.d/mysqld stop)
2. Goto the MySQL directory and backup ib_logfile0 and ib_logfile1.
3. Change the my.cnf with the new innodb_log_file_size
4. Start MySQL (#/etc/init.d/mysqld start)
If you check the logs you will find the following
110103 11:24:34 [Warning] Changed limits: max_open_files: 65535  max_connections: 500  table_cache:32512
110103 11:24:34  InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 100 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
110103 11:24:42  InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 100 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100
110103 11:24:49  InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
110103 11:24:49  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 3 622597644.
InnoDB: Doing recovery: scanned up to log sequence number 3 622597644
110103 11:24:50  InnoDB: Started; log sequence number 3 622597644

 As you have already set your server innodb_buffer_pool_size = 2048M, you will want to make sure that your Linux OS is not caching and swapping you pages. the default swappiness of Linux is set to be 60 (# cat /proc/sys/vm/swappiness) which is good for desktops but not optimized for servers. You would want to set this to 10.
To set this in the running instanyce, ou would #echo 10 > /proc/sys/vm/swappiness
To set this for reboot, you would have to add this line in /etc/sysctl.conf, vm.swappiness=10


By doing this, I have been able to reduce the load time for my homepage by 30%


Useful links
http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/
http://forums.mysql.com/read.php?22,32004,32014

Comments

Post a Comment

Popular posts from this blog

The Selfish Organization: How Internal 'Selfish Genes' Sabotage Organizational Success

Image
The second law of thermodynamics tells us that entropy drives systems toward equilibrium and disorder. In his final shareholder letter as CEO of Amazon, Jeff Bezos referenced Richard Dawkins' "The Blind Watchmaker" to illustrate how natural selection counters entropy by building complex life through energy-driven processes. Bezos urged Amazon to resist the entropy-like pull of complacency by staying in "Day 1" mode—continuously growing, innovating, and dynamically adapting to trends rather than following predetermined designs. While this advice resonates for maintaining corporate vitality, a deeper examination through Dawkins' evolutionary lens reveals a more insidious challenge lurking within organizations themselves. The very mechanisms that help companies survive external entropy may be generating internal evolutionary pressures that undermine efficiency and threaten long-term survival. The Gene's-Eye View of Organizations Richard Dawkins' rev...
Read more

Monkeys and Alligators: My ADHD Journey

Image
In 2023, at age 49, I received the diagnosis that transformed my understanding of myself: Attention Deficit Hyperactive disorder (ADHD). For years, I had navigated episodes of anxiety, depression, and anger, developing coping mechanisms that somehow kept me afloat. But that year, the depression became overwhelming. I needed help. What pushed me to seek support was my sister. During her medical studies, she had studied ADHD extensively. With the frankness only a sister can provide, she said, "You're a textbook case of ADHD." Her words were simultaneously shocking and deeply familiar. The psychiatrist confirmed both types: inattentive and hyperactive. The moment he spoke those words, a weight lifted from my chest—a burden I hadn't realized I'd been carrying. I had always known I was different, but could never name it. Now I had a map to understand my own mind. The Medication Revelation My doctor prescribed atomoxetine, starting low. For weeks, nothing changed. ...
Read more

Masala Management: Product and Engineering Leadership Lessons from the Indian Kitchen

Image
As a technology leader constantly seeking wisdom in unexpected places, I've discovered that some of my most profound leadership insights come not from business books or conference talks, but from watching the intricate dance of an Indian kitchen. In my last article, " The Indian Kitchen Philosophy: Transforming the Ordinary into Extraordinary ," I wrote about how the Indian kitchen follows principles that differ from those of the western kitchen. The same principles that transform humble ingredients into extraordinary meals offer remarkable parallels for navigating the complexities of modern product development and engineering management. Making Magic with What You Have The Indian kitchen's greatest strength lies in its ability to create abundance from scarcity. A skilled cook can transform whatever vegetables are available at the local market into a feast, just as successful product and engineering teams learn to excel with the resources actually at the...
Read more