1. Home
  2. Raspberry Pi Group
  3. SQL on ?
  4. Page 3

SQL on ?

Quite.

Up until about three years ago my mail archive was running on an 866MHz P3 box with 512MB RAM, so not totally out of line with an RPi. Ran on Fedora rather than Debian.

Software: the archive is written in Java and uses a JDBC to talk to its PostgreSQL database.

Size: at that time the archive was around 2Gb and contained about 50,000 messages.

Performance: whole-database searches on body text took about 25 seconds. Body text searches on the last two year's messages took 5 seconds.

I did almost no Postgres tuning to get that performance, but OTOH I have been a DBA in the past and do know something about schema design and appropriate use of indexes and constraints to support entity relationships.

Current: exactly the same software, though with later Fedora, Java and Postgres versions, is now running on a 3 GHz dual core Athlon rig with

4GB RAM. The database is now around 420,000 messages. The whole DB scan takes 10 seconds or (2-3 secs for a search limited to the last two years.

To the OP: you *do* need to take the time to understand any RDBMS. The software must be tweaked to suit the hardware its running on. In the case of MySQL your choice or whether to use INNODB or not will be crucial to performance.

Last but not least, many DB schemas are poorly designed by people who should know better and may need modifications to get adequate performance. Watchpoints:

- is the schema fully normalised to 3rd normal form?

- is every prime key supported by a unique index?

- are all relationships supported by suitable constraints and indexes?

If not, *fix the schema* before doing anything else. If it is OK, use the database's tuning facilities (the EXPLAIN verb or equivalent) to see how the RDBMS is executing any noticeably slow queries and take the steps needed to fix the query performance - usually by adding missing indexes or, less rarely, by rewriting/simplifying over-complex queries or stored procedures.

Yes, you *do* need to know this stuff if you're going to use an RDBMS for any non-trivial application. And, if you're using it to store long term data, you need to know how to handle backups and to migrate the data to a later version of the RDBMS.

--
martin@   | Martin Gregorie 
gregorie. | Essex, UK 
org       |
Reply to
Martin Gregorie
Loading thread data ...

< 400! :-) That increased to > 600 thanks to the mysql/pi/sync chaos.

B. Alabay

--
http://www.thetrial.de/ 
???????????????
Reply to
Ba?ar Alabay

I don?t set a completely new thing up for myself. I just celt owncloud convert. So ? that?s abit too much for me.

I think, this should be addressed to the ownlcoud staff, not to the end users. Especially not to those who know even less than me in these cases.

B. Alabay

--
http://www.thetrial.de/ 
???????????????
Reply to
Ba?ar Alabay

The PI is faster that the cray-1.

With asterisk it can handle several hundred registrations of sip/iax clients (adapters/phones), and it can transcode around 25 calls from gsm to/from a/ulaw; or about 40 to/from g.722.

Asterisk uses mysql very sparingly (unless you let it load the config from and start siptracing to mysql. Otherwise it is a read/write per

20 minutes per adapter, and about 10 per phone call.)

kamailio/(ser) runs even more lightweight. It can run thousands of client registrations on a single pi.

I know, I am testing this with sipsak. I just built a copy of a customer's (medium size ISP) infrastructure on 4 pis, and use sipsak on two 4-processor i5's to load it.

To load up a pi like the OP described the applications will have to do unspeakable things, or there is a problem with the SD card. I would bet on the latter. Cheap SD cards can act up like that.

-- mrr

Reply to
Morten Reistad

On Tue, 18 Nov 2014 07:34:57 +0000 (UTC), Ba?ar Alabay declaimed the following:

SQLite3 is a "file server" system -- if two processes are accessing the same database, they both are opening the database file (as a side effect, that can mean lots of file locks going up and down as each process commits changes).

MySQL even with the InnoDB table engine is still client/server -- the database server is running in the background, and all processes are sending command to the server.

--
	Wulfraed                 Dennis Lee Bieber         AF6VN 
    wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/
Reply to
Dennis Lee Bieber

I think Asterisk as running on the Pi does not use MySQL at all.

