Best FS fo PostgreSQL/MariaDB

classic Classic list List threaded Threaded
20 messages Options
Reply | Threaded
Open this post in threaded view
|

Best FS fo PostgreSQL/MariaDB

Vojtěch Zeisek-2
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/

signature.asc (484 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Best FS fo PostgreSQL/MariaDB

Richard Brown
On 25 November 2015 at 10:27, Vojtěch Zeisek
<[hidden email]> 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
>
> http://www.opensuse.org/
> http://trapa.cz/

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: [hidden email]
To contact the owner, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Best FS fo PostgreSQL/MariaDB

Vojtěch Zeisek-2
Dne St 25. listopadu 2015 10:31:39, Richard Brown napsal(a):

> On 25 November 2015 at 10:27, Vojtěch Zeisek
> <[hidden email]> 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/

signature.asc (484 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Best FS fo PostgreSQL/MariaDB

Marcus Rueckert-3
In reply to this post by Richard Brown
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: [hidden email]
To contact the owner, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Best FS fo PostgreSQL/MariaDB

Vojtěch Zeisek-2
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/

signature.asc (484 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Best FS fo PostgreSQL/MariaDB

jdd@dodin.org
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: [hidden email]
To contact the owner, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Best FS fo PostgreSQL/MariaDB

Vojtěch Zeisek-2
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/

signature.asc (484 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Best FS fo PostgreSQL/MariaDB

jdd@dodin.org
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: [hidden email]
To contact the owner, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Best FS fo PostgreSQL/MariaDB

Vojtěch Zeisek-2
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/

signature.asc (484 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Best FS fo PostgreSQL/MariaDB

Vojtěch Zeisek-2
Dne Čt 26. listopadu 2015 13:58:23 jste napsal(a):
> I've also often heard people complaining that because of ACID DBs are
> wearing the SSD very fast, are you concerned about that?

I think this used to be case few years ago, but as far as I know, it should be
alright now.

--
Vojtěch Zeisek

Komunita openSUSE GNU/Linuxu
Community of the openSUSE GNU/Linux

http://www.opensuse.org/
http://trapa.cz/

signature.asc (484 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Best FS fo PostgreSQL/MariaDB

Anton Aylward-2
In reply to this post by Vojtěch Zeisek-2
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: [hidden email]
To contact the owner, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Best FS fo PostgreSQL/MariaDB

John Andersen-2
In reply to this post by Vojtěch Zeisek-2
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 11/26/2015 05:18 AM, Vojtěch Zeisek wrote:
> Dne Čt 26. listopadu 2015 13:58:23 jste napsal(a):
>> I've also often heard people complaining that because of ACID DBs are wearing the SSD very
>> fast, are you concerned about that?
>
> I think this used to be case few years ago, but as far as I know, it should be alright now.
>

Define "alright now".

Did the software change what it was doing, or did the existing computers
all magically get upgraded SSDs?  Many are stuck with the hardware they
bought a "few years ago".


- --
After all is said and done, more is said than done.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2

iEYEARECAAYFAlZXX3QACgkQv7M3G5+2DLKL3QCffVj6mFAMlTw07/lV/oh0B0H1
vE8An390zCntFXmFiIblSwTlsjCoFZeV
=zAbC
-----END PGP SIGNATURE-----
--
To unsubscribe, e-mail: [hidden email]
To contact the owner, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Best FS fo PostgreSQL/MariaDB

Vojtěch Zeisek-2
Dne Čt 26. listopadu 2015 11:37:24, John Andersen napsal(a):

> On 11/26/2015 05:18 AM, Vojtěch Zeisek wrote:
> > Dne Čt 26. listopadu 2015 13:58:23 jste napsal(a):
> >> I've also often heard people complaining that because of ACID DBs are
> >> wearing the SSD very fast, are you concerned about that?
> >
> > I think this used to be case few years ago, but as far as I know, it
> > should be alright now.
> Define "alright now".
>
> Did the software change what it was doing, or did the existing computers
> all magically get upgraded SSDs?  Many are stuck with the hardware they
> bought a "few years ago".
I mean distributions/HW since last year.I really haven't heard many complains
about lifespan of SSDs. I have very positive experience with it in several
computers (although not in server).

--
Vojtěch Zeisek

Komunita openSUSE GNU/Linuxu
Community of the openSUSE GNU/Linux

http://www.opensuse.org/
http://trapa.cz/

signature.asc (484 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Best FS fo PostgreSQL/MariaDB

Stanislav Baiduzhyi-2
In reply to this post by Anton Aylward-2
On Thu, Nov 26, 2015 at 5:36 PM, Anton Aylward
<[hidden email]> 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: [hidden email]
To contact the owner, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Best FS fo PostgreSQL/MariaDB

Anton Aylward-2
On 12/03/2015 05:31 AM, Stanislav Baiduzhyi wrote:

> On Thu, Nov 26, 2015 at 5:36 PM, Anton Aylward
> <[hidden email]> 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: [hidden email]
To contact the owner, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Best FS fo PostgreSQL/MariaDB

Vojtěch Zeisek-2
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
> > <[hidden email]> 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/

signature.asc (484 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Best FS fo PostgreSQL/MariaDB

Vojtěch Zeisek-2
In reply to this post by Vojtěch Zeisek-2
Dne Čt 3. prosince 2015 11:33:55 jste napsal(a):

> On Fri, Nov 27, 2015 at 8:52 AM, Vojtěch Zeisek
> <[hidden email]> wrote:
> > Dne Čt 26. listopadu 2015 11:37:24, John Andersen napsal(a):
> >> On 11/26/2015 05:18 AM, Vojtěch Zeisek wrote:
> >> > Dne Čt 26. listopadu 2015 13:58:23 jste napsal(a):
> >> >> I've also often heard people complaining that because of ACID DBs are
> >> >> wearing the SSD very fast, are you concerned about that?
> >> >
> >> > I think this used to be case few years ago, but as far as I know, it
> >> > should be alright now.
> >>
> >> Define "alright now".
> >>
> >> Did the software change what it was doing, or did the existing computers
> >> all magically get upgraded SSDs?  Many are stuck with the hardware they
> >> bought a "few years ago".
> >
> > I mean distributions/HW since last year.I really haven't heard many
> > complains about lifespan of SSDs. I have very positive experience with it
> > in several computers (although not in server).
>
> Well I was asking because I've heard the exact opposite, pretty much
> everyone who runs ACID-compliant DB moved back to HDD raids. So
> interested in your take on this, and of course your future experience
> :)
One my friend provides webhosting (several dozens mainly smaller webs - mainly
private, small companies etc.). After half year on SSD (Centos 7, Ext4) he is
very happy with performance as well as with wearing out (almost none).

--
Vojtěch Zeisek

Komunita openSUSE GNU/Linuxu
Community of the openSUSE GNU/Linux

http://www.opensuse.org/
http://trapa.cz/

signature.asc (484 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Best FS fo PostgreSQL/MariaDB

Anton Aylward-2
In reply to this post by Vojtěch Zeisek-2
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: [hidden email]
To contact the owner, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Best FS fo PostgreSQL/MariaDB

Vojtěch Zeisek-2
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/

signature.asc (484 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Best FS fo PostgreSQL/MariaDB

Anton Aylward-2
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: [hidden email]
To contact the owner, e-mail: [hidden email]