PDA

View Full Version : postgresql.conf cannot be tuned



nokl
09-05-2011, 05:57 PM
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
2422124231


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
09-05-2011, 10:29 PM
Read:
http://forums.holdemmanager.com/manager-general/11194-top-10-hem-performance-increase-tips.html



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

nokl
09-06-2011, 01:52 AM
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
09-06-2011, 01:31 PM
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
09-06-2011, 04:53 PM
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
09-07-2011, 04:02 AM
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
09-07-2011, 06:48 AM
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
09-07-2011, 09:14 AM
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
09-27-2011, 05:16 AM
Try SQL 9.0 x64 bit from PostgreSQL: The world's most advanced open source database (http://www.postgresql.org) 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 (http://www.mydigitallife.info/2008/12/30/how-to-disable-and-turn-off-uac-in-windows-7/) )
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 (http://www.enterprisedb.com/products-services-training/pgdownload#windows)
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
09-27-2011, 06:54 AM
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.

Sarek
09-27-2011, 12:32 PM
All you can do with performance is described in thread on the top of the first page of this part of forum.