PDA

View Full Version : postgreSQL autovacuum daemon ?



_Loki_
03-24-2010, 01:02 PM
I was thinking that some of the DB problems I've seen in this forum are due to postgreSQL not being maintained. I was looking around the www today & discovered that if a busy DB isn't maintained the old data can become corrupt - I hadn't realised that could happen. I thought the performance dropped & dropped & that was the end of it...

In FAQ 89:
FAQ - Hold'em Manager Poker Tracking Software :: Advanced PostgreSQL Tuning (http://faq.holdemmanager.com/questions/89/Advanced+PostgreSQL+Tuning)

I found this link:
5-Minute Introduction to PostgreSQL Performance (http://www.westnet.com/%7Egsmith/content/postgresql/pg-5minute.htm)

In that link I found this link:
PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: Routine Vacuuming (http://www.postgresql.org/docs/current/interactive/routine-vacuuming.html)

In section 23.1.5 'The Autovacuum Daemon' I understand it to say that there is an option (or add-on) to maintain the postgreSQL DB with little user intervention

Does anyone know how to do this or is already doing it ?
Is it a good thing ?
Can maintenance occur while playing poker ? (not quite the same thing, but i know you can set some defrag utilities to always be 'on' & they do the defrag in periods of low disk activity)

jenkulle
03-24-2010, 02:26 PM
yeah I was just thinking about something like that yesterday

why isn't there a big red button for Maintenance... light clean up and complete !!

something the average user would use, because most of them don't do crap about their DB and don't even know they need to do it in the first place, but if you had a clear option in HEM it would help


I know their is a vaccum in the menu but it never worked for me and tell me to use pgadmin, wich I use but most won't... and also for reindex and dump/restore

fozzy71
03-24-2010, 07:53 PM
Regular Maintenance is important and I agree we need it integrated into the GUI and simpler.

I normally suggest to everyone I help, that they should vacuum every 10k hands, or to make it easy to remember, once a week. Then once a month, after they vacuum, they should reindex, stop postgresql, and defrag. This is a good thing to do Sunday or Monday night after you finish your session and before bed.

I vacuum/reindex/defrag way more than necessary, especially considering the low volume I play now. I have never had a DB just lose connection and I do lots of ridiculous install/uninstalls of my database \data folder, on XP and Vista.


Does anyone know how to do this or is already doing it ?
Is it a good thing ?

I have no experience with it


Can maintenance occur while playing poker ?

You should not try importing hands and using the HUD while this is going on. I would suggest you close HM completely before you do maintenance.

_Loki_
03-24-2010, 10:09 PM
Thanks Fozzy

my question re can I play poker while running maintenance was referring to using the autovacuum daemon doing it on auto all the time

I think it may be possible to do that. The daemon is smart & decides for itself when to vacuum & when to index. It waits for moments of inactivity i think

I wouldn't dream of doing a maintenance task myself while postgreSQL was running, but it seems to be possible & it might reduce the incidence of corrupt DB's

I agree with jenkulle (& you), but I was exploring the idea of maintenance being invisible & automatic within HM/postgreSQL

I bet there's loads of users who NEVER maintain their DB's - auto would be a good user-friendly option

fozzy71
03-25-2010, 11:28 AM
.....
I bet there's loads of users who NEVER maintain their DB's - ......

If I had to guess, I would say ~90% of users have NEVER vacuumed their SQL DB or used a Defragger. And 90% of those 10% that actually do DB maintenance don't know they should stop the postgresql service before defragging. :(

I will forward this thread to the tech team to be sure we work on having it added. I think a proper, integrated maintenance system, would reduce the number of damaged/disconnected DB threads we see on the forums.

_Loki_
03-25-2010, 11:38 AM
Thank you

jenkulle
03-25-2010, 01:35 PM
would also create a feeling that HEM runs faster for that 90% that never did a thing about it

thanks for your support