Questions about PG performace and HM2

[exculibrus]

Hi,

regarding to your sentese here's my questions from 2+2. The main reason why I ask is that I have big issue with slowing down my PC (freezing). It turns out almost everyone who I know have the issue.

My PC:
Software: Windows 7 Home Premium 64bit, PostgreSQL 9.2 64 bits (I was using also 9.0 and 8.4 but with this same result).
Hardware: SSD drive, 8GB DDR3, Intel Core i7 860 2.8GHz, ATI Radeon HD 4550, Gigabyte H55-UD3H.

Edit: My actuall PG configuration:
effective_cache_size - 2048MB
shared_buffers - 512MB
wal_buffers = 8MB
temp_buffers = 24MB
work_mem = 64MB
maintenance_work_mem = 256MB
commit_delay = 10000
checkpoint_segments = 128
checkpoint_completion_target = 0.9


To be clear I did everything what you advice:
-installed PG and HM2 as administrator with Windows Firewall turned off,
-don't use any antyvirus soft,
-Windows Defender is turned off,
-Added all exceptions to Windows Firewall (HudFuncsApp.exe, HoldemManager.exe , Easyhook files (exe and dll), blitz.dll, all ftp .dll, partyinject.dll, ip.dll, tablescannerdllloader, thirtytwobitipc,888.dll, speed.dll, all stars dll files, textboxhook.dll, all .exe files from TN2 folders, all .exe files from poker client folder, - Pg_ctl.exe, postgres.exe, stackbuilder.exe, pgAdminIII, input and output ports for PG) after what I turned it off anyway,
-turned off UAC,
-reinstalled .NET Framework,
-OS is fully update,
-Secondary Logon is set to automatic,
-now I have ENG OS so I did installed PG it in default localizatrion,
- for hudfuncapps.exe, HoldemManager.exe, tablescannerdllloader.exe, exe files from pokerclients folders, Postgres.exe, pg_ctl.exe, tableninja.exe I make them to run as admin for all users;
- HoldemMenager2, HoldemManager in Roaming folder, HM2Archiwe in C:/ and other folders like hand histories etc. make them not to read only, added users everyone and Network (PG user in 9.2) and gave them full control,
- Installed the newest drivers for graphic card from producent website,
- I have decktop compositions turned on,
- no opacity in HUD,
- use Auto-Z order,
- added <Key Name="ProcessWorkingSetBase" Value="500000000" /> <Key Name="ProcessWorkingSetHandAmount" Value="000" /> to Generals in HoldemManager.conf,
- I have disabled Note Caddy Pro or set to using 2 threats with Turbo Grabber off (depends what I need)
- disabled some off the aero theme components.


So now I'm back with my questions :
-May it be HM/PG is leaking connections? Failing to close connections after use, instead opening a new connection.
When I asked somebody about PG performace I receive this question:

Do you close the connections on your app? Or the DB crashes because of too many open connections?

Could it be the main reason why a lot of grinders throw away their monitors via windows? I found also this:

When running as a service, you may experience failures with more than approximately 125 simultaneous connections. This can happen because some of the libraries that PostgreSQL is dependent on are dependent on user32.dll which allocates memory from an area known as the Desktop Heap. The desktop heap is assigned to each logon session, with non-interactive sessions typically being assigned 512KB. Each postgres process that runs typically consumes approximately 3.2KB of desktop heap, which coupled with other overhead can exhaust the allocated heap at somewhere around 125 connections. This doesn't happen when running from the command line (or more precisely, happens with much higher numbers of connections) because the interactive logon session is typically allocated 3MB of Desktop Heap.

- after I will install 32-bit postgres do I have also instal PostgreSQL ODBC driver?

Because 32-bit versions of the PostgreSQL server only install a 32-bit libpq and pgsqlODBC, only 32-bit programs on the computer the server was installed on can use the database unless a 64-bit ODBC driver or libpq is installed as an add-on.

