search

gi-kent-content / hca-tuatara

A little python-based web site for helping the wranglers and others track incoming single cell projects as they are brought into HCA. The Tuatara has an extra eye which comes in handy in this project.
MIT License
2 stars 1 forks source link

Switch to InnoDB for transactions #19

Closed claymfischer closed 4 years ago

claymfischer commented 4 years ago

@diekhans would like to move away from MyISAM to InnoDB for transactions. Foreign key constraints will help us to ensure linking is valid. MyISAM does not check constraints, even if declared.

diekhans commented 4 years ago

yes, and also atomic commits across the tables ensure consistency if there is a crash (bug or system problem).

There is a reason the world's financial systems run on ACID rather than ReST.

galt commented 4 years ago

Django documentation suggests they like Postgres because it can create transactions around db table structural changes (schema alterations), something that mysql apparently cannot do even with innodb.

Of course most of our experience is with mysql and myisam. But with the exception of our cart, we are not doing a lot of update on the ucsc genome browser.

diekhans commented 4 years ago

Postgresql is amazing, however we don't have a need to support schema changes while running.

All that is require are basic transactions and foreign key constraints to make application integrity easy. InnoDb differences are minor, mostly to do with admin and backup.

I know a lab that updated to Maria which switch their default tables on their browser mirror to database to InnoDb. They continued to load locally produced tables and didn't even know it had changed.

InnoDb will be ideal. Admins should be able to help with configuration and backup.

galt notifications@github.com writes:

Django documentation suggests they like Postgres because it can create transactions around db table structural changes (schema alterations), something that mysql apparently cannot do even with innodb.

Of course most of our experience is with mysql and myisam. But with the exception of our cart, we are not doing a lot of update on the ucsc genome browser.

-- You are receiving this because you were mentioned. Reply to this email directly or view it on GitHub: https://github.com/HumanCellAtlas/hca-tuatara/issues/19#issuecomment-548927316 Django documentation suggests they like Postgres because it can create transactions around db table structural changes (schema alterations), something that mysql apparently cannot do even with innodb.

Of course most of our experience is with mysql and myisam. But with the exception of our cart, we are not doing a lot of update on the ucsc genome browser.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub, or unsubscribe.*

galt commented 4 years ago

OK. I have converted my dev db hcatGalt on hgwdev to innodb and it worked fine. Then I carefully converted hcat on hgwdev to innodb also. Then I went to ec2 and ran my updateDb.sh script and it pulled in the new tables with the new engine innodb. Then I upgraded Jim's kentDjangoTest5 to save him time. Of course I made sql dumps of those databases before converting.

galt commented 4 years ago

done. we are using innodb.