Re: Six PostgreSQL questions from a pokerplayer

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