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@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
