gdillen
Posts: 18
Joined: Sat Feb 01, 2014 11:36 am

Database selection

Thu Feb 06, 2014 11:07 am

I read already different postings about using databases on the Pi. Mainly sqlite3 is advised in most situations. What I want to achieve is that different sensors send data (of course this can happen simultaneously) to the Pi. I suppose since sqlite3 is a single user db probably its not suited for this project? What are my alternatives, that are performance wise acceptable: MySQL, ...?

Thanks.

User avatar
joan
Posts: 13557
Joined: Thu Jul 05, 2012 5:09 pm
Location: UK

Re: Database selection

Thu Feb 06, 2014 11:39 am

http://www.sqlite.org/threadsafe.html suggests that (by default) sqlite3 should be safe.

User avatar
DougieLawson
Posts: 33794
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website

Re: Database selection

Thu Feb 06, 2014 7:25 pm

MySQL runs OK on a RPi.

Although in my case I'm running the MariaDB variant on my Ubuntu server and just the mysql-client code on my RPi.
Microprocessor, Raspberry Pi & Arduino Hacker
Mainframe database troubleshooter
MQTT Evangelist
Twitter: @DougieLawson

2012-18: 1B*5, 2B*2, B+, A+, Z, ZW, 3Bs*3, 3B+

Any DMs sent on Twitter will be answered next month.

User avatar
brox
Posts: 19
Joined: Sat Mar 02, 2013 11:24 pm
Location: Betelgeuse

Re: Database selection

Fri Feb 07, 2014 3:57 pm

gdillen wrote:I read already different postings about using databases on the Pi. Mainly sqlite3 is advised in most situations. What I want to achieve is that different sensors send data (of course this can happen simultaneously) to the Pi. I suppose since sqlite3 is a single user db probably its not suited for this project? What are my alternatives, that are performance wise acceptable: MySQL, ...?

Thanks.
From your description it is still unclear why you need database in the first place. If you need just some persistent storage, are you sure you need relational monster like mysql? Perhaps storing data in json-formatted text file is enough?
So long, and thanks for all the fish!

henrik
Posts: 65
Joined: Tue Dec 18, 2012 4:24 pm

Re: Database selection

Fri Feb 07, 2014 10:38 pm

If you're a Java developer and want a simple SQL database, use JavaDB - it's a pure Java database based on Apache Derby, is distributed with the Oracle JDK that comes with your RPi and very simple to use.

http://docs.oracle.com/javase/tutorial/ ... arted.html
http://docs.oracle.com/javadb/10.8.3.0/ ... index.html

Henrik, Oracle Java team

User avatar
xranby
Posts: 538
Joined: Sat Mar 03, 2012 10:02 pm
Contact: Website

Re: Database selection

Mon Feb 10, 2014 10:41 am

You should try the NoSQL database OrientDB on the RaspberryPi. OrientDB is designed for performance and it is known to be able to handle ~4500 request/second on the Pi.

https://github.com/orientechnologies/orientdb
https://github.com/orientechnologies/orientdb/wiki
http://zion-city.blogspot.se/2012/11/or ... nt-in.html
Xerxes Rånby @xranby I once had two, then I gave one away. Now both are in use every day!
twitter.com/xranby

gdillen
Posts: 18
Joined: Sat Feb 01, 2014 11:36 am

Re: Database selection

Tue Feb 11, 2014 12:49 pm

Thanks all for the info/recommendations. Maybe it was not clear for what I need a db: I want to store eg captured sensor data on my Pi.

Thanks

User avatar
xranby
Posts: 538
Joined: Sat Mar 03, 2012 10:02 pm
Contact: Website

Re: Database selection

Fri Feb 14, 2014 12:33 pm

gdillen wrote:Thanks all for the info/recommendations. Maybe it was not clear for what I need a db: I want to store eg captured sensor data on my Pi.

