-3

My question is simply: have i missed any configuration settings that might make postgres achieve better read throughput?

Background
I'm modeling a very simple twitter program. My schema has only 3 tables: subscriptions, posts, and timelines.

I have indexes on each of the tables, most importantly the timeline.time field.

I start postgres with:

postgres -h 127.0.0.1 -p 10000 -D /mnt/tmp/postgres -c synchronous_commit=off
-c fsync=off -c full_page_writes=off  -c bgwriter_lru_maxpages=0
-c shared_buffers=24GB  -c bgwriter_delay=10000 -c checkpoint_segments=600

Note that 24GB is somewhat bigger than my dataset (i tried using 36GB with no additional gain). Also, /mnt/tmp is a tmpfs in ram so any 'disk' writes should be as fast as possible.

Am i missing anything major?

Improve this question
3
  • 1
    How about telling us something, anything, about your physical hardware? Particularly IO? Perhaps you might have some performance stats from the server?
    – mfinni
    Aug 19, 2013 at 17:09
  • 5
    There isn't a boolean flag for "make.database.go.faster.no.really" that no one's telling you about that you can just set to 'true'. Like all DB servers you need to profile it to discover where the bottlenecks are and then fix them.
    – Rob Moir
    Aug 19, 2013 at 17:21
  • I suggest reviewing serverfault.com/questions/350458/… and for postgres specifically, wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server ; You may also find some tips on Database Administrators but without more detail we can't really make many specific suggestions for your situation...
    – voretaq7
    Aug 19, 2013 at 19:59

1 Answer 1

Reset to default
3

Yes, this is possible. Here's a quick recipe:

  1. Find out what's the slowest part
  2. Make it go faster

If you think this answer is too generic and useless, well so was the question :) So unless you have more specific issues/problems/questions, I think this is the best we can do.

Improve this answer
2
  • You may want to look at using a memory resident DB to do the actual changes etc and only write out the stuff you need to keep on persistent storage into Postgre
    – TheFiddlerWins
    Aug 19, 2013 at 17:51
  • 2
    That's 1 flavor of general answer, but without any metrics, it's premature optimization.
    – mfinni
    Aug 19, 2013 at 17:56

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