Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
1  
Adding more hardware isn't going to help you. Understanding and fixing the problem(s) might. –  Michael Hampton Dec 12 '12 at 5:51
    
Have you tried talking to a DBA yet? –  Ignacio Vazquez-Abrams Dec 12 '12 at 6:01
add comment

3 Answers

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).

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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