PDA

View Full Version : Vacuum Analyze Database doesn't work



dqmien
02-22-2010, 09:36 AM
Hi,

I followed this tutorial (http://faq.holdemmanager.com/questions/88/Vacuum+Analyze+Database+) by the book. Nevertheless it doesn't seam to work.

when I click OK, there is a lot of text that is written in the messages box (for some reason I can't select it, so I can't tell you what is written in it) but after a few seconds it stop. The "ok" button is gray and I can't click on it, the only possibility is to click on "cancel"

It doesn't free any space on my computer.

My database has over 15 million datamined hands in it and I want to delete them all. Is there any other way to do it please ?


Thanks in advance for your help.

fozzy71
02-22-2010, 12:03 PM
Hi,

I followed this tutorial (http://faq.holdemmanager.com/questions/88/Vacuum+Analyze+Database+) by the book. Nevertheless it doesn't seam to work.

when I click OK, there is a lot of text that is written in the messages box (for some reason I can't select it, so I can't tell you what is written in it) but after a few seconds it stop. The "ok" button is gray and I can't click on it, the only possibility is to click on "cancel"

If you havent vacuumed in a long time, or ever, the first vacuum on a large DB can take a very long time. You need to let it finish, even if it says Not Responding. Eventually the button will change to Done.


It doesn't free any space on my computer.

Did you purge hands before you attempted a vacuum? If you dont let the vacuum finish, and then reboot, you wont see any difference. If you only vacuumed without purging hands, you won't see much of a difference.


My database has over 15 million datamined hands in it and I want to delete them all. Is there any other way to do it please ?

Is logging on your DB on or off? Did you follow the purge process?


Logging:

I would suggest you turn off logging on your new SQL installation as well as these other performance improvements in my signature - Top 10: HEM performance increase tips


To turn off logging, follow these instructions. This is the first thing I do anytime I install SQL for someone.

- Start > My Computer > Tools > Folder Options > View > UnCheck 'Hide Extensions for known file types' > OK

- C:\Program Files\PostgreSQL\data\postgresql.conf > Right-Click > Open With.. > NotePad > Check 'Always use this program'

- Scroll about half way down to ~Lines 245 - 260:

- The last line of this block of code, for the 'logging_collector', must be changed from On to Off.



------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------

# - Where to Log -

log_destination = 'stderr' # Valid values are combinations of
# stderr, csvlog, syslog and eventlog,
# depending on platform. csvlog
# requires logging_collector to be on.

# This is used when logging to stderr:
logging_collector = off # Enable capturing of stderr and csvlog


File > Save; File > Close

Start > Programs > PostgreSQL 8.3 > Reload Configuration.


- Start > My Computer > C:\Program Files\PostgreSQL\data\pg_log, or C:\Program Files (x86)\PostgreSQL\data\pg_log, if you have 64 bit Vista. Delete all files in that folder. And it might be a good idea to empty your recycle bin at that point, or use a 3rd party utility like CCleaner to clean up all your junk/temporary files. You want to do this before you get to the vacuum and defrag process.



Regarding database maintenance:

You should regularly purge mined hands in your database, after purge vacuum / full analyze the database via pgadmin.
FAQ - Hold'em Manager Poker Tracking Software :: Purge Hands from Database (http://faq.holdemmanager.com/questions/100/Purge+Hands+from+Database)
FAQ - Hold'em Manager Poker Tracking Software :: Purge Hands (http://faq.holdemmanager.com/questions/197/Purge+Hands)

Then you can vacuum/analyze - FAQ - Hold'em Manager Poker Tracking Software :: Vacuum Analyze Database (http://faq.holdemmanager.com/questions/88/Vacuum+Analyze+Database+#manual)

Before you defragment you should stop the PostgreSQL service:

Start > Programs > PostgreSQL 8.x > Stop Server

Use your favorite defrag tool. I recommend Smart Defrag.

After defrag I recommend you reboot your PC. If you need to access the DB before rebooting you can start the PostgreSQL service.

Start > Programs > PostgreSQL 8.x > Start Server

dqmien
02-22-2010, 12:31 PM
Thanks a lot for your very detailed answer.

Once again Fozzy, you're really the best :)

demonseed
03-10-2010, 08:30 AM
I have a similar question:

I have purged hands (about 14 million) and am doing the vacuum analyze database now since about 30 hours ;)

I realize it can take very long but the text (inside the maintain database window) is not changing anymore since like 5-6 hours (maybe longer as I was sleeping in the meantime).

My db is really huge and I have been lazy with vacuum analyzing (now the db teaches me a lesson lol)... so can this still be normal? My pc is still working really slow and the drive still seems to be doing something at least.

fozzy71
03-10-2010, 01:11 PM
How many hands were in the DB before you purged? Do you have logging turned off and the log files deleted? How big is your DB in GB's? \postgresql\8.x\data > right-click > properties.


It is really hard to be sure if it is still working or froze. SQL is designed by/for linux originally so they don't give a lot of thought to the windows side.

Open your task manager and sort the processes in descending order by CPU and Memory amounts. Post screen shots of each.

Capture a Screen Shot of your Desktop or the Active Window in Windows (http://graphicssoft.about.com/cs/general/ht/winscreenshot.htm)

To attach it to a thread in the forums, look below the post reply button when you start a new thread and you'll see a manage attachments button. Click this and attach the file.

demonseed
03-11-2010, 08:06 AM
I let it run and it finished now (only took over 2 days haha ;)).

Thanks for the answer.

fozzy71
03-11-2010, 11:00 AM
I recommend you vacuum every 10k hands. :p

A good regular maintenance schedule, that is easy to remember, is to vacuum the DB once a week, and vacuum/defrag once a month. If you do it regularly it should not take 2 days every time. I can't say for sure how long it will take, but it should be noticeably quicker the next time you vacuum. I would still probably wait to start it until you go to bed, as your DB is so large. Hopefully this next time will take <24 hours. :rolleyes:

martin dtc
10-27-2010, 06:30 AM
Hello,
I have a question as well about vacuuming after a big purge.
I didn't wanna try it without asking but..

Is it possible to play poker using Stars & HEM (with Hud of course) while performing a vacuum scan?

Will this slow down the performance?

Thank you,

Martin.

loustic
10-27-2010, 08:41 AM
Martin: Never use Holdem Manager if you're doing a vacuum analyse!!!

dqmien: For what I see in your first message, it appears you want to delete all these 15 million hands.

I've understood you want to delete all the hands, so the best way is by far to create a new database, because even with a purge, you'll have some remaining data in a couple of tables.

Also, vacuum / analyse is not done to delete your hands in the first place, but to optimize your database, after you've done a purge, or just after 10k hands played.

martin dtc
10-30-2010, 06:55 AM
My vacuum is running for about 72 hours now and its still busy. Even though I have a pretty fast PC.

How long will this take..

The last rules are:

CPU 4.74s/2.66u sec elapsed 187.73 sec.INFO: index "handhistories_pkey" now contains 3500000 row versions in 20000 pages
DETAIL: 0 index row versions were removed
922 index pages have been deleted, 922 are currently reusable.
CPU 0.15s/0.68u sec elapsed 10.69 sec.

Does some specialist know if there a lot of processes ahead or it is busy with one of the last ones?

Regards,

Martin

morny
10-30-2010, 03:23 PM
Shouldnt take anywhere near that long, its not ideal to force it to close but it looks like it has stop responding by the sounds of it

martin dtc
10-30-2010, 05:33 PM
it still changes text sometimes, it does responds to certain things..

I purged about 10 million hands before the vacuum

its now busy with index "playervsplayer_idx2", is this one of the last processes?