7 ms·

We use a combination of SQLite and a sharding frontend service. One SQLite database file per device, one table per sensor, table contents are timestamp and meas

We use a combination of SQLite and a sharding frontend service. One SQLite database file per device, one table per sensor, table contents are timestamp and measured value. As simple as it gets, easy to scale, and damn fast. But try telling people you're using SQLite to store critical data...

otterley 9y ago
What's your replication/HA story?
- j_s 9y ago
  Yes, collecting/aggregating for querying seems like the sticking point.
  - tzakrajs 9y ago
    What does collecting or aggregating have to do with replication/HA?
    - j_s 9y ago
      It was my improvisational approach to pointing in the right direction. https://en.wikipedia.org/wiki/Yes,_and... https://en.wikipedia.org/wiki/Yes,_and.... I don't really see how replication/HA is an issue in this setup beyond normal file system replication/HA (which seems to be a solved "spend money on NAS" problem in the enterprise world). If your filesystem isn't reliable / delivering what's needed then you're gonna have a bad time no matter the storage approach; I doubt some unusual option is involved though it would be cool to be proven wrong. The only time the filesystem could even potentially have read/write conflicts (assuming 1-to-1 correlation between devices writing to files) is when querying. I did wind up muddling the terminology in the interest of jumping to the actual problem.
- tomc1985 9y ago
  Wouldn't be hard to rsync that data to rotating s3 buckets/nfs shares/ftp drops/whatever
  - otterley 9y ago
    Those are backups/DR. HA is for when a database instance fails so you can keep on trucking as though nothing happened.
