PDA

View Full Version : What can cause a Db to get corrupted



Shloogy
02-02-2010, 12:30 PM
Hi
After couple of millions imported hands, my db always get corrupted, and I get 100% import errors.

What could cause such thing? and how do I fix it w\o having to go through the process of importing the hands all over again?

fozzy71
02-02-2010, 04:04 PM
Which Operating System, Firewall, and Anti-Virus are you using? What version of HM? Where are these hands from? Does this happen during a bulk import of datamined hands? Are you importing all of them at once, or breaking them into smaller groups?

I would suggest a complete reinstall of postgresql and then import to a new DB in smaller groups. Then you can do DB maintenance in between the import groups, such as vacuum/analyze, stop postgresql, and defrag/reboot.

Shloogy
02-02-2010, 04:25 PM
OS win7 pro anti virus nod 32 HM latest version as always.
The hands are both from the poker room itself and from mined hands.
And yes they are broken into groups.

besides how is all that related to the corruption of the database? How could imported hands cause a database to be broken??


I would suggest a complete reinstall of postgresql and then import to a new DB in smaller groups. Then you can do DB maintenance in between the import groups, such as vacuum/analyze, stop postgresql, and defrag/reboot.


I've done that many times. I just dont understand what could cause these issues over and over, if I would have known I'd just avoid them.

Maybe HM is incapable of having more than x amount of hands on the DB.
I have no more than 10 million of hands though.

fozzy71
02-02-2010, 09:00 PM
....
besides how is all that related to the corruption of the database? How could imported hands cause a database to be broken?

I don't really know what causes DB corruption most of the time. It can be something obvious like a power outage or surge, or perhaps a computer crash. Often times there is nothing obvious. I just wanted to get at least some basics details about your system before I tried making any types of guesses.



I've done that many times. I just dont understand what could cause these issues over and over, if I would have known I'd just avoid them.

Maybe HM is incapable of having more than x amount of hands on the DB.
I have no more than 10 million of hands though.

It is hard to know what causes it. We have many customers with 10+ mm hand DB's. Is HM and your PostgreSQL files set to Run as Administrator? Have you disabled logging and deleted your log files? Do you performa regular DB maintenance such as full/vacuum/analyze, reindex, stop sql service, defrag, etc?

Shloogy
02-03-2010, 11:13 AM
How do I disable logging?
Which log files should be deleted? and how deleting these logs could resolve the issue?


I've discovered one more interesting thing:
When importing hands, from 10k HM only imported 100 hands, and there are no logs under my c:\HM folder while there are always logs when there are errors on import.





Thanks ahead,

fozzy71
02-03-2010, 01:28 PM
How do I disable logging?
Which log files should be deleted? and how deleting these logs could resolve the issue?

It frees up a lot of wasted space and lets your DB work easier/faster. Regular maintenance can also help reduce DB corruption.

I have been using HM for almost 3 years, and have reimported my hands numerous times using SQL 8.3 and 8.4, and 2 different computers. I perform DB maintenance 1 - 3 times per month. I have never had a DB corrupt. I have lost my SQL connection a few times, like other people often have happen to them, but never a damaged/corrupt DB that wouldn't import, or gave import errors and/or DB index errors when connecting.



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.
http://faq.holdemmanager.com/questions/100/Purge+Hands+from+Database
http://faq.holdemmanager.com/questions/197/Purge+Hands

Then you can vacuum/analyze - 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



I've discovered one more interesting thing:
When importing hands, from 10k HM only imported 100 hands, and there are no logs under my c:\HM folder while there are always logs when there are errors on import.

What site are these hands from and are they original hand history files or exported hands? What version of HM did you import with? Please www.7zip.org the hands and email them to me at fozzy@holdemmanager.net, with a link to this thread.

Importing error logs would appear in \rvg software\holdem manager\importing\logs

Shloogy
02-03-2010, 02:04 PM
I've done all of the above.


What site are these hands from and are they original hand history files or exported hands? What version of HM did you import with? Please www.7zip.org the hands and email them to me at fozzy@holdemmanager.net, with a link to this thread.


original hands. on other computer the are being imported fine, but on the computer where the db is "corrupted" than they fail to import.

And as said before, HM doesn't output any log file to the correspondent folders.

netsrak
02-04-2010, 04:15 AM
It always happens after impoting millions of hands?

Is your harddisk very full?

I have had some users reporting problems with Nod 32 and the database - can that be the reason?

Shloogy
02-04-2010, 05:40 AM
It always happens after impoting millions of hands?

yes


Is your harddisk very full?

not very, got few gigs free

I have had some users reporting problems with Nod 32 and the database - can that be the reason?

I have nod 32, but what exactly is the problem with it? can you attach link to these threads?

Nemesis
02-04-2010, 05:59 AM
Sorry to hijack/disturb the thread. Would just like to add that I have the same problem!

I have a 3 month old computer on which I am running windows7. I have the latest postgres and always update to the latest HEM. I have a 3M hand database. I import in groups and do full/vacuum/analyze, reindex in between (I havenīt been defragging much but my defrag program tells me I donīt need to after an analysis). I use Avast antivirus and I run HEM as admin.

Still my DB has been corrupt 3 times the last 3 months and it takes for ever to reimport 3M hands in groups (a week for me since it needs semi-monitoring)... so alish isnīt alone on this.

You can imagine how frustrating this is (and costly) Could it be that Iīm using an UTF coding? would you recommend ascii instead?

fozzy71
02-05-2010, 12:43 AM
Did you use our old postgresql 8.3.8 package that came in a zip file? Was it more stable for you? Next time you have a problem try using one of the older versions, perhaps.

I have had problems installing 8.4.x.x versions on some W7 machines and end up either installing 8.3.9 in a one click installer or I use the old, reliable 8.3.8 zip package we distributed.

http://www.holdemmanager.com/downloads/Postgres_8.3.9-v1.0.7.exe

http://www.holdemmanager.com/downloads/postgresql.zip


If/when you have to reinstall postgresql, make sure to delete the postgres user before you install it again.

1) Uninstall PostgreSQL from the Windows Control Panel.
2) Start > Programs > Accessories > Command Prompt > Right-Click > Run As Administrator

net user postgres /delete
3) Reboot.

Shloogy
02-05-2010, 10:22 AM
Tried all versions.

Also, if I export all the stats from the "corrupted" db, will it have impact on the new one?

Thanks in advance

fozzy71
02-05-2010, 03:18 PM
No, the DB corruption doesn't actually affect the hand histories. It affects how the hands are imported/displayed through the database. I always try to start by importing any archived/original hands first and then import my exported versions to be sure I have all the hands.