SQL on π

[Başar Alabay]

How realistic is it to install sql in Raspbian on a Pi? One says the
machine is too weak. But, for owncloud (say 3 users) or a test
Wordpress installation a »real, unlite« sql is necessary. Pi B, 700–800
MHz.

B. Alabay

--
http://www.thetrial.de/
ケディエ・ばく・ハヤテ・あんら

[MArtin]

On Thu, 30 Oct 2014 07:47:38 +0000 (UTC), Ba?ar Alabay
<ala...@gmx.net> wrote:

>How realistic is it to install sql in Raspbian on a Pi? One says the
>machine is too weak. But, for owncloud (say 3 users) or a test
>Wordpress installation a »real, unlite« sql is necessary. Pi B, 700–800
>MHz.
>
>B. Alabay

Having installed Wordpress on a Pi B, the standard packages work fine.
They installed cleanly and with no issues. I used the recipe at
http://www.raspberrypi.org/learning/web-server-wordpress/worksheet.md
The only issue being that as laid out there the php tests don't work.
The php lines need to be closed with "?>" (without the inverted
commas)! Rendering is slow and how it would perform in a real loaded
enviroment I am not so sure though Google will find you pages for
people who have optimised the standard instal for speed.

MArtin

---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

[c...@isbd.net]

Başar Alabay <ala...@gmx.net> wrote:
> How realistic is it to install sql in Raspbian on a Pi? One says the
> machine is too weak. But, for owncloud (say 3 users) or a test
> Wordpress installation a »real, unlite« sql is necessary. Pi B, 700–800
> MHz.
>

Owncloud works very satifactorily with sqlite, much easier to manage
in my opinion too.

I think Wordpress defaults to mysql though, try it and see how it
goes, you've lost nothing.

--
Chris Green
·

[Tony van der Hoff]

I run mysql on a pi without problems

[Gordon Henderson]

In article <m2sqeq$ntj$7...@news.albasani.net>,

BaÅ ar Alabay <use...@alabay.de> wrote:
>How realistic is it to install sql in Raspbian on a Pi? One says the
>machine is too weak. But, for owncloud (say 3 users) or a test

>Wordpress installation a »real, unlite« sql is necessary. Pi B, 700–800
>MHz.

Some time (er, 2 years?) back I compiled up my usual LAMP stack for a
Pi which was a slightly tuned kernel, apache, php & MYSQL... Then stuck
wordpress on it.

I was plesantly surprised with the result. It was usable - although I
didn't load any big plugins into wordpress.

That was on a Rev 1 Pi too (256MB of RAM). It's more CPU bound than
SD/Memory though.

I think it would be fine for a small home/hobby setup especially on a
Rev 2/B+ with a lot more RAM.

Gordon

[Ed Davies]

On Thu, 30 Oct 2014 07:47:38 +0000, Başar Alabay wrote:

You're probably not going to want to hear this, but if you post with
Unicode characters that are not in ISO8859-1 to an English Usenet group,
then expect to see your name and/or the title horribly broken in the
followups with the followups misthreaded as the various newreaders decide
that the title is in the same thread or not at random because each
newsreader will break the Unicode in different ways. Bear in mind that
Usenet can now be regarded as a "legacy" system, with perhaps most
newsreaders only able to display a limited character set. And while
ISO8859-1 contains a number of non-English characters, the
s-with-cedilla unfortunately isn't one of them. You'll have to settle
for 's' otherwise you'll be known as "Ba?ar" or "BaÅŸar".

[Rob Morley]

On Thu, 30 Oct 2014 11:32:13 +0000 (UTC)
Ed Davies <e...@invalid.invalid> wrote:

> On Thu, 30 Oct 2014 07:47:38 +0000, Başar Alabay wrote:
>
> You're probably not going to want to hear this, but if you post with
> Unicode characters that are not in ISO8859-1 to an English Usenet
> group, then expect to see your name and/or the title horribly broken
> in the followups

