EDIT (15th June 2018) - I have resolved the issue. It was Postgres where some queries were taking more than 15 seconds. Most of the queries were for the same type of pages so I just removed that. This is what I did -
- Enable Postgres logging (I enabled for more than 3-second queries)
- Check the log file and find which queries are taking more time. There were few taking 15 seconds.
- I looked at the code. There was no indexing in one table so I created it. The issue was due to one unoptimized query. Since it wasn't needed, I just removed it from the code.
We are on 30 GB AWS instance running a Ruby on Rails app using Passenger + Apache.
Though the App Postgres DB has millions of records, The DB size is few GB only. Now I am finding the server to always be on High Usage. Here is the server load and process running.
What can I do to diagnose the issue or optimize the server? as the app keeps crashing due to high CPU or Memory usage with only 20 users online.
Here is the output of commands -
vmstat 10 6
ps -eo 'pcpu,%cpu,pid,comm' | grep -v '%CPU PID COMMAND' | sort -n (last 20 rows)
ps -eo 'rss,%mem,pcpu,%cpu,pid,comm' | grep -v '%CPU PID COMMAND' | sort -n