RE: Disk Groups/Storage Management for a Large Database in PostgreSQL

[Scot Kreienkamp]

El lun, 22 ene 2024 18:44, Amit Sharma 
mailto:amitpg...@gmail.com>> escribió:
Hi,

We are building new VMs for PostgreSQL v15 on RHEL 8.x For a large database of 
15TB-20TB.

I would like to know from the experts that is it a good idea to create LVMs to 
manage storage for the database?

Or are there any other better options/tools for disk groups in PostgreSQL, 
similar to ASM in Oracle?

Thanks
Amit

Simple question that requires a somewhat more complex answer. There are 
actually 3 metrics to consider:

1) Capacity
Your database doesn't fit on a single disk, so you need to distribute your data 
across several disks. LVM would indeed be an option (as well as ZFS or RAID 
disk arrays)

2) Safety
If you loose 1 disk, your data is at risk, as you're likely to loose all tables 
partially loaded on that disk. LVM is still an option as long as it is 
configured on a RAID array. ZFS can do that natively.

3) Performance
Oracle ADM ensures performance by automatically controlling the distribution of 
the tables. I would need to see on a real case how it is actually done. For 
sure, LVM and ZFS won't have this type of granularity.

On the other hand, you can distribute your data in table partitions to help 
this distribution. It is not automatic but will surely help you to distribute 
your workload.


As he is building VM’s I’m assuming the hardware level has all the redundancy 
for RAID/ZFS/etc.  If that is the case then you don’t want to run RAID/ZFS/etc 
on top of that, let the hardware do its thing.  If my assumption is wrong then 
ignore everything I’m saying.

One thing I found that helps with speed of reads/writes… you can spread your 
read/write load across multiple SCSI controllers/disks using LVM.  For example, 
I’m assuming VMWare which allows 4 SCSI controllers.  Set the OS disk on SCSI 
controller 0, then spread your database disks in sets of 3 across SCSI 
controllers 1-3, IE 3 disks of 5TB each, one on each SCSI controller.  Then 
when you create your LVM partition specify the option to stripe it with 3 
stripes.  That gives you a setup where you are multiplexing reads/writes across 
all 3 SCSI controllers and disks instead of bottlenecking them all through 1 
SCSI controller and disk at a time.

Scot Kreienkamp | Applications Infrastructure Architect | La-Z-Boy Corporate
One La-Z-Boy Drive | Monroe, Michigan 48162 | • (734) 384-6403 | |  • 
1-734-915-1444  | • scot.kreienk...@la-z-boy.com
www.la-z-boy.com  | 
facebook.com/lazboy  | 
twitter.com/lazboy | 
youtube.com/lazboy
[cid:smallerlzbonlylogoforsign_b8ca06bf-75b5-4619-8093-c9418c455597.png]

This message is intended only for the individual or entity to which it is 
addressed. It may contain privileged, confidential information which is exempt 
from disclosure under applicable laws. If you are not the intended recipient, 
you are strictly prohibited from disseminating or distributing this information 
(other than to the intended recipient) or copying this information. If you have 
received this communication in error, please notify us immediately by e-mail or 
by telephone at the above number. Thank you.

Re: Disk Groups/Storage Management for a Large Database in PostgreSQL

[Amit Sharma]

Thanks Olivier and Scot for your inputs!

Another data point I would like to share is that VMs will be built in Azure
Cloud with Azure Managed Storage and Locally redundant storage (LRS) option
with a remote DR as well.

 LVM or ZFS would still be a good option to allow easy storage/disk
management like add, resize or remove disks while PostgreSQL services are
up?
Is equal data distribution a challenge on LVM/ZFS disks?


Thanks
Amit


On Tue, Jan 23, 2024 at 9:49 AM Scot Kreienkamp <
scot.kreienk...@la-z-boy.com> wrote:

