-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?

user1794469
  • 217
  • 2
  • 6
  • 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 '13 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 '13 at 17:21
  • I suggest reviewing http://serverfault.com/questions/350458/how-do-you-do-load-testing-and-capacity-planning-for-databases and for postgres specifically, http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server ; You may also find some tips on [dba.SE] but without more detail we can't really make many specific suggestions for your situation... – voretaq7 Aug 19 '13 at 19:59

1 Answers1

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.

Dennis Kaarsemaker
  • 18,793
  • 2
  • 43
  • 69
  • 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 '13 at 17:51
  • 2
    That's 1 flavor of general answer, but without any metrics, it's premature optimization. – mfinni Aug 19 '13 at 17:56