Recommended settings for PostgreSQL
Hi, I have an existing few RBDs. I would like to create a new RBD Image for PostgreSQL. Do you have any suggestions for such use cases? For example; Currently defaults are: Object size (4MB) and Stripe Unit (None) Features: Deep flatten + Layering + Exclusive Lock + Object Map + FastDiff Should I use as is or should I use 16KB of object size and different sets of features for PostgreSQL? Thanks, Gencer.
Hi, Ceph for a transaction database bad solution from medium loads. сб, 17 окт. 2020 г. в 16:29, Gencer W. Genç <gencer@gencgiyen.com>:
Hi,
I have an existing few RBDs. I would like to create a new RBD Image for PostgreSQL. Do you have any suggestions for such use cases? For example;
Currently defaults are:
Object size (4MB) and Stripe Unit (None) Features: Deep flatten + Layering + Exclusive Lock + Object Map + FastDiff
Should I use as is or should I use 16KB of object size and different sets of features for PostgreSQL?
Thanks, Gencer. _______________________________________________ ceph-users mailing list -- ceph-users@ceph.io To unsubscribe send an email to ceph-users-leave@ceph.io
Hi Irek, In the past I see some good results (benchmark & latencies) for MySQL and PostgreSQL. However, I've always used 4MB object size. Maybe i can get much better performance on smaller object size. Haven't tried actually. Why are you not recommending this setup actually? Gencer. On 17.10.2020 18:01:19, Irek Fasikhov <malmyzh@gmail.com> wrote: Hi, Ceph for a transaction database bad solution from medium loads. сб, 17 окт. 2020 г. в 16:29, Gencer W. Genç <gencer@gencgiyen.com [mailto:gencer@gencgiyen.com]>: Hi, I have an existing few RBDs. I would like to create a new RBD Image for PostgreSQL. Do you have any suggestions for such use cases? For example; Currently defaults are: Object size (4MB) and Stripe Unit (None) Features: Deep flatten + Layering + Exclusive Lock + Object Map + FastDiff Should I use as is or should I use 16KB of object size and different sets of features for PostgreSQL? Thanks, Gencer. _______________________________________________ ceph-users mailing list -- ceph-users@ceph.io [mailto:ceph-users@ceph.io] To unsubscribe send an email to ceph-users-leave@ceph.io [mailto:ceph-users-leave@ceph.io]
Hi, After reading few sources, I decided to use dedicated NVMe Disk with native replication. Those benchmarks and performances I was referred for internal use only. Not much data stored there. I believe for production usages, it will deliver more trouble than advantages. Thanks, Gencer. On 17.10.2020 18:39:44, Gencer W. Genç <gencer@gencgiyen.com> wrote: Hi Irek, In the past I see some good results (benchmark & latencies) for MySQL and PostgreSQL. However, I've always used 4MB object size. Maybe i can get much better performance on smaller object size. Haven't tried actually. Why are you not recommending this setup actually? Gencer. On 17.10.2020 18:01:19, Irek Fasikhov <malmyzh@gmail.com> wrote: Hi, Ceph for a transaction database bad solution from medium loads. сб, 17 окт. 2020 г. в 16:29, Gencer W. Genç <gencer@gencgiyen.com [mailto:gencer@gencgiyen.com]>: Hi, I have an existing few RBDs. I would like to create a new RBD Image for PostgreSQL. Do you have any suggestions for such use cases? For example; Currently defaults are: Object size (4MB) and Stripe Unit (None) Features: Deep flatten + Layering + Exclusive Lock + Object Map + FastDiff Should I use as is or should I use 16KB of object size and different sets of features for PostgreSQL? Thanks, Gencer. _______________________________________________ ceph-users mailing list -- ceph-users@ceph.io [mailto:ceph-users@ceph.io] To unsubscribe send an email to ceph-users-leave@ceph.io [mailto:ceph-users-leave@ceph.io]
In the past I see some good results (benchmark & latencies) for MySQL and PostgreSQL. However, I've always used 4MB object size. Maybe i can get much better performance on smaller object size. Haven't tried actually.
Did you tune mysql / postgres for this setup? Did you have a default ceph rbd setup?
Another option is to let PosgreSQL do the replication with local storage. There are great reasons for Ceph, but databases optimize for this kind of thing extremely well. With replication in hand, run snapshots to RADOS buckets for long term storage.
On Oct 17, 2020, at 7:28 AM, Gencer W. Genç <gencer@gencgiyen.com> wrote:
Hi,
I have an existing few RBDs. I would like to create a new RBD Image for PostgreSQL. Do you have any suggestions for such use cases? For example;
Currently defaults are:
Object size (4MB) and Stripe Unit (None) Features: Deep flatten + Layering + Exclusive Lock + Object Map + FastDiff
Should I use as is or should I use 16KB of object size and different sets of features for PostgreSQL?
Thanks, Gencer. _______________________________________________ ceph-users mailing list -- ceph-users@ceph.io To unsubscribe send an email to ceph-users-leave@ceph.io
Another path that we have been investigating is to use some NVMe on the database machine as a cache (bcache, cachefs, etc). Several TB of U.2 drives in a striped-LVM should enhance performance for 'hot' data and cover for the issues of storing a large DB in Ceph. Note that we haven't tried this yet, but there are at least some discussions for MySQL. -Dave Dave Hall Binghamton University On 10/19/2020 10:49 PM, Brian Topping wrote:
Another option is to let PosgreSQL do the replication with local storage. There are great reasons for Ceph, but databases optimize for this kind of thing extremely well.
With replication in hand, run snapshots to RADOS buckets for long term storage.
On Oct 17, 2020, at 7:28 AM, Gencer W. Genç <gencer@gencgiyen.com> wrote:
Hi,
I have an existing few RBDs. I would like to create a new RBD Image for PostgreSQL. Do you have any suggestions for such use cases? For example;
Currently defaults are:
Object size (4MB) and Stripe Unit (None) Features: Deep flatten + Layering + Exclusive Lock + Object Map + FastDiff
Should I use as is or should I use 16KB of object size and different sets of features for PostgreSQL?
Thanks, Gencer. _______________________________________________ ceph-users mailing list -- ceph-users@ceph.io To unsubscribe send an email to ceph-users-leave@ceph.io
ceph-users mailing list -- ceph-users@ceph.io To unsubscribe send an email to ceph-users-leave@ceph.io
I wanted to create a few statefull containers with mysql/postgres that did not depend on local persistant storage, so I can dynamically move them around. What about using; - a 1x replicated pool and use rbd mirror, - or having postgres use 2 1x replicated pools - or upon task launch create an lvm mirror between a ceph rbd and a local drive and use local drive as a primary access device (if that is even possible with lvm) -----Original Message----- Cc: ceph-users@ceph.io Subject: *****SPAM***** [ceph-users] Re: Recommended settings for PostgreSQL Another option is to let PosgreSQL do the replication with local storage. There are great reasons for Ceph, but databases optimize for this kind of thing extremely well. With replication in hand, run snapshots to RADOS buckets for long term storage.
Hi,
I have an existing few RBDs. I would like to create a new RBD Image for PostgreSQL. Do you have any suggestions for such use cases? For example;
Currently defaults are:
Object size (4MB) and Stripe Unit (None) Features: Deep flatten + Layering + Exclusive Lock + Object Map + FastDiff
Should I use as is or should I use 16KB of object size and different
sets of features for PostgreSQL?
Thanks, Gencer.
participants (5)
-
Brian Topping
-
Dave Hall
-
Gencer W. Genç
-
Irek Fasikhov
-
Marc Roos