> El lun, 22 ene 2024 18:44, Amit Sharma  escribió:
>
> Hi,
>
>
>
> We are building new VMs for PostgreSQL v15 on RHEL 8.x For a large
> database of 15TB-20TB.
>
>
>
> I would like to know from the experts that is it a good idea to create
> LVMs to manage storage for the database?
>
>
>
> Or are there any other better options/tools for disk groups in PostgreSQL,
> similar to ASM in Oracle?
>
>
>
> Thanks
>
> Amit
>
>
>
> Simple question that requires a somewhat more complex answer. There are
> actually 3 metrics to consider:
>
>
>
> 1) Capacity
>
> Your database doesn't fit on a single disk, so you need to distribute your
> data across several disks. LVM would indeed be an option (as well as ZFS or
> RAID disk arrays)
>
>
>
> 2) Safety
>
> If you loose 1 disk, your data is at risk, as you're likely to loose all
> tables partially loaded on that disk. LVM is still an option as long as it
> is configured on a RAID array. ZFS can do that natively.
>
>
>
> 3) Performance
>
> Oracle ADM ensures performance by automatically controlling the
> distribution of the tables. I would need to see on a real case how it is
> actually done. For sure, LVM and ZFS won't have this type of granularity.
>
>
>
> On the other hand, you can distribute your data in table partitions to
> help this distribution. It is not automatic but will surely help you to
> distribute your workload.
>
>
>
>
>
> As he is building VM’s I’m assuming the hardware level has all the
> redundancy for RAID/ZFS/etc.  If that is the case then you don’t want to
> run RAID/ZFS/etc on top of that, let the hardware do its thing.  If my
> assumption is wrong then ignore everything I’m saying.
>
>
>
> One thing I found that helps with speed of reads/writes… you can spread
> your read/write load across multiple SCSI controllers/disks using LVM.  For
> example, I’m assuming VMWare which allows 4 SCSI controllers.  Set the OS
> disk on SCSI controller 0, then spread your database disks in sets of 3
> across SCSI controllers 1-3, IE 3 disks of 5TB each, one on each SCSI
> controller.  Then when you create your LVM partition specify the option to
> stripe it with 3 stripes.  That gives you a setup where you are
> multiplexing reads/writes across all 3 SCSI controllers and disks instead
> of bottlenecking them all through 1 SCSI controller and disk at a time.
>
> *Scot Kreienkamp | Applications Infrastructure Architect | La-Z-Boy
> Corporate*
> One La-Z-Boy Drive | Monroe, Michigan 48162 | ( (734) 384-6403 | |  )
> 1-734-915-1444  | * scot.kreienk...@la-z-boy.com
> www.la-z-boy.com  | facebook.com/lazboy  | twitter.com/lazboy |
> youtube.com/lazboy
> [image: Smaller LZB Only Logo for Sign.png]
>
> This message is intended only for the individual or entity to which it is
> addressed. It may contain privileged, confidential information which is
> exempt from disclosure under applicable laws. If you are not the intended
> recipient, you are strictly prohibited from disseminating or distributing
> this information (other than to the intended recipient) or copying this
> information. If you have received this communication in error, please
> notify us immediately by e-mail or by telephone at the above number. Thank
> you.
>

Need assistance for running postgresql procedures

[Sasmit Utkarsh]

Hi Postgres Team,

Kindly assist with the issue faced while calling procedures on postgresql
using libpq in C. I have attached all the details in the note. Please let
me know if you need any more information


Regards,
Sasmit Utkarsh
+91-7674022625
In general code flow:
main() -> SQL_init_db_connection() -> SQL_get_RIAT_size()

globals:
char SelectSizeName[11];
char SelectSizeCommand[150];
int  SelectSizeNParams;
Oid  SelectSizeParamTypes[2];



//SQL_init_db_connection() which initiates connection and creates procedures 
e.t.c

SQL_init_db_connection():
{

//some code

res = PQexec(conn, "CREATE OR REPLACE PROCEDURE sql_select_size_procedure(hexid 
text, rtp_in integer, INOUT size_data text) LANGUAGE plpgsql AS $$ BEGIN SELECT 
size FROM riat WHERE id = hexid AND rtp = rtp_in INTO size_data; END; $$;");
if(PQresultStatus(res) != PGRES_COMMAND_OK)
{
LOG_ERROR("CREATE sql_select_size_procedure failed! %s", 
PQerrorMessage(conn));
SQL_exit_nicely(conn,res);
}
PQclear(res);


// some other code

sprintf(SelectSizeName,"%s","SelectSize");
if(SQL_vsn10) {
sprintf(SelectSizeCommand,"%s","SELECT size FROM riat WHERE id = $1 AND 
rtp = $2");
} else {
sprintf(SelectSizeCommand,"%s","CALL SQL_select_size_procedure($1, $2, 
NULL)");
}

SelectSizeNParams   = 2;
SelectSizeParamTypes[0] = 25;  // {text}
SelectSizeParamTypes[1] = 23;  // {int}

//some other code

res = PQprepare(conn,
SelectSizeName,
SelectSizeCommand,
SelectSizeNParams,
SelectSizeParamTypes);
LOG_DEBUG("%s() PREPARE SelectSize PQresultStatus = 
%s",__func__,PQresStatus(PQresultStatus(res)));
if(PQresultStatus(res) != PGRES_COMMAND_OK)
{
LOG_ERROR("PREPARE failed for RIAT! %s", PQerrorMessage(conn));
SQL_exit_nicely(conn,res);
}
PQclear(res);
}

//But in SQL_get_RIAT_size():

/*-
 return block size from RIAT table for record ID and RTP
 --*/
void SQL_get_RIAT_size(unsigned int ID, int rtp, int *BlkSize)
{
int blkSz = BLOCK_L4;  // default to 4k if RIAT table does not contain the 
blocksize for the Record ID and rtp
char *size = NULL;

char *rtpVal = (char *)&rtp;
char hexId[9] = {0};
char *hexIdVal = (char *)&hexId;

const char *paramValues[2] = {hexIdVal, rtpVal};
int paramLengths[2] = {4, sizeof(rtp)};
int paramFormats[2] = {1, 1};
int resultFormat = 1;

int nFields = 0;
int nTuples = 0;
PGresult *res = NULL;

sprintf (hexId, "%04X", ID);
LOG_TRACE("%s() - ID %s rtp %i 
---",__func__,hexId,rtp);

if(sql_db) 
{
LOG_DEBUG("%s() SelectSizeCommand = %s '%s' 
%d",__func__,SelectSizeCommand,hexId,rtp);
LOG_DEBUG("%s() SeelctSizeNParams = 
%i",__func__,SelectSizeNParams);
res = PQexecPrepared (conn, 
  SelectSizeName, 
  SelectSizeNParams,
  paramValues, 
  paramLengths, 
  paramFormats,
  resultFormat);
}
else
{
char queryStmt[100] = {0};
sprintf (queryStmt, "SELECT size FROM riat WHERE id = 
'%s' AND rtp = %d", hexId, rtp);
LOG_DEBUG("%s() FILE path - %s",__func__,queryStmt);
PQclear(res);
res = PQexec (conn, queryStmt);
}
LOG_DEBUG("%s() res = 
%s",__func__,PQresStatus(PQresultStatus(res)));

if ( PQresultStatus(res) != PGRES_TUPLES_OK ) {
LOG_ERROR("SELECT failed: %s", PQerrorMessage(conn));
PQclear(res);
} else {
nFields = PQnfields(res);
nTuples = PQntuples(res);
LOG_DEBUG("%s() nFields=%i 
nTuples=%i",__func__,nFields,nTuples);
if (nFields == 0 || nTuples == 0) {
LOG_ERROR("SELECT failed: NO ROWS/COLUMNS 
RETURNED");
PQclear(res);
} else if ( nTuples > 1 ) {
LOG_ERROR("More than 1 row is returned");
PQclear(res);
} else {
size = PQgetvalue (res, 0, 0);
LOG_DEBUG("%s() - size = %s",__func__,size);
  //See the below logs size is showing empty, even if there are 
records in db

if ( memcmp (size, "Small", 5) == 0 ) {

Re: how to trace a backend session

[Pierre Forstmann]

Hello,

I have coded an extension to trace SQL statements for specific backends:
https://github.com/pierreforstmann/pg_log_statements
(only SQL statements are traced - no wait events data is collected).

Pierre

Le lun. 22 janv. 2024 à 08:29, James Pang  a écrit :

> experts,
> We migrated our database from Oracle to Postgresql recently, we used
> to turn on Oracle session trace(that will capture all activities, SQL
> statements, waiting, waiting time), to do living troubleshooting.  could
> you direct any similar tracing in Postgresql v13 , v14.
>
> Thanks,
>
> James
>

Re: Need assistance for running postgresql procedures

[Francisco Olarte]

Hi sasmit.

On Tue, 23 Jan 2024 at 18:51, Sasmit Utkarsh  wrote:
> Kindly assist with the issue faced while calling procedures on postgresql 
> using libpq in C. I have attached all the details in the note. Please let me 
> know if you need any more information

I'm not familiar with using binary parameters, but are you sured you
do not need calling htonl(3) on the rtp value? ( 3 is man section, not
htonl argument ).

"Values passed in binary format require knowledge of the internal
representation expected by the backend.
* For example, integers must be passed in network byte order. **
 Passing numeric values requires knowledge of the server storage
format, as implemented in
src/backend/utils/adt/numeric.c::numeric_send() and
src/backend/utils/adt/numeric.c::numeric_recv()."

A quick test would be to use rtp=0x0400 and see if it then works (
if it comes from cmd line or similar, if it comes from code just use
hton )


Francisco Olarte.

RE: Disk Groups/Storage Management for a Large Database in PostgreSQL

[Scot Kreienkamp]

Thanks Olivier and Scot for your inputs!

Another data point I would like to share is that VMs will be built in Azure 
Cloud with Azure Managed Storage and Locally redundant storage (LRS) option 
with a remote DR as well.

 LVM or ZFS would still be a good option to allow easy storage/disk management 
like add, resize or remove disks while PostgreSQL services are up?
Is equal data distribution a challenge on LVM/ZFS disks?


Thanks
Amit



I would not call data distribution a challenge, but something to be aware of.  
If LVM has 3 disks in a pool it will, by default, use all of disk 1, then use 
all of disk 2, then use all of disk 3.  The reason for that is with the default 
you can add new disks one at a time.  With striping you must add new disks 
equal to the number of stripes.  Either way I would still advise use of LVM.

Scot Kreienkamp | Applications Infrastructure Architect | La-Z-Boy Corporate
One La-Z-Boy Drive | Monroe, Michigan 48162 | • (734) 384-6403 | |  • 
1-734-915-1444  |  Email: scot.kreienk...@la-z-boy.com



This message is intended only for the individual or entity to which it is 
addressed. It may contain privileged, confidential information which is exempt 
from disclosure under applicable laws. If you are not the intended recipient, 
you are strictly prohibited from disseminating or distributing this information 
(other than to the intended recipient) or copying this information. If you have 
received this communication in error, please notify us immediately by e-mail or 
by telephone at the above number. Thank you.

Providers missing from pgdg-common for GDAL 3.7 (related to multiple missing providers from pgdg-common)

[James Colannino]

A little while ago this month, this was reported:
https://www.spinics.net/lists/pgsql/msg219648.html

The issue was fixed for GDAL 3.6, but the same problem still exists for
GDAL 3.7, which also needs to be rebuilt against armadillo 12 for rhel 8
(and possibly 9 as well?) This is what I get currently when I try to update
my packages on AlmaLinux 8:

$ sudo dnf update
Last metadata expiration check: 0:14:08 ago on Tue 23 Jan 2024 04:16:31 PM
PST.
Error:
 Problem 1: package gdal37-libs-3.7.2-1PGDG.rhel8.x86_64 from @System
requires libarmadillo.so.10()(64bit), but none of the providers can be
installed
  - cannot install both armadillo-12.6.6-1.el8.x86_64 from epel and
armadillo-10.8.2-1.el8.x86_64 from @System
  - cannot install the best update candidate for package
gdal37-libs-3.7.2-1PGDG.rhel8.x86_64
  - cannot install the best update candidate for package
armadillo-10.8.2-1.el8.x86_64
 Problem 2: package gdal37-devel-3.7.2-1PGDG.rhel8.x86_64 from @System
requires libgdal.so.33()(64bit), but none of the providers can be installed
  - package gdal37-devel-3.7.2-1PGDG.rhel8.x86_64 from @System requires
gdal37-libs(x86-64) = 3.7.2-1PGDG.rhel8, but none of the providers can be
installed
  - package gdal37-libs-3.7.2-1PGDG.rhel8.x86_64 from @System requires
libarmadillo.so.10()(64bit), but none of the providers can be installed
  - package gdal37-libs-3.7.2-1PGDG.rhel8.x86_64 from pgdg-common requires
libarmadillo.so.10()(64bit), but none of the providers can be installed
  - cannot install both armadillo-12.6.6-1.el8.x86_64 from epel and
armadillo-10.8.2-1.el8.x86_64 from @System
  - package gdal36-libs-3.6.4-6PGDG.rhel8.x86_64 from pgdg-common requires
libarmadillo.so.12()(64bit), but none of the providers can be installed
  - cannot install the best update candidate for package
gdal37-devel-3.7.2-1PGDG.rhel8.x86_64
  - cannot install the best update candidate for package
gdal36-libs-3.6.4-5PGDG.rhel8.x86_64

Re: Need assistance for running postgresql procedures

[Sasmit Utkarsh]

Thanks, I'll check it out.

Regards,
Sasmit Utkarsh
+91-7674022625

On Wed, 24 Jan, 2024, 01:59 Francisco Olarte, 
wrote:

> Hi sasmit.
>
> On Tue, 23 Jan 2024 at 18:51, Sasmit Utkarsh 
> wrote:
> > Kindly assist with the issue faced while calling procedures on
> postgresql using libpq in C. I have attached all the details in the note.
> Please let me know if you need any more information
>
> I'm not familiar with using binary parameters, but are you sured you
> do not need calling htonl(3) on the rtp value? ( 3 is man section, not
> htonl argument ).
>
> "Values passed in binary format require knowledge of the internal
> representation expected by the backend.
> * For example, integers must be passed in network byte order.
> **
>  Passing numeric values requires knowledge of the server storage
> format, as implemented in
> src/backend/utils/adt/numeric.c::numeric_send() and
> src/backend/utils/adt/numeric.c::numeric_recv()."
>
> A quick test would be to use rtp=0x0400 and see if it then works (
> if it comes from cmd line or similar, if it comes from code just use
> hton )
>
>
> Francisco Olarte.
>