So far only one out of five replies (the only Windows user?) has mangled
the 'ş', and this post should make it one out of six.

[Ed Davies]

On Thu, 30 Oct 2014 11:45:33 +0000, Rob Morley wrote:

> On Thu, 30 Oct 2014 11:32:13 +0000 (UTC)
> Ed Davies <e...@invalid.invalid> wrote:
>>
>> You're probably not going to want to hear this, but if you post with
>> Unicode characters that are not in ISO8859-1 to an English Usenet

>> group, then expect to see your name and/or the title horribly broken...

>
> So far only one out of five replies (the only Windows user?) has mangled
> the 'ş', and this post should make it one out of six.

Also the trn user, so that was 2 out of 4, or 50% before I posted, which
was par for the course, but that would get worse as people followed the
followups.

[c...@isbd.net]

This tin user (a descendent of trn I believe) is seeing the 'ş' OK,
the Linux command line works pretty well with UTF, I get to see
Chinese, Russian, Arbic etc. in all their glory! :-)

--
Chris Green
·

[Gordon Henderson]

I'm the (a) trn user... Maybe time to move on, but it's done me well
for the past decade or 2...

What's mildly irritating is that I see the weirdo characters OK here
as I type this into vim, but get the inverse question marks, etc.
in the trn viewer...

Gordon

[Başar Alabay]

MArtin wrote:

> Having installed Wordpress on a Pi B, the standard packages work fine.
> They installed cleanly and with no issues. I used the recipe at
> http://www.raspberrypi.org/learning/web-server-wordpress/worksheet.md
> The only issue being that as laid out there the php tests don't work.
> The php lines need to be closed with "?>" (without the inverted
> commas)! Rendering is slow and how it would perform in a real loaded
> enviroment I am not so sure though Google will find you pages for
> people who have optimised the standard instal for speed.

So you have installed SQL and it behaves well? I’m also thinking of/for
OwnCloud.

[Başar Alabay]

Yes, and we live in 2014, usenet can’t be *so* legacy that it touches
legastheny. I guess unicode and utf8 should be »standard« nowadays. Not
only for shy şs :-)

Windozy … yes, but (I hope) Raspberry users deal /a little bit/ with
linux, unix, darwin or something else and therefore know how to adjust
utf.

[Başar Alabay]

c...@isbd.net wrote:

>> > So far only one out of five replies (the only Windows user?) has mangled
>> > the 'ş', and this post should make it one out of six.
>>
>> Also the trn user, so that was 2 out of 4, or 50% before I posted, which
>> was par for the course, but that would get worse as people followed the
>> followups.
>
> This tin user (a descendent of trn I believe) is seeing the 'ş' OK,
> the Linux command line works pretty well with UTF, I get to see
> Chinese, Russian, Arbic etc. in all their glory! :-)

BTW, this tiny little ş found its way via tin (2.3) and vim on Darwin.
Including naughty, naughty, über-nauuuughtyyyy djappaneez non-emoji kanas.
Burp.

[Başar Alabay]

Gordon Henderson wrote:

> I'm the (a) trn user... Maybe time to move on, but it's done me well
> for the past decade or 2...

Globalization says hellouw :-)

> What's mildly irritating is that I see the weirdo characters OK here
> as I type this into vim, but get the inverse question marks, etc.
> in the trn viewer...

Vim should be unicode-aware out of the box, trn … well, tin is really
not bad.

[Başar Alabay]

Dennis Lee Bieber wrote:

> On Thu, 30 Oct 2014 07:47:38 +0000 (UTC), Ba?ar Alabay <ala...@gmx.net>

> declaimed the following:

>
>>How realistic is it to install sql in Raspbian on a Pi? One says the
>

> SQL is a semi-standardized language for querying databases -- you don't
> "install" SQL.
>
> SQLite3, MySQL/MariaDB, PostgreSQL, Firebird, Interbase, Oracle, Sybase
> are all relational database systems.

I meant MySQL instead SQLite.

