Reasons to choose psql over mysql or not
Hi All, We are thinking of putting our galaxy instance in a new more powerful environment. Galaxy is now on a single server containing a psql database which is migrated from mysql a few years ago. The new environment already houses a really nice backupped highly available mysql server. as galaxy admin I am quite reluctant of migrating back to mysql, the system admin is quite reluctant of installing this psql database with the same grade of backup and availability just for galaxy. I could only come up with the line "*PostgreSQL is much preferred since we've found it works better with our DB abstraction layer, SQLAlchemy <http://www.sqlalchemy.org/>.*" from the galaxy wiki, which is a rather slim argument. My question now is. Is Postgres still the preferred database or is Mysql now also a safe bet. For mysql we would have to migrate back, system management would have a lot of experience tuning databases and a high quality instance is already running. For Psql a new database instance would have to be installed, less knowledge of the database system is available but it is the preferred database. Could someone point out some more pros and cons in this question? Thanks, Eric -- Central Veterinary Institute of Wageningen UR (CVI) Department of Infection Biology PO box 65, 8200 AB Lelystad, NL Visiting address: ASG, Edelhertweg 15, 8219 PH Lelystad Tel: +31-(0)320-293391 Fax: +31-(0)320-238153 E-mail: eric.kuijt@wur.nl Web: http://www.cvi.wur.nl
Hi Eric Well, there is no official statement besides the one you have quoted below, but anyone in the Galaxy team I have spoken to recently told me to move to PostgreSQL. And that's why, I've been through the painful process of migrating our MySQL database to a PostgreSQl database. Actually, the switch for our production server just happened this morning - and I am still anxious whether I have done everything correctly. Our system admin also prefers MySQl. Hence I had to set up my own PostgreSQL server. And now, I am running the back-ups myself. For your case: why not moving the Galaxy server to the "new more powerful environment" but still connected to the old PostgreSQL server? I don't think the database server (unless it is sqlite) is ever the bottleneck, is-it? Regards, Hans-Rudolf On 07/27/2015 02:35 PM, Eric Kuyt wrote:
Hi All,
We are thinking of putting our galaxy instance in a new more powerful environment. Galaxy is now on a single server containing a psql database which is migrated from mysql a few years ago. The new environment already houses a really nice backupped highly available mysql server.
as galaxy admin I am quite reluctant of migrating back to mysql, the system admin is quite reluctant of installing this psql database with the same grade of backup and availability just for galaxy.
I could only come up with the line "/PostgreSQL is much preferred since we've found it works better with our DB abstraction layer, SQLAlchemy <http://www.sqlalchemy.org/>./" from the galaxy wiki, which is a rather slim argument.
My question now is.
Is Postgres still the preferred database or is Mysql now also a safe bet.
For mysql we would have to migrate back, system management would have a lot of experience tuning databases and a high quality instance is already running.
For Psql a new database instance would have to be installed, less knowledge of the database system is available but it is the preferred database.
Could someone point out some more pros and cons in this question?
Thanks,
Eric
-- Central Veterinary Institute of Wageningen UR (CVI) Department of Infection Biology PO box 65, 8200 AB Lelystad, NL Visiting address: ASG, Edelhertweg 15, 8219 PH Lelystad
Tel: +31-(0)320-293391 Fax: +31-(0)320-238153 E-mail: eric.kuijt@wur.nl <mailto:eric.kuijt@wur.nl> Web:http://www.cvi.wur.nl <http://www.cvi.wur.nl>
___________________________________________________________ Please keep all replies on the list by using "reply all" in your mail client. To manage your subscriptions to this and other Galaxy lists, please use the interface at: https://lists.galaxyproject.org/
To search Galaxy mailing lists use the unified search at: http://galaxyproject.org/search/mailinglists/
I ran into problems with the MySQL installation. It seems that the galaxy team and most people are testing the PSQL implementation. I've ran into a couple of issues with regards to the ORM creating columns in mysql that are not equivalent in size to PSQL. For example, the metadata column in the repository table does not support all the metadata that the MOTHUR repository generates when it is installed. This only occurred on MySQL and the fix was as simple as dropping the new record that was truncated, re-typing the column to support more metadata, and re-installing the repository. Also, MySQL prior to v5.5 had a default engine of MyISAM, and the ORM did not create tables and force InnoDB. MyISAM doesn't enforce table relations. This was a problem for us. PSQL is more scalable than mysql out of the box for relational queries (no modifications required). It's a bit of pain to manage at times compared to MySQL's simple commands, but you rarely have to touch it. Backups are essentially identically between the two RDBMSes. Iyad Kandalaft Acting Chief Bioinformatician in Biodiversity, STB Agriculture and Agri-Food Canada / Government of Canada Iyad.Kandalaft@Agr.gc.ca / Tel: 613-759-1228 / TTY: 613-773-2600 Bioinformaticien chef de la biodiversite interim, Direction générale des Science et de la technologie Agriculture et Agroalimentaire Canada / Gouvernement du Canada Iyad.Kandalaft@Agr.gc.ca / Tel: 613-759-1228 / TTY: 613-773-2600 -----Original Message----- From: galaxy-dev [mailto:galaxy-dev-bounces@lists.galaxyproject.org] On Behalf Of Hans-Rudolf Hotz Sent: July-27-15 9:36 AM To: Eric Kuyt; galaxy-dev Subject: Re: [galaxy-dev] Reasons to choose psql over mysql or not Hi Eric Well, there is no official statement besides the one you have quoted below, but anyone in the Galaxy team I have spoken to recently told me to move to PostgreSQL. And that's why, I've been through the painful process of migrating our MySQL database to a PostgreSQl database. Actually, the switch for our production server just happened this morning - and I am still anxious whether I have done everything correctly. Our system admin also prefers MySQl. Hence I had to set up my own PostgreSQL server. And now, I am running the back-ups myself. For your case: why not moving the Galaxy server to the "new more powerful environment" but still connected to the old PostgreSQL server? I don't think the database server (unless it is sqlite) is ever the bottleneck, is-it? Regards, Hans-Rudolf On 07/27/2015 02:35 PM, Eric Kuyt wrote:
Hi All,
We are thinking of putting our galaxy instance in a new more powerful environment. Galaxy is now on a single server containing a psql database which is migrated from mysql a few years ago. The new environment already houses a really nice backupped highly available mysql server.
as galaxy admin I am quite reluctant of migrating back to mysql, the system admin is quite reluctant of installing this psql database with the same grade of backup and availability just for galaxy.
I could only come up with the line "/PostgreSQL is much preferred since we've found it works better with our DB abstraction layer, SQLAlchemy <http://www.sqlalchemy.org/>./" from the galaxy wiki, which is a rather slim argument.
My question now is.
Is Postgres still the preferred database or is Mysql now also a safe bet.
For mysql we would have to migrate back, system management would have a lot of experience tuning databases and a high quality instance is already running.
For Psql a new database instance would have to be installed, less knowledge of the database system is available but it is the preferred database.
Could someone point out some more pros and cons in this question?
Thanks,
Eric
-- Central Veterinary Institute of Wageningen UR (CVI) Department of Infection Biology PO box 65, 8200 AB Lelystad, NL Visiting address: ASG, Edelhertweg 15, 8219 PH Lelystad
Tel: +31-(0)320-293391 Fax: +31-(0)320-238153 E-mail: eric.kuijt@wur.nl <mailto:eric.kuijt@wur.nl> Web:http://www.cvi.wur.nl <http://www.cvi.wur.nl>
___________________________________________________________ Please keep all replies on the list by using "reply all" in your mail client. To manage your subscriptions to this and other Galaxy lists, please use the interface at: https://lists.galaxyproject.org/
To search Galaxy mailing lists use the unified search at: http://galaxyproject.org/search/mailinglists/
___________________________________________________________ Please keep all replies on the list by using "reply all" in your mail client. To manage your subscriptions to this and other Galaxy lists, please use the interface at: https://lists.galaxyproject.org/ To search Galaxy mailing lists use the unified search at: http://galaxyproject.org/search/mailinglists/
participants (3)
-
Eric Kuyt -
Hans-Rudolf Hotz -
Kandalaft, Iyad