0

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 -

  1. Enable Postgres logging (I enabled for more than 3-second queries)
  2. Check the log file and find which queries are taking more time. There were few taking 15 seconds.
  3. 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 -

uptime

enter image description here

sar -q

enter image description here

top

enter image description here

vmstat 10 6

enter image description here

iostat 2

enter image description here

ps -eo 'pcpu,%cpu,pid,comm' | grep -v '%CPU PID COMMAND' | sort -n (last 20 rows)

enter image description here

ps -eo 'rss,%mem,pcpu,%cpu,pid,comm' | grep -v '%CPU PID COMMAND' | sort -n

enter image description here

  • 2
    MySQL and Postgres are using a lot of CPU. I think you need to look at indexing, slow queries and such rather than server issues. Best hire a DBA or do some reading. – Tim May 26 '18 at 2:35
  • Thanks for the reply. Really appreciate it. Anything else I can work on? I am looking at various resources on the web now and will optimize Postgres. – iCyborg May 26 '18 at 12:56
  • 1
    Define "crashing". Does syslog indicate the oom killer is terminating processes? Is your application timing out or having other poor performance? – John Mahowald May 26 '18 at 14:35
  • @JohnMahowald Crashing means the app doesn't load in the browser. Memory usage shows full 100% used and CPU shows it is under high load. Though I can still login to server via SSH to restart apache and free memory by removing log files which bring things to normal for some time. – iCyborg May 26 '18 at 15:20
  • We don't have visibility into your app to see what it doing, get a DBA or other professional services to look at it and give you a capacity assessment. And try separating your database and web tiers onto different hosts, you can isolate problems and auto scale easier that way. – John Mahowald May 30 '18 at 4:03
0

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 -

  1. Enable Postgres logging (I enabled for more than 3-second queries). Open postgresql.conf file and enable logging.

    sudo vi /etc/postgresql/9.3/main/postgresql.conf

  2. Check the log file and find which queries are taking more time.

    sudo tail -f /var/log/postgresql/postgresql-9.3-main.log

  3. There were few queries 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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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