[opensuse] Best FS fo PostgreSQL/MariaDB
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 -- Vojtěch Zeisek Komunita openSUSE GNU/Linuxu Community of the openSUSE GNU/Linux http://www.opensuse.org/ http://trapa.cz/
On 25 November 2015 at 10:27, Vojtěch Zeisek <vojtech.zeisek@opensuse.org> 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
-- Vojtěch Zeisek
Komunita openSUSE GNU/Linuxu Community of the openSUSE GNU/Linux
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 -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
Dne St 25. listopadu 2015 10:31:39, Richard Brown napsal(a):
On 25 November 2015 at 10:27, Vojtěch Zeisek <vojtech.zeisek@opensuse.org> 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/
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. ext4 or xfs. with the preference to xfs as it gives you the most *consistent* performance. darix -- openSUSE - SUSE Linux is my linux openSUSE is good for you www.opensuse.org -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
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/
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? jdd -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
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/
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? jdd -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
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 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. Make sure you are using InnoDB storage and are configured for caching. Then and only then try performance measurements. I recall one search intensive application where putting the indexes on a faster media (it was a memory based FS loaded up at startup) was the key to performance. Of course that depends on the application/use profile. Which is why making measurements with different configurations to find out what works beet for you is important. -- A: Yes. > Q: Are you sure? >> A: Because it reverses the logical flow of conversation. >>> Q: Why is top posting frowned upon? -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
On Thu, Nov 26, 2015 at 5:36 PM, Anton Aylward <opensuse@antonaylward.com> 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
On 12/03/2015 05:31 AM, Stanislav Baiduzhyi wrote:
On Thu, Nov 26, 2015 at 5:36 PM, Anton Aylward <opensuse@antonaylward.com> 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.
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. As I keep saying ... Context is Everything Perhaps the OP would care to comment on the nature of the application that the database is being used for so that you and I don't end up doing a "yes-but" ping pong game any further. -- A: Yes. > Q: Are you sure? >> A: Because it reverses the logical flow of conversation. >>> Q: Why is top posting frowned upon? -- 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 <opensuse@antonaylward.com> 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/
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. 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. Some CMSs do various kinds of caching of their own, again you have options of how much of that can be in memory. Check out 'memcached' and 'redis'. -- A: Yes. > Q: Are you sure? >> A: Because it reverses the logical flow of conversation. >>> Q: Why is top posting frowned upon? -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
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/
On 12/03/2015 11:03 AM, Vojtěch Zeisek wrote:
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.
Depending on how you cut the statistics having them in the database might not always be a good move. If this is a function that is to be displayed to the web users, then its really a web function and so should be in the database, but if its an 'accounting' function, what sites get use, who does what, weekly reports, them having it in the database puts a load on the database that isn't needed. Its back to the "Context is Everything" discussion. The use-case is what matters. That being said, you can over-optimise for one and only one use case. Back when SSD was still expensive I saw a use-case that put the system on the SSD for little benefit because the loading of binaries was a one time occurrence and the real traffic was to the "to huge to fit on the SSD as well as the system" database. Heck, just putting the index files rather than the system on the SSD would have been better! Now the SSDs are large and cheap enough that it doesn't matter any more :-) Never the less, memory is faster :-) -- A: Yes. > Q: Are you sure? >> A: Because it reverses the logical flow of conversation. >>> Q: Why is top posting frowned upon? -- To unsubscribe, e-mail: opensuse+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse+owner@opensuse.org
participants (6)
-
Anton Aylward -
jdd -
Marcus Rueckert -
Richard Brown -
Stanislav Baiduzhyi -
Vojtěch Zeisek