SQLite vs. MySQL

[tempus]

Hi,

I recently installed weewx using apt-get under Ubuntu 16.04.1 Server.  I then enabled the Simulator for testing while awaiting delivery of a USB logger for a Vantage Pro2 console.  All that went well.  Weewx is working as expected with the default SQLite database.

The hardware has been used only to host a personal cloud server that is online 24/7.  It has a multi-core processor, plenty of memory, and plenty of storage space.  MariaDB (MySQL-compatible) is installed.  I am very familiar with MySQL, but have never used SQLite.  I am wondering whether to switch to MariaDB before inputting real data.

1) Other than network accessibility, does MySQL provide any significant advantage in weewx applications (maybe with multiple weather stations, various extensions, or whatever)?

2) Other than simpler installation and administration, does SQLite provide any significant advantage in weewx applications (speed, maybe)?

3) I assume apt-get upgrade will work in the future without custom changes if weewx is reconfigured to use MySQL.  Is that true?

-Bob

[Andrew Milner]

1) No - multiple stations will still require multiple databases. Weewx and its extensions work with both databases as far as I am aware

2) No - KISS principles would indicate SQLite as being preferable to MySQL, even though I use MySQL

3) Yes - weewx caters for both SQLite and MySQL with no issues - the config is in a single file (weewx.conf) and can be changed at will.

[vince]

On Monday, January 9, 2017 at 11:07:33 AM UTC-8, tempus wrote:

2) Other than simpler installation and administration, does SQLite provide any significant advantage in weewx applications (speed, maybe)?

I'd lean toward yes.   It is much simpler to learn and it runs fast on much less capable hardware.   It's also much easier to extend and debug (at least for me). 

[Thomas Keffer]

sqlite is also easier to backup. All its state is held in a single, regular, file.

I've been using it for 10 years and 200 MB of data with no issues.

The big advantage of MySQL over sqlite is with multi-user, high-transaction throughput. That's not applicable in the weewx context.

-tk

--
You received this message because you are subscribed to the Google Groups "weewx-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to weewx-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[gjr80]

The only MySQl issue I can remember is that I thought one aspect of the MesoWx extension required MySQL. Not sure if that has changed with a couple of the user mods to the original MesoWx extension.

Gary

[Paul Bartholdi]

Hello,

An other difference that I like are: easier integration within shell scripts (I use it a lot withweewx!), - more functions, including your own sql functons. For me the choice between SQLite and MySQL was very clear in favor of the second, but I understand that is different for other users. Notice also that mysqldump is not much more difficult to use than copying a sqlite file...

-Paul