postgres 9.2

[Patvs]

There is no science, your speeds will always improve if you increase value "x", or decrease "value "y".
The problem is the suggested tweaks, improve performance for most users.

But I've seen reports, where wal_buffers = 32MB would decrease performance, or when changing a value to one of the recommended values PostgreSQL would still stop running.

I wouldn't spend too much time, trying to tweak the settings even further.
Read: https://de.pokertracker.com/forums/v...p?f=15&t=30450 where other users have already attempted to that without improving results.

[LeviJ88]

There is no science, your speeds will always improve if you increase value "x", or decrease "value "y".

Exactly. The problem is, postgres 8.4 can't be increased past shared_buffers = 1024MB. Changing it to 2048 makes postgres 8.4 unable to load. Whereas 9.0+ is 64-bit, and can utilize shared_buffers = 2048MB (and cache 4096)

But I've seen reports, where wal_buffers = 32MB would decrease performance

I read that wal_buffers saves in 16MB chunks, so that seems like the optimal setting for anyone. I seen a big difference from 2>4, slight increase from 4>16, and no difference, if not slightly slower, from 16>64

[Azalin]

LeviJ888 can you share the settings on your 9.2 installation? I am particularly interested in increasing NC speed.. I have 16GB of RAM etc. Also you wrote that at 9.4 speed was like 200h/s. The latest postgre I see its 9.3 beta. Was that a typo or you ment 9.2.4?

[LeviJ88]

Ah ya, typo. 9.2. I'm sure 9.3 would work too. The main thing is getting to 9.0 where they upgrade it to 64-bit.

After reading a bunch of help webpages, postgresql configuration definitions, reading postgresql forum posts, and tinkering with each setting and restarting my computer over and over again and comparing speeds, I believe I got the best results from these settings. Half of them dont seem to make much of a difference, I just copied the list over from my 8.4 .conf

C:\Program Files\PostgreSQL\9.2\data\postgresql.conf

shared_buffers = 2048MB
effective_cache_size = 4096
work_mem = 64MB
maintenance_work_mem = 64MB
commit_delay = 10000
checkpoint_segments = 128
checkpoint_completion_target = 0.9
wal_buffers = 16MB

These seem to be the most important and gave me a noticeable increase;

shared_buffers = 2048MB
effective_cache_size = 4096
wal_buffers = 16MB

work mem/maintenance mem could be a lot higher, but I never noticed much of a difference. They say to keep it small because if you're running this on a system where lots of users connect to your computer/database, the strain on your comp would be these stats x users, they used 30users as an example. Would be a lot of memory usage. Could probly set these to 512 or even 1024 since the way we're using postgres and HEM its usually just us, 1 user. I never tried. I didnt notice a difference from 32 to 64 or 128.


Notecaddy is incredibly fast, I get 500h/sec pretty consistantly when doing all 180 of my definitions. When I added 6 new definitions, it went over my entire 7m hand database going at 2300h/sec the whole way. pretty sick

[Azalin]

wow thanks for settings details. Did you back up and restored your database through HEM2 ? The other way seems to be through pgadmin but I am guessing that you'll need to copy and paste the many small files for each player HEM2 keeps in a separate directory and I am not sure that works 100%. Of course you might have simply reimported all hands in the 9.2.4 database..

[LeviJ88]

Doing backup the postgres way was a total pain and in the end it wouldnt restore properly into 9.2.

Doing backup in HEM was quick and easy. Backup. Close HEM. Switch to 9.2, Restart HEM, and restore.

You can see which server is running in Services (Start Menu > Search; "Services")

Also, from here you can change 8.4 start up type to manual (Right click > Properties > Startup Type) and make 9.2 Automatic. This way 9.2 starts with windows. (I still haven't uninstalled 8.4 or deleted my old databases, just in case.)

[Azalin]

thanks again for the info

[buranela]

Hi, there is a new option in 9.2 maintanance - 'cluster', what does it do? and should I use it?

[Patvs]

A table can be reindexed and clustered.
I can't find any FAQ/help page that actually explains what 'clustering' does. Read: Replication, Clustering, and Connection Pooling - PostgreSQL wiki

[buranela]

A table can be reindexed and clustered.
I can't find any FAQ/help page that actually explains what 'clustering' does. Read: Replication, Clustering, and Connection Pooling - PostgreSQL wiki

What settings do you use for maintenance ANALAZYE+FULL? or FREEZE as well?