FYI,

I was having some trouble with my computer being slow when having a bunch of FTP tables open, auto-importing from HEM and using FTTO, but I did a couple of really simple PostgreSQL optimizations that have made a huge difference:

1.) Find your postgresql.conf file and open in notepad. Mine was in C:\Program Files\PostgreSQL\8.2\data

2.) Find the 'shared_buffers' value. The default value is comically low, but a good value is 1/4 of your total memory. I set mine to 768MB as I have 4GB of RAM (of which a little more than 3GB actually can be used in non 64-bit systems).

3.) Find the 'effective_cache_size' value. The default for this is also very low, but can be set to upward of 1/2 of your total memory. I set mine to 1536MB.

4.) Find the 'redirect_stderr' value and change it to 'off', unless you have a specific reason for looking at log files. If you're like me, the HEM queries result it tons of log data being written out. I had 10MB being written out every 20 minutes. Not only does this eat up tons of disk space, but this will cause your system to be wasting time writing to disk for logs you'll likely never look at.

5.) Restart either your computer or the PostgreSQL process from Start -> Control Panel -> Administrative Tools -> Services.

See '5-Minute Introduction to PostgreSQL Performance' for more info:
http://www.westnet.com/~gsmith/content/postgresql/pg-5minute.htm

Enjoy.

Know why my 'effective_cache_size' (128MB by default) is commented out?

Thanks, will this also improve performance on your HUD?

Did you put this in the software forum? Thanks

I guess the value isn't used by default, but you should un-comment it out. I forgot this was the case by default, so anyone that has this value commented out, delete the '#' at the beginning of the line.

Yes. I use the HEM HUD and it tends to run a lot of PostgreSQL queries, so these optimizations helped a lot.

No, but I can cross-post this if people want.

For those of us stubbornly and inexplicably sticking with PT3, they recommend uncommenting and setting "maintenance_work_mem" to 1/5 of the "shared_buffers" value, and uncommenting and setting "work_mem" to 1/4 of the "maintenance_work_mem" value (or 1/20 of the "shared_buffers" value).

I tried making these changes in notepad but it won't let me save them. When I try to save the changes it says " cannot create the c:\Program files xxxxx.conf file"

What am I doing wrong?

I'm on 8.3 and can't find 'redirect_stderr' though I did find:


# This is used when logging to stderr:
logging_collector = on # Enable capturing of stderr and csvlog
# into log files. Required to be on for
# csvlogs.
# (change requires restart)

what are csvlogs and do I need them?

oh and thanks for posting this

Assuming you are in Vista

Go to Start|All Programs|PostgreSQL 8.3|Configuration Files

Right click on Edit postgresql.conf and select Run as Administrator. You will then be able to save the file.

Thank you jschell, my computer is very slow when running hem+hud on FTP, even if im only playing 4 tables.

Another tip that may help some of you:

my DB files were badly fragmented and wouldn't defrag, even when the service was stopped. However, I was able to defrag them using contig (see link below). I just dropped it in the directory and did a contig *. -- worked slick. I could have sworn I read about contig on 2+2 but I couldn't find the post with a search.

http://technet.microsoft.com/en-us/s.../bb897428.aspx

I believe this does the same as turning off 'redirect_stderr'. Generally, you don't need the log files unless you are the one doing the database programming and need to debug things or you need to send the logs to someone for more information about an error, etc. So, I believe you can turn this off if a bunch of logs are getting written out and taking up hard drive space for you.


np, glad to help.

Does this improve import speeds? I'm importing at like 17h/s despite having a very new machine.

I would think so, but I didn't note before and after speeds. Mainly I notice my computer being more responsive while multi-tabling while auto-importing and running the hud.

tyty

ok, i made the changes & i was able to save them, but admittedly I have absolutely no idea what I am doing.

What is commenting/uncommenting? was I also supposed to remove the # from the beginning of the lines I changed, or just change the values?

A 'commented' line is one that has a '#' at the beginning. These lines have no effect (generally used to explain something to someone who might be looking at the code, or provide an example of a typical value without actually using it). To 'uncomment' a line, simply remove the '#'. You should change the values and remove the '#' (if any) or the change will have no effect.

Do you have the latest version of HEM? I just did a mass import and averaged over 60h/s and my computer is over 3 years old.

Hey thanks for the tip! I have a quesiton maybe you can answer.

I heard that if you have two hard drives, it is best to have your database on the the 2nd hard drive, and just use that hard drive for only that purpose. Thoughts on that?

Also, I am wondering, if you do think that is a good idea, would it also make sense to use the 2nd hard drive for all the poker clients and HEM, or JUST postgres.

Thanks

It is best to have the database on a drive that is not also holding your virtual memory swap file. Since the latter is always getting read/write requests, there is a bottle neck for that drive. Having it on another drive means that PostgreSQL can have the full data pipe to and from the drive.

thanks for the tips op!


wondering this also, i have an external hd that i use only for music, should i just put pt3 on that hd?

What he said.

For the second part of your question, I don't think putting your poker clients and HEM on the second drive would improve performance much, if any. I say this because those applications will be loaded into memory when you start them up and they won't be reading from/writing to disk very much, while PostgreSQL is going to constantly be doing so. You could, however, get some sort of improvement if you setup the poker clients to save your hand histories on the second drive.

Thanks for the tips.

This. And I'm a tard but mine seems to have saved on vista although I didn't click on anything that said run as admin