ODK Aggregate setup on DigitalOcean with MySQL

I love the new Digital Ocean setup!!
It's so fast and easy compared to the previous tomcat install.

Could the cloud config script have a version with MySQL in place of PostGresSQL?

Is there any reason this would not work? I could take a swipe at writing it, but I feel others with more linux platform experience could do it much quicker.

Thanks!!!!
Fred

1 Like

Glad you like the new setup! I’m curious why you prefer MySQL over PostgreSQL? We default to PostgreSQL because we feel it is the better DB and that’s what we develop against.

As to exactly how we proceed, we’d love your help, but we should decide an approach first.

I’m not excited about having two scripts that users pick from. I’d bias to providing a post-install script that let’s people switch to MySQL after the install? What do you think, @ggalmazor?

1 Like

I have more experience with MySQL, and I'm just a lot more familiar with the commands and syntax. I'm starting to learn PostgreSQL because that is also the default for the ODK-X deployments with docker. It may be a better database, but for now it just takes me longer to do everything. We tried to do an install of ODK-X with MySQL, but our first attempt is still not working.

2 Likes

Hi, @fvandyk! I have to agree with @yanokwa on saying that PostgreSQL is better suited for Aggregate's workload, but I understand you'd prefer MySQL to reuse your knowledge :slight_smile: (I'd prefer it too). I think that supporting MySQL is a good idea in general, and other users might even want to reuse their current infrastructure too.

Both ODK servers (Aggregate and Central) use PostgreSQL as a default. I'm not against creating a CloudConfig build for Aggregate+MySQL, but I don't think it's a good idea without investing more in tooling to make this options easy to manage during the installation.

We're working to make the new aggregate-cli command line tool the go-to tool for Aggregate deployments. I've added an issue to add support for MySQL deployments so that we don't forget about it and we can track general interest on this.

Can you quantify (or failing that, 'qualify'... :wink: )? I'm curious as to the difference...

Sure, I'll qualify it!

MySQL shows great performance on small, non-transactional, few-writes, many-reads scenario.

This is not the typical workload of an ODK server, which has to support a wider range of scenarios: bigger payloads per query, many transactional writes, big sequential reads...

Also, PostgreSQL is better suited for analytics because it supports modern SQL dialects, which MySQL 5 doesn't (MySQL 8 has just started to support basic stuff like CTEs, window functions...).