There is Asterisk with some 50 config files in /etc, and there is a separate "web admin" tool (I forget the name; there are several competing tools like this) that uses MySQL to store its config values and generates the config files for Asterisk whenever the user changes something.

As long as you make only calls and do not change config, MySQL is not being used. It may well be that it gets swapped out and/or that it does not load any data in memory when the Pi has been rebooted and is only running the already configured Asterisk.

However, I would think that a normal config holds some tens to some hundreds of config records and the "update" program reads them all to generate a new config. That is apparently working fine.

Maybe the program that this thread is about stores many more than 600 records for a 600-person contact list.

Reply to
Rob

THe SD card is a 32 GB card, absolutely not cheap, a Sandisk and yesterday completely checked (for other things). No errors. An rat-fast.

B. Alabay

--
http://www.thetrial.de/ 
???????????????
Reply to
Ba?ar Alabay

Raspbian isn't one of Owncloud's supported distros.

This means that its up to you to install Owncloud's dependencies, e.g. the database (MySQL or SQLite), a web server (lightppd and nginx) and PHP, and then, when these are up and running, install Owncloud; probably from the source tarball.

You have decided to put it Owncloud an RPi, so if you don't have the background and knowledge to do that right now, best you get reading and learn what you need to troubleshoot what you've installed and to work round any quirks in the Raspbian versions of the servers it depends on. In this case that means at least understanding the database, web server and PHP well enough to make them perform well on the RPi and to get them playing nicely with Owncloud.

Why? You've set out to do something they don't claim to support.

IMO you'd be better off finding a small box that will run one of their supported Linux distros and use that.

Also, and I may be wrong about this, but I would hesitate to run any database-based application off an SD card. The cheap solution would be to use a hard drive: a more expensive choice would be the sort of solid state drive that's sold as a spinning rust replacement.

--
martin@   | Martin Gregorie 
gregorie. | Essex, UK 
org       |
Reply to
Martin Gregorie

Its quite heavy once you include all OwnCloud's its support requirements. I took a look at its website. A fairly rapid scan shows it needs:

- an RDBMS (SQLite, MySQL, MariaDB or PostgreSQL are supported)

- a web server (Nginx and Lightppd were mentioned)

- a fairly recent PHP

... and there may be other stuff I missed. Raspbian, or even vanilla Debian, are not among the supported distros listed on the website.

--
martin@   | Martin Gregorie 
gregorie. | Essex, UK 
org       |
Reply to
Martin Gregorie

Owncloud runs more than a year here on the Pi. With apache.

I have set that up more than a year ago, but with sqlite.

The only thing/problem is mysql instead of sqlite!

Ehm, sorry? Support means for you a deaf-dumb-blind package and

It?s madness to run a machine bigger than a pi 24/7 for caldav and carddav.

That?s not what I want. Use?cost.

B. Alabay

--
http://www.thetrial.de/ 
???????????????
Reply to
Ba?ar Alabay

On Tue, 18 Nov 2014 16:00:26 +0000 (UTC), Ba?ar Alabay declaimed the following:

Meaningless...

Is that a Class 10 SD card?

Class 10 cards are rated based on: Freshly formatted, and ONE data stream (ie: VIDEO camera capture). The Class 10 rating can actually perform terribly when using fragmented file access (pretty much everything except a video camera). Class 6 cards, OTOH, are rated for small file and fragmented access.

To put it at a lower level, a class 10 card internally is optimized to stream data and may use internal structures that make small scattered files very slow to access.

--
	Wulfraed                 Dennis Lee Bieber         AF6VN 
    wlfraed@ix.netcom.com    HTTP://wlfraed.home.netcom.com/
Reply to
Dennis Lee Bieber

Yes. A SanDisk Ultra.

So you mean, next time I should look for class 6? I don?t know if these are available in 32 GB.

B. Alabay

--
http://www.thetrial.de/ 
???????????????
Reply to
Ba?ar Alabay

Yes, but as

formatting link
rightly states: "lower Class does not necessarily mean better small-file performance."

Reply to
A. Dumas

ElectronDepot website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.