> No idea of what constitutes "weak" -- I used to run MySQL on a Win98
> machine with a 733MHz processor (where the server had to be started as part
> of my login startup programs, as Win98 didn't support running it as a
> system service).

So you mean, it’s no problem to run (e. g.) Owncloud with MySQL instead
SQLite on a Pi?

> For an r-pi -- the big killer may be wear&tear on the SD card.
> Relational databases are heavy I/O processes.

Is there a difference between MySQL’s and SQLite’s usage of the SD? It’s
a 32 GB card, so it’s not yet cramped.

[Jack]

Ba?ar Alabay <ala...@gmx.net> wrote:

> How realistic is it to install sql in Raspbian on a Pi? One says the
> machine is too weak. But, for owncloud (say 3 users) or a test
> Wordpress installation a »real, unlite« sql is necessary. Pi B, 700–800
> MHz.
>
> B. Alabay

I'm using an Alix board as linuxbox with my personal website
(serendipity blog + postgresql). Has to be said that /boot is in the
internal CF card, but the rest of / is on an axternal USB HD.
It's not the fastest webserver in the world, but it works.
The Pi is more powerful (and has more ram) than the Alix, so I don't see
the problem there.

Bye Jack
--
Yoda of Borg am I! Assimilated shall you be! Futile resistance is, hmm?

[Pete]

In article <pan.2014.10...@invalid.invalid>,

Slow response to this thread, but I had to do a lot of playing
first...(:-/)

I'm also a trn user, and was seeing a lot of garbage in the original
post, and I finally got irritated enough to dig into the code a bit.

Turns out that when trn checks for control characters in an article
it assumes everything might be 7-bit with parity, so it strips off the
top bit before checking! As a result it turns every extended byte
between 0x80 and 0x9F into a control character which it then escapes!
Hence the various garbage characters in any utf-8 or ISO-8859 article
text.

I ended up with a simple fix, which anyone who's compiled their own
should be able to quickly replicate. In util.h there are two macros:

#define AT_GREY_SPACE(s) ((*(Uchar*)(s) & 0x7F) <= ' ' || *(s) == '\177')
#define AT_NORM_CHAR(s) ((*(Uchar*)(s) & 0x7F) >= ' ' && *(s) != '\177')

which I switched to:

#define AT_GREY_SPACE(s) (*(Uchar*)(s) <= ' ' || *(s) == '\177')
#define AT_NORM_CHAR(s) (*(Uchar*)(s) >= ' ' && *(s) != '\177')

and I'm now getting my utf-8 text unadulterated. (My Terminal window is
utf-8. I can easily switch it to 8859 if needed.)

Of course I'll be in trouble if I ever encounter any post that is
actually 7-bit-with parity, but I wonder if this can ever happen these
days? In the olden time of UUCP, I suppose that was standard, but I
think everything is 8-bit these days.

Ironically, the only posts that are now getting munged are Ed Davies'!
Whatever transformation his reader did seems to have put an
'underline-on' code into the Subject header (and also hides the
newlines in trn's article list...).a [So I've edited the Subject
to avoid this.]

In any case, I rather feel that it is good manners *not* to use either
utf-8 or 8859 (or Windows-1252) in articles. There are just too many
variant standards around. [I'm having the same irritation with the web
these days. Sometimes I have to try 3 or 4 browsers before I find one
that shows the site properly!]

-- Pete --

[Pete]

In article <0oednRq_x-oyCcTJ...@lmi.net>,
Pete <neve...@GoodeveCa.net> wrote:

[.....]

Heh -- just having checked my own post, I see that trn doesn't bother
to add either a 'Content-type:' or a 'Content-Transfer-Encoding:'
header, so I wonder if that causes problems elsewhere? (There's at
least one utf-8 character there, copied over in the quoted section.)

-- Pete --

[Andrew Gabriel]

In article <vf2dnSiJtbEaC8TJ...@lmi.net>,

These are MIME headers, originally intended for email.

