HOW TO MAKE POKERTRACKER FLY . . . . (well almost)

[Germaniac2]

-
MYSTERY OF MOLASSES LIKE IMPORT SPEED OF NEW FILES SOLVED !

Through an exchange with tarix and WhiteRider the mystery of the vastly slower
imports of some of my Hand History files has been hunted down and cornered.

There still remains a question as to whether the various encoding of the files, i.e.
ANSI versus UTF-8 could also have a small impact on import speeds. This may be the
case because there is a slight overhead for the internal conversion by PSQL of the
UTF-8 encoded .txt files.

However, the cause for the 65% slower import speeds in my tests was:

> > > C H A O S ! < < <

When WhiteRider examined some of the files I used, he found that the newer files
contained Hand Histories that were in no particular order. That is they jumped from
table to table. Each hand was complete and error free. But rather than being in an
order that was sequential and with the same players, PSQL was forced to jump around
in the database file system to perform its work. This of course slowed the work down
by a significant margin. In my case imports slowed down to a third of the speed files
were imported at that had sequential Hand Histories!

Thank you WhiteRider and tarix !

So if you feel that your import speeds are not what they once were or just to make
sure that you aren't giving up a lot of performance to a file issue, open up some of
your Hand History files in notepad and check them for order. You just might be able
to triple the speed of your imports either by setting your provider straight & getting
oderly files by moving to a source that will provide you with 'straight' files.
-

[Germaniac2]

-
zubs1aa:

Just start with your current postgresql.conf file and change the setting for wal_buffers to 1MB.
This setting is under the WRITE AHEAD LOG Section and is most likely commented out by a #;
meaning that currently PSQL uses the default setting of 64kB. If an increase to 1MB [default x16]
gives you a performance boost keep trying to increase the value.

Run import tests before and after the change. Then try different settings, e.g. 4MB, 8MB, etc.
The same goes for the temp_buffers setting which is under the RESOURCE USAGE heading.
The default value for this parameter is 8MB. I found using three times that or three times the
amount for wal_buffers to work for me. [i.e. 24MB temp_buffers with up to 8MB wal_buffers
or in my case 36MB temp_buffers with 12MB wal_buffers.]

For more information on the various parameters see the PostgreSQL Wiki Article at:
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
-

[zubs1aa]

good wiki- exactly what I wanted to see.

I'll run an import test into a blank DB with some old HH files and see what happens.

Is there any test other than import that would make sense re: PT's running speed during play?

[tarix]

wal_buffers only effect writes. If you are trying to fix query speed those are different settings.

Postgresql 8.4: Query Planning

If you have an SSD then you'll want to tune down to the random_page_cost value. Since sequential is 1.0 you are basically telling postgres how much slower a random read is. My guess is that for SSD the value should be 2.0 or lower.

[Jeglodyte]

Nice piece of work, I am eager to try out some of the settings. Aside from tuning for import speed, I'd also be very keen for some tuning on RUSH HUD loading times. The several seconds I mostly have to wait can be quite annoying. I' ve read the post on RUSH performance tuning, but I think there is more to be gained from optimal configuration, just don't know how enough about postgres or how to monitor loading times reliably to do testing myself. Any thoughts on what settings influence HUD load delay?

Cheers,

Jeglodyte

[tarix]

Any thoughts on what settings influence HUD load delay?

In general the more load on your system the slower stats will appear. With one table an a 100,000 hand database mine are nearly instant. (They update before the blinds finish posting.)

What is your configuration exactly? The amount of ram, hard drive speed, number of cores, postgres version, number of tables, and if you are combining more than one database are all going to be huge factors causing slowdowns.

[Jeglodyte]

Indeed, single-tabling is no problem at all. The problem is that I like to run four Rush tables plus either another four rush tables or six or so non-rush tables. I'll usually get to wait maybe 5-10 seconds before the rush-HUD appears, causing a substantial slow-down in my hourly hands unless I give up on the HUD.

What is your configuration exactly? The amount of ram, hard drive speed, number of cores, postgres version, number of tables, and if you are combining more than one database are all going to be huge factors causing slowdowns.

My configuration: I7 Quad core, 4GB Ram, SSD, postgres 8.4.3, and a HUD with extensive stats in the popups. If I only use my RUSH database (100k or so) it loads with acceptable delay, but to play the other tables I need to have the other databases loaded with a couple of million hands, causing delays for the RUSH tables. If I could give table-specific instructions to PT3 for what bases to load, it would pretty much resolve the problem, or if the speed of loading could be tuned otherwise. It would also help a lot if the HUD would CLEAR a lot faster. Now it lingers for several seconds or more, somtimes leading to big preflop misreads. Any way to tune that bit up?

[tarix]

If you can attach the logs of a few rounds of your 4 + 4 Rush tables or your half and half configuration to a support ticket that would be very helpful. Also include your postgres.conf so that can be checked over.

Over the long term things will improve the most for setups like yours, but there still might be some small gains we can provide for you today.

[kes1981]

Is it normal for me to be getting 780 hands/sec while importing a huge number of hands (4 million)?

[WhiteRider]

On a good system that is possible.