Thanks
simple file storage is the fastest way to store this kind of linear captured senor data.
you can use java helper librarys that allows you to store the data in xml or json for easy interchange with other applications.
running a database to store small amounts of linear sensor data is usually over-engineering.

if you got huge amounts of sensor data that is best processed in the "cloud" then you may consider solutions such as the apache hive hcatalog for hadoop:
http://hive.apache.org/
https://cwiki.apache.org/confluence/dis ... e/HCatalog
Xerxes Rånby @xranby I once had two, then I gave one away. Now both are in use every day!
twitter.com/xranby

User avatar
DougieLawson
Posts: 33794
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website

Re: Database selection

Fri Feb 14, 2014 2:30 pm

gdillen wrote:Thanks all for the info/recommendations. Maybe it was not clear for what I need a db: I want to store eg captured sensor data on my Pi.

Thanks
How much data? How big is a data record? How's it keyed (date/time or something else)? What are you doing with it after it's captured?
Microprocessor, Raspberry Pi & Arduino Hacker
Mainframe database troubleshooter
MQTT Evangelist
Twitter: @DougieLawson

2012-18: 1B*5, 2B*2, B+, A+, Z, ZW, 3Bs*3, 3B+

Any DMs sent on Twitter will be answered next month.

famco
Posts: 5
Joined: Thu Apr 03, 2014 11:05 am

Re: Database selection

Tue Apr 08, 2014 2:32 pm

Hi, I am using h2-Database on mi pi. Small footprint, very fast (if connected once) and very reliable on resets, lost of power ...

ghp
Posts: 1170
Joined: Wed Jun 12, 2013 12:41 pm
Location: Stuttgart Germany
Contact: Website

Re: Database selection

Tue Apr 08, 2014 7:34 pm

Hello, I run mysql on a model B revision 1 to store some timestamps for my dooropen-system. Runs for 375 days now without a break, thanks to an UPS. So mysql is not a problem.
Another option is apache derby I use in various projects.

REgards, Gerhard

111Muhammad
Posts: 1
Joined: Thu Oct 11, 2018 4:29 pm

Re: Database selection

Thu Oct 11, 2018 4:38 pm

Hi guys......Im doing a project related to capturing and storing videos using raspberry pi (coded in python). i want to save these captured videos on a database. which database do you recommend for me?

Balin
Posts: 9
Joined: Mon Sep 17, 2018 7:43 am

Re: Database selection

Mon Oct 15, 2018 12:20 pm

My favorite is HSQLDB (http://hsqldb.org/). It has different modes (RAM - not recommended on PI :-), embedded or as separate server), and you can use different database flavours to access it. It's for Java, though, with other languages, it loses lots of it's advantages. But for simple storage of sensor data, I would recommend an embedded database, e.g. HSQLDB for Java or SQLite for C, at all circumstances, except you have really huge masses of data - and for really huge masses of data, a Pi might be a little bit underpowered. As long as you do not have complex relations between your data, it's of no big advantage using a fully fledged sql database engine, like PostgreSQL, My/MariaSQL or Oracle.
Regards, Balin.

Balin
Posts: 9
Joined: Mon Sep 17, 2018 7:43 am

Re: Database selection

Tue Oct 16, 2018 11:06 am

111Muhammad wrote:
Thu Oct 11, 2018 4:38 pm
Hi guys......Im doing a project related to capturing and storing videos using raspberry pi (coded in python). i want to save these captured videos on a database. which database do you recommend for me?
You want to save whole videos in your database? Or only the references, and store the videos themselves somewhere in the filesystem? For the latter, any embeddded database will do. But if you want to store whole videos in the database, I'd recommend something that can work with BLOBs (Binary Large OBjects). With that, the videos themselves are not squeezed into the database structure, instead they are saved on the file system, and only the references are stored within the database structure. The database engine takes care for proper linking between these items. PostgreSQL can handle this, for example. See here for more: https://www.postgresql.org/docs/current ... jects.html
Regards, Balin.

Return to “Java”

Who is online

Users browsing this forum: No registered users and 2 guests