SQlite or Mysql?

ℹ️ Support
,
1

I got about 1 terabyte of very good quality photos and I’m planning to move them on my nextcloud.

In my situation which database is best suited?

Thanks to everyone.

2

MySQL. The performance is better in handling big tabels.

1 Like
3

with big tables do you mean with many records or fields?

4

i guess the database doesn’t care. could be even 2tb. :wink:

if you are the only user and you access your fotos once in a while sqlite is fine.
nevertheless it’s no rocket science to bring up a mysql db. no more than 10 line on the command line.
https://www.c-rieger.de/nextcloud-13-nginx-installation-guide-for-ubuntu-18-04-lts/#c03

1 Like
5

I use MySQL actually

But somerimes photos take some seconds to load

I d like to set up it as best as possibile

6

what hardware, os, network are you runing on?

7

centos 7 last update

virtualized with esxi

2 cores
2 gb ram

port group as ethernet, about 10 gigabit

8

If you will do it “best as possible” you should take MariaDB Galera with a HA-MaxScale instanace, see also https://docs.nextcloud.com/server/11/admin_manual/installation/deployment_recommendations.html#scale-out-deployment

9

I would try more RAM first. 4 or better 8GB.
Is your maschine paging? Is swap used a lot?

10

Why does the db even matter? The files are stored on the file system anyway. Or am I missing something?

Also, I would like suggest a third alternative: PostgreSQL. It is imho superior to MySQL. Whenever I have to use MySQL I dislike it more and more, because it requires fiddling around even for the most basic things when PostgreSQL just works.

11

Files are not scanned all the time and Nextcloud relies on the entries in the database. And there are some limitations to SQLite (simultaneous access), so if you can use a real database, do use it!

12

OK, I focused on “high quality” and thought that it was implied that the size of the pictures is of any relevance to the db. The number of pictures certainly makes a difference.

13

for my understanding files are stored in the local file system.
the database only holds meta data. like owner, permissions, sharing info.

in which way you access the photos? through the web gui? mobile app?

you may run top on your server.

top - 14:43:19 up 3 days, 15:13,  1 user,  load average: 0,89, 0,76, 0,69
Tasks: 128 total,   1 running, 127 sleeping,   0 stopped,   0 zombie
%Cpu(s): 26,6 us,  3,0 sy,  0,0 ni, 67,2 id,  3,2 wa,  0,0 hi,  0,0 si,  0,0 st
KiB Mem:   4194304 total,  1879068 used,  2315236 free,        0 buffers
KiB Swap:  4194304 total,   635512 used,  3558792 free.   252768 cached Mem

high load average means you have a cpu bottleneck
high swap usage means you have to few RAM
a high “wa” value in the third line (fifth value) means your HD is too busy/slow.