Jorge Maldonado
asked on
Web server response time problem on high load
I have a website where the number of visitors is increasing and it is facing a poor response time. I am not a server specialist and for this reason I want to expose my issue here to receive some feedback. The website has the following characteristics:
* Developed with ASP.NET Web Forms (I will be implementing a new version with ASP.NET MVC soon).
* It uses a PostgreSQL 9.1 database.
* It is 100% dymanic which means that every page is formed at run-time getting information from the DB.
* Hosted in a cloud server with 2 Cores and 4 GB of RAM.
* Windows Server 2012 in drive C:
* PostgreSQL DB in another "hard disk" as drive D:
* The average number of daily visitors is 2,500
Watching the Task Manager I can notice that there are many PostgreSQL processes which consume CPU 100%. Another process that is shown in the "IIS Worker Process" but I have not seen it as critical as the PostgreSQL process. So it seems that the main issue here has to do with the database access. When CPU usage is high the website response is extremely low and also it is very difficult to establish a remote desktop connection.
I have thought about upgrading the cloud server to, for example, 4 cores and 8 GB of RAM, but firstly I want to get feedback from experts here so I can take the best approach. I am not a server expert and, for this reason, I do not want to take steps blindly.
Respectfully,
Jorge Maldonado
* Developed with ASP.NET Web Forms (I will be implementing a new version with ASP.NET MVC soon).
* It uses a PostgreSQL 9.1 database.
* It is 100% dymanic which means that every page is formed at run-time getting information from the DB.
* Hosted in a cloud server with 2 Cores and 4 GB of RAM.
* Windows Server 2012 in drive C:
* PostgreSQL DB in another "hard disk" as drive D:
* The average number of daily visitors is 2,500
Watching the Task Manager I can notice that there are many PostgreSQL processes which consume CPU 100%. Another process that is shown in the "IIS Worker Process" but I have not seen it as critical as the PostgreSQL process. So it seems that the main issue here has to do with the database access. When CPU usage is high the website response is extremely low and also it is very difficult to establish a remote desktop connection.
I have thought about upgrading the cloud server to, for example, 4 cores and 8 GB of RAM, but firstly I want to get feedback from experts here so I can take the best approach. I am not a server expert and, for this reason, I do not want to take steps blindly.
Respectfully,
Jorge Maldonado
David Johnson, CD
more ram and more CPU will definitely help in the short term. Long-term put the SQL server on its own hardware (preferably SSD HD's)
System definitely needs more RAM - 8GB Min
Database needs to be on SSD's - a pair min, in RAID-1/ RAID10
Database needs to be on SSD's - a pair min, in RAID-1/ RAID10
As David said, likely if you check the following you will find problems.
1) Run top + check swap usage. If you see more than 0% swap used, increase memory.
2) Try using https://github.com/jfcoz/p
Note: I gave up trying to use PostgreSQL years ago, so I haven't used the above script. Since you use PostgreSQL, look around for other similar scripts till you find a useful one.
Better to tune your PostgreSQL, than just throw it onto an SSD disk.
a) If you're system is primarily random read access, use a mechanical disk + tune PostgreSQL (via a tuning script) to correctly buffer data.
b) Primarily sequential reads, use an SSD drive if reads are very long... return 1000s of records, which can't easily fit in memory. Better to use LIMIT statements than expensive SSD drives.
c) Primarily writes, use a mechanical drive + tune your buffering correctly.
3) Check your disk i/o throughput. Many hosting companies use shared disk i/o hardware. This will show up as your disk i/o paths redlining (running at or near 100%). If this occurs, the only fix is change hosting.
I check this via alias diskio='iotop -P -a -d 1'
Note: Just reread your comment + notice you're running Windows rather than LAMP, so debugging performance issues will be far more time consuming.
In your case, you'll look for the best equivalent of top + iotop for your version of Windows.
1) Run top + check swap usage. If you see more than 0% swap used, increase memory.
2) Try using https://github.com/jfcoz/p
Note: I gave up trying to use PostgreSQL years ago, so I haven't used the above script. Since you use PostgreSQL, look around for other similar scripts till you find a useful one.
Better to tune your PostgreSQL, than just throw it onto an SSD disk.
a) If you're system is primarily random read access, use a mechanical disk + tune PostgreSQL (via a tuning script) to correctly buffer data.
b) Primarily sequential reads, use an SSD drive if reads are very long... return 1000s of records, which can't easily fit in memory. Better to use LIMIT statements than expensive SSD drives.
c) Primarily writes, use a mechanical drive + tune your buffering correctly.
3) Check your disk i/o throughput. Many hosting companies use shared disk i/o hardware. This will show up as your disk i/o paths redlining (running at or near 100%). If this occurs, the only fix is change hosting.
I check this via alias diskio='iotop -P -a -d 1'
Note: Just reread your comment + notice you're running Windows rather than LAMP, so debugging performance issues will be far more time consuming.
In your case, you'll look for the best equivalent of top + iotop for your version of Windows.
i agree with tune your DB
But put it on SSD's as well. Using Spinning Rust will only slow things down!
e.g single HDD v Single SSD - HDD (150 IOPS/150 MB/s, SSD 30K IOPS/500 MB/s minimum) No contest
But put it on SSD's as well. Using Spinning Rust will only slow things down!
e.g single HDD v Single SSD - HDD (150 IOPS/150 MB/s, SSD 30K IOPS/500 MB/s minimum) No contest
hello,
https://www.postgresql.org/docs/current/static/monitoring-stats.html#MONITORING-STATS-SETUP
on db side you can have an point of view of the posgresql activities .
may be can you found some clues on the time consumming.
https://pgtune.leopard.in.ua/
can give you some tips about your configuration.
another way is to use pg_bench to obtain metrics and tune the database resources ,this is an iterative processus.
https://www.postgresql.org/docs/10/static/pgbench.html
adjust the size of shared_buffers,work_mem work_mem, maintenance_work_mem
to obtain the better time.
but start and stop of database are required.
https://www.postgresql.org/docs/current/static/monitoring-stats.html#MONITORING-STATS-SETUP
on db side you can have an point of view of the posgresql activities .
may be can you found some clues on the time consumming.
https://pgtune.leopard.in.ua/
can give you some tips about your configuration.
another way is to use pg_bench to obtain metrics and tune the database resources ,this is an iterative processus.
https://www.postgresql.org/docs/10/static/pgbench.html
adjust the size of shared_buffers,work_mem work_mem, maintenance_work_mem
to obtain the better time.
but start and stop of database are required.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.