Unless you've also installed a 64-bit version of psqlODBC, a 32-bit install of PostgreSQL will only have a 32-bit ODBC driver. The 32-bit ODBC driver may only be used by 32-bit programs, and will not show up in the 64-bit ODBC administrator.

This gets confusing because on 64-bit windows c:\windows\system32\odbcad32 is the 64-bit ODBC driver administrator, despite the name. This is a historical artifact of Windows development. Apparently many apps and installers depend on odbcad32.exe having that name and path, so Microsoft landed up being stuck with it despite the now-stupid name, which made sense back in the 16- to 32-bit transition but makes none now

- how big should be work_mem if queries using by HM2 are very simple?
You advice to set it to 128MB but if the queries are so simple isn't it better to set it to let's say 16MB?

-effective_cache_size - is it reasonable to set it to 2/3 of available RAM? and what should I thnik when thinkin about available RAM, whole RAM, available RAM when I'm running my PC in normal way (turing the sessions with HM2 turned of)? Or available RAM is ram left after dedicating some values to other processes?


- autovacuum - should I have turned it on or off? Other way, is writes constant or batch? If I have to turn this on, how should I do that - I mean parameters for auto_vacuum like how often etc.


- multiple_autovacuum_workers - what should I understand by advice to set it to no more than 1/2 cores?



I understand when HM2 is using PG as DB server it's not your fault PG is configured for low performace but could you make some guide like a PG performance for fishes. I don't mean giving people clues in way set it to, set it as and should be ok. I mean it would be nice to explain what is some parameter mean, how depended from each other are for example sharing_buffers and effective_cache_size. I want to eat PG but all the time I ready one sentence about PG I have to open dozens new tabs in my web browser and search for informations to understand that sentence. It would be helpful specially a lot of grinders would set PG to best performance and not everyone have a knowledge about DBs or atleat postgre. I may be wrong but what actually u do is:

yes, we made commercial soft which is using open-source technology but it's not our fault the free soft needs to be professionally configured. U have to configure it and that's your problem, there's everything good from our side. GL!

Don't take it wrong because I understand it's not so easy but 95% users don't. When you can read at some poker social network one of the network owners had wrote " HM2 (and TN2) is a scam " and when you hear complainments again, again and again that's not a very good image for your company. I know dozens of grinders and nobody every said: buy HM2, that's great soft! Instead I hear all the time thinks like this: "LOL, u use HM2?! buy PT4 and make your life easier".
I wrote this because I dream about the day when I will say: "LOL, throw away PT4 and buy HM2 you donkey".

Regards,
HM enthusiast.

[udbrky]

What is the hard drive? How big is it? How big is your database? How much free space is there?

[exculibrus]

Samsung 840 EVO 120GB. DB never been larger than 2GB but right now I have new DB and hands only from todays session. I have 66GB of 100GB free space (11GB for overprovisioning, 9GB missing I have no idea where). Before I never have less then 50GB of free space as on SSD drive I'm storing only OS and software.


edit: at todays session even with this small DB (8MB HM2Archive folder and 57MB shown by pgAdminIII) I had to make full vacuum with analyze every ~3hours and once restart HM2 when vacuum didn't helped.

[udbrky]

Out of HM1, HM2, PT3, HM2 has by far the smallest DB's.

Some SSD's now use compression automatically. The Truth About SSDs and Compression

You can also use NTFS compression on the DB folder in roaming and the postgres\data\base folder to make it smaller:

Best practices for NTFS compression in Windows

You can put your postgres data folder and the HM2 database parent folder on a different drive.