Usenet predates MIME by a long way, and it's only relatively recently
that it has been updated to allow MIME. Although MIME has worked for
many users who read Usenet through email clients which supported MIME
for a long time, strictly it was not in the spec.

trn is probably written to RFC1036, where usenet articles are only
permitted to be 7-bit ASCII. MIME is added to Usenet in RFC5536 in
Nov 2009.

--
Andrew Gabriel
[email address is not usable -- followup in the newsgroup]

[Başar Alabay]

Self-answering:

BEWARE! :-/

I tried and installed mysql on the Pi (modell B). After converting the
owncloud db and reconnecting it (not so easy, but I managed it) … first
everything worked well. It seemed.

But then I tried to sync approx. 400 vcards via carddav … load increased
to 12, peaked at 20 (!) and even my 100 MB swap ran full (this never,
never happened before in one year). And? Well, owncloud could not manage
it, it seems. The second computer synced and got nearly 600 vcards, but
looots of doublettes. And I could not reach the adressbook via https.

So, sad but true, it seems the Pi won’t be able to handle this
combination. Bad due to sqlite is too slow and blocks with locks. With
mysql this did not happen, but the machine maybe had not enough power? I
run it with 800 MHz scaling governor.

Such a pitty.

[Gordon Henderson]

In article <m4cltv$o0u$1...@news.albasani.net>,

BaÅ ar Alabay <use...@alabay.de> wrote:

Did you tune the MySQL engine to use less RAM? If you do, then it won't
swap and should be much more responsive.

Gordon

[Başar Alabay]

Gordon Henderson wrote:

>>So, sad but true, it seems the Pi won???t be able to handle this

>>combination. Bad due to sqlite is too slow and blocks with locks. With
>>mysql this did not happen, but the machine maybe had not enough power? I
>>run it with 800 MHz scaling governor.
>
> Did you tune the MySQL engine to use less RAM? If you do, then it won't
> swap and should be much more responsive.

No. I had questioned that here … no-one told me something about tuning
:-) And via web one finds ooo much blahblah, especially in »fores«,
unbelievable. But it was shocking how much mysql sucks the cpu.

Syncing big address books? No way.

[Gordon Henderson]

In article <m4cpvk$pq$1...@news.albasani.net>,

BaÅ ar Alabay <use...@alabay.de> wrote:

>Gordon Henderson wrote:
>
>>>So, sad but true, it seems the Pi won???t be able to handle this
>>>combination. Bad due to sqlite is too slow and blocks with locks. With
>>>mysql this did not happen, but the machine maybe had not enough power? I
>>>run it with 800 MHz scaling governor.
>>
>> Did you tune the MySQL engine to use less RAM? If you do, then it won't
>> swap and should be much more responsive.
>

>No. I had questioned that here … no-one told me something about tuning
>:-) And via web one finds ooo much blahblah, especially in »fores«,

>unbelievable. But it was shocking how much mysql sucks the cpu.
>
>Syncing big address books? No way.

Make sure it's not swapping first. Really. That's important - if it swaps,
then you'll lost 99% of any avalable anything )-:

Edit /etc/my.cnf and look for the numbers.

I've never installed Debians version of MySQL though - but the
MySQL source distribution comes with small, medium and large standard
configuration files - seeing if they exist might be a good starting point
(ie. start with the small one)

Gordon

[Başar Alabay]

Gordon Henderson wrote:

> Make sure it's not swapping first. Really. That's important - if it swaps,
> then you'll lost 99% of any avalable anything )-:

Who or what is »it«?

> Edit /etc/my.cnf and look for the numbers.
>
> I've never installed Debians version of MySQL though - but the
> MySQL source distribution comes with small, medium and large standard
> configuration files - seeing if they exist might be a good starting point
> (ie. start with the small one)

Well, the problem is, I just wanted owncloud to work. Not to dive
deepest into mysql :-/

[Başar Alabay]

Dennis Lee Bieber wrote:

