Re: software or hardware RAID?
On 17/03/19, Rory Campbell-Lange (r...@campbell-lange.net) wrote: > We aren't sure whether to use software MDRaid or a MegaRAID card. > > We're buying some new Postgres servers with > > 2 x 240GB Intel SSD S4610 (RAID1 : system) > 4 x 960GB Intel SSD S4610 (RAID10 : db) > > We'll be using Postgres 11 on Debian. > > The MegaRAID 9271-8i with flash cache protection is available from our > provider. I think they may also have the 9361-8i which is 12Gb/s. > > Our current servers which use the LSI 9261 with SSDs and we don't see > any IO significant load as we are in RAM most of the time and the RAID > card seems to flatten out any IO spikes. > > We use MDRaid elsewhere but we've never used it for our databases > before. Apologies for re-heating this email from last week. I could really do with the advice. Has anyone got any general comments on whether software RAID or an LSI card is preferable? We will be replicating load on an existing server, which has an LSI 9261 card. Below is some stats from sar showing a "heavy" period of load on vdisk sda 00:00:01 DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 14:15:01 sda 112.82643.09 14986.24138.53 2.09 18.50 0.25 2.86 14:25:01 sda 108.52270.17 15682.94147.01 1.87 17.22 0.25 2.73 14:35:01 sda 107.96178.25 14868.52139.37 1.70 15.73 0.23 2.53 14:45:01 sda 150.97748.94 16919.69117.03 1.83 12.11 0.22 3.28 Thanks for any advice. Rory
Re: software or hardware RAID?
On 3/23/19 7:09 AM, Rory Campbell-Lange wrote: On 17/03/19, Rory Campbell-Lange (r...@campbell-lange.net) wrote: We aren't sure whether to use software MDRaid or a MegaRAID card. We're buying some new Postgres servers with 2 x 240GB Intel SSD S4610 (RAID1 : system) 4 x 960GB Intel SSD S4610 (RAID10 : db) We'll be using Postgres 11 on Debian. The MegaRAID 9271-8i with flash cache protection is available from our provider. I think they may also have the 9361-8i which is 12Gb/s. Our current servers which use the LSI 9261 with SSDs and we don't see any IO significant load as we are in RAM most of the time and the RAID card seems to flatten out any IO spikes. We use MDRaid elsewhere but we've never used it for our databases before. Apologies for re-heating this email from last week. I could really do with the advice. Has anyone got any general comments on whether software RAID or an LSI card is preferable? We will be replicating load on an existing server, which has an LSI 9261 card. Below is some stats from sar showing a "heavy" period of load on vdisk sda 00:00:01 DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 14:15:01 sda 112.82643.09 14986.24138.53 2.09 18.50 0.25 2.86 14:25:01 sda 108.52270.17 15682.94147.01 1.87 17.22 0.25 2.73 14:35:01 sda 107.96178.25 14868.52139.37 1.70 15.73 0.23 2.53 14:45:01 sda 150.97748.94 16919.69117.03 1.83 12.11 0.22 3.28 Thanks for any advice. Rory I have run both software and hardware (though different than the card you listed), and had good success with both. In cases where I had little money, just drop 6 drives into a md raid 10, and run happy for years and years. I run production PG 11 on software raid 10 as we speek. I personally prefer software raid, for a few reasons: 1) you'll probably be running on a batter backup anyway, so missing raid card battery isn't that much 2) 100% compatible with any other hardware you wanna run. Sucky thing about hardware card is your on that one forever. 3) tooling is much better and simpler. I really hate the crappy bios raid screen. I never know if adding an HD to an exiting raid will wipe it or maintain it. 4) I setup smartctl to watch and report on drives. Even a 50% chance it detects before failure is a net benefit. You cant always to that through hardware raid You can always start with software raid, see how it runs for a while, then buy hardware raid if its not working out. -Andy
Re: Installing PostgreSQL on Oracle Solaris
On 3/22/19 11:59 PM, Igor Korot wrote: Hi, ALL, I tried to follow an instructions at https://www.postgresql.org/ftp/binary/v9.6.1/solaris/solaris11/i386/ in the README but I received following: Off hand I would say the user you are running as does not have the permissions to unpack the tarball in the location you have selected. [code] igor@solaris:/usr$ sudo bunzip2 < postgresql-9.6.1-S11.i386-64.tar.bz2 | tar xpf - tar: postgres: Permission denied tar: postgres: Permission denied tar: cannot open postgres: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/doc: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/doc/extension: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/doc/extension: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/doc/extension: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/doc/extension: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/doc/extension: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64/server: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64/server: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64/server/fe_utils: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64/server/fe_utils: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64/server/fe_utils: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64/server/fe_utils: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64/server/fe_utils: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64/server/fe_utils: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64/server: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64/server: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64/server: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64/server: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64/server/utils: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64/server/utils: No such file or directory tar: postgres: Permission denied [/code] And many other like those. Is the instructions wrong? I'm trying 9.6.1 because I'm using it on Windows/OSX for libpq. Thank you. -- Adrian Klaver adrian.kla...@aklaver.com
Re: Installing PostgreSQL on Oracle Solaris
On 3/23/19 8:58 AM, Adrian Klaver wrote: On 3/22/19 11:59 PM, Igor Korot wrote: Hi, ALL, I tried to follow an instructions at https://www.postgresql.org/ftp/binary/v9.6.1/solaris/solaris11/i386/ in the README but I received following: Off hand I would say the user you are running as does not have the permissions to unpack the tarball in the location you have selected. [code] igor@solaris:*/usr*$ sudo bunzip2 < postgresql-9.6.1-S11.i386-64.tar.bz2 | tar xpf - tar: postgres: Permission denied tar: postgres: Permission denied tar: cannot open postgres: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg: No such file or directory Yeah. Unpacking it directly into /usr doesn't seem a particularly wise idea. tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/doc: No such file or directory [snip] 4/server: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64/server: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64/server/utils: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64/server/utils: No such file or directory tar: postgres: Permission denied [/code] And many other like those. Is the instructions wrong? I'm trying 9.6.1 because I'm using it on Windows/OSX for libpq. Thank you. -- Angular momentum makes the world go 'round.
Re: Installing PostgreSQL on Oracle Solaris
Hi On Sat, Mar 23, 2019 at 9:25 AM Ron wrote: > > On 3/23/19 8:58 AM, Adrian Klaver wrote: > > On 3/22/19 11:59 PM, Igor Korot wrote: > > Hi, ALL, > I tried to follow an instructions at > https://www.postgresql.org/ftp/binary/v9.6.1/solaris/solaris11/i386/ > in the README but I received following: > > > Off hand I would say the user you are running as does not have the > permissions to unpack the tarball in the location you have selected. > > > [code] > igor@solaris:/usr$ sudo bunzip2 < postgresql-9.6.1-S11.i386-64.tar.bz2 You mean even running as "sudo"? Thank you. > | tar xpf - > tar: postgres: Permission denied > tar: postgres: Permission denied > tar: cannot open postgres: No such file or directory > tar: postgres: Permission denied > tar: cannot open postgres/9.6-pgdg: No such file or directory > > > Yeah. Unpacking it directly into /usr doesn't seem a particularly wise idea. > > tar: postgres: Permission denied > tar: cannot open postgres/9.6-pgdg/doc: No such file or directory > > [snip] > > 4/server: No such file or directory > tar: postgres: Permission denied > tar: cannot open postgres/9.6-pgdg/include/64/server: No such file or > directory > tar: postgres: Permission denied > tar: cannot open postgres/9.6-pgdg/include/64/server/utils: No such > file or directory > tar: postgres: Permission denied > tar: cannot open postgres/9.6-pgdg/include/64/server/utils: No such > file or directory > tar: postgres: Permission denied > [/code] > > And many other like those. > > Is the instructions wrong? > > I'm trying 9.6.1 because I'm using it on Windows/OSX for libpq. > > Thank you. > > > > > > -- > Angular momentum makes the world go 'round.
Re: Installing PostgreSQL on Oracle Solaris
On 3/22/19 9:50 PM, Igor Korot wrote: Hi On Sat, Mar 23, 2019 at 9:25 AM Ron wrote: On 3/23/19 8:58 AM, Adrian Klaver wrote: On 3/22/19 11:59 PM, Igor Korot wrote: Hi, ALL, I tried to follow an instructions at https://www.postgresql.org/ftp/binary/v9.6.1/solaris/solaris11/i386/ in the README but I received following: Off hand I would say the user you are running as does not have the permissions to unpack the tarball in the location you have selected. [code] igor@solaris:/usr$ sudo bunzip2 < postgresql-9.6.1-S11.i386-64.tar.bz2 You mean even running as "sudo"? Is that a binary or source tarball? If binary, is it designed specifically for Solaris? Does the tarball's README *tell you* to untar it under /usr? Thank you. | tar xpf - tar: postgres: Permission denied tar: postgres: Permission denied tar: cannot open postgres: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg: No such file or directory Yeah. Unpacking it directly into /usr doesn't seem a particularly wise idea. tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/doc: No such file or directory [snip] 4/server: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64/server: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64/server/utils: No such file or directory tar: postgres: Permission denied tar: cannot open postgres/9.6-pgdg/include/64/server/utils: No such file or directory tar: postgres: Permission denied [/code] And many other like those. Is the instructions wrong? I'm trying 9.6.1 because I'm using it on Windows/OSX for libpq. Thank you. -- Angular momentum makes the world go 'round. -- Angular momentum makes the world go 'round.
Re: Installing PostgreSQL on Oracle Solaris
On Sat, Mar 23, 2019 at 3:48 PM Igor Korot wrote: > > You mean even running as "sudo"? > igor@solaris:/usr$ sudo bunzip2 < postgresql-9.6.1-S11.i386-64.tar.bz2 | tar xpf - No, you are not running tar as sudo. I would at least suggest: cd /usr/local/src sudo tar --bzip2 xf /usr/postgresql-9.6.1-S11.i386-64.tar.bz2 a bit dependen of which tar you have. otherwise: cd /usr/local/src sudo tar xf <( bzcat /usr/postgresql-9.6.1-S11.i386-64.tar.bz2 )
Re: Installing PostgreSQL on Oracle Solaris
Igor Korot writes: igor@solaris:/usr$ sudo bunzip2 < postgresql-9.6.1-S11.i386-64.tar.bz2 | tar xpf - > On Sat, Mar 23, 2019 at 9:25 AM Ron wrote: >> Off hand I would say the user you are running as does not have the >> permissions to unpack the tarball in the location you have selected. > You mean even running as "sudo"? In the above, the "sudo" raises the permissions of the bunzip2 program (quite uselessly, AFAICS), while doing nothing for the tar program in the other pipe step. Put the "sudo" in the other pipe step. (This is assuming that unpacking straight into /usr is actually what you want to do. I share the doubts of the other responders about that being a wise procedure.) regards, tom lane
Re: Installing PostgreSQL on Oracle Solaris
On 3/23/19 8:42 AM, Tom Lane wrote: Igor Korot writes: igor@solaris:/usr$ sudo bunzip2 < postgresql-9.6.1-S11.i386-64.tar.bz2 | tar xpf - On Sat, Mar 23, 2019 at 9:25 AM Ron wrote: Off hand I would say the user you are running as does not have the permissions to unpack the tarball in the location you have selected. You mean even running as "sudo"? In the above, the "sudo" raises the permissions of the bunzip2 program (quite uselessly, AFAICS), while doing nothing for the tar program in the other pipe step. Put the "sudo" in the other pipe step. (This is assuming that unpacking straight into /usr is actually what you want to do. I share the doubts of the other responders about that being a wise procedure.) It is what the README says: https://www.postgresql.org/ftp/binary/v9.6.1/solaris/solaris11/i386/ "The files will be installed in directories under 'postgres/9.6-pgdg', so if you install under /usr they will be at locations similar to any PostgreSQL in Solaris." regards, tom lane -- Adrian Klaver adrian.kla...@aklaver.com
Re: Installing PostgreSQL on Oracle Solaris
Hi, On Sat, Mar 23, 2019 at 9:55 AM Michael Loftis wrote: > > > > On Sat, Mar 23, 2019 at 08:48 Igor Korot wrote: >> >> >> > [code] >> > igor@solaris:/usr$ sudo bunzip2 < postgresql-9.6.1-S11.i386-64.tar.bz2 >> >> You mean even running as "sudo"? >> >> Thank you. > > > > You ran the bunzip2 as sudo. No reason to do that. I didn't look at the > instructions you did but you'd need the tar command under sudo. Running below command it looks like everything went good. igor@solaris:/usr$ sudo bunzip2 < postgresql-9.6.1-S11.i386-64.tar.bz2 | sudo tar xpf - Thank you. > > >> >> >> > | tar xpf - >> > tar: postgres: Permission denied >> > tar: postgres: Permission denied >> > tar: cannot open postgres: No such file or directory >> > tar: postgres: Permission denied >> > tar: cannot open postgres/9.6-pgdg: No such file or directory >> > >> > >> > Yeah. Unpacking it directly into /usr doesn't seem a particularly wise >> > idea. >> > >> > tar: postgres: Permission denied >> > tar: cannot open postgres/9.6-pgdg/doc: No such file or directory >> > >> > [snip] >> > >> > 4/server: No such file or directory >> > tar: postgres: Permission denied >> > tar: cannot open postgres/9.6-pgdg/include/64/server: No such file or >> > directory >> > tar: postgres: Permission denied >> > tar: cannot open postgres/9.6-pgdg/include/64/server/utils: No such >> > file or directory >> > tar: postgres: Permission denied >> > tar: cannot open postgres/9.6-pgdg/include/64/server/utils: No such >> > file or directory >> > tar: postgres: Permission denied >> > [/code] >> > >> > And many other like those. >> > >> > Is the instructions wrong? >> > >> > I'm trying 9.6.1 because I'm using it on Windows/OSX for libpq. >> > >> > Thank you. >> > >> > >> > >> > >> > >> > -- >> > Angular momentum makes the world go 'round. >> > -- > > "Genius might be described as a supreme capacity for getting its possessors > into trouble of all kinds." > -- Samuel Butler
Re: Installing PostgreSQL on Oracle Solaris
Hi, On Sat, Mar 23, 2019 at 10:17 AM Ron wrote: > > On 3/22/19 9:50 PM, Igor Korot wrote: > > Hi > > On Sat, Mar 23, 2019 at 9:25 AM Ron wrote: > > On 3/23/19 8:58 AM, Adrian Klaver wrote: > > On 3/22/19 11:59 PM, Igor Korot wrote: > > Hi, ALL, > I tried to follow an instructions at > https://www.postgresql.org/ftp/binary/v9.6.1/solaris/solaris11/i386/ > in the README but I received following: > > > Off hand I would say the user you are running as does not have the > permissions to unpack the tarball in the location you have selected. > > > [code] > igor@solaris:/usr$ sudo bunzip2 < postgresql-9.6.1-S11.i386-64.tar.bz2 > > You mean even running as "sudo"? > > > Is that a binary or source tarball? Binary. > > If binary, is it designed specifically for Solaris? Yes, the link I posted says so. > > Does the tarball's README tell you to untar it under /usr? Not specifically. But it mentioned /usr. Thank you. > > Thank you. > > | tar xpf - > tar: postgres: Permission denied > tar: postgres: Permission denied > tar: cannot open postgres: No such file or directory > tar: postgres: Permission denied > tar: cannot open postgres/9.6-pgdg: No such file or directory > > > Yeah. Unpacking it directly into /usr doesn't seem a particularly wise idea. > > tar: postgres: Permission denied > tar: cannot open postgres/9.6-pgdg/doc: No such file or directory > > [snip] > > 4/server: No such file or directory > tar: postgres: Permission denied > tar: cannot open postgres/9.6-pgdg/include/64/server: No such file or > directory > tar: postgres: Permission denied > tar: cannot open postgres/9.6-pgdg/include/64/server/utils: No such > file or directory > tar: postgres: Permission denied > tar: cannot open postgres/9.6-pgdg/include/64/server/utils: No such > file or directory > tar: postgres: Permission denied > [/code] > > And many other like those. > > Is the instructions wrong? > > I'm trying 9.6.1 because I'm using it on Windows/OSX for libpq. > > Thank you. > > > > > > -- > Angular momentum makes the world go 'round. > > > -- > Angular momentum makes the world go 'round.
Re: Installing PostgreSQL on Oracle Solaris
Hi, On Sat, Mar 23, 2019 at 10:36 AM Hans Schou wrote: > > > > On Sat, Mar 23, 2019 at 3:48 PM Igor Korot wrote: >> >> >> You mean even running as "sudo"? > > > igor@solaris:/usr$ sudo bunzip2 < postgresql-9.6.1-S11.i386-64.tar.bz2 > | tar xpf - > > No, you are not running tar as sudo. > > I would at least suggest: > cd /usr/local/src > sudo tar --bzip2 xf /usr/postgresql-9.6.1-S11.i386-64.tar.bz2 > > a bit dependen of which tar you have. > > otherwise: > cd /usr/local/src > sudo tar xf <( bzcat /usr/postgresql-9.6.1-S11.i386-64.tar.bz2 ) Thank you. >
Re: Installing PostgreSQL on Oracle Solaris
Hi, On Sat, Mar 23, 2019 at 10:42 AM Tom Lane wrote: > > Igor Korot writes: > igor@solaris:/usr$ sudo bunzip2 < postgresql-9.6.1-S11.i386-64.tar.bz2 > | tar xpf - > > > On Sat, Mar 23, 2019 at 9:25 AM Ron wrote: > >> Off hand I would say the user you are running as does not have the > >> permissions to unpack the tarball in the location you have selected. > > > You mean even running as "sudo"? > > In the above, the "sudo" raises the permissions of the bunzip2 program > (quite uselessly, AFAICS), while doing nothing for the tar program in > the other pipe step. Put the "sudo" in the other pipe step. > > (This is assuming that unpacking straight into /usr is actually what > you want to do. I share the doubts of the other responders about > that being a wise procedure.) Well, as I said I don't need a server - I just need libpq. So I didn't really care where to install it. ;-) Thank you. > > regards, tom lane
Re: software or hardware RAID?
On Sat, Mar 23, 2019 at 9:40 AM Andy Colson wrote: > On 3/23/19 7:09 AM, Rory Campbell-Lange wrote: > > On 17/03/19, Rory Campbell-Lange (r...@campbell-lange.net) wrote: > >> We aren't sure whether to use software MDRaid or a MegaRAID card. > >> > >> We're buying some new Postgres servers with > >> > >> 2 x 240GB Intel SSD S4610 (RAID1 : system) > >> 4 x 960GB Intel SSD S4610 (RAID10 : db) > >> > >> We'll be using Postgres 11 on Debian. > >> > >> The MegaRAID 9271-8i with flash cache protection is available from our > >> provider. I think they may also have the 9361-8i which is 12Gb/s. > >> > >> Our current servers which use the LSI 9261 with SSDs and we don't see > >> any IO significant load as we are in RAM most of the time and the RAID > >> card seems to flatten out any IO spikes. > >> > >> We use MDRaid elsewhere but we've never used it for our databases > >> before. > > > > Apologies for re-heating this email from last week. I could really do > with the > > advice. > > > > Has anyone got any general comments on whether software RAID or an LSI > card > > is preferable? > > > > We will be replicating load on an existing server, which has an LSI 9261 > card. > > Below is some stats from sar showing a "heavy" period of load on vdisk > sda > > > > 00:00:01 DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz > await svctm %util > > 14:15:01 sda 112.82643.09 14986.24138.53 2.09 > 18.50 0.25 2.86 > > 14:25:01 sda 108.52270.17 15682.94147.01 1.87 > 17.22 0.25 2.73 > > 14:35:01 sda 107.96178.25 14868.52139.37 1.70 > 15.73 0.23 2.53 > > 14:45:01 sda 150.97748.94 16919.69117.03 1.83 > 12.11 0.22 3.28 > > > > Thanks for any advice. > > > > Rory > > > > > I have run both software and hardware (though different than the card you > listed), and had good success with both. In cases where I had little > money, just drop 6 drives into a md raid 10, and run happy for years and > years. I run production PG 11 on software raid 10 as we speek. > > I personally prefer software raid, for a few reasons: > 1) you'll probably be running on a batter backup anyway, so missing raid > card battery isn't that much > 2) 100% compatible with any other hardware you wanna run. Sucky thing > about hardware card is your on that one forever. This is the main reason I ditched the rsi raid card. But I went with zfs. It’s worth a look. > 3) tooling is much better and simpler. I really hate the crappy bios raid > screen. I never know if adding an HD to an exiting raid will wipe it or > maintain it. > 4) I setup smartctl to watch and report on drives. Even a 50% chance it > detects before failure is a net benefit. You cant always to that through > hardware raid > > You can always start with software raid, see how it runs for a while, then > buy hardware raid if its not working out. > > -Andy > > -- Charles L. Martin Martin Jones & Piemonte BUSINESS email: serv...@mjpdisability.com Personal email: clmar...@mjpdisability.com Decatur Office: 123 N. McDonough St. Decatur, GA 30030 404-373-3116 Fax 404-373-4110 Charlotte Office: 4601 Charlotte Park Drive, Suite 390 Charlotte, NC 28217 704-399-8890 Fax 888-490-1315
Re: software or hardware RAID?
On 23/03/19, Andy Colson (a...@squeakycode.net) wrote: > On 3/23/19 7:09 AM, Rory Campbell-Lange wrote: > > On 17/03/19, Rory Campbell-Lange (r...@campbell-lange.net) wrote: ... > > > We're buying some new Postgres servers with > > > > > > 2 x 240GB Intel SSD S4610 (RAID1 : system) > > > 4 x 960GB Intel SSD S4610 (RAID10 : db) > > > > > > We'll be using Postgres 11 on Debian. > > > > > > The MegaRAID 9271-8i with flash cache protection is available from our > > > provider. I think they may also have the 9361-8i which is 12Gb/s. > > > > > > Our current servers which use the LSI 9261 with SSDs and we don't see > > > any IO significant load as we are in RAM most of the time and the RAID > > > card seems to flatten out any IO spikes. > > > > > > We use MDRaid elsewhere but we've never used it for our databases > > > before. > > Has anyone got any general comments on whether software RAID or an LSI card > > is preferable? > > > > We will be replicating load on an existing server, which has an LSI 9261 > > card. > > Below is some stats from sar showing a "heavy" period of load on vdisk sda > > > > 00:00:01 DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await > > svctm %util > > 14:15:01 sda 112.82643.09 14986.24138.53 2.09 18.50 > > 0.25 2.86 > > 14:25:01 sda 108.52270.17 15682.94147.01 1.87 17.22 > > 0.25 2.73 > > 14:35:01 sda 107.96178.25 14868.52139.37 1.70 15.73 > > 0.23 2.53 > > 14:45:01 sda 150.97748.94 16919.69117.03 1.83 12.11 > > 0.22 3.28 > I have run both software and hardware (though different than the card you > listed), and had good success with both. In cases where I had little money, > just drop 6 drives into a md raid 10, and run happy for years and years. I > run production PG 11 on software raid 10 as we speek. > > I personally prefer software raid, for a few reasons: > 1) you'll probably be running on a batter backup anyway, so missing raid card > battery isn't that much > 2) 100% compatible with any other hardware you wanna run. Sucky thing about > hardware card is your on that one forever. > 3) tooling is much better and simpler. I really hate the crappy bios raid > screen. I never know if adding an HD to an exiting raid will wipe it or > maintain it. > 4) I setup smartctl to watch and report on drives. Even a 50% chance it > detects before failure is a net benefit. You cant always to that through > hardware raid > > You can always start with software raid, see how it runs for a while, then > buy hardware raid if its not working out. Thanks very much for the comments, Andy. If money was no object, would you choose a fancy hardware RAID card? You are right that the SSDs we are purchasing have enough cache + power to not need a BBU, and I agree that the management tools for software raid are much more convenient. Rory
Re: software or hardware RAID?
On 3/23/19 11:51 AM, Rory Campbell-Lange wrote: On 23/03/19, Andy Colson (a...@squeakycode.net) wrote: On 3/23/19 7:09 AM, Rory Campbell-Lange wrote: On 17/03/19, Rory Campbell-Lange (r...@campbell-lange.net) wrote: ... We're buying some new Postgres servers with 2 x 240GB Intel SSD S4610 (RAID1 : system) 4 x 960GB Intel SSD S4610 (RAID10 : db) We'll be using Postgres 11 on Debian. The MegaRAID 9271-8i with flash cache protection is available from our provider. I think they may also have the 9361-8i which is 12Gb/s. Our current servers which use the LSI 9261 with SSDs and we don't see any IO significant load as we are in RAM most of the time and the RAID card seems to flatten out any IO spikes. We use MDRaid elsewhere but we've never used it for our databases before. Has anyone got any general comments on whether software RAID or an LSI card is preferable? We will be replicating load on an existing server, which has an LSI 9261 card. Below is some stats from sar showing a "heavy" period of load on vdisk sda 00:00:01 DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 14:15:01 sda 112.82643.09 14986.24138.53 2.09 18.50 0.25 2.86 14:25:01 sda 108.52270.17 15682.94147.01 1.87 17.22 0.25 2.73 14:35:01 sda 107.96178.25 14868.52139.37 1.70 15.73 0.23 2.53 14:45:01 sda 150.97748.94 16919.69117.03 1.83 12.11 0.22 3.28 I have run both software and hardware (though different than the card you listed), and had good success with both. In cases where I had little money, just drop 6 drives into a md raid 10, and run happy for years and years. I run production PG 11 on software raid 10 as we speek. I personally prefer software raid, for a few reasons: 1) you'll probably be running on a batter backup anyway, so missing raid card battery isn't that much 2) 100% compatible with any other hardware you wanna run. Sucky thing about hardware card is your on that one forever. 3) tooling is much better and simpler. I really hate the crappy bios raid screen. I never know if adding an HD to an exiting raid will wipe it or maintain it. 4) I setup smartctl to watch and report on drives. Even a 50% chance it detects before failure is a net benefit. You cant always to that through hardware raid You can always start with software raid, see how it runs for a while, then buy hardware raid if its not working out. Thanks very much for the comments, Andy. If money was no object, would you choose a fancy hardware RAID card? You are right that the SSDs we are purchasing have enough cache + power to not need a BBU, and I agree that the management tools for software raid are much more convenient. Rory That's a tough question. I don't even have SSD's yet. I can't say what performance is like on HW Raid SSD vs SW Raid SSD. If money where no object, and I wanted performance over all else, I'd benchmark both. If usability were >= performance, I'd go with extra ram, and SW Raid SSD. And a BMW. :-) -Andy
Re: software or hardware RAID?
On Sun, Mar 17, 2019 at 11:54 PM Rory Campbell-Lange < r...@campbell-lange.net> wrote: > We aren't sure whether to use software MDRaid or a MegaRAID card. > Never go with hardRaid. I have had a breakdown on a hardware RAID and as it was special and not off-the-shelf, I could not move the disk to another controller. I think it was a capacitor, maybe capasitor plaegue. Only thing I had to do was to restore to the day before and the customer lost one days work. >From that on, I only use softRAID.
Re: software or hardware RAID?
On 23/03/19, Hans Schou (hans.sc...@gmail.com) wrote: > On Sun, Mar 17, 2019 at 11:54 PM Rory Campbell-Lange < > r...@campbell-lange.net> wrote: > > > We aren't sure whether to use software MDRaid or a MegaRAID card. > > Never go with hardRaid. I have had a breakdown on a hardware RAID and as > it was special and not off-the-shelf, I could not move the disk to another > controller. I think it was a capacitor, maybe capasitor plaegue. > Only thing I had to do was to restore to the day before and the customer > lost one days work. > > From that on, I only use softRAID. Thanks for that advice, Hans.
Re: software or hardware RAID?
On Sat, Mar 23, 2019 at 12:09:11PM +, Rory Campbell-Lange wrote: > On 17/03/19, Rory Campbell-Lange (r...@campbell-lange.net) wrote: > > We aren't sure whether to use software MDRaid or a MegaRAID card. > > > > We're buying some new Postgres servers with > > > > 2 x 240GB Intel SSD S4610 (RAID1 : system) > > 4 x 960GB Intel SSD S4610 (RAID10 : db) > > > > We'll be using Postgres 11 on Debian. > > > > The MegaRAID 9271-8i with flash cache protection is available from our > > provider. I think they may also have the 9361-8i which is 12Gb/s. > > > > Our current servers which use the LSI 9261 with SSDs and we don't see > > any IO significant load as we are in RAM most of the time and the RAID > > card seems to flatten out any IO spikes. > > > > We use MDRaid elsewhere but we've never used it for our databases > > before. > > Apologies for re-heating this email from last week. I could really do with the > advice. > > Has anyone got any general comments on whether software RAID or an LSI card > is preferable? > > We will be replicating load on an existing server, which has an LSI 9261 card. > Below is some stats from sar showing a "heavy" period of load on vdisk sda > > 00:00:01 DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await > svctm %util > 14:15:01 sda 112.82643.09 14986.24138.53 2.09 18.50 > 0.25 2.86 > 14:25:01 sda 108.52270.17 15682.94147.01 1.87 17.22 > 0.25 2.73 > 14:35:01 sda 107.96178.25 14868.52139.37 1.70 15.73 > 0.23 2.53 > 14:45:01 sda 150.97748.94 16919.69117.03 1.83 12.11 > 0.22 3.28 > > Thanks for any advice. > Rory Hi Rory, The main reason, in my opinion, to use a HW RAID card is for the NVRAM battery backed cache to support writing to traditional spinning disks. Since your SSDs have power-loss support, you do not need that and the HW RAID controller. For database use, you would almost certainly be using RAID 10 and software RAID 10 is extremely performant. I am in the middle of setting up a new system with NVMe SSD drives and HW RAID would be a terrible bottle-neck and software RAID is really the only realistice option. Regards, Ken
Re: Installing PostgreSQL on Oracle Solaris
Hi, ALL, On Fri, Mar 22, 2019 at 11:32 PM Igor Korot wrote: > > Hi, > > On Sat, Mar 23, 2019 at 10:42 AM Tom Lane wrote: > > > > Igor Korot writes: > > igor@solaris:/usr$ sudo bunzip2 < postgresql-9.6.1-S11.i386-64.tar.bz2 > > | tar xpf - > > > > > On Sat, Mar 23, 2019 at 9:25 AM Ron wrote: > > >> Off hand I would say the user you are running as does not have the > > >> permissions to unpack the tarball in the location you have selected. > > > > > You mean even running as "sudo"? > > > > In the above, the "sudo" raises the permissions of the bunzip2 program > > (quite uselessly, AFAICS), while doing nothing for the tar program in > > the other pipe step. Put the "sudo" in the other pipe step. > > > > (This is assuming that unpacking straight into /usr is actually what > > you want to do. I share the doubts of the other responders about > > that being a wise procedure.) > > Well, as I said I don't need a server - I just need libpq. > So I didn't really care where to install it. ;-) I am sorry to say this, but unfortunately I will have to build it from sources. It looks like the binary package I downloaded were built with the gcc and I'm trying to build my software with the Oracle DevStudio. Thank you all and sorry for wasting everybody's time. > > Thank you. > > > > > regards, tom lane
regr_slope returning NULL
Hi,
I'm not sure whether or not this is a bug, so I've posted here first (after
having posted on Stack Overflow).
Consider the following:
log=# create table sb1(id text, elapsed int, ts numeric);
CREATE TABLE
log=# insert into sb1 values
('317e',86,1552861322.627),('317e',58,1552861324.747),('317e',52,1552861325.722),('317e',58,1552861326.647),('317e',82,1552861327.609),('317e',118,1552861328.514),('317e',58,1552861329.336),('317e',58,1552861330.317),('317e',54,1552861330.935),('3441',68,1552861324.765),('3441',84,1552861326.665),('3441',56,1552861327.627),('3441',50,1552861330.952),('5fe6',42,1552993248.398),('5fe6',44,1552993255.883),('5fe6',44,1553166049.261),('c742',62,1552861322.149),('c742',68,1552861322.455);
INSERT 0 18
log=# select * from sb1 order by id, ts;
id | elapsed | ts
--+-+
317e | 86 | 1552861322.627
317e | 58 | 1552861324.747
317e | 52 | 1552861325.722
317e | 58 | 1552861326.647
317e | 82 | 1552861327.609
317e | 118 | 1552861328.514
317e | 58 | 1552861329.336
317e | 58 | 1552861330.317
317e | 54 | 1552861330.935
3441 | 68 | 1552861324.765
3441 | 84 | 1552861326.665
3441 | 56 | 1552861327.627
3441 | 50 | 1552861330.952
5fe6 | 42 | 1552993248.398
5fe6 | 44 | 1552993255.883
5fe6 | 44 | 1553166049.261
c742 | 62 | 1552861322.149
c742 | 68 | 1552861322.455
(18 rows)
log=# select id, regr_slope(elapsed, ts) as trend from sb1 group by id;
id |trend
--+--
c742 |
317e |
5fe6 | 5.78750952760444e-06
3441 |
(4 rows)
Interestingly, the same dataset and function in Oracle 11.2 returns the
same value for one of the ids and non-null values for the other ids :
SQL> select * from sb1 order by id, ts;
IDELAPSED TS
-- --
317e 86 1552861322.627
317e 58 1552861324.747
317e 52 1552861325.722
317e 58 1552861326.647
317e 82 1552861327.609
317e 118 1552861328.514
317e 58 1552861329.336
317e 58 1552861330.317
317e 54 1552861330.935
3441 68 1552861324.765
3441 84 1552861326.665
3441 56 1552861327.627
3441 50 1552861330.952
5fe6 42 1552993248.398
5fe6 44 1552993255.883
5fe6 44 1553166049.261
c742 62 1552861322.149
c742 68 1552861322.455
18 rows selected.
SQL> select id, regr_slope(elapsed, ts) from sb1 group by id;
ID REGR_SLOPE(ELAPSED,TS)
-- --
c742 19.6078431
5fe6 5.7875E-06
317e -1.0838511
3441 -3.8283951
If pg is correctly returning NULL, I'd be interested to understand the
circumstances under which this can occur.
Thanks,
Steve
Re: regr_slope returning NULL
Steve Baldwin writes: > Consider the following: > ... > log=# select id, regr_slope(elapsed, ts) as trend from sb1 group by id; > id |trend > --+-- > c742 | > 317e | > 5fe6 | 5.78750952760444e-06 > 3441 | > (4 rows) Hm, I get regression=# select id, regr_slope(elapsed, ts) as trend from sb1 group by id; id | trend --+--- c742 |19.607858781290517 317e | -1.0838511987808963 5fe6 | 5.787509483586743e-06 3441 |-3.828395463097356 (4 rows) What platform are you doing this on, and what exactly is the PG version? > If pg is correctly returning NULL, I'd be interested to understand the > circumstances under which this can occur. The source code shows two cases in which NULL would be returned: /* if N is 0 we should return NULL */ if (N < 1.0) PG_RETURN_NULL(); /* per spec, return NULL for a vertical line */ if (Sxx == 0) PG_RETURN_NULL(); Maybe the cases you're looking at are sufficiently numerically ill-conditioned that you could get Sxx == 0 depending on platform- specific roundoff error, but it seems fishy. regards, tom lane
When to store data that could be derived
Hi all As I understand it, a general rule of thumb is that you should never create a physical column if the data could be derived from existing columns. A possible reason for breaking this rule is for performance reasons. I have a situation where I am considering breaking the rule, but I am not experienced enough in SQL to know if my reason is valid. I would appreciate it if someone could glance at my 'before' and 'after' scenarios and see if, from a 'gut-feel' point of view, I should proceed. I have a VIEW constructed as follows - CREATE VIEW view_name AS [select statement 1] UNION ALL [select statement 2] etc. This is one of the select statements. I will give the 'after' scenario first - SELECT 'arec' AS tran_type, a.row_id AS tran_row_id, a.tran_number AS tran_number, a.cust_row_id AS cust_row_id, CASE WHEN a.tran_type = 'ar_rec' THEN y.tran_date WHEN a.tran_type = 'cb_rec' THEN w.tran_date END AS tran_date, CASE WHEN a.tran_type = 'ar_rec' THEN y.text WHEN a.tran_type = 'cb_rec' THEN w.text END AS text, 0 - a.arec_cust AS amount_cust, 0 - a.arec_local AS amount_local FROM ar_rec_subtran a LEFT JOIN ar_tran_rec_det z ON z.row_id = a.tran_det_row_id LEFT JOIN ar_tran_rec y ON y.row_id = z.tran_row_id LEFT JOIN cb_tran_rec_det x ON x.row_id = a.tran_det_row_id LEFT JOIN cb_tran_rec w ON w.row_id = x.tran_row_id WHERE CASE WHEN a.tran_type = 'ar_rec' THEN y.posted WHEN a.tran_type = 'cb_rec' THEN w.posted END = '1' The two columns a.arec_cust and a.arec_local *can* be derived from other columns, and in fact that is how it is working at the moment, so here is the 'before' scenario - SELECT 'arec' AS tran_type, a.row_id AS tran_row_id, a.tran_number AS tran_number, a.cust_row_id AS cust_row_id, CASE WHEN a.tran_type = 'ar_rec' THEN y.tran_date WHEN a.tran_type = 'cb_rec' THEN w.tran_date END AS tran_date, CASE WHEN a.tran_type = 'ar_rec' THEN y.text WHEN a.tran_type = 'cb_rec' THEN w.text END AS text, ROUND(0 - (ROUND(a.arec_amount / CASE WHEN a.tran_type = 'ar_rec' THEN y.tran_exch_rate WHEN a.tran_type = 'cb_rec' THEN w.tran_exch_rate END * a.cust_exch_rate, u.scale)), 2) AS amount_cust, ROUND(0 - (ROUND(a.arec_amount / CASE WHEN a.tran_type = 'ar_rec' THEN y.tran_exch_rate WHEN a.tran_type = 'cb_rec' THEN w.tran_exch_rate END, s.scale)), 2) AS amount_local FROM ar_rec_subtran a LEFT JOIN ar_tran_rec_det z ON z.row_id = a.tran_det_row_id LEFT JOIN ar_tran_rec y ON y.row_id = z.tran_row_id LEFT JOIN cb_tran_rec_det x ON x.row_id = a.tran_det_row_id LEFT JOIN cb_tran_rec w ON w.row_id = x.tran_row_id LEFT JOIN ar_customers v ON v.row_id = a.cust_row_id LEFT JOIN adm_currencies u ON u.row_id = v.currency_id LEFT JOIN adm_params t ON t.row_id = 1 LEFT JOIN adm_currencies s ON s.row_id = t.local_curr_id WHERE CASE WHEN a.tran_type = 'ar_rec' THEN y.posted WHEN a.tran_type = 'cb_rec' THEN w.posted END = '1' As you can see, complexity has increased and there are four additional JOINs. I am expecting the VIEW to be used extensively for query purposes, and my gut-feel says that the second one is likely to lead to performance problems in a system with a lot of data and a lot of users. I am not looking for an answer - I know that I should create dummy data and run some timing tests. I was just wondering if someone more experienced would wince when they look at the second SELECT, or if they would shrug and think that it looks fine. Any input will be appreciated. Frank Millman
