Re: [ADMIN] diskspace
On 5 February 2013 20:33, Geoff Winkless wrote: > http://www.postgresql.org/docs/9.2/static/storage-page-layout.html gives > detail... > > Let's say around 249MB (23 bytes per row, according to that page) for the > columns you mention, so that leaves 234MB unexplained. > > I can see 44 bytes per page header (given 5 columns, so 20 bytes > ItemIdData, with 24 bytes PageHeaderData). Given page size of 8kb that > would imply page headers of about 3.3MB over a 611MB table. > Of course I got that slightly wrong: ItemIdData is for each row, not for each column; an extra 4 bytes for each row makes the per-row space 290MB, leaving 167MB unexplained. In answer to other questions: the data was inserted in bulk, so there have been no updates; even so I have run a VACUUM FULL just in case and it makes no difference. I'm assuming the remaining 167MB is related to the alignment requirements, although that does seem quite a lot at 15 bytes per row (perhaps I'm just unfortunate with the data sizes resulting in poor alignment). I guess the answer to my question is that there is no answer to my question; pg just does use a massive (especially in relation to thin but tall tables) proportion of diskspace for its own purposes. Thanks again for all the responses. Geoff
Re: [ADMIN] diskspace
Geoff Winkless wrote: [trying to account for the disk space used] > Of course I got that slightly wrong: ItemIdData is for each row, not for each > column; an extra 4 bytes > for each row makes the per-row space 290MB, leaving 167MB unexplained. > I'm assuming the remaining 167MB is related to the alignment requirements, > although that does seem > quite a lot at 15 bytes per row (perhaps I'm just unfortunate with the data > sizes resulting in poor > alignment). > > I guess the answer to my question is that there is no answer to my question; > pg just does use a > massive (especially in relation to thin but tall tables) proportion of > diskspace for its own purposes. I doubt that PostgreSQL has substantially more disk overhead than other DBMS with comparable capabilities (comparison with flat files or MyISAM would be unfair). Have you tried using pg_filedump (http://pgfoundry.org/frs/?group_id=1000541) to dump a page or two of your table and figure out what is where and where the space went? Yours, Laurenz Albe -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] diskspace
On 6 February 2013 11:04, Albe Laurenz wrote: > I doubt that PostgreSQL has substantially more disk overhead > than other DBMS with comparable capabilities (comparison with > flat files or MyISAM would be unfair). > You're right, of course; the same data on InnoDB works out if anything slightly larger, as far as I can tell. I wasn't (and I'm not) trying to do-down pgsql, just trying to figure out if there's a way of cutting back on the extra space used. In this instance it would be nice to be able to mark a table as WORM, for example, and remove the need for any of this stuff. At least in MySQL I can specify MyISAM for the table, since it rarely if ever needs updates and so there's no requirement for MVCC. Have you tried using pg_filedump > (http://pgfoundry.org/frs/?group_id=1000541) > to dump a page or two of your table and figure > out what is where and where the space went? > I haven't; I will do for interest's sake, thanks for the suggestion. Geoff
Re: [ADMIN] diskspace
On 6 February 2013 11:12, Geoff Winkless wrote:
> On 6 February 2013 11:04, Albe Laurenz wrote:
>
>> Have you tried using pg_filedump
>> (http://pgfoundry.org/frs/?group_id=1000541)
>> to dump a page or two of your table and figure
>> out what is where and where the space went?
>>
>
> I haven't; I will do for interest's sake, thanks for the suggestion.
>
A simple hexdump output shows that I can see each row taking an extra 28 to
31 (depending on the number of bytes padding to align after the varchar)
bytes inline, which is about we thought. There's also a variable amount of
wasted space in each page where the next row won't fit in the page, varying
between 0 and (I guess) the largest row size + 30.
pg_filedump -a 22212| grep -i 'Free space' | cut -c46- | perl -nle '$sum +=
$_ } END { print $sum'
gives us a total of the "free space" values for all blocks in the 22212
table at 1875964 bytes.
Given that there are 11367253 rows and we accept (being overly generous) an
extra 35 bytes per row (379MB) plus 178MB real data plus just under 2MB
free space plus 78494 block headers of 60 bytes each (ignoring the
4-bytes-per-row in the block header because we've already included that in
the "35" bytes-per-row value) of 4MB gives a total of 563MB. I'm still 65MB
short of the 618MB space taken.
Not that that's going to make any significant difference but I am now
intrigued as to where it's gone. :)
Geoff
[ADMIN] Facing authentication error on postgres 9.2 -> dblink functions
Hello Everyone,
I am using postgres 9.2 and when executing function dblink facing a fatal
error while trying to execute dblink_connect as follows:
* SELECT * FROM dblink_connect('host=127.0.0.1 port=5432 dbname=postgres
password=test')*
*ERROR*: could not establish connection DETAIL: FATAL: password
authentication failed for user "NETWORK SERVICE"
What this error is related to? Do I need to modify pg_hba.conf file by any
chance?
Thanks..
Re: [ADMIN] Facing authentication error on postgres 9.2 -> dblink functions
Dev Kumkar wrote:
> I am using postgres 9.2 and when executing function dblink facing a fatal
> error while trying to
> execute dblink_connect as follows:
>
> SELECT * FROM dblink_connect('host=127.0.0.1 port=5432 dbname=postgres
> password=test')
>
> ERROR: could not establish connection DETAIL: FATAL: password
> authentication failed for user
> "NETWORK SERVICE"
>
> What this error is related to? Do I need to modify pg_hba.conf file by any
> chance?
You should specify a user=username option in the connection string,
otherwise that defaults to your operating system user name.
Yours,
Laurenz Albe
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Re: [HACKERS] Facing authentication error on postgres 9.2 -> dblink functions
On 02/06/2013 08:09 AM, Dev Kumkar wrote:
Hello Everyone,
I am using postgres 9.2 and when executing function dblink facing a
fatal error while trying to execute dblink_connect as follows:
/SELECT * FROM dblink_connect('host=127.0.0.1 port=5432
dbname=postgres password=test')/
*ERROR*: could not establish connection DETAIL: FATAL: password
authentication failed for user "NETWORK SERVICE"
What this error is related to? Do I need to modify pg_hba.conf file by
any chance?
Thanks..
Do NOT send questions to multiple lists. That is a waste of everybody's
time. So do NOT follow up this email. This question belongs on
pgsql-general. If you have further questions pleease ask there.
The short answer is that you need to provide the user name in your
connect string.
cheers
andrew
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Facing authentication error on postgres 9.2 -> dblink functions
Great.. thanks a lot Laurenz Albe!
Works perfect !!
Regards - Dev
On Wed, Feb 6, 2013 at 7:00 PM, Albe Laurenz wrote:
> Dev Kumkar wrote:
> > I am using postgres 9.2 and when executing function dblink facing a
> fatal error while trying to
> > execute dblink_connect as follows:
> >
> > SELECT * FROM dblink_connect('host=127.0.0.1 port=5432
> dbname=postgres password=test')
> >
> > ERROR: could not establish connection DETAIL: FATAL: password
> authentication failed for user
> > "NETWORK SERVICE"
> >
> > What this error is related to? Do I need to modify pg_hba.conf file by
> any chance?
>
> You should specify a user=username option in the connection string,
> otherwise that defaults to your operating system user name.
>
> Yours,
> Laurenz Albe
>
Re: [ADMIN] Facing authentication error on postgres 9.2 -> dblink functions
Hey went ahead and tried to query across machines and facing following
error:
SELECT * FROM dblink_connect('host=x.x.x.x port=5432 dbname=postgres
user=postgres password=test')
could not connect to server: Connection timed out (0x274C/10060)
Is the server running on host "x.y.z.com" (x.x.x.x) and accepting
TCP/IP connections on port 5432?
Tried modifying pg_hba as follows:
1st try
hostall all 0.0.0.0/0md5
2nd try
hostall all .z.commd5
postgresql.conf looks good for connections and authentication:
listen_addresses = '*'
Please suggest?
Thanks...
On Wed, Feb 6, 2013 at 11:25 PM, Dev Kumkar wrote:
> Great.. thanks a lot Laurenz Albe!
>
> Works perfect !!
>
> Regards - Dev
>
>
> On Wed, Feb 6, 2013 at 7:00 PM, Albe Laurenz wrote:
>
>> Dev Kumkar wrote:
>> > I am using postgres 9.2 and when executing function dblink facing a
>> fatal error while trying to
>> > execute dblink_connect as follows:
>> >
>> > SELECT * FROM dblink_connect('host=127.0.0.1 port=5432
>> dbname=postgres password=test')
>> >
>> > ERROR: could not establish connection DETAIL: FATAL: password
>> authentication failed for user
>> > "NETWORK SERVICE"
>> >
>> > What this error is related to? Do I need to modify pg_hba.conf file by
>> any chance?
>>
>> You should specify a user=username option in the connection string,
>> otherwise that defaults to your operating system user name.
>>
>> Yours,
>> Laurenz Albe
>>
>
>
Re: [ADMIN] Facing authentication error on postgres 9.2 -> dblink functions
Checked further netstat and could see following lines:
TCP0.0.0.0:5432 0.0.0.0:0 LISTENING
Then checked from remote machine 'telnet' to target database machine and
could see connection failing, looking further port was blocked and
unblocking port resolved this. Queries working fine.
So far so good..
Thanks...
On Thu, Feb 7, 2013 at 12:49 AM, Dev Kumkar wrote:
> Hey went ahead and tried to query across machines and facing following
> error:
>
> SELECT * FROM dblink_connect('host=x.x.x.x port=5432 dbname=postgres
> user=postgres password=test')
>
> could not connect to server: Connection timed out (0x274C/10060)
> Is the server running on host "x.y.z.com" (x.x.x.x) and accepting
> TCP/IP connections on port 5432?
>
> Tried modifying pg_hba as follows:
> 1st try
> hostall all 0.0.0.0/0md5
>
> 2nd try
> hostall all .z.commd5
>
>
> postgresql.conf looks good for connections and authentication:
> listen_addresses = '*'
>
> Please suggest?
>
> Thanks...
>
> On Wed, Feb 6, 2013 at 11:25 PM, Dev Kumkar wrote:
>
>> Great.. thanks a lot Laurenz Albe!
>>
>> Works perfect !!
>>
>> Regards - Dev
>>
>>
>> On Wed, Feb 6, 2013 at 7:00 PM, Albe Laurenz wrote:
>>
>>> Dev Kumkar wrote:
>>> > I am using postgres 9.2 and when executing function dblink facing a
>>> fatal error while trying to
>>> > execute dblink_connect as follows:
>>> >
>>> > SELECT * FROM dblink_connect('host=127.0.0.1 port=5432
>>> dbname=postgres password=test')
>>> >
>>> > ERROR: could not establish connection DETAIL: FATAL: password
>>> authentication failed for user
>>> > "NETWORK SERVICE"
>>> >
>>> > What this error is related to? Do I need to modify pg_hba.conf file by
>>> any chance?
>>>
>>> You should specify a user=username option in the connection string,
>>> otherwise that defaults to your operating system user name.
>>>
>>> Yours,
>>> Laurenz Albe
>>>
>>
>>
[ADMIN] PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax
All, I'm wondering about how postgresql calculates the value for shared buffers as I see some discrepancies with what the following script provides versus what is recommended in the pgctl.log when the database fails to start. #!/bin/bash # simple shmsetup script page_size=`getconf PAGE_SIZE` phys_pages=`getconf _PHYS_PAGES` shmall=`expr $phys_pages / 2` shmmax=`expr $shmall \* $page_size` echo kernel.shmmax = $shmmax echo kernel.shmall = $shmall Any pointers which would explain these differences would be greatly appreciate. Ultimately, I'd like to calculate the expected kernel.shmmax which matches postgresql's shmget() call. TIA, Mel
Re: [ADMIN] PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax
On Thu, Feb 7, 2013 at 6:28 AM, Mel Llaguno wrote: > All, > > I'm wondering about how postgresql calculates the value for shared buffers > as I see some discrepancies with what the following script provides versus > what is recommended in the pgctl.log when the database fails to start. > > #!/bin/bash > # simple shmsetup script > page_size=`getconf PAGE_SIZE` > phys_pages=`getconf _PHYS_PAGES` > shmall=`expr $phys_pages / 2` > shmmax=`expr $shmall \* $page_size` > echo kernel.shmmax = $shmmax > echo kernel.shmall = $shmall > > Any pointers which would explain these differences would be greatly > appreciate. Ultimately, I'd like to calculate the expected kernel.shmmax > which matches postgresql's shmget() call. > I don't know where you got hold of this script, but it seems this is setting SHMMAX equal to half the size of RAM. So if your system has 4GB RAM, a process can request maximum of 2GB of shared memory. The amount of shared memory PostgreSQL needs is governed by several configuration parameters, but the most important and the one which will eat up most of that shared memory is "shared_buffers". So if you want to work within the bounds of SHMMAX set by this script, you would need to set shared_buffers a notch lower than that. But often its easier and better to decide your Postgres configuration parameters and then set SHMMAX to satisfy that. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax
Pavan, Thanks for your reply. I agree with your statement that you should set the configuration parameters first, but I would like to be able to calculate the SHMMAX value based on those parameters. This is particularly useful when suggesting postgresql.conf optimizations to our customers whose machine have a lot of RAM (64+GB). Having to guess this value is far from ideal; what I'd like is the formula used by postgresql that generates the shmget() value displayed in the pgctl.log. Thanks, Mel From: Pavan Deolasee [pavan.deola...@gmail.com] Sent: Wednesday, February 06, 2013 10:12 PM To: Mel Llaguno Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax On Thu, Feb 7, 2013 at 6:28 AM, Mel Llaguno wrote: > All, > > I'm wondering about how postgresql calculates the value for shared buffers > as I see some discrepancies with what the following script provides versus > what is recommended in the pgctl.log when the database fails to start. > > #!/bin/bash > # simple shmsetup script > page_size=`getconf PAGE_SIZE` > phys_pages=`getconf _PHYS_PAGES` > shmall=`expr $phys_pages / 2` > shmmax=`expr $shmall \* $page_size` > echo kernel.shmmax = $shmmax > echo kernel.shmall = $shmall > > Any pointers which would explain these differences would be greatly > appreciate. Ultimately, I'd like to calculate the expected kernel.shmmax > which matches postgresql's shmget() call. > I don't know where you got hold of this script, but it seems this is setting SHMMAX equal to half the size of RAM. So if your system has 4GB RAM, a process can request maximum of 2GB of shared memory. The amount of shared memory PostgreSQL needs is governed by several configuration parameters, but the most important and the one which will eat up most of that shared memory is "shared_buffers". So if you want to work within the bounds of SHMMAX set by this script, you would need to set shared_buffers a notch lower than that. But often its easier and better to decide your Postgres configuration parameters and then set SHMMAX to satisfy that. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax
On Thu, Feb 7, 2013 at 11:01 AM, Mel Llaguno wrote: > Having to guess this value is far from ideal; what I'd like is the formula > used by postgresql that generates the shmget() value displayed in the > pgctl.log. > There is no easy way or at least none that I'm aware of, to get the exact value of shared memory needed for Postgres. If you have access to the source code, you can look at CreateSharedMemoryAndSemaphores() function in src/backend/storage/ipc/ipci.c to see what all goes in determining the size for shmget() Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax
Pavan, Thanks. I'll have a look at the source code. M. From: Pavan Deolasee [pavan.deola...@gmail.com] Sent: Wednesday, February 06, 2013 10:41 PM To: Mel Llaguno Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax On Thu, Feb 7, 2013 at 11:01 AM, Mel Llaguno wrote: > Having to guess this value is far from ideal; what I'd like is the formula > used by postgresql that generates the shmget() value displayed in the > pgctl.log. > There is no easy way or at least none that I'm aware of, to get the exact value of shared memory needed for Postgres. If you have access to the source code, you can look at CreateSharedMemoryAndSemaphores() function in src/backend/storage/ipc/ipci.c to see what all goes in determining the size for shmget() Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax
Mel Llaguno writes: > Thanks for your reply. I agree with your statement that you should set the > configuration parameters first, but I would like to be able to calculate the > SHMMAX value based on those parameters. This is particularly useful when > suggesting postgresql.conf optimizations to our customers whose machine have > a lot of RAM (64+GB). Having to guess this value is far from ideal; what I'd > like is the formula used by postgresql that generates the shmget() value > displayed in the pgctl.log. There's some rather old information in Table 17-2 here: http://www.postgresql.org/docs/9.2/static/kernel-resources.html#SYSVIPC As Pavan says, the shared_buffers term is usually the only one worth worrying about. The traditional advice is to not set that to more than about a quarter of your physical RAM, which would mean that this script you're using to set SHMMAX is leaving lots of headroom, which is perfectly OK. (AFAIK there is no penalty to setting SHMMAX larger than you need.) There's more info worth looking at here: https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax
Tom, Thanks for the response. I've been doing a lot of performance tuning for our customers and I've found that wiki link a life saver ;-) I'm trying to come up with a precise way to calculate the shmget() value which postgresql uses in the pgctl.log message when the kernel.shmmax is set too low. There are situations when knowing this exact value is useful as our customers are sometimes not as familiar with postgresql as we'd like. Being able to calculate this value from enabled settings in postgresql.conf would help us provide accurate guidance. As per Pavan's suggestion, I'm having a look at the src/backend/storage/ipc/ipci.c. Thanks, Mel From: Tom Lane [t...@sss.pgh.pa.us] Sent: Wednesday, February 06, 2013 10:49 PM To: Mel Llaguno Cc: Pavan Deolasee; pgsql-admin@postgresql.org Subject: Re: [ADMIN] PostgreSQL's share_buffer calculation using shmget() versus kernel.shmmax Mel Llaguno writes: > Thanks for your reply. I agree with your statement that you should set the > configuration parameters first, but I would like to be able to calculate the > SHMMAX value based on those parameters. This is particularly useful when > suggesting postgresql.conf optimizations to our customers whose machine have > a lot of RAM (64+GB). Having to guess this value is far from ideal; what I'd > like is the formula used by postgresql that generates the shmget() value > displayed in the pgctl.log. There's some rather old information in Table 17-2 here: http://www.postgresql.org/docs/9.2/static/kernel-resources.html#SYSVIPC As Pavan says, the shared_buffers term is usually the only one worth worrying about. The traditional advice is to not set that to more than about a quarter of your physical RAM, which would mean that this script you're using to set SHMMAX is leaving lots of headroom, which is perfectly OK. (AFAIK there is no penalty to setting SHMMAX larger than you need.) There's more info worth looking at here: https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
