Re: Six PostgreSQL questions from a pokerplayer - Mailing list pgsql-performance

On Sat, 4 Jul 2009, Patvs wrote:

> I use poker software (HoldemManager) to keep track of the statistics (and
> show nice graphs) of millions of poker hand histories.
> This software (also PokerTracker 3) imports all the poker hands in
> PostgreSQL.

I've got about 200MB of PokerTracker data myself in a PostgreSQL database,
pretty familiar with what you're doing.

1) I don't think there's much that software does that will take advantage
of multiple cores.  You might get better real-time performance while
playing in that case, because you can have database/hand history
program/table processes all doing their own thing at once, but the
database itself isn't going to benefit from more cores.

2) The main performance benefit of 8.4 kicks in when you're deleting data.
Since that's not happening in your hand history database, I wouldn't
expect that to run any better than 8.3.  Eventually you might see the
software rewritten to take advantage of the new programming features added
in 8.4, that might give the newer version a significant advantage
eventually; until then, 8.3 will run at about the same speed.

3) There's not much reason for you to consider running in 64 bits, you
would need to be on something other than Windows to fully take advantage
of that.  The database server doesn't support it yet on that platform
partly because there's so little to gain:
http://wiki.postgresql.org/wiki/64bit_Windows_port

4) None of your options are the right first step.  The best thing you
could do to improve performance here is add significantly more RAM to your
server, so that more hand data could be stored there.  That will help you
out more than adding more cores, and you'll need a 64-bit Windows to fully
take advantage of it--but you don't need to give that memory directly to a
64-bit database to see that gain.  If you're not running with at least 8GB
or RAM, nothing else you can do will give you as much bang for your buck
as upgrading to there (pretty easy on a lot of desktops, harder to get
into a portable).  Along with that, you might as well follow the basic
tuning guide at
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server and get some
of the basics done correctly.  You may find correctly setting
effective_cache_size, default_statistics_target, and work_mem in
particular could give you better results when running queries against the
database; a modest bump to shared_buffers might help too, but you can't go
crazy there on Windows.  The defaults really aren't set well for as much
data as you've got in a small number of tables.

5) It's hard to imagine your use case involving anything but random I/O,
particularly if you have a decent amount of memory in the system, so a SSD
should be significantly better than your other disk options here.  That
would be the third area for improvement after getting the memory and basic
database parameters are set correctly if I were tuning your system.

6) Normally to change the locale you have to shutdown the database, delete
its data directory, and then run the "initdb" command with appropriate
options to use an alternate locale.  I thought the one-click installer
handled that though--the screen shots at
http://www.enterprisedb.com/learning/pginst_guide.do show the "Advanced
Options" page allowing one to set the locale.  This is really the wrong
list for that questions--if you still have trouble there, try sending
something with *just* that one to the pgsql-general list instead.  From
the replies you've gotten here you can see everyone is fixed on the
performance questions, and this one is buried at the bottom of your long
message.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD