PDA

View Full Version : purge hands + vaccum problems please help



jasmin97
11-23-2010, 10:19 PM
hi
i run:

windows 7 x64
hem 1.11.05b
postgres sql 8.3

i purged 6 million hands last week from 2009
than i made a manual vaccum following exactly the faq : FAQ - Hold'em Manager Poker Tracking Software :: Vacuum Analyze Database (http://faq.holdemmanager.com/questions/88/Vacuum+Analyze+Database+)

1. it took half a day and i got the following message in the end:

"DETAIL: A total of 204800 page slots are in use (including overhead).
733520 page slots are required to track all free space.
Current limits are: 204800 page slots, 1000 relations, using 1269 kB.NOTICE: number of page slots needed (733520) exceeds max_fsm_pages (204800)
HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 733520.
Gesamtlaufzeit der Abfrage: 55754764 ms."

is that good or bad???

2. I had no speed problems but my intention was to save disk space however since the vaccum hem won't load the hands or it will take ages. It shows my sessions for the last 3 or whatever days in no time but below where the last 100 or 500 hands are supposed to be it is empty or takes 10 or more minutes to load. Same in the reports section. Graph is okay it just takes some time to graph 1mio+ hands.


what should i do? help needed!

Patvs
11-24-2010, 12:59 AM
Read:
http://forums.holdemmanager.com/manager-general/11194-top-10-hem-performance-increase-tips.html




How many hands do you have in total?
If < 10 million. Why not install SQL 9.0.1 64 bit and start with a new database, reimporting all your hands?

jasmin97
11-24-2010, 10:11 AM
thanx i know the thread but some of the thinks like buying an ssd is not really what i had in mind...

my database directory is 445GB in 3 different databases i know that postgres 8.3 is outdated but a rebuild could take ages i fear... is it worth it?

anybody an idea what the log message with the max_fsm_pages means or does???

Patvs
11-24-2010, 07:51 PM
You have 3 databases?
Do you only get the error:

"DETAIL: A total of 204800 page slots are in use (including overhead).
733520 page slots are required to track all free space.
Current limits are: 204800 page slots, 1000 relations, using 1269 kB.NOTICE: number of page slots needed (733520) exceeds max_fsm_pages (204800)
HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 733520.
Gesamtlaufzeit der Abfrage: 55754764 ms."

when you vacuum/analysed one of them? Or you get the error with all three?
If just with one (your main, biggest database?), it's probably caused by a corrupt index. Did you already reindex the entire database?
If the error remains.... you could try to create a new database. (or just leave it as it is, if all the HUD results are still correct)

(you already deleted all log files?)

jasmin97
11-25-2010, 08:38 AM
i run different databases for the 3 poker clients i use...

the problem is just with the one database i purged+vaccumed
it contains 138k players with up to 400k hands on few mass players... i have no idea how many hands the db contains overall...

i reindexed and won 15GB but nothing changed with the handssection... hud results are still perfect...


the restart u mentioned is postgres 9.x 64bit a lot better on a 64bit system???

Patvs
11-25-2010, 12:23 PM
I'm using it on my Windows 64 bit.
It's not faster than 8.3/8.4, but it definately seems more stable (and vacuum/analyse uses a different algorithm which is faster)

As a test.. since the 138k player database, isn't that large, install SQL 9.0.1 (64 bit) to port 5433 (which can co-exist with your current database) and import the hands into a 9.0.1 database

jasmin97
11-26-2010, 01:44 PM
I. its not getting better...

i noticed that after some time of playing postgres uses most of my computer resources when i quit hem the cpu usage is still 100% by postgres.exe

i tried the "stop service" withput any effect
the only way to get the computer back is complete reboot

however i increased the max_fsm_pages to 1200000
vaccum was now without problems and a lot faster (for whatever reason)
i did reindex

still the database doesn't show the hands
i will watch tonight what happens to the cpu usage...


II.
9.0.1 database: what is the best way? export the old db and than reimport into a new one?

Patvs
11-26-2010, 02:26 PM
SQL 8.4.x is compatible with any other version of 8.4.x (if you want to backup/restore a database).. but not with 9.0.x.

So when switching to 9.0.x you'll have to reimport all the original handhistories into a new database.