Running a LAMP application entirely in memory

Question

Our company uses a PHP/MySQL-based project management app heavily. So heavily that the app is really starting to slow down. It's not that the app is running out of memory -- I never go above 50% utilization. CPU isn't that heavily used either. The code in the app just does not seem very optimized in terms of queries and disk I/O... It does sloppy stuff like a SELECT DISTINCT query on a million row table to populate a few checkboxes. But we're stuck with it for now.

I'm curious what options I have if I was willing to throw more hardware at it (it's running on a Linode VPS). Is it possible to run the whole app and MySQL database in memory? How would I accomplish that? What would the drawbacks be? Is this something worth exploring?

Answer 1

Your best bet is to optimize your queries. Alternatively you can run your application on a server with RAID 10 or SSD.

Increasing RAM and enabling MySQL query cache will have little impact if your queries are badly written (or not using indexes or not having indexes at all).

Answer 2

The database could be run in memory if it was migrated to something in nosql, but that is a bad solution and may not work. The ultimate solution here is to improve your queries and optimise your MySQL database. Moving to postgres is also an option that could see some minor improvement.

Answer 3

As the others say, running the DB in memory is a very bad idea. Tuning the queries is the place to start and will likely give you your biggest wins.

I never go above 50% utilization

...but you don't say what DB engine you are using. If it's innodb then you should increase the size of the buffer pool a lot. If it's MyISAM, then increase the myisam_sort_buffer_size a little, also check what proportion of time queries are blocked for - if it's more than around 20% then switch to innodb.

It sounds like you need professional help with tuning your DBMS as well as fixing your queries.