>>So, sad but true, it seems the Pi won’t be able to handle this
>>combination. Bad due to sqlite is too slow and blocks with locks. With
>>mysql this did not happen, but the machine maybe had not enough power? I
>>run it with 800 MHz scaling governor.
>>
>

> But which table type(s) had you defined? MyISAM, InnoDB, some other?

Ehm, I just switched the database type from sqlite to mysql … means, let
owncloud convert and switch. So me, I hadn’t defined anything.

> When doing such block updates, did you try disabling the index first,
> then letting it build the index after the update completed?

I just wanted owncloud to work. I’m not a database specialist. As with
sqlite, one sets up and it goes. So the owncloud team itself just states
mysql is the better way for bigger (what this means) installations.
Nothing more. A bit poor information for the masses.

> There are a LOT of configuration parameters for MySQL that maybe should
> be looked at...

Maybe that’s the problem :-|

[Gordon Henderson]

In article <m4dqq4$hqb$1...@news.albasani.net>,

BaÅ ar Alabay <use...@alabay.de> wrote:

>Gordon Henderson wrote:
>
>> Make sure it's not swapping first. Really. That's important - if it swaps,
>> then you'll lost 99% of any avalable anything )-:
>

>Who or what is »it«?

What do you think? It's the Pi. Or specifically Linux running on the
Pi deciding that some program or other needs more memory, so it (Linux)
swaps stuff out to disk (SD card)

>
>> Edit /etc/my.cnf and look for the numbers.
>>
>> I've never installed Debians version of MySQL though - but the
>> MySQL source distribution comes with small, medium and large standard
>> configuration files - seeing if they exist might be a good starting point
>> (ie. start with the small one)
>
>Well, the problem is, I just wanted owncloud to work. Not to dive
>deepest into mysql :-/

It's not going to happen. Only the most trivial of mysql systems on a Pi
is going to work without any sort of tuning. You "get away" with it these
days because servers have stupid amount of memory and fast processors. A
Pi has neither, so you'll need to tune.

There are gigabytes of manuals, documents, how-to's, etc. online about
MySQL tuning. Start reading.

Gordon

[Dom]

On 18/11/14 00:30, Dennis Lee Bieber wrote:
> On Mon, 17 Nov 2014 21:57:25 +0000 (UTC), Ba?ar Alabay <ala...@gmx.net>
> declaimed the following:
>

>>> There are a LOT of configuration parameters for MySQL that maybe should
>>> be looked at...
>>
>> Maybe that’s the problem :-|
>

> Is MySQL Workbench available for the OS on the PI? Possibly not -- I
> know it runs on Win7 but that's as a prebuilt executable.
>

mysql-workbench is available, as is mysqltuner which may help decide
which parameters to set.

I don't run MySQL on a Pi, but I do have several (admittedly not too
busy) MySQL databases and an Apache server running on an old PC which
has 384MB RAM and a 500MHz Celeron CPU. It's a bit faster than a Pi.

[Başar Alabay]

Gordon Henderson wrote:

>>> Make sure it's not swapping first. Really. That's important - if it swaps,
>>> then you'll lost 99% of any avalable anything )-:
>>

>>Who or what is ??it???

>
> What do you think? It's the Pi. Or specifically Linux running on the
> Pi deciding that some program or other needs more memory, so it (Linux)
> swaps stuff out to disk (SD card)

Ah, okay. I wasn’t quite sure if »it« was mysql itself. Except for
mysql, the pi never swapped for over a year.

>>Well, the problem is, I just wanted owncloud to work. Not to dive
>>deepest into mysql :-/
>
> It's not going to happen. Only the most trivial of mysql systems on a Pi
> is going to work without any sort of tuning. You "get away" with it these
> days because servers have stupid amount of memory and fast processors. A
> Pi has neither, so you'll need to tune.
>
> There are gigabytes of manuals, documents, how-to's, etc. online about
> MySQL tuning. Start reading.

I cannot experiment with this little machine. That’s the point. So I have
to wait.

[Başar Alabay]

Dennis Lee Bieber wrote:

> It probably is working... But it's your initial build of the database
> that could be killing you.

You mean the filling up? Maybe.

> Consider: once you loaded that database, how often do you expect to
> make changes to the data? How big are the changes?

Well, sometimes there are lots of changes in calendars and file syncs.
All these reflect to the db I guess.

> You'll have to examine what type of tables "owncloud" creates... MyISAM
> will have sets of:
>
> xxx.frm
> xxx.MYD
> xxx.MYI
> {at least, the version I run on Win7 creates such}

I even don’t know where the db itself is :-) Is it like with sqlite just
inside /data (OC) or somewhere else?

> .frm is the table schema, .MYD is the table data, and .MYI is the table
> index. xxx is the table name.
>
> InnoDB mixes the tables into a "single" file -- but creates overflow
> files as needed.

Single file sounds like sqlite.

> Is MySQL Workbench available for the OS on the PI? Possibly not -- I
> know it runs on Win7 but that's as a prebuilt executable.

I haven’t to had to do anything with db issues for the last 20 years.
Before that, yes, on Atari :-)

[Başar Alabay]

Dom wrote:

>> Is MySQL Workbench available for the OS on the PI? Possibly not -- I
>> know it runs on Win7 but that's as a prebuilt executable.
>>
>
> mysql-workbench is available, as is mysqltuner which may help decide
> which parameters to set.

Hm, as I run the pi without any GUI … maybe I could have used these on
OSX and connect to the pi. But for now I don’t have time and
possibilities to experiment with that.

> I don't run MySQL on a Pi, but I do have several (admittedly not too
> busy) MySQL databases and an Apache server running on an old PC which
> has 384MB RAM and a 500MHz Celeron CPU. It's a bit faster than a Pi.

Maybe a bit better cooled, too.

[Rob]

Gordon Henderson <gordon...@drogon.net> wrote:
> It's not going to happen. Only the most trivial of mysql systems on a Pi
> is going to work without any sort of tuning. You "get away" with it these
> days because servers have stupid amount of memory and fast processors. A
> Pi has neither, so you'll need to tune.

Maybe it would be reasonable to expect that a MySQL package distributed
in a Pi distribution is already tuned not to use more memory than a Pi
usually has available.

OTOH, I know someone who runs an out-of-the-box VoIP exchange install on
a Pi (Asterisk plus some web config frontend that I think uses MySQL as
configuration storage), so either the tuning is better on that install
or it is not so much of an issue until you really start to load it.

[Dom]

On 18/11/14 07:37, Başar Alabay wrote:

> Dom wrote:
>
>>
>> mysql-workbench is available, as is mysqltuner which may help decide
>> which parameters to set.
> Hm, as I run the pi without any GUI … maybe I could have used these on
> OSX and connect to the pi. But for now I don’t have time and
> possibilities to experiment with that.

mysqltuner is a command line utility. It's just a perl script that
analyses the databases and gives a few suggestions and reports.

>
>> I don't run MySQL on a Pi, but I do have several (admittedly not too
>> busy) MySQL databases and an Apache server running on an old PC which
>> has 384MB RAM and a 500MHz Celeron CPU. It's a bit faster than a Pi.
>
> Maybe a bit better cooled, too.

Probably needs to be. But the fan doesn't run very often. It's an old
laptop.

The Pi doesn't need cooling.

[Dom]

On 18/11/14 07:34, Başar Alabay wrote:

> Dennis Lee Bieber wrote:
>
>> You'll have to examine what type of tables "owncloud" creates... MyISAM
>> will have sets of:
>>
>> xxx.frm
>> xxx.MYD
>> xxx.MYI
>> {at least, the version I run on Win7 creates such}
>
> I even don’t know where the db itself is :-) Is it like with sqlite just
> inside /data (OC) or somewhere else?

The default location for the database files is
/var/lib/mysql/$databasename/