srcmap 9y ago
Just tell them SQLite is used by ALL Android / iOS phones (billions of them) to store user's critical data. I have not heard of any cases of SQLite failure in phone, or anyone needing a DB admin to recover their phone contact data.
- j_s 9y ago
  > I have not heard of any cases of SQLite failure in phone, or anyone needing a DB admin to recover their phone contact data. For a long time (until API level 11), Android automatically silently deleted corrupted SQLite databases. https://android.stackexchange.com/questions/12388#33896 https://android.stackexchange.com/questions/12388#33896 https://stackoverflow.com/questions/7764943 https://stackoverflow.com/questions/7764943 I learned the hard way to limit SMS storage and install Jan Berkel's SMS Backup+ https://github.com/jberkel/sms-backup-plus/#readme https://github.com/jberkel/sms-backup-plus/#readme after losing my first Android SMS database (this may or may not have been SQLite's fault). https://www.sqlite.org/howtocorrupt.html#_bugs_in_sqlite https://www.sqlite.org/howtocorrupt.html#_bugs_in_sqlite
  - j_s 9y ago
    Specifically (can't edit above post -- too slow): https://issuetracker.google.com/issues/36911900#comment457 https://issuetracker.google.com/issues/36911900#comment457 >the SQLite packaged with Android is being upgraded to version 3.6.22 in Android 2.2. I think that the SQLite database corruption bug linked in comment 451 has been fixed in Android 2.2. So _if_ that's what's causing the text message database corruption and deletion, then this bug will probably be fixed in Android 2.2 http://sqlite.1065341.n5.nabble.com/Android-database-corruption-td59563.html http://sqlite.1065341.n5.nabble.com/Android-database-corrupt... >Statically link your application against SQLite 3.6.23 instead of using the SQLite 3.5.9 that is found on Android. The bug you are hitting was fixed in SQLite 3.6.2 Contrast https://web.archive.org/web/20110219041419/https://www.sqlite.org/howtocorrupt.html https://web.archive.org/web/20110219041419/https://www.sqlit... (Feb 2011) vs. https://web.archive.org/web/20110729152833/https://www.sqlite.org/howtocorrupt.html https://web.archive.org/web/20110729152833/https://www.sqlit... (July 2011), which could be a glitch in the Internet Archive (a partial copy of the oldest record?) but adds "Though SQLite is resistant to database corruption, it is not immune" and introduced an extensive history of known issues. Sqlite is amazing and its development history is further testament to its current reliability, especially when used correctly!
gregmac 9y ago
Wow, that's a pretty neat approach. I have a bunch of questions, if you don't mind.. Do you do filesystem replication or something else for HA? Is this running in one of the cloud cloud IaaS providers or something else? Why did you go with this approach? Eg: Was it a deliberate decision after evaluating other options, a "this is what we know" type situation, or did it grow organically from something smaller? How long has it been running? Whats the rough size of data you have (GB/TB, number of servers, number of DB instances)? Any reliability problems? If you were building this service from scratch, would you do it this way again?
- lbruder 9y ago
  Don't mind at all. ATM we're using a simple VMWare server backing up the virtual HDD every night, with a dirty hack thrown in (no full backups on weekends as that's when the bigger jobs run, so I dump the current day to a CSV on a smaller virtual HDD that's still saved daily). We're running on our own systems as our customers are pretty paranoid about where their data is sent. The history of this system is pretty organic, lots of experimentation. Started with Postgres, but that was too slow if a select statement returns tens of thousands of rows. MariaDB was way faster but became erratic once we passed about 2 billions of records. Now with SQLite we are at about 180 GB, about three years of data so far, so not really "big data" territory yet. Constantly looking for better alternatives. InfluxDB looks promising, but maybe we'll switch to a "hot/cold" approach instead, keeping whole "cold" days as JSON objects in JSONB fields in Postgres again while storing "hot" data in SQLite. We'll see... So far the system has been rock solid. In the last few weeks we went live for real, now storing about 2k records a second. Not a problem so far.
  - kgc 9y ago
    Wait, so to be clear, you moved from PG > Maria > SQLite?
creeble 9y ago
Simpler than a log file? What's the significant advantage of SQLite vs just appending to a file?
- rpedela 9y ago
  SQL query support?
- eloff 9y ago
  Transactions? Crash data safety? SQL queries? Portability?
  - creeble 9y ago
    I don't see how any of those apply for a two-column 'db' of timestamp and data.
    - jmkirby 9y ago
      Except.. compliance staff who want to use some ahem wonderful standards complaint reporting tool. There's just one solitary mention of Informix in the whole discussion, as I'm typing this. And that's a comment lumping Informix with everything else commercial and expensive. I thought Informix owned the embedded dB space at a certain rollout complexity. Remember the backups... Informix speaks json too, including at some quite low level primitives. Whether you buy a product like Informix Edge plus tools or not, depends on what is sending the time series data.
    - rpedela 9y ago
      It is database per device and table per sensor, not just one table. With SQL you can do joins, aggregations, etc. If you just want to log it, sure use a log file. As soon as you want to use the data somehow, you need SQL or your own log file parsing and query code. Personally, I rather just use SQL.
      - creeble 9y ago
        If you’re storing from multiple sensors with multiple write processess, you will have concurrency problems if they’re in the same db with SQLite. I’m not referring to manipulating or even querying the data after it’s stored. I just don’t see how saving the original data in SQLite buys you anything, if it needs to be collected into a db for queries, etc, anyway.
baldfat 9y ago
> But try telling people you're using SQLite to store critical data... SQLite is solid and trusted by many but for some unknown reason some people refuse to see it as a solution for their needs. I have seen many databases that could easily been replaced by SQLite.
- wenc 9y ago
  I looked into SQLite a while ago, and it wasn't so much its robustness that was the deal-breaker, but more the fact that it couldn't handle writes from parallel processes from multiple users (i.e. concurrency) like a normal SQL database could. I see SQLite as a file-format with a SQL interface.
  - dfox 9y ago
    There is Berkeley DB/SQL (now sadly owned by Oracle), which is essentially Sqlite with BDB instead it's original B-tree implementation, that supports multiple parallel writers. Also Firebird can be used as in-process embedded database and supports multiple writers (in fact in the server mode it uses same file-locking based synchronisation mechanisms as in embedded mode) On the other hand it is somewhat rare to see aplication that really needs multiple writers and would not be better served by full fledged database server.
    - fpoling 9y ago
      Berkley DB is not as reliable as SQLite. Subversion, before switching to own storage, where known for data corruption problems attributed to Berkley DB.
      - amyjess 9y ago
        I remember the old days when people would refer to berkdb as "Berserkley" because it tended to go berserk on your data. It's always had a bad reputation for reliability.
  - da_chicken 9y ago
wenc 9y ago
That's a good pattern for straight data retrieval. Unfortunately if you need to do aggregated queries on all of the SQLLite tables, things may be challenging. But if you could somehow connect Spark to a folder (on a distributed FS) of these SQLite files... Edit: Also SQLite has a limitation that only one process can write to it at a given a time. For this particular use case though, it shouldn't be a problem unless you have rewrites coming from various sources (which can happen when correcting data)
- foota 9y ago
  I mean, depends on the aggregation you need imo. Shouldn't be too hard (tm) to rig up some distributed query pipeline. (as long as you are ok with coding per query, instead of the convenience of sql)

otterley 9y ago

What's your replication/HA story?

j_s 9y ago
Yes, collecting/aggregating for querying seems like the sticking point.
- tzakrajs 9y ago
  What does collecting or aggregating have to do with replication/HA?
  - j_s 9y ago
    It was my improvisational approach to pointing in the right direction. https://en.wikipedia.org/wiki/Yes,_and... https://en.wikipedia.org/wiki/Yes,_and.... I don't really see how replication/HA is an issue in this setup beyond normal file system replication/HA (which seems to be a solved "spend money on NAS" problem in the enterprise world). If your filesystem isn't reliable / delivering what's needed then you're gonna have a bad time no matter the storage approach; I doubt some unusual option is involved though it would be cool to be proven wrong. The only time the filesystem could even potentially have read/write conflicts (assuming 1-to-1 correlation between devices writing to files) is when querying. I did wind up muddling the terminology in the interest of jumping to the actual problem.
tomc1985 9y ago
Wouldn't be hard to rsync that data to rotating s3 buckets/nfs shares/ftp drops/whatever
- otterley 9y ago
  Those are backups/DR. HA is for when a database instance fails so you can keep on trucking as though nothing happened.

srcmap 9y ago

Just tell them SQLite is used by ALL Android / iOS phones (billions of them) to store user's critical data. I have not heard of any cases of SQLite failure in phone, or anyone needing a DB admin to recover their phone contact data.

j_s 9y ago
> I have not heard of any cases of SQLite failure in phone, or anyone needing a DB admin to recover their phone contact data. For a long time (until API level 11), Android automatically silently deleted corrupted SQLite databases. https://android.stackexchange.com/questions/12388#33896 https://android.stackexchange.com/questions/12388#33896 https://stackoverflow.com/questions/7764943 https://stackoverflow.com/questions/7764943 I learned the hard way to limit SMS storage and install Jan Berkel's SMS Backup+ https://github.com/jberkel/sms-backup-plus/#readme https://github.com/jberkel/sms-backup-plus/#readme after losing my first Android SMS database (this may or may not have been SQLite's fault). https://www.sqlite.org/howtocorrupt.html#_bugs_in_sqlite https://www.sqlite.org/howtocorrupt.html#_bugs_in_sqlite
- j_s 9y ago
  Specifically (can't edit above post -- too slow): https://issuetracker.google.com/issues/36911900#comment457 https://issuetracker.google.com/issues/36911900#comment457 >the SQLite packaged with Android is being upgraded to version 3.6.22 in Android 2.2. I think that the SQLite database corruption bug linked in comment 451 has been fixed in Android 2.2. So _if_ that's what's causing the text message database corruption and deletion, then this bug will probably be fixed in Android 2.2 http://sqlite.1065341.n5.nabble.com/Android-database-corruption-td59563.html http://sqlite.1065341.n5.nabble.com/Android-database-corrupt... >Statically link your application against SQLite 3.6.23 instead of using the SQLite 3.5.9 that is found on Android. The bug you are hitting was fixed in SQLite 3.6.2 Contrast https://web.archive.org/web/20110219041419/https://www.sqlite.org/howtocorrupt.html https://web.archive.org/web/20110219041419/https://www.sqlit... (Feb 2011) vs. https://web.archive.org/web/20110729152833/https://www.sqlite.org/howtocorrupt.html https://web.archive.org/web/20110729152833/https://www.sqlit... (July 2011), which could be a glitch in the Internet Archive (a partial copy of the oldest record?) but adds "Though SQLite is resistant to database corruption, it is not immune" and introduced an extensive history of known issues. Sqlite is amazing and its development history is further testament to its current reliability, especially when used correctly!

gregmac 9y ago

Wow, that's a pretty neat approach. I have a bunch of questions, if you don't mind.. Do you do filesystem replication or something else for HA? Is this running in one of the cloud cloud IaaS providers or something else? Why did you go with this approach? Eg: Was it a deliberate decision after evaluating other options, a "this is what we know" type situation, or did it grow organically from something smaller? How long has it been running? Whats the rough size of data you have (GB/TB, number of servers, number of DB instances)? Any reliability problems? If you were building this service from scratch, would you do it this way again?

lbruder 9y ago
Don't mind at all. ATM we're using a simple VMWare server backing up the virtual HDD every night, with a dirty hack thrown in (no full backups on weekends as that's when the bigger jobs run, so I dump the current day to a CSV on a smaller virtual HDD that's still saved daily). We're running on our own systems as our customers are pretty paranoid about where their data is sent. The history of this system is pretty organic, lots of experimentation. Started with Postgres, but that was too slow if a select statement returns tens of thousands of rows. MariaDB was way faster but became erratic once we passed about 2 billions of records. Now with SQLite we are at about 180 GB, about three years of data so far, so not really "big data" territory yet. Constantly looking for better alternatives. InfluxDB looks promising, but maybe we'll switch to a "hot/cold" approach instead, keeping whole "cold" days as JSON objects in JSONB fields in Postgres again while storing "hot" data in SQLite. We'll see... So far the system has been rock solid. In the last few weeks we went live for real, now storing about 2k records a second. Not a problem so far.
- kgc 9y ago
  Wait, so to be clear, you moved from PG > Maria > SQLite?

creeble 9y ago

Simpler than a log file? What's the significant advantage of SQLite vs just appending to a file?

rpedela 9y ago
SQL query support?
eloff 9y ago
Transactions? Crash data safety? SQL queries? Portability?
- creeble 9y ago
  I don't see how any of those apply for a two-column 'db' of timestamp and data.
  - jmkirby 9y ago
    Except.. compliance staff who want to use some ahem wonderful standards complaint reporting tool. There's just one solitary mention of Informix in the whole discussion, as I'm typing this. And that's a comment lumping Informix with everything else commercial and expensive. I thought Informix owned the embedded dB space at a certain rollout complexity. Remember the backups... Informix speaks json too, including at some quite low level primitives. Whether you buy a product like Informix Edge plus tools or not, depends on what is sending the time series data.
  - rpedela 9y ago
    It is database per device and table per sensor, not just one table. With SQL you can do joins, aggregations, etc. If you just want to log it, sure use a log file. As soon as you want to use the data somehow, you need SQL or your own log file parsing and query code. Personally, I rather just use SQL.
    - creeble 9y ago
      If you’re storing from multiple sensors with multiple write processess, you will have concurrency problems if they’re in the same db with SQLite. I’m not referring to manipulating or even querying the data after it’s stored. I just don’t see how saving the original data in SQLite buys you anything, if it needs to be collected into a db for queries, etc, anyway.

baldfat 9y ago

> But try telling people you're using SQLite to store critical data... SQLite is solid and trusted by many but for some unknown reason some people refuse to see it as a solution for their needs. I have seen many databases that could easily been replaced by SQLite.

wenc 9y ago
I looked into SQLite a while ago, and it wasn't so much its robustness that was the deal-breaker, but more the fact that it couldn't handle writes from parallel processes from multiple users (i.e. concurrency) like a normal SQL database could. I see SQLite as a file-format with a SQL interface.
- dfox 9y ago
  There is Berkeley DB/SQL (now sadly owned by Oracle), which is essentially Sqlite with BDB instead it's original B-tree implementation, that supports multiple parallel writers. Also Firebird can be used as in-process embedded database and supports multiple writers (in fact in the server mode it uses same file-locking based synchronisation mechanisms as in embedded mode) On the other hand it is somewhat rare to see aplication that really needs multiple writers and would not be better served by full fledged database server.
  - fpoling 9y ago
    Berkley DB is not as reliable as SQLite. Subversion, before switching to own storage, where known for data corruption problems attributed to Berkley DB.
    - amyjess 9y ago
      I remember the old days when people would refer to berkdb as "Berserkley" because it tended to go berserk on your data. It's always had a bad reputation for reliability.
- da_chicken 9y ago

wenc 9y ago

That's a good pattern for straight data retrieval. Unfortunately if you need to do aggregated queries on all of the SQLLite tables, things may be challenging. But if you could somehow connect Spark to a folder (on a distributed FS) of these SQLite files... Edit: Also SQLite has a limitation that only one process can write to it at a given a time. For this particular use case though, it shouldn't be a problem unless you have rewrites coming from various sources (which can happen when correcting data)

foota 9y ago
I mean, depends on the aggregation you need imo. Shouldn't be too hard (tm) to rig up some distributed query pipeline. (as long as you are ok with coding per query, instead of the convenience of sql)