In HM1 there are a lot of tables that deal with a specific hand played by a specific player. In HM2 we don't have these tables, instead, for player related hand data, we use a flat file approach. Each player has his own folder and each file represents a single day. Each line within the file is a tokenized version of a single hand with incredible amounts of detail. Your overall HM2 spaced used (DB + Files) is about 2/3 of HM1 and we store probably 2-3 times as much info plus it can be accessed many, many times faster and allows us to do some things that wouldn't be possible otherwise, many of which are yet to come. If you don't want the space in your Roaming folder due to C: file space or something like that, we do give you the option of storing this data anywhere

General rule is 1 million hands = 10 GB.

In reality it's: 1 million hands
HM1: 6.8 GB
HM2: 4.3 GB
PT4: 13.6 GB

So with a 10 million hand database you need as SSD of at least 60 GB (Windows) + 100 GB = 160 GB.
We use 1 million = 10 GB (instead of 6.8) because when you want to perform a vacuum/analyse or backup/restore a database it will require a LOT of disk space to perform such a task.

You can also run tree size free to locate wastes of space:

TreeSize Free - Quickly Scan Directory Sizes and Find Space Hogs

Please see this guide to optimize your SSD:

Can You Get More Space Or Speed From Your SSD? - Optimizing Precious Solid-State Storage

Here is another guide:

Super-Fast SSDs: Four Rules for How To Treat Them Right « IT Expert Voice

[exculibrus]

Thank you udbrky for answer but what about other questions? The size of DB is no matter as I had that same issue with 8MB DB... I'd love to implement advices from the articles but I don't need to do it at all. I just want to HM2 work as smooth as it's working by first hour of every session...

[exculibrus]

Ok. I made changes to postgresql.conf file:

everything is set as default except for:
-shared_buffers: 1024MB
-effective_cache_size: 4096MB
-work_mem: 4MB
-maintrance_work_mem: 256MB

Tommorow I will see how that settings perform. Can you reccomend some solution for performance tests in other way than while playing?

[Patvs]

Performance tests: create a new empty database, and compare import speeds when importing a lot of handhistories

[exculibrus]

Thank you, what's about other/earlier questions?

[Patvs]

Earlier questions: I replied to some on 2+2, but those were just my experiences.

There was a similar thread in 2012:

>
>
>
>
>My complaint isn’t about the admin-rights on the windows system or 
>disabling UAC for HM2. I do see why HEM2 would want to have those rights, 
>but I still don't see one reason why HEM2 wants superadmin-rights on my 
>database.
>I'm sitting here and wondering what your hem2-developer is trying to do on 
>my database server.
>
>-) creating tables all the time that are already existing?
>- ) 87 database connections just to start up the program seems a bit 
>excessive, esp. when all you do with the connections is to close them a 
>second later, most of them without even sending one sql statement).
>-) alter table to owner postgres for all new objects. Why? You are logged 
>in on the database and managed to create the table, why is this not enough, 
>why the deep desire to change it to postgres? If you are logged in as 
>postgres you could care less about who owns the table, but >it seriously 
>messes with people that are trying to protect their other databases from 
>(other) questionable products.
>-) not only does HEM2 connect to the database I told him to connect, he 
>also wants 20 connections to the database postgres and another one to 
><dbusername>. There's nothing in those two databases and there is really no 
>need to connect to those two.
>-) during create database, the sql statements include changing ownership of 
>the objects to the user 'postgres'. Why exactly does HEM2 require 
>superuser-privileges, creating databases and doing everything a user might 
>want to do in it should be enough for everyone. I can't think >of one 
>legitimate reason in this context to change the ownership in a create 
>table-statement, esp. not to a superuser.
>-) unable to use an existing database in the setup wizard if it was created 
>under a different database user, even if the ownership of the database was 
>changed to the new username. Heck you notice the database is existing, just 
>allow me to use it please?
>
>
>I have the impression that there is a lot of room for improvements in the 
>database management of your product, which would result in being safer and 
>easier to use and possibly a bit faster too.

I've forwarded this thread to the programmers to respond to. (I couldn't find a reply to the old thread, on the issue of leaking connections)

[exculibrus]

So... do you have any answer?