>> .frm is the table schema, .MYD is the table data, and .MYI is the table
>> index. xxx is the table name.
>>
>> InnoDB mixes the tables into a "single" file -- but creates overflow
>> files as needed.
>
> Single file sounds like sqlite.

I believe that's a single (.frm) file per table, not per DB like sqlite.

[Gordon Henderson]

In article <slrnm6m30e...@xs8.xs4all.nl>,

Rob <nom...@example.com> wrote:
>Gordon Henderson <gordon...@drogon.net> wrote:
>> It's not going to happen. Only the most trivial of mysql systems on a Pi
>> is going to work without any sort of tuning. You "get away" with it these
>> days because servers have stupid amount of memory and fast processors. A
>> Pi has neither, so you'll need to tune.
>
>Maybe it would be reasonable to expect that a MySQL package distributed
>in a Pi distribution is already tuned not to use more memory than a Pi
>usually has available.

Well - the issue here is that Raspbian is essentially Debian re-compiled
for the Pi and is fairly generic rather than being targetted.

What might be an idea is for some enterprising individual to create &
release just the my.cfg configuration file tailored for the Pi. Any
takers?

>OTOH, I know someone who runs an out-of-the-box VoIP exchange install on
>a Pi (Asterisk plus some web config frontend that I think uses MySQL as
>configuration storage), so either the tuning is better on that install
>or it is not so much of an issue until you really start to load it.

That particular asterisk is pretty light weight. Hardly anything is stored
in the database.

My own asterisk based PBX runs in 128MB of RAM with no SQL (uses apache &
php though)

Gordon

[Rob]

Gordon Henderson <gordon...@drogon.net> wrote:
>>OTOH, I know someone who runs an out-of-the-box VoIP exchange install on
>>a Pi (Asterisk plus some web config frontend that I think uses MySQL as
>>configuration storage), so either the tuning is better on that install
>>or it is not so much of an issue until you really start to load it.
>
> That particular asterisk is pretty light weight. Hardly anything is stored
> in the database.
>
> My own asterisk based PBX runs in 128MB of RAM with no SQL (uses apache &
> php though)

I would not have considered a db with 600 personal contacts a heavy
load until I read here that it causes a problem.

[Martin Gregorie]

On Tue, 18 Nov 2014 10:11:35 +0000, Rob wrote:

>
> I would not have considered a db with 600 personal contacts a heavy load
> until I read here that it causes a problem.
>

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 |

[Başar Alabay]

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

[Başar Alabay]

Martin Gregorie wrote:

> 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.

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

> 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.

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.

[Başar Alabay]

Morten Reistad wrote:

> 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.

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.

[Martin Gregorie]

On Tue, 18 Nov 2014 12:51:53 +0000, Başar Alabay wrote:

> Martin Gregorie wrote:
>
>> 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.
>
> I don’t set a completely new thing up for myself. I just celt owncloud
> convert. So … that’s abit too much for me.
>

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.

> 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.
>

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.

[Başar Alabay]

Martin Gregorie wrote:

> 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.

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

> 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.

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

> 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.

The only thing/problem is mysql instead of sqlite!

>> 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.
>>
> Why? You've set out to do something they don't claim to support.

Ehm, sorry? Support means for you a deaf-dumb-blind package and
nothing more? Of course they »support« ist via source.

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

I don’t know where you live, but »here« you pay for electricity :-)
It’s madness to run a machine bigger than a pi 24/7 for caldav and
carddav.

> 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.

That’s not what I want. Use–cost.

[Başar Alabay]

Dennis Lee Bieber wrote:

>>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.
>>
>

> Meaningless...
>
> Is that a Class 10 SD card?

Yes. A SanDisk Ultra.

> 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.

Before, I had a »133X« card inside, muuuch slower.

> 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.

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

[A. Dumas]

On 19/11/2014 03:36, Dennis Lee Bieber wrote:
> 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.

Yes, but as
https://en.wikipedia.org/wiki/Secure_Digital#Speed_class_rating rightly
states: "lower Class does not necessarily mean better small-file
performance."