Dne St 25. listopadu 2015 10:31:39, Richard Brown napsal(a):

On 25 November 2015 at 10:27, Vojtěch Zeisek wrote:

...

Hello, recently I saw (on Czech Linux days, so no useful reference for here;-) presentation testing performance of PostgreSQL on various Linux FS (on SSD). Performance of Ext4 and XFS were more or less the same. But Btrfs failed. Possibly this is because of copy-on-write, which can be turned off. In such case its performance is better, but still little bit loosing. I'd like to use Btrfs, especially because of its snapshot feature, but I wonder what is the best FS for /var/lib/{mysql,pgsql} on SSD. Do people have Btrfs subvolumes with COW turned off? Any extra tuning? Or rather separate partitions (but it would be bit uncomfortable for resizing, yes, LVM, but might be too complicated architecture then...) with Ext4/XFS? Any other ideas? Thanks in advance, Vojtěch If you can partition accordingly, XFS is what I would recommend, and what YaST will give you if you tell it you want a data partition.

I'll have HDD with XFS for data (/home) and SSD for root with Btrfs. That is what openSUSE recommends and it seems appropriate. I wish to keep DB files on SSD for better performance. And if possible, I'd like to avoid complicated disks layouts.

However, if you only have the chance for one filesystem on disk, I'd agree with you that btrfs should be your choice as you get snapshotting, etc.

btrfs on openSUSE Leap 42.1 and Tumbleweed automatically disable copy on write in the subvolumes it creates automatically for /var/lib/pgsql and /var/lib/mysql, and that will give you good performance

This seems as a good option, thank You. -- Vojtěch Zeisek Komunita openSUSE GNU/Linuxu Community of the openSUSE GNU/Linux http://www.opensuse.org/ http://trapa.cz/

Dne Čt 26. listopadu 2015 11:28:36, Marcus Rueckert napsal(a):

On 2015-11-25 10:31:39 +0100, Richard Brown wrote:

...

If you can partition accordingly, XFS is what I would recommend, and what YaST will give you if you tell it you want a data partition.

However, if you only have the chance for one filesystem on disk, I'd agree with you that btrfs should be your choice as you get snapshotting, etc.

btrfs on openSUSE Leap 42.1 and Tumbleweed automatically disable copy on write in the subvolumes it creates automatically for /var/lib/pgsql and /var/lib/mysql, and that will give you good performance

btrfs is the *worst* FS for databases. you write the same data like 4-5 times with the WAL writing in the DB and how btrfs work.

Is there any settings of Btrfs volume which would be good for DB?

ext4 or xfs. with the preference to xfs as it gives you the most *consistent* performance.

The only my concern with other FS is need of separate partitions and potential future problems regarding need of resizes. LVM is solution, but I'd have to have whole SSD covered by LVM and in it / using Btrfs and extra XFS partitions for DBs. I'm not sure how much Btrfs likes LVM... V. -- Vojtěch Zeisek Komunita openSUSE GNU/Linuxu Community of the openSUSE GNU/Linux http://www.opensuse.org/ http://trapa.cz/

Dne Čt 26. listopadu 2015 12:43:40, jdd napsal(a):

Le 26/11/2015 12:41, Vojtěch Zeisek a écrit :

...

The only my concern with other FS is need of separate partitions and potential future problems regarding need of resizes. LVM is solution, but I'd have to have whole SSD covered by LVM and in it / using Btrfs and extra XFS partitions for DBs. I'm not sure how much Btrfs likes LVM... V.

can't you move the database to /home?

I can, but /home will be on much slower HDD, so it must be within /, which will be otherwise covered by Btrfs. V. -- Vojtěch Zeisek Komunita openSUSE GNU/Linuxu Community of the openSUSE GNU/Linux http://www.opensuse.org/ http://trapa.cz/

Dne Čt 26. listopadu 2015 13:04:17, jdd napsal(a):

Le 26/11/2015 12:54, Vojtěch Zeisek a écrit :

...

Dne Čt 26. listopadu 2015 12:43:40, jdd napsal(a):

...

can't you move the database to /home?

I can, but /home will be on much slower HDD, so it must be within /, which will be otherwise covered by Btrfs. V.

see the #10 answer http://dba.stackexchange.com/questions/59828/ssd-vs-hdd-for-databases how big is your database? and your memory?

Interesting. Currently, I have almost 8 GB DB (and it will grow) and 8 GB RAM. And applications using DB are also providing big files from disks so I suppose SSD will help. V. -- Vojtěch Zeisek Komunita openSUSE GNU/Linuxu Community of the openSUSE GNU/Linux http://www.opensuse.org/ http://trapa.cz/

On Thu, Nov 26, 2015 at 5:36 PM, Anton Aylward wrote:

On 11/25/2015 04:27 AM, Vojtěch Zeisek wrote:

...

Any other ideas?

Add memory. Memory is cheaper. If necessary, upgrade the mobo so you can add more memory. That may still be cheaper.

Well that will help for reading, but due to ACID every transaction has to end up written on the drive, not even in kernel or fs cache (or whatever additional cache layer is involved). Which means that writing speed is still hard-bound by "random write" of your drive. -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org

Dne Čt 3. prosince 2015 07:33:47, Anton Aylward napsal(a):

On 12/03/2015 05:31 AM, Stanislav Baiduzhyi wrote:

...

On Thu, Nov 26, 2015 at 5:36 PM, Anton Aylward wrote:

...

On 11/25/2015 04:27 AM, Vojtěch Zeisek wrote:

...

Any other ideas?

Add memory.

Well that will help for reading, but due to ACID every transaction has to end up written on the drive, not even in kernel or fs cache (or whatever additional cache layer is involved). Which means that writing speed is still hard-bound by "random write" of your drive.

This is one point I was thinking about. The second is when whole system is on SSD and the web application itself is also there, it should help overall performance.

In absolute terms that is true, but in practical terms queries, joins and sorts dominate the activity of a database that is making use of its relational nature.

If, perhaps, the database is dominated by writes, then really its doing logging, in which case a relational database is probably not the best choice for this function.

It's dominated by reading, I'd say - records are not updated too frequently, but are frequently requested.

As I keep saying ...

Context is Everything

Perhaps the OP would care to comment on the nature of the application

There are several web applications using complex searches and filtering of records and the applications provide also „big“ data like PDF and high resolution images. It is for biologists - people browse or search using various criteria for taxa they wish and then display/download details about them. Those files are not stored in DBs. Also there are several, I'd say ordinary, webs using CMS like Drupal. -- Vojtěch Zeisek Komunita openSUSE GNU/Linuxu Community of the openSUSE GNU/Linux http://www.opensuse.org/ http://trapa.cz/

Dne Čt 3. prosince 2015 10:51:10, Anton Aylward napsal(a):

On 12/03/2015 07:52 AM, Vojtěch Zeisek wrote:

...

Also there are several, I'd say ordinary, webs using CMS like Drupal.

The you are going to get a lot of searches of the FIND WHERE AND nature, where one of the AND is going to be the user id. That is a problem for indexing, which again gets back to reads of the database.

Exactly

Most CMSs do logging, but whether you choose to log to the database or the file system depends on the options offered by the CMS. How you use the logs will be of importance here. Using them for accounting/billing seems wasteful when the function is better implemented n the application.

They are set to log into the database (not system), but usage of logs is extensive, basically just for statistics.

Some CMSs do various kinds of caching of their own, again you have options of how much of that can be in memory.

Yes, Drupal has it.

Check out 'memcached' and 'redis'.

Of course, tuning of the application itself is the most important step... -- Vojtěch Zeisek Komunita openSUSE GNU/Linuxu Community of the openSUSE GNU/Linux http://www.opensuse.org/ http://trapa.cz/