up vote 0 down vote favorite
share [g+] share [fb]

Recently we changed app server of our rails website from mongrel to passenger [with REE and Rails 2.3.8]. The production setup has 6 machines pointing to a single mysql server and a memcache server. Before each machine had 5 mongrel instance. Now we have 45 passenger instance as the RAM in each machine is 16GB with 2, 4 core cpu. Once we deployed this passenger set up in production. the Website became so slow. and all the request starting to queue up. And eventually we had to roll back.

Now we suspect that the cause should be the increased load to the Mysql server. As before there where only 30 mysql connection and now we have 275 connection. The mysql server has the similar set up as our website machine. bUt all the configs were left to the defaul limit. The buffer_pool_size is only 8 mb though we have 16GB ram. and number of Concurrent threads is 8.

Will this increased simultaneous connection to mysql would have caused mysql to respond slowly than when we had only 30 connections? If so, how can we make mysql perform better with 275 simultaneous connection in place.

Any advice greatly appreciated.

UPDATE:

More information on the mysql server:

RAM : 16GB CPU: two processors each having 4 cores

Tables are innoDB with default configs.

Thanks

link|improve this question
feedback

1 Answer

up vote 0 down vote

I am going to need the following information please.

  1. myISAM or InnoDB ? (my.cnf implies myisam please confirm)

Tools you may want to consider:

InnoTop (http://code.google.com/p/innotop/) myisam_defrag (https://github.com/Oneiroi/myisam_defrag) mysqltuner (http://mysqltuner.pl/mysqltuner.pl)

Some with sending data for > 700 seconds, can you please run an explain on these?

EXPLAIN SELECT `reviews`.* FROM `reviews` WHERE (`reviews`.user_id IN (78707,123639,30671,31638)) AND (reviews.deleted_at IS NULL OR reviews.deleted_at > '2011-03-25 06:00:07')

TEMP TABLES == BAD! run an explain on the following please.

  State: Copying to tmp table
  Info: SELECT `reviews`.* FROM `reviews`  inner join users on users.id = reviews.user_id WHERE (reviews.user_id is not null and reviews.karma>0 and datediff(curdate(),reviews.created_at)<=30) AND (reviews.deleted_at IS NULL OR reviews.deleted_at > '2011-03-25 06:12:11')  ORDER BY reviews.karma desc, reviews.unhelpful_count asc, users.reviews_count desc,reviews.created_at desc LIMIT 3
link|improve this answer
we are using innoDB tables – railscoder Mar 28 '11 at 13:08
Then first and formost increase your innodb_buffer_pool size, it is default and will most likely be being exhausted, use mysqltuner (link above) for recomendations on the size to set it too. – Oneiroi Mar 28 '11 at 13:13
i pasted the explain output here pastebin.com/cZi5zx49 – railscoder Mar 28 '11 at 13:14
when there were only 30 connection, the website was performing without problems. only when it was increased to 275 we faced all issues. do u think these mysql config was ok enough for 30 connection but cannot handle 275 connections. We are not sure what was the reason for issue. just guessing. this will make us clear. – railscoder Mar 28 '11 at 13:17
capping at 30 connections will certainly use much less resources, the explain you have provided also shows that keys are not being used correctly, you will need to also look at your use of indexes. – Oneiroi Mar 28 '11 at 14:08
show 3 more comments
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.