3

There is a django "web application" (from hell) we are hosting on a dedicated server. After some benchmarking I found that the "Application" is amazingly bad at database operations (refactoring is not an option at this time). It generates an ungodly amount of read and write activity. Right now we are getting another (second one) dedicated server for the sake of increased performance and a bit of emergency redundancy. Both machines sport 24cores (Intel(R) Xeon(R) CPU E5645 @ 2.40GHz) and 48GB of RAM (also, there is Raid 10, 6x150gb 15k hardrives).

I was wondering how can we improve the setup.

  1. Use KVM for virtualization, do separate vmachines for nginx+uwsgi, memcached and postgresql. Preferably make more than 1 postgresql - and use pg-pool to get more out of it.
  2. Leave the virtualization alone, install everything on each server, then with this mirror setup get proper load balancer at the datacenter level.
  3. Get a couple more machines - fewer cores, less ram (cheaper in general than the other two "main ones") and set some db cluster there?
  4. IF db cluster route - then what would give the best performance for writes and reads?

(we are rewriting the application proper way in the meantime, but getting the current - crappy - one running a bit faster is vital, we need it to hold with increasing traffic for at least the next 3 months...)

Can you offer some advice regarding strengthening the setup a bit? With the emphasize on preparing database machine structure for the ridiculously inefficient app-from-hell.

3
  • 1
    Avoid virtualization at all costs. it looks like your big bottleneck is IOPS, and a VM will just exacerbate those problems. Dec 1, 2011 at 19:38
  • You may get some good DB optimization tips over on DBA.SE
    – voretaq7
    Dec 1, 2011 at 20:14
  • 1
    i know you dont want to refactor, but it could be that just checking where those queries are, that they can easily (very easily) be fixed. Sounds like there is some looping without using select_related, where some views might generate hundreds of requests when only ~10 should be made
    – ashwoods
    Dec 1, 2011 at 21:13

3 Answers 3

4

Some basic OS-level DB performance tips:

  1. Have more RAM than you know what to do with.
    If you can fit your whole query in RAM, or at least keep the data in either the database or OS cache, your performance will be substantially improved.

  2. Spend the money on fast disk and a good RAID controller.
    RAID 10 if you can get it, and with a battery backup on the RAID controller so you can take full advantage of write-caching.

  3. Tune the Postgres server's settings
    (There's a link to the Postgres wiki page on tuning in Khaled's answer)

  4. Take advantage of Read-Only Slaves
    If you're running Postgres 9.x you can have Read-Only slave servers. Offload some read-intensive work (like reporting) to the slaves so your primary database isn't busy with that when you try to make updates.

  5. never Never NEVER NEVER virtualize a production database server
    Well almost never - Virtualizing the DB server is a performance killer.

For DB specific tips you may want to check in over on dba.SE -- Huge performance gains can be realized from proper indexing and query design.

2
  • 1
    Thanks alot for the tips, i got some further explanation from book "PostgreSQL 9.0 High Performance" Dec 2, 2011 at 10:09
  • 1
    "PostgreSQL 9.0 High Performance"++
    – gsiems
    Dec 5, 2011 at 16:44
2

I was always told - although I have no experience with it - that for optimal database speed you should run the database daemon on the bare metal (so not in a VM) on a RAID10 array. For what I understand a RAID1 + LVM counts as RAID10 in this case and loads of RAM. VMs will eat into your available RAM.

Also I not sure what good load balancing in front of VMs on the same physical server will do (maybe I'm completely wrong about this though).

2
  • +1. As a general rule, production databases should not be virtualized. All other optimizations will help, but moving to physical hardware can easily double your speed with no other changes.
    – voretaq7
    Dec 1, 2011 at 20:00
  • +1 for RAID10 performance tradeoffs/gains. RAID10 minimizes risk, minimizes performance impact, but increases cost. Don't bother using LVM's mirror to provide part of the RAID10 setup, use mdadm all the way for this. Dec 1, 2011 at 21:31
1

There are several things that can be done to improve the DB server performance. Here are some:

  1. Optimize your queries as much as possible.
    Set log_min_duration_statement in your Postgres configuration file to what you consider to be the boundary of acceptable speed, then attack the slow queries with EXPLAIN to find out why they're slow.

  2. Tune your postgresql server parameters. You can find resources on the web on how to do this.

  3. Separate the services onto different machines when applicable.
    This is not only good for performance, but it is also good for security.

  4. Create the needed index(es) on the DB table(s) to speedup the queries.
    The results of EXPLAIN from (1) above will probably help you

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .