postgresql.conf cannot be tuned

[nokl]

Hi, I have a laptop, i5 2.3GHz, 4gb memory, Windows 7 x64, postgre 8.3.

When I have default postgresql.conf settings the import is too slooow (20-30 hands per second).

When I do a tuning through the database management import speed increases too 200-250 h/s but not for too long. The whole system is working very-very slowly until i stop postgre service.
When my database become large, import becomes even slower than without any tuning.
Of course I make housekeeping and disk defragmentation as often as i can, but i think that the problem is in the configuration file.

Here are the screenshots from pgAdmin server configuration
2011-08-30_205231.jpg2011-08-30_205332.jpg


As we can see some points are not increased, maybe the problem is here? I tried to use postgreSQL 8.4 but my system works very bad with it too.
What should i do to make my system work better when the hand histories are being imported?

[Patvs]

Read:
http://forums.holdemmanager.com/mana...ease-tips.html



Try SQL 9.0 x64 bit from PostgreSQL: The world's most advanced open source database and install it to a different port, like 5433.
Make sure NO program (firewall, antivirus, windows defender) is monitoring the SQL connection.

[nokl]

Oh i forgot to notice that i turn off any other programs (antivirus also is turned off) during the import, and those 10 tips were read first of all.
But thanks, i'll try 9.0

[fozzy71]

Hi, I have a laptop, i5 2.3GHz, 4gb memory, Windows 7 x64, postgre 8.3......

Unfortunately the problems you describe are somewhat common for laptop users with large databases because most of them come with 5,400rpm HDDs. If you had a 7,200rpm HDD (like most desktops, or premium laptops), or an even faster (and more expensive) SSD you would have much better database performance.

I have a 7 year old desktop with 7,200rpm HDD and a 2 year old desktop with an Intel SSD and there is no comparison in terms of performance with my database. I expect my 7 year old desktop (AMD 4200+, 2gb RAM) would outperform newer laptops with slower HDDs, in terms of database performance.

[nokl]

2fozzy Yeah, i do have a 5400 HDD, but maybe the problem is that postgre conf. file cannot be edited. As we can see on screenshots. Because usually import starts good (200-230 hh/s) and after 5-7 minutes all system starts to work very slowly.

[netsrak]

You should try to manually edit the conf file instead using pgadmin.
Stop the postgresql service, create a backup copy of the file and edit the original. (under Vista / Win 7 it may be necessary to edit another copy of the file and move it back to its original folder because of administrator rights).
Restart the postgresql service and test it.

[nokl]

I edited the conf. file this ways (before any editing i created a backup file of course):
1. Holdem manager tuning in database management
2. Manually
3. Using pgAdmin.

I did tried different combinations but as we can see on screenshots, wal buffers, shared buffers and effective cache size are not changing that way i want them to be changed.

[netsrak]

Then i guess those parameters are just not changeable?
We are not the in-deep postgresql tuning experts but i think 20-30 hands/sec on a standard PC with a large database is a normal speed.
HM2 will import hands much faster.

[nokl]

Try SQL 9.0 x64 bit from PostgreSQL: The world's most advanced open source database and install it to a different port, like 5433.

I try to do this and in the end of installation i get

Problem running post-install step. Installation may not complete corretctly. Failed to start the database server.


What should I do now? I've uninstalled previous version of postgre.


UPD: I read all of this

If you have Win7 (or Vista), consider the following:
1) PC name must be in English and "in one word" - requires reboot after changing.
2) The same for Windows user name. If not - create new Win user with EN name and reboot PC for this user when install Postgres. And he must have admin rights. After installation you can kill this new user - his mission is completed.
3) Secondary logon service must be set to "Autostart".
This is a standard windows service which is available on every windows computer. In the latest versions it is not started automatically. You need to start it via control panel > administrative tools > services.
4) UAC = OFF ( How to Disable and Turn Off UAC in Windows 7 « My Digital Life )
5) Disable firewall and antivirus when installing Postgres and HM. Or uninstall them at all.
6) All installations must run "as admin" (by right-click on file). And do them as first thing after PC reboot.
7) If you want to change install path for Postgres (or to set non-default folder for database cluster) - download "standalone" installation file from Download PostgreSQL | EnterpriseDB
8) If your Windows is not in English - you have to install Postgres not in default folder in Program_Files. Create new (example: c:\postgresql) first and select it when install.
9) Install Postgres and HM separarelly, one after one. As admin. If HM (when runs for the 1st time) says "no database" - simply create it via DBControlpanel.
10) If Postgres install fails - after uninstalling don't forget to kill windows user named "postgres":
Go to Start > Programs > Accessories > Command Prompt > Right-Click > Run As Administrator
In the window that appears please type the following and press enter:
net user postgres /del
exactly as shown. You should get a confirmation if it was successful.
11) Run all pokerclients "as admin" too.

[nokl]

Somehow I've installed this version of postgre, but cannot be tuned too. Import speed is 80 h\s for a new base. And it must be at least 200 h\s.