CODEWITHSUNDEEP
mysqlmagentoamazon-ec2
nickwes
nickwes

Reputation: 21

MySQL/Magento Performance issues on Amazon EC2

We are running 2 web servers that host a Magento eCommerce site and 1 MySQL database server on the Amazon EC2.

We are experiencing major performance issues, deadlocks, 'lock wait timeout exceeded' errors etc on the MySQL server and really struggling to get these resolved.

We have recently upgraded the db server to an m1.xlarge instance (from m1.large) and still we are continuing to experience these problems.

We've been attributing these issues to bad disk IO we often see on the EC2 servers, but recently I've seen issues with deadlocks etc even when the disk IO is fine.

The "sar" command is showing that we have pretty poor disk IO performance at peak times or when we perform database intensive operations like creating invoices via the Magento API. We often see the iowait go up to over 20%.

Below is a link to a screenshot show the results of an "mtop" during a recent problem we had where a query was causing a slow down of the entire database:

https://i.sstatic.net/iKMLk.png

This screenshot shows one or other query that is holding up the rest of the queries from executing. It also shows quite a low load average, often we see the load average going up to 3.0 when an intensive command is being executed.

Here are the my.cnf settings:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
innodb_file_per_table=1
key_buffer=512M
max_allowed_packet=64M
table_cache=512
innodb_thread_concurrency=5
innodb_buffer_pool_size=4976M
innodb_additional_mem_pool_size=8M
innodb_log_file_size=128M
innodb_log_buffer_size=8M
thread_cache_size=150
sort_buffer_size=4M
read_buffer_size=4M
read_rnd_buffer_size=2M
myisam_sort_buffer_size=64M
tmp_table_size=256M
query_cache_type=1
query_cache_size=128M
max_connections=400
wait_timeout=28800
innodb_lock_wait_timeout=120
max_heap_table_size=256M
long_query_time=3
log-slow-queries=...mysql-slow.log

[mysqld_safe]
log-error=...mysqld.log
pid-file=...mysqld.pid

We have used the pt-query-digest function extensively to analyze our MySQL slow query log.

Basically we are seeing that the sales_flat_quote table is extremely slow with updates and inserts, but so are a number of other tables.

sales_flat_quote is not particularly large though, there are only around 100k rows in the table.

Upvotes: 2

Views: 3051

Answers (3)

lavb
lavb

Reputation: 628

I recommend to migrate your database to an RDS instance.

Upvotes: 0

sulabh
sulabh

Reputation: 1117

We have observed similar hogs on our mysql EC2 server, however, we quickly migrated our database to an RDS instance. Since then, there have been very few problems. One might argue that RDS are costly and EC2 are not, however, you would also save on the time spent on managing databases/daily backups etc.

Upvotes: 1

adrien
adrien

Reputation: 4439

Several root causes are possible:

  • Some of your slow queries may be locking tables, thus queueing other queries
  • Your queries may not be optimized
  • Your queries may need more indexes on some tables

Check your slowest queries using this official tool:

mysqldumpslow

Upvotes